Detailed explanation of the types and advantages and disadvantages of Mysql indexes

IndexDies ist eine spezielle Datei (Indizes von InnoDB-Datenbanktabellen sind ein Bestandteil des Speicherbereichs), die Referenzpointer auf alle Zeilen in der Tabelle enthält.
Hinweis:
[1]Indizes sind nicht alles! Indizes können die Datenabfrage beschleunigen, aber die Datenänderungen verlangsamen. Bei jeder Änderung eines Datenzeilen muss der Index aktualisiert werden. Um diesen Mangel etwas auszugleichen, hat viele SQL-Befehle ein DELAY_KEY_WRITE-Element. Diese Option unterbricht temporär MySQL, den Index sofort nach jedem neuen Eintrag und jeder Änderung eines bestehenden Eintrags zu aktualisieren, und die Aktualisierung des Indexes wird bis zum Ende der Einfügung aller Einträge aufgeschoben./Nachdem die Änderungen abgeschlossen sind, sollte fortgefahren werden. Bei der Notwendigkeit, viele neue Zeilen in ein Datenbanktable zuinsertieren, ist die Option DELAY_KEY_WRITE sehr nützlich.
[2Außerdem nehmen Indizes erheblichen Speicherplatz auf der Festplatte in Anspruch. Daher sollte nur für die am häufigsten abgefragten und sortierten Spalten Indizes erstellt werden. Beachten Sie, dass die Erstellung eines Indexes für eine Spalte mit vielen wiederholten Inhalten nicht sehr wirksam ist.
Von theoretischer Seite aus könnte für jeden Feld in einer Tabelle ein eigener Index erstellt werden, aber MySQL begrenzt die Anzahl der Indizes in einer Tabelle auf16Indizes können theoretisch für jeden Feld in einer Tabelle erstellt werden, aber MySQL begrenzt die Anzahl der Indizes in einer Tabelle auf

1. Indizes von InnoDB-Datenbanktabellen

Im Vergleich zu MyISAM-Tabellen ist der Index für InnoDB-Tabellen viel wichtiger. Auf InnoDB-Tabellen hat der Index nicht nur eine Rolle bei der Suche nach Datenzeilen, sondern ist auch die Grundlage des Mechanismus der Zeilenstufe der Datenbanktabellen. 'Zeilenstufe der Datenbanktabellen' bedeutet, dass während der Ausführung von Transaktionen individuelle Zeilen, die bearbeitet werden, gesperrt werden, um den Zugriff anderer Benutzer zu verhindern. Diese Sperre betrifft (aber ist nicht beschränkt auf) die Befehle SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE sowie INSERT, UPDATE und DELETE.
Aus Effizienzgründen tritt die Zeilenstufe der Datenbanktabellen von InnoDB in den Indizes auf, nicht im Tabellen selbst auf. Offensichtlich kann das Mechanismus der Zeilenstufe der Datenbanktabellen nur wirksam sein, wenn für die betreffenden Tabellen ein geeigneter Index für das Sperren vorhanden ist.

2. Begrenzung

Wenn die Abfragebedingung des WHERE-Clauses ein Ungleichheitszeichen (WHERE column != ...) enthält, kann MySQL keine Indizes verwenden.
Ähnlich kann MySQL keine Indexes verwenden, wenn im WHERE-Ausdruck der Suchbedingungen Funktionen verwendet werden (WHERE DAY(column) = …).
MySQL kann Indexes nur dann verwenden, wenn die Datentypen der Primärschlüssel und der Fremdschlüssel in der JOIN-Operation identisch sind.
MySQL kann nur Indexes verwenden, wenn der Vergleichsoperator LIKE und REGEXP im WHERE-Ausdruck verwendet werden und der erste Character des Suchmusters kein Platzhalter ist. Zum Beispiel wird MySQL einen Index verwenden, wenn der Suchausdruck LIKE ‘abc%' ist; wenn der Suchausdruck LIKE ‘%abc' ist, wird MySQL keinen Index verwenden.
MySQL verwendet in der ORDER BY-Operation nur Indexes, wenn die Sortierungsbedingung nicht ein Suchkriterium ausdrückt. (Trotzdem haben Indexes in Abfragen, die mehrere Datenbanktabellen betreffen, selbst wenn Indexes verfügbar sind, keine nennenswerte Beschleunigung in Bezug auf ORDER BY.)
Wenn eine Daten列 viele Duplikate enthält, hat selbst der Erstellung eines Indexes nicht unbedingt eine gute Wirkung. Zum Beispiel, wenn eine Daten列 nur Werte wie „0“ enthält/1″oder”Y/N”wenn es keine Notwendigkeit gibt, einen Index für es zu erstellen.　

Regulärer Index, eindeutiger Index und Primärschlüsselindex

1. Regulärer Index

Der einzige Zweck eines regulären Indexes (definiert durch das Schlüsselwort KEY oder INDEX) ist die Beschleunigung des Zugriffs auf die Daten. Daher sollte ein Index nur für die Daten列 erstellt werden, die häufig in den Suchkriterien (WHERE column = …) oder Sortierungskriterien (ORDER BY column) vorkommen. Wenn möglich, sollte eine Daten列 mit den besten, am schärfsten zusammengefassten Daten (wie eine Daten列 vom Typ Integer) zum Erstellen eines Indexes ausgewählt werden.

2. Eindeutiger Index
Ein regulärer Index ermöglicht es, dass die zu indexierenden Daten列 Duplikate enthalten können. Zum Beispiel könnte eine Person denselben Namen haben, daher könnte derselbe Name in der gleichen Tabelle „Mitarbeiterpersönlichkeitsdaten“ zwei oder mehr Mal vorkommen.
Wenn eine bestimmte Daten列 festgelegt ist, dass sie nur einzigartige Werte enthalten wird, sollte sie beim Erstellen eines Index für diese Daten列 mit dem Schlüsselwort UNIQUE als einen eindeutigen Index definiert werden. Der Vorteil dieses Verfahrens: Erstens wird die Verwaltung dieses Index durch MySQL vereinfacht, und der Index wird daher effizienter; Zweitens überprüft MySQL beim Einfügen neuer Datensätze in die Datenbank, ob der Wert dieses Felds bereits in einem anderen Datensatz dieses Felds vorkommt; wenn ja, lehnt MySQL den Eintrag dieses neuen Datensatzes ab. Das bedeutet, dass der eindeutige Index die Einzigartigkeit der Daten aufrechterhält. Tatsächlich ist der Zweck der Erstellung eines eindeutigen Indexes in vielen Fällen nicht der, die Zugriffsgeschwindigkeit zu verbessern, sondern um die Wiederholung von Daten zu vermeiden.

3. Primärschlüsselindex

Es wurde bereits mehrmals betont: Ein Index muss für das Primärschlüsselfeld erstellt werden, der als "Primärschlüsselindex" bezeichnet wird. Der einzige Unterschied zwischen dem Primärschlüsselindex und dem einzigartigen Index ist: Der Primärschlüsselindex verwendet das Schlüsselwort PRIMARY anstatt UNIQUE.

4. Fremdschlüsselindex

Wenn eine Fremdschlüsselbedingung für ein Fremdschlüsselfeld definiert wird, definiert MySQL einen internen Index, um sich effizient um die Verwaltung und Nutzung der Fremdschlüsselbedingung zu kümmern.

5. Komplexer Index

Ein Index kann mehrere Datenfelder abdecken, wie z.B. der INDEX(columnA, columnB) Index. Der Vorteil dieses Indexes ist, dass MySQL einen solchen Index auswählen kann. Wenn eine Abfrageoperation nur einen Index des Datenfelds columnA benötigt, kann der komplexe Index INDEX(columnA, columnB) verwendet werden. Allerdings ist diese Verwendung nur für die Kombinationen von Datenfeldern im Index applicable, die an erster Stelle stehen. Zum Beispiel kann INDEX(A, B, C) als Index für A oder (A, B) verwendet werden, aber nicht als Index für B, C oder (B, C).

6. Die Länge des Indexes

Bei der Definition von Indizes für Datenfelder vom Typ CHAR und VARCHAR kann die Länge des Indexes auf eine bestimmte Anzahl von Zeichen begrenzt werden (dieses Zahl muss kleiner als die maximale Anzahl von Zeichen sein, die dieses Feld zulässt). Der Vorteil dabei ist, dass ein kleinerer, schnellerer Indexdatei erstellt werden kann. In den meisten Anwendungen sind die Daten im Datenbankstring hauptsächlich in verschiedenen Namen vorhanden, daher kann die Länge des Indexes auf10~15Zeichen, ist ausreichend, um den Suchbereich auf eine geringe Anzahl von Datensätzen zu beschränken.
Bei der Erstellung von Indizes für Datenfelder vom Typ BLOB und TEXT muss die Länge des Indexes begrenzt werden; der maximale Indexlänge, die MySQL erlaubt, beträgt255Zeichen.
Volltextindex

　　 Der gewöhnliche Index auf dem Textfeld kann nur die Suche nach dem ersten Zeichen im Feldinhalt beschleunigen (also dem ersten Zeichen des Feldinhalts). Wenn im Feld größere Abschnitte von Text, bestehend aus mehreren, manchmal sogar mehreren Wörtern, gespeichert sind, ist der gewöhnliche Index fast nutzlos. Diese Suche tritt oft in der Form LIKE %word% auf, was für MySQL komplex ist und eine lange Antwortzeit verursachen kann, wenn eine große Menge an Daten verarbeitet werden muss.
Diese Situationen sind genau die, in denen Fulltext-Indizes (full-text index) kann in solchen Fällen großartig eingesetzt werden. Bei der Erstellung solcher Indizes erstellt MySQL eine Liste aller im Text出现的单词，und die Abfrageoperationen suchen die entsprechenden Datensätze basierend auf dieser Liste. Fulltext-Indizes können sowohl mit der Tabelle erstellt werden als auch später verwendet werden, wenn dies erforderlich ist.

Dieser Befehl fügt hinzu:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

Mit dem Fulltext-Index kann der SELECT-Befehl verwendet werden, um Datensätze abzurufen, die einen oder mehrere angegebene Wörter enthalten. Hier ist die grundlegende Syntax für solche Abfragebefehle:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

Dieser Befehl wird column1und column2enthalten1、word2und word3alle Datensätze abgefragt werden.

Anmerkung：InnoDB-Tabellen unterstützen keine Fulltext-Indizes.

Optimierung von Abfragen und Indizes

　　　　　 Nur wenn ausreichend viele Testdaten in der Datenbank vorhanden sind, haben die Ergebnisse der Leistungstests tatsächliche Referenzwerte. Wenn es in der Testdatenbank nur einige hundert Datensatzeinträge gibt, werden sie oft nach der Ausführung des ersten Abfragebefehls vollständig in den Speicher geladen, was die Ausführung der nachfolgenden Abfragebefehle sehr schnell macht – egal ob ein Index verwendet wird oder nicht. Nur wenn die Datensätze in der Datenbank1000 Zeilen und der Gesamtbetrag der Daten auch die Gesamtkapazität des Speichers des MySQL-Servers übersteigt, haben die Ergebnisse der Leistungstests der Datenbank Bedeutung.

　　　　　 Wenn unklar ist, in welchen Datenfeldern ein Index erstellt werden sollte, kann man oft von dem EXPLAIN SELECT-Befehl profitieren. Dies ist nur eine einfache Angabe eines EXPLAIN-Schlüsselworts vor einem normalen SELECT-Befehl. Mit diesem Schlüsselwort führt MySQL nicht den SELECT-Befehl aus, sondern analysiert ihn. MySQL listet den Ablauf der Abfrage und verwendete Indizes (falls vorhanden) in tabellarischer Form auf.
In den Ausgabeergebnissen des EXPLAIN-Befehls, der1Die Spalte ist der Name der Tabelle, die aus der Datenbank gelesen wird, und sie sind in der Reihenfolge ihres Lesens angeordnet. Die Spalte type spezifiziert die Beziehung (JOIN) zwischen dieser Tabelle und anderen Tabellen. Unter verschiedenen Arten von Beziehungen ist system am effizientesten, gefolgt von const, eq_ref, ref, range, index und All (All bedeutet: Für jedes Eintrag in der übergeordneten Tabelle müssen alle Einträge in dieser Tabelle einmal gelesen werden – dies kann oft durch einen Index vermieden werden).

Der possible_keys-Datensatz gibt die verschiedenen Indizes an, die MySQL bei der Suche nach Datenrecorden verwenden kann. Der key-Datensatz ist der tatsächlich von MySQL verwendete Index, dessen Länge in Bytes im key_len-Datensatz angegeben ist. Zum Beispiel beträgt die Byte-Länge eines INDEX für ein INTEGER-Datenfeld4Wenn ein komplexer Index verwendet wird, können Sie im key_len-Datensatz sehen, welche Teile von MySQL konkret verwendet werden. Als allgemeine Regel ist der Wert im key_len-Datensatz kleiner, je besser (das bedeutet schneller).
Der ref-Datensatz gibt den Namen der Datenbanktabelle an, die in der Beziehung verwendet wird. Der row-Datensatz ist die Anzahl der Datenzeilen, die MySQL beim Ausführen der Abfrage voraussichtlich aus dieser Datenbanktabelle lesen wird. Das Produkt aller Zahlen im row-Datensatz gibt uns eine grobe Vorstellung davon, wie viele Kombinationen diese Abfrage verarbeiten muss.
Der extra-Datensatz bietet zusätzliche Informationen zum JOIN-Operation, z.B. wenn MySQL eine temporäre Datenbanktabelle erstellen muss, wird im extra-Datensatz der Text 'using temporary' angezeigt.

Das ist der gesamte Inhalt dieses Artikels. Wir hoffen, dass er Ihnen bei Ihrem Lernen hilft und wir hoffen, dass Sie die Anstrengungen von呐喊教程 mehr unterstützen.

Erklärung: Der Inhalt dieses Artikels wurde aus dem Internet entnommen und gehört dem Urheberrecht des Urhebers. Der Inhalt wurde von Internetbenutzern selbstständig beigesteuert und hochgeladen. Diese Website besitzt keine Eigentumsrechte und hat den Inhalt nicht manuell bearbeitet. Sie übernimmt auch keine rechtlichen Verantwortlichkeiten. Wenn Sie verdächtige Inhalte entdecken, senden Sie bitte eine E-Mail an: notice#w und fügen Sie relevante Beweise bei. Sobald nachgewiesen wird, dass der Inhalt urheberrechtlich geschützt ist, wird dieser sofort gelöscht.3Erklärung: Der Inhalt dieses Artikels wurde aus dem Internet entnommen und gehört dem Urheberrecht des Urhebers. Der Inhalt wurde von Internetbenutzern selbstständig beigesteuert und hochgeladen. Diese Website besitzt keine Eigentumsrechte und hat den Inhalt nicht manuell bearbeitet. Sie übernimmt auch keine rechtlichen Verantwortlichkeiten. Wenn Sie verdächtige Inhalte entdecken, senden Sie bitte eine E-Mail an: notice#w und fügen Sie relevante Beweise bei. Sobald nachgewiesen wird, dass der Inhalt urheberrechtlich geschützt ist, wird dieser sofort gelöscht.