Detaillierte Untersuchung des MySQL-Verbundindex

Ein zusammengesetzter Index (auch gemeinsamer Index genannt) ist ein Index, der auf Grundlage mehrerer Spalten erstellt wird. Das Wichtigste beim Erstellen eines zusammengesetzten Indexes ist die Wahl der Spaltenreihenfolge, die sich darauf auswirkt, ob der Index verwendet werden kann oder wie viele Prädikatbedingungen den Index verwenden können. Die Verwendung zusammengesetzter Indizes folgt dem Prinzip der Übereinstimmung ganz links. Nur wenn die Spalten links vom Index übereinstimmen, können die nachfolgenden Spalten weiterhin übereinstimmen. In diesem Artikel wird hauptsächlich die Erstellungsreihenfolge und Verwendung zusammengesetzter Indizes untersucht.

1. Konzept des zusammengesetzten Index

Ein für eine einzelne Spalte erstellter Index wird als einspaltiger Index bezeichnet, und ein für zwei oder mehr Spalten erstellter Index wird als zusammengesetzter Index bezeichnet. Es ist relativ einfach, einen Index für eine einzelne Spalte zu erstellen. Normalerweise müssen Sie nur die Selektivität der Spalte berücksichtigen. Je besser die Selektivität, desto stärker sind die Daten gestreut und desto besser ist die Leistung des erstellten Indexes. Normalerweise lautet die Formel zur Berechnung der Selektivität einer Säule:
Selektivität = Anzahl der Datensätze, die nach Anwendung der Prädikatbedingung zurückgegeben werden / Anzahl der Datensätze, die ohne Anwendung der Prädikatbedingung zurückgegeben werden. Der Wertebereich der Selektivität beträgt (0,1). Je kleiner der Wert, desto besser die Selektivität.
Ein zusammengesetzter Index (auch gemeinsamer Index genannt) ist ein Index, der auf Grundlage mehrerer Spalten erstellt wird. Das Wichtigste beim Erstellen eines zusammengesetzten Indexes ist die Wahl der Spaltenreihenfolge, die sich darauf auswirkt, ob der Index verwendet werden kann oder wie viele Prädikatbedingungen den Index verwenden können. Die Verwendung zusammengesetzter Indizes folgt dem Prinzip der Übereinstimmung ganz links. Nur wenn die Spalten links vom Index übereinstimmen, können die nachfolgenden Spalten weiterhin übereinstimmen.

2. Wann werden zusammengesetzte Indexspalten verwendet?

Zusammengesetzte Indizes folgen dem Übereinstimmungsprinzip ganz links. Nur wenn die ganz linke Spalte im Index übereinstimmt, kann die nächste Spalte übereinstimmen. Wenn die linke Spalte für Abfragen ungleicher Werte verwendet wird, wird die Spalte auf der rechten Seite des Index nicht für Abfragen oder Sortierungen verwendet.

Experiment: Wann werden zusammengesetzte Indizes verwendet?

Welche Felder im zusammengesetzten Index verwendet werden, ist eine Frage, die uns große Sorgen bereitet. Ein klassisches Beispiel im Internet:

-- Erstellen Sie eine Testtabelle CREATE TABLE t1(
c1 CHAR(1) ungleich null,
c2 CHAR(1) ungleich null,
c3 CHAR(1) ungleich null,
c4 CHAR(1) ungleich null,
c5 CHAR(1) nicht null
)ENGINE innodb CHARSET UTF8;

-- Index hinzufügen, Tabelle ändern, t1, Index hinzufügen, idx_c1234(c1,c2,c3,c4);

--Testdaten einfügen in t1-Werte einfügen('1','1','1','1','1'),('2','2','2','2','2'),
('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5','5');

Sie müssen untersuchen, welche der folgenden Abfrageanweisungen den Index idx_c1234 verwenden und welche Felder des Index verwendet werden.

(A) wobei c1=? und c2=? und c4>? und c3=?

(B) wobei c1=? und c2=? und c4=? sortiert nach c3

(C) wobei c1=? und c4=? nach c3,c2 gruppieren

(D) wobei c1=? und c5=? sortiert nach c2,c3

(E) wobei c1=? und c2=? und c5=?, sortiert nach c2,c3

(F) wobei c1>? und c2=? und c4>? und c3=?

Option A:

mysql> erklären Sie „select c1,c2,c3,c4,c5 from t1“, wobei c1='2' und c2='2' und c4>'1' und c3='2';
+----+----------+-------+---------+-----------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+----------+-----------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+---------+-----------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | Bereich | idx_c1234 | idx_c1234 | 12 | NULL | 1 | 100,00 | Indexbedingung wird verwendet |
+----+----------+-------+---------+-----------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+----------+-----------------------+

Die verwendete Indexlänge beträgt 12, was bedeutet, dass alle vier Felder den Index verwenden. Da es sich bei c1, c2 und c3 um gleichwertige Abfragen handelt, kann auch die folgende Spalte c4 verwendet werden.

Hinweis: Bei der UTF-8-Kodierung beträgt die Länge eines Indexes 3. 12 bedeutet hier, dass alle vier Felder diesen Index verwenden.

Option B:

mysql> erklären Sie „select c1,c2,c3,c4,c5 from t1“, wobei c1='2' und c2='2' und c4='2', sortiert nach c3;
+----+----------+----------+---------+------+---------------+---------------+---------------+-------------+------+----------+----------+----------+----------+-----------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+---------------+--------------+-----------+-------------+----------+----------+----------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 1 | 20.00 | Indexbedingung verwenden |
+----+----------+----------+---------+------+---------------+---------------+--------------+-----------+-------------+----------+----------+----------+----------+-----------------------+

Die verwendete Indexlänge beträgt 6, was bedeutet, dass zwei Felder den Index verwenden. Gemäß dem Prinzip der äußersten linken Verwendung verwenden c1 und c2 Indizes. Da in der Abfrage keine Prädikatbedingung c3 vorhanden ist, wird der Indexwert nach der Verwendung von c2 unterbrochen, was dazu führt, dass nur die Spalten c1 und c2 verwendet werden. Das SQL verwendet hier die Reihenfolge durch Sortierung, aber im zusätzlichen Teil des Ausführungsplans gibt es kein Schlüsselwort „filesort“, was bedeutet, dass die Daten in der Reihenfolge des Felds c3 im Index gelesen werden können.

Beachten Sie, dass, obwohl das Feld c3 im Index nicht am Ende des Index platziert wird, die geordnete Natur des Felds c2 im Index tatsächlich verwendet wird, da das Schlüsselwort „fileasort“ nicht im Extra-Teil des Ausführungsplans erscheint. Warum ist das so? Hier kommt die in MySQL 5.6 eingeführte Index Condition Pushdown (ICP)-Optimierung zum Einsatz. Die Kernidee besteht darin, die Felder im Index zum Filtern von Daten zu verwenden. Lassen Sie uns die Unterschiede zwischen der Verwendung von ICP und der Nichtverwendung von ICP klären:

Wenn keine ICP-Optimierung verwendet wird, lauten die SQL-Ausführungsschritte wie folgt:

1. Verwenden Sie die Indexspalten c1 und c2, um Zeilendaten zu erhalten, die die Bedingungen erfüllen. wobei c1='2' und c2='2'

2. Kehren Sie zur Tabelle zurück, um Daten abzufragen, und verwenden Sie where c4='2', um Daten zu filtern

3. Daten sortieren und ausgeben

Wenn die ICP-Optimierung verwendet wird, lauten die SQL-Ausführungsschritte wie folgt:

1. Verwenden Sie die Indexspalten c1 und c2, um Zeilendaten zu erhalten, die die Bedingungen erfüllen. wobei c1='2' und c2='2'

2. Verwenden Sie where c4='2' im Index, um Daten zu filtern

3. Da die Daten geordnet sind, rufen Sie die Daten, die die Bedingungen erfüllen, direkt in der richtigen Reihenfolge ab

Option C:

mysql> erklären Sie „select c2,c3 from t1 where c1='2' and c4='2' group by c3,c2;“
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+---------+----------+----------+----------+----------+----------+-----------------------------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+---------+----------+----------+----------+----------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Where verwenden; Index verwenden; Temporär verwenden; Filesort verwenden |
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+---------+----------+----------+----------+----------+----------+-----------------------------------------------------------+

Die verwendete Indexlänge beträgt 3, was bedeutet, dass ein Feld den Index verwendet. Gemäß dem Leftmost-Use-Prinzip verwendet c1 den Index. Da in der Abfrage keine Prädikatbedingung c2 vorhanden ist, wird der Indexwert nach der Verwendung von c1 unterbrochen, was dazu führt, dass nur die Spalte c1 verwendet wird. Der SQL-Ausführungsprozess ist:

1. Verwenden Sie den Index in Spalte c1, um alle Zeilen zu finden, in denen c1 = '2' ist. Kehren Sie dann zur Tabelle zurück und verwenden Sie c4 = '2', um nicht übereinstimmende Daten herauszufiltern.
2. Sortieren Sie basierend auf den Ergebnissen des vorherigen Schritts c3 und c2 in den Ergebnissen, um kontinuierlich wechselnde Daten zu erhalten. Erstellen Sie gleichzeitig eine temporäre Tabelle in der Datenbank, um die Ergebnisse der Gruppierung zu speichern.

C-Optionserweiterungen:

mysql> erklären Sie „select c2,c3 from t1 where c1='2' and c4='2' group by c2,c3;“
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+----------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+----------+----------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Verwenden von where; Verwenden von index |
+----+----------+-------+------------+------+---------------+-----------+-----------+-----------+---------+-------+---------+----------+----------+----------+-------------+

Die verwendete Indexlänge beträgt 3, was bedeutet, dass ein Feld den Index verwendet. Gemäß dem Leftmost-Use-Prinzip verwendet c1 den Index.

Option D:

mysql> erklären Sie „select c2,c3 from t1“, wobei c1='2' und c5='2', sortiert nach c2,c3;
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Indexbedingung verwenden; Where verwenden |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+------------------------------------+

Die verwendete Indexlänge beträgt 3, was bedeutet, dass alle Felder den Index verwenden. Gemäß dem Leftmost-Use-Prinzip verwendet c1 den Index. Da in der Abfrage keine Prädikatbedingung c2 vorhanden ist, wird der Indexwert nach der Verwendung von c1 unterbrochen, was dazu führt, dass nur die Spalte c1 verwendet wird.

D-Optionserweiterung:

mysql> erklären Sie „select c2,c3 from t1“, wobei c1='2' und c5='2', sortiert nach c3,c2;
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+----------------------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | Indexbedingung verwenden; Where verwenden; Filesort verwenden |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+-----------+---------+----------+----------+----------+----------------------------------------------------+

Die verwendete Indexlänge beträgt 3, was bedeutet, dass alle Felder den Index verwenden. Gemäß dem Leftmost-Use-Prinzip verwendet c1 den Index. Da in der Abfrage keine Prädikatbedingung c2 vorhanden ist, wird der Indexwert nach der Verwendung von c1 unterbrochen, was dazu führt, dass nur die Spalte c1 verwendet wird.

Option E:

mysql> erklären Sie „select c1,c2,c3,c4,c5 from t1“, wobei c1='2' und c2='2' und c5='2', sortiert nach c2,c3;
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+----------+----------+----------+------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+----------+----------+----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 2 | 14.29 | Indexbedingung verwenden; Where verwenden |
+----+----------+----------+---------+------+---------------+-----------+-----------+-----------+---------+----------+----------+----------+------------------------------------+

Die verwendete Indexlänge beträgt 6, was bedeutet, dass beide Felder Indizes verwenden. Gemäß dem Prinzip der äußersten linken Verwendung verwenden c1 und c2 Indizes. Hier verwendet das SQL die Sortierung nach Reihenfolge, aber im Extra-Teil des Ausführungsplans gibt es kein Schlüsselwort „filesort“, was bedeutet, dass die Daten in der Reihenfolge des Felds c3 im Index gelesen werden können (c2 ist eine Konstante).

Option F:

mysql> erläutern Sie „Select c1,c2,c3,c4,c5 from t1“, wobei c1>„4“ und c2=„2“ und c3=„2“ und c4=„1“;
+----+----------+-------+---------+-----------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+----------+-----------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+---------+-----------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | Bereich | idx_c1234 | idx_c1234 | 3 | NULL | 1 | 20.00 | Indexbedingung verwenden |
+----+----------+----------+---------+-------+---------------+-----------+-----------+-----------+---------+------+----------+----------+----------+-----------------------+

Die verwendete Indexlänge beträgt 3, was bedeutet, dass alle Felder den Index verwenden. Gemäß dem Leftmost-Use-Prinzip verwendet c1 den Index. Hier verwendet c1 eine Ungleichheitsabfrage, die dazu führt, dass die nachfolgende c2-Abfrage den Index nicht verwenden kann. Dieser Fall verdient besondere Aufmerksamkeit. Wenn die Prädikatbedingung eine Gleichheitsabfrage und eine Bereichsabfrage enthält und die Bereichsabfrage vor dem Index steht, kann die Gleichheitsabfrage den Index nicht verwenden; wenn die Gleichheitsabfrage davor und die Bereichsabfrage danach steht, können beide den Index verwenden.

(III) So erstellen Sie einen zusammengesetzten Index

Die Schwierigkeit bei der Erstellung eines zusammengesetzten Indexes liegt in der Auswahl der Feldreihenfolge. Meine Ansichten sind folgende:

• Wenn es Abfragen und Sortierungen mit gleichen Werten gibt, platzieren Sie beim Erstellen eines zusammengesetzten Indexes das Abfragefeld mit gleichen Werten an den Anfang und das Sortierfeld ans Ende.
• Wenn mehrere Abfragen mit gleichem Wert vorhanden sind, werden die Abfragen mit der besseren Selektivität an den Anfang und die Abfragen mit der schlechteren Selektivität ans Ende gestellt.
• Wenn es Abfragen mit gleichen Werten, Bereichsabfragen und Sortierungen gibt. Abfragen mit gleichen Werten stehen an erster Stelle und bei Bereichsabfragen und Sortierungen muss die Indexreihenfolge auf Grundlage der tatsächlichen Bedingungen bestimmt werden.
  

Darüber hinaus gibt es im Alibaba Java Development Manual - 2020 Latest Songshan Edition mehrere Spezifikationen zu zusammengesetzten Indizes. Werfen wir einen Blick darauf:

1. Wenn ein Order-by-Szenario vorliegt, nutzen Sie bitte die Ordnungsmäßigkeit des Index. Das Feld nach „order by“ ist Teil des zusammengesetzten Index und wird am Ende des zusammengesetzten Index platziert, um eine Dateisortierung zu vermeiden und die Abfrageleistung zu beeinträchtigen.

Positives Beispiel: wobei a=? b=? Sortiert nach c; Index a_b_c

Gegenbeispiel: Wenn der Index eine Bereichsabfrage hat, kann die Indexreihenfolge nicht verwendet werden. Beispiel: wobei a > 10 nach b sortiert wird; der Index a_b kann nicht sortiert werden.

2. Beim Erstellen eines zusammengesetzten Indexes steht der Index mit der höchsten Unterscheidung ganz links. Wenn a=? und b=?, der Wert der Spalte a nahezu eindeutig ist, müssen Sie nur einen einspaltigen Index idx_a erstellen.

Hinweis: Wenn gemischte Beurteilungsbedingungen mit Gleichheitszeichen und Ungleichheitszeichen vorliegen, setzen Sie beim Erstellen des Index bitte die Spalte mit der Gleichheitszeichenbedingung an den Anfang. Zum Beispiel: wenn c>? und d=?, dann selbst wenn die Unterscheidung von c

Höher müssen wir auch d an den Anfang des Index setzen, das heißt, den Index idx_d_c erstellen.

Experiment: So erstellen Sie einen zusammengesetzten Index

In einigen Dokumenten werden die Regeln zum Erstellen zusammengesetzter Indizes beschrieben: ESR-Prinzip: Platzieren Sie die Felder mit exakter (Equal) Übereinstimmung an den Anfang, die Sortierbedingungen (Sort) in die Mitte und die Felder mit Bereichsübereinstimmung (Range) ans Ende. Lassen Sie uns als Nächstes untersuchen, ob dieser Ansatz richtig ist.

Beispiel: Es gibt eine Mitarbeitertabelle „employees“

mysql> zeigen, erstellen Sie die Tabelle „Mitarbeiter“;
+--------------+------------------
| Tabelle | Tabelle erstellen                                                                   
+--------------+-------------------------------------
| Mitarbeiter | CREATE TABLE `Mitarbeiter` (
 `emp_no` int(11) NICHT NULL,
 `Geburtsdatum` Datum NICHT NULL,
 `Vorname` varchar(14) NICHT NULL,
 `Nachname` varchar(16) NICHT NULL,
 `Geschlecht` enum('M','F') NICHT NULL,
 `hire_date` Datum NICHT NULL,
 PRIMÄRSCHLÜSSEL (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+-------------------------------------

-- Das Datenvolumen beträgt ca. 300.000 Zeilenmysql> select count(*) from employees;
+----------+
| Anzahl(*) |
+----------+
| 300024 |
+----------+

Nun müssen wir die Mitarbeiter abfragen, die mit Vornamen „Ebbe“ nach 1998 in die Firma eingetreten sind und diese aufsteigend nach Geburtsdatum sortieren.

Die SQL-Anweisung lautet wie folgt:

Wählen Sie Mitarbeiternummer, Geburtsdatum, Vorname, Nachname, Geschlecht, Einstellungsdatum 
von Mitarbeitern 
wobei Einstellungsdatum >= '1998-01-01'
und Vorname = 'Ebbe'
Sortiert nach Geburtsdatum;

Um die Leistung dieser SQL-Anweisung zu optimieren, müssen Sie einen Index für die Tabelle erstellen. Um sicherzustellen, dass sowohl „where“ als auch „order by“ den Index verwenden, entscheiden Sie sich für die Erstellung eines zusammengesetzten Indexes. Die Erstellungsreihenfolge ist wie folgt:

（A）Einstellungsdatum,Vorname,Geburtsdatum

（B）Einstellungsdatum,Geburtsdatum,Vorname

（C）Vorname,Einstellungsdatum,Geburtsdatum

（D）Vorname,Geburtsdatum,Einstellungsdatum

（E）Geburtsdatum,Vorname,Einstellungsdatum

(F) Geburtsdatum, Einstellungsdatum, Vorname

Bestimmen Sie, welche Reihenfolge zum Erstellen von Indizes optimal ist.

Notiz:

1. Der Datumstyp nimmt 3 Byte Speicherplatz ein, „hire_date“ und „birth_date“ nehmen beide 3 Byte Speicherplatz ein.

2. first_name ist ein Feld mit variabler Länge, das 2 weitere Bytes verwendet. Wenn NULL-Werte zulässig sind, wird 1 weiteres Byte benötigt, wodurch 16 Bytes belegt werden.

Option A: Einstellungsdatum, Vorname, Geburtsdatum

Erstellen Sie den Index idx_a für Mitarbeiter (Einstellungsdatum, Vorname, Geburtsdatum).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+----------+----------+----------+----------+---------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+
| 1 | SIMPLE | Mitarbeiter | NULL | Bereich | idx_a | idx_a | 19 | NULL | 5678 | 10.00 | Indexbedingung wird verwendet; Dateisortierung wird verwendet |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+

Hier beträgt die key_len-Länge 19, was verwirrend ist. Da hire_date eine Abfrage mit ungleichen Werten ist, sollte key_len theoretisch 3 sein. Indem wir MySQL Workbench verwenden, um den Ausführungsplan anzuzeigen, können wir auch feststellen, dass der Index nur die Spalte hire_date verwendet (wie unten gezeigt). Warum ist es 19 und nicht 3? Es ist wirklich rätselhaft. Ich habe lange darüber nachgedacht, aber kann es immer noch nicht herausfinden. Wenn jemand die Antwort kennt, hoffe ich, dass Sie als Experten sie beantworten können.

Option B: Einstellungsdatum, Geburtsdatum, Vorname

Um Störungen zu vermeiden, löschen Sie den oben erstellten Index idx_a und erstellen Sie dann idx_b.

Erstellen Sie den Index idx_b für Mitarbeiter (Einstellungsdatum, Geburtsdatum, Vorname).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+
| 1 | SIMPLE | Mitarbeiter | NULL | Bereich | idx_b | idx_b | 3 | NULL | 5682 | 10.00 | Indexbedingung wird verwendet; Dateisortierung wird verwendet |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+

Hier beträgt die Länge von key_len 3 und hire_date ist eine Abfrage mit ungleichen Werten, was die nachfolgenden Indexspalten unbrauchbar macht.

Option C: Vorname, Einstellungsdatum, Geburtsdatum

Um Störungen zu vermeiden, löschen Sie den oben erstellten Index idx_b und erstellen Sie dann idx_c.

Erstellen Sie den Index idx_c für Mitarbeiter (Vorname, Einstellungsdatum, Geburtsdatum).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+---------+----------+----------+----------+---------------------------------------+
| 1 | SIMPLE | Mitarbeiter | NULL | Bereich | idx_c | idx_c | 19 | NULL | 5 | 100,00 | Indexbedingung wird verwendet; Dateisortierung wird verwendet |
+----+----------+-----------+---------+-----------+---------------+-----------+-----------+---------+------+------+---------+----------+----------+----------+----------+---------------------------------------+

Hier beträgt die Länge von key_len 19 und first_name ist eine Abfrage mit gleichem Wert. Die Spalte hire_date kann weiterhin verwendet werden. Die Spalte hire_date ist jedoch eine Abfrage mit ungleichem Wert, was es dem Index unmöglich macht, birth_date weiterhin zu verwenden.

Option D: Vorname, Geburtsdatum, Einstellungsdatum

Um Störungen zu vermeiden, löschen Sie den oben erstellten Index idx_c und erstellen Sie dann idx_d.

Erstellen Sie den Index idx_d für Mitarbeiter (Vorname, Geburtsdatum, Einstellungsdatum).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+------+---------------+-----------+-----------+---------+-------+---------+----------+----------+----------+-----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+------+---------------+-----------+-----------+---------+-------+---------+----------+----------+----------+-----------+
| 1 | SIMPLE | Mitarbeiter | NULL | ref | idx_d | idx_d | 16 | const | 190 | 33.33 | Indexbedingung wird verwendet |
+----+----------+-----------+---------+------+---------------+-----------+-----------+---------+-------+---------+----------+----------+----------+-----------+

Hier beträgt die Länge von key_len 16, first_name ist eine Abfrage mit gleichem Wert und birth_date wird nicht in der Prädikatfilterung verwendet, was dazu führt, dass nur die Spalte first_name den oberen Index verwendet, die Spalte birth_date jedoch zum Sortieren verwendet wird. Der obige Ausführungsplan zeigt, dass das SQL am Ende nicht sortiert ist, was darauf hinweist, dass die Daten in der Reihenfolge nach birth_date aus dem Index abgerufen werden.

Option E: Geburtsdatum, Vorname, Einstellungsdatum

Um Störungen zu vermeiden, löschen Sie den oben erstellten Index idx_d und erstellen Sie dann idx_e.

Erstellen Sie den Index idx_e für Mitarbeiter (Geburtsdatum, Vorname, Einstellungsdatum).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+
| 1 | SIMPLE | Mitarbeiter | NULL | ALLE | NULL | NULL | NULL | NULL | 299468 | 3.33 | Where verwenden; Filesort verwenden |
+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+

Der Index wird hierbei nicht verwendet, das heißt, die Sortierspalte kann nicht verwendet werden, wenn sie am Anfang des zusammengesetzten Index steht.

Option F: Geburtsdatum, Einstellungsdatum, Vorname

Um Störungen zu vermeiden, löschen Sie den oben erstellten Index idx_e und erstellen Sie dann idx_f.

Erstellen Sie den Index idx_f für Mitarbeiter (Geburtsdatum, Einstellungsdatum, Vorname).

Der Ausführungsplan sieht wie folgt aus:

+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+
| 1 | SIMPLE | Mitarbeiter | NULL | ALLE | NULL | NULL | NULL | NULL | 299468 | 3.33 | Where verwenden; Filesort verwenden |
+----+----------+-----------+---------+------+---------------+---------+---------+---------+---------+---------+----------+---------+----------+-------------+

Wie bei Option E wird hier der Index nicht verwendet, was bedeutet, dass die Sortierspalte nicht verwendet werden kann, wenn sie am Anfang des zusammengesetzten Index steht.

Durch die obigen 6 Indextests haben wir festgestellt, dass der zusammengesetzte Index verwendet werden kann, wenn die gleiche Abfragespalte und die Bereichsabfragespalte vor dem zusammengesetzten Index platziert werden, die verwendeten Spalten jedoch unterschiedlich sein können. Was ist die beste Methode zum Erstellen eines Indexes? Der Abfrageoptimierer von MySQL wählt den besten Ausführungsplan auf Grundlage der Kosten aus. Werfen wir einen Blick auf die Ausführungskosten der oben genannten sechs Indizes.

Indexkosten
---------- ------------
idx_a 8518
idx_b 8524
idx_c 13
idx_d 228
idx_e 78083
idx_f 78083

Aus den oben genannten Ausgaben können wir Folgendes ersehen:

• idx_a und idx_b: Der Index verwendet den Anfang des Bereichsabfragefelds, sodass der Index nur die erste Spalte verwenden und die Sortierung nicht eliminieren kann, was zu einem hohen Overhead führt.
• idx_c und idx_d: Der Index beginnt mit dem Abfragefeld mit gleichem Wert und die Bereichsabfrage und Sortierung erfolgen am Ende, sodass der Overhead minimal ist.
• idx_e und idx_f: Der Index beginnt mit dem Sortierfeld, was dazu führt, dass der Index unbrauchbar wird und einen vollständigen Tabellenscan erfordert, was sehr aufwändig ist.
  

Und wie wählt man idx_c und idx_d aus? idx_c verwendet den Index, um eine Abfrage mit gleichem Wert + eine Bereichsabfrage durchzuführen und sortiert dann die Daten; idx_d verwendet den Index, um eine Abfrage mit gleichem Wert + eine Pushdown-Abfrage mit Indexbedingung durchzuführen und ruft dann die Daten direkt in der richtigen Reihenfolge ab. Beide Methoden haben ihre eigenen Vor- und Nachteile. Schauen wir uns ein weiteres Beispiel an:

Fügen Sie der Tabelle die oben genannten sechs Indizes hinzu und sehen Sie, welchen Index das folgende SQL auswählt.

mysql> Index von Mitarbeitern anzeigen;
+-----------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+----------+--------+----------+---------+---------+---------+---------+---------+
| Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar |
+-----------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+----------+--------+----------+---------+---------+---------+---------+---------+
| Mitarbeiter | 0 | PRIMARY | 1 | emp_no | A | 299468 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_a | 1 | Einstellungsdatum | A | 5355 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_a | 2 | Vorname | A | 290745 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_a | 3 | Geburtsdatum | A | 299468 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_b | 1 | Einstellungsdatum | A | 6237 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_b | 2 | Geburtsdatum | A | 297591 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_b | 3 | Vorname | A | 299468 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_c | 1 | Vorname | A | 1260 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_c | 2 | Einstellungsdatum | A | 293517 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_c | 3 | Geburtsdatum | A | 299468 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_d | 1 | Vorname | A | 1218 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_d | 2 | Geburtsdatum | A | 294525 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_d | 3 | Einstellungsdatum | A | 298095 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_e | 1 | Geburtsdatum | A | 4767 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_e | 2 | Vorname | A | 292761 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_e | 3 | Einstellungsdatum | A | 299468 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_f | 1 | Geburtsdatum | A | 4767 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_f | 2 | Einstellungsdatum | A | 297864 | NULL | NULL | | BTREE | | |
| Mitarbeiter | 1 | idx_f | 3 | Vorname | A | 299468 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+--------------+--------------+--------------+-------------+----------+----------+--------+----------+---------+---------+---------+---------+---------+

SQL1

mysql> erklären Sie, wählen Sie Mitarbeiternummer, Geburtsdatum, Vorname, Nachname, Geschlecht, Einstellungsdatum 
von Mitarbeitern 
wobei Einstellungsdatum >= '1998-01-01'
und Vorname = 'Ebbe'
Sortiert nach Geburtsdatum;
+----+----------+-----------+---------+-----------+-------------------------+-----------+---------+------+---------+----------+----------+----------+----------+---------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+-----------+-------------------------+-----------+---------+------+---------+----------+----------+----------+----------+---------------------------------------+
| 1 | SIMPLE | Mitarbeiter | NULL | Bereich | idx_a,idx_b,idx_c,idx_d | idx_c | 19 | NULL | 5 | 100,00 | Indexbedingung wird verwendet; Dateisortierung wird verwendet |
+----+----------+-----------+---------+-----------+-------------------------+-----------+---------+------+---------+----------+----------+----------+----------+---------------------------------------+

Hier wählt MySQL automatisch idx_c aus, da die beiden Felder first_name+hire_date die Daten auf nur 5 Zeilen gefiltert haben. Da die Daten klein sind, erfolgt die Sortierung sehr schnell. Wenn Sie dagegen „idx_d“ auswählen, müssen Sie zunächst 190 Datenzeilen herausfiltern, die die Bedingungen über das Feld „first_name“ erfüllen, und dann „hire_date“ zum Filtern der Daten verwenden, was sehr viel Arbeit bedeutet.

SQL2

mysql> erklären Sie, wählen Sie Mitarbeiternummer, Geburtsdatum, Vorname, Nachname, Geschlecht, Einstellungsdatum 
von Mitarbeitern 
wobei Einstellungsdatum >= '1980-01-01'
und Vorname = 'Ebbe'
Sortiert nach Geburtsdatum;
+----+----------+-----------+---------+------+-------------------------+-----------+---------+-----------+---------+-----------+----------+-----------+-----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-----------+---------+------+-------------------------+-----------+---------+-----------+---------+-----------+----------+-----------+-----------+
| 1 | SIMPLE | Mitarbeiter | NULL | ref | idx_a,idx_b,idx_c,idx_d | idx_d | 16 | const | 190 | 50.00 | Indexbedingung wird verwendet |
+----+----------+-----------+---------+------+-------------------------+-----------+---------+-----------+---------+-----------+----------+-----------+-----------+

Wenn idx_c ausgewählt ist, ist das Datenvolumen groß, nachdem die Felder „Vorname“ und „Einstellungsdatum“ die Daten durch den Index gefiltert haben, was zu einer sehr langsamen Sortierung führt. MySQL wählt automatisch idx_d aus, filtert die Daten nach der Spalte first_name des Index und filtert das Feld hire_date, indem es die Indexbedingung nach unten verschiebt, und ruft dann die Daten der Reihe nach aus dem Index ab. Da idx_d keine Sortierung erfordert, ist die Geschwindigkeit relativ gesehen höher.

(IV) Zusammenfassung der zusammengesetzten Indizes

1. Wenn Sie einen zusammengesetzten Index erstellen und mehrere Abfragen mit gleichem Wert vorhanden sind, platzieren Sie die Spalte mit guter Selektivität vorne und die Spalte mit schlechter Selektivität hinten.

2. Wenn beim Erstellen eines zusammengesetzten Indexes Abfragen mit gleichem Wert und Bereichsabfragen beteiligt sind, sollte das Feld mit der Abfrage mit gleichem Wert vor der Abfrage mit ungleichem Wert platziert werden, unabhängig davon, wie gut die Selektivität der Spalte mit der Abfrage mit ungleichem Wert ist.

3. Wenn beim Erstellen eines zusammengesetzten Indexes Abfragen gleicher Werte, Bereichsabfragen und Sortierungen (Order by, Group by) erforderlich sind, sollten die Abfragen gleicher Werte am Anfang des Indexes platziert werden. Die Reihenfolge der Bereichsabfragen und Sortierungen sollte basierend auf dem tatsächlichen Szenario bestimmt werden. Wenn die Bereichsabfrage zuerst kommt, kann die Reihenfolge des Index nicht verwendet werden und es ist eine Dateisortierung erforderlich. Dies ist für SQL geeignet, das weniger Ergebnisse zurückgibt, da weniger Ergebnisse weniger Sortieraufwand bedeuten. Wenn die Sortierung zuerst kommt, kann die Reihenfolge des Index verwendet werden, aber es ist notwendig, zur Tabelle zurückzukehren (oder die Indexbedingung nach unten zu drücken), um die Daten abzufragen. Dies ist für SQL geeignet, das mehr Ergebnisse zurückgibt, da keine Sortierung erforderlich ist und die Daten direkt abgerufen werden können.

4. Wenn Sie einen zusammengesetzten Index erstellen, platzieren Sie die Spalten „order by“ und „group by“ nicht an den Anfang des Index, da die Where-Klausel in der Abfrage immer vor der Order by-Klausel ausgeführt wird.

5. Die Verwendung eines Indexes für eine Bereichsabfrage führt dazu, dass nachfolgende Indexfelder unbrauchbar werden. Wenn eine Sortierung vorhanden ist, kann die Filesort-Sortierung nicht eliminiert werden. Beispiel: a_b_c-Index, wobei a>? und b = ? nach c sortiert ist, dann kann a verwendet werden, b jedoch nicht, und das Feld c benötigt eine Dateisortierung.

Zusammenfassen

Dies ist das Ende dieses Artikels über MySQL-Verbundindizes. Weitere Informationen zu MySQL-Verbundindizes finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!