Indexfunktion der MySQL-Serie (5).

Indizes sind spezielle Dateien (die Indizes der InnoDB-Datentabelle sind Bestandteil des Tabellenbereichs. Sie enthalten Referenzzeiger auf alle Datensätze in der Datentabelle). Indizes sind kein Allheilmittel. Sie können Datenabrufvorgänge beschleunigen, aber sie können Datenänderungsvorgänge verlangsamen. Bei jeder Änderung eines Datensatzes muss der Index aktualisiert werden. Um diesen Mangel einigermaßen auszugleichen, verfügen viele SQL-Befehle über einen DELAY_KEY_WRITE-Eintrag. 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 durch diesen Befehl geändert wurde. Die Aktualisierung des Index wartet, bis alle Datensätze eingefügt/geändert wurden. Die Rolle der Option DELAY_KEY_WRITE wird sehr deutlich, wenn viele neue Datensätze in eine Datentabelle eingefügt werden müssen. 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． Indizes von InnoDB-Datentabellen

Im Vergleich zu InnoDB-Datentabellen sind Indizes für InnoDB-Datentabellen viel wichtiger. 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 betrifft (ist aber nicht beschränkt auf) die Befehle SELECT, LOCKINSHAREMODE, SELECT, FORUPDATE und INSERT, UPDATE und DELETE. Aus Effizienzgründen erfolgt die Sperrung von InnoDB-Tabellen auf Zeilenebene tatsächlich für deren Indizes und nicht für die Tabelle 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 WHERE-Klausel ein Ungleichheitszeichen (WHERE-Spalte !=) vorhanden ist, kann MySQL den Index nicht verwenden. Wenn die Funktion (WHERE DAY (column) =) in der Abfragebedingung der WHERE-Klausel 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 der Datentyp des Primärschlüssels und des Fremdschlüssels gleich ist.

Wenn in der Abfragebedingung 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 „%abc“ 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 nur geringe Auswirkungen auf die Beschleunigung von ORDER BY, selbst wenn Indizes verfügbar sind.) Wenn eine Datenspalte viele doppelte Werte enthält, führt sie 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.

Indexkategorie

1. Gewöhnlicher Index

Die einzige Aufgabe eines gewöhnlichen 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 die 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

Ein gewöhnlicher Index ermöglicht, dass die indizierte Datenspalte doppelte Werte enthält. Da beispielsweise Personen möglicherweise denselben Namen haben, erscheint derselbe Name möglicherweise zweimal oder öfter in derselben Datentabelle „Mitarbeiterprofil“.

Wenn Sie sicher sein können, dass eine bestimmte Datenspalte nur voneinander verschiedene Werte enthält, sollten Sie beim Erstellen eines Index für diese Datenspalte das Schlüsselwort UNIQUE verwenden, um sie als eindeutigen Index zu definieren . Die Vorteile davon sind: 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． Primärindex

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 ersterer mithilfe des Schlüsselworts PRIMARY anstelle von UNIQUE definiert wird.

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． Der zusammengesetzte Index

kann mehrere Datenspalten abdecken, z. B. den Index INDEX (SpalteA, SpalteB). Das Merkmal dieser Art von Index besteht darin, dass MySQL einen solchen Index selektiv verwenden kann. Wenn der Abfragevorgang nur einen Index für die Datenspalte SpalteA erfordert, können Sie den zusammengesetzten Index INDEX(SpalteA, SpalteB) 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 für A oder (A, B) verwendet werden, jedoch nicht als Index für B, C oder (B, C).

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 beschränken (diese Zahl muss sein). weniger als die maximal zulässige Anzahl von Zeichen für dieses Feld). Dies hat den Vorteil, dass eine Indexdatei generiert werden kann, die kleiner und schneller abrufbar ist. In den meisten Anwendungen basieren die Zeichenfolgendaten 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 müssen Einschränkungen hinsichtlich der Länge des Index vorgenommen werden. Der von MySQL maximal zulässige Index ist ein normaler Index für ein Volltextindex-Textfeld, der die angezeigte Zeichenfolge nur beschleunigen kann den Anfang des Feldinhalts (d. h. die Zeichen am Anfang des Feldinhalts), um den Suchvorgang auszuführen. Wenn das Feld einen größeren Textabschnitt speichert, der aus mehreren oder sogar mehreren Wörtern besteht, sind gewöhnliche Indizes von geringem Nutzen. Dieser Abruf erfolgt häufig in der Form, was für MySQL komplex ist und zu langen Antwortzeiten führen kann, wenn die zu verarbeitende Datenmenge groß ist.

In solchen Situationen kann der Volltextindex (Volltextindex) seine Talente zeigen. Bei der Generierung dieser Art von Index erstellt MySQL eine Liste aller Wörter, die im Text vorkommen, und Abfragevorgänge verwenden diese Liste, um relevante Datensätze abzurufen. Der Volltextindex kann zusammen mit der Datentabelle erstellt werden, oder Sie können den folgenden Befehl verwenden, um ihn bei Bedarf in der Zukunft hinzuzufügen:

ALTER TABLE Tabellenname ADD FULLTEXT (Spalte1, Spalte2) Mit dem Volltextindex Textindex können Sie mit SELECT-Abfragebefehlen Datensätze abrufen, die ein oder mehrere gegebene Wörter enthalten. Das Folgende ist die grundlegende Syntax dieser Art von Abfragebefehl:

SELECT * FROM tablename

WHERE MATCH (column1,column2) AGAINST('word1','word2','word3')

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

Hinweis: InnoDB-Datentabellen unterstützen keine Volltextindizierung.

Abfrage und Index

Nur ​​wenn genügend Testdaten in der Datenbank vorhanden sind, haben die Ergebnisse des Leistungstests einen tatsächlichen Referenzwert. Befinden sich nur wenige hundert Datensätze in der Testdatenbank, werden diese häufig 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 oder nicht. 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, für welche Datenspalten sie Indizes erstellen sollen, kann ihnen häufig der Befehl EXPLAIN SELECT helfen. 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 den Abfrageausführungsprozess und die verwendeten Indizes in 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 Beziehung (JOIN) zwischen dieser Datentabelle und anderen Datentabellen an. Unter den verschiedenen Beziehungstypen ist die Systembeziehung 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 vorhanden sein einmal gelesen werden - dies lässt sich oft mit einem Index vermeiden).

Die Datenspalte „possible_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.

Ref-Datenspalte 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 Ihnen eine Vorstellung davon, wie viele Kombinationen die Abfrage verarbeiten muss.

Schließlich bietet die zusätzliche Datenspalte weitere Informationen zur JOIN-Operation. Wenn MySQL beispielsweise beim Ausführen dieser Abfrage eine temporäre Datentabelle erstellen muss, werden Sie in der zusätzlichen Spalte die Wörter „usingtemporary“ sehen.

Das Obige ist der Inhalt der Indexfunktion der MySQL-Serie (5). Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn).