MySQL インデックスが失敗した場合はどうすればよいですか?失敗の理由の簡単な分析

この記事は、すべての人のために Mysql インデックスの失敗を記録し、Mysql インデックスの失敗の理由を分析します。すべての人に役立つことを願っています。

この記事には、Mysql の Where 条件クエリの実行プロセス、ジョイント インデックスの一致を停止する範囲クエリ、テーブルの戻り操作の分析、一般的なインデックスの失敗シナリオ、追加の分析、その他の知識が含まれています。 。 [関連する推奨事項: mysql ビデオ チュートリアル ]

背景

データ ボリュームが 6,000 万のデータ テーブルに完全なクエリが表示され、再現されました。 sql ステートメント検出クエリはインデックスを使用せず、テーブル クエリ全体を使用してインデックスの失敗の原因を見つけます。

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

order_recipient_extend_tab テーブルには 6,000 万のデータがあります。低速クエリのクエリ フィールドには、start_date、station_id、status が含まれます。インデックス設計の本来の目的によれば、インデックスは実際に失敗するのは次のとおりです:

#ユニオン インデックスフィールド 1フィールド 2フィールド 3start_datestation_iddriver_id

Where 条件付きクエリの実行プロセス

Mysql が where 条件付きクエリを実行する方法を理解すると、インデックスが失敗する理由を迅速かつ明確に理解できます。この遅いクエリで一致度の高いインデックスは idx_date_station_driver です。この遅いクエリの where 条件クエリの実行プロセスを分析します。

Mysql の where 条件抽出ルールは、次の 3 つの主要なカテゴリに要約できます: インデックス キー (最初のキーと最後のキー)、インデックス フィルター、テーブル フィルター 。

インデックス キー

インデックス キーは、インデックス ツリー上のこの SQL クエリの範囲を決定するために使用されます。範囲には開始と終了が含まれます。Index First Key はインデックス クエリの開始範囲を特定するために使用され、Index Last Key はインデックス クエリの終了範囲を特定するために使用されます。

• インデックスの最初のキー
  抽出ルール: インデックスの最初のフィールドから開始して、フィールドが where 条件に存在するかどうか、フィールドが存在し、条件が=、>=、対応する条件を Index First Key に追加し、インデックスの次のフィールドの読み取りを続けます。フィールドが存在し、条件が > の場合は、対応する条件を Index First Key に追加して、Index First を終了します。 Key.Extract; 存在しない場合は、Index First Key の抽出も終了します。

• Index Last Key
  は Index First Key の逆です。抽出ルール: インデックスの最初のフィールドから開始し、where 条件に存在するかどうかを確認します。 If が存在し、条件が =、

インデックス キーの抽出ルールによると、この遅いクエリで抽出されたインデックスの最終キーは start_date>'1628442000'、インデックスの最終キーは start_dateインデックスの最初のキーは、インデックスの開始範囲を見つけるためにのみ使用されます。インデックス B ツリーのルート ノードから開始してインデックスの最初のキー条件を使用し、バイナリ検索メソッドを使用して、インデックスをすばやく作成します。正しいリーフノードの位置。 Where クエリ プロセス中に、Index First Key は 1 回だけ判断されます。

インデックスの最終キーは、インデックスの終了範囲を特定するために使用されます。したがって、開始範囲の後に読み取られた各インデックス レコードについて、インデックスの最終キーの範囲を超えているかどうかを判断する必要があります。を超えると、現在のクエリが終了します。

インデックス フィルター

インデックス キーによって決定されるインデックス範囲では、すべてのインデックス レコードがクエリ条件を満たしているわけではありません。たとえば、Index Last Key および Index Last Key の範囲では、すべてのインデックス レコードが station_id = '1809' を満たすわけではありません。このとき、インデックスフィルターを使用する必要があります。

インデックス フィルター (インデックス プッシュダウン とも呼ばれます) は、インデックス クエリ範囲内のクエリ条件を満たさないレコードをフィルターするために使用されます 。インデックス範囲内の各レコードについては、インデックス フィルターと比較する必要があります。インデックス フィルターを満たさない場合は、直接破棄され、インデックス内の次のレコードの読み取りが続行されます。

インデックスフィルターの抽出規則: インデックスの最初のフィールドから、where条件に存在するかどうかを確認し、存在し条件が=のみの場合は、最初のフィールドを飛ばして次のフィールドを確認します。インデックスのフィールド。 の場合、次のインデックス列は同じ抽出ルールを採用します (説明: 条件 = を持つフィールドはインデックス キーで除外されています)。それが存在し、条件が >=、>、インデックス フィルターの抽出ルールによると、このスロー クエリで抽出されるインデックス フィルターは station_id='1809' です。インデックス キーによって決定されるインデックス クエリ範囲では、インデックス レコードを走査するときに station_id='1809' を比較する必要があります。この条件が満たされない場合、インデックスは直接失われ、インデックス内の次のレコードが引き続き読み取られます。 。

テーブル フィルター

テーブル フィルターは、インデックスでフィルターできないデータをフィルターで除外するために使用されます。レコードの行全体が主キーを介してセカンダリ インデックスでクエリされた後、レコードがテーブル フィルターの条件を満たすかどうかが判断されます。条件を満たさない場合は、失われると、次の記録が引き続き審査されます。

抽出ルールは非常に単純です。インデックス フィールドに属さないすべてのクエリ条件はテーブル フィルターに分類されます。テーブル フィルターの抽出ルールによると、このクエリのテーブル フィルターは status=‘2’ となります。

概要と補足

インデックス キーはインデックス スキャンの範囲を決定するために使用されます。インデックス フィルターはインデックス内のフィルターに使用されます。テーブル フィルターは返される必要があります。 Mysql サーバー上のテーブルにコピーしてフィルタリングします。

インデックス キーとインデックス フィルターは InnoDB ストレージ レイヤーで発生し、テーブル フィルターは Mysql Server レイヤーで発生します。

MySQL5.6 より前では、インデックス フィルターとテーブル フィルターの区別がなく、インデックスの最初のキーとインデックスの最後のキーの範囲内のすべてのインデックス レコードがテーブルに返されて完全なレコードを読み取ってから返されていました。処理のために MySQL Server 層に送信されます。

MySQL 5.6 以降では、インデックス フィルターはテーブル フィルターから分離されています。インデックス フィルターはフィルタリングのために InnoDB のストレージ エンジン層にドロップされ、テーブルを返したりレコードを MySQL Server 層に返す際の対話オーバーヘッドが軽減されます。 SQLの実行効率が向上します。

インデックス失敗の原因を分析する

最初は count() です。この時点では、ワイルドカード * は最適化後にすべての列を展開するわけではありません。実際にはすべて無視されます。列は行数を直接カウントします。したがって、行数のみを収集したい場合は、count() を使用するのが最善です。

次に、where ステートメントを分析します。この遅いクエリはセカンダリ インデックス idx_date_station_driver を使用すると仮定します。上記の where 条件クエリの実行プロセスによると、遅いクエリのインデックスの最初のキーは start_date>'1628442000'、インデックスの最後のキーは: start_date

Index First Key を抽出した後、インデックス B ツリー上でインデックス開始範囲を見つけるのが、インデックス マッチングのプロセスです。。インデックス B ツリー上で二分検索メソッドを使用して、インデックス B ツリー上でインデックス開始範囲をすばやく見つけます。クエリ条件を満たす開始範囲 リーフノード。上記の Where 条件クエリの実行プロセスを通じて、スロー クエリの where 条件 (start_date>'1628442000' および start_date がわかります。インデックス <code>idx_date_station_driver(start_date, station_id, driver_id) の最初のフィールドのみに一致します。つまり、idx_date_station_driver(start_date) のみに一致します。 station_id='1809' の正確なクエリは一致に影響しません。インデックスでは、インデックス フィルター、つまりインデックス プッシュダウン プロセスで役割を果たします。実際、これは、 範囲クエリにより、ジョイント インデックスが と一致しなくなるためです。

範囲クエリによりジョイント インデックスの一致が停止します

範囲クエリによりジョイント インデックスの一致が停止するのはなぜですか?これには、左端のプレフィックス マッチング原則が含まれます。結合インデックス Index(a, b) が確立されていると仮定すると、次の図に示すように、a が最初にソートされ、a が等しい場合は b がソートされます。このインデックス ツリーでは、a はグローバルに順序付けされていますが、b はグローバルに順序付けされておらず、ローカルに順序付けされている状態にあります。グローバルな観点から見ると、 b の値は 1、2、1、4、1、2 ですが、b=2 クエリ条件だけがこのインデックスを直接使用できません。ローカルな観点から見ると、 a は決定され、b は順序付けられた状態です。a=2 && b=4 はこのインデックスを使用できます。したがって、範囲クエリによってジョイント インデックスの一致が停止する根本的な理由は、インデックス ツリー上の先頭以外のフィールドの順序付けされた状態が前のフィールドの同等性に依存し、range クエリによってローカルの順序付けされた状態が破壊されるためです。次のインデックス フィールドの状態、インデックスの一致を停止します。

Range クエリはジョイント インデックスの一致を停止し、インデックスが一致したときに station_id が '1809' に等しくないデータをフィルタリングできません。その結果、インデックス上の Mysql スキャン範囲が発生します。インデックスの最初のキーとインデックスの最後のキーは、start_timestamp_of_date 時刻によって完全に決定されます。 start_timestamp_of_date 範囲クエリではデータ ボリュームの 73% をフィルタリングできますが、station_id='1809' の正確なクエリではデータ ボリュームの 99% をフィルタリングできます。

idx_date_station_driver

#クエリ条件データ量パーセンテージすべてのデータ6,367万100%start_timestamp_of_date>'1628442000'およびstart_timestamp_of_date1742万27.35%station_id='1809'80,0000.16%

テーブル戻り操作のオーバーヘッド

ステータス フィールドはインデックス idx_date_station_driver フィールドにないため、フィルターされたインデックスをクエリするにはテーブルを返す必要があります。データがクエリ条件を満たしているかどうかをMysqlサービス層で判断します。

Mysql のオプティマイザは、SQL ステートメントの実行時に、一致度の高いインデックス作成のコストを最初に見積もります。インデックス作成のコストがテーブル全体の検索よりも大きい場合、Mysql はテーブル全体のスキャンを選択します。この結論は直感に反するかもしれませんが、私たちの印象では、インデックスはクエリの効率を向上させるために使用されます。ここには主に 2 つの要素が関係します。

• クエリ条件または検索対象のフィールドがセカンダリ インデックスのフィールドにない場合、テーブルの戻り操作が実行されます。 : セカンダリ インデックスの主キー インデックス。

• ディスクのランダム I/O のパフォーマンスは、シーケンシャル I/O よりも低くなります。テーブル リターン クエリは主キー インデックスに対するランダム I/O であり、フル テーブル スキャンは主キー インデックスに対する順次 I/O です。

#テーブルの戻り操作のコストがインデックスの失敗の直接の原因であるかどうかを分析する実験を行いますか?

status='0' クエリ条件を削除し、クエリでインデックス idx_date_station_driver が使用されているかどうかを確認します。結果は下図のようになり、テーブル返却処理のオーバヘッドが軽減され、インデクスも無効になりません。

概要

上記の分析と組み合わせると、インデックスの失敗の理由が要約されます。範囲クエリによって結合インデックスが作成されるということです。マッチングを停止し、インデックスがフィルタリングされたデータと一致するだけでは十分ではないため、MySQL オプティマイザは、テーブル フィルタのテーブルを返す操作のコストが完全なテーブル クエリのコストよりも大きいと推定し、そのため完全なテーブル クエリが選択されました。結合インデックスの一致を停止させる範囲クエリがインデックス障害の原因であり、テーブルの戻り操作のコストがインデックス障害の直接の原因です。

インデックスの最適化

クエリ インデックスが遅い原因は、範囲クエリによって結合インデックスの一致が停止することです。調整する必要があるのは、範囲クエリを正確なクエリのフィールドに追加します。後で、

共同インデックスidx_date_station_driver(start_date, station_id, driver_id) が idx_station_date_driver(station_id, start_date, driver_id) に変更されます。 。最適化された結果を下の図に示します。

#拡張

インデックス障害の一般的なシナリオ

• 左端のプレフィックス マッチング原則に違反します。たとえば、インデックス Index(a,b) がありますが、クエリ条件には b フィールドしかありません。

• インデックス列に対して、計算、関数、型変換などのあらゆる操作を実行します。

• 範囲クエリにより、ユニオン インデックスの一致が停止します。

• select* の使用を減らします。不要なテーブルの戻り操作のオーバーヘッドを回避するには、カバーインデックスを使用するようにしてください。

• 等しくない (!=, ) を使用し、or 演算を使用します。

#一重引用符のない文字列インデックスは無効です。
like はワイルドカード '�c' で始まります。 「abc%」のようにインデックスを作成できることに注意してください。
order by は左端の一致原則に違反し、インデックス以外のフィールドの並べ替えが含まれるため、ファイルの並べ替えが行われます。
group by は、左端の一致原則に違反しており、インデックス以外のフィールドのグループ化が含まれているため、一時テーブルが生成されます。

Explain 分析

遅いクエリの分析は、mysql Explain ステートメントと切り離すことができません。Explain では、主に Type と Extra の 2 つのフィールドに焦点を当てます。

Type はデータにアクセスする方法を表し、Extra はデータをフィルターして整理する方法を表します。簡単に検索できるようにここにリストされています。

#タイプ##追加ALLフル テーブル スキャンインデックス ツリーのフル スキャンインデックス ツリー範囲スキャン非一意インデックスや一意インデックスの非一意プレフィックスなど、非一意インデックス スキャン一意のインデックス スキャン (一意のインデックスなど)および主キー インデックスクエリの変換#filesort の使用NULLテーブルに返信プログラミング関連の知識の詳細については、をご覧ください。 ！

インデックスの使用	カバリング インデックスを使用します。テーブルを返す必要がなく、Mysql サービス レイヤーのフィルタリングも必要ありません。		index
where の使用	ストレージ エンジン層からデータを取得し、where を使用します。 Mysql サービス層のクエリ条件 データをフィルタリングします。		#range
どこを使用するか、インデックスを使用する	インデックス範囲スキャン。インデックス スキャンはフル テーブル スキャンに似ていますが、異なるレベルで実行されます。		ref
インデックス条件の使用	インデックス プッシュダウンを使用して、クエリ インデックス フィールドを最大限に活用し、ストレージ エンジン層でデータをフィルター処理します		eq_ref
一時テーブルの使用	一時テーブルには結果が保存され、クエリの並べ替えとグループ化に使用されます		const
##ファイルの並べ替え、並べ替えに使用		#テーブルやインデックスにアクセスする必要はありません	NULL
		プログラミング入門

以上がMySQL インデックスが失敗した場合はどうすればよいですか?失敗の理由の簡単な分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。