MariaDB - JSON 데이터 포멧 사용

※ MariaDB만 해당. MySQL은 다름 

DROP TABLE TEST_JSON;
CREATE TABLE TEST_JSON(
	uid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	val json
);

INSERT INTO TEST_JSON(val) VALUES('123456789');
INSERT INTO TEST_JSON(val) VALUES('[]');
INSERT INTO TEST_JSON(val) VALUES('{}');
INSERT INTO TEST_JSON(val) VALUES(JSON_OBJECT("name","홍길동", "age",37, "addr","대전 서구"));

SELECT * FROM TEST_JSON

DESC TEST_JSON;

 

 

● 조회

SELECT JSON_VALUE(val, '$.name'), JSON_VALUE(val, '$.age') FROM TEST_JSON WHERE val LIKE '%홍길동%';
SELECT * FROM TEST_JSON WHERE JSON_VALUE(val, "$.name") LIKE '_길동'

 

 

● 업데이트

UPDATE TEST_JSON SET val = JSON_SET(val, '$.name', '고길동', '$.age', 27) WHERE uid = 4;
SELECT * FROM TEST_JSON;

 

 

● 실행계획(예측)

EXPLAIN SELECT * FROM TEST_JSON WHERE JSON_VALUE(val, "$.name") = '고길동';

>> ALL : FULL SCAN

>> 해당 컬럼 인덱스 생성

 

※ 실행 계획 속도 : system > const > eq_ref > ref > ref_or_null > index_mergy > unique_subquery > index_subquery > range > index > ALL

>> system, const 가 제일 빠름(unique index)

>> eq_ref(index)

>> ref_or_null(null 포함 index)

>> 이하 셀프 검색 

 

CREATE INDEX IDX_TEST_JSON ON TEST_JSON(val);
EXPLAIN SELECT * FROM TEST_JSON WHERE JSON_VALUE(val, "$.name") = '고길동';

>> 역시 풀스캔

>> 하지만

EXPLAIN SELECT * FROM TEST_JSON WHERE val LIKE '고길동%';

>> range 스캔 ㅋㅋㅋ

 

 

● JSON 항목 인덱스 정석은, 인덱스 컬럼을 따로 저장하여 인덱스 부여

ALTER TABLE TEST_JSON ADD name VARCHAR(100) AS (JSON_VALUE(val, '$.name')) AFTER val;
CREATE INDEX IDX_TEST_JSON_NAME ON TEST_JSON(name);
SELECT * FROM TEST_JSON;

 

 

● JSON 인덱스 항목 예측

EXPLAIN SELECT * FROM TEST_JSON WHERE name = '고길동';

 

● JSON 인덱스 항목 실측

SET profiling = 1;
SHOW PROFILES;

 

 

끗.