MySQL의 JSON 타입 사용 시 주의사항

1. 들어가며

 

보통 JSON 타입하면 NoSQL, 특히 MongoDB를 많이 떠올릴 것 같습니다. 하지만 MySQL에서도 Json 타입을 제공하고 있는데요. JSON 형식의 column을 만들고, 해당 데이터를 일부만 업데이트 하거나, 심지어 json의 특정 키에 대해서 인덱스까지 만들 수 있습니다.

기본적인 JSON 데이터 형식이나 저장 방법 등은 생략하고 JSON 타입을 사용할 때 주의해야될 점을 위주로 살펴보겠습니다.

 

2. JSON 데이터 부분 업데이트

 

JSON 데이터를 변경할 때 전체 데이터를 바꿀 일은 흔치 않습니다. 보통 json에는 많은 key, value가 혼재되어 있고 그 중 특정 값만 수정될 일이 많을 것입니다. 그래서 mySQL에서는 특정 키 값만 변경 시 변경된 키 값에 대해서만 데이터를 업데이트하는 "부분 업데이트" 최적화를 제공합니다.

• JSON_REPLACE(), JSON_SET(), JSON_REMOVE()만 사용 가능
  • 자세한 함수 정보는 다음 링크에서 확인 가능: https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

이를 통해 불필요하게 전체 데이터를 다시 쓰지 않으므로 쿼리 성능이 향상할 수 있습니다. 경우에 따라 다르겠지만 2배 가량의 쿼리 속도 차이가 날 수 있습니다.

 

-- 일반 업데이트 (Esther → Esther Baek)
UPDATE tb_json SET fd = JSON_SET(fd, '$.name', "Esther Baek");
Query OK, 20 rows affected (2.73 sec)
Rows matched: 20  Changed: 20  Warnings: 0
-- 부분 업데이트 (Esther → Emma)
UPDATE tb_json SET fd = JSON_SET(fd, '$.name', "Emma");
Query OK, 20 rows affected (1.24 sec)
Rows matched: 20  Changed: 20  Warnings: 0

다만 모든 위 함수를 사용한다고 무조건 부분 업데이트가 일어나는 것은 아니니 주의해야 합니다.

• 함수의 인자로 주어진 컬럼과 변경 대상 컬럼이 일치해야 합니다.
• 값 변경 시 기존 값을 새로운 값으로 "대체"되는 형태여야 합니다. 새로운 키-값이 추가되는 변경 등은 부분 업데이트 처리가 불가합니다.
• 대체되는 새로운 값은 기존에 저장된 값보다 저장되는 크기가 작거나 같아야 합니다.

마지막 조건이 쉽지 않은데 VARCHAR 때 봤던 조건과 유사한 것을 알 수 있습니다. JSON도 마찬가지로 공간 할당을 위해 큰 경우 새롭게 쓰여지는 수 밖에 없기 때문입니다.

 

다만 부분 데이터 업데이트 시 binary log에 변경된 것만 기록할지, 아니면 전체에 대해 할 지 설정이 필요한데, 기본 설정은 성능을 저하시킬 수 있다는 점을 유념해야 합니다.

 

MySQL 8.0에서 바이너리 로그 기본 설정은 다음과 같습니다.

• log_bin=ON
• binlog_format=ROW
• binlog_row_image=full
• binlog_row_value_options='' (empty string)

기본으로 설정된 값은 보시다시피 전체에 대해 기록하므로 부분 업데이트 성능을 저하시킬 수 있습니다.

위 설정 중

• binlog_row_image=MINIMAL
• binlog_row_value_options= PARTIAL_JSON

 

으로 바꾸면 위의 부분 업데이트 쿼리와 동일함에도 update json column 쿼리가 속도 차이가 4배 가량 나는 것을 알 수 있습니다.

UPDATE tb_json SET fd = JSON_SET(fd, '$.name', "Emma");
Query OK, 20 rows affected (0.30 sec)
Rows matched: 20  Changed: 20  Warnings: 0

 

binlog_format의 설정을 바꾸는 것도 방법이 될 수 있습니다. ROW가 아닌 STATEMENT로 바꿀 경우 binary log의 쿼리가 변경한 데이터를 기록하는 게 아니라 실행된 쿼리를 기록하기 때문에 기록하는 양이 훨씬 적습니다. 그래서 더 빨라질 수 있습니다.

 

물론 json 데이터의 최적화를 위해 설정을 바꾸는 것이 맞는가..?에 대해서는 논의해봐야 될 일인 것 같습니다.

https://aws.amazon.com/ko/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-2-parameters-related-to-replication/

Best practices for configuring parameters for Amazon RDS for MySQL, part 2: Parameters related to replication | Amazon Web Servi

우선 아마존의 글을 읽고 각 옵션의 특성을 읽히는 걸 추천드립니다. 

 

그 외에 binlog_format의 경우 Statement 사용시 복제 구성에서 now(), sysdate() 같은 함수를 쓰는 쿼리가 있을 경우 Slave 에서 반영될때 시간차이가 발생할 수 있다고 합니다.

Master 에서 10:00:00 에 쿼리가 실행된 쿼리가 Slave에 10:00:04초에 반영되면 4초의 오차 발생하는 식입니다.

Row 의 경우 기록되는 로그사이즈가 큽니다.. 이로 인한 디스크 공간, I/O, 네트워크 부하 등등이 문제가 발생할 수 있습니다.

Mixed 의 경우 위의 문제를 해결하기 위해 둘을 혼합한 방식으로 now(), sysdate() 같은 함수는 row 형태로 저장된다고 합니다.

 

3. JSON 데이터 인덱싱

• JSON의 특정 키 값에 대해 인덱싱이 가능합니다. 
• json column의 특정 키 값을 인덱싱해야 하므로 컬럼명 지정 일반 인덱스가 아닌 함수 기반 인덱스로 인덱스 생성할 수 있습니다.
• 문자열 값을 인덱싱할 경우 ""와 콜레이션을 주의해야 하는데 이는 추후 더 자세히 살펴보겠습니다.

ALTER TABLE tb_json_index
    ADD KEY ix_string ( (CAST(fd->>'$.name' AS CHAR(30))) ),
    ADD KEY ix_number ( (CAST(fd->>'$.account_no' AS UNSIGNED)) ),
    ADD KEY ix_date   ( (CAST(fd->>'$.hire_date' AS DATE)) ),
    ADD KEY ix_array  ( (CAST(fd->>'$.dept_no' AS UNSIGNED ARRAY)) );

 

• 이 때 CAST 함수가 필수인데, 인라인 path operator로 반환되는 타입이 LONG TEXT이기 때문입니다.
• 저번 글에서 살펴본 것처럼, 함수 기반 인덱스에서는 표현식에서 반환되는 결과와 동일한 타입과 테이블에 내부적으로 가상column을 만들고 해당 가상 column에 대해 index를 만듭니다. 근데 long text는 column에 prefix를 만들어야하고, 이 경우 함수형 인덱스가 불가능해지기 때문입니다.
• 그래서 위와 같이 각 column 별로 타입이 다르게 설정되어있는 걸 알 수 있습니다.
• 함수 기반 인덱스이기 때문에, 쿼리에서도 동일한 표현식을 사용해야 인덱스를 탈 수 있습니다.

독특한 것은 배열 인덱스인데

SELECT * FROM tb_json_index WHERE 14 MEMBER OF (fd->> '$dept_no');

 

와 같이 MEMBER OF 함수를 사용해야 합니다. 위 쿼리는 dept_no에 14가 포함된 것을 가져오라는 것 뜻 입니다.

MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() 함수만 배열 인덱스 사용 가능

사실 json의 배열 인덱스는 아직 기능이 성숙하지 못해 버그가 존재하는 상황이므로 유의해서 사용하는 것이 좋습니다. 

 

배열 인덱스를 사용하는 쿼리에서 COUNT(*)가 잘못된 결과 건수 반환되는 큰 버그가 있었고 → 8.0.29에 Fix되었습니다.
    - https://bugs.mysql.com/bug.php?id=104898  

일반 컬럼과 배열 값이 같이 인덱싱된 복합 인덱스에서 쿼리 성능 이슈가 있고 현재 진행 중입니다.

- https://bugs.mysql.com/bug.php?id=104897

그 외에도 여러 제한 사항이 있어 온라인으로 인덱스 생성 불가, 커버링 인덱스 & 범위 스캔 불가, 빈 배열 식별 불가 등의 문제점이 있습니다.

 

앞서 살펴본 것처럼 문자열 인덱스를 사용할 때도 주의해야할 점이 있습니다.

 

1) 문자열 인덱스 생성

인덱스 생성에 있어서 ->> 를 사용하는 것이 좋습니다. ->를 붙여 만들었다면 ""를 붙여야 인덱싱 조건이 되기 때문에 다음과 같은 실수를 하기 쉽습니다.

ALTER TABLE tb_json_index ADD KEY ix_string ( (CAST(fd->'$.name' AS CHAR(30))) );


SELECT * FROM tb_json_index WHERE CAST(fd->'$.name' AS CHAR(30)) = 'Brynn'; -- 결과 없음
SELECT * FROM tb_json_index WHERE CAST(fd->'$.name' AS CHAR(30)) = "Brynn"; -- 1개 행 결과 반환

 

2) 문자열 인덱스 콜레이션

 

JSON 내 문자열 데이터 처리 시 utf8mb4_bin 콜레이션이 사용되고, CAST()에서는 문자열을 utfmb4_0900_ai_ci 콜레이션으로 반환하여 쿼리에서 인덱스 사용 여부에 따라 결과 데이터가 다를 수 있다는 점도 정말 주의해야 합니다.

 

4. TEXT 타입 vs JSON 타입

• JSON이 지원되기 전에는 TEXT를 많이 사용하기도 했다고 합니다. 실제로 TEXT column을 만들고 json 형식의 데이터를 저장하는 방식으로 쓰는 것을 본 적이 있습니다.
• 사실 저도 궁금했는데 JSON이라고 꼭 json을 사용해야 하는가? 위처럼, json 형식을 TEXT로 저장하는 것도 충분히 가능한 이야기입니다.
• json의 경우 최적화된 바이너리 포맷으로 저장하면서 유효성 검사도 할 수 있다는 장점이 있지만
• JSON은 항상 바이너리에서 문자열로 변환해야 된다는 단점도 같이 가지고 갑니다.
• 다만 부분 업데이트할 수 있다는 것은 꽤 큰 장점입니다. (여러 제약이 있지만)
• 참고로 TEXT 타입 컬럼도 JSON 함수를 사용하고 특정 키 값에 대한 인덱스를 생성할 수 있습니다.

JSON 컬럼도 데이터 사이즈가 클수록 조회 성능은 저하됩니다. 이는 TEXT나 BLOB과 마찬가지로 Off-page로 처리되기 때문입니다.

따라서 해당 데이터가 필요한 경우에만 프로젝션으로 명시해서 조회하는 것이 더 효율적입니다.