この SQL 書き込み方法では本当にインデックスが失敗するのでしょうか?

序文

インターネット上には、MySQL でインデックスにヒットできないさまざまな状況をまとめた記事がよくあります。その 1 つは、またはを使用するステートメントがインデックスにヒットできないというものです。

この記述は実際には正しくありません。正しい結論は、MySQL 5.0 以降では、またはで接続されたフィールドに独立したインデックスがある場合、そのインデックスにヒットできるということです。ここで、index_merge 機能が使用されます。

MySQL 5.0 より前では、SQL ステートメントは 1 つのインデックスしか使用できません。SQL ステートメントで or キーワードを使用すると、既存のインデックスは無効になり、テーブル全体のスキャンが実行されます。どのインデックスを使用しても、MySQL は条件を満たすデータを一度に見つけることができないため、インデックスを放棄することしかできません。

MySQL も継続的にアップグレードおよび更新されており、MySQL バージョン 5.0 以降では index_merge インデックスマージ機能が追加され、1 つの SQL で複数のインデックスを使用することも可能になりました。

index_merge の基本的な考え方は、まず単一のインデックスを使用して要件を満たすデータを見つけ、次にそれらのデータを結合して返すことです。
例を見てみましょう。

ここでも前回の記事で作成したテーブルとテストデータを使用します。10w のテストデータがテーブルに挿入されます。テーブル構造は次のとおりです。

テーブル `t` を作成します (
 `id` int(11) NULLではない、
 `a` int(11) デフォルト NULL,
 `b` int(11) デフォルト NULL,
 主キー (`id`)
)ENGINE=InnoDB;

まず a フィールドにインデックスを追加し、次にまたはを使用してクエリステートメントを実行して、それがどのように機能するかを確認しましょう。

mysql> テーブル t を変更し、インデックス a_index(a) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.17 秒)
レコード: 0 重複: 0 警告: 0

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+
| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | where の使用 |
+----+-------------+-------+-------+---------------+-------+--------+---------+------------+------------+
セット内の 1 行 (0.00 秒)

フィールド b にはインデックスがないため、MySQL はテーブルを返すプロセスを回避できるため、完全なテーブルスキャンの方がコストがかからないと判断します。

次に、フィールド b にインデックスを追加して、SQL ステートメントを再度実行します。

mysql> テーブル t を変更し、インデックス b_index(b) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.17 秒)
レコード: 0 重複: 0 警告: 0

mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------------+------------+-----------------+--------+------+------+-------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------------+------------+-----------------+--------+------+------+-------------------------------------------+
| 1 | SIMPLE | t | index_merge | a_index,b_index | a_index,b_index | 5,5 | NULL | 2 | union(a_index,b_index); を使用する where を使用する |
+----+-------------+-------------+------------+-----------------+--------+------+------+-------------------------------------------+
セット内の 1 行 (0.00 秒)

今回は、MySQL がインデックス a と b の両方を使用し、type フィールドの値が index_merge であることがわかります。

次に、別の SQL ステートメントを見て、結果がどうなるかを確認してみましょう。

mysql> explain select a from t where a>100 or b>6000;
+----+-------------+-------+-------+-----------------+-------+--------+--------+-----------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+-----------------+-------+--------+--------+-----------+------------+
| 1 | SIMPLE | t | ALL | a_index,b_index | NULL | NULL | NULL | 100332 | where の使用 |
+----+-------------+-------+-------+-----------------+-------+--------+--------+-----------+------------+
セット内の 1 行 (0.00 秒)

この SQL 文は、等号を大なり記号に変更するだけなので、返される結果セットは間隔セットになります。MySQL はここでインデックスを放棄し、テーブル全体のスキャンを実行します。ただし、いくつかの記事で、この問題は MySQL バージョン 5.7 以降で最適化されている、つまり、間隔クエリでも index_merge がサポートされていると読みました。私のバージョンは 5.6 で、この最適化はまだ検証していません。興味があれば、検証してみてください。

実際、MySQL には絶対的なことがたくさんあります。MySQL のバージョンが異なれば、同じ SQL に対しても内部処理方法が異なる場合があります。同時に、MySQL は継続的に最適化およびアップグレードされており、古い知識ポイントの一部は簡単に適用できなくなることがわかります。

この記事が皆さんのお役に立てば幸いです。フォローして「いいね！」していただければ、私にとって最高のサポートになります。ありがとうございます。

また、MySQL の基盤となるデータ構造については、私が以前に書いた他の記事を参照すると、この記事の理解に役立つかもしれません。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

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