MySql 範囲内の検索時にインデックスが有効にならない理由の分析

1 問題の説明

この記事では、確立された複合インデックスをソートし、レコード内の非インデックス フィールドを取得します。インデックスが有効でないことがわかります。たとえば、次のテーブルがあり、DDL ステートメントは次のとおりです。

テーブル「従業員」を作成します（
 `emp_no` int(11) NULLではない、
 `birth_date` 日付がNULLではない、
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `性別` enum('M','F') NOT NULL,
 `hire_date` 日付がNULLではない、
 `age` int(11) NOT NULL,
 主キー (`emp_no`)、
 キー `unique_birth_name` (`first_name`,`last_name`) BTREE の使用
 )ENGINE=InnoDB デフォルト文字セット=utf8;

複合インデックスはunique_birth_name (first_name,last_name)です。次の文を使用します。

説明選択
 性別
から
 従業員
注文する
 ファーストネーム、
 苗字

上図によると、type:all および Extra:Using filesort の場合、インデックスは有効ではありません。

実験を続行し、クエリ ステートメントをさらに書き直して範囲検索を追加します。

説明選択
 性別
から
 従業員
WHERE first_name > 'Leah'
注文する
 ファーストネーム、
 苗字

実行プランは次の図に示されています。

ここでの結果は最初の SQL 分析と変わりません。実験を続けてください。

SQL ステートメントを書き直します。

説明選択
 性別
から
 従業員
WHERE first_name > 'Tzvetan'
注文する
 ファーストネーム、
 苗字

この時点で、驚くべきことに、インデックスは機能します。

2 問題分析

この時点で、私たちは大胆な推測をします。

初めてSQL分析を実行する場合、最初のorder byの後はテーブル全体のデータがまだ取得されるため、複合インデックスに保持されている主キーに従って各性別を検索して結合すると、当然、非常に多くのリソースと時間がかかります。MySQLはそのような愚かなことはしません。テーブル全体を直接スキャンし、スキャンした各データを order by で取得した一時データと連結して、必要なデータを取得する方がよいでしょう。

上記のアイデアの正しさを検証するために、3 つの SQL ステートメントを分析します。

複合インデックスに基づく最初のSQLで取得されるデータの量は300024で、これはテーブル全体のデータです。

選択
 COUNT(名)
から
 従業員
注文する
 ファーストネーム、
 苗字

複合インデックスに基づいて書き換えられた 2 番目の SQL によって取得されるデータ量は159149で、これはテーブル全体のデータ量の 1/2 です。

選択
 COUNT(名)
から
 従業員
WHERE first_name > 'Leah'
注文する
 ファーストネーム、
 苗字

複合インデックスに基づいて書き換えられた 3 番目の SQL によって取得されるデータ量は36731で、これはテーブル全体のデータ量の 1/10 です。

選択
  COUNT(名)
から
  従業員
WHERE first_name > 'Tzvetan'
注文する
  ファーストネーム、
  苗字

比較すると、複合インデックスに基づいて書き換えられた 2 番目の SQL によって取得されたデータ量は、テーブル全体のデータ量の 1/2 であることがわかりました。この時点では、MySQL はまだ二次検索にインデックスを使用するレベルには達していません。複合インデックスを元に書き換えた3番目のSQLで取得するデータ量は、テーブル全体のデータ量の1/10となり、二次検索にインデックスを使ったMySQLのレベルに達しています。そのため、実行プランから書き換えた3番目のSQLはインデックスを使ったことがわかります。

3 結論

MySQL が最初のインデックス条件から照会された主キーに基づいて二次検索を実行するかどうかは、照会されたデータの量によっても異なります。データ量がテーブル全体のデータ量に近い場合は、フルテーブルスキャンが実行されます。それ以外の場合は、最初に照会された主キーに基づいて二次検索が実行されます。

これで、MySql 範囲検索中にインデックスが有効にならない問題の原因分析に関するこの記事は終了です。MySql 範囲検索中にインデックスが有効にならない問題に関する関連コンテンツの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。