このページは機械翻訳したものです。

8.3.11 生成されたカラムインデックスのオプティマイザによる使用

MySQL では、生成されたカラムのインデックスがサポートされます。 例:

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成されたカラム gc は、式 f1 + 1 として定義されます。 カラムもインデックス付けされ、オプティマイザは実行計画の構成時にそのインデックスを考慮できます。 次のクエリーでは、WHERE 句が gc を参照し、オプティマイザはそのカラムのインデックスがより効率的な計画をもたらすかどうかを考慮します:

SELECT * FROM t1 WHERE gc > 9;

オプティマイザは、生成されたカラムのインデックスを使用して実行計画を生成できます。これらのカラムへの名前によるクエリーに直接参照がない場合でも同様です。 これは、WHERE、ORDER BY または GROUP BY 句が、インデックス付けされた生成カラムの定義と一致する式を参照している場合に発生します。 次のクエリーは、gc を直接参照しませんが、gc の定義と一致する式を使用します:

SELECT * FROM t1 WHERE f1 + 1 > 9;

オプティマイザは、式 f1 + 1 が gc の定義と一致し、gc がインデックス付けされていることを認識するため、実行計画の構成時にそのインデックスが考慮されます。 これは、EXPLAIN を使用して表示できます:

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

実際には、オプティマイザは式 f1 + 1 を、式に一致する生成されたカラムの名前に置き換えています。 これは、SHOW WARNINGS によって表示される拡張 EXPLAIN 情報で使用可能なリライトされたクエリーでもわかります:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

生成されたカラムインデックスのオプティマイザでの使用には、次の制限および条件が適用されます:

• クエリー式が生成されたカラム定義と一致するには、式が同一であり、同じ結果タイプである必要があります。 たとえば、生成されたカラム式が f1 + 1 の場合、クエリーで 1 + f1 が使用されているか、f1 + 1 (整数式) が文字列と比較されても、オプティマイザは一致を認識しません。

• 最適化はこれらの演算子に適用されます: =, <, <=, >, >=, BETWEEN および IN()。

  BETWEEN および IN() 以外の演算子の場合、どちらのオペランドも一致する生成されたカラムで置換できます。 BETWEEN および IN() の場合、最初の引数のみが一致する生成されたカラムで置換でき、他の引数の結果タイプは同じである必要があります。 BETWEEN および IN() は、JSON 値を含む比較ではまだサポートされていません。

• 生成されたカラムは、少なくとも関数コールまたは前述のいずれかの演算子を含む式として定義する必要があります。 式は、別のカラムへの単純な参照で構成できません。 たとえば、gc INT AS (f1) STORED はカラム参照のみで構成されるため、gc のインデックスは考慮されません。

• 引用符で囲まれた文字列を返す JSON 関数の値を計算するインデックス付き生成カラムと文字列を比較するには、関数値から余分な引用符を削除するために、カラム定義に JSON_UNQUOTE() が必要です。 (文字列と関数の結果を直接比較する場合、JSON コンパレータは引用符の削除を処理しますが、これはインデックスルックアップでは発生しません。) たとえば、次のようなカラム定義を記述するかわりに:

  doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED

  次のように記述します:

  doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED

  後者の定義では、オプティマイザは次の両方の比較で一致を検出できます:

  ... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
  ... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...

  カラム定義に JSON_UNQUOTE() が指定されていない場合、オプティマイザはこれらの最初の比較に対してのみ一致を検出します。

• オプティマイザが間違ったインデックスを選択した場合は、インデックスヒントを使用して無効にし、オプティマイザに別の選択を強制できます。