仮想列を使ってJSONドキュメントにインデックスを作成する(MySQL Server Blogより)

免責事項

この記事はMorgan Tocker氏によるMySQL Server Blogの投稿「Indexing JSON documents via Virtual Columns」(2016/3/8)をユーザが翻訳したものであり、Oracle公式の文書ではありません。

前回の投稿では、新しく追加されたJSON型および関数をSF OpenDataのサンプルデータを使って試しに動かしてみました。本日は、一歩進んでインデックスを作成する例を紹介しよう思います。

生成列の紹介

MySQL 5.7では、生成列と呼ばれる新機能が追加されています。簡単な例から始めましょう。

CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 my_integer INT,
 my_integer_plus_one INT AS (my_integer+1)
);

ここでmy_integer_plus_oneは仮想生成列(virtual generated column)として知られているものです。メタデータにしか存在せず、メモリ上あるいはディスク上に行データの追加の領域を必要としません。オンデマンドに生成されるビューやマクロと同じようなものと考えることができます。

仮想列を生成する利点としては、仮想列がインデックスをサポートすることが挙げられます。 上記の例を次のように拡張します。

CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 my_integer INT,
 my_integer_plus_one INT AS (my_integer+1),
 INDEX (my_integer_plus_one)
);

生成列を使うとMySQLは効率的にある式に対するインデックスを生成できます。これは関数インデックスとして知られているものです。インデックス自体は現在はマテリアライズされており、実態が存在します(他のインデックス同様)。

JSONにインデックスを作成する

MySQLのマニュアルの注記に次の記載があります。

JSONカラムにはインデックスが生成できません。この制限を回避する方法として、JSONカラムからスカラー値を抽出する生成列に対してインデックスを作成する方法があります。

前回の私のブログ投稿の例を再利用してこれを実施してみましょう。

# 元のクエリ
SELECT * FROM features 
WHERE feature->"$.properties.STREET" = 'MARKET'

# 仮想列を追加します
# この操作はメタデータの変更しかしないため、即時に終了します
ALTER TABLE features ADD feature_street VARCHAR(30) AS (JSON_UNQUOTE(feature->"$.properties.STREET"));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# インデックスを追加します
# これはオンラインの操作ですが、大量のデータに対してはいくばくか時間がかかります

ALTER TABLE features ADD INDEX (feature_street);
Query OK, 0 rows affected (9.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

これによってこのクエリを仮想列を使った場合と元の式とで実行できます。

# JSONを抽出する短縮演算子を利用する
EXPLAIN SELECT * FROM features  WHERE feature->"$.properties.STREET" = 'MARKET'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 33
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

# 仮想列を利用する
mysql> EXPLAIN SELECT * FROM features  WHERE feature_street = 'MARKET'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: features
   partitions: NULL
         type: ref
possible_keys: feature_street
          key: feature_street
      key_len: 33
          ref: const
         rows: 808
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAINの実例で示されているように、両方のパターンでインデックスを利用していることにご留意ください。つまり、オプティマイザーがJSONクエリー内の式と一致するインデックスのついた仮想列を進んで使う[1]、といえます。

[1] JSON_EXTRACT(および短縮演算子の ->)はstringにキャストする必要があるため、JSON_UNQUOTEは仮想列の表現に必要です。

ストアド生成列

網羅性をもたせるために述べておくと、上記の例は生成列のデフォルトのVIRTUALな生成列を利用しています。テーブルの実際の行として存在するストアド(STORED)な生成列もあります。

ALTER TABLE features ADD feature_type VARCHAR(30) 
AS (JSON_UNQUOTE(feature->"$.type")) STORED;

ストアド生成列はテーブルが変更されると、自動的にメンテナンスされ最新の状態に保たれます。 次の条件に該当しない限りVIRTUAL仮想列を利用することが推奨されます。

1. 主キーにインデックスを生成する必要がある
2. (通常のBTREEの代わりに)フルテキスト(FULLTEXT)またはRTREEインデックスが必要
3. 仮想列の計算コストが高く、大量のスキャンが予想される(つまりインデックスを利用しない)

最初の2例ではストアド生成列が必須で、3つ目の例ではクエリーが高速になることが期待されます。

まとめ

この記事がJSONデータにインデックスを追加するのにどのように仮想列を利用できるかの実例となればと思います。仮想列はJSONや他のデータにインデックスを生成することができ、これは主な利用例の1つです。オプティマイザーは仮想列のインデックスの使い方を理解していますので、非常に柔軟な使い方ができるようになっています！