19) JSON 타입 활용

JSON 데이터 타입

• JSON 형식의 데이터를 손쉽게 저장 및 조회 , 관리 가능
• 빌트인 함수들을 사용해서 JSON 데이터 조작 가능
• 저장된 JSON 데이터의 일부만 업데이트 가능( 부분 업데이트도 가능)
• 저장된 JSON 데이터의 특정 키에 대해서 인덱스 생성 가능

 

 

default 가 null이 아니면 instant 알고리즘으로 DDL이 불가능하다.

테이블 전체를 락을 잡게되는 algorithm = Copy를 사용해야한다. 이때는 DDL작업중에는 DML이 차단된다.

 

 

JSON 데이터 저장

주의할점은 객체 데이터 입력시 key에 해당하는 값은 반드시 쌍따옴표로 감싸줘야 정상적으로 값이 들어간다.

insert할시에 json 형식인지 validation이 존재한다.

 

저장 구조

• 최적화된 바이너리 포맷으로 저장된다.
• 중복된 키 값은 마지막 순서의 데이터로 저장된다.
  • {"a": 123, "a": 456} ==> {"a": 456}
• Json 데이터 내 키들을 정렬해서 저장한다.
  • {"c" : 1, "a": 1} ==> {"a" : 1, "c": 1}
• 키는 json 데이터마다 중복해서 저장되므로, 적당한 길이로 사용하는 것을 권고한다.

 

JSON 데이터 조회

JSON Path

• Json데이터의 요소를 쿼리하는 표준화된 방법
• 대표적인 연산자
  • $ = json데이터 계층의 루트(최상위)를 의미한다.
  • . = 객체의 하위 요소들을 참조할 때 사용한다.
  • []  = 배열 내의 요소에 접근할 때 사용한다.

 

inline path operator의 경우에는 추출한 값에서 작은 따옴표와 이스케이핑 처리가 제거된 문자열을 결과로 반환한다.

json 데이터가 배열인 경우이다.

josn 데이터가 객체인 경우이다.

 

JSON_COTAINS(target_json, candidate_json[, path])

• target_json 에 candidate_json 가 포함돼있으면 True(1) , 아니면 False(0) 반환한다.
• path 가 주어진 경우에는 지정된 path에 위치한 값에 대해서만 확인한다.

 

 

 

JSON 데이터 변경

• json데이터의 특정 키값만 변경시 변경된 키 값에 대해서만 데이터를 업데이트하는 "부분 업데이트" 최적화를 제공한다.
• 불필요하게 전체 데이터를 다시 쓰지 않으므로 쿼리 성능이 향상된다.
• JSON_INSERT , JSON_REPLACE , JSON_SET , JSON_REMOVE 등등 존재한다.

 

JSON 부분 업데이트

• 부분 업데이트가 수행되는 조건
  • JSON_SET() , JSON_REPLACE() , JSON_REMOVE 함수만 가능하다.
  • 함수의 인자로 주어진 컬럼과 변경 대상 컬럼이 일치해야한다.
  • 값 변경시 기존 값을 새로운 값으로 대체 되는 형태여야 한다. 새로운 키-값이 추가되는 변경 등은 부분 업데이트 처리 불가능하다.
  • 대체되는 새로운 값은 기존에 저장된 값보다 저장된느 크기가 작거나 같아야 한다.

 

JSON_STORAGE_SIZE는 json data를 저자하기 위해 잡은 용량의 바이트 값이다. free는 여유분이다.

update를 통해 value의 길이를 줄였더니 5바이트 free space가 생성되었다.

 

부분업데이트가 훨씬 빠르다. 저장된 json data가 크면 클수록 차이가난다. 최대한 부분업데이트가 일어나는 조건대로 update를 진행하는게 좋겠다.

 

• 바이너리 로그와 부분 업데이트
  • MySQL 8.0에서 바이너리 로그 기본 설정
    • log_bin=ON
    • binlog_format=ROW
    • binlog_row_image=full
    • binlog_row_value_options=''  (empty string)
  • 기본으로 설정된 값은 부분 업데이트 성능을 저하 시킬 수 있다.

 

MINIMAL: Only primary key columns and columns that changed in a row are logged. This reduces the amount of data in the binary log, minimizing storage requirements and improving replication efficiency.

However, MINIMAL might not be appropriate if you have replication setups that require the full row data for each change or if you are using features like CDC (Change Data Capture) tools that rely on all columns being present in each row event.

Partial JSON Logging: When set to PARTIAL_JSON, MySQL will log only the modified parts of a JSON document, rather than logging the entire JSON object each time it is updated. This can lead to significant savings in binary log size, especially for large JSON documents where only a small part of the data changes frequently.

However, keep in mind:

• Compatibility: If you use replication features or tools that require full JSON objects for each change (e.g., certain Change Data Capture tools), this setting may not be suitable.

 

• STATEMENT: Logs only the SQL statements. It requires less space in the binary log and can be more efficient in terms of storage and performance because it logs only the statement, not each individual row change.
• ROW: Logs each row change rather than the SQL statement. This is more reliable for ensuring data consistency in replication but generates larger binary logs.
• MIXED: Automatically switches between STATEMENT and ROW formats based on the type of SQL operation to combine efficiency and reliability.

However, statement-based replication has limitations:

• It can lead to data inconsistency in cases where statements are non-deterministic or rely on server-specific values (e.g., CURRENT_TIMESTAMP).
• Some SQL operations, such as those involving UUID() or functions with random elements, might not replicate identically.

 

 

 

JSON 데이터 인덱싱

• 특정 키 값에 대해 인덱싱 가능
• 함수 기반 인덱스로 인덱스 생성한다.
• 문자열 값을 인덱싱하는 경우 따옴표 및 콜레이션 주의

 

CAST를 사용하지 않으면 json data를 인덱싱할 수 없다.

inline path operator 로 반환되는 결과 타입이 LongText 타입이기 때문이다.

함수 기반 인덱스에서는 표현식에서 반환되는 결과의 타입과 동일한 타입으로 테이블에 내부적으로 가상 컬럼을 생성하고 이 가상컬럼에 인덱스를 생성하는 형태로 동작한다.

이때 컬럼이 LongText 인 경우 prefix 길이를 설정하지않으면 인덱스로 생성할 수 없고 ,
또 컬럼에 prefix 길이를 설정하는 형태는 함수 기반 인덱스에서 사용이 불가능하다.
(Since expressions can return varying types and lengths depending on the data, MySQL cannot determine a consistent prefix length to use across different computed results.)

 

 

 

배열 인덱스 사용 시 주의사항

• MEMBER OF , JSON_CONTAINS , JSON_VOERLAPS 함수만 배열 인덱스 사용이 가능하다.
• 아직 기능이 성숙하지 못해 버그가 존재하는 상황이므로 유의해서 사용한다.
  • 배열 인덱스를 사용하는 쿼리에서 count (*) 가 잘못된 결과 변수 반환 -> 8.0.29에 fix
  • 일반 컬럼과 배열 값이 같이 인덱싱된 복합 인덱스에서 쿼리 성능 이슈 -> 아직 진행중
• 기타 여러 제한 사항
  • 온라인으로 인덱스 생성 불가
  • 커버링 인덱스 & 범위 스캔 불가
  • 빈 배열 식별 불가 등등

 

문자열 값 인덱싱시 주의사항

inline operator의 경우 따옴표가 제거된 상태로 결과 값을 반환하게 된다. 

하지만 위에서 사용되는 column path operator는 쌍따옴표를 제거하지 않으므로 아래처럼 쌍따옴표가 query에 있어야 제대로 조회할 수 있다.

 

 

TEXT 타입 vs JSON 타입

text

• 입력된 문자열 그대로 저장
• 데이터 조회시 저장된 데이터를 변환하지 않고 전송
• 항상 전체 데이터 업데이트
• 주로 사용할 때
  • 데이터를 저장후 전체 데이터 조회하는 패턴으로 주로 사용된다.
  • JSON 형식이 아닌 데이터도 저장이 될 수 있는 경우

json

• 최적화된 바이너리 포맷으로 저장 & 유효성 검사
• 데이터 조회시 바이너리 json 데이터를 문자열 형식으로 변환 후 전송한다.
• 부분 업데이트가 가능
• 주로 사용할 때
  • JSON 데이터의 특정 키 값만 주로 조회하고 변경할때 (부분 업데이트가 가능하니까)

 

정규화된 컬럼 vs JSON 컬럼

• 정적 스키마 vs 유연한 스키마
• 데이터의 일관성 및 유지보수 용이성 vs 개발 편의성
• 쿼리 최적화와 인덱싱의 편리성 vs 복잡한 형태의 쿼리와 인덱싱 방식

 

JSON 컬럼을 가진 테이블 데이터 조회시 주의사항

JSON 컬럼에 저장되는 데이터 사이즈가 클 수록 조회 성능은 저하된다. 필요한 경우에만 조회하자

orm의 경우 쓸데없이 가져오는 경우가 많으므로 주의해야한다.