Welche Arten von Indizes gibt es in der MySQL-Datenbank? Gründungsmethoden sowie Vor- und Nachteile

Dieser Artikel konzentriert sich auf die Beschreibung der vier Arten von Masql-Datenbankindizes. Wie erstellt man einen Datenbankindex? Spalten, die in WHERE und JOIN erscheinen, müssen indiziert werden, aber nicht vollständig, da MySQL nur Indizes <, <=, =, >, >=, BETWEEN, IN und manchmal LIKE verwendet. Ich hoffe, dieser Artikel kann allen helfen. Lassen Sie uns zunächst verstehen, was ein Index ist. Um es in einem Satz zusammenzufassen: Der Index ist der Schlüssel zur schnellen Suche.

Die Einrichtung eines MySQL-Index ist für den effizienten Betrieb von MySQL sehr wichtig. Im Folgenden werden einige gängige MySQL-Indextypen vorgestellt

In Datenbanktabellen kann die Indizierung von Feldern die Abfragegeschwindigkeit erheblich verbessern. Angenommen, wir erstellen eine Mytable-Tabelle:

Der Code lautet wie folgt:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

Wir Zufällig Es wurden 10.000 Datensätze eingefügt, darunter einer: 5555, Admin.

Bei der Suche nach dem Datensatz von username="admin" SELECT * FROM mytable WHERE username='admin';, wenn ein Index für username eingerichtet wurde, kann MySQL den Datensatz ohne Scan genau finden. Im Gegenteil, MySQL scannt alle Datensätze, dh es werden 10.000 Datensätze abgefragt.

Indizes werden in einspaltige Indizes und kombinierte Indizes unterteilt. Ein einspaltiger Index bedeutet, dass ein Index nur eine einzige Spalte enthält. Eine Tabelle kann mehrere einspaltige Indizes haben, es handelt sich jedoch nicht um einen kombinierten Index. Kombinierter Index, das heißt, ein Index enthält mehrere Spalten.

Zu den MySQL-Indextypen gehören:

1. Gewöhnlicher Index

Dies ist der einfachste Index, für den es keine Einschränkungen gibt. Es gibt die folgenden Erstellungsmethoden:

1. Erstellen Sie einen Index

Der Code lautet wie folgt:

CREATE INDEX indexName ON mytable(username(length));

Wenn es vom Typ CHAR oder VARCHAR ist, kann die Länge kleiner sein als die tatsächliche Länge des Feldes; wenn es vom Typ BLOB und TEXT ist, muss die Länge angegeben werden, wie unten angegeben.

2. Ändern Sie die Tabellenstruktur

Der Code lautet wie folgt:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- Direkt angeben

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

DROP INDEX [indexName] ON mytable;

2. Eindeutiger Index

Er ähnelt dem vorherigen gewöhnlichen Index, außer dass der Wert der Indexspalte ist muss eindeutig sein, ist aber zulässig. Es gibt einen Nullwert. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Es verfügt über die folgenden Erstellungsmethoden: Der Code lautet wie folgt:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

--Ändern Sie die Tabellenstruktur

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- Geben Sie beim Erstellen der Tabelle direkt

CREATE TABLE mytable an( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username (Länge) );


3. Primärschlüsselindex

Es handelt sich um einen speziellen eindeutigen Index, der keine Nullwerte zulässt. Im Allgemeinen wird der Primärschlüsselindex gleichzeitig mit der Erstellung der Tabelle erstellt: Der Code lautet wie folgt:

CREATE TABLE mytable( ID INT NOT NULL, Benutzername VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ) ;

Natürlich können Sie auch den ALTER-Befehl verwenden. Denken Sie daran: Eine Tabelle kann nur einen Primärschlüssel haben.

4. Kombinierter Index

Um den Einzelspaltenindex und den kombinierten Index anschaulich zu vergleichen, fügen Sie der Tabelle mehrere Felder hinzu: Der Code lautet wie folgt:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

Um weiterzumachen Um die Effizienz von MySQL zu extrahieren, müssen Sie den Aufbau eines zusammengesetzten Index in Betracht ziehen. Bauen Sie einfach Name, Stadt und Alter in einen Index ein:

Der Code lautet wie folgt:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

Bei der Erstellung der Tabelle beträgt die Länge des Benutzernamens 16, hier wird 10 verwendet. Dies liegt daran, dass die Namenslänge im Allgemeinen 10 nicht überschreitet, was die Indexabfrage beschleunigt, die Größe der Indexdatei verringert und die Aktualisierungsgeschwindigkeit von INSERT verbessert.

Wenn Sie jeweils einspaltige Indizes für Benutzername, Stadt und Alter erstellen, sodass die Tabelle über drei einspaltige Indizes verfügt, unterscheidet sich die Abfrageeffizienz stark vom oben genannten kombinierten Index, der weitaus geringer ist als unser kombinierter Index. Obwohl es derzeit drei Indizes gibt, kann MySQL nur den einspaltigen Index verwenden, der seiner Meinung nach am effizientesten ist.

Die Erstellung eines solchen kombinierten Index entspricht tatsächlich der Erstellung der folgenden drei Sätze kombinierter Indizes:

Benutzername, Stadt, Alter Benutzername, Stadt Benutzername Warum gibt es keinen kombinierten Index wie Stadt, Alter? Dies ist auf das „ganz linke Präfix“ des MySQL-Verbundindex zurückzuführen. Das einfache Verständnis besteht darin, die Kombination nur ganz links zu beginnen. Nicht nur Abfragen, die diese drei Spalten enthalten, verwenden diesen kombinierten Index, sondern auch die folgende SQL verwendet diesen kombinierten Index:
[code]
SELECT * FROM mytable WHREE username="admin" AND city ="Zhengzhou" SELECT * FROM mytable WHREE username="admin"

Die folgenden werden nicht verwendet:

Der Code lautet wie folgt:
SELECT * FROM mytable WHREE age=20 AND city="Zhengzhou" SELECT * FROM mytable WHREE city="Zhengzhou"

5. So erstellen Sie einen Index

Bis hierher haben wir gelernt, wie man einen Index erstellt, dann wir Unter welchen Umständen müssen Sie einen Index erstellen? Im Allgemeinen müssen Spalten, die in WHERE und JOIN erscheinen, indiziert werden, aber das ist nicht ganz richtig, da MySQL nur <, <=, =, >, >=, BETWEEN, IN indiziert und manchmal LIKE verwendet Index. Zum Beispiel:

Der Code lautet wie folgt:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city ='Zhengzhou'

Zu diesem Zeitpunkt ist es notwendig, Stadt und Alter zu indizieren. Da der Benutzername der Mytable-Tabelle auch in der JOIN-Klausel erscheint, ist es auch notwendig, ihn zu indizieren.

Ich habe gerade erwähnt, dass LIKE nur zu bestimmten Zeiten indiziert werden muss. Weil MySQL den Index nicht verwendet, wenn Abfragen mit den Platzhalterzeichen % und _ beginnen. Beispielsweise verwendet der folgende Satz den Index:

Der Code lautet wie folgt:
SELECT * FROM mytable WHERE username like'admin%'
Der nächste Satz verwendet nicht den Index:

Der Code lautet wie folgt:

SELECT * FROM mytable WHEREt Name like'%admin'

Daher sollten Sie bei der Verwendung von LIKE auf die oben genannten Unterschiede achten.

6. Nachteile von Indizes

Das oben Genannte spricht von den Vorteilen der Verwendung von Indizes, aber eine übermäßige Verwendung von Indizes führt zu Missbrauch. Daher hat der Index auch seine Nachteile:

1 Obwohl der Index die Abfragegeschwindigkeit erheblich verbessert, verringert er auch die Geschwindigkeit der Aktualisierung der Tabelle, z. B. INSERT, UPDATE und DELETE in der Tabelle. Denn beim Aktualisieren der Tabelle muss MySQL nicht nur die Daten, sondern auch die Indexdatei speichern.

2. Das Erstellen von Indexdateien belegt Speicherplatz. Im Allgemeinen ist dieses Problem nicht schwerwiegend. Wenn Sie jedoch mehrere kombinierte Indizes für eine große Tabelle erstellen, wird die Indexdatei schnell erweitert.

Indizes sind nur ein Faktor zur Verbesserung der Effizienz. Wenn Ihr MySQL über eine große Menge an Datentabellen verfügt, müssen Sie Zeit damit verbringen, die besten Indizes zu recherchieren und zu erstellen oder Abfrageanweisungen zu optimieren.

7. Dinge, die Sie bei der Verwendung von Indizes beachten sollten:

Bei der Verwendung von Indizes gibt es die folgenden Tipps und Vorsichtsmaßnahmen:

1 nicht enthalten Spalten mit NULL-Werten

werden nicht in den Index aufgenommen, solange die Spalte NULL-Werte enthält. Solange eine Spalte im zusammengesetzten Index einen NULL-Wert enthält, ist diese Spalte ungültig für den zusammengesetzten Index. Daher sollten wir beim Entwerfen der Datenbank nicht zulassen, dass der Standardwert des Felds NULL ist.

2. Verwenden Sie den Kurzindex

, um die Zeichenfolge zu indizieren, und wenn möglich sollte eine Präfixlänge angegeben werden. Wenn Sie beispielsweise eine CHAR(255)-Spalte haben und die meisten Werte innerhalb der ersten 10 oder 20 Zeichen eindeutig sind, indizieren Sie nicht die gesamte Spalte. Kurze Indizes verbessern nicht nur die Abfragegeschwindigkeit, sondern sparen auch Speicherplatz und E/A-Vorgänge.

3. Indexspaltensortierung

MySQL-Abfrage verwendet nur einen Index. Wenn der Index also in der where-Klausel verwendet wurde, verwenden die Spalten in der Reihenfolge nach nicht den Index. Verwenden Sie daher keine Sortiervorgänge, wenn die Standardsortierung der Datenbank die Anforderungen erfüllen kann. Versuchen Sie, die Sortierung mehrerer Spalten zu vermeiden. Bei Bedarf ist es am besten, zusammengesetzte Indizes für diese Spalten zu erstellen.

4. Like-Anweisungsoperation

Im Allgemeinen wird die Verwendung einer Like-Operation nicht empfohlen. Wenn sie verwendet werden muss, ist auch die Verwendung problematisch. „%aaa%“ verwendet nicht den Index, aber „aaa%“ verwendet den Index.

5. Bearbeiten Sie keine Spalten.

Der Code lautet wie folgt:
Wählen Sie * aus den Benutzern aus, in denen sich YEAR(adddate)<2007;

befindet Jede Zeile führt dazu, dass der Index fehlschlägt und ein vollständiger Tabellenscan durchgeführt wird, sodass wir ihn wie folgt ändern können:

Der Code lautet wie folgt:

Wählen Sie * aus Benutzern aus, bei denen das Datum adddate<'2007 ist -01-01' ;

6. Verwenden Sie keine NOT IN- und <>-Operationen.

Oben werden die MySQL-Indextypen vorgestellt. Ich hoffe, es hilft allen.

Zwei Methoden der Indizierung:

B-Tree-Index

Hinweis: Es wird als Btree-Index bezeichnet, der ein ist Großer Aspekt Schauen Sie, sie verwenden alle ausgewogene Bäume, aber in Bezug auf die spezifische Implementierung unterscheidet sich jede Speicher-Engine geringfügig. Genau genommen verwendet die NDB-Engine beispielsweise T-Tree
Myisam und in Innodb den B-Tree-Index wird standardmäßig verwendet. Die theoretische Abfragezeitkomplexität des B-Baums beträgt O(log2 (N-1)), N ist die Anzahl der Zeilen in der Datentabelle

Hash-Index
Für Tabellen mit Speicher-Speicher-Engine, der Standardwert ist Hash. Die theoretische Abfragezeitkomplexität von Index und Hash beträgt O(1)

Verwandte Empfehlungen:

Detaillierte Erläuterung der Verwendung der MySQL-Indizierung und -Analyse von Vor- und Nachteilen

Vor- und Nachteile der Indexierung von Seite 1/2

Das obige ist der detaillierte Inhalt vonWelche Arten von Indizes gibt es in der MySQL-Datenbank? Gründungsmethoden sowie Vor- und Nachteile. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!