MySQL インデックスプッシュダウンの詳細

インデックスプッシュダウンがない場合、MySQL はストレージエンジンを使用してインデックスをトラバースし、テーブル内のデータ行を見つけて MySQL サーバーに返します。次に、サーバーは WHERE 条件を決定して、データ行を結果セットに追加するかどうかを確認します。
インデックスプッシュダウンが有効になっていて、WHERE 条件の一部をインデックス内の列のみを使用して評価できる場合、MySQL サーバーは WHERE 条件のこの部分をストレージエンジンにプッシュします。ストレージエンジンは、インデックスエントリを使用してプッシュされたインデックス条件を評価し、条件が満たされた場合にのみテーブルから読み取ります。

インデックスプッシュダウンにより、ストレージエンジンがデータテーブルにアクセスする回数と、MySQL サーバーがストレージエンジンにアクセスする回数を削減できます。

まだ少し混乱していますか？その通りです。上記の段落は確かに理解するのがかなり難しいですが、落胆しないでください。最もわかりやすい言葉を使って、インデックスプッシュダウンを理解する方法を説明します。

要約すると:

左端接頭辞原則
表に戻る

1. 左端接頭辞原則

MySQL 、結合インデックスを確立するときに、最左接頭辞の原則に従います。たとえば、User テーブルには結合インデックス (id、name、age) が確立されます。最左接頭辞の原則によると、この結合インデックスは、SQL の条件部分が (id)、(id、name)、または ( id 、 name 、 age ) に一致する場合にのみ使用できます。

インデックスは次の状況で使用できます。

SELECT * FROM USER WHERE id = 1

SELECT * FROM USER WHERE id = 1 かつ name = 'zhangsan'

SELECT * FROM USER WHERE id = 1、name = 'zhangsan'、age = 18 の場合

次の状況ではインデックスは使用できません。

SELECT * FROM USER WHERE name = 'zhangsan'

SELECT * FROM USER WHERE age = 18

SELECT * FROM USER WHERE name = 'zhangsan'、age = 18 の場合

結合インデックスの場合、 mysql範囲クエリ (>、<、 between 、 like ) に遭遇して一致を停止するまで、右方向への一致処理を続けます。

2. 表に戻る

MySQL 、 InnoDBエンジンで 2 種類のインデックスをサポートしています。

クラスター化インデックス:インデックス (B+ ツリーのリーフノード上) はデータ行 (実データ) を格納します。
通常のインデックス:主キーはインデックスに格納されます (B+ ツリーのリーフノード上)

ここではクラスター化インデックスに焦点を当てます。公式ドキュメントには次のように説明されています。

InnoDB 、主キーを持つテーブルのクラスター化インデックスとして主キーを使用します。
主キーのないテーブルの場合、 InnoDB最初の一意のインデックスをクラスター化インデックスとして使用します。
主キーまたは一意のインデックスがない場合、MySQL はクラスター化インデックスとして非表示の 6 バイトrow IDフィールドを生成します。

MySQL通常のインデックスを通じて一度にすべてのデータを取得できない場合、通常のインデックスを通じて主キー値を取得し、主キー値を通じてクラスター化インデックス内のレコードを検索します。このプロセスはテーブル取得と呼ばれます。カバリングインデックスを作成することで、テーブルを返す回数を減らすことができます。たとえば、ID番号で名前を検索する場合、ID番号と名前の結合インデックス（ id 、 name ）を作成できます。クエリを実行すると、このインデックスを通じてnameの値を直接取得でき、クラスター化インデックスで検索する必要がなくなります。これがカバリングインデックスです。

3. インデックスプッシュダウン

まずユーザーテーブルを作成する

テーブル「学生」を作成（
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) デフォルト NULL,
  `age` int デフォルト 0,
  `class` varchar(255) デフォルト NULL,
  主キー (`id`)、
  キー `index_two` (`name`,`age`)
)ENGINE=InnoDB;

//このテーブルに複合インデックス (`name`,`age`) を追加します

テーブルにデータを挿入する

`student` (`name`, `age`, `class`) に VALUES ('pengpeng', 21, '1') を挿入します。
`student` (`name`, `age`, `class`) に VALUES ('pengpeng', 22, '2') を挿入します。
`student` (`name`, `age`, `class`) に VALUES ('pengpeng', 23, '3') を挿入します。
`student` (`name`, `age`, `class`) に VALUES ('pengpeng', 24, '4') を挿入します。
`student` (`name`, `age`, `class`) に VALUES ('pengpeng', 25, '5') を挿入します。

クエリによって挿入されたデータは次のとおりです

次に次のSQLを説明します

説明: select * from student where name like 'peng%' and age = 23;

Extra フィールドに USING INDEX CONDITION と表示されていることがわかります。これは、この SQL がインデックスプッシュダウンを使用していることを示しています。上記の SQL ステートメントを分析してみましょう。

MySQL 5.6 より前では、名前フィールドから条件を満たす行のみを検索し、テーブルに戻ってクラスター化インデックスのデータ行を検索し、年齢フィールドを比較して、条件に該当するデータを結果セットに追加することしかできませんでした。
インデックスプッシュダウン最適化は、MySQL 5.6 で導入されました。インデックストラバーサルプロセス中に、インデックスに含まれるフィールドが最初に判断されます。ここでは、age フィールドが判断されます。年齢フィールドの要件を満たさないデータ行を直接除外することで、テーブルから返される値の数を減らします。

質疑応答エリア

質問 1:複合インデックス列が (名前、年齢、住所) の場合、次の SQL でインデックスを使用できますか?

名前が 'peng%' で、年齢が 23 である学生から * を選択します。

はい、like に遭遇すると後続の要素のマッチングが中断されますが、使用できるのは name フィールドのみです。MySQL は範囲クエリ (>、<、between、like) に遭遇してマッチングを停止するまで、右側へのマッチングを続けます。範囲列ではインデックスを使用できますが、範囲列に続く列ではインデックスを使用できません。つまり、インデックスは最大 1 つの範囲列に使用されるため、クエリ条件に 2 つの範囲列がある場合、インデックスを完全に使用することはできません。

質問 2:インデックスプッシュダウンは、結合インデックスにのみ存在できますか?

はい、非結合インデックスではインデックスプッシュダウンは不可能です。

質問 3:どのような状況でインデックスプッシュダウンを使用できないのでしょうか?

プッシュダウン条件がサブクエリに遭遇しました
押し下げ条件遭遇機能
非 InnoDB テーブルと MyISAM テーブル

質問 4 : インデックスプッシュダウンをオン/オフにするにはどうすればいいですか?

// インデックスプッシュダウンはデフォルトで有効になっています。set optimizer_switch='index_condition_pushdown=off'; // 無効にする set optimizer_switch='index_condition_pushdown=on'; // 有効にする

要約する

非主キーインデックスのインデックスプッシュダウンの最適化により、テーブルリターンの数を効果的に削減し、クエリの効率を大幅に向上できます。日常業務では、インデックスプッシュダウンを使用して、ビジネス状況に応じてインデックスを最適化し、ビジネススループットを向上させることができます。

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

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