MySQL の結合インデックスと左端一致原則の詳細な説明

序文

これまでインターネットでMySQLジョイントインデックスの最左接頭辞マッチングに関する記事をたくさん見てきましたが、その原理は理解しているつもりでした。最近、面接中に面接官とやりとりしているときに、いくつか見逃していたことが分かりました。ここではこの点について内容を整理します。

複合インデックスはいつ作成すればよいですか?

whereクエリに複数の条件クエリがある場合、クエリ列の複合インデックスを作成する必要があります。

すべての列にインデックスを作成しないのはなぜですか?

経費を削減
カバーインデックス
高効率

オーバーヘッドの削減: col1、col2、col3 の複合インデックスを作成すると、(col1)、(col1、col2)、(col1、col2、col3) の 3 つのインデックスを作成するのと同じになります。カバーインデックス: SELECT col1、col2、col3 FROM テーブル名をクエリすると、クエリ対象のフィールドがインデックスページに存在するため、クエリのためにテーブルに戻らなくても、インデックスから直接取得できます。

高効率: col1、col2、col3 にそれぞれインデックスを作成します。MySQL は、最も認識度の高い列のみをインデックスとして選択します。 100 万のデータがあり、インデックスがデータの 10% をフィルターすると仮定すると、1,000 万のデータをフィルターできます。結合インデックスの場合は、100 万 * 10% * 10% * 10% = 1,000 のデータをフィルターできます。

左端一致原則

(col1、col2、col3) の複合インデックスを作成するとします。これは、col1 列をソートするのと同じです。つまり、左端の列に基づいて複合インデックスを作成します。クエリ条件に左端の列が含まれている限り、クエリはインデックスを使用します。

テストテーブルを作成する

テーブル「学生」を作成（
 `id` int(11) NULLではない、
 `name` varchar(10) NOT NULL,
 `age` int(11) NOT NULL,
 主キー (`id`)、
 キー `idx_id_name_age` (`id`,`name`,`age`)
) エンジン=InnoDB デフォルト文字セット=utf8

100万のテストデータを入力する

ドロップ手順 pro10;
プロシージャ pro10() を作成する
始める
	i INT を宣言します。
	char_str varchar(100) を DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' として宣言します。
	return_str varchar(255) DEFAULT '' を宣言します。
	age INT を宣言します。
	i = 1 に設定します。
	私は5000000未満ですが
		return_str = substring(char_str, FLOOR(1 + RAND()*62), 8 を設定します。
		i = i+1 を設定します。
		年齢 = FLOOR(RAND() * 100) を設定します。
		学生にINSERT INTO(ID, 名前, 年齢) values(i, return_str, 年齢);
	終了しながら;
終わり;

pro10() を呼び出します。

シナリオテスト

EXPLAIN SELECT * FROM student WHERE id = 2;

クエリがインデックスを使用していることがわかります

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk';

クエリがインデックスを使用していることがわかります

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk' and age = 8;

クエリがインデックスを使用していることがわかります

EXPLAIN SELECT * FROM student WHERE id = 2 AND age = 8;

クエリがインデックスを使用していることがわかります

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND age = 8;

クエリはインデックスを使用しておらず、タイプはインデックス、クエリ行数は 4989449 で、ほぼテーブル全体がスキャンされていることがわかります。結合されたインデックスは左端の列のみをソートするため、名前と年齢のみを完全にスキャンできます。

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND id = 2;

EXPLAIN SELECT * FROM student WHERE age = 8 AND id = 2;

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' and age = 8 AND id = 2;

上記のクエリでもインデックスが使用されていることがわかります。ID を先頭または末尾に配置した場合の結果は同じです。MySQL は、最初に ID に基づいてクエリを実行する最も効率的なクエリ方法を見つけます。

要約する

上記のテストに示されているように、クエリ条件内の列に複合インデックスの左端の列が含まれている限り、クエリ条件内の列の位置に関係なく、インデックスがクエリに使用されます。

さて、今回の記事は以上です。この記事の内容が皆さんの勉強や仕事に少しでも参考になれば幸いです。123WORDPRESS.COM をよろしくお願いいたします。

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