MySQLのJSONデータにインデックスを使って効率よく利用しよう

この記事はIndexing JSON Data in MySQLの翻訳版です。

JSON をリレーショナル データベースに格納することは、開発者がかなり前から行ってきました。 JSON をデータベース テーブルに格納する理由はさまざまです。ユーザー設定と構成データがその 2 つの良い例です。 JSONデータ型 JSON データ型は、バージョン 5.7.8 で MySQL に導入されました。 このデータ型を使用すると、有効な JSON をデータベース列に格納し、JSON の値に基づいてクエリを実行できます。

潜在的問題：

JSON データを MySQL に保存する場合、その JSON 内の値に基づいてデータベースにクエリを実行できます。 これは、さまざまな MySQLのJSON関数を使用して実現できます。 潜在的な問題は、行数が増えるにつれてクエリのパフォーマンスが低下する可能性があることです。 これがJSON以外のデータ型の場合、クエリのパフォーマンスを向上させる解決策の一つとしてインデックスを追加することが可能です。

MySQL 8.0.13から、関数インデックスを作成できるようになりました。 関数インデックスを使用すると、列データではなく、式に基づいてインデックスを作成できます。 この機能を利用して、JSON 値に基づいてインデックスを作成できます。

はじめに：

インデックスを作成する前に、JSON データを含む列を持つ単純なテーブルを作成しましょう。

CREATE TABLE `vehicle` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `vehicle_data` JSON NOT NULL,
  PRIMARY KEY (`id`));

テーブルを作成したら、いくつかデータを追加しましょう。

 INTO vehicle(vehicle_data) values('{"first_name":"Austine","last_name":"Okill","gender":"Polygender","manufacturer":"GMC","model":"Sierra Hybrid","year":2006,"vin":"5TDBK3EH7BS492643","color":"Maroon"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Carrissa","last_name":"McGowing","gender":"Female","manufacturer":"Dodge","model":"Avenger","year":2000,"vin":"WBAPM7C53AE594359","color":"Maroon"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Mirabella","last_name":"O''Tuohy","gender":"Female","manufacturer":"Mercury","model":"Mountaineer","year":1997,"vin":"YV4902DZ7E2611356","color":"Red"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Marni","last_name":"Fratczak","gender":"Female","manufacturer":"Ford","model":"F150","year":2005,"vin":"WAUVT68EX5A254703","color":"Indigo"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Marcelo","last_name":"Cellone","gender":"Male","manufacturer":"Dodge","model":"Dakota","year":2004,"vin":"WBAPH5C55BF851378","color":"Turquoise"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Wilden","last_name":"Norwell","gender":"Bigender","manufacturer":"Mercury","model":"Sable","year":1996,"vin":"WAUHFAFL1EA004615","color":"Turquoise"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"York","last_name":"Hemerijk","gender":"Male","manufacturer":"Dodge","model":"Dakota","year":2002,"vin":"JTDZN3EU7FJ032100","color":"Teal"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Paquito","last_name":"Chappelow","gender":"Male","manufacturer":"Ford","model":"Falcon","year":1967,"vin":"WA1EY94L67D885695","color":"Crimson"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Klarrisa","last_name":"Ryott","gender":"Female","manufacturer":"Mitsubishi","model":"Tredia","year":1988,"vin":"1GD12YEG1FF019807","color":"Teal"}');
INSERT INTO vehicle(vehicle_data) values('{"first_name":"Maurice","last_name":"Minot","gender":"Male","manufacturer":"Acura","model":"Vigor","year":1992,"vin":"3C63DRLL0CG858281","color":"Indigo"}');

保存している JSON を整形した例です。

  "first_name":"Austine",
  "last_name":"Okill",
  "gender":"Polygender",
  "manufacturer":"GMC",
  "model":"Sierra Hybrid",
  "year":2006,
  "vin":"5TDBK3EH7BS492643",
  "color":"Maroon"
}

クエリの実行：

自動車メーカーに基づいてデータをフィルタリングします。

select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford';

Note: ->> は、JSON_UNQUOTE() 内の JSON_EXTRACT() の省略形です。

この小さなデータセットのクエリではパフォーマンスの問題はありませんが、以下を実行して、このクエリの実行計画がどのように見えるかを見てみましょう。

explain select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford'\G

Note: コマンド ライン インターフェイスでより読みやすい出力を得るために、コマンドの最後に \G を使用します。

出力結果:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vehicle
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 100
        Extra: Using where
1 row in set, 1 warning (0.0008 sec)

今回の注目すべき部分は、possible_keys と key の値です。 ご覧のとおり、これらの値はNULLです。これは、このクエリに使用できるインデックスが存在しないことを意味します。

インデックスの追加：

このクエリの潜在的なパフォーマンスの問題に対処するために、JSON の値に基づいてインデックスを作成できます。 このSQLコマンドは次のようになります。

ALTER TABLE vehicle
    ADD INDEX manufacturer((
        CAST(vehicle_data->>"$.manufacturer" as CHAR(255))
    COLLATE utf8mb4_bin
    ));

以下のコマンドで、インデックス定義を確認できます

show indexes from vehicle\G

このコマンドの結果には、主キーのインデックスと作成したばかりのインデックスの 2 つのインデックスが表示されます。

*************************** 1. row ***************************
        Table: vehicle
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: vehicle
   Non_unique: 1
     Key_name: manufacturer
Seq_in_index: 1
  Column_name: NULL
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: (cast(json_unquote(json_extract(`vehicle_data`,_utf8mb4\'$.manufacturer\')) as char(255) charset utf8mb4) collate utf8mb4_bin)

新しいインデックスの式プロパティを見ると、テキスト vehicle_data->>”$.manufacturer” が json_unquote(json_extract(vehicle_data,_utf8mb4\’$.manufacturer\’)) に置き換えられていることがわかります。 

なぜ CAST() と COLLATE を使用してこのインデックスを作成したのか疑問に思われるかもしれません。 

まず、関数 JSON_UNQUOTE() は LONGTEXT のデータ型を返します。LONGTEXT データ型はインデックスでは使用できないため、結果をインデックス可能なデータ型に CAST() する必要があります。 この例では、CHAR(255) です。 

次に、データを抽出するために使用される関数 (クエリの WHERE ステートメントで使用される) が utf8mb4_bin に照合されるため、COLLATE を使用します。 ただし、COLLATE を使用せずに文字列をキャストすると、utf8mb4_0900_ai_ci にキャストされます。 インデックスに格納されているものの照合が WHERE 句の文字列の照合と一致しない場合、インデックスは使用されません。

確認してみよう：

インデックスを作成したら、以下のコマンドを再実行して実行計画を確認しましょう。

explain select * from vehicle where vehicle_data->>"$.manufacturer" = 'Ford'\G

出力結果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vehicle
   partitions: NULL
         type: ref
possible_keys: manufacturer
          key: manufacturer
      key_len: 1023
          ref: const
         rows: 2
     filtered: 100
        Extra: NULL
1 row in set, 1 warning (0.0085 sec)

possible_key と key に、このクエリの実行に新しいインデックスが使用されていることを示す表示があることがわかります。

まとめ：

JSON データをリレーショナル データベースに格納することは、JSON データ型が存在するずっと前から開発者が行ってきたことです。 JSON データ型を使用すると、有効な JSON データを保存し、JSON オブジェクトの値に基づいてクエリを実行できます。 関数インデックスを使用することで、他のデータ型のインデックスがパフォーマンスを向上させるのと同じように、これらのクエリのパフォーマンスを向上させることができます。

Photo by Maksym Kaharlytskyi on Unsplash