Ausführliche Erläuterung der Typen, Vor- und Nachteile von MySQL index_MySQL

Indizes sind spezielle Dateien (der Index der InnoDB-Datentabelle ist ein integraler Bestandteil des Tabellenbereichs). Sie enthalten Referenzzeiger auf alle Datensätze in der Datentabelle.
Hinweis:
[1] Indizes sind nicht allmächtig! Indizes können Datenabrufvorgänge beschleunigen, Datenänderungsvorgänge jedoch verlangsamen. Bei jeder Änderung eines Datensatzes muss der Index aktualisiert werden. Um diesen Mangel in einem bestimmten Programm auszugleichen, verfügen viele SQL-Befehle über ein DELAY_KEY_WRITE-Element. Die Funktion dieser Option besteht darin, MySQL vorübergehend daran zu hindern, den Index sofort zu aktualisieren, nachdem jeder neue Datensatz eingefügt und jeder vorhandene Datensatz durch diesen Befehl geändert wurde. Die Aktualisierung des Index wartet, bis alle Datensätze eingefügt/geändert wurden. In Situationen, in denen viele neue Datensätze in eine Datentabelle eingefügt werden müssen, ist die Rolle der Option DELAY_KEY_WRITE sehr offensichtlich.
[2] Darüber hinaus beanspruchen Indizes erheblichen Speicherplatz auf der Festplatte. Daher sollten nur die am häufigsten abgefragten und am häufigsten sortierten Datenspalten indiziert werden. Beachten Sie, dass eine Indizierung keine großen praktischen Auswirkungen hat, wenn eine Datenspalte viele doppelte Inhalte enthält.
Theoretisch ist es möglich, für jedes Feld in der Datentabelle einen Index zu erstellen, MySQL begrenzt jedoch die Gesamtzahl der Indizes in derselben Datentabelle auf 16.

1. Index der InnoDB-Datentabelle

Im Vergleich zu MyISAM-Datentabellen sind Indizes für InnoDB-Daten viel wichtiger. Bei InnoDB-Datentabellen sind Indizes viel wichtiger als bei InnoDB-Datentabellen. In InnoDB-Datentabellen spielen Indizes nicht nur eine Rolle bei der Suche nach Datensätzen, sondern sind auch die Grundlage für den Sperrmechanismus auf Datenzeilenebene. „Sperren auf Datenzeilenebene“ bedeutet das Sperren einzelner Datensätze, die während der Ausführung von Transaktionsvorgängen verarbeitet werden, um zu verhindern, dass andere Benutzer darauf zugreifen. Diese Sperre wirkt sich auf die Befehle SELECT...LOCK IN SHARE MODE, SELECT...FOR UPDATE und die Befehle INSERT, UPDATE und DELETE aus (ist aber nicht darauf beschränkt).
Aus Effizienzgründen erfolgt die Sperrung von InnoDB-Datentabellen auf Zeilenebene tatsächlich für deren Indizes und nicht für die Datentabelle selbst. Offensichtlich kann der Sperrmechanismus auf Datenzeilenebene nur dann wirksam sein, wenn die relevante Datentabelle über einen geeigneten Index zum Sperren verfügt.

2. Einschränkungen

Wenn in der Abfragebedingung der WEHERE-Klausel ein Ungleichheitszeichen (WHERE-Farbe != ...) vorhanden ist, kann MySQL den Index nicht verwenden.
Wenn in der Abfragebedingung der WHERE-Klausel eine Funktion (WHERE DAY(column) = ...) verwendet wird, kann MySQL den Index ebenfalls nicht verwenden.
Bei einer JOIN-Operation (wenn Daten aus mehreren Datentabellen extrahiert werden müssen) kann MySQL nur dann Indizes verwenden, wenn die Datentypen des Primärschlüssels und des Fremdschlüssels gleich sind.
Wenn in den Abfragebedingungen der WHERE-Klausel die Vergleichsoperatoren LIKE und REGEXP verwendet werden, kann MySQL den Index nur verwenden, wenn das erste Zeichen der Suchvorlage kein Platzhalterzeichen ist. Wenn die Abfragebedingung beispielsweise LIKE „abc%“ lautet, verwendet MySQL den Index. Wenn die Abfragebedingung LIKE „�c“ lautet, verwendet MySQL den Index nicht.
Bei einer ORDER BY-Operation verwendet MySQL den Index nur, wenn die Sortierbedingung kein Abfragebedingungsausdruck ist. (Bei Abfragen mit mehreren Datentabellen haben diese Indizes jedoch keinen Einfluss auf die Beschleunigung von ORDER BY, selbst wenn Indizes verfügbar sind.)
Wenn eine Datenspalte viele doppelte Werte enthält, führt dies selbst dann nicht zu guten Ergebnissen, wenn sie indiziert ist. Wenn eine Datenspalte beispielsweise nur Werte wie „0/1“ oder „J/N“ enthält, muss kein Index dafür erstellt werden.

Normaler Index, eindeutiger Index und Primärindex

1. Gewöhnlicher Index

Die einzige Aufgabe eines normalen Index (ein durch das Schlüsselwort KEY oder INDEX definierter Index) besteht darin, den Zugriff auf Daten zu beschleunigen. Daher sollten Indizes nur für diejenigen Datenspalten erstellt werden, die am häufigsten in Abfragebedingungen (Spalte WHERE = ...) oder Sortierbedingungen (Spalte ORDER BY) vorkommen. Wann immer möglich, sollten Sie zum Erstellen eines Index eine Datenspalte mit den übersichtlichsten und kompaktesten Daten (z. B. eine Datenspalte vom Typ Ganzzahl) auswählen.

2. Eindeutiger Index
Normale Indizes ermöglichen, dass die indizierten Datenspalten doppelte Werte enthalten. Da beispielsweise Personen möglicherweise denselben Namen haben, erscheint derselbe Name möglicherweise zweimal oder öfter in derselben Datentabelle „Mitarbeiterprofil“.
Wenn Sie feststellen können, dass eine bestimmte Datenspalte nur voneinander verschiedene Werte enthält, sollten Sie beim Erstellen eines Indexes für diese Datenspalte das Schlüsselwort UNIQUE verwenden, um diesen als eindeutigen Index zu definieren. Dies hat folgende Vorteile: Erstens vereinfacht MySQL die Verwaltung dieses Indexes und der Index wird effizienter. Zweitens überprüft MySQL automatisch den Wert dieses Felds im neuen Datensatz, wenn ein neuer Datensatz in die Datentabelle eingefügt wird Dieses Feld ist bereits in einem Datensatz enthalten. Wenn dies der Fall ist, weigert sich MySQL, den neuen Datensatz einzufügen. Mit anderen Worten: Ein eindeutiger Index kann die Einzigartigkeit von Datensätzen sicherstellen. Tatsächlich besteht der Zweck der Erstellung eindeutiger Indizes in vielen Fällen nicht darin, die Zugriffsgeschwindigkeit zu erhöhen, sondern lediglich darin, Datenduplizierung zu vermeiden.

3. Hauptindex

Es wurde bereits mehrfach betont: Für das Primärschlüsselfeld muss ein Index erstellt werden. Dieser Index ist der sogenannte „Primärindex“. Der einzige Unterschied zwischen einem Primärindex und einem eindeutigen Index besteht darin, dass das Schlüsselwort, das bei der Definition des ersteren verwendet wird, PRIMARY statt UNIQUE ist.

4. Fremdschlüsselindex

Wenn eine Fremdschlüsseleinschränkung für ein Fremdschlüsselfeld definiert ist, definiert MySQL einen internen Index, um die Fremdschlüsseleinschränkungen möglichst effizient zu verwalten und zu nutzen.

5. Zusammengesetzter Index

Indizes können mehrere Datenspalten abdecken, z. B. den Index INDEX(SpalteA, SpalteB). Das Merkmal dieses Index besteht darin, dass MySQL einen solchen Index selektiv verwenden kann. Wenn der Abfragevorgang nur einen Index für die Datenspalte Spalte A verwenden muss, können Sie den zusammengesetzten Index INDEX (Spalte A, Spalte B) verwenden. Diese Verwendung gilt jedoch nur für die Kombination von Datenspalten, die im zusammengesetzten Index an erster Stelle stehen. Beispielsweise kann INDEX(A, B, C) als Index von A oder (A, B) verwendet werden, aber nicht als Index von B, C oder (B, C).

6. Indexlänge

Beim Definieren von Indizes für Datenspalten vom Typ CHAR und VARCHAR können Sie die Länge des Index auf eine bestimmte Anzahl von Zeichen begrenzen (diese Anzahl muss kleiner sein als die maximal zulässige Anzahl von Zeichen in diesem Feld). Dies hat den Vorteil, dass eine Indexdatei generiert werden kann, die kleiner und schneller abrufbar ist. In den meisten Anwendungen basieren die String-Daten in der Datenbank meist auf verschiedenen Namen. Die Einstellung der Indexlänge auf 10 bis 15 Zeichen reicht aus, um den Suchbereich auf wenige Datensätze einzugrenzen.
Beim Erstellen von Indizes für Datenspalten vom Typ BLOB und TEXT muss die Länge des Index begrenzt werden; die von MySQL maximal zulässige Indexlänge beträgt 255 Zeichen.
Volltextindex

Ein normaler Index für ein Textfeld kann nur das Abrufen der Zeichenfolge beschleunigen, die am Anfang des Feldinhalts erscheint (d. h. der Zeichen am Anfang des Feldinhalts). Wenn das Feld einen größeren Textabschnitt speichert, der aus mehreren oder sogar mehreren Wörtern besteht, sind gewöhnliche Indizes von geringem Nutzen. Diese Art des Abrufs erfolgt häufig in der Form LIKE %word%, was für MySQL sehr kompliziert ist. Wenn die zu verarbeitende Datenmenge groß ist, ist die Antwortzeit sehr lang.
Hier können Volltextindizes hilfreich sein. Bei der Generierung dieser Art von Index erstellt MySQL eine Liste aller Wörter, die im Text vorkommen, und Abfragevorgänge rufen relevante Datensätze basierend auf dieser Liste ab. Der Volltextindex kann zusammen mit der Datentabelle erstellt oder später bei Bedarf verwendet werden

Fügen Sie den folgenden Befehl hinzu:
ALTER TABLE Tabellenname ADD FULLTEXT(Spalte1, Spalte2)

Mit einem Volltextindex können Sie mit dem SELECT-Abfragebefehl Datensätze abrufen, die ein oder mehrere gegebene Wörter enthalten. Das Folgende ist die grundlegende Syntax dieser Art von Abfragebefehl:
SELECT * FROM Tabellenname
WHERE MATCH(column1, columns2) AGAINST(‘word1′, ‚word2′, ‚word3′)

Der obige Befehl fragt alle Datensätze ab, die Wort1, Wort2 und Wort3 in den Feldern Spalte1 und Spalte2 enthalten.

Hinweis: InnoDB-Datentabellen unterstützen keine Volltextindizierung.

Abfrage- und Indexoptimierung

Nur wenn genügend Testdaten in der Datenbank vorhanden sind, haben die Leistungstestergebnisse einen tatsächlichen Referenzwert. Befinden sich in der Testdatenbank nur wenige hundert Datensätze, werden diese häufig bereits nach der Ausführung des ersten Abfragebefehls alle in den Speicher geladen, wodurch nachfolgende Abfragebefehle sehr schnell ausgeführt werden – unabhängig davon, ob Indizes verwendet werden. Die Ergebnisse des Datenbankleistungstests sind nur dann aussagekräftig, wenn die Anzahl der Datensätze in der Datenbank 1.000 übersteigt und die Gesamtdatenmenge den Gesamtspeicher auf dem MySQL-Server übersteigt.

Wenn Benutzer nicht sicher sind, welche Datenspalten zum Erstellen von Indizes verwendet werden sollen, kann häufig der Befehl EXPLAIN SELECT hilfreich sein. Dabei wird einem gewöhnlichen SELECT-Befehl lediglich das Schlüsselwort EXPLAIN vorangestellt. Mit diesem Schlüsselwort führt MySQL den SELECT-Befehl nicht aus, sondern analysiert ihn. MySQL listet Informationen wie den Abfrageausführungsprozess und die verwendeten Indizes (falls vorhanden) in Form einer Tabelle auf.
In der Ausgabe des EXPLAIN-Befehls ist Spalte 1 der Name der aus der Datenbank gelesenen Datentabelle und sie sind in der Reihenfolge angeordnet, in der sie gelesen werden. Die Typspalte gibt die Assoziationsbeziehung (JOIN) zwischen dieser Datentabelle und anderen Datentabellen an. Unter den verschiedenen Arten von Beziehungen ist System die effizienteste, gefolgt von const, eq_ref, ref, range, index und All (Alle bedeuten: Diese Daten entsprechen jedem Datensatz in der Datentabelle der oberen Ebene. Alle Datensätze in der Tabelle müssen gelesen werden (dies lässt sich oft durch einen Index vermeiden).

Die Datenspalte „posable_keys“ gibt die verschiedenen Indizes an, die MySQL bei der Suche nach Datensätzen verwenden kann. Die Schlüsseldatenspalte ist der tatsächlich von MySQL ausgewählte Index. Die Länge dieses Index in Bytes ist in der Datenspalte key_len angegeben. Für einen Index für eine INTEGER-Datenspalte wäre die Bytelänge beispielsweise 4. Wenn ein zusammengesetzter Index verwendet wird, können Sie in der Datenspalte key_len auch sehen, welche Teile davon von MySQL verwendet werden. Generell gilt: Je kleiner der Wert in der Datenspalte key_len, desto besser (d. h. schneller).
Die Referenzdatenspalte gibt den Namen der Datenspalte in einer anderen Datentabelle in der Beziehung an. Die Zeilendatenspalte ist die Anzahl der Datenzeilen, die MySQL bei der Ausführung dieser Abfrage voraussichtlich aus dieser Datentabelle lesen wird. Das Produkt aller Zahlen in der Zeilendatenspalte gibt uns eine Vorstellung davon, wie viele Kombinationen diese Abfrage verarbeiten muss.
Schließlich enthält die zusätzliche Datenspalte weitere Informationen zur JOIN-Operation. Wenn MySQL beispielsweise beim Ausführen dieser Abfrage eine temporäre Datentabelle erstellen muss, werden in der zusätzlichen Spalte die Wörter „using temporary“ angezeigt.

Das Obige ist der gesamte Inhalt dieses Artikels. Ich hoffe, dass er für das Studium aller hilfreich sein wird, und ich hoffe, dass Sie mich sehr unterstützen werden.