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

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

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

背景

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

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

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

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 が存在し、条件が =、

インデックス フィルター

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

テーブル フィルター

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

概要と補足

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

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 がわかります。インデックス 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 フィールドにないため、フィルターされたインデックスをクエリするにはテーブルを返す必要があります。データがクエリ条件を満たしているかどうかを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 は、左端の一致原則に違反しており、インデックス以外のフィールドのグループ化が含まれているため、一時テーブルが生成されます。

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

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

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