Zusammenführen von MySQL-Indizes: Ein SQL kann mehrere Indizes verwenden

Bitte geben Sie die Quelle für den Nachdruck an: MySQL-Indexzusammenführung: Ein SQL kann mehrere Indizes verwenden

Vorwort

Die Indexzusammenführung von MySQL ist keine neue Funktion. Es wurde bereits in der MySQL5.0-Version implementiert. Der Grund, warum ich diesen Blogbeitrag immer noch schreibe, liegt darin, dass viele Leute immer noch die falsche Vorstellung haben, dass eine SQL-Anweisung nur einen Index verwenden kann. In diesem Artikel wird anhand einiger Beispiele veranschaulicht, wie die Indexzusammenführung verwendet wird.

Was ist Indexzusammenführung?

Werfen wir einen Blick auf die Beschreibung der Indexzusammenführung im MySQL-Dokument:

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. Bei der Indexzusammenführung werden die Bereichsscans mehrerer Indizes in einem Index zusammengeführt.
2. Beim Zusammenführen von Indizes werden die Indizes zuerst kombiniert, überschnitten oder überschnitten und dann zu einem Index zusammengeführt.
3. Die Indizes, die zusammengeführt werden müssen, können nur zu einer Tabelle gehören. Die Indexzusammenführung kann nicht für mehrere Tabellen durchgeführt werden.

Welche Vorteile bietet die Indexzusammenführung?

Einfach ausgedrückt ermöglicht die Indexzusammenführung, dass ein SQL mehrere Indizes verwendet. Nehmen Sie zuerst den Schnittpunkt, die Vereinigung oder den Schnittpunkt und dann die Vereinigung dieser Indizes. Dies reduziert die Häufigkeit, mit der Daten aus der Datentabelle abgerufen werden müssen, und verbessert die Abfrageeffizienz.

So bestätigen Sie, dass die Indexzusammenführung verwendet wird

Wenn Sie EXPLAIN zum Betreiben einer SQL-Anweisung verwenden und die Indexzusammenführung verwenden, wird index_merge in der Typspalte des Ausgabeinhalts und alle verwendeten Indizes in der Schlüsselspalte angezeigt. Wie folgt:

Es gibt die folgenden Typen im zusätzlichen Feld von EXPLAIN:
Verwenden Sie den Union-Index, um die Union zu erhalten.
Verwenden Sie sort_union, um die abgerufenen Daten zuerst nach Zeilen-ID zu sortieren, und erhalten Sie dann die Union.
Verwenden Sie intersect, um die Schnittmenge zu erhalten

Sie werden feststellen, dass es keinen sort_intersect gibt, da Sie gemäß der aktuellen Implementierung, wenn Sie den Schnittpunkt nach Index abrufen möchten, sicherstellen müssen, dass die Reihenfolge der über den Index abgerufenen Daten mit der Zeilen-ID-Reihenfolge übereinstimmt. Daher ist keine Sortierung erforderlich.

Beispiel für die Zusammenführung des sort_union-Index

Datentabellenstruktur

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)

Daten

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-Zusammenfassung

Aus den beiden oben genannten Fällen können Sie feststellen, dass die SQL-Anweisungen desselben Modus manchmal Indizes verwenden können und manchmal möglicherweise keine Indizes verwenden können. Ob der Index verwendet werden kann, hängt davon ab, ob der MySQL-Abfrageoptimierer der Meinung ist, dass die Verwendung des Index nach der Analyse der statistischen Daten schneller ist.
Daher ist die bloße Diskussion darüber, ob eine SQL-Anweisung Indizes verwenden kann, etwas einseitig und es müssen auch die Daten berücksichtigt werden.

Anwendungsfall zum Zusammenführen von Unionsindizes

Datentabellenstruktur

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)

Gewerkschaftszusammenfassung

Dieselben Daten, dieselbe SQL-Anweisung, aber die Datentabellenstruktur wurde angepasst, von sort_union zu union. Dafür gibt es mehrere Gründe:
1. Solange die über den Index abgerufenen Daten nach Zeilen-ID sortiert wurden, kann Union verwendet werden.
2. Fügen Sie das ID-Feld am Ende des kombinierten Index hinzu. Der Zweck besteht darin, die von den ersten beiden Feldern des Index abgerufenen Daten nach ID zu sortieren.
3. Ändern Sie die Engine von myisam zu innodb. Der Zweck besteht darin, die Reihenfolge von id und rowid konsistent zu machen.

Anwendungsfall überschneiden

MySQL-Index-Zusammenführung: Ein SQL kann mehrere Indizes verwenden
http://www.php.cn/

Das Obige ist die MySQL-Index-Zusammenführung :Ein SQL kann den Inhalt mehrerer Indizes verwenden. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!