So optimieren Sie MySQL-Indizes

1. Wie MySQL Indizes verwendet

Indizes werden verwendet, um schnell Zeilen mit bestimmten Spaltenwerten zu finden. Ohne Index muss MySQL mit der ersten Zeile beginnen und dann die gesamte Tabelle durchsuchen, um die relevanten Zeilen zu finden. Je größer der Tisch, desto mehr kostet er. Wenn die Tabelle über einen Index für die relevanten Spalten verfügt, kann MySQL schnell ermitteln, wo in der Mitte der Datendatei gesucht werden muss, ohne alle Daten durchsuchen zu müssen. Dies ist viel schneller, als jede Zeile nacheinander zu lesen.

Die meisten MySQL-Indizes (PRIMARY KEY, UNIQUE, INDEX und FULLTEXT) werden in B-Bäumen gespeichert. Ausnahmen: Indizes für räumliche Datentypen verwenden R-Bäume; MEMORY-Tabellen unterstützen auch Hash-Indizes. InnoDB verwendet invertierte Listen für FULLTEXT-Indizes.

MySQL verwendet Indizes für Folgendes:

• Schnelles Finden von Zeilen, die der WHERE-Klausel entsprechen
• Wenn zwischen mehreren Indizes gewählt werden kann, verwendet MySQL normalerweise den Index, der die geringste Anzahl von Zeilen findet (den selektivsten Index).
• Bei einem mehrspaltigen Index (auch „zusammengesetzter Index“ oder „gemeinsamer Index“ genannt) kann der Optimierer jedes ganz links stehende Präfix des Index verwenden, um Zeilen zu finden. Wenn Sie beispielsweise einen dreispaltigen Index für (Spalte1, Spalte2, Spalte3) haben, verfügen Sie über indizierte Suchfunktionen für (Spalte1), (Spalte1, Spalte2) und (Spalte1, Spalte2, Spalte3).
• Wenn Sie eine Join-Abfrage verwenden, um Zeilen aus anderen Tabellen abzurufen, kann MySQL Indizes für Spalten effizienter verwenden, wenn diese als gleicher Typ und gleicher Größe deklariert sind. In diesem Fall werden VARCHAR und CHAR als gleich betrachtet, wenn für sie die gleiche Größe deklariert ist. Beispielsweise haben VARCHAR(10) und CHAR(10) die gleiche Größe, aber VARCHAR(10) und CHAR(15) haben unterschiedliche Größen.
• Für Vergleiche zwischen nichtbinären Zeichenfolgenspalten sollten beide Spalten denselben Zeichensatz verwenden.
• Wenn die Sortierung oder Gruppierung nach dem äußersten linken Präfix des verfügbaren Index erfolgt (z. B. ORDER BY key_part1, key_part2), wird die Tabelle sortiert oder gruppiert. Folgen auf alle Schlüsselteile DESC, werden die Schlüssel in umgekehrter Reihenfolge gelesen.
• In einigen Fällen kann MySQL Indizes verwenden, um die ORDER BY-Klausel zu erfüllen und die zusätzliche Sortierung zu vermeiden, die beim Ausführen eines Dateisortiervorgangs erforderlich ist.
• In einigen Fällen können Abfragen optimiert werden, um Werte abzurufen, ohne die Datenzeilen abzufragen. (Ein Index, der alle notwendigen Ergebnisse für eine Abfrage liefert, wird als abdeckender Index bezeichnet.) Wenn eine Abfrage nur Spalten aus einer Tabelle verwendet, die in einigen Indizes enthalten sind, können die ausgewählten Werte zur Erhöhung der Geschwindigkeit aus dem Indexbaum abgerufen werden.

Schließlich sind Indizes für Abfragen kleiner Tabellen weniger wichtig. Wenn eine Abfrage auf die meisten Zeilen zugreifen muss, sind sequentielle Lesevorgänge schneller als die Verarbeitung von Indizes.

2. Vermeiden Sie vollständige Tabellenscans

Wenn MySQL zum Auflösen einer Abfrage einen vollständigen Tabellenscan verwendet, zeigt die Ausgabe von EXPLAIN „ALL“ in der Spalte „Typ“ an. Dies geschieht normalerweise, wenn:

• Die Tabelle ist so klein, dass ein vollständiger Tabellenscan viel schneller ist als ein Indexsuchvorgang. Dies kommt häufig bei Tabellen mit weniger als 10 Zeilen und kurzer Zeilenlänge vor.
• Die Indexspalte wird in der ON- oder WHERE-Klausel nicht verwendet.
• Durch den Vergleich der indizierten Spalte mit einem konstanten Wert, den MySQL bereits berechnet hat (basierend auf dem Indexbaum), wird ein großer Teil der Tabelle abgedeckt, und der Tabellenscan wird schneller durchgeführt.
• Sie verwenden einen Schlüssel mit niedriger Kardinalität (viele Zeilen stimmen mit dem Schlüsselwert überein) über eine andere Spalte. In diesem Fall geht MySQL davon aus, dass durch die Verwendung dieses Schlüssels möglicherweise viele Schlüsselsuchen durchgeführt werden können und ein Tabellenscan schneller erfolgt.

Bei kleinen Tabellen ist ein Tabellenscan normalerweise angemessen und hat nur vernachlässigbare Auswirkungen auf die Leistung.

Bei großen Tabellen können Sie die folgenden Techniken ausprobieren, um zu verhindern, dass der Optimierer fälschlicherweise einen Tabellenscan auswählt:

• Verwenden Sie ANALYZE TABLE tbl_name, um die Schlüsselverteilung zu aktualisieren
• Verwenden Sie FORCE INDEX, um MySQL mitzuteilen, dass ein Tabellenscan im Vergleich zur Verwendung eines bestimmten Indexes sehr aufwändig ist.

3. Spaltenindex

Die B-Baum-Datenstruktur ermöglicht es Indizes, schnell einen bestimmten Wert, eine Menge von Werten oder einen Wertebereich zu finden, der Operatoren wie =, >, ≤, BETWEEN, IN usw. in einer WHERE-Klausel entspricht.

Jede Speicher-Engine definiert die maximale Anzahl von Indizes und die maximale Indexlänge pro Tabelle. Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle und die Gesamtindexlänge beträgt mindestens 256 Byte.

Indexpräfix

Verwenden Sie col_name(N), um einen Index zu erstellen, der nur die ersten N Zeichen einer Spalte verwendet. In InnoDB-Tabellen beträgt die maximale Präfixlänge 767 Bytes.

Volltextindex

FULLTEXT-Indizes werden für Volltextsuchen verwendet. FULLTEXT-Indizes werden nur für die Speicher-Engines InnoDB und MyISAM und nur für CHAR-, VARCHAR- und TEXT-Spalten unterstützt. Die Indizierung erfolgt immer für die gesamte Spalte; Spaltenpräfixindizes werden nicht unterstützt.

Räumlicher Index

Bezieht sich auf eine Datenstruktur, die basierend auf der Position und Form räumlicher Objekte oder einer bestimmten räumlichen Beziehung zwischen räumlichen Objekten in einer bestimmten Reihenfolge angeordnet ist.

Indizes auf der Speicher-Engine MEMORY

Standardmäßig verwendet die MEMORY-Speicher-Engine HASH-Indizes, unterstützt aber auch BTREE-Indizes.

4. Mehrspaltiger Index

MySQL kann zusammengesetzte Indizes erstellen (das heißt, Indizes für mehrere Spalten). Ein Index kann bis zu 16 Spalten enthalten.

Angenommen, es gibt eine Darstellung, die wie folgt definiert ist:

CREATE TABLE-Test (
  id INT NICHT NULL,
  Nachname CHAR(30) NOT NULL,
  Vorname CHAR(30) NOT NULL,
  Primärschlüssel (ID),
  INDEX idx_name (Nachname,Vorname)
);

Der idx_name-Index ist ein Index, der auf den Spalten last_name und first_name basiert. Dieser Index kann für Abfragen verwendet werden, die eine Kombination aus last_name- und first_name-Werten angeben, oder für Abfragen, die nur last_name-Werte angeben, da dieser Index mit dem Präfix ganz links übereinstimmt.

Daher kann der idx_name-Index für die folgenden Abfragen verwendet werden:

SELECT * FROM test WHERE Nachname='Jones';

SELECT * FROM test WHERE Nachname='Jones' AND Vorname='John';

SELECT * FROM test WHERE Nachname='Jones' AND (Vorname='John' OR Vorname='Jon');

SELECT * FROM test WHERE Nachname='Jones' AND Vorname >='M' AND Vorname < 'N';

Der idx_name-Index kann jedoch nicht für die folgenden Abfragen verwendet werden:

SELECT * FROM test WHERE Vorname='John';

SELECT * FROM test WHERE Nachname='Jones' OR Vorname='John';

Betrachten Sie das folgende SQL:

Wählen Sie * aus Tabellenname, wobei col1 = Wert1 und col2 = Wert2 ist.

Wenn für col1 und col2 ein mehrspaltiger Index existiert, können die entsprechenden Zeilen direkt abgerufen werden. Wenn für Spalte1 und Spalte2 separate Einzelspaltenindizes vorhanden sind, versucht der Optimierer, die Indexzusammenführungsoptimierung zu verwenden oder den restriktivsten Index zu finden, indem er ermittelt, welcher Index mehr Zeilen ausschließen muss, und diesen Index zum Abrufen der Zeilen verwendet.

Wenn die Tabelle einen mehrspaltigen Index hat, kann der Optimierer jedes ganz linke Präfix des Index verwenden, um Zeilen zu finden. Wenn Sie beispielsweise einen dreispaltigen Index (Spalte1, Spalte2, Spalte3) haben, dann verfügen Sie über Indexsuchfunktionen für (Spalte1), (Spalte1, Spalte2), (Spalte1, Spalte2, Spalte3).

Wenn die Spalten kein äußerstes linkes Präfix des Index bilden, kann MySQL den Index nicht für die Suche verwenden.

Sehen Sie sich die folgende SQL-Anweisung an:

Wählen Sie * aus Tabellenname, wobei Spalte1 = Wert1 ist.
Wählen Sie * aus Tabellenname, wobei col1 = Wert1 und col2 = Wert2 ist.

Wählen Sie * aus Tabellenname, wobei col2 = val2;
Wählen Sie * aus Tabellenname, wobei col2 = val2 und col3 = val3 ist;

Wenn es einen zusammengesetzten Index für (Spalte1, Spalte2, Spalte3) gibt, wird dieser nur von den ersten beiden Abfragen verwendet. Dann verwenden die letzten beiden Abfragen den Index nicht zur Durchführung der Suche, da (col2) und (col2, col3) keine ganz linken Präfixe von (col1, col2, col3) sind.

5. Vergleich von B-Tree- und Hash-Indizes

B-Tree-Indexeigenschaften

B-Baum-Indizes können für Spaltenvergleiche in Ausdrücken verwendet werden, die die Operatoren =, >, >=, <, <= und BETWEEN verwenden. Der Index kann auch für LIKE-Vergleiche verwendet werden, wenn das Argument für LIKE eine konstante Zeichenfolge ist, die nicht mit einem Platzhalterzeichen beginnt.

Die folgenden Klauseln verwenden keine Indizes:

/* der LIKE-Wert beginnt mit einem Platzhalterzeichen */
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
/* der LIKE-Wert ist keine Konstante */
SELECT * FROM tbl_name WHERE Schlüsselspalte LIKE andere_Spalte;

Jeder Index, der nicht alle UND-Ebenen in der WHERE-Klausel abdeckt, wird nicht zur Optimierung der Abfrage verwendet. Mit anderen Worten: Um einen Index verwenden zu können, muss in jeder UND-Gruppe ein Präfix des Index verwendet werden.

Die folgenden WHERE-Klauseln verwenden Indizes:

... WO index_part1=1 UND index_part2=2 UND andere_Spalte=3

  /* Index = 1 ODER Index = 2 */
... WO index=1 ODER A=10 UND index=2

  /* optimiert wie "index_part1='hello'" */
... WO index_part1='hallo' UND index_part3=5

  /* Kann Index auf Index1 verwenden, aber nicht auf Index2 oder Index3 */
... WO index1=1 UND index2=2 ODER index1=3 UND index3=3;

Die folgenden WHERE-Klauseln verwenden keine Indizes:

/* index_part1 wird nicht verwendet */
... WO index_part2=1 UND index_part3=2

  /* Der Index wird in beiden Teilen der WHERE-Klausel nicht verwendet */
... WO Index=1 ODER A=10

  /* Kein Index erstreckt sich über alle Zeilen */
... WO index_part1=1 ODER index_part2=10

Manchmal verwendet MySQL einen Index nicht, obwohl einer verfügbar ist. Ein möglicher Grund hierfür ist, dass der Optimierer schätzt, dass bei Verwendung des Indexes auf einen großen Prozentsatz der Zeilen in der Tabelle zugegriffen werden muss. (In diesem Fall ist ein Tabellenscan möglicherweise schneller, da weniger Suchvorgänge erforderlich sind.) Wenn eine solche Abfrage jedoch LIMIT verwendet, um nur bestimmte Zeilen abzurufen, verwendet MySQL dennoch den Index, da es die wenigen zurückzugebenden Zeilen schneller finden kann.

Hash-Index-Eigenschaften

Hash-Indizes weisen einige andere Merkmale auf als die gerade besprochenen Indizes:

• Hash-Indizes werden nur für Gleichheitsvergleiche mit den Operatoren = oder <=> verwendet (sie sind jedoch sehr schnell), nicht für Vergleichsoperatoren zum Suchen von Wertebereichen. Systeme, die auf solchen Einzelwertsuchen basieren, heißen „Schlüssel-Wert-Speicher“. Um MySQL für solche Anwendungen zu nutzen, verwenden Sie, wann immer möglich, Hash-Indizes.
• Der Optimierer kann keine Hash-Indizes verwenden, um ORDER BY-Operationen zu beschleunigen. (Hash-Typ-Indizes können nicht verwendet werden, um nach dem nächsten Eintrag in der Sequenz zu suchen)
• MySQL kann nicht ungefähr bestimmen, wie viele Zeilen zwischen zwei Werten liegen (was der Bereichsoptimierer verwendet, um zu entscheiden, welcher Index verwendet werden soll)
• Für die Zeilensuche können nur ganze Schlüssel verwendet werden. (Bei einem B-Baum-Index kann jedes ganz linke Präfix des Schlüssels zum Suchen einer Zeile verwendet werden.)

B-Baum

Baumdatenstruktur, die häufig bei der Datenbankindizierung verwendet wird. Die Struktur bleibt immer geordnet, sodass eine schnelle Suche nach exakten Übereinstimmungen (Gleichheitsoperatoren) und Bereichen (z. B. Größer-als-, Kleiner-als- und ZWISCHEN-Operatoren) möglich ist. Solche Indizes sind für die meisten Speicher-Engines wie InnoDB und MyISAM verfügbar.

Da ein B-Baum-Knoten viele untergeordnete Knoten haben kann, unterscheidet sich ein B-Baum von einem Binärbaum, bei dem jeder Knoten höchstens zwei untergeordnete Knoten haben kann.

Mit dem Begriff „B-Baum“ ist eine allgemeine Klasse von Indexdesigns gemeint. Die von der MySQL-Speicher-Engine verwendete B-Baum-Struktur kann aufgrund der im klassischen B-Baum-Design nicht vorhandenen Komplexität als eine Variante betrachtet werden.

Hash-Index

Ein Indextyp, der für Abfragen entwickelt wurde, die den Gleichheitsoperator anstelle des Bereichsoperators verwenden. Es kann für MEMORY-Tabellen verwendet werden. Obwohl Hash-Indizes aus historischen Gründen der Standardindex für MEMORY-Tabellen sind, unterstützt die Speicher-Engine auch B-Tree-Indizes, die im Allgemeinen für allgemeine Abfragen die bessere Wahl sind.

6. Datengröße optimieren

Entwerfen Sie Tabellen so, dass sie möglichst wenig Speicherplatz auf der Festplatte beanspruchen. Dies kann eine enorme Verbesserung darstellen, da die auf die Festplatte geschriebene und von der Festplatte gelesene Datenmenge reduziert wird. Kleinere Tabellen benötigen im Allgemeinen weniger Hauptspeicher für die Verarbeitung ihrer Inhalte während der Abfrageausführung. Jede Reduzierung des Speicherplatzes für die Tabellendaten führt zu kleineren Indizes, die schneller verarbeitet werden können.

MySQL unterstützt viele verschiedene Speicher-Engines (Tabellentypen) und Zeilenformate. Sie können für jede Tabelle entscheiden, welche Speicher- und Indizierungsmethode verwendet werden soll. Die Wahl des geeigneten Tabellenformats für Ihre Anwendung kann die Leistung erheblich verbessern.

Tabellenspalten

• Verwenden Sie den effizientesten (kleinsten) möglichen Datentyp. MySQL verfügt über viele spezialisierte Typen, die Speicherplatz und Arbeitsspeicher sparen können. Verwenden Sie beispielsweise nach Möglichkeit kleinere Ganzzahltypen, um kleinere Tabellen zu erhalten. MEDIUMINT ist normalerweise besser als INT, da eine MEDIUMINT-Spalte 25 % weniger Platz benötigt.
• Deklarieren Sie die Spalte nach Möglichkeit als NOT NULL. SQL-Operationen können dadurch beschleunigt werden, indem Indizes besser genutzt werden und der Mehraufwand beim Prüfen jedes Werts auf NULL entfällt. Und es spart auch etwas Speicherplatz, ein Bit pro Spalte. Wenn Sie wirklich NULL-Werte in Ihrer Tabelle benötigen, verwenden Sie diese. Vermeiden Sie einfach die Verwendung der Standardeinstellung, die NULL-Werte in jeder Spalte zulässt.
  

Zeilenformat

Um den Speicherplatz weiter zu reduzieren, indem Sie Tabellendaten in komprimierter Form speichern, geben Sie beim Erstellen einer InnoDB-Tabelle ROW_FORMAT=COMPRESSED an.

Indizes

• Der Primärschlüsselindex einer Tabelle sollte so kurz wie möglich sein. Dadurch lässt sich jede Zeile einfach und effizient identifizieren. Bei InnoDB-Tabellen werden die Primärschlüsselspalten in jedem sekundären Indexeintrag wiederholt, sodass ein kürzerer Primärschlüssel viel Platz sparen kann, wenn Sie viele sekundäre Indizes haben.
• Erstellen Sie nur die Indizes, die Sie zur Verbesserung der Abfrageleistung benötigen. Indizes eignen sich hervorragend zum Abrufen, verlangsamen jedoch Einfüge- und Aktualisierungsvorgänge. Wenn Sie auf eine Tabelle hauptsächlich durch die Suche nach Spaltenkombinationen zugreifen, erstellen Sie einen einzelnen zusammengesetzten Index für die Tabelle, anstatt separate Indizes für jede Spalte zu erstellen. Der erste Teil des Index sollte die am häufigsten verwendeten Spalten enthalten. Wenn bei Abfragen aus der Tabelle viele Spalten konsistent verwendet werden, sollte die erste Spalte im Index die am häufigsten wiederholte Spalte sein, um den Index besser zu komprimieren.
• Handelt es sich um eine lange Zeichenfolge, ist es sehr wahrscheinlich, dass das erste Zeichen ein eindeutiges Präfix hat. In diesem Fall ist es am besten, das MySQL-Präfix für die Indizierung zu verwenden (PS: Es werden nur die ersten paar Zeichen indiziert). Kürzere Indizes sind schneller, nicht nur weil sie weniger Speicherplatz benötigen, sondern auch weil sie zu mehr Treffern im Index-Cache führen und somit die Anzahl der Suchvorgänge auf der Festplatte verringern.

Verknüpfungen

Deklarieren Sie Spalten mit identischen Informationen in verschiedenen Tabellen mit demselben Datentyp, um Verknüpfungen basierend auf entsprechenden Spalten zu beschleunigen.
Halten Sie die Spaltennamen einfach, damit Sie denselben Namen in verschiedenen Tabellen verwenden und Join-Abfragen vereinfachen können. Verwenden Sie beispielsweise in einer Tabelle mit dem Namen „Kunde“ den Spaltennamen „Name“ anstelle von „Kundenname“. Um Ihre Namen auf andere SQL-Server portierbar zu machen, sollten Sie die Namenslänge auf 18 Zeichen oder weniger begrenzen.

Normalisierung

Versuchen Sie grundsätzlich, alle Daten nicht redundant zu halten (in der Datenbanktheorie als dritte Normalform bezeichnet). Weisen Sie ihnen eindeutige IDs anstelle eines langen, sich wiederholenden Werts zu, wiederholen Sie diese IDs in so vielen kleineren Tabellen wie nötig und verknüpfen Sie die Tabellen in Abfragen, indem Sie in der Join-Klausel auf die IDs verweisen.

7. Datentypen optimieren

Numerische Typen

Zur eindeutigen Identifizierung von Zeilen empfiehlt sich die Verwendung numerischer Werte anstelle von Zeichenfolgen, da große Zahlen weniger Speicherplatz beanspruchen als die entsprechenden Zeichenfolgen, ihre Übertragung und ihr Vergleich also schneller erfolgt und weniger Speicher beansprucht.

Zeichen- und Zeichenfolgetypen

• Wenn Sie Werte aus verschiedenen Spalten vergleichen, deklarieren Sie diese Spalten nach Möglichkeit mit demselben Zeichensatz und derselben Sortierung, um Zeichenfolgenkonvertierungen beim Ausführen von Abfragen zu vermeiden.
• Verwenden Sie für Spaltenwerte, die kleiner als 8 KB sind, binäres VARCHAR statt BLOB. Die Klauseln GROUP BY und ORDER BY können temporäre Tabellen generieren, und diese temporären Tabellen können die Speicher-Engine MEMORY verwenden, wenn die ursprüngliche Tabelle keine BLOB-Spalten enthält.
• Wenn eine Tabelle Zeichenfolgenspalten wie „Name“ und „Adresse“ enthält, diese Spalten jedoch von vielen Abfragen nicht abgerufen werden, sollten Sie die Zeichenfolgenspalten in eine separate Tabelle aufteilen und bei Bedarf Join-Abfragen mit Fremdschlüsseln verwenden. Wenn MySQL einen beliebigen Wert aus einer Zeile abruft, liest es einen Datenblock, der alle Spalten für diese Zeile (und möglicherweise andere angrenzende Zeilen) enthält. Wenn Sie die Zeilen klein halten und nur die am häufigsten verwendeten Spalten enthalten, können Sie mehr Zeilen pro Datenblock unterbringen. Diese kompakte Tabelle reduziert den Festplatten-E/A- und Speicherverbrauch für allgemeine Abfragen.
• Wenn Sie einen zufällig generierten Wert als Primärschlüssel in einer InnoDB-Tabelle verwenden, ist es am besten, ihm einen aufsteigenden Wert voranzustellen, beispielsweise das aktuelle Datum und die aktuelle Uhrzeit (falls möglich). InnoDB kann aufeinanderfolgende Primärschlüsselwerte schneller einfügen und abrufen, wenn sie physisch nebeneinander gespeichert sind.
  

andere

• Eine temporäre Tabelle wird verwendet, wenn die in ORDER BY und GROUP BY verwendeten Spalten inkonsistent sind oder wenn ORDER BY oder GROUP BY in einer Join-Abfrage Spalten aus einer anderen als der ersten Tabelle verwendet.
• MySQL hat ein festes Limit von 4096 Spalten pro Tabelle, aber für eine bestimmte Tabelle kann das effektive Maximum geringer sein. InnoDB hat ein Limit von 1017 Spalten pro Tabelle.

Oben finden Sie Einzelheiten zur Optimierung von MySQL-Indizes. Weitere Informationen zur Optimierung von MySQL-Indizes finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!