Detaillierte Einführung in den MySql-Index und korrekte Verwendungsmethode

Detaillierte Einführung in den MySql-Index und korrekte Verwendungsmethode

1. Einleitung:

Indizes haben einen entscheidenden Einfluss auf die Abfragegeschwindigkeit und das Verständnis von Indizes ist der Ausgangspunkt für die Optimierung der Datenbankleistung.

Ein Index ist eine Datenstruktur, die von der Speicher-Engine verwendet wird, um Datensätze schnell zu finden. Der sinnvolle Einsatz von Datenbankindizes kann die Zugriffsleistung des Systems erheblich verbessern. Im Folgenden werden hauptsächlich die Indextypen in der MySQL-Datenbank vorgestellt und wie sinnvollere und effizientere Indizierungstechniken erstellt werden können.

Hinweis: Dies zielt hauptsächlich auf die B+Tree-Indexdatenstruktur der InnoDB-Speicher-Engine ab.

2. Vorteile des Index

1. Reduziert die Datenmenge, die der Server scannen muss, erheblich und erhöht dadurch die Geschwindigkeit des Datenabrufs

2. Helfen Sie dem Server, Sortierungen und temporäre Tabellen zu vermeiden

3. Zufällige E/A können in sequentielle E/A umgewandelt werden

3. Indexerstellung

3.1. Primärschlüsselindex

ALTER TABLE 'Tabellenname' ADD PRIMARY KEY 'Indexname' ('Spalte');

3.2 Eindeutiger Index

ALTER TABLE 'Tabellenname' ADD UNIQUE 'Indexname' ('Spalte');

3.3. Gemeinsamer Index

ALTER TABLE 'Tabellenname' ADD INDEX 'Indexname' ('Spalte');

3.4 Volltextindex

ALTER TABLE 'Tabellenname' ADD FULLTEXT 'Indexname' ('Spalte');

3.5. Kombinierter Index

ALTER TABLE 'Tabellenname' ADD INDEX 'Indexname' ('Spalte1', 'Spalte2', ...);

4. B+Tree-Indexregeln

Erstellen einer Testbenutzertabelle

Tabelle löschen, wenn Benutzertest vorhanden ist;
CREATE TABLE Benutzertest(
 ID int AUTO_INCREMENT PRIMARY KEY,
 Benutzername varchar (30) NICHT NULL,
 Geschlechtsbit (1) NICHT NULL STANDARD b'1',
 Stadt varchar (50) NICHT NULL,
 Alter int NICHT NULL
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

Erstellen Sie einen zusammengesetzten Index: ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1. Indexeffektive Abfragen

4.1.1、Vollständiger Werteabgleich

Vollständiger Werteabgleich bezieht sich auf den Abgleich aller Spalten im Index. Nehmen wir beispielsweise den oben erstellten Index als Beispiel. Nach der Where-Bedingung können Sie Daten mit (Benutzername, Stadt, Alter) als Bedingung gleichzeitig abfragen.

Hinweis: Es hat nichts mit der Reihenfolge der Abfragebedingungen nach where zu tun. Dies ist ein Ort, an dem viele Schüler dazu neigen,

SELECT * FROM user_test WHERE user_name = ‚feinik‘ AND age = 26 AND city = ‚Guangzhou‘;

4.1.2. Passen Sie das Präfix ganz links an

Wenn das Präfix ganz links übereinstimmt, wird zuerst die Indexspalte ganz links übereinstimmen. Der oben erstellte Index kann beispielsweise für die Abfragebedingungen verwendet werden: (Benutzername), (Benutzername, Stadt), (Benutzername, Stadt, Alter)

Hinweis: Die Reihenfolge, die die Abfragebedingung für das am weitesten links stehende Präfix erfüllt, hat nichts mit der Reihenfolge der Indexspalten zu tun, z. B.: (Stadt, Benutzername), (Alter, Stadt, Benutzername).

4.1.3. Passende Spaltenpräfixe

Bezieht sich auf den Anfang des übereinstimmenden Spaltenwertes, z. B.: Abfrage aller Benutzer, deren Benutzername mit feinik beginnt

SELECT * FROM Benutzertest WHERE Benutzername LIKE 'feinik%';

4.1.4, Passender Bereichswert

Um beispielsweise alle Benutzer abzufragen, deren Benutzername mit feinik beginnt, wird hier die erste Spalte des Index verwendet.

SELECT * FROM Benutzertest WHERE Benutzername LIKE 'feinik%';

4.2 Indexbeschränkungen

1. Wenn die Where-Abfragebedingung nicht die Indexspalte ganz links in der Indexspalte enthält, kann die Indexabfrage nicht verwendet werden, beispielsweise:

Wählen Sie * aus Benutzertest, wobei Stadt = "Guangzhou" ist.

oder

Wählen Sie * aus Benutzertest, wobei Alter = 26 ist.

oder

SELECT * FROM user_test WHERE Stadt = ,Guangzhou‘ UND Alter = ,26‘;

2. Auch wenn die Abfragebedingung die Indexspalte ganz links ist, kann der Index nicht zum Abfragen von Benutzern verwendet werden, deren Benutzername mit Feinik endet.

SELECT * FROM user_test WHERE Benutzername wie '%feinik';

3. Wenn in der Where-Abfragebedingung eine Bereichsabfrage einer Spalte vorhanden ist, können alle Spalten rechts davon keine Indexoptimierungsabfrage verwenden, z. B.:

SELECT * FROM user_test WHERE user_name = ‚feinik‘ AND city LIKE ‚Guangzhou%‘ AND age = 26;

5. Effiziente Indexierungsstrategie

5.1. Indexspalten können nicht Teil eines Ausdrucks sein oder als Funktionsparameter verwendet werden. Andernfalls können keine Indexabfragen verwendet werden.

SELECT * FROM user_test WHERE Benutzername = concat(Benutzername, 'fei');

5.2 Präfixindex

Manchmal müssen Sie eine sehr lange Zeichenspalte indizieren, was den Indexspeicherplatz vergrößert und die Indexeffizienz verringert. Eine Strategie besteht darin, einen Hash-Index zu verwenden, eine andere darin, einen Präfixindex zu verwenden. Der Präfixindex wählt die ersten n Zeichen der Zeichenspalte als Index aus, was erheblich Indexspeicherplatz sparen und somit die Indexeffizienz verbessern kann.

5.2.1. Selektivität des Präfixindex

Der Präfixindex sollte lang genug sein, um eine hohe Selektivität zu gewährleisten, aber nicht zu lang. Wir können den entsprechenden Wert für die Präfixindex-Auswahllänge folgendermaßen berechnen:

(1)

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column stellt die Spalte dar, zu der der Präfixindex hinzugefügt werden soll

Hinweis: Das Selektivitätsverhältnis des Präfixindex wird auf die oben beschriebene Weise berechnet. Je höher das Verhältnis, desto effizienter ist der Index.

(2)

WÄHLEN

ANZAHL(DISTINCT LEFT(Indexspalte,1))/ANZAHL(*),

ANZAHL(DISTINCT LEFT(Indexspalte,2))/ANZAHL(*),

ANZAHL(DISTINCT LEFT(Indexspalte,3))/ANZAHL(*)

...

VON Tabellenname;

Hinweis: Mithilfe der obigen Anweisungen können wir schrittweise das Selektivitätsverhältnis finden, das dem Präfixindex in (1) am nächsten kommt. Anschließend können wir die entsprechende Zeichenkürzungslänge verwenden, um den Präfixindex zu erstellen.

5.2.2. Erstellung eines Präfixindex

ALTER TABLE Tabellenname ADD INDEX Indexname (Indexspalte (Länge));

5.2.3. Hinweise zur Verwendung des Präfixindex

Ein Präfixindex ist eine effektive Möglichkeit, den Index kleiner und schneller zu machen, aber MySQL kann keinen Präfixindex für ORDER BY und GROUP BY oder für Covering-Scans verwenden.

5.3. Wählen Sie die entsprechende Indexspaltenreihenfolge

Die Reihenfolge der Indexspalten ist beim Erstellen eines zusammengesetzten Indexes sehr wichtig. Die richtige Indexreihenfolge hängt von der Abfragemethode ab, die den Index verwendet. Die Indexreihenfolge zusammengesetzter Indizes kann mit der Faustregel bestimmt werden: Setzen Sie die Spalte mit der höchsten Selektivität an den Anfang des Indexes. Diese Regel entspricht der Selektivitätsmethode von Präfixindizes. Dies bedeutet jedoch nicht, dass die Reihenfolge aller zusammengesetzten Indizes mit dieser Regel bestimmt werden kann. Die spezifische Indexreihenfolge muss auch entsprechend dem spezifischen Abfrageszenario bestimmt werden.

5.4 Clustered-Index und Nonclustered-Index

1. Clustered-Index

Der Clustered-Index bestimmt die physische Reihenfolge der Daten auf der physischen Festplatte. Eine Tabelle kann nur einen Clustered-Index haben. Wenn ein Primärschlüssel definiert ist, clustert InnoDB die Daten nach dem Primärschlüssel. Wenn kein Primärschlüssel definiert ist, wählt InnoDB stattdessen einen eindeutigen, nicht leeren Index aus. Wenn kein eindeutiger, nicht leerer Index vorhanden ist, definiert InnoDB implizit einen Primärschlüssel als Clustered-Index.

Clustered-Indizes können die Zugriffsgeschwindigkeit erheblich verbessern, da Clustered-Indizes Indizes und Zeilendaten im selben B-Baum speichern. Wenn also der Index gefunden wird, werden die entsprechenden Zeilendaten entsprechend gefunden. Bei der Verwendung von Clustered-Indizes sollte jedoch darauf geachtet werden, zufällige Clustered-Indizes zu vermeiden (im Allgemeinen bezieht sich dies auf diskontinuierliche Primärschlüsselwerte und einen ungleichmäßigen Verteilungsbereich). Wenn beispielsweise UUID als Clustered-Index verwendet wird, ist die Leistung sehr schlecht, da die Diskontinuität der UUID-Werte zu einer starken Indexfragmentierung und zufälligen E/A führt, was letztendlich zu einem starken Rückgang der Abfrageleistung führt.

2. Nicht gruppierter Index

Im Gegensatz zum gruppierten Index bestimmt der nicht gruppierte Index nicht die physische Reihenfolge der Daten auf der Festplatte und enthält den Index, aber nicht die Zeilendaten im B-Baum. Auf die Zeilendaten wird nur durch den Zeiger verwiesen, der dem im B-Baum gespeicherten Index entspricht. Beispielsweise ist der oben für (Benutzername, Stadt, Alter) erstellte Index ein nicht gruppierter Index.

5.5 Deckungsindex

Wenn ein Index (z. B. ein zusammengesetzter Index) die Werte aller abzufragenden Felder enthält, wird er als überdeckender Index bezeichnet, z. B.:

SELECT Benutzername, Stadt, Alter FROM Benutzertest WHERE Benutzername = 'feinik' AND Alter > 25;
Da die abzufragenden Felder (Benutzername, Stadt, Alter) alle in den Indexspalten des zusammengesetzten Indexes enthalten sind, wird eine abdeckende Indexabfrage verwendet. Um zu prüfen, ob ein abdeckender Index verwendet wird, können Sie prüfen, ob der Wert von Extra im Ausführungsplan Using index ist, was beweist, dass ein abdeckender Index verwendet wird. Ein abdeckender Index kann die Zugriffsleistung erheblich verbessern.

5.6 So verwenden Sie Indizes zum Sortieren

Wenn Sie beim Sortiervorgang den Index zum Sortieren verwenden können, lässt sich die Sortiergeschwindigkeit erheblich verbessern. Um den Index zum Sortieren zu verwenden, müssen die folgenden beiden Punkte erfüllt sein.

1. Die Reihenfolge der Spalten nach der ORDER BY-Klausel muss mit der Reihenfolge der Spalten im zusammengesetzten Index übereinstimmen, und die Sortierrichtung (aufsteigend / absteigend) aller Sortierspalten muss übereinstimmen

2. Der abgefragte Feldwert muss in der Indexspalte enthalten sein und den abdeckenden Index erfüllen

Analysieren mit Beispielen

Erstellen Sie einen zusammengesetzten Index für die Tabelle „user_test“

ALTER TABLE user_test ADD INDEX index_user(Benutzername, Stadt, Alter);

Beispiele für die Verwendung der Indexsortierung

1. SELECT Benutzername, Stadt, Alter FROM Benutzertest ORDER BY Benutzername;

2. SELECT Benutzername, Stadt, Alter FROM Benutzertest ORDER BY Benutzername, Stadt;

3. SELECT Benutzername, Stadt, Alter FROM Benutzertest ORDER BY Benutzername DESC, Stadt DESC;

4. SELECT Benutzername, Stadt, Alter FROM Benutzertest WHERE Benutzername = 'feinik' ORDER BY Stadt;

Hinweis: Punkt 4 ist etwas speziell. Wenn die Where-Abfragebedingung die erste Spalte der Indexspalte ist und eine konstante Bedingung ist, kann auch der Index verwendet werden.

Fälle, in denen die Indexsortierung nicht verwendet werden kann

1. Das Geschlecht steht nicht in der Indexspalte

SELECT Benutzername, Stadt, Alter FROM Benutzertest ORDER BY Benutzername, Geschlecht;

2. Die Richtung der Sortierspalte ist inkonsistent

SELECT Benutzername, Stadt, Alter FROM Benutzertest ORDER BY Benutzername ASC, Stadt DESC;

3. Die abzufragende Feldspalte Geschlecht ist nicht in der Indexspalte enthalten

SELECT Benutzername, Stadt, Alter, Geschlecht FROM Benutzertest ORDER BY Benutzername;

4. Der Benutzername nach der Where-Abfragebedingung ist eine Bereichsabfrage, sodass andere Spalten des Index nicht verwendet werden können

SELECT Benutzername, Stadt, Alter FROM Benutzertest WHERE Benutzername LIKE 'feinik%' ORDER BY Stadt;

5. Bei der Abfrage mehrerer Tabellen kann die Indexsortierung nur verwendet werden, wenn die Sortierfelder nach ORDER BY alle Indexspalten in der ersten Tabelle sind (die beiden oben genannten Regeln der Indexsortierung müssen erfüllt sein). Erstellen Sie beispielsweise eine weitere Benutzererweiterungstabelle user_test_ext und richten Sie einen Index für die UID ein.

Tabelle löschen, wenn user_test_ext vorhanden ist;

Tabelle erstellen Benutzer_Test_Ext(

  ID int AUTO_INCREMENT PRIMARY KEY,

  uid int NICHT NULL,

  u_password VARCHAR(64) NICHT NULL

)ENGINE=InnoDB STANDARD-CHARSET=utf8;

ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);

Sortieren nach Index

SELECT Benutzername, Stadt, Alter FROM Benutzertest u LEFT JOIN Benutzertest_ext ue ON u.id = ue.uid ORDER BY u.Benutzername;

Keine Indexsortierung

SELECT Benutzername, Stadt, Alter FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6. Zusammenfassung

In diesem Artikel geht es hauptsächlich um die Indizierungsregeln der B+Tree-Baumstruktur, die Erstellung verschiedener Indizes und die korrekte Erstellung effizienter Indizierungstechniken zur Maximierung der Abfragegeschwindigkeit. Natürlich gibt es noch weitere Tipps zur Verwendung von Indizes. Sie müssen relevante Erfahrungen sammeln, um mehr über die Indizierung zu erfahren.

Vielen Dank fürs Lesen, ich hoffe, es kann Ihnen helfen, danke für Ihre Unterstützung dieser Site!