MySQL JSON vs TEXT

최종윤·2024년 3월 9일

프로젝트

목록 보기

13/13

MySQL JSON type

https://planetscale.com/blog/the-mysql-json-data-type

MySQL JSON 5.7 => 8.0

https://dev.mysql.com/blog-archive/partial-update-of-json-values/?ref=hackernoon.com

MySQL
CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT,
               json_col JSON,
               name VARCHAR(100) AS (json_col->>'$.name'),
               age INT AS (json_col->'$.age'));

INSERT INTO t(json_col) VALUES
(JSON_OBJECT('name', 'Joe', 'age', 24,
             'data', REPEAT('x', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Sue', 'age', 32,
             'data', REPEAT('y', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Pete', 'age', 40,
             'data', REPEAT('z', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Jenny', 'age', 27,
             'data', REPEAT('w', 10 * 1000 * 1000)));

INSERT INTO t(json_col) SELECT json_col FROM t;
INSERT INTO t(json_col) SELECT json_col FROM t;

테이블에 1개에 10MB document를 16개 저장한 상태에서 성능 측정

MySQL5.7 (13.56sec)

mysql> update t set json_col = json_set(json_col, '$.age', age + 1);
Query OK, 16 rows affected (13.56 sec)
Rows matched: 16  Changed: 16  Warnings: 0

MySQL8.0 (2.94sec)

mysql> update t set json_col = json_set(json_col, '$.age', age + 1);
Query OK, 16 rows affected (2.94 sec)
Rows matched: 16  Changed: 16  Warnings: 0

5.7 에서는 특정 속성을 업데이트 하는게 느렸습니다. 5.7에서는 일부만 업데이터 하려해도 full new JSON document를 저장해야 했기때문입니다.

8.0 오면서 빨라졌습니다. JSON document에서 조각(patches)을 가진 저장 엔진을 제공해서 일부만 업데이트하는데 사용할 수 있습니다.

option을 enabled하면 이 조각(patches)는 replication slave 에 적재된 binary log의 양을 줄이는 row-based replication 에 의해 사용될 수 있습니다.
이는 update-intensive workloads 에서 disk I/O, network I/O 를
상당히 줄일 수 있습니다.

MySQL 8.0에서 이진수 로깅 활성화가 비 복제 환경에서, 기본 값으로 설정되는데 이진수 로그가 full JSON documents 대신 조각(patches)를 포함하게한다

8.0 option enabled (0.23s)

mysql> SET binlog_row_value_options = PARTIAL_JSON, binlog_row_image = MINIMAL;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> update t set json_col = json_set(json_col, '$.age', age + 1);
Query OK, 16 rows affected (0.23 sec)
Rows matched: 16  Changed: 16  Warnings: 0

JSON Documents로 데이터를 저장할 때 이점

Log output written by an application or a server 서버가 작성한 로그 출력을 저장할 때
A Rest API response you want to store REST API 응답을 저장할 때
Storing configuration data 설정 데이터를 저장할 때
A set of entities with variable attributes 변수 속성을 가진 개체 집합을 저장할 때
내가 고려하고 있는 상황은 4번 변수 속성을 가진 개체 집합이다.

MySQL JSON 타입의 단점

MySQL JSON 타입은 longtext 보다 4 ~ 10 Byte를 더 사용한다. 조회를 빠르게 하기 위해 이진수 인코딩, 메타데이터,

딕셔너리들이 있기 때문이다. 저장공간 효율적으로 데이터를 저장해야 한다면 JSON 타입 보다 VARCHAR, INT 같은 기본 타입을 사용하는 것이 좋다.

MySQL JSON 은 binary 형식과 유사하게 바로 Index 적용될 수 없습니다. 간접적으로 Indexing 할 수 있습니다.

그래서 더 많은 버퍼 공간을 사용하고, 더 큰 결과 집합을 응답해서 더 많은 데이터 교환을 유도합니다.

MySQL 에서 1개의 JSON document는 약 1GB까지 이론적으로 될 수 있지만, 몇 MB 사이즈로 유지하는 것이 추천됩니다. PlanetScale(MySQL platform) 에서는 67 MB까지 지원합니다.

TEXT대신 JSON 타입을 써야하는 이유

https://hackernoon.com/unlocking-the-power-of-json-in-mysql-tips-and-tricks

JSON 타입이 JSON 데이터에 대한 validation 기능을 제공하기 때문입니다. DB 에 저장하기 전에 각 JSON 값이 유효한지 검사할 수 있습니다.
JSON 타입은 JSON 데이터 저장에 최적화됐기 때문입니다.DB 에서 더 빠르게 JSON documents를 조회하기 위해 최적화된 이진수 형식으로 각 요소를 저장합니다.

3.JSON 타입은 JSON 값을 사용해 Index 생성이 가능합니다. JSON 데이터를 쿼리할 떄 큰 장점입니다.