Detaillierte Erklärung des MySQL-Datenbankindex

Heute werde ich Ihnen weiterhin MySQL-bezogenes Wissen vorstellen. Der Hauptinhalt dieses Artikels sind MySQL-Index-bezogene Inhalte.

1. Einführung in den MySQL-Index

Ein Index ist ein „Verzeichnis“, das von der MySQL-Datenbank zu einer oder mehreren Spalten einer Tabelle hinzugefügt wird, um die Datenabfrage zu beschleunigen. Der MySQL-Index ist eine spezielle Datei, aber der Index der Tabelle der InnoDB-Typ-Engine (wir werden die MySQL-Engine in zukünftigen Artikeln erläutern) ist ein integraler Bestandteil des Tabellenbereichs.
Die MySQL-Datenbank unterstützt insgesamt 5 Indextypen, nämlich Normalindex, eindeutiger Index, Primärschlüsselindex, zusammengesetzter Index und Volltextindex. Im Folgenden werde ich diese vier Indextypen nacheinander vorstellen.

2. Detaillierte Erläuterung der fünf Arten von MySQL-Indizes

1. Allgemeiner Index

Ein gemeinsamer Index ist ein gemeinsamer Index in der MySQL-Datenbank. Es gibt keine besonderen Anforderungen an die Daten in der Spalte, zu der ein gemeinsamer Index hinzugefügt wird. Die Rolle eines gemeinsamen Index besteht darin, differenzielle Abfragen zu beschleunigen.
Nachfolgend sehen Sie ein Beispiel für das Hinzufügen einer allgemeinen Index-SQL-Anweisung beim Erstellen einer Datentabelle:

Tabelle exp erstellen(id int, name varchar(20),index exp_name(name));

Oder ersetzen Sie den Index durch den Schlüssel, wie folgt:

Erstelle Tabelle exp (ID, int, Name varahcr (20), Schlüssel exp_name(Name));

Im obigen SQL-Befehl bedeutet Schlüssel oder Index das Hinzufügen eines Indexes, gefolgt vom Indexnamen, und die Spalten in Klammern sind die Spalten, denen der Index hinzugefügt werden soll.
In allen in diesem Artikel vorgestellten indexbezogenen SQL-Anweisungen kann der Index durch den Schlüssel ersetzt werden, sofern keine besondere Erklärung vorliegt. Um die Länge des Artikels zu sparen, wird dieser Punkt in Zukunft nicht wiederholt.
Darüber hinaus können wir auch einen Index hinzufügen, ohne den Namen des Index anzugeben. In diesem Fall fügt MySQL dem Index automatisch einen Indexnamen mit demselben Namen wie das Feld hinzu.
Die Ausführungsergebnisse sind wie folgt:

Nach dem Erstellen einer Datentabelle sieht das Hinzufügen einer neuen allgemeinen Index-SQL-Anweisung zur Tabelle wie folgt aus:

Tabelle-Exp ändern, Index exp_id(id) hinzufügen;

Die Ausführungsergebnisse sind wie folgt:

Nachfolgend sehen Sie ein Beispiel für eine SQL-Anweisung zum Löschen eines gemeinsamen Indexes nach dem Erstellen einer Datentabelle:

Tabelle ändern, Index exp_name löschen;

Die Ausführungsergebnisse sind wie folgt:

Beachten Sie, dass im obigen Befehl exp_name der Name des Indexes ist und nicht der Name des Feldes, das den Index enthält. Wenn wir den Namen des Indexes in der Tabelle vergessen, können wir den folgenden SQL-Befehl zur Abfrage ausführen:

Index vom Exp anzeigen;

Unter diesen ist exp der Tabellenname und die Ergebnisse der Befehlsausführung sind wie folgt:

Wie aus den obigen Bildern ersichtlich ist, finden Sie nach dem Hinzufügen eines normalen Indexes beim Verwenden von desc zum Anzeigen der Tabellenstruktur MUL in der Spalte „Schlüssel“, was bedeutet, dass der Spalte ein normaler Index hinzugefügt wurde.

(II) Eindeutiger Index

Ein eindeutiger Index ist ein gemeinsamer Index, der erfordert, dass alle Werte in der Spalte, zu der der Index hinzugefügt wird, nur einmal vorkommen dürfen. Eindeutige Indizes werden normalerweise zu Feldern wie Ausweisnummer und Matrikelnummer hinzugefügt, können jedoch nicht zu Feldern wie Name hinzugefügt werden.
Das Hinzufügen eines eindeutigen Indexes ist fast dasselbe wie das Hinzufügen eines normalen Indexes, außer dass die Schlüsselwörter Schlüssel und Index des normalen Indexes durch eindeutigen Schlüssel und eindeutigen Index ersetzt werden.
Nachfolgend sehen Sie ein Beispiel für eine SQL-Anweisung zum Hinzufügen eines eindeutigen Indexes beim Erstellen einer Datentabelle:

Erstellen Sie die Tabelle exp (ID int, Name varchar (20), eindeutiger Schlüssel (Name));

Die Ausführungsergebnisse des obigen Befehls sind wie folgt:

Es ist ersichtlich, dass beim Abfragen der Tabellenstruktur eines Felds mit hinzugefügtem eindeutigen Index mit dem Befehl desc in der Spalte „Schlüssel“ UNI angezeigt wird, was darauf hinweist, dass dem Feld ein eindeutiger Index hinzugefügt wurde.
Ein Beispiel für eine SQL-Anweisung zum Hinzufügen eines eindeutigen Indexes nach dem Erstellen einer Datentabelle lautet wie folgt:

Nachfolgend sehen Sie ein Beispiel für eine SQL-Anweisung zum Löschen eines eindeutigen Indexes:

Tabellenausdruck ändern, Indexnamen löschen;

Die Ausführungsergebnisse sind wie folgt:

(III) Primärschlüsselindex

Der Primärschlüsselindex hat die schnellste Abfragegeschwindigkeit unter allen Indizes in der Datenbank und jede Datentabelle kann nur eine Primärschlüsselindexspalte haben. Die Spalten des Primärschlüsselindex dürfen keine doppelten Daten oder Nullwerte enthalten.
Das Hinzufügen und Löschen von Primärschlüsselindizes ähnelt stark den normalen und eindeutigen Indizes, mit der Ausnahme, dass der Schlüssel durch den Primärschlüssel ersetzt wird. Die relevanten SQL-Befehle lauten wie folgt:

Tabelle exp erstellen(id int, Name varchar(20), Primärschlüssel (id)); Tabelle exp ändern, Primärschlüssel (id) hinzufügen;

Wenn Sie einer Spalte einen Primärschlüsselindex hinzufügen, wird PRI in der Spalte „Schlüssel“ angezeigt, wenn Sie die Tabellenstruktur in der Beschreibung anzeigen, wie unten dargestellt:

Um den Primärschlüsselindex zu löschen, können Sie den folgenden Befehl ausführen:

Tabellenausdruck ändern, Primärschlüssel löschen;

Beachten Sie, dass in dieser SQL-Anweisung der Schlüssel nicht durch den Index ersetzt werden kann.
Manchmal lehnt MySQL den Versuch ab, den Primärschlüsselindex zu löschen. Dies kann daran liegen, dass dem Feld das Attribut auto_increment hinzugefügt wurde. Wir können den Primärschlüsselindex des Felds löschen, indem wir den Feldmodifikator entfernen, wie unten gezeigt:

(IV) Zusammengesetzter Index

Wenn wir einen Index erstellen möchten, der verschiedene Spalten enthält, können wir einen zusammengesetzten Index erstellen. Tatsächlich werden zusammengesetzte Indizes häufig in Geschäftsszenarien verwendet. Wenn wir beispielsweise den Inhalt eines Datenpakets aufzeichnen möchten, müssen wir die IP- und Portnummer als Grundlage zur Identifizierung des Datenpakets verwenden. In diesem Fall können wir einen zusammengesetzten Index für die IP-Adressspalte und die Portnummernspalte erstellen. Nachfolgend finden Sie Beispiele für SQL-Anweisungen zum Erstellen zusammengesetzter, hinzufügender und löschender Indizes:

Tabelle exp erstellen (ip varchar(15),port int ,Primärschlüssel (ip,port));
alter table exp, primären Schlüssel hinzufügen (IP, Port);
alter table exp dorp primamary key;

Beachten Sie, dass ein zusammengesetzter Index einem mehrspaltigen Primärschlüsselindex entspricht. Daher darf keine Spalte, der der zusammengesetzte Index hinzugefügt wird, leere Daten enthalten und die Daten in diesen Spalten dürfen nicht genau gleich sein, da die MySQL-Datenbank sonst einen Fehler meldet. Wie unten dargestellt:

(V) Volltextindex

Die Volltextindizierung wird hauptsächlich verwendet, um das Problem der unscharfen Übereinstimmung in großen Datenmengen zu lösen. Wenn die Datenmenge in einem Feld in der Datenbank sehr groß ist, wird die Geschwindigkeit sehr langsam, wenn wir die Like+Wildcard-Methode zur Suche verwenden möchten. In diesem Fall können wir die Volltextindizierung verwenden, um Fuzzy-Abfragen zu beschleunigen. Das Prinzip der Volltextindizierung besteht darin, die Schlüsselwörter und ihre Häufigkeit im Text mithilfe der Wortsegmentierungstechnologie zu analysieren und dann nacheinander Indizes zu erstellen. Die Verwendung von Volltextindizes hängt eng mit der Datenbankversion, der Datentabellen-Engine und sogar dem Feldtyp zusammen. Die wichtigsten Einschränkungen sind wie folgt:
1. Volltextindizierung wird erst ab MySQL Version 3.2 unterstützt.
2. MySQL Version 5.7 und höher hatte ein integriertes Ngram-Plug-In und die Volltextindizierung begann, Chinesisch zu unterstützen.
3. In Versionen vor MySQL 5.6 unterstützt nur die MyISAM-Engine die Volltextindizierung.
4. In Versionen nach MySQL 5.6 unterstützen sowohl die MyISAM-Engine als auch die InnoDB-Engine die Volltextindizierung.
5. Nur Felder mit den Datentypen Char, Varchar und Text unterstützen das Hinzufügen von Volltextindizes.
Die SQL-Befehle zum Erstellen, Hinzufügen und Löschen von Volltextindizes lauten wie folgt:

Tabelle erstellen exp (ID int, Inhaltstext, Fülltextschlüssel (Inhalt)) Engine = MyISAM;
Tabellenausdruck ändern, Volltextindex (Inhalt) hinzufügen;
Tabellenausdruck ändern, Indexinhalt löschen;

Einige der Ausführungsergebnisse sind wie folgt:

Nachdem Sie einen Volltextindex erstellt haben, können Sie die Methode „like+wildcard“ nicht mehr für die Fuzzy-Suche verwenden. Die Verwendung eines Volltextindex hat eine eigene, spezifische Syntax, wie unten gezeigt:

Wählen Sie * aus dem Exp, wobei Übereinstimmung (Inhalt) mit ('a') übereinstimmt.

Die Felder in den Klammern nach „match“ enthalten den Volltextindex und die Felder in den Klammern nach „away“ enthalten den Inhalt, für den ein Fuzzy-Match durchgeführt werden soll.
Darüber hinaus ist die Rolle des Volltextindex nicht eindeutig. In vielen Szenarien verwenden wir nicht den integrierten Volltextindex der MySQL-Datenbank, sondern verwenden ähnliche Indizes von Drittanbietern, um dieselbe Funktion zu erreichen.

3. Grundsätze der MySQL-Indexverwendung

1. Die Indizierung ist eine typische Strategie, bei der man „Platz gegen Zeit tauscht“. Sie verbraucht zwar Speicherplatz, beschleunigt aber Abfragen.
2. Obwohl das Hinzufügen von Indizes die Abfragegeschwindigkeit erhöht, verlangsamt es die Einfüge- und Löschgeschwindigkeit. Denn beim Einfügen und Löschen von Daten sind zusätzliche Indexoperationen notwendig.
3. Je mehr Indizes, desto besser. Wenn die Datenmenge nicht groß ist, müssen keine Indizes hinzugefügt werden.
4. Wenn die Werte einer Tabelle häufig eingefügt und geändert werden müssen, ist es nicht geeignet, einen Index zu erstellen. Im Gegenteil, wenn die Werte eines Felds in einer Tabelle häufig abgefragt, sortiert und gruppiert werden müssen, muss ein Index erstellt werden.
5. Wenn ein Feld die Bedingungen zum Erstellen eines eindeutigen Indexes erfüllt, erstellen Sie keinen normalen Index.

Zusammenfassen

Dies ist das Ende dieses Artikels mit der detaillierten Erklärung des MySQL-Datenbankindex. Weitere relevante MySQL-Indexinhalte finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!