mysql json 색인 index 만들기 (ver 8 기준)

아래 링크를 참고하여 mysql json 을 다루는 방법을 유용하게 잘 배웠으나, 링크 페이지의 내용 중 색인(index)관련해서는 특정 사용방법에서만 작동하는 문제가 발생함. (이 문서는 의도치않게 index가 작동하지 않는 문제만 다루고 있습니다.)

• Mysql JSON - SELECT, INSERT, UPDATE, 색인 (웹아카이브 링크1, 웹아카이브 링크2)

- age(int)는 샘플코드 올바르게 잘 작동함.
- name(string)은 where절 형식에 따라 index가 작동하지 않을 때가 있음 (아래 코드 참고)

-- 신규로 생성한 컬럼을 where절에서 사용시 index 잘 작동함.
EXPLAIN select * from users where name_virtual = 'yundream';

-- 기존 방식대로 where절에서 사용시 index 작동하지 않음.
-- (기존 작성된 쿼리에서는 index가 작동하지 않음을 의미함.)
EXPLAIN select * from users where info->'$.name' = 'yundream';

핵심 : index로 만들 virtual 컬럼을 아래와 같이 생성한 후 index를 생성한다.(기존 컬럼과 인덱스를 삭제하고 다시 시도해 보세요)

-- XXX 링크사이트의 예시 코드
ALTER TABLE users ADD COLUMN name_virtual VARCHAR(64) GENERATED ALWAYS AS (info->'$.name') NOT NULL;
CREATE INDEX `name_idx` ON users(name_virtual);

-- OOO 올바른 예시 코드
ALTER TABLE users ADD COLUMN name_virtual VARCHAR(64) GENERATED ALWAYS AS (info->>'$.name') VIRTUAL;
CREATE INDEX `name_idx` ON users(name_virtual);

* 참고사항
- info->'$.name' 는 json_extract(info,'$.name')
- info->>'$.name' 는 json_unquote(json_extract(info,'$.name'))

https://stackoverflow.com/questions/36933742/extract-value-without-quotation-mark-from-mysql-json-data-type

 

결론 : 기존 쿼리에서도 생성한 index가 작동한다.

-- 아래의 모든 경우에서 생성한 index가 잘 작동함.
explain select * from users where info->'$.name' = 'yundream'; 
explain select * from users where info->>'$.name' = 'yundream'; 
explain select * from users where json_extract(data, '$.name') = 'yundream';
explain select * from users where name_vartual = 'yundream';