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)