この記事はFunctional Indexes in MySQLの翻訳版です。

 データベース インデックスは、クエリのパフォーマンスを向上させるために使用されます。 通常インデックスは、テーブルの特定の列のデータに関する情報が含まれています。 MySQL 8.0.13 での関数インデックスの導入により、式または関数の結果に基づいてインデックスを作成できるようになりました。

事前準備：

関数インデックスについて話す前に、データをセットアップします。 このファイルをダウンロードし、含まれている SQL スクリプトを実行して、テーブル作成及びデータを挿入します。

正常に完了すると、test_data という名前のテーブルが作成されます。

test_data table structure

このテーブルには2000行のデータが含まれいます。

問題：

テーブル構造は基本的なものです。 ３つの列（ID と２つの列（整数）で構成されています。col1,col2 の2 つの整数の合計が 10 になる行がいくつ存在するかを数えたい場合は、次のようになります。

select count(*) from test_data where col1 + col2 = 10;

結果:

query results

次の SQL コマンドで、このクエリの実行計画を確認できます。:

explain select count(*) from test_data where col1 + col2 = 10\G

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

実行計画出力結果:

explain plan result

赤い矢印は、このクエリで使用できるインデックスがないことを示しています。

黄色の矢印は、クエリが完全なテーブル スキャンを実行したことを示します。

このクエリのパフォーマンスは、今回のような少ない行数であれば許容できますが、かなり多くのデータがある場合、フルテーブルスキャンとなりパフォーマンスが低下します。

ソリューション：

おそらく、この問題の解決策は機能的なインデックスを作成することであることに気付いたでしょう。- その通りです。

関数インデックスを作成するための構文は、標準のインデックスを作成する場合と似ています。 今回の対応するインデックスを作成するコマンドは次のようになります。

alter table test_data
    add index col_sum((col1 + col2));

クエリに役立つインデックスができたので、実行計画を再度見てみましょう

explain plan result 2

赤い矢印はいくつかのインデックスが使用されていることを示してます。

黄色の矢印は 177 行のみがスキャンされたことを示しています。

この小さなデータセットでも、かなりの改善です。

ルール：

関数インデックスを使用すると、クエリを書き直してボトルネックに対処する必要なく、クエリのパフォーマンスを向上させることができます。 ただしいくつかのルールがあります。

  • 列と区別するために、式を括弧で囲む必要があります。
    • INDEX((col1 + col2)) vs INDEX( col1, col2)
    • 関数インデックスと通常インデックスを持つインデックスを作成できます。
      • INDEX((col1 + col2), col1)
  • 関数インデックスの定義に列名のみを指定することは出来ません。
    • INDEX ((col1), (col2)) will throw an error.
  • 関数インデックスの定義に外部キー列は指定出来ません。
  • インデックスは、クエリが同じ式を使用する場合にのみ有効です。
    • select count(*) from test_data where col1 - col2 = 0 適用されません。
      • 式 (col1-col2) を使用して新しいインデックスを作成する必要があります。

影響：

関数型インデックスを作成して WHERE 句で式を使用するすべてのクエリに対応する前に、留意すべき点がいくつかあります。 関数インデックスが追加されると、非表示の仮想列が作成されます。 その結果次のような影響があります。

  • 生成された列は、テーブル数の制限にカウントされます。
  • 関数インデックスの式で使用できるのは、生成された列で許可されている関数のみです。
  • 以下は、機能インデックスでは許可されていません。
    • サブクエリ
    • パラメータ
    • 変数
    • ストアドファンクション
    • ロータブルファンクション（User-Defined Function）

まとめ：

これまでに示したように、関数インデックスは、WHERE 句で式を使用するクエリのパフォーマンスを向上させることができます。

ただし、基礎となるデータベース構造に対するこれらのインデックスの影響を考慮する必要があります。