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

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

インデックスコンディションプッシュダウン最適化は、完全なテーブル行にアクセスする必要がある場合に、range、ref、eq_ref、および ref_or_null アクセスメソッドで使用されます。この戦略は、InnoDB テーブルと MyISAM テーブルに使用できます。(インデックスコンディションプッシュダウンは、MySQL 5.6 ではパーティション化されたテーブルでサポートされていません。この問題は MySQL 5.7 で解決されています。)ただし、InnoDB テーブルの場合、ICP はセカンダリインデックスにのみ使用されます。ICP の目標は、完全なレコードの読み取りの回数を減らし、それによって IO 操作を減らすことです。InnoDB のクラスタ化されたインデックスの場合、完全なレコードはすでに InnoDB バッファーに読み込まれています。この場合に ICP を使用しても IO は削減されません。

この最適化の仕組みを確認するには、まずインデックスコンディションプッシュダウンが使用されない場合に、インデックススキャンがどのように進められるかを考察します。

1. まず、インデックスタプルを読み取り、次にそのインデックスタプルを使用して、完全なテーブル行を見つけて読み取ることで、次の行を取得します。

2. このテーブルに適用される WHERE 条件の部分をテストします。テスト結果に基づいて行を受け入れるか、拒否します。

インデックスコンディションプッシュダウンが使用される場合、代わりにスキャンは次のように進められます。

1. 次の行のインデックスタプルを取得します (ただし完全なテーブル行ではありません)。

2. このテーブルに適用され、インデックスカラムのみを使用してチェックできる WHERE 条件の部分をテストします。条件が満たされている場合、次の行のインデックスタプルに進みます。

3. 条件が満たされている場合、インデックスタプルを使用して、完全なテーブル行を見つけて読み取ります。

4. このテーブルに適用される WHERE 条件の残りの部分をテストします。テスト結果に基づいて行を受け入れるか、拒否します。

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

人とその住所に関する情報を格納するテーブルがあり、そのテーブルに、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%' 部分をチェックします。これにより、lastname 条件に一致しないすべてのインデックスタプルに対応する完全な行の読み取りが避けられます。

インデックスコンディションプッシュダウンはデフォルトで有効です。これは、optimizer_switch システム変数で index_condition_pushdown フラグを設定することで制御できます。セクション8.8.5.2「切り替え可能な最適化の制御」を参照してください。