8.2.1.6 インデックスコンディションプッシュダウンの最適化

インデックスコンディションプッシュダウン (ICP) は、MySQL がインデックスを使用してテーブルから行を取得する場合の最適化です。 ICP を使用しない場合、ストレージエンジンはインデックスをトラバースして、ベーステーブル内で行を検索し、MySQL Server に返し、MySQL Server が行に対して WHERE 条件を評価します。 ICP が有効で、インデックスのカラムのみを使用して WHERE 条件の一部を評価できる場合、MySQL サーバーは WHERE 条件のこの部分をストレージエンジンにプッシュダウンします。ストレージエンジンは、インデックスエントリを使用して、プッシュされたインデックス条件を評価し、これが満たされている場合にのみ、テーブルから行を読み取ります。 ICP は、ストレージエンジンがベーステーブルにアクセスする必要がある回数と、MySQL サーバーがストレージエンジンにアクセスする必要がある回数を削減できます。

インデックス条件プッシュダウン最適化の適用には、次の条件があります:

ICP は、完全なテーブルの行にアクセスする必要がある場合に、range, ref, eq_ref および ref_or_null のアクセス方法に使用されます。
ICP は、パーティション化された InnoDB テーブルおよび MyISAM テーブルを含む InnoDB テーブルおよび MyISAM テーブルに使用できます。
InnoDB テーブルの場合、ICP はセカンダリインデックスにのみ使用されます。 ICP の目的は、全行読取りの数を減らして I/O 操作を減らすことです。 InnoDB のクラスタ化されたインデックスの場合、完全なレコードはすでに InnoDB バッファーに読み込まれています。この場合、ICP を使用しても I/O は削減されません。
ICP は、仮想生成カラムに作成されたセカンダリインデックスではサポートされていません。 InnoDB では、仮想生成カラムのセカンダリインデックスがサポートされます。
サブクエリーを参照する条件はプッシュダウンできません。
ストアドファンクションを参照する条件はプッシュダウンできません。ストレージエンジンはストアドファンクションを呼び出せません。
トリガー条件はプッシュダウンできません。 (トリガーされる条件の詳細は、セクション8.2.2.3「EXISTS 戦略を使用したサブクエリーの最適化」を参照してください。)

この最適化の仕組みを理解するには、最初に、インデックス条件プッシュダウンが使用されない場合のインデックススキャンの進行方法を検討します:

まず、インデックスタプルを読み取り、次にそのインデックスタプルを使用して、完全なテーブル行を見つけて読み取ることで、次の行を取得します。
このテーブルに適用される WHERE 条件の部分をテストします。テスト結果に基づいて行を受け入れるか、拒否します。

インデックス条件プッシュダウンを使用すると、かわりに次のようにスキャンが続行されます:

次の行のインデックスタプルを取得します (ただし完全なテーブル行ではありません)。
このテーブルに適用され、インデックスカラムのみを使用してチェックできる WHERE 条件の部分をテストします。条件が満たされている場合、次の行のインデックスタプルに進みます。
条件が満たされている場合、インデックスタプルを使用して、完全なテーブル行を見つけて読み取ります。
このテーブルに適用される WHERE 条件の残りの部分をテストします。テスト結果に基づいて行を受け入れるか、拒否します。

インデックス条件プッシュダウンが使用されている場合、EXPLAIN 出力の Extra カラムに Using index condition が表示されます。完全なテーブルの行を読み取る必要がある場合は適用されないため、Using index は表示されません。

テーブルに人とそのアドレスに関する情報が含まれており、テーブルに INDEX (zipcode, lastname, firstname) として定義されたインデックスがあるとします。個人の zipcode 値がわかっているが、姓がわからない場合は、次のように検索できます:

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL はインデックスを使用して、zipcode='95054' を持つ人をスキャンします。 2 番目の部分 (lastname LIKE '%etrunia%') を使用してスキャンする必要がある行数を制限することはできないため、インデックス条件プッシュダウンを使用しない場合、このクエリーでは、zipcode='95054'を持つすべてのユーザーの完全なテーブルの行を取得する必要があります。

インデックス条件プッシュダウンでは、MySQL はテーブルの行全体を読み取る前に lastname LIKE '%etrunia%'部分をチェックします。これにより、zipcode 条件に一致するが lastname 条件に一致しないインデックスタプルに対応する完全な行の読取りが回避されます。

インデックス条件のプッシュダウンはデフォルトで有効になっています。 index_condition_pushdown フラグを設定することで、optimizer_switch システム変数で制御できます:

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

セクション8.9.2「切り替え可能な最適化」を参照してください。