mysql インデックスのマージ: 1 つの SQL で複数のインデックスを使用できます

regrupting転載時にソースを示してください：MySQLインデックスの合併：1つのSQLが複数のインデックスを使用できます

はじめに

MySQL のインデックスのマージは新しい機能ではありません。 mysql5.0 バージョンから実装されています。私が今でもこのブログ記事を書いている理由は、多くの人が SQL ステートメントで使用できるインデックスは 1 つだけであるという誤解をまだ持っているからです。この記事では、いくつかの例を通してインデックスの結合の使用方法を説明します。

インデックスマージとは何ですか

MySQL ドキュメントのインデックス マージの説明を見てみましょう:

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. 
The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. 
This access method merges index scans from a single table; 
it does not merge scans across multiple tables.

1. インデックス マージとは、複数のインデックスの範囲スキャンを 1 つのインデックスにマージすることです。

2. インデックスを結合する場合、インデックスは最初に結合、交差、または交差してから結合されて 1 つのインデックスになります。
3. マージする必要があるこれらのインデックスは 1 つのテーブルにのみ所属できます。複数のテーブルに対してインデックスのマージを実行することはできません。

インデックスの結合を使用する利点は何ですか

簡単に言えば、インデックスのマージにより、1 つの SQL で複数のインデックスを使用できるようになります。最初に交差、和集合、または交差を取得し、次にこれらのインデックスの和集合を取得します。これにより、データ テーブルからデータを取得する回数が減り、クエリの効率が向上します。

インデックスマージが使用されていることを確認する方法

Explainを使用してSQL文を操作する場合、インデクスのマージを使用している場合、出力内容のtype列にはindex_mergeが表示され、key列には使用されているすべてのインデクスが表示されます。以下の通り:

Explain の追加フィールドには以下の型があります:

union Index を使用して共用体を取得する
sort_union を使用して、取得したデータを最初に ROWID でソートし、次に共用体を取得する
intersect を使用してインデックスの共通部分を取得する

現在の実装によれば、インデックスによって交差を取得する場合は、インデックスを通じて取得されるデータの順序が ROWID の順序と一致していることを確認する必要があるため、sort_intersect がないことがわかります。したがって、並べ替える必要はありません。

sort_union インデックスのマージの例

データテーブル構造

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1_part1` int(11) NOT NULL DEFAULT '0', `key1_part2` int(11) NOT NULL DEFAULT '0', `key2_part1` int(11) NOT NULL DEFAULT '0', `key2_part2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `key1` (`key1_part1`,`key1_part2`), KEY `key2` (`key2_part1`,`key2_part2`) ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

データ

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

mysql> select * from test; +----+------------+------------+------------+------------+ | id | key1_part1 | key1_part2 | key2_part1 | key2_part2 | +----+------------+------------+------------+------------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 2 | 1 | | 3 | 1 | 1 | 2 | 2 | | 4 | 1 | 1 | 3 | 2 | | 5 | 1 | 1 | 3 | 3 | | 6 | 1 | 1 | 4 | 3 | | 7 | 1 | 1 | 4 | 4 | | 8 | 1 | 1 | 5 | 4 | | 9 | 1 | 1 | 5 | 5 | | 10 | 2 | 1 | 1 | 1 | | 11 | 2 | 2 | 1 | 1 | | 12 | 3 | 2 | 1 | 1 | | 13 | 3 | 3 | 1 | 1 | | 14 | 4 | 3 | 1 | 1 | | 15 | 4 | 4 | 1 | 1 | | 16 | 5 | 4 | 1 | 1 | | 17 | 5 | 5 | 1 | 1 | | 18 | 5 | 5 | 3 | 3 | | 19 | 5 | 5 | 3 | 1 | | 20 | 5 | 5 | 3 | 2 | | 21 | 5 | 5 | 3 | 4 | | 22 | 6 | 6 | 3 | 3 | | 23 | 6 | 6 | 3 | 4 | | 24 | 6 | 6 | 3 | 5 | | 25 | 6 | 6 | 3 | 6 | | 26 | 6 | 6 | 3 | 7 | | 27 | 1 | 1 | 3 | 6 | | 28 | 1 | 2 | 3 | 6 | | 29 | 1 | 3 | 3 | 6 | +----+------------+------------+------------+------------+ 29 rows in set (0.00 sec)

使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: index_merge possible_keys: key1,key2 key: key1,key2 key_len: 8,4 ref: NULL rows: 3 Extra: Using sort_union(key1,key2); Using where 1 row in set (0.00 sec)

未使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=1 and key1_part2=1) or key2_part1=4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ALL possible_keys: key1,key2 key: NULL key_len: NULL ref: NULL rows: 29 Extra: Using where 1 row in set (0.00 sec)

sort_union の概要

上記 2 つのケースから、同じモードの SQL 文でもインデックスが使用できる場合と使用できない場合があることがわかります。インデックスを使用できるかどうかは、統計データを分析した後、MySQL クエリ オプティマイザーがインデックスを使用した方が高速であると判断するかどうかによって決まります。
したがって、SQL ステートメントでインデックスを使用できるかどうかを単純に議論するのは少し一方的であり、データについても考慮する必要があります。

ユニオンインデックスマージの使用例

データテーブル構造

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1_part1` int(11) NOT NULL DEFAULT '0', `key1_part2` int(11) NOT NULL DEFAULT '0', `key2_part1` int(11) NOT NULL DEFAULT '0', `key2_part2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `key1` (`key1_part1`,`key1_part2`,`id`), KEY `key2` (`key2_part1`,`key2_part2`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

数据结构和之前有所调整。主要调整有如下两方面：
1、引擎从myisam改为了innodb。
2、组合索引中增加了id，并把id放在最后。

数据

数据和上面的数据一样。

使用索引合并的案例

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or (key2_part1=4 and key2_part2=4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: index_merge possible_keys: key1,key2 key: key1,key2 key_len: 8,8 ref: NULL rows: 2 Extra: Using union(key1,key2); Using where 1 row in set (0.00 sec)

組合概要

同じデータ、同じ SQL ステートメントですが、データ テーブルの構造が sort_union から Union に調整されています。理由はいくつかあります。
1. インデックスを通じて取得されたデータが ROWID によってソートされている限り、Union を使用できます。
2. 結合されたインデックスの最後に id フィールドを追加します。目的は、インデックスの最初の 2 つのフィールドによって取得されたデータを ID で並べ替えることです。
3. エンジンを myisam から innodb に変更します。目的は、ID と ROWID の順序を一致させることです。

交差ユースケース

mysql インデックスのマージ: 1 つの SQL で複数のインデックスを使用できます
http://www.php.cn/

上記は mysql インデックスのマージの内容です: 1 つの SQL で複数のインデックスを使用できます。 PHP 中国語 Web サイト (www.php.cn) のコンテンツに注目してください。