So entwerfen und optimieren Sie MySQL-Indizes

Was ist ein Index?

Ein Datenbankindex ist eine Datenstruktur, die die Geschwindigkeit von Datenabrufvorgängen in einer Datenbanktabelle erhöht, allerdings auf Kosten zusätzlicher Schreibvorgänge und Speicherplatz. Vereinfacht ausgedrückt ist ein Index vergleichbar mit dem Inhaltsverzeichnis eines Buches und ermöglicht Ihnen, anhand der darin verzeichneten Seitenzahlen schnell den gewünschten Inhalt zu finden. ——Was sind die gängigen Indizes in Wikipedia?

• Normaler Index: der grundlegendste Index ohne jegliche Einschränkungen
• Eindeutiger Index: Ähnlich wie "normaler Index", außer dass der Wert der Indexspalte eindeutig sein muss, aber Nullwerte zulässig sind
• Primärschlüsselindex: Dies ist ein spezieller Index, der keine Nullwerte zulässt.
• Volltextindex: Kann nur für MyISAM-Tabellen verwendet werden. Bei großen Datenmengen ist das Generieren von Volltextindizes zeit- und platzaufwändig.
• Kombinierter Index: Um die Effizienz von Abfragen mit mehreren Bedingungen zu verbessern, kann ein kombinierter Index nach dem „Prinzip der Übereinstimmung mit dem am weitesten links stehenden Präfix“ erstellt werden.
  

Hier nehmen wir eine relativ komplexe Kombination als Beispiel, um zu zeigen, wie sie optimiert werden kann.

Prinzip der Übereinstimmung mit dem äußersten linken Präfix

Zunächst müssen wir wissen, was das Prinzip der Übereinstimmung des am weitesten links stehenden Präfixes ist.

Das Prinzip des ganz links stehenden Präfixabgleichs bedeutet, dass bei Verwendung eines gemeinsamen B+Tree-Index zum Abrufen von Daten der MySQL-Optimierer das Prädikat (Filterbedingung) liest und in der Reihenfolge, in der die gemeinsamen Indexfelder erstellt werden, nach rechts abgleicht, bis eine Bereichsabfrage oder eine ungleiche Abfrage gefunden wird. Die Indexspalten nach diesem Feld werden nicht verwendet. Zu diesem Zeitpunkt kann durch Berechnen key_len analysiert werden, welche Indexspalten tatsächlich vom gemeinsamen Index verwendet werden.

So berechnen Sie key_len

Die Berechnung key_len hilft uns auch dabei, das Prinzip der Übereinstimmung des ganz linken Präfixes des Index zu verstehen.

key_len gibt die Länge [Anzahl der Bytes] des Auswahlindex an, der zum Abrufen des Ergebnissatzes verwendet wird, ausschließlich order by . Das heißt, wenn order by auch einen Index verwendet, ist key_len nicht enthalten.

Bevor wir key_len berechnen, schauen wir uns die grundlegenden Datentypen an (am Beispiel der UTF8-Kodierung):

Die Testdatentabelle sieht wie folgt aus:

CREATE TABLE `test_table` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `a` int(11) STANDARD NICHT NULL,
 `b` int(11) STANDARD NICHT NULL,
 `c` int(11) STANDARD NICHT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `test_table_a_b_c_index` (`a`,`b`,`c`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Trefferindex:

mysql> erklären Sie „select * from test_table“, wobei a = 1 und b = 2 und c = 3;
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const,const,const | 1 | 100.00 | Index wird verwendet |
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+

Sie können sehen, key_len = 12 Wie wird das berechnet?
Da der Zeichensatz UTF8 ist, belegt ein Feld vier Bytes und drei Felder sind 4 * 3 = 12 Bytes.

Ob NULL zulässig ist. Wenn NULL zulässig ist, wird ein zusätzliches Byte benötigt, um das Feld zu markieren. Unterschiedliche Datentypen erfordern unterschiedliche Bytegrößen.

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> erklären Sie „select * from test_table“, wobei a = 1 und b = 2 und c = 3;
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const,const,const | 1 | 100.00 | Index wird verwendet |
+----+-------------+------------+------------+------+------------------------+---------+---------+-------------------+----------+-------------+

Es ist ersichtlich, dass key_len 15 = 4 3 + 1 3 wird, wenn das Feld leer sein darf (wenn der INT-Typ leer ist, belegt er ein zusätzliches Byte).

Indexoptimierung

Mit diesem Grundwissen können wir die Leistung anhand des tatsächlichen SQL beurteilen.

Erstellen Sie anhand der obigen Datentabelle als Beispiel einen gemeinsamen Index für die drei Felder a, b und c.

Wenn beim Anzeigen eines Ausführungsplans in der Spalte „Extra“ der Wert „Index verwenden“ angezeigt wird, bedeutet dies normalerweise, dass der Optimierer einen überdeckenden Index verwendet.

• SQL1 kann abdeckende Indizes verwenden, was eine gute Leistung bietet
• SQL2 kann abdeckende Indizes verwenden und das Sortieren vermeiden, was eine gute Leistung bietet
• SQL3 kann abdeckende Indizes verwenden, muss aber basierend auf der Where-Klausel gefiltert werden
• SQL4 kann den Teilindex a verwenden, kann aber das Sortieren nicht vermeiden und die Leistung ist schlecht
• SQL5 kann abdeckende Indizes vollständig nutzen und das Sortieren vermeiden, was zu einer guten Leistung führt.
• SQL6 kann abdeckende Indizes verwenden, kann aber das Sortieren nicht vermeiden (das liegt daran, dass MySQL InnoDB beim Erstellen eines Index standardmäßig die aufsteigende Reihenfolge verwendet und der Index nicht automatisch in absteigender Reihenfolge sortiert werden kann).
• SQL7 kann abdeckende Indizes verwenden, muss aber gemäß der Where-Klausel gefiltert werden (Abfrage mit nicht festem Wert).
  

Erstellen einer Indexspezifikation

• Unter Berücksichtigung der Kosten für die Indexwartung sollte die Anzahl der Indizes in einer einzelnen Tabelle 5 und die Anzahl der Felder in einem einzelnen Index 5 nicht überschreiten.
• Erstellen Sie keine Indizes für Spalten mit niedriger Kardinalität, wie etwa „Geschlecht“. Auf Spalten mit niedriger Kardinalität erstellte Indexabfragen bieten gegenüber vollständigen Tabellenscans nicht unbedingt einen Leistungsvorteil, insbesondere dann nicht, wenn Kosten für die Tabellenrückgabe anfallen.
• Erstellen Sie einen gemeinsamen Index ordnungsgemäß. (a,b,c) entspricht (a), (a,b), (a,b,c).
• Der sinnvolle Einsatz von abdeckenden Indizes reduziert die IO und vermeidet das Sortieren.

Oben finden Sie Einzelheiten zum Entwerfen und Optimieren von MySQL-Indizes. Weitere Informationen zum Entwerfen und Optimieren von MySQL-Indizes finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!