MySQLのJSONの活用方法に関して説明をしてきましたが、第205回 MySQLでJSONを活用してみる
今回は、複数値indexを実際に運用する際に注意しなければならない点を紹介していきたいと思います。
検証環境
今回は、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カラムを用意しています。
複数値indexの貼り方のおさらい
簡単なおさらいをしましょう。まずは単純な複数値indexを貼ってみます。
mysql> ALTER TABLE json_data ADD INDEX json_array((CAST(doc->"$.array1" AS UNSIGNED ARRAY)));
複数値indexを貼ると、以下のように使えることがわかります。
mysql> explain select doc FROM json_data WHERE 1 MEMBER OF(doc->'$.array1'); +----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | json_data | NULL | ref | json_array | json_array | 9 | const | 1 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.04 sec)
また前回、説明を省いてしまったのですが、複数値indexは複合indexとしても使用することができます。
mysql> ALTER TABLE json_data ADD INDEX json_array_complex(id, (CAST(doc->"$.array1" AS UNSIGNED ARRAY))); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
このように複合indexの要素としても定義できている事がわかります。
複数値indexの特性
複数値indexは以下のような特性があります。
- 複数値indexに挿入削除更新をした際にindexが複数操作される可能性がある
- 空配列はindexに含まれない
- NULL値に注意が必要
- 内部的にはGenerated Columnの仮想列なため、同様の制約がある
以上の特性があります。簡単にそれぞれ説明していきます。複数値indexなので挿入削除更新を行った場合に複数の値が変更されることがあります。ということですが、こちらは複数値を更新することがあり得るので、indexが複数変更される可能性があることは直感的にわかっていただけるのではないかと思います。
空配列の場合ですが、こちらは単純にSELECTをしてみるとわかります。 JSON_
mysql> explain select doc FROM json_data WHERE JSON_CONTAINS(doc->'$.array1', CAST('[]' AS JSON));
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | json_data | NULL | ALL | json_array | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
このようにindexが貼ってあるにも関わらずtype ALLになっていることがわかります。
NULL値に関しては、まずは要素にnullを入れてみましょう。
mysql> INSERT INTO json_data(doc) VALUES('{"string1": "string1", "string2": "string2", "array1": null, "array2": ["3","4"]}');
ERROR 3903 (22018): Invalid JSON value for CAST for functional index 'json_array'.
このようにnull値を入れようとするとCASTに失敗して阻まれる結果となりました。他にも配列にnullを入れてみましょう。
mysql> INSERT INTO json_data(doc) VALUES('{"string1": "string1", "string2": "string2", "array1": [null], "array2": ["3","4"]}');
ERROR 3903 (22018): Invalid JSON value for CAST for functional index 'json_array'.
こちらもnullとなります。ただし、キーの結果がNULLにならないものであれば良いので、キー自体を削除してみましょう。
mysql> INSERT INTO json_data(doc) VALUES('{"string1": "string1", "string2": "string2", "array2": ["3","4"]}');
Query OK, 1 row affected (0.01 sec)
こちらは問題なく挿入できていることがわかります。なので値がnullの場合にキーが存在するJSONをINSERTしたい場合には注意をしましょう。
第150回 Generated Columnを利用してみるで紹介させていただいたGenerated Columnと同様の仕組みになるため、結果が不定になるようなものを使用することができません。
複数値indexの注意するべき制約
運用上一番大事で注意しないといけないところとしては、この複数値indexは現状ではオンラインでの作成がサポートされていません。つまりInplaceやMySQL 8.
この制約による影響はかなり大きいです。すでに大量のデータが存在する場合に、改めてindexを貼り直すのは非常に難しいものになります。現状の制約が続く限りはIndexを貼る場合は注意をして貼りましょう。
また地味に困る点としては、使用できる照合順序が固定されている点です。binary文字セットを使用した場合にはbinaryの照合順序、utfmb4文字セットを使用した場合はutf8mb4_
その他にも、複数値indexは順序付けができないので、ASDやDESCなどによるインデックスの昇順、降順の指定が行えません。またそのため主キーや外部キーに使用することもできません。
まとめ
今回は、複数値indexの制約や特性に関して紹介させていただきました。JSONを突合する際などに非常に便利なのですが、場合によっては速度が低下してしまう可能性があり、また使用できる場面が限られるため、使用する際はよく考えてから導入しましょう。
一番注意するところとしてはやはり、AlgorithmがCOPYに限定されてしまうことです。この制約によって運用が一部変わる可能性もあるため、DBAチームなどが存在する場合は使用する際に事前に相談してから導入を決めましょう。この機能は現状ではメリットもあると思いますが、デメリットも激しいため使用する際には気をつけて導入を行いましょう。
今回紹介しきれなかった特性や制約もあるので、使用する前にぜひ一度公式のマニュアルを読んで理解して使用しましょう。