これまでMySQLのJSONの活用方法に関して、第197回 MySQLでJSONを活用してみる
今回はちょっとした応用として、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.
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つです。
- 第150回 Generated Columnを利用してみるで紹介したGenerated Columnを使用する
- 関数インデックスを使用する
それぞれに関して紹介していきたいと思います。
Generated Columnを使用する
第150回 Generated Columnを利用してみるで紹介したGenerated Columnは、テーブル定義で宣言した式に従って値を自動で生成し、カラムとして扱えるようになるMySQL5.
今回は以下のようなJSONを考えた場合に、JSONの中身のjson_
{
"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_
これに対して検索を行った場合の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と何が違うのかな？
またGenerated ColumnのStoredを使用している場合は、実態データがテーブル上にあるため、インデックスが適切に張られていない場合でも、計算結果がそれなりの時間で返ってくる場合があるかもしれません。ただしそれに頼るのは良くないと思いますので、インデックスを適切に貼ることをおすすめします。
それでは、説明が長くなってしまいましたが、実際に関数インデックスを張ってみましょう。JSON_ERROR 3753 (42000): Cannot create a functional index on a function that returns a JSON or GEOMETRY value.というエラーになってしまうので、CASTして数字型に変換しています。
また、前項で定義したindex_があると複雑になってしまうため、一旦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.