MySQL道普請便り

第202回MySQLでJSONを活用してみる[その3]

これまでMySQLのJSONの活用方法に関して、第197回 MySQLでJSONを活用してみる[その1]第199回 MySQLでJSONを活用してみる[その2]で簡単に紹介してきました。

今回はちょっとした応用として、JSONの操作を高速に扱えるようにインデックスを貼る方法を2つ紹介したいと思います。

検証環境

今回はDockerで建てたMySQLを使用します。以下のコマンドでdockerを建てて、ローカルからアクセスをします。

% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

アクセス方法は以下の通りになります。

% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307

執筆時点では、以下の通りMySQL 8.0.33を使用しております。

mysql>  select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.01 sec)

今回はtestデータベースとdataテーブルを作成します。

mysql> create database test;
mysql> use test
mysql> CREATE TABLE `json_data` (id SERIAL, doc json);

カラムには、SERIAL型のidとjson型のdocカラムを用意しています。

インデックスを貼ってみる

MySQLで検索を効率的に行うために、インデックスを貼ることが多いと思います。JSON型に関しても、何らかの形でインデックスを貼りたいと思うかもしれません。そんな時に使える方法は以下の2つです。

それぞれに関して紹介していきたいと思います。

Generated Columnを使用する

第150回 Generated Columnを利用してみるで紹介したGenerated Columnは、テーブル定義で宣言した式に従って値を自動で生成し、カラムとして扱えるようになるMySQL5.7から使える機能でした。

今回は以下のようなJSONを考えた場合に、JSONの中身のjson_idキーに対してインデックスを貼りたいとします。

{
        "json_id":  1
}

これを一旦、MySQLにINSERTしてみましょう。

mysql> INSERT INTO json_data(doc) VALUES ('{"json_id":  1}');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT  JSON_PRETTY(doc) FROM json_data;
+--------------------+
| JSON_PRETTY(doc)   |
+--------------------+
| {
  "json_id": 1
} |
+--------------------+
1 row in set (0.01 sec)

挿入できたことが確認できました。Generated Columnに使用できるのは、結果が一定になる計算式でしたが、JSONのオブジェクトの検索などはデータを変更しない限り一定になるのは、納得していただけると思います。

ということで、Generated Columnを定義して、そのカラムにインデックスを貼ってみましょう。

mysql> ALTER TABLE json_data ADD COLUMN json_id bigint GENERATED ALWAYS AS (JSON_EXTRACT(`doc`, '$.json_id'));

mysql> select * from json_data;
+----+----------------+---------+
| id | doc            | json_id |
+----+----------------+---------+
|  1 | {"json_id": 1} |       1 |
+----+----------------+---------+
1 row in set (0.01 sec)

このように、json_idのvalueがjson_idのカラムに保存されていることがわかります。

これに対して検索を行った場合のexplainを見てみましょう。

mysql> explain SELECT * FROM json_data where json_id = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | json_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

現在はtype ALLになっているので、現状ではindexが効いていないと思います。では、Generate Columnにindexを貼ってみましょう。

mysql> ALTER TABLE json_data ADD INDEX index_json_id(json_id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

explainを取ってみます。

mysql> explain SELECT * FROM json_data where json_id = 1;
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | json_data | NULL       | ref  | index_json_id | index_json_id | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

explainの結果からkeyなどを見てもらうとわかりますが、インデックスが使われていることがわかると思います。JSON型の扱いに慣れていないとしても、普段のSQLと同じ扱いができるという利点があります。

ただし、Generated Columnで生成したカラムに対して更新をかけようとするとエラーになるため、誤ってGenerated Columnに対してupdateをかけてもJSONの更新をしてくれないところには注意が必要です。

関数インデックスを使用する

式インデックスはMySQL 8から導入された機能で、関数の結果をインデックスとして扱うことができる機能になります。

Generated Columnと何が違うのかな?と思う方もいるかも知れません。テーブルに存在するカラムで更新可能なものと更新不能なものが混在しない、という点が一番の違いになるかと思います。使っているORMによっては、insertやupdateのカラムを選択できない場合にちょっと面倒なことになるかもしれません。

またGenerated ColumnのStoredを使用している場合は、実態データがテーブル上にあるため、インデックスが適切に張られていない場合でも、計算結果がそれなりの時間で返ってくる場合があるかもしれません。ただしそれに頼るのは良くないと思いますので、インデックスを適切に貼ることをおすすめします。

それでは、説明が長くなってしまいましたが、実際に関数インデックスを張ってみましょう。JSON_EXTRACTの戻り値が通常だと、JSON型になるためにERROR 3753 (42000): Cannot create a functional index on a function that returns a JSON or GEOMETRY value.というエラーになってしまうので、CASTして数字型に変換しています。

また、前項で定義したindex_json_idがあると複雑になってしまうため、一旦dropしておきます。

mysql> ALTER TABLE json_data DROP INDEX index_json_id;
mysql> ALTER TABLE json_data ADD INDEX index_for_json((CAST(JSON_EXTRACT(`doc`, '$.json_id') AS UNSIGNED)));

続いてexplainを使って検索結果を比較してみましょう。検索をする際にはインデックスで定義した値を使用しましょう。

mysql> explain SELECT * FROM json_data where  JSON_EXTRACT(`doc`, '$.json_id') = 1
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | json_data | NULL       | ref  | index_for_json | index_for_json | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

ということで、適用した関数インデックスが使用されていることがわかります。

まとめ

今回はMySQLでJSON型のデータのキーに対してインデックスを貼る方法を2つ紹介してみました。JSONを効率的に運用する方法として、知っておくと便利に活用できると思いますので、上手に使っていきましょう。

MySQL 5.7系を使用している場合に、JSON型にインデックスを貼りたい場合Generated Columnを使用するしか選択肢がありませんでしたが、MySQL 8.0系以上を使用している場合は式インデックスを使うこともできます。そしてMySQL 5.7のEOLが2023年10月に迫っていることもあるので、ぜひ8.0へのアップグレードを進めてみてはいかがでしょうか。

おすすめ記事

記事・ニュース一覧