MySQLのIndex Condition Pushdown とはなんぞやという話

MySQL のバージョン5.6から追加された機能に、Index Condition Pushdown(ICP) というものがあります。ICP は「マルチカラムインデックスの順番を意識しなくてもよくなる仕組み」的な説明がされることがあり、それはそれで間違いではないのかもしれません。が、それだと「ストレージエンジン側に条件式をプッシュダウンする」という動作が伝わりにくい気がしますし、ICP がマルチカラムインデックスの順序による制限を解消するものだと勘違いしてしまう可能性があります。なので、この記事では行フェッチ時の動作を見ながら ICP の動作イメージや利点を考えてみようと思います。ちなみに InnoDB を前提に書いていこうと思います。

先に簡単に書いておくと、

  • ICPとは
    • ストレージエンジンがセカンダリインデックスを使って行をフェッチしようとする際に、MySQLサーバからストレージエンジンに条件式を渡してストレージエンジン側でWHEREの条件判定を行う仕組みのこと
  • なんのために
    • 行のフェッチ時の IO を減らすため

という感じでしょうか。

MySQLの基本的な構造

ICP の動作を考える上で必要な基本的な MySQL の構造を書いておきます。図にすると次のような感じになります。 f:id:norikone:20181001212436p:plain

ここでまず言いたいのは、MySQL サーバとストレージエンジンは構造的に分離されているということです(少々語弊があるかもしれませんが)。サーバ側ではクエリのパースやオプティマイズが行われ、取得したい行が決まったらストレージエンジンの API を叩いてレコードを取ってきてもらいます。この際、ストレージエンジンから取得するデータ量(レコード数)は少ない方がパフォーマンスがいいです。通信が少ない方がオーバヘッドを抑えられますし、MySQL サーバのバッファプールの消費量も抑えられます。

また、インデックスについて言えることは、セカンダリインデックスからクラスタインデックスへのアクセスは少ない方がいいということです。InnoDB ではカバリングインデックスでない限り、完全な行を取得するためにクラスタインデックスを辿る処理が必要です。で、この処理とそれに伴うディスクIOが大きなオーバヘッドになります。ちらみに最近の MySQL には MRR(Multi Range Read) という機能が実装されていて、このディスクIO時のランダムアクセスをシーケンシャル化してオーバヘッドを抑えられるみたいです。

ということで、MySQL サーバ↔ストレージエンジン間の通信」と「セカンダリインデックス使用時のクラスタインデックスへのアクセス」はできる限り抑えたいという前提があります。

ICP使用時およびICP非使用時の動作

先に書いたように、MySQL サーバ自体は実際の行の取得に深く関与しません。ですが、クエリによって最終的に返される行は、MySQL サーバとストレージエンジンの2者の連携によって選択されます。

具体例として、以下のようなクエリ(公式docに記載のクエリをちょっと変えたもの)を考えます。また、マルチカラムインデックスとして (zipcode, lastname, firstname) が設定されているとします(以後、このインデックスを「midx」と呼びます)。

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

ここで、zipcode によって絞り込めるため、セカンダリインデックスである midx が使われることになるとします。lastname の条件判定は条件が完全一致であれば midx を活用できますが、ここでは部分一致なので、インデックスの篩にかけられるのは zipcode 条件までです。

ICP非使用時の行フェッチ動作

図にすると以下のような感じになります。

f:id:norikone:20181002210127p:plain

ストレージエンジンはクエリに答えるために midx を走査して、zipcode が 95054 のレコードを探します。図では、該当するレコードが4件存在します。見つけたら、クラスタインデックスを辿ってそれぞれの完全なレコードを取得し、MySQL サーバに返します。この時点では、MySQL サーバが受け取ったレコード群はまだ lastname や address の条件の評価をされていません。なので、MySQL サーバ側で where 条件を評価して、条件を満たさないレコードを捨てます。図では最終的に1レコードが残ったとして、それがクライアントに返されています。

さて、上の「MySQLの基本的な構造」の説明では、ストレージエンジンから MySQL サーバへの転送レコードとセカンダリインデックスからクラスタインデックスへのアクセス数は少ないほうがいいと書きました。しかし、ここでは1レコードをクライアントに返せばいいだけなのに、実際には4レコードもフェッチしています。つまり、それぞれのタイミングで3レコード分も無駄が発生しているのです。これをどうにかしたいよね、というのが Index Condition Pushdown です。

ICP使用時の動作

ICP を使うと先ほどの図は次のようになります。

f:id:norikone:20181002211609p:plain

ICP を使わない場合との大きな違いは、セカンダリインデックスを走査している時にクエリの条件式を評価している点です。ICP では、MySQL サーバ側からストレージエンジンにクエリの条件式を渡す(Pushdownする)ことで、走査のタイミングでWHERE条件の篩にかけることができます。これにより、lastname 条件式を満たさないレコードについては、フェッチせずに済むようになります。このため、ICP を使う場合の図では、先ほどと比べて矢印の数が減っています。つまり、無駄な行フェッチを減らしてパフォーマンスを上げようよということですね。

それと、このケースでは ICP を使ったとしても、address の条件式はインデックス走査中に評価できません。これは、midx に address カラムの値が含まれていないためです。なので、いずれにせよクラスタインデックスへのアクセスは必要になります(減らすことはできますが)。例えば midx が (zipcode, lastname, firstname, address) になっていれば、セカンダリインデックスを走査するタイミングで address 式の評価もできるはずです。

ちなみに、図では便宜上ストレージエンジンから MySQL サーバに一方的にレコードを送っているように描いていますが、実際には MySQL サーバ側からストレージエンジンに向けてリクエストが沢山飛んでいて、それに対するレスポンスが返っているとみるのが正確だと思います。で、ICP によってそれが抑えられているということでもあります。

おわり

ということで ICP を使えば、ストレージエンジンの完全な行へのアクセスと、MySQLサーバ↔ストレージエンジン間のアクセスを抑えることができます。それに伴い、MySQL サーバのバッファプールの消費を抑えられます。逆に ICP を使わずに大量に無駄レコードをフェッチするケースでは、ディスクIOで読んだレコード群を少しづつバッファプールにロードしたものの、ほとんどは条件判定で捨てられるという悲しいことが起きてしまいます。ICP 強い。おわり。