MySQL パフォーマンス最適化インデックス プッシュダウン

インデックス条件プッシュダウン (ICP) は MySQL 5.6 で導入され、クエリを最適化するために使用されます。

ICP を使用せずに、非主キー インデックス (通常のインデックスまたはセカンダリ インデックスとも呼ばれます) を使用してクエリを実行すると、ストレージ エンジンはインデックスを通じてデータを取得し、それを MySQL サーバーに返します。その後、MySQL サーバーはデータが条件を満たしているかどうかを判断します。

ICP を使用する場合、特定のインデックス列に判定条件がある場合、MySQL サーバーはこれらの判定条件をストレージ エンジンに渡し、ストレージ エンジンはインデックスが MySQL サーバーから渡された条件を満たしているかどうかを判断します。インデックスが条件を満たしている場合にのみ、データが取得され、MySQL サーバーに返されます。

インデックス条件プッシュダウンの最適化により、ストレージ エンジンがベース テーブルをクエリする回数と、MySQL サーバーがストレージ エンジンからデータを受信する回数を削減できます。

自慰行為を始める

始める前に、ユーザー テーブル (user) を準備する必要があります。このテーブルの主なフィールドは、id、名前、年齢、住所です。共同インデックス（名前、年齢）を作成します。

ファーストネームが Chen であるすべてのユーザーを一致させる必要があると仮定します。SQL ステートメントは次のようになります。

SELECT * from user where name like '陈%'

「最良左プレフィックス」の原則に従って、ここでは結合インデックス (名前、年齢) がクエリに使用され、パフォーマンスは完全なテーブルスキャンよりも確実に高くなります。

問題は、他の条件がある場合はどうなるかということです。ファーストネームが Chen で、年齢が 20 歳のユーザーを一致させるという別の要件があるとします。このときの SQL ステートメントは次のようになります。

名前が '陈%' で、年齢が 20 であるユーザーから SELECT * を実行します。

この SQL ステートメントはどのように実行すればよいでしょうか?以下は、MySQL 5.6 以前とそれ以降のバージョンの分析です。

MySQL バージョン 5.6 より前

5.6 より前のバージョンにはインデックス プッシュダウンの最適化がないため、実行プロセスは次のようになります。

age フィールドは無視され、クエリは name を通じて直接実行されます。(name, age) レッスン ツリーには、それぞれ ID が 2 と 1 である 2 つの結果が見つかります。次に、取得された ID 値を使用して、テーブルを何度もクエリします。したがって、このプロセスではテーブルに 2 回戻る必要があります。

Mysql5.6以降のバージョン

バージョン 5.6 ではインデックス プッシュダウンの最適化が追加され、実行プロセスは次のようになります。

InnoDB は age フィールドを無視しません。代わりに、インデックス内で age が 20 に等しいかどうかを判断し、20 に等しくないレコードを直接スキップします。したがって、(name, age) インデックス ツリーでは 1 つのレコードのみが一致します。この時点で、id を使用して主キー インデックス ツリー内のすべてのデータをクエリします。このプロセスでは、 1 つのテーブルを返すだけで済みます。

練習する

もちろん、上記の分析はあくまでも原理的なものです。実際に分析することができます。そこで、Chen は MySQL バージョン 5.6 をインストールし、上記のステートメントを次のように解析しました。

explain 分析結果によると、Extra の値が Using index condition になっていることがわかります。これは、インデックス プッシュダウンが使用されたことを意味します。

要約する

非主キー インデックスでのインデックス プッシュダウンの最適化により、テーブル戻りの数を効果的に削減し、クエリ効率を大幅に向上させることができます。

インデックス プッシュダウンをオフにするには、次のコマンドを使用します。設定ファイルの変更についてはこれ以上説明しません。結局のところ、なぜこのような優れた機能をオフにするのでしょうか?

optimizer_switch='index_condition_pushdown=off' を設定します。

これで、MySQL パフォーマンスの最適化とインデックス プッシュダウンに関するこの記事は終了です。MySQL インデックス プッシュダウンに関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。