MySQL VS PostgreSQL JSON 데이터 INSERT & 조회 성능 및 장단점 비교하기

728x90

👨🏻‍🔬 테스트 주제
MySQL(5.7 이상)과 PostgreSQL 모두 JSON 타입의 컬럼을 지원한다.
Spring boot 2.7 + Spring Data JPA 사용 시 데이터베이스에 JSON 형식의 컬럼을 INSERT 하고,
JSON 데이터의 특정 key 값으로 value를 조회할 때의 속도 및 기타 특징을 비교해보려고 한다.

MySQL의 주요 특징

MySQL의 경우 JSON 데이터 내 전체 키를 인덱싱 하는 방법을 제공하지 않는다.
JSON 데이터 내 특정 키를 선택하여 인덱싱 할 수는 있다.
무조건 JSON 타입으로 저장해야 하는 특별한 상황이 아니면 해당 key만 따로 빼서 컬럼으로 추가하는 것이 더 좋은 선택일 수 있다.
- 별도의 컬럼으로 관리하는 것이 RDBMS의 장점을 온전히 활용할 수 있기 때문이다.

PostgreSQL의 주요 특징

PostgreSQL의 경우 'json' 타입 외에 'jsonb'라는 전용 json 타입을 사용할 수 있다.
이 타입은 JSON 데이터를 저장할 때 저장 공간을 절약하고 쿼리 속도를 높일 수 있다.
'jsonb' 타입으로 json 데이터를 컬럼에 저장하면 데이터 전체가 자동으로 B-tree 인덱싱이 적용되어 별도로 인덱스 생성을 할 필요가 없다.

알아둬야 할 사항

자바의 경우 Hibernate 6(Spring Boot 3.0 이상)부터 json, jsonb 타입을 정식으로 지원한다.
Hibernate 5 이하(Spring Boot 2.7 이하)에서는 지원하지 않으므로 2.7 이하의 버전에서는 Spring Data JPA를 활용한 Entity 클래스에서 필드와 컬럼 매핑 시 외부 라이브러리를 통해 json, jsonb 타입으로 매핑을 해줘야 한다.

테스트 조건

PC: 2022 M2 맥북 에어

프로젝트 툴 : Spring Boot 2.7 + Spring Data JPA 사용

데이터베이스 : MySQL 8.0.1, PostgreSQL 15

데이터 : 5만 개의 더미 데이터

목표 : 데이터 저장 및 조회 시 성능 비교

조건 :

MySQL은 JSON 타입으로 저장 후 조회
PostgreSQL은 JSONB 타입으로 저장 후 조회
첫 번째 조회 시 초기화 문제 등으로 이후 실행보다 속도가 현저히 느리므로 모든 테스트의 첫번째 실행은 제외함
JSON 데이터 내 키 값 중 어떤 값을 가져올지 정해지지 않는다고 가정

MySQL 테스트 시 ExcelData(Entity)

import io.hypersistence.utils.hibernate.type.json.JsonBinaryType;
import io.hypersistence.utils.hibernate.type.json.JsonType;
import lombok.AccessLevel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import javax.persistence.*;

@Entity(name = "excel_data")
@Getter
@TypeDef(name = "json", typeClass = JsonType.class) // json 타입
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class ExcelData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; 

    private String name; // 이름 컬럼

    private String phone; // 전화번호 컬럼

    @Type(type = "json") // json 타입
    @Column(name = "json_data", columnDefinition = "json")
    private String jsonData; // json 데이터 컬럼

    private ExcelData(String name, String phone, String jsonData) {
        this.name = name;
        this.phone = phone;
        this.jsonData = jsonData;
    }

    public static ExcelData createEntityOf(String name, String phone, String jsonData) {
        return new ExcelData(name, phone, jsonData);
    }
}

MySQL 테스트 시 사용할 엔티티 클래스이다.
외부 라이브러리를 통해 매핑할 컬럼에 json type을 지정했다.

PostgreSQL 테스트 시 ExcelData

import io.hypersistence.utils.hibernate.type.json.JsonBinaryType;
import io.hypersistence.utils.hibernate.type.json.JsonType;
import lombok.AccessLevel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import javax.persistence.*;

@Entity(name = "excel_data")
@Getter
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class) // jsonb 타입
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class ExcelData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name; // 이름 컬럼

    private String phone; // 전화번호 컬럼

    @Type(type = "jsonb") // jsonb 타입
    @Column(name = "json_data", columnDefinition = "jsonb")
    private String jsonData; // json 데이터 컬럼

    private ExcelData(String name, String phone, String jsonData) {
        this.name = name;
        this.phone = phone;
        this.jsonData = jsonData;
    }

    public static ExcelData createEntityOf(String name, String phone, String jsonData) {
        return new ExcelData(name, phone, jsonData);
    }
}

PostgreSQL 테스트 시 사용할 엔티티 클래스이다.
외부 라이브러리를 통해 매핑할 컬럼에 jsonb type을 지정했다.

공통 ExcelDataRepository

public interface ExcelDataRepository extends JpaRepository<ExcelData, Long> {

	// PostgreSQL json 데이터 내 단일키 조회
    @Query(value = "SELECT * FROM excel_data WHERE json_data ->> :key = :value", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValue(@Param("key") String key, @Param("value") String value);

	// PostgreSQL json 데이터 내 복합키 2 개 조회
    @Query(value = "SELECT * FROM excel_data WHERE json_data ->> :key = :value OR json_data ->> :key2 = :value2", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValue2(@Param("key") String key, @Param("value") String value, @Param("key2") String key2, @Param("value2") String value2);

	// PostgreSQL json 데이터 내 복합키 3 개 조회
    @Query(value = "SELECT * FROM excel_data WHERE json_data ->> :key = :value OR json_data ->> :key2 = :value2 OR json_data ->> :key3 = :value3", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValue3(@Param("key") String key, @Param("value") String value, @Param("key2") String key2, @Param("value2") String value2, @Param("key3") String key3, @Param("value3") String value3);

	// MySQL json 데이터 내 단일키 조회
    @Query(value = "SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$.\"', :key, '\"')) = :value", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValueMysql(@Param("key") String key, @Param("value") String value);

	// MySQL json 데이터 내 복합키 2 개 조회
    @Query(value = "SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$.\"', :key, '\"')) = :value OR JSON_EXTRACT(json_data, CONCAT('$.\"', :key2, '\"')) = :value2", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValueMysql2(@Param("key") String key, @Param("value") String value, @Param("key2") String key2, @Param("value2") String value2);

	// MySQL json 데이터 내 복합키 3 개 조회
    @Query(value = "SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$.\"', :key, '\"')) = :value OR JSON_EXTRACT(json_data, CONCAT('$.\"', :key2, '\"')) = :value2 OR JSON_EXTRACT(json_data, CONCAT('$.\"', :key3, '\"')) = :value3", nativeQuery = true)
    List<ExcelData> findByJsonKeyAndValueMysql3(@Param("key") String key, @Param("value") String value, @Param("key2") String key2, @Param("value2") String value2, @Param("key3") String key3, @Param("value3") String value3);
}

두 데이터베이스의 SQL 문이 조금 다르다.
PostgreSQL에서 조회할 메서드는 JSON 데이터의 특정 속성의 값을 추출하기 위해 -> 연산자를 사용했다.
MySQL에서 조회할 메서드는 -> 연산자 대신 JSON_EXTRACT라는 JSON 형식의 데이터에서 특정 속성의 값을 추출하는 함수를 사용했다.
- JSON_EXTRACT는 MySQL에서만 지원하는 함수이다.

데이터베이스에 저장할 엑셀 데이터

5만 개의 Row로 이루어진 데이터이다. 문자열 4, 5, 6에는 중복이 가능한 값(과일명)이 들어가 있으며
위의 엔티티에서 보았듯이 데이터베이스 테이블의 컬럼은 id, name(이름), phone(전화번호) json_data 총 네 개이다.
json_data에는 문자열1 ~ 10까지 10 개의 컬럼이 모두 들어간다.

데이터 Insert 테스트

MySQL

19.601초가 나온다.

데이터베이스에 정상적으로 id, name(이름), phone(전화번호) json_data(문자열1-10)이 저장되었다.

PostgreSQL

32.569초가 나온다.

데이터베이스에 정상적으로 id, name(이름), phone(전화번호) json_data(문자열1-10)이 저장되었다.

데이터 조회 테스트

MySQL

MySQL은 위에서 언급했듯이 JSON 데이터의 키 전체를 인덱싱하는 기능은 지원하지 않고 특정 키만 별도로 인덱싱할 수 있다.
이 테스트의 전제 조건은 JSON 데이터 내 키 값 중 어떤 값을 가져올지 모른다는 것이므로 MySQL은 따로 인덱싱을 걸지 않겠다.

@Test
public void findJsonKeyMysqlSpeed() {
    //given
    String key = "문자열4";
    String value = "토마토";
    String key2 = "문자열5";
    String value2 = "참외";
    String key3 = "문자열6";
    String value3 = "사과";

    //when
    long startTime = System.nanoTime(); // 시간 측정 시작
    List<ExcelData> excelDataList = excelDataRepository.findByJsonKeyAndValueMysql(key, value); // 단일키 조회

    long endTime = System.nanoTime(); // 시간 측정 종료
    long millis = TimeUnit.NANOSECONDS.toMillis(endTime - startTime); // 나노초 -> 밀리초 변환

    //then
    System.out.println("total time : " + millis);
}

문자열 4가 토마토인 값을 찾는다.

결과

Hibernate: SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ?
total time : 184ms

// 복합키 2 개 조회
List<ExcelData> excelDataList = excelDataRepository.findByJsonKeyAndValueMysql2(key, value, key2, value2);

문자열 4가 토마토이면서 문자열 5가 참외인 값을 찾는다.

결과

Hibernate: SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ? OR JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ?
total time : 253ms

// 복합키 3 개 조회
List<ExcelData> excelDataList = excelDataRepository.findByJsonKeyAndValueMysql3(key, value, key2, value2, key3, value3);

단일키로 문자열 4가 토마토이면서 문자열 5가 참외이면서 문자열 6이 사과인 값을 찾는다.

결과

Hibernate: SELECT * FROM excel_data WHERE JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ? OR JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ? OR JSON_EXTRACT(json_data, CONCAT('$."', ?, '"')) = ?
total time : 355ms

PostgreSQL

Postgres의 경우 jsonb 타입으로 저장했기 때문에 b-tree 인덱싱이 자동으로 적용된 상태이다.

@Test
public void findJsonKeyPostgresSpeed() {
    //given
    String key = "문자열4";
    String value = "토마토";
    String key2 = "문자열5";
    String value2 = "참외";
    String key3 = "문자열6";
    String value3 = "사과";

    //when
    long startTime = System.nanoTime(); // 시간 측정 시작
    List<ExcelData> excelDataList = excelDataRepository.findByJsonKeyAndValue(key, value); // 단일키 조회

    long endTime = System.nanoTime(); // 시간 측정 종료
    long millis = TimeUnit.NANOSECONDS.toMillis(endTime - startTime); // 나노초 -> 밀리초 변환

    //then
    System.out.println("total time : " + millis);
}

방식은 MySQL과 같고 메서드 명만 다르다.

단일키 결과

Hibernate: SELECT * FROM excel_data WHERE json_data ->> ? = ?
total time : 34ms

복합키 2 개 조회 결과

Hibernate: SELECT * FROM excel_data WHERE json_data ->> ? = ? OR json_data ->> ? = ?
total time : 42ms

복합키 3 개 조회 결과

Hibernate: SELECT * FROM excel_data WHERE json_data ->> ? = ? OR json_data ->> ? = ? OR json_data ->> ? = ?
total time : 37ms

결론

5만 개의 더미 데이터로 INSERT & 조회 테스트를 진행했다.
INSERT의 경우 MySQL이 PostgreSQL에 비해 약 1.7배 빨랐다.
데이터 조회의 경우 MySQL은 인덱싱을 걸지 않아 정상적인 비교 테스트라고 할 수 없지만 압도적으로 PostgreSQL이 빠름을 알 수 있다.
전체 키가 인덱싱 되는 PostgreSQL은 JSON 데이터를 다루는 데에 있어서는 MySQL을 압도한다고 볼 수 있다.
그러나, 5만 건의 데이터에서 양쪽 모두 ms 단위로 계산했다는 점을 감안하면 MySQL 역시 훌륭한 데이터베이스라고 할 수 있겠다.
- 어차피 둘 다 1초도 걸리지 않는다.
- 오히려 안정적이며 사용성이 쉽고 INSERT 부분에서 좋은 결과를 보인 MySQL 쪽이 더 매력적으로 느껴진다.

참고

뤼튼

728x90

'넓고 얕은 데이터베이스 지식' 카테고리의 다른 글

MySQL Replication : DB 서버 이중화(master - slave)하기 (1)	2024.01.10
PostgreSQL의 JSON 데이터 저장 타입 JSON vs JSONB 비교하기 (0)	2023.08.22
EAV 모델에 대해 간단히 알아보자 (0)	2023.07.23
Spring Boot에서 H2 DB 3 가지 모드로 사용하는 방법 정리 (0)	2023.06.20
커넥션 풀(Connection Pool) 1분 요약 (0)	2023.03.06

ENTP의 요약형 개발 블로그

MySQL VS PostgreSQL JSON 데이터 INSERT & 조회 성능 및 장단점 비교하기

MySQL의 주요 특징

PostgreSQL의 주요 특징

알아둬야 할 사항

테스트 조건

MySQL 테스트 시 ExcelData(Entity)

PostgreSQL 테스트 시 ExcelData

공통 ExcelDataRepository

데이터베이스에 저장할 엑셀 데이터

데이터 Insert 테스트

MySQL

PostgreSQL

데이터 조회 테스트

MySQL

PostgreSQL

결론

'넓고 얕은 데이터베이스 지식' 카테고리의 다른 글

티스토리툴바

MySQL VS PostgreSQL JSON 데이터 INSERT & 조회 성능 및 장단점 비교하기

MySQL의 주요 특징

PostgreSQL의 주요 특징

알아둬야 할 사항

테스트 조건

MySQL 테스트 시 ExcelData(Entity)

PostgreSQL 테스트 시 ExcelData

공통 ExcelDataRepository

데이터베이스에 저장할 엑셀 데이터

데이터 Insert 테스트

MySQL

PostgreSQL

데이터 조회 테스트

MySQL

PostgreSQL

결론

'넓고 얕은 데이터베이스 지식' 카테고리의 다른 글

관련글

티스토리툴바