MySQL インデックスプッシュダウンを 5 分で学ぶ

序文

面接中に「MySQL 5.6」や「インデックス最適化」などの用語を聞いた場合、この質問は「インデックスプッシュダウン」に関するものであることがすぐにわかるはずです。

インデックスプッシュダウンとは何ですか?

Index Condition Pushdown (ICP) は、MySQL 5.6 の新機能です。テーブルクエリの数を減らし、クエリの効率を向上させることができます。

インデックスプッシュダウン最適化の原理

まず、MySQL の一般的なアーキテクチャを簡単に見てみましょう。

MySQL サービス層は、SQL 構文の解析、実行プランの生成などを担当し、ストレージエンジン層を呼び出してデータの保存と取得を実行します。

インデックスプッシュのプッシュダウンは、実際には、上位層 (サービス層) が担当するタスクの一部が、処理のために下位層 (エンジン層) に引き渡されることを意味します。

ICP を使用せずに MySQL クエリを詳しく見てみましょう。

ストレージエンジンはインデックスレコードを読み取ります。
インデックス内の主キー値に基づいて完全な行レコードを見つけて読み取ります。
ストレージエンジンはレコードをサーバーレイヤーに渡し、レコードが WHERE 条件を満たしているかどうかを確認します。

ICP を使用する場合、クエリプロセスは次のようになります。

ストレージエンジンは、インデックスレコード (完全な行レコードではない) を読み取ります。
インデックス内の列を使用して WHERE 条件をチェックできるかどうかを判断します。条件が満たされない場合は、インデックスレコードの次の行を処理します。
条件が満たされた場合、インデックス内の主キーを使用して完全な行レコードを検索して読み取ります (これをテーブル戻りと呼びます)。
ストレージエンジンはレコードをサーバーレイヤーに渡し、サーバーレイヤーはレコードが WHERE 条件の残りの部分を満たしているかどうかを確認します。

インデックスプッシュダウンの具体的な実践

理論はかなり抽象的なので、実際に実践してみましょう。

ユーザーテーブル tuser を使用して、テーブル内に共同インデックス (名前、年齢) を作成します。

ここで要件がある場合: テーブル内の、名前が Zhang で、年齢が 10 歳のすべてのユーザーを取得します。次に、SQL ステートメントは次のように記述されます。

select * from tuser where name like '张%' and age=10;

インデックスの左端一致の原則を理解していれば、このステートメントはインデックスツリーを検索するときにのみ張でき、条件を満たす最初のレコードの ID は 1 であることがわかります。

では次のステップは何でしょうか?

ICPは使用されていません

MySQL 5.6 より前では、ストレージエンジンは結合インデックスを通じてname likelike '張%'の主キー ID (1, 4) を見つけ、テーブルを 1 つずつスキャンし、クラスター化インデックスを削除して完全な行レコードを見つけ、サーバーレイヤーはage=10進行篩選。

概略図を見てみましょう。

テーブルを 2 回返す必要があることがわかります。これにより、結合インデックスの他のフィールド age が無駄になります。

ICPの使用

MySQL 5.6 以降では、ストレージエンジンは (name, age) 結合インデックスに基づいてname likelike '張%'を検索します。結合インデックスには age 列が含まれているため、ストレージエンジンはage=10に従って結合インデックスを直接フィルター処理します。フィルタリングされたデータに従ってテーブルを 1 つずつスキャンします。

概略図を見てみましょう。

テーブルが 1 回だけ返されたことがわかります。

さらに、実行プランを確認すると、Extra 列にUsing index condition表示され、インデックスプッシュダウンが使用されていることがわかります。

+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+
| 1 | SIMPLE | tuser | NULL | 範囲 | na_index | na_index | 102 | NULL | 2 | 25.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+-------+---------+------------------------+

インデックスプッシュダウンの使用条件

range、ref、eq_ref、ref_or_null アクセスメソッドにのみ使用できます。
InnoDB および MyISAM ストレージエンジンとそれらのパーティションテーブルにのみ使用できます。
InnoDB ストレージエンジンの場合、インデックスプッシュダウンはセカンダリインデックス (補助インデックスとも呼ばれます) にのみ適用されます。

インデックスプッシュダウンの目的は、テーブル戻り数を減らすこと、つまり IO 操作を減らすことです。 InnoDB のクラスター化インデックスの場合、データとインデックスは一緒になり、テーブルを返すことはありません。

サブクエリを参照する条件はプッシュダウンできません。
ストレージエンジンはストアド関数を呼び出せないため、ストアド関数を参照する条件はプッシュダウンできません。

要約する

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

以下もご興味があるかもしれません: