MySQL-Performance-Optimierung: So nutzen Sie Indizes effizient und richtig

Die Praxis ist der einzige Weg, die Wahrheit zu testen. Dieser Artikel stellt nur die allgemeine Verwendung von Indizes dar. Sie müssen nur den gesamten Artikel lesen und ihn mit bestimmten Beispielen kombinieren oder sich an die Stellen erinnern, an denen er in der Vergangenheit verwendet wurde, um ein umfassendes Verständnis des Ganzen zu erlangen und zu verstehen, wie der Index funktioniert. Wenn Sie in Zukunft Indizes verwenden oder optimieren, können Sie von diesen Aspekten ausgehen, um die korrekte und effiziente Verwendung von Indizes weiter zu vertiefen.

1. Indexfehler

Indexfehler sind ein häufiges Thema. Wenn von Datenbankoptimierung und Indexverwendung die Rede ist, kann man in einem Atemzug viele Szenarien aufzählen, in denen Indexe versagen, was nicht verwendet werden kann und was nicht verwendet werden sollte. Ich werde sie hier nicht einzeln aufzählen.

Von einem Indexfehler spricht man, wenn für ein Feld in einer Tabelle ein Index erstellt wird, der Index jedoch aufgrund einer falschen SQL-Anweisung ungültig wird.

Wenn die Indexspalte in SQL-Anweisungen als Teil eines Ausdrucks verwendet wird oder an Funktionen oder mathematischen Operationen beteiligt ist, wird der Index ungültig.

Beispielsweise kann die folgende Abfrage den Index der Spalte „Alter“ nicht verwenden:

Wählen Sie ID, Name, Alter aus t_user, wobei Alter + 1 = 7;

Es ist leicht zu erkennen, dass der Ausdruck in where eigentlich äquivalent zu age=8 ist, aber MySQL kann diesen Ausdruck nicht automatisch analysieren. Dies ist ausschließlich Benutzerverhalten.

(Aus dem vorherigen Artikel wissen wir, dass MySQL zuerst den Index nach Werten durchsucht und dann die Datenzeile zurückgibt, die dem Indexwert entspricht. Sobald die Indexspalte bearbeitet wird, kann die entsprechende Datenzeile nicht richtig gefunden werden. Daher wird die gesamte Tabelle Zeile für Zeile durchsucht, um die Abfrage zu vergleichen.)

2. Präfixindex und Indexselektivität

Manchmal führt die Verwendung einer Spalte mit sehr langem Inhalt als Indexspalte dazu, dass der Index sehr groß und langsam wird. Wenn Sie dieser Spalte einen Index hinzufügen müssen, ist die Lösung der im vorherigen Artikel erwähnte simulierte Hash-Index.

Normalerweise können die ersten paar Zeichen indiziert werden, wodurch der Indexspeicherplatz erheblich gespart und die Indexeffizienz verbessert werden kann, jedoch auch die Selektivität des Index verringert wird.

Die Selektivität eines Indexes bezeichnet das Verhältnis der Anzahl eindeutiger Indexwerte (auch Kardinalität genannt) zur Gesamtzahl der Datensätze in den Tabellendaten T und liegt im Bereich von 1/T bis 1. Je höher die Selektivität des Indexes, desto höher die Abfrageeffizienz, denn ein hochselektiver Index ermöglicht es MySQL, bei der Suche mehr Zeilen herauszufiltern.

Die Selektivität eines eindeutigen Indexes beträgt 1. Dies ist die beste Indexselektivität und weist die beste Leistung auf.

Bei Spalten vom Typ BLOB, TEXT oder großen VARCHAR muss die Spalte einen Präfixindex verwenden, um die Abfrageleistung zu verbessern, wenn sie als Abfragebedingungen verwendet werden (grundsätzlich sollten solche Vorgänge vermieden werden, manchmal sind sie jedoch erforderlich). Weil MySQL keine Indizierung der vollständigen Länge dieser Spalten zulässt.

3. Mehrspaltiger Index

Ein mehrspaltiger Index bedeutet, dass für jede Spalte ein unabhängiger Index erstellt wird.

Bei der SQL-Optimierung indizieren manche Benutzer alle Spalten in der Where-Bedingung in der Hoffnung, die Abfrageleistung zu optimieren. Tatsächlich ist eine solche Optimierung jedoch sehr falsch. Im besten Fall kann es sich nur um einen „Ein-Stern“-Index handeln, und seine Leistung kann mehrere Datenebenen unter der des wirklich optimalen Index liegen. Wenn es manchmal nicht möglich ist, einen „Drei-Sterne“-Index zu entwerfen, ist es besser, die Where-Klausel zu ignorieren und sich auf die Optimierung der Reihenfolge der Indexspalten zu konzentrieren oder einen vollständig abdeckenden Index zu erstellen.

Drei-Sterne-Index: In dem Buch „Relational Database Index Design and the Optimizers“ von Lahdenmaki und Leach wird ein „Drei-Sterne-System“ erwähnt, um zu bewerten, ob ein Index für eine Abfrage geeignet ist: Der Index erhält „einen Stern“, wenn er zusammengehörende Datensätze zusammenfügt; wenn die Reihenfolge der Daten im Index mit der Sortierreihenfolge in der Suche übereinstimmt, erhält er „zwei Sterne“; wenn die Spalten im Index alle von der Abfrage benötigten Spalten enthalten, erhält er „drei Sterne“.

Das Erstellen unabhängiger Einzelspaltenindizes für mehrere Spalten verbessert in den meisten Fällen nicht die Leistung von MySQL-Abfragen. Auch das ist der falsche Ansatz.

MySQL 5.0 und spätere Versionen führten eine Indexzusammenführungsstrategie ein, die mehrere einspaltige Indizes für eine Tabelle verwenden kann, um eine angegebene Zeile bis zu einem gewissen Grad zu lokalisieren. Frühere Versionen von MySQL konnten nur einen der einspaltigen Indizes verwenden, aber in diesem Fall war kein einspaltiger Index besonders effektiv.

Strategien zum Zusammenführen von Indizes sind manchmal das Ergebnis einer Optimierung, aber häufiger deuten sie darauf hin, dass die Indizes der Tabelle schlecht konstruiert sind:

1) Wenn eine Schnittmenge mehrerer Indizes (normalerweise mit mehreren UND-Bedingungen) verwendet wird, bedeutet dies normalerweise, dass ein mehrspaltiger Index mit allen relevanten Spalten erforderlich ist und nicht mehrere unabhängige einspaltige Indizes.

2) Wenn mehrere Indizes kombiniert werden müssen (normalerweise mit mehreren ODER-Bedingungen), werden für die Caching-, Sortier- und Zusammenführungsvorgänge des Algorithmus normalerweise große Mengen an CPU- und Speicherressourcen verbraucht. Dies gilt insbesondere, wenn einige der Indizes nicht sehr selektiv sind und große Mengen der vom Scan zurückgegebenen Daten zusammenführen müssen.

3) Der Optimierer berechnet diese nicht in die „Abfragekosten“, der Optimierer kümmert sich nur um zufällige Seitenaufrufe. Dies führt dazu, dass die Abfragekosten „unterschätzt“ werden, was dazu führt, dass der Ausführungsplan schlechter ist als ein direkter vollständiger Tabellenscan. Dadurch werden nicht nur mehr CPU- und Speicherressourcen verbraucht, sondern es kann auch die Parallelität der Abfrage beeinträchtigt werden. Wenn eine solche Abfrage jedoch einzeln ausgeführt wird, werden die Auswirkungen auf die Parallelität häufig ignoriert.

Wenn Sie im Ausführungsplan EXPLAIN eine Indexzusammenführung sehen, sollten Sie die Abfrage- und Tabellenstruktur überprüfen, um festzustellen, ob sie optimal sind. Sie können auch den Parameter optimizer_switch verwenden, um die Indexzusammenführungsfunktion zu deaktivieren, oder den Hinweis IGNORE INDEX verwenden, um den Optimierer bestimmte Indizes ignorieren zu lassen.

Bei mehrspaltigen Indizes wird der Index im Allgemeinen nicht ungültig, solange die Spalte ganz links in den Abfragebedingungen verwendet wird.

Nachfolgend sind einige Beispiele aufgeführt:

Die Tabelle t_user erstellt einen mehrspaltigen Index (ID, Name) wie folgt:

mysql> anzeigen, Tabelle erstellen t_user;
+--------+--------------+
| Tabelle | Tabelle erstellen |
+--------+--------------+
| t_user | CREATE TABLE `t_user` (
`id` int(11) NICHT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`Alter` int(11) DEFAULT NULL,
SCHLÜSSEL `idx` (`id`,`name`) MIT BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------+
1 Reihe im Set

Führen Sie die Abfrage nach ID wie folgt durch:

mysql> erläutern Sie „select * from t_user where id = 1;“
+----+----------+--------+------------+------+---------------+-----+---------+---------+-------+-------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+--------+------------+------+---------------+-----+---------+---------+-------+-------+------+------+------+
| 1 | SIMPLE | t_user | NULL | ref | idx | idx | 4 | const | 1 | 100 | NULL |
+----+----------+--------+------------+------+---------------+-----+---------+---------+-------+-------+------+------+------+
1 Reihe im Set

Am Typ im Ausführungsplan können wir erkennen, dass der Index gültig ist. Wenn Sie jedoch nach Namen abfragen, schlägt der Index fehl (vollständiger Tabellenscan) und zwar wie folgt:

mysql> erläutern Sie „select * from t_user where name = 'xcbeyond';“
+----+----------+--------+------------+------+---------------+---------+---------+------+---------+------+---------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+--------+------------+------+---------------+---------+---------+------+---------+------+---------+----------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Verwenden von where |
+----+----------+--------+------------+------+---------------+---------+---------+------+---------+------+---------+----------+----------+-------------+
1 Reihe im Set

4. Wählen Sie die entsprechende Indexspaltenreihenfolge

Die Reihenfolge der Indexspalten ist wirklich wichtig. Die richtige Reihenfolge hängt von der Abfrage ab, die den Index verwendet, und muss auch berücksichtigen, wie die Sortier- und Gruppierungsanforderungen besser erfüllt werden können (gilt nur für B-Tree-Indizes; Hash- oder andere Indizes speichern Daten nicht sequenziell).

In einem mehrspaltigen B-Tree-Index bedeutet die Reihenfolge der Indexspalten, dass der Index zuerst nach der Spalte ganz links sortiert wird. Daher kann der Index in aufsteigender oder absteigender Reihenfolge durchsucht werden, um die Abfrageanforderungen von Klauseln wie „order by“, „group by“ und „distinct“ zu erfüllen, die der Spaltenreihenfolge entsprechen.

Daher ist die Reihenfolge der Spalten in einem mehrspaltigen Index entscheidend. Eine Faustregel für die Auswahl der Spaltenreihenfolge in einem Index besteht darin, den selektivsten Index an den Anfang zu setzen. Diese Erfahrung ist in einigen Szenarien sehr nützlich, aber normalerweise nicht so wichtig wie das Vermeiden von zufälliger E/A und Sortierung, und das Problem muss umfassender betrachtet werden.

Wenn Sortieren und Gruppieren kein Problem darstellen müssen, empfiehlt es sich häufig, die selektivsten Spalten an den Anfang zu setzen. Derzeit besteht die Rolle des Indexes nur darin, die Suche der Where-Bedingung zu optimieren. In diesem Fall kann der so konzipierte Index tatsächlich die benötigten Zeilen möglichst schnell herausfiltern und ist zudem selektiver für Abfragen, die in der Where-Klausel nur die Präfixspalten des Indexes verwenden. Die Leistung hängt jedoch nicht nur von der Selektivität aller Indexspalten ab, sondern auch von den spezifischen Werten der Abfragebedingungen, dh von der Verteilung der Werte (die Reihenfolge der Indexspalten muss entsprechend den am häufigsten ausgeführten Abfragen angepasst werden, um die Indexspalten in diesem Fall möglichst selektiv zu gestalten).

5. Clustered-Index

Ein gruppierter Index ist kein separater Indextyp, sondern eine Datenspeichermethode, die Datenspeicherung und Indizierung miteinander kombiniert. Wenn Sie die Indexseite finden, finden Sie die Daten. Die genauen Details hängen von der Implementierung ab, aber der gruppierte Index von InnoDB speichert den B-Tree-Index und die Datenzeilen tatsächlich in derselben Struktur.

Nicht gruppierter Index: Datenspeicher und Index werden separat gespeichert und die Blattknoten der Indexstruktur zeigen auf die entsprechenden Zeilen der Daten. Wenn auf Daten zugegriffen werden muss (über den Index), wird der Index direkt im Speicher gesucht und dann werden die entsprechenden Daten auf der Festplatte über den Index gefunden. Aus diesem Grund ist die Geschwindigkeit langsam, wenn der Index nicht im Schlüsselpuffer gefunden wird.

Wenn eine Tabelle über einen gruppierten Index verfügt, werden ihre Datenzeilen tatsächlich auf den Blattseiten des Index gespeichert. „Clustered“ bedeutet, dass Datenzeilen und benachbarte Schlüsselwerte kompakt zusammen gespeichert werden. Da es unmöglich ist, Datenzeilen gleichzeitig an zwei verschiedenen Orten zu speichern, kann eine Tabelle nur einen gruppierten Index haben.

Clustered-Index-Einstellungen:

Der Standard ist der Primärschlüssel. Wenn kein Primärschlüssel definiert ist, wählt InnoDB stattdessen einen eindeutigen, nicht leeren Index. Wenn kein solcher Index vorhanden ist, definiert InnoD implizit einen Primärschlüssel als gruppierten Index. InnoDB clustert nur Datensätze, die sich auf derselben Seite befinden, auch wenn Seiten mit benachbarten Schlüsselwerten weit voneinander entfernt sind.

(Wenn Sie mit der B-Tree-Indexstruktur vertraut sind, wissen Sie, warum [Schlüssel, Daten] als Tupel in einem Knoten gespeichert werden.)

Ein gruppierter Primärschlüssel kann die Leistung verbessern, aber auch schwerwiegende Leistungsprobleme verursachen. Daher müssen Clustered-Indizes sorgfältig überlegt werden, insbesondere wenn die Speicher-Engine einer Tabelle von InnoDB zu einer anderen Engine geändert wird (oder umgekehrt).

Vorteile von Clustered-Indizes:

• Zusammengehörende Daten können gemeinsam gespeichert werden. Wenn Sie beispielsweise ein E-Mail-Postfach implementieren, können Sie Daten basierend auf der Benutzer-ID aggregieren, sodass Sie nur eine kleine Datenmenge von der Festplatte lesen müssen, um alle E-Mails für einen Benutzer abzurufen.
• Schnellerer Datenzugriff. Der gruppierte Index platziert sowohl den Index als auch die Daten im selben B-Baum. Daher ist das Abrufen von Daten aus einem gruppierten Index schneller als das Abrufen von Daten aus einem nicht gruppierten Index.
• Abfragen mit abdeckenden Index-Scans können die Primärschlüsselwerte in den Seitenknoten direkt verwenden.
  

Nachteile des Clustered-Index:

• Dadurch wird die Leistung von E/A-intensiven Anwendungen maximiert. Wenn jedoch alle Daten im Speicher abgelegt sind, ist die Zugriffsreihenfolge nicht so wichtig und der gruppierte Index bietet keinen Vorteil.
• Die Einfügegeschwindigkeit hängt stark von der Einfügereihenfolge ab. Das Einfügen in der Primärschlüsselreihenfolge ist der schnellste Weg, Daten in eine InnoDB-Tabelle zu laden. Wenn die Daten jedoch nicht schrittweise geladen werden, empfiehlt es sich, nach Abschluss des Ladevorgangs die Tabelle mit OPTIMIZE TABLE neu zu organisieren.
• Das Aktualisieren von gruppierten Indexspalten ist teuer. Weil es InnoDB zwingt, jede aktualisierte Zeile an einen neuen Speicherort zu verschieben.
• Bei Tabellen, die auf gruppierten Indizes basieren, kann es beim Einfügen neuer Zeilen oder beim Aktualisieren des Primärschlüssels, was zu einer Zeilenverschiebung führt, zu „Seitenaufteilungen“ kommen. Wenn der Primärschlüsselwert einer Zeile erfordert, dass die Zeile in eine ganze Seite eingefügt wird, teilt die Speicher-Engine die Seite in zwei Seiten auf, um die Zeile unterzubringen. Dies ist eine Seitenaufteilungsoperation, was bedeutet, dass die Tabelle mehr Speicherplatz beansprucht.
• Clustered-Indizes können die Ausführung vollständiger Tabellenscans verlangsamen, insbesondere bei spärlicher Zeilenanzahl oder wenn die Daten aufgrund von Seitenaufteilungen nicht zusammenhängend gespeichert sind.
• Sekundärindizes (nicht gruppierte Indizes) können größer sein als Sie denken. Weil die Blattknoten des sekundären Indexes die Primärschlüsselspalten der referenzierten Zeile enthalten.
• Für den sekundären Indexzugriff sind zwei Indexsuchen statt einer erforderlich.
  

6. Abdeckungsindex

Normalerweise werden entsprechende Indizes auf Grundlage der Where-Bedingungen der Abfrage erstellt, dies ist jedoch nur ein Aspekt der Indexoptimierung. Ein gut konzipierter Index sollte die gesamte Abfrage berücksichtigen, nicht nur die Where-Bedingung. Indizes stellen tatsächlich eine effiziente Methode zum Auffinden von Daten dar. MySQL kann mithilfe von Indizes jedoch auch Spaltendaten direkt abrufen, sodass das Lesen von Datenzeilen nicht mehr erforderlich ist. Wenn ein Index alle abzufragenden Feldwerte enthält, nennen wir ihn einen „abdeckenden Index“, d. h. ein Index deckt alle Spalten der Where-Bedingung ab.

Die Vorteile der Indexabdeckung sind folgende:

• Indexeinträge sind normalerweise viel kleiner als die Größe einer Datenzeile. Wenn also nur der Index gelesen werden muss, kann MySQL die Menge der Datenzugriffe erheblich reduzieren. Dies ist insbesondere bei zwischengespeicherten Workloads wichtig, bei denen ein großer Teil der Reaktionszeit für das Kopieren von Daten aufgewendet wird. Das Abdecken von Indizes ist auch bei E/A-intensiven Anwendungen hilfreich, da der Index kleiner als die Daten ist und leichter im Speicher abgelegt werden kann.
• Da der Index in der Reihenfolge der Spaltenwerte gespeichert wird, erfordern E/A-intensive Bereichsabfragen viel weniger E/A als das zufällige Lesen jeder einzelnen Datenzeile von der Festplatte. Bei einigen Speicher-Engines wie MyISAM und Percona XtraDB ist es sogar möglich, den Index mit dem Befehl POTIMIZE vollständig zu sortieren, wodurch einfache Bereichsabfragen einen vollständig sortierten Indexzugriff verwenden können.
• Einige Speicher-Engines, beispielsweise MyISAM , speichern nur Indizes im Speicher zwischen. Da die Daten vom Betriebssystem zwischengespeichert werden müssen, ist für den Zugriff auf die Daten ein Systemaufruf erforderlich. Dies kann zu schwerwiegenden Leistungsproblemen führen, insbesondere in Szenarios, in denen Systemaufrufe den größten Kostenfaktor beim Datenzugriff darstellen.
• Aufgrund des gruppierten Indexes von InnoDB sind abdeckende Indizes besonders für InnoDB-Tabellen nützlich. Der sekundäre Index von InnoDB speichert den Primärschlüsselwert der Zeile im Blattknoten. Wenn also der sekundäre Primärschlüssel die Abfrage abdecken kann, kann die sekundäre Abfrage des Primärschlüsselindex vermieden werden.
  

Nicht alle Indextypen können abdeckende Indizes sein. Überdeckende Indizes müssen die Indexspalte speichern, während Hash-Indizes, räumliche Indizes und Volltextindizes die Werte der Indexspalten nicht speichern. Daher kann MySQL nur B-Tree verwenden, um überdeckende Indizes zu erstellen. Darüber hinaus implementieren verschiedene Speicher-Engines überdeckende Indizes auf unterschiedliche Weise, und nicht alle Engines unterstützen überdeckende Indizes.

7. Verwenden Sie den Index-Scan zum Sortieren

MySQL bietet zwei Möglichkeiten, geordnete Ergebnismengen zu generieren: durch eine Sortieroperation oder durch Scannen in Indexreihenfolge. Wenn der Wert der Typspalte in EXPLAIN index , bedeutet dies, dass MySQL zum Sortieren einen Index-Scan verwendet.

Das Scannen des Indexes selbst geht schnell, weil man nur von einem Indexdatensatz zum unmittelbar nächsten Datensatz wechseln muss. Wenn der Index jedoch nicht alle für die Abfrage erforderlichen Spalten abdeckt, müssen Sie bei jedem Scannen eines Indexdatensatzes zur Tabelle zurückkehren, um die entsprechende Zeile abzufragen. Da es sich im Wesentlichen um zufällige E/A-Vorgänge handelt, ist das Lesen der Daten in Indexreihenfolge normalerweise langsamer als ein sequentieller vollständiger Tabellenscan, insbesondere bei E/A-intensiven Arbeitslasten.

MySQL kann denselben Index sowohl zum Sortieren als auch zum Suchen von Zeilen verwenden. Daher sollte der Index nach Möglichkeit so gestaltet sein, dass er beide Situationen gleichzeitig erfüllt, d. h. die Indexspalte wird als Sortierspalte verwendet.

• MySQL kann den Index nur dann zum Sortieren der Ergebnisse verwenden, wenn die Reihenfolge der Indexspalten genau mit der Reihenfolge order by Klausel übereinstimmt und die Sortierrichtung aller Spalten gleich ist.
• Wenn die Abfrage mehrere Tabellen verbinden muss, kann der Index nur dann zum Sortieren verwendet werden, wenn alle von order by Klausel referenzierten Felder aus der ersten Tabelle stammen. Die Einschränkungen order by -Klausel sind dieselben wie bei der Suchanfrage: Das am weitesten links stehende Präfix des Index muss erfüllt sein. Andernfalls muss MySQL sequenzielle Operationen ausführen und kann keine Indexsortierung verwenden.
  

8. Redundante und doppelte Indizes

Doppelte Indizes sind Indizes desselben Typs, die für dieselben Spalten in derselben Reihenfolge erstellt werden. Die Erstellung doppelter Indizes sollte vermieden und nach Entdeckung sofort entfernt werden.

Zum Beispiel:

Tabellentest erstellen{
 id int nicht null Primärschlüssel,
 eine Ganzzahl ungleich null,
 b int ungleich null,
 eindeutig (ID)
 Index(ID)
}engine=InnoDB;

Ein unerfahrener Benutzer möchte möglicherweise einen Primärschlüssel erstellen, eine eindeutige Einschränkung (unique(id))， hinzufügen und dann einen Index (index(id)) zur Abfrageverwendung hinzufügen. Über die Indizes werden jedoch sowohl eindeutige Einschränkungen als auch Primärschlüsseleinschränkungen verwendet, sodass die obige Anweisung tatsächlich drei doppelte Indizes für dieselbe Spalte erstellt. Normalerweise gibt es hierfür keinen Grund, außer dass verschiedene Indextypen für die gleiche Spalte erstellt werden sollen, um unterschiedliche Abfrageanforderungen zu erfüllen.

Es gibt einige Unterschiede zwischen redundanten und doppelten Indizes. Wenn Sie beispielsweise einen Index （A,B） erstellen und dann （A） erstellen, handelt es sich um einen redundanten Index, da A ein Präfixindex des vorherigen Indexes ist. Der Index （A,B） kann genauso verwendet werden wie A. Wenn Sie jedoch einen Index （B,A） erstellen, handelt es sich dabei nicht um einen redundanten Index und dies gilt auch für Index B. Weil B nicht der äußerste linke Präfixindex des Index (A,B） ist. Darüber hinaus sind andere Indextypen, wie etwa Hash- und Volltextindizes, keine redundanten Indizes für B-Tree.

Redundante Indizes treten normalerweise auf, wenn einer Tabelle neue Indizes hinzugefügt werden. Beispielsweise könnte jemand einen neuen Index （A,B） hinzufügen, anstatt den vorhandenen Index （A） zu erweitern. Ein anderer Fall ist die Erweiterung eines Index auf （A,ID） , wobei ID der Primärschlüssel ist. Bei InnoDB ist die Primärschlüsselspalte bereits im Sekundärindex enthalten, daher ist dies ebenfalls redundant.

In den meisten Fällen werden redundante Indizes nicht benötigt und vorhandene Indizes sollten erweitert werden, anstatt neue zu erstellen. Manchmal sind jedoch aus Leistungsgründen redundante Indizes erforderlich, da die Erweiterung eines vorhandenen Indexes dazu führen würde, dass dieser zu groß wird, was wiederum die Leistung anderer Abfragen, die den Index verwenden, beeinträchtigen würde. Wenn Sie beispielsweise einem Integer-Spaltenindex eine sehr lange varchar -Spalte hinzufügen, kann die Leistung erheblich nachlassen. Dies gilt insbesondere, wenn ein Index vorhanden ist, der diesen Index abdeckt, oder wenn es sich um eine MyISAM Tabelle handelt und viele Bereichsabfragen vorhanden sind.

Die Lösung für redundante und doppelte Indizes ist sehr einfach: Löschen Sie sie einfach. Aber zuerst müssen wir einen solchen Index finden. Sie können sie finden, indem Sie einige komplexe Abfragen schreiben, die auf information_schema -Tabellen zugreifen. Es gibt jedoch zwei einfachere Möglichkeiten, sie mithilfe einiger Ansichten in Shlomi Noach common_schema zu lokalisieren (Common_Schema ist ein Satz gemeinsamer Speicher und Ansichten, die auf einem Server installiert werden können). Eine andere Methode ist die Verwendung pt_duplicate-key-checker im Percona Toolkit , das die Tabellenstruktur analysiert, um redundante und doppelte Indizes zu finden.

9. Unbenutzte Indizes

Zusätzlich zu redundanten und doppelten Indizes kann es einige Indizes geben, die der Server nie verwendet. Ein solcher Index ist völlig überflüssig und es wird empfohlen, ihn direkt zu löschen.

Sie können die Tabelle table_io_waits_summary_by_index_usage im Performance_schema verwenden, um Folgendes zu finden:

SELECT Objektschema, Objektname, Indexname FROM Performanceschema.table_io_waits_summary_by_index_usage, wobei Indexname NICHT NULL ist und count_star = 0 ORDER BY Objektschema, Objektname, Indexname;

10. Indizes und Sperren

Durch Indizes können Abfragen weniger Zeilen sperren . Wenn Ihre Abfragen nie auf Zeilen zugreifen, die nicht benötigt werden, werden weniger Zeilen gesperrt, was sich in zweierlei Hinsicht positiv auf die Leistung auswirkt.

Erstens: Obwohl die Zeilensperren von InnoDB sehr effizient sind und sehr wenig Speicher verbrauchen, entsteht beim Sperren von Zeilen dennoch zusätzlicher Overhead. Zweitens führt das Sperren von mehr Zeilen als nötig zu mehr Sperrkonflikten und verringert die Parallelität.

11. Zusammenfassung

Der obige Langtext dient der Erläuterung, wie Indizes effizient eingesetzt und Fehlanwendungen vermieden werden. Die Indizierung scheint einfach, ist aber in Wirklichkeit sehr kompliziert. Um sie wirklich gut zu nutzen, müssen Sie ständig üben. Die Praxis ist der einzige Weg, die Wahrheit zu testen. Dieser Artikel stellt nur die allgemeine Verwendung von Indizes dar. Sie müssen nur den gesamten Artikel lesen und ihn mit bestimmten Beispielen kombinieren oder sich an die Stellen erinnern, an denen er in der Vergangenheit verwendet wurde, um ein umfassendes Verständnis des Ganzen zu erlangen und zu verstehen, wie der Index funktioniert. Wenn Sie in Zukunft Indizes verwenden oder optimieren, können Sie von diesen Aspekten ausgehen, um die korrekte und effiziente Verwendung von Indizes weiter zu vertiefen.

Im üblichen Gebrauch von Indizes gibt es folgende Zusammenfassungen und Vorschläge:

1. Das Erstellen eines Indexes für ein Feld mit hoher Unterscheidungskraft kann den Index effektiv nutzen. Wenn die Unterscheidungskraft zu gering ist, kann der Index nicht effektiv genutzt werden und es müssen möglicherweise alle Datenseiten gescannt werden. In diesem Fall gibt es kaum einen Unterschied zur Nichtverwendung eines Indexes.
2. Bei gemeinsamen Indizes ist das Prinzip des Matchings ganz links zu beachten: Das Matching muss von links nach rechts erfolgen. MySQL führt das Matching so lange nach rechts aus, bis es auf eine Bereichsabfrage (>、<、between、like) stößt und das Matching beendet. Wenn beispielsweise a = 1 and b = 2 and c > 3 and d = 4 in der Reihenfolge (a, b, c, d) erstellt werden, wird d nicht verwendet. Wenn ein Index in der Reihenfolge (a, b, d, c) erstellt wird, können alle verwendet werden. Die Reihenfolge von a, b, d kann beliebig angepasst werden.
3. Verwenden Sie beim Abfragen von Datensätzen seltener * und versuchen Sie, die Indexabdeckung zu verwenden, um Tabellenrückgabevorgänge zu reduzieren und die Effizienz zu verbessern.
4. Einige Abfragen können gemeinsame Indizes verwenden, die wiederum Index-Pushdown verwenden können und auch Tabellenrückgabevorgänge reduzieren und die Effizienz verbessern können.
5. Es ist verboten, Funktionen oder Operatoren auf Indexfelder anzuwenden, da dies den Index ungültig macht.
6. Der Vergleich von Zeichenfolgenfeldern mit Zahlen macht den Index ungültig.
7. Die Fuzzy-Abfrage '%值%' macht den Index ungültig und wandelt ihn in einen vollständigen Tabellenscan um, '值%' kann den Index jedoch effektiv nutzen.
8. Versuchen Sie beim Sortieren Indexfelder zu verwenden. Dadurch kann der Sortiervorgang reduziert und die Abfrageeffizienz verbessert werden.

Oben finden Sie Einzelheiten zur effizienten und korrekten Verwendung von Indizes zur Optimierung der MySQL-Leistung. Weitere Informationen zu MySQL-Indizes finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!