明滅するプログラマの思索

WEBエンジニアとして勤務している一介の男が、日々気づいたことをまとめるブログです

MySQL の JSON 型カラムにインデックスを貼る方法

MySQL は5.6から JSONのデータ型を持てるようになりました。
JSON型カラムへのスキャンは TEXT型カラムと比べはるかに高速ですが、インデックスを利用すれば更なる速度向上が見込めます。
しかし、JSON型カラムに直接インデックスを貼ることはできません。
ここでは MySQL 5.7 より実装された Generated Column を用いて、JSON型カラムへのインデックスを実現します。

Generated Column とは

Generated Column は、レコードへの追加・更新をトリガとして、そのレコードの値に関数などを実行し、その結果を格納することができるカラムです。
このカラムはSTOREDを指定することで実体値として持つこともできますし、VIRTUALを指定することで実体値を持たずに必要なときに計算して獲得することもできます。

実際の使い方

例として以下のようなテーブルを作成します。

CREATE TABLE item (
  id int primary key not null auto_increment,
  data json
);

INSERT INTO item SET data = '{"name":"Tシャツ","price":"980"}';
INSERT INTO item SET data = '{"name":"Yシャツ","price":"1280"}';
INSERT INTO item SET data = '{"name":"ジーンズ","price":"3980"}';
INSERT INTO item SET data = '{"name":"セーター","price":"4980"}';
INSERT INTO item SET data = '{"name":"ジャケット","price":"22980"}';
INSERT INTO item SET data = '{"name":"コート","price":"49980"}';

結果は以下のようになります。

SELECT * FROM item;
+----+-----------------------------------------------+
| id | data                                          |
+----+-----------------------------------------------+
|  1 | {"name": "Tシャツ", "price": "980"}           |
|  2 | {"name": "Yシャツ", "price": "1280"}          |
|  3 | {"name": "ジーンズ", "price": "3980"}         |
|  4 | {"name": "セーター", "price": "4980"}         |
|  5 | {"name": "ジャケット", "price": "22980"}      |
|  6 | {"name": "コート", "price": "49980"}          |
+----+-----------------------------------------------+
6 rows in set (0.00 sec)

さて、price で検索する場合は以下ですね。

SELECT * FROM item WHERE JSON_EXTRACT(data, '$.price') = '980';
+----+----------------------------------------+
| id | data                                   |
+----+----------------------------------------+
|  1 | {"name": "Tシャツ", "price": "980"}    |
+----+----------------------------------------+
1 row in set (0.00 sec)

以上/以下で絞り込みたければ、JSON_UNQUOTEが使えます。

SELECT * FROM item WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.price')) >= 3980;
+----+-----------------------------------------------+
| id | data                                          |
+----+-----------------------------------------------+
|  3 | {"name": "ジーンズ", "price": "3980"}         |
|  4 | {"name": "セーター", "price": "4980"}         |
|  5 | {"name": "ジャケット", "price": "22980"}      |
|  6 | {"name": "コート", "price": "49980"}          |
+----+-----------------------------------------------+
4 rows in set (0.00 sec)

Generated Column を作り、そこにインデックスを貼って、インデックスで検索できるようにしてみます。

ALTER TABLE item ADD price int AS (json_extract(data, '$.price')) VIRTUAL;
CREATE INDEX price_index ON item (price);

Generated Column にはVIRTUAL を指定しましたが、問題なくインデックスを貼ることができました。
explainしてみます。

explain select * from item where price > 980;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | item  | NULL       | range | price_index   | price_index | 5       | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

作ったインデックスが使われていることがわかります。

JSON 型カラムへの全文検索

Mroongaなどの全文検索エンジンを使い、Generated Column に FULL TEXT インデックスを貼ることができます。
ただしその場合、Generated Column にはVIRTUALは指定できません。必ずSTOREDにする必要があります。

ALTER TABLE item ADD name varchar(32) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) STORED;
CREATE FULLTEXT INDEX name_index ON item (name) comment 'parser = "TokenMecab"';

今回はトークナイザにmecabを使用しています。

select * from item where match name against("シャツ" in boolean mode);
explain select * from item where match name against("シャツ" in boolean mode);
+----+-------------+-------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                                                                                       |
+----+-------------+-------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | item  | NULL       | fulltext | name_index    | name_index | 0       | const |    1 |   100.00 | Using where with pushed condition (match `test`.`item`.`name` against ('シャツ' in boolean mode))    |
+----+-------------+-------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

作ったインデックスが使われていることがわかります。