mysql 5.7.14부터 지원되는 json 필드 인덱스 지원

mysql의 innodb에서는 json과 같은 특정 컬럼으로부터 2차 인덱스를 지원한다. 

5.7.14부터이니.. 참고.

https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html#json-column-indirect-index 예를 든다.

테스트한 버전은 mysql version은 5.7.17이다.

MySQL [mysql5]> SHOW VARIABLES LIKE "%version%";

+-------------------------+------------------------------+

| Variable_name           | Value                        |

+-------------------------+------------------------------+

| innodb_version          | 5.7.17                       |

| protocol_version        | 10                           |

| slave_type_conversions  |                              |

| tls_version             | TLSv1,TLSv1.1                |

| version                 | 5.7.17-log                   |

| version_comment         | MySQL Community Server (GPL) |

| version_compile_machine | x86_64                       |

| version_compile_os      | linux-glibc2.5               |

+-------------------------+------------------------------+

8 rows in set (0.01 sec)

테이블을 생성할 때 json컬럼의 특정 데이터를 인덱싱을 걸 수 있다. 

MySQL [mysql5]>  CREATE TABLE jemp (c JSON, g INT GENERATED ALWAYS AS (c->"$.id"), INDEX i (g));

Query OK, 0 rows affected (0.05 sec)

데이터를 추가한다 .

MySQL [mysql5]>  INSERT INTO jemp (c) VALUES

    ->  ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),

    ->  ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

데이터를 검색할 수 있다.

(-->은 5.7.13부터 사용할 수 있는 키워드이다)

MySQL [mysql5]>   SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;

+--------+

| name   |

+--------+

| Barney |

| Betty  |

+--------+

2 rows in set (0.01 sec)

explain으로 확인하면 인덱스가 걸려있음을 확인할 수 있다. 

MySQL [mysql5]>   EXPLAIN SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | jemp  | NULL       | range | i             | i    | 5       | NULL |    2 |   100.00 | Using where |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

warning이 하나 있는데.. 부가 설명이 잘 되어 있다. 

MySQL [mysql5]> SHOW WARNINGS

    -> ;

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                       |

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select json_unquote(json_extract(`mysql5`.`jemp`.`c`,'$.name')) AS `name` from `mysql5`.`jemp` where (`mysql5`.`jemp`.`g` > 2) |

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)