Die richtige Verwendung von MySQL-Indizes und detaillierte Erklärung der Indexprinzipien

1. Einleitung

Warum brauchen wir Indizes?

In allgemeinen Anwendungssystemen beträgt das Lese-/Schreibverhältnis etwa 10:1, und bei Einfügevorgängen und allgemeinen Aktualisierungsvorgängen treten selten Leistungsprobleme auf. In einer Produktionsumgebung sind die häufigsten und problematischsten Vorgänge, denen wir begegnen, immer noch einige komplexe Abfragevorgänge, sodass die Optimierung von Abfrageanweisungen offensichtlich oberste Priorität hat. Wenn es um die Beschleunigung von Abfragen geht, müssen wir Indizes erwähnen.

Was ist ein Index?

Ein Index, in MySQL auch „Schlüssel“ genannt, ist eine Datenstruktur, die von der Speicher-Engine zum schnellen Auffinden von Datensätzen verwendet wird. Indizes sind wichtig für eine gute Performance

Insbesondere wenn die Datenmenge in der Tabelle zunimmt, wird der Einfluss des Index auf die Leistung immer wichtiger.

Die Indexoptimierung sollte das effektivste Mittel zur Optimierung der Abfrageleistung sein. Indizes können die Abfrageleistung problemlos um mehrere Größenordnungen verbessern.

Der Index entspricht der Phonetiktabelle im Wörterbuch. Wenn Sie ein bestimmtes Wort nachschlagen möchten und die Phonetiktabelle nicht verwenden, müssen Sie Hunderte von Seiten einzeln durchsuchen.

 30

 10 40

 5 15 35 66

1 6 11 19 21 39 55 100

Gibt es bei Ihnen Missverständnisse bezüglich der Indizierung?

Die Indizierung ist ein wichtiger Aspekt bei der Anwendungsgestaltung und -entwicklung. Wenn zu viele Indizes vorhanden sind, kann die Leistung Ihrer Anwendung beeinträchtigt werden. Wenn jedoch zu wenige Indizes vorhanden sind, wird die Abfrageleistung beeinträchtigt. Für die Leistung der Anwendung ist es entscheidend, ein Gleichgewicht zu finden. Manche Entwickler denken immer, das Hinzufügen von Indizes sei zweitrangig – ich war immer der Meinung, dass dies auf einem falschen Entwicklungsmodell beruht. Wenn Sie wissen, wie Ihre Daten verwendet werden, sollten Sie von Anfang an bei Bedarf Indizes hinzufügen. Entwickler verwenden die Datenbank häufig auf Anwendungsebene, beispielsweise zum Schreiben von SQL-Anweisungen, gespeicherten Prozeduren usw. Möglicherweise wissen sie nicht einmal, dass Indizes vorhanden sind, oder glauben, dass sie den entsprechenden DBA einfach bitten können, diese anschließend hinzuzufügen. Datenbankadministratoren haben oft kein gutes Verständnis für den Geschäftsdatenfluss und das Hinzufügen von Indizes erfordert die Überwachung einer großen Anzahl von SQL-Anweisungen, um Probleme zu finden. Dieser Schritt dauert definitiv viel länger als das anfängliche Hinzufügen des Indexes und einige Indizes werden möglicherweise übersehen. Natürlich gilt: Je mehr Indizes, desto besser. Ich bin einmal auf ein Problem wie dieses gestoßen: Der iostat eines MySQL-Servers zeigte, dass die Festplattennutzungsrate immer bei 100 % lag. Nach der Analyse stellte sich heraus, dass dies daran lag, dass die Entwickler zu viele Indizes hinzugefügt hatten. Nachdem einige unnötige Indizes gelöscht wurden, sank die Festplattennutzungsrate sofort auf 20 %. Es ist ersichtlich, dass das Hinzufügen von Indizes auch sehr technisch ist.

2. Das Indexprinzip

1. Indexierungsprinzip

Der Zweck des Index besteht darin, die Abfrageeffizienz zu verbessern. Dies ist derselbe wie bei dem Katalog, den wir zum Nachschlagen von Büchern verwenden: Suchen Sie zuerst das Kapitel, dann einen Unterabschnitt unter dem Kapitel und dann die Seitenzahl. Ähnliche Beispiele sind: Nachschlagen in einem Wörterbuch, Überprüfen von Zugfahrplänen, Flugplänen usw.

Das Wesentliche besteht darin, den Umfang der abzurufenden Daten kontinuierlich einzuschränken und das endgültige gewünschte Ergebnis herauszufiltern und gleichzeitig zufällige Ereignisse in sequentielle Ereignisse umzuwandeln. Mit anderen Worten, mit diesem Indizierungsmechanismus können wir immer dieselbe Suchmethode verwenden, um die Daten zu sperren.

Dasselbe gilt für Datenbanken, aber es ist offensichtlich viel komplizierter, weil es nicht nur mit Gleichheitsabfragen zu tun hat, sondern auch mit Bereichsabfragen (>, <, zwischen, in), Fuzzy-Abfragen (wie), Union-Abfragen (oder) und so weiter. Wie sollte die Datenbank mit all den Problemen umgehen? Denken wir noch einmal an das Wörterbuchbeispiel zurück. Können wir die Daten in Segmente aufteilen und sie dann segmentweise abfragen? Am einfachsten geht das, indem Sie 1.000 Daten in einen ersten Abschnitt mit den Nummern 1 bis 100, einen zweiten Abschnitt mit den Nummern 101 bis 200 und einen dritten Abschnitt mit den Nummern 201 bis 300 aufteilen. Um dann das 250. Datenelement zu prüfen, müssen Sie nur den dritten Abschnitt finden und so 90 % der ungültigen Daten auf einmal eliminieren. Was aber ist, wenn 10 Millionen Datensätze vorhanden sind? In wie viele Segmente sollten diese unterteilt werden? Studenten mit Grundkenntnissen in Algorithmen werden an den Suchbaum denken, der eine durchschnittliche Komplexität von lgN aufweist und eine gute Abfrageleistung bietet. Dabei haben wir jedoch einen wesentlichen Punkt übersehen: Das Komplexitätsmodell basiert auf den Kosten jedes Mal derselben Operation. Die Datenbankimplementierung ist relativ komplex. Einerseits werden die Daten auf der Festplatte gespeichert. Andererseits kann zur Verbesserung der Leistung jedes Mal ein Teil der Daten zur Berechnung in den Speicher eingelesen werden. Da wir wissen, dass der Zugriff auf die Festplatte etwa 100.000 Mal so viel kostet wie der Zugriff auf den Speicher, ist es für einen einfachen Suchbaum schwierig, komplexe Anwendungsszenarien zu erfüllen.

2. Disk-E/A und Vorlesen

Wir haben den Festplattenzugriff bereits erwähnt, daher folgt hier eine kurze Einführung in Festplatten-E/A und Vorlesen. Das Lesen von Festplatten basiert auf mechanischer Bewegung. Die für jedes Datenlesen benötigte Zeit kann in drei Teile unterteilt werden: Suchzeit, Rotationsverzögerung und Übertragungszeit. Die Suchzeit bezieht sich auf die Zeit, die der Magnetarm benötigt, um sich zur angegebenen Spur zu bewegen, und bei herkömmlichen Festplatten liegt sie im Allgemeinen unter 5 ms. Die Rotationsverzögerung ist die Festplattenrotationsgeschwindigkeit, von der wir oft hören. Beispielsweise bedeutet eine Festplatte mit 7200 U/min, dass sie sich 7200 Mal pro Minute drehen kann, was bedeutet, dass sie sich 120 Mal pro Sekunde drehen kann. Die Rotationsverzögerung beträgt 1/120/2 = 4,17 ms. Die Übertragungszeit bezieht sich auf die Zeit, die zum Lesen oder Schreiben von Daten von der Festplatte benötigt wird. Sie beträgt im Allgemeinen einige Zehntel einer Millisekunde und kann im Vergleich zu den ersten beiden Zeiten ignoriert werden. Die Zeit für den Zugriff auf eine Festplatte, also die Zeit für einen Festplatten-IO, beträgt also etwa 5+4,17 = 9 ms. Das klingt zwar gut, aber Sie müssen wissen, dass eine 500-MIPS-Maschine (Millionen Anweisungen pro Sekunde) 500 Millionen Anweisungen pro Sekunde ausführen kann. Da Anweisungen von der Natur der Elektrizität abhängen, kann die Zeit für die Ausführung eines IO mit anderen Worten etwa 4,5 Millionen Anweisungen betragen. Datenbanken enthalten oft Hunderttausende, Millionen oder sogar Zehnmillionen von Daten. 9 Millisekunden jedes Mal sind offensichtlich eine Katastrophe. Die folgende Abbildung zeigt zu Ihrer Information einen Vergleich der Computerhardwareverzögerungen:

Da Disk-IO ein sehr aufwändiger Vorgang ist, hat das Betriebssystem des Computers einige Optimierungen vorgenommen. Während eines IO werden nicht nur die Daten an der aktuellen Disk-Adresse, sondern auch die benachbarten Daten in den Speicherpuffer gelesen. Das liegt daran, dass das Prinzip des lokalen Vorlesens besagt, dass, wenn der Computer auf Daten an einer Adresse zugreift, auch die benachbarten Daten schnell abgerufen werden. Die Daten, die jedes Mal von IO gelesen werden, werden als Seite bezeichnet. Die genaue Größe einer Seite hängt vom Betriebssystem ab und beträgt normalerweise 4 KB oder 8 KB. Das heißt, wenn wir Daten auf einer Seite lesen, erfolgt tatsächlich nur ein IO. Diese Theorie ist für den Entwurf der Indexdatenstruktur sehr hilfreich.

3. Indexdatenstruktur

Zuvor haben wir die Grundprinzipien der Indizierung, die Komplexität von Datenbanken und die relevanten Kenntnisse über Betriebssysteme besprochen. Ziel ist es, allen klar zu machen, dass keine Datenstruktur aus dem Nichts kommt und ihren Hintergrund und ihre Verwendungsszenarien haben muss. Lassen Sie uns nun zusammenfassen, was diese Datenstruktur tun soll. Tatsächlich ist es sehr einfach, nämlich: Kontrollieren Sie bei jedem Nachschlagen von Daten die Anzahl der Festplatten-IO-Vorgänge auf eine sehr kleine Größenordnung, vorzugsweise eine konstante Größenordnung. Wir fragen uns also, ob ein hochgradig steuerbarer Mehrwege-Suchbaum den Anforderungen gerecht werden kann? Auf diese Weise entstand der B+-Baum (der B+-Baum entwickelte sich aus dem binären Suchbaum, dann aus dem balancierten Binärbaum und dem B-Baum).

Wie in der Abbildung oben gezeigt, handelt es sich um einen B+-Baum. Die Definition des B+-Baums finden Sie unter B+-Baum. Hier werden wir nur einige wichtige Punkte besprechen. Der hellblaue Block wird als Plattenblock bezeichnet. Sie können sehen, dass jeder Plattenblock mehrere Datenelemente (dunkelblau dargestellt) und Zeiger (gelb dargestellt) enthält. Beispielsweise enthält Plattenblock 1 die Datenelemente 17 und 35 sowie die Zeiger P1, P2 und P3. P1 stellt einen Plattenblock kleiner als 17 dar, P2 stellt einen Plattenblock zwischen 17 und 35 dar und P3 stellt einen Plattenblock größer als 35 dar. Die realen Daten existieren in den Blattknoten 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90 und 99. Nicht-Blattknoten speichern keine echten Daten, sondern nur Datenelemente, die die Suchrichtung vorgeben. Beispielsweise sind 17 und 35 in der Datentabelle nicht wirklich vorhanden.

###b+ Baumsuchprozess

Wie in der Abbildung gezeigt, wird, wenn Sie das Datenelement 29 finden möchten, zuerst Datenträgerblock 1 von der Festplatte in den Speicher geladen. Zu diesem Zeitpunkt erfolgt ein IO. Eine binäre Suche im Speicher wird verwendet, um zu bestimmen, dass 29 zwischen 17 und 35 liegt. Der P2-Zeiger von Datenträgerblock 1 ist gesperrt. Die Speicherzeit ist sehr kurz (im Vergleich zum Datenträger-IO) und kann ignoriert werden. Datenträgerblock 3 wird über die Datenträgeradresse des P2-Zeigers von Datenträgerblock 1 von der Festplatte in den Speicher geladen. Der zweite IO erfolgt. 29 liegt zwischen 26 und 30. Der P2-Zeiger von Datenträgerblock 3 ist gesperrt. Datenträgerblock 8 wird über den Zeiger in den Speicher geladen. Der dritte IO erfolgt. Gleichzeitig wird eine binäre Suche im Speicher durchgeführt, um 29 zu finden, und die Abfrage endet. Insgesamt werden drei IOs durchgeführt. Tatsächlich kann ein 3-Schicht-B+-Baum Millionen von Daten darstellen. Wenn für die Suche nach Millionen von Daten nur drei IOs erforderlich sind, ist die Leistungssteigerung enorm. Wenn kein Index vorhanden ist, ist für jedes Datenelement ein IO erforderlich, sodass insgesamt Millionen von IOs erforderlich sind, was offensichtlich sehr kostspielig ist.

###b+Baumeigenschaften

1. Das Indexfeld sollte so klein wie möglich sein: Durch die obige Analyse wissen wir, dass die Anzahl der IO-Vorgänge von der Höhe h von b+Zahl abhängt. Angenommen, die Daten in der aktuellen Datentabelle sind N und die Anzahl der Datenelemente in jedem Plattenblock ist m, dann ist h=㏒(m+1)N. Wenn das Datenvolumen N konstant ist, ist h umso kleiner, je größer m ist; und m = Plattenblockgröße/Datenelementgröße. Die Plattenblockgröße ist die Größe einer Datenseite, die fest ist. Wenn der von den Datenelementen belegte Speicherplatz kleiner und die Anzahl der Datenelemente größer ist, ist die Höhe des Baums geringer. Aus diesem Grund sollte jedes Datenelement, d. h. das Indexfeld, so klein wie möglich sein. Beispielsweise belegt int 4 Bytes, also die Hälfte der 8 Bytes von bigint. Aus diesem Grund erfordert der B+-Baum, dass die tatsächlichen Daten in den Blattknoten und nicht in den inneren Knoten platziert werden. Sobald sie in den inneren Knoten platziert sind, sinken die Datenelemente der Festplattenblöcke erheblich, wodurch die Höhe des Baums zunimmt. Wenn das Datenelement gleich 1 ist, degeneriert es zu einer linearen Liste.

2. Die ganz links stehende Übereinstimmungsfunktion des Index: Wenn das Datenelement des B+-Baums eine zusammengesetzte Datenstruktur ist, wie z. B. (Name, Alter, Geschlecht), erstellt der B+-Baum den Suchbaum der Reihe nach von links nach rechts. Wenn beispielsweise Daten wie (Zhang San, 20, W) abgerufen werden, vergleicht der B+-Baum zuerst den Namen, um die nächste Suchrichtung zu bestimmen. Wenn der Name gleich ist, werden Alter und Geschlecht nacheinander verglichen, um schließlich die abgerufenen Daten zu erhalten. Wenn jedoch Daten ohne Namen wie (20, W) kommen, weiß der B+-Baum nicht, welcher Knoten als nächstes überprüft werden soll, da der Name der erste Vergleichsfaktor ist, wenn der Suchbaum erstellt wird, und es notwendig ist, zuerst nach dem Namen zu suchen, um zu wissen, wo als nächstes abgefragt werden soll. Wenn beispielsweise Daten wie (Zhang San, F) abgerufen werden, kann der b+-Baum den Namen verwenden, um die Suchrichtung anzugeben, aber das nächste Feld „Alter“ fehlt. Daher kann er nur die Daten mit dem Namen „Zhang San“ finden und dann die Daten mit dem Geschlecht „F“ abgleichen. Dies ist eine sehr wichtige Eigenschaft, nämlich das am weitesten links stehende Übereinstimmungsmerkmal des Index.

4. Clustered-Index und Hilfsindex

In der Datenbank beträgt die Höhe des B + -Baums im Allgemeinen 2 bis 4 Ebenen, was bedeutet, dass höchstens 2 bis 4 IOs erforderlich sind, um einen Zeilendatensatz mit einem bestimmten Schlüsselwert zu finden, was ziemlich gut ist. Da die aktuelle allgemeine mechanische Festplatte mindestens 100 IOs pro Sekunde ausführen kann, bedeuten 2 bis 4 IOs, dass die Abfragezeit nur 0,02 bis 0,04 Sekunden beträgt.

Der B+-Baumindex in der Datenbank kann in einen Clusterindex und einen Sekundärindex unterteilt werden.

Das Gleiche gilt für Cluster-Indizes und Hilfsindizes: Beide haben die Form eines B+-Bäumchens, d. h. die Höhe ist ausgeglichen und die Blattknoten speichern alle Daten.

Der Unterschied zwischen einem gruppierten Index und einem sekundären Index besteht darin, ob der Blattknoten eine ganze Zeile an Informationen speichert.

1. Clustered-Index

#InnoDB-Speicher-Engine bedeutet indexorganisierte Tabelle, d. h. die Daten in der Tabelle werden in der Reihenfolge des Primärschlüssels gespeichert. Ein gruppierter Index erstellt einen B+-Baum entsprechend dem Primärschlüssel jeder Tabelle. Die Blattknoten speichern die Zeilendatensätze der gesamten Tabelle. Die Blattknoten des gruppierten Index werden Datenseiten genannt. Diese Eigenschaft des gruppierten Indexes bestimmt, dass die Daten in der indexorganisierten Tabelle auch Teil des Indexes sind.

Wie bei der B+-Baumdatenstruktur ist jede Datenseite über eine doppelt verknüpfte Liste verknüpft. #Wenn der Primärschlüssel nicht definiert ist, nimmt MySQL den ersten eindeutigen Index (unique), der nur nicht leere Spalten (NOT NULL) enthält, als Primärschlüssel, und InnoDB verwendet ihn als gruppierten Index. #Wenn es keine solche Spalte gibt, generiert InnoDB selbst einen ID-Wert wie diesen. Er hat sechs Bytes und ist versteckt, was ihn zu einem gruppierten Index macht. #Da die eigentlichen Datenseiten nur nach einem B+-Baum sortiert werden können, kann jede Tabelle nur einen gruppierten Index haben. In vielen Fällen bevorzugt der Abfrageoptimierer einen gruppierten Index.

Weil der gruppierte Index Daten direkt auf den Blattknoten des B+-Baumindex finden kann. Darüber hinaus können gruppierte Indizes einen besonders schnellen Zugriff auf Abfragen ermöglichen, die auf Bereichswerte abzielen, da sie die logische Reihenfolge der Daten definieren.

Einer der Vorteile eines gruppierten Indexes besteht darin, dass er Primärschlüssel schnell sortieren und suchen kann und die Daten in den Blattknoten genau das sind, was der Benutzer abfragen möchte. Wenn ein Benutzer beispielsweise eine Tabelle durchsuchen und die letzten 10 Ziffern der Benutzerinformationen abfragen muss, kann er schnell die letzte Datenseite finden und 10 Datensätze abrufen, da der B+-Baumindex eine bidirektional verknüpfte Liste ist.

Der zweite Vorteil von Clustered-Indizes: Bereichsabfragen. Das heißt, wenn Sie Daten innerhalb eines bestimmten Bereichs des Primärschlüssels suchen möchten, können Sie den Seitenbereich über die oberen Zwischenknoten der Blattknoten abrufen und dann die Datenseite direkt lesen.

2. Hilfsindex

Mit Ausnahme des gruppierten Index sind alle anderen Indizes in der Tabelle sekundäre Indizes (auch nicht gruppierte Indizes genannt). Der Unterschied zum gruppierten Index besteht darin, dass die Blattknoten des sekundären Index nicht alle Daten des Zeilendatensatzes enthalten.

Zusätzlich zum Schlüsselwert enthält jede Indexzeile des Blattknotens auch ein Lesezeichen. Das Lesezeichen wird verwendet, um der InnoDB-Speicher-Engine mitzuteilen, wo die dem Index entsprechenden Zeilendaten zu finden sind.

Da es sich bei der InnoDB-Speicher-Engine um eine indexorganisierte Tabelle handelt, ist das Lesezeichen des Hilfsindex der InnoDB-Speicher-Engine der Clustered-Indexschlüssel der entsprechenden Zeilendaten. Wie unten gezeigt

Die Existenz von Hilfsindizes hat keinen Einfluss auf die Organisation der Daten im Clustered-Index, sodass jede Tabelle mehrere Hilfsindizes, aber nur einen Clustered-Index haben kann. Bei der Suche nach Daten über Hilfsindizes durchläuft die InnoDB-Speicher-Engine die Hilfsindizes und erhält den Primärschlüssel des Primärschlüsselindex über den Zeiger auf Blattebene und findet dann über den Primärschlüsselindex einen vollständigen Zeilendatensatz.

Wenn Sie beispielsweise in einem Hilfsindexbaum mit einer Höhe von 3 nach Daten suchen, müssen Sie den Hilfsindexbaum dreimal durchlaufen, um den angegebenen Primärschlüssel zu finden. Wenn die Höhe des gruppierten Indexbaums ebenfalls 3 beträgt, müssen Sie den gruppierten Indexbaum dreimal durchsuchen, um schließlich die Seite zu finden, auf der sich die vollständigen Zeilendaten befinden. Daher sind insgesamt 6 logische IO-Zugriffe erforderlich, um die endgültige Datenseite abzurufen.

5. MySQL-Indexverwaltung

1. Funktion

1. Die Funktion des Index besteht darin, die Suche zu beschleunigen

2. Primärschlüssel, eindeutige und gemeinsame eindeutige Indizes sind in MySQL ebenfalls Indizes. Diese Indizes beschleunigen nicht nur die Suche, sie unterliegen aber auch Einschränkungen.

2. Häufig verwendete Indizes in MySQL

Normaler Index INDEX: Suche beschleunigen

Eindeutiger Index: -Primärschlüsselindex: Suche beschleunigen + Einschränkung (nicht leer, kann nicht wiederholt werden) -Eindeutiger Index UNIQUE: Suche beschleunigen + Einschränkung (kann nicht wiederholt werden)

Kombinierter Index: -PRIMARY KEY(id,name): kombinierter Primärschlüsselindex -UNIQUE(id,name): kombinierter eindeutiger Index -INDEX(id,name): kombinierter gemeinsamer Index

3. Zwei Haupttypen von Indizes: Hash und Btree

#Wenn wir den obigen Index erstellen, können wir den Indextyp dafür angeben, der in zwei Kategorien unterteilt ist
Hash-Index: Einzelabfrage ist schnell, Bereichsabfrage ist langsam
Btree-Typindex: B+-Baum, je mehr Schichten vorhanden sind, desto exponentieller wächst das Datenvolumen (wir verwenden ihn, weil InnoDB ihn standardmäßig unterstützt)

#Verschiedene Speicher-Engines unterstützen unterschiedliche Indextypen
InnoDB unterstützt Transaktionen, Zeilensperren, B-Baum, Volltext und andere Indizes, jedoch keine Hash-Indizes.
MyISAM unterstützt keine Transaktionen, aber Sperren auf Tabellenebene, B-Baum, Volltext und andere Indizes, jedoch keine Hash-Indizes.
Der Speicher unterstützt keine Transaktionen, aber Sperren auf Tabellenebene, B-Baum, Hash und andere Indizes, jedoch keine Volltextindizes.
NDB unterstützt Transaktionen, Zeilensperren und Hash-Indizes, jedoch keine B-Baum-, Volltext- und anderen Indizes.
Das Archiv unterstützt keine Transaktionen, aber Sperren auf Tabellenebene. B-Tree-, Hash-, Volltext- und andere Indizes werden nicht unterstützt.

4. Syntax zum Erstellen/Löschen von Indizes

#Methode 1: Erstellen Sie eine Tabelle CREATE TABLE Tabellenname (
 Feldname 1 Datentyp [Integritätsbeschränkungen…],
 Feldname 2 Datentyp [Integritätsbedingung...],
 [EINZIGARTIG | VOLLER TEXT | RÄUMLICH] INDEX | SCHLÜSSEL
 [Indexname] (Feldname [(Länge)] [ASC | DESC]) 
 );


#Methode 2: CREATE erstellt einen Index für eine vorhandene Tabelle CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX Indexname ON Tabellenname (Feldname [(Länge)] [ASC | DESC]);


#Methode 3: ALTER TABLE erstellt einen Index für eine vorhandene Tabelle ALTER TABLE Tabellenname ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
 Indexname(Feldname[(Länge)] [ASC |DESC]);
 
#Index löschen: DROP INDEX Indexname ON Tabellenname;

#Methode 1: Tabelle t1 erstellen (
 Ich würde int,
 Name Zeichen,
 Alter int,
 Geschlecht Aufzählung('männlich','weiblich'),
 eindeutiger Schlüssel uni_id(id),
 index ix_name(name) #index hat keinen Schlüssel
);


#Methode 2: Erstellen Sie den Index ix_age auf t1 (Alter);

#Methode 3: Tabelle ändern t1, Index hinzufügen ix_sex(sex);

#Viewmysql> anzeigen, Tabelle t1 erstellen;
| t1 | TABELLE ERSTELLEN `t1` (
 `id` int(11) DEFAULT NULL,
 `name` char(1) DEFAULT NULL,
 `Alter` int(11) DEFAULT NULL,
 `Geschlecht` enum('männlich','weiblich') DEFAULT NULL,
 EINZIGARTIGER SCHLÜSSEL `uni_id` (`id`),
 SCHLÜSSEL `ix_name` (`Name`),
 SCHLÜSSEL `ix_age` (`Alter`),
 SCHLÜSSEL `ix_sex` (`Geschlecht`)
) ENGINE=InnoDB STANDARD CHARSET=latin1

6. Testindex

1. Vorbereitung

#1. Tabelle vorbereiten create table s1(
Ich würde int,
Name varchar(20),
Geschlecht char(6),
E-Mail varchar(50)
);

#2. Erstellen Sie eine gespeicherte Prozedur, um Datensätze in Stapel einzufügen. Trennzeichen $$ #Deklarieren Sie das Endsymbol der gespeicherten Prozedur als $$
Prozedur auto_insert1() erstellen
BEGINNEN
 deklariere i als int default 1;
 während(i<3000000)mache
 in s1-Werte einfügen (i, „duoduo“, „männlich“, concat („duoduo“, „i“, „@oldboy“));
 setze i=i+1;
 Ende während;
END$$ #$$Ende-Trennzeichen; #Deklarieren Sie das Semikolon erneut als Endsymbol#3. Zeigen Sie die gespeicherte Prozedur an show create procedure auto_insert1\G 

#4. Rufen Sie die gespeicherte Prozedur call auto_insert1(); auf.

#Warten Sie eine gewisse Zeit, um die Leistung der Maschine zu sehen

Tipp: Die Dauer der Tabellenerstellung ist abhängig von der Leistungsfähigkeit des Rechners, bitte haben Sie also Geduld!

2. Testen Sie die Abfragegeschwindigkeit ohne Index

#Kein Index: MySQL hat keine Ahnung, ob es einen Datensatz mit der ID 333333333 gibt. Es kann die Datentabelle nur von Anfang bis Ende scannen. Zu diesem Zeitpunkt gibt es so viele IO-Operationen wie Festplattenblöcke, daher ist die Abfragegeschwindigkeit sehr langsam. mysql> select * from s1 where id=333333333;
Leeres Set (0,33 Sek.)

3. Wenn bereits eine große Datenmenge in der Tabelle vorhanden ist, ist das Erstellen eines Indexes für ein Feldsegment sehr langsam

4. Nachdem der Index erstellt wurde, wird die Abfragegeschwindigkeit erheblich verbessert, wenn das Feld als Abfragebedingung verwendet wird

PS:

1. MySQL durchsucht zunächst die Indextabelle basierend auf dem B+-Baumsuchprinzip und stellt schnell fest, dass der Datensatz mit der ID 333333333 nicht vorhanden ist. Die IO wird erheblich reduziert, sodass die Geschwindigkeit deutlich verbessert wird.

2. Wir können die Tabelle im Datenverzeichnis von MySQL finden und sehen, dass sie mehr Festplattenspeicher einnimmt.

3. Bitte beachten Sie, dass wie unten gezeigt

V. Fazit

#1. Stellen Sie sicher, dass Sie einen Index für das Suchbedingungsfeld erstellen, z. B. select * from s1 where id = 333; Sie müssen einen Index für die ID hinzufügen

#2. Wenn die Tabelle bereits eine große Datenmenge enthält, ist das Erstellen eines Index sehr langsam und nimmt Festplattenspeicher in Anspruch. Nachdem der Index erstellt wurde, ist die Abfragegeschwindigkeit höher. Beispielsweise wird durch Erstellen des Index idx auf s1(id) alle Daten in der Tabelle gescannt und anschließend wird id als Datenelement verwendet, um eine Indexstruktur zu erstellen und diese in der Tabelle auf der Festplatte zu speichern.
Nach der Erstellung erfolgt die Abfrage sehr schnell.

#3. Beachten Sie, dass der Index der InnoDB-Tabelle in der Datei s1.ibd gespeichert wird, während der Index der Myisam-Tabelle eine separate Indexdatei table1.MYI hat.

MySAM-Indexdateien und Datendateien sind getrennt und die Indexdateien speichern nur die Adressen der Datensätze.
In InnoDB ist die Tabellendatendatei selbst eine gemäß B+Tree (BTree steht für Balance True) organisierte Indexstruktur, und das Blattknoten-Datenfeld dieses Baums speichert vollständige Datensätze.
Der Schlüssel dieses Indexes ist der Primärschlüssel der Datentabelle, daher ist die InnoDB-Tabellendatendatei selbst der Primärindex.
Da die Datendateien von InnoDB nach dem Primärschlüssel gruppiert sind, erfordert InnoDB, dass die Tabelle einen Primärschlüssel haben muss (MyISAM benötigt keinen).
Wenn nicht explizit definiert, wählt das MySQL-System automatisch eine Spalte aus, die den Datensatz eindeutig als Primärschlüssel identifizieren kann.
Wenn diese Spalte nicht existiert, generiert MySQL automatisch ein implizites Feld für die InnoDB-Tabelle als Primärschlüssel. Die Länge dieses Felds beträgt 6 Bytes und der Typ ist Long Integer.

7. Indizes richtig verwenden

1. Indexfehler

Dies bedeutet nicht, dass das Erstellen eines Index die Abfrage definitiv beschleunigt. Wenn wir den Index verwenden möchten, um den erwarteten Effekt einer Verbesserung der Abfragegeschwindigkeit zu erzielen, müssen wir beim Hinzufügen des Index die folgenden Punkte beachten.

1. Gültigkeitsbereichsproblem oder die Bedingung ist nicht klar. Die folgenden Symbole oder Schlüsselwörter erscheinen in der Bedingung: >, >=, <, <=, !=, between...and..., like,

Größer als, kleiner als

Ungleich! =

zwischen ...und...

wie

2Versuchen Sie, Spalten mit hoher Unterscheidung als Indizes auszuwählen. Die Formel für die Unterscheidung lautet count(distinct col)/count(*), was das Verhältnis der nicht duplizierten Felder angibt. Je größer das Verhältnis, desto weniger Datensätze müssen wir scannen. Die Unterscheidung eines eindeutigen Schlüssels beträgt 1, während einige Status- und Geschlechtsfelder angesichts großer Datenmengen eine Unterscheidung von 0 aufweisen können. Jemand könnte fragen, ob es für dieses Verhältnis einen empirischen Wert gibt. Dieser Wert ist in verschiedenen Nutzungsszenarien schwer zu bestimmen. Im Allgemeinen verlangen wir, dass der Wert des zu verknüpfenden Felds über 0,1 liegt, was bedeutet, dass 1 Scan im Durchschnitt 10 Datensätze benötigt.

# Löschen Sie zuerst alle Indizes in der Tabelle, damit wir uns auf das Diskriminierungsproblem konzentrieren können mysql> desc s1;
+--------+----------+------+-----+---------+---------+----------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+--------+----------+------+-----+---------+---------+----------+
| Ich würde | int(11) | JA | MUL | NULL | |
| Name | varchar(20) | JA | | NULL | |
| Geschlecht | char(5) | JA | | NULL | |
| E-Mail | varchar(50) | JA | MUL | NULL | |
+--------+----------+------+-----+---------+---------+----------+
Zeilen im Set (0,00 Sek.)

mysql> Index a auf s1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,20 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql> Index d auf s1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,18 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql> Beschreibung s1;
+--------+----------+------+-----+---------+---------+----------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+--------+----------+------+-----+---------+---------+----------+
| Ich würde | int(11) | JA | | NULL | |
| Name | varchar(20) | JA | | NULL | |
| Geschlecht | char(5) | JA | | NULL | |
| E-Mail | varchar(50) | JA | | NULL | |
+--------+----------+------+-----+---------+---------+----------+
Zeilen im Set (0,00 Sek.)

Löschen Sie zunächst alle Indizes in der Tabelle und konzentrieren wir uns auf das Diskriminierungsproblem.

Wir schreiben eine gespeicherte Prozedur, um Batch-Datensätze zur Tabelle s1 hinzuzufügen. Der Wert des Namensfelds ist duoduo, was bedeutet, dass das Namensfeld einen geringen Unterscheidungsgrad aufweist (dasselbe gilt für das Geschlechtsfeld, mit dem wir uns später befassen werden).

Erinnern Sie sich an die Struktur des B+-Baums. Die Abfragegeschwindigkeit ist umgekehrt proportional zur Höhe des Baums. Um die Höhe des Baums sehr niedrig zu halten, muss Folgendes sichergestellt werden: In einer bestimmten Ebene sind die Datenelemente in der Reihenfolge von links nach rechts und von klein nach groß angeordnet, d. h. links 1 < links 2 < links 3 < ...

Bei Feldern mit geringer Unterscheidungskraft ist es unmöglich, die Größenbeziehung zu finden, da die Werte alle gleich sind. Wenn Sie dennoch einen B+-Baum verwenden möchten, um diese gleichwertigen Daten zu speichern, können Sie natürlich nur die Höhe des Baums erhöhen. Je geringer die Unterscheidungskraft des Felds, desto höher die Höhe des Baums. Im Extremfall, wenn die Werte der Indexfelder gleich sind, wird der B+-Baum fast zu einem Stock. Dies ist in diesem Beispiel ein Extremfall. Alle Werte im Namensfeld sind 'duoduo'

#Nun kommen wir zu einer Schlussfolgerung: Wenn wir einen Index für ein Feld mit geringer Unterscheidung erstellen, wird die Höhe des Indexbaums sehr hoch sein, aber welche konkreten Auswirkungen wird dies haben? ? ?

#1: Wenn die Bedingung name = 'xxxx' ist, kann sofort festgestellt werden, dass 'xxxx' nicht im Indexbaum enthalten ist (da alle Werte im Baum 'duoduo' sind), sodass die Abfragegeschwindigkeit sehr schnell ist

#2: Wenn die Bedingung name='duoduo' ist, können wir bei der Abfrage nie einen klaren Bereich von einer bestimmten Position im Baum erhalten, wir können nur nach unten, unten und unten schauen. . . Dies unterscheidet sich nicht wesentlich von den IO-Zeiten eines vollständigen Tabellenscans und ist daher langsam

analysieren

3. = und in können in beliebiger Reihenfolge stehen, zum Beispiel a = 1 und b = 2 und c = 3. Sie können einen (a, b, c)-Index in beliebiger Reihenfolge erstellen und der MySQL-Abfrageoptimierer hilft Ihnen dabei, ihn in eine Form zu optimieren, die der Index erkennen kann.

4. Indexspalten können nicht in Berechnungen verwendet werden. Halten Sie die Spalten „sauber“. Wenn beispielsweise from_unixtime(create_time) = '2014-05-29' ist, kann der Index nicht verwendet werden. Der Grund ist einfach. Der b+-Baum speichert die Feldwerte in der Datentabelle. Bei der Suche müssen jedoch alle Elemente mit der Funktion verglichen werden, was offensichtlich zu aufwändig ist. Die Anweisung sollte also wie folgt geschrieben werden: create_time = unix_timestamp('2014-05-29')

5. und/oder

#1. Logische Bedingungen von und und oder 1 und 2: Alle Bedingungen müssen erfüllt sein, um als wahr zu gelten. Wenn eine Bedingung nicht erfüllt ist, ist das Endergebnis nicht wahr. 1 oder 2: Solange eine Bedingung erfüllt ist, ist das Endergebnis wahr.

#2. Funktionsprinzip und Bedingungen von AND:
a = 10 und b = 'xxx' und c > 3 und d = 4
Index:
Erstellen Sie einen gemeinsamen Index (d, a, b, c).
Funktionsprinzip:
Für mehrere aufeinanderfolgende Unds: MySQL findet ein Indexfeld mit hoher Unterscheidung von links nach rechts gemäß dem gemeinsamen Index (so dass ein kleiner Bereich schnell gesperrt werden kann), um die Abfrage zu beschleunigen, d. h. in der Reihenfolge d->a->b->c

#3.Arbeitsprinzipbedingungen für unseren Betrieb:
a = 10 oder b = 'xxx' oder c > 3 oder d = 4
Index:
Erstellen Sie einen gemeinsamen Index (d, a, b, c).

Funktionsprinzip:
Bei mehreren aufeinanderfolgenden ODERs: MySQL beurteilt von links nach rechts in der Reihenfolge der Bedingungen, d. h. a->b->c->d

Wenn die Bedingung links erfüllt ist, das Indexfeld jedoch eine geringe Unterscheidungskraft hat (Name, Beschleunigung der Abfrage)

6 Das Prinzip der Übereinstimmung mit dem am weitesten links stehenden Präfix ist ein sehr wichtiges Prinzip. Bei einem kombinierten Index führt MySQL die Übereinstimmung nach rechts weiter aus, bis es auf eine Bereichsabfrage (>, <, zwischen, wie) stößt, und stoppt dann die Übereinstimmung (was bedeutet, dass der Bereich groß und die Indexgeschwindigkeit langsam ist). Wenn beispielsweise a = 1 und b = 2 und c > 3 und d = 4 in der Reihenfolge (a, b, c, d) erstellt werden, wird d im Index nicht verwendet. Wenn ein Index in der Reihenfolge (a, b, d, c) erstellt wird, können alle verwendet werden und die Reihenfolge von a, b, d kann beliebig angepasst werden.

7. Andere Situationen

- Verwenden Sie die Funktion „select * from tb1 where reverse(email) = 'duoduo';“
 
- Typinkonsistenz. Wenn die Spalte vom Typ String ist, muss die Eingabebedingung in Anführungszeichen gesetzt werden, andernfalls ...
 Wählen Sie * aus tb1, wobei E-Mail = 999 ist;
 
#Wenn die Sortierbedingung ein Index ist, muss das Auswahlfeld auch ein Indexfeld sein, sonst wird es nicht gefunden - sortieren nach
 Wählen Sie den Namen aus S1, bestellen Sie nach E-Mail-Abstieg;
 Wenn beim Sortieren nach Index das ausgewählte Abfragefeld nicht indiziert ist, ist die Geschwindigkeit immer noch langsam. Wählen Sie E-Mail aus S1 aus, um nach E-Mail zu sortieren, desc;
 Besonderheit: Auch wenn man nach Primärschlüssel sortiert, geht es sehr schnell:
 Wählen Sie * aus TB1, sortiert nach NID Desc;
 
- Das am weitesten links stehende Präfix des kombinierten Index. Wenn der kombinierte Index lautet: (Name, E-Mail)
 Name und E-Mail -- trifft Indexnamen -- trifft Indexemail -- verfehlt Index - count(1) oder count(column) statt count(*) macht in MySQL keinen Unterschied - erstelle Index xxxx auf tb(title(19)) #Texttyp, Länge muss angegeben werden

Weitere Überlegungen

- Vermeiden Sie die Verwendung von „select *“
- count(1) oder count(column) statt count(*)
- Versuchen Sie beim Erstellen einer Tabelle, char statt varchar zu verwenden
- Die Reihenfolge der Felder in der Tabelle ist: Felder mit fester Länge zuerst
- Zusammengesetzte Indizes anstelle mehrerer einspaltiger Indizes (wenn häufig mehrere Bedingungen für die Abfrage verwendet werden)
- Versuchen Sie, kurze Indizes zu verwenden
- Verwenden Sie JOIN anstelle von Unterabfragen
- Beim Verknüpfen von Tabellen ist auf die Konsistenz des Bedingungstyps zu achten
- Index-Hashwerte (mit wenigen Duplikaten) sind nicht für die Indizierung geeignet, z.B. ist das Geschlecht nicht geeignet

8. Gemeinsamer Index und Deckindex

1. Gemeinsamer Index

Ein gemeinsamer Index bezieht sich auf die Kombination mehrerer Spalten einer Tabelle in einem Index. Die Methode zum Erstellen eines gemeinsamen Indexes ist dieselbe wie die zum Erstellen eines einzelnen Indexes. Der einzige Unterschied besteht darin, dass es mehrere Indexspalten gibt, wie folgt

mysql> Tabelle erstellen t(
 -> eine Ganzzahl,
 -> b int,
 -> Primärschlüssel(a),
 -> Schlüssel idx_a_b(a,b)
 -> );
Abfrage OK, 0 Zeilen betroffen (0,11 Sek.)

Wann müssen Sie also einen gemeinsamen Index verwenden? Bevor wir dieses Problem diskutieren, werfen wir einen Blick auf die Ergebnisse im gemeinsamen Index. Im Wesentlichen ist ein gemeinsamer Index ein B + -Baum. Der Unterschied besteht darin, dass die Anzahl der Schlüsselwerte in einem gemeinsamen Index nicht 1, sondern >= 2 ist. Als nächstes besprechen wir den gemeinsamen Index, der aus zwei ganzzahligen Spalten besteht. Angenommen, die Namen der beiden Schlüsselwerte sind a und b, wie in der Abbildung gezeigt.

Sie können sehen, dass sich dies nicht von dem Einzelschlüssel-B+-Baum unterscheidet, den wir zuvor gesehen haben. Die Schlüsselwerte sind sortiert und alle Daten können logisch und sequenziell über die Blattknoten gelesen werden. Im obigen Beispiel (1,1), (1,2), (2,1), (2,4), (3,1), (3,2) werden die Daten in der Reihenfolge (a,b) gespeichert.

Daher ist es offensichtlich, dass für die Abfrage „select * from table where a=xxx and b=xxx“ der gemeinsame Index (a,b) verwendet werden kann. Für die Abfrage „select * from table where a=xxx“ auf einer einzelnen Spalte a kann auch der Index (a,b) verwendet werden.

Aber für die Abfrage der Spalte b, select * from table where b=xxx, kann der Index (a,b) nicht verwendet werden. Tatsächlich ist es nicht schwer, den Grund zu finden. Die Werte von b auf den Blattknoten sind 1, 2, 1, 4, 1, 2, die offensichtlich nicht sortiert sind. Daher kann der Index (a,b) nicht für die Abfrage der Spalte b verwendet werden.

Der zweite Vorteil eines gemeinsamen Indexes ist, dass, wenn der erste Schlüssel derselbe ist, der zweite Schlüssel bereits sortiert wurde. Beispielsweise muss eine Anwendung in vielen Fällen den Einkaufsverlauf eines Benutzers abfragen, nach Zeit sortieren und schließlich die drei aktuellsten Kaufdatensätze abrufen. In diesem Fall kann die Verwendung eines gemeinsamen Indexes uns helfen, einen zusätzlichen Sortiervorgang zu vermeiden, da der Index selbst bereits am Blattknoten sortiert wurde, wie unten gezeigt.

#===========Tisch vorbereiten==============
Tabelle buy_log erstellen(
 Benutzer-ID int unsigniert nicht null,
 Kaufdatum Datum
);

in buy_log-Werte einfügen
(1,'2009-01-01'),
(2,'2009-01-01'),
(3,'2009-01-01'),
(1,'2009-02-01'),
(3,'2009-02-01'),
(1,'2009-03-01'),
(1,'2009-04-01');

Tabelle „buy_log“ ändern, Schlüssel hinzufügen (Benutzer-ID);
Tabelle „buy_log“ ändern, Schlüssel hinzufügen (Benutzer-ID, Kaufdatum);

#==========Überprüfung==============
mysql> anzeigen, Tabelle „buy_log“ erstellen;
| buy_log | CREATE TABLE `buy_log` (
 `userid` int(10) unsigned NOT NULL,
 `Kaufdatum` Datum DEFAULT NULL,
 SCHLÜSSEL `Benutzer-ID` (`Benutzer-ID`),
 SCHLÜSSEL `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8 |

#Sie können sehen, dass possible_keys hier über zwei Indizes verfügt, nämlich den einzelnen Index userid und den gemeinsamen Index userid_2, aber der Optimierer entscheidet sich schließlich für die Verwendung des Schlüssels userid, da der Blattknoten des Indexes einen einzelnen Schlüsselwert enthält, sodass eine Seite theoretisch mehr Datensätze speichern kann mysql> explain select * from buy_log where userid=2;
+----+----------+---------+------+-----------------+--------+---------+---------+-----------+-------+-----------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+------+-----------------+--------+---------+---------+-----------+-------+-----------+
| 1 | SIMPLE | buy_log | ref | Benutzer-ID, Benutzer-ID_2 | Benutzer-ID | 4 | const | 1 | |
+----+----------+---------+------+-----------------+--------+---------+---------+-----------+----------+-----------+
Zeile im Satz (0,00 Sek.)

#Nehmen Sie als Nächstes an, dass Sie die drei aktuellsten Kaufdatensätze für Benutzer-ID 1 abrufen möchten. Sie verwenden den gemeinsamen Index userid_2, da in diesem Index bei userid=1 das Kaufdatum bereits sortiert ist. mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3;
+----+----------+---------+------+-----------------+----------+---------+-----------+----------+-----------+--------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+------+-----------------+----------+---------+-----------+----------+-----------+--------------------------+
| 1 | SIMPLE | buy_log | ref | userid,userid_2 | userid_2 | 4 | const | 4 | Where verwenden; Index verwenden |
+----+----------+---------+------+-----------------+----------+---------+-----------+----------+-----------+--------------------------+
Zeile im Satz (0,00 Sek.)

#ps: Wenn bei der zusätzlichen Sortierung „Filesort verwenden“ angezeigt wird, bedeutet dies, dass nach dem Auffinden der Daten eine sekundäre Sortierung erforderlich ist. #Für den gemeinsamen Index (a, b) kann die folgende Anweisung den Index direkt ohne sekundäre Sortierung verwenden: select ... from table where a=xxx order by b;

#Dann kann für den gemeinsamen Index (a,b,c) die folgende Anweisung das Ergebnis auch direkt über die Indexauswahl erhalten ... from table where a=xxx order by b;
Wählen Sie ... aus der Tabelle, wobei a=xxx und b=xxx ist, sortiert nach c;

#Für den gemeinsamen Index (a, b, c) kann die folgende Anweisung das Ergebnis jedoch nicht direkt über den Index abrufen und Sie müssen selbst eine Dateisortieroperation durchführen, da der Index (a, c) nicht sortiert ist. select ... from table where a=xxx order by c;

2. Abdeckungsindex

Die InnoDB-Speicher-Engine unterstützt das Abdecken von Indizes (oder Index-Covering), d. h. Abfragedatensätze können aus Hilfsindizes abgerufen werden, ohne Datensätze im gruppierten Index abzufragen.

Ein Vorteil der Verwendung eines überdeckenden Indexes besteht darin, dass der Hilfsindex nicht alle Informationen des gesamten Zeilendatensatzes enthält und daher viel kleiner als der gruppierte Index ist, wodurch eine große Anzahl von E/A-Vorgängen reduziert wird.

Hinweis: Die Covering-Index-Technologie wurde zuerst im InnoDB-Plugin fertiggestellt und implementiert. Dies bedeutet, dass die InnoDB-Speicher-Engine für InnoDB-Versionen unter 1.0 oder MySQL-Datenbankversionen unter 5.0 keine Covering-Index-Funktionen unterstützt.

Für den Hilfsindex der InnoDB-Speicher-Engine gilt: Da er die Primärschlüsselinformationen enthält, lauten die in seinem Blattknoten gespeicherten Daten (Primärschlüssel1, Primärschlüssel2, …, Schlüssel1, Schlüssel2, …). Zum Beispiel

select age from s1 where id=123 and name = 'duoduo'; #Das ID-Feld hat einen Index, das Namensfeld jedoch nicht. Dieses SQL trifft auf den Index, deckt ihn aber nicht ab. Sie müssen im Clustered-Index nach detaillierten Informationen suchen.
Im besten Fall deckt das Indexfeld alles ab, sodass der gesamte Indizierungsprozess genutzt werden kann, um die Abfrage zu beschleunigen und Ergebnisse zu erhalten.mysql> desc s1;
+--------+----------+------+-----+---------+---------+----------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+--------+----------+------+-----+---------+---------+----------+
| Ich würde | int(11) | NEIN | | NULL | |
| Name | varchar(20) | JA | | NULL | |
| Geschlecht | char(6) | JA | | NULL | |
| E-Mail | varchar(50) | JA | | NULL | |
+--------+----------+------+-----+---------+---------+----------+
Zeilen im Satz (0,21 Sek.)

mysql> erläutern Sie „Select Name from s1 where id=1000;“ #Kein Index+----+-------------+-------+------------+------+---------------+---------+---------+---------+---------+---------+---------+---------+----------+----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+---------+---------+------+---------+---------+---------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 2688336 | 10.00 | Verwenden von where |
+----+----------+----------+---------+------+---------------+---------+---------+------+---------+---------+---------+----------+-------------+
Zeile im Satz, 1 Warnung (0,00 Sek.)

mysql> create index idx_id on s1(id); #Index erstellen Abfrage OK, 0 Zeilen betroffen (4,16 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

mysql> erklären select name from s1 where id=1000; #Der Hilfsindex wurde gefunden, aber der Index ist nicht abgedeckt. Sie müssen auch den Namen aus dem Clustered-Index finden
+----+----------+----------+---------+------+---------------+--------+---------+---------+---------+-------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+--------+---------+---------+---------+-------+------+------+------+
| 1 | EINFACH | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100,00 | NULL |
+----+----------+----------+---------+------+---------------+--------+---------+---------+---------+-------+------+------+------+
Zeile im Satz, 1 Warnung (0,08 Sek.)

mysql> Erklärung select id from s1 where id=1000; #Alle Informationen sind im Hilfsindex zu finden. Index verwenden bedeutet, Index abzudecken+----+----------+----------+------------+------+---------------+--------+---------+---------+---------+---------+---------+----------+----------+----------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+------------+------+---------------+--------+---------+---------+----------+----------+----------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Index wird verwendet |
+----+----------+-------+------------+------+---------------+--------+---------+---------+----------+-------+----------+-------------+
Zeile im Satz, 1 Warnung (0,03 Sek.)

Ein weiterer Vorteil der Indizes-Abdeckung liegt in der Lösung bestimmter statistischer Probleme. Basierend auf der in der vorherigen Zusammenfassung erstellten Tabelle buy_log lautet der Abfrageplan wie folgt

mysql> erklären Sie select count(*) aus buy_log;
+----+----------+---------+-------+---------------+--------+---------+---------+------+---------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+-------+---------------+--------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | buy_log | index | NULL | Benutzer-ID | 4 | NULL | 7 | Index wird verwendet |
+----+----------+---------+-------+---------------+--------+---------+---------+------+---------+-------------+
Zeile im Satz (0,00 Sek.)

Die InnoDB-Speicher-Engine führt keine Statistiken durch, indem sie den gruppierten Index abfragt. Da die Tabelle buy_log einen Sekundärindex hat und dieser viel kleiner als der Clusterindex ist, kann die Auswahl des Sekundärindex die Anzahl der E/A-Vorgänge verringern. Daher wählt der Optimierer den Sekundärindex der Benutzer-ID als obigen Schlüssel.

Bei einem gemeinsamen Index der Form (a, b) ist es im Allgemeinen nicht möglich, die sogenannte Abfragebedingung in b auszuwählen. Wenn es sich jedoch um eine statistische Operation handelt und es sich um einen abdeckenden Index handelt, verwendet der Optimierer den Index dennoch wie folgt

#Gemeinsamer Index userid_2 (userid, buy_date). Normalerweise können wir diesen Index nicht entsprechend buy_date verwenden. In besonderen Fällen jedoch, wenn die Abfrageanweisung eine statistische Operation ist und es sich um einen überdeckenden Index handelt, können wir diesen gemeinsamen Index auch verwenden, wenn buy_date als Abfragebedingung verwendet wird. mysql> explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date < '2011-02-01';
+----+----------+---------+-------+---------------+-----------+----------+---------+---------+------+---------+--------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+---------+-------+---------------+-----------+----------+---------+---------+------+---------+--------------------------+
| 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Verwenden von where; Verwenden von index |
+----+----------+---------+-------+---------------+-----------+----------+---------+---------+------+---------+--------------------------+
Zeile im Satz (0,00 Sek.)

9. Abfrageoptimierungstool - Erläuterung

Ich glaube, jeder kennt den Befehl „explain“. Informationen zur spezifischen Verwendung und Feldbedeutung finden Sie auf der offiziellen Website „explain-output“. Hier sollte betont werden, dass Zeilen der Kernindikator sind. Die meisten Anweisungen mit kleinen Zeilen müssen sehr schnell ausgeführt werden (es gibt Ausnahmen, die weiter unten erläutert werden). Optimierungsanweisungen optimieren daher grundsätzlich Zeilen.

Ausführungsplan: Lassen Sie MySQL den Ausführungsvorgang schätzen (normalerweise korrekt)
alle < Index < Bereich < Index_Merge < Ref_oder_Null < Ref < Eq_Ref < System/Const
ID, E-Mail

langsam:
Wählen Sie * aus Benutzerinfo3, wobei Name = "Alex" ist.

Erklären Sie „select * from userinfo3 where name='alex'“.
Typ: ALL (vollständiger Tabellenscan)
Wähle * aus Benutzerinfo3, Limit 1;
schnell:
Wählen Sie * aus Benutzerinfo3, wobei E-Mail = "Alex" ist.
Typ: const (Go-Index)

Referenz: https://www.jb51.net/article/140759.htm

10. Grundlegende Schritte zur Optimierung langsamer Abfragen

0. Führen Sie zuerst aus, um zu sehen, ob es wirklich langsam ist, und setzen Sie SQL_NO_CACHE
1. Wenn die Bedingung für die Abfrage einer einzelnen Tabelle vorliegt, sperren Sie die Tabelle mit den Datensätzen mit der Mindestrendite. Dieser Satz bedeutet, dass die Where-Klausel der Abfrageanweisung auf die Tabelle mit der geringsten Anzahl zurückgegebener Datensätze angewendet und dann jedes Feld der Tabelle einzeln abgefragt werden soll, um zu sehen, welches Feld die höchste Unterscheidungsfähigkeit aufweist.
2. Erklären Sie, um zu überprüfen, ob der Ausführungsplan mit der Erwartung in 1 übereinstimmt (Starten Sie die Abfrage aus der Tabelle mit weniger gesperrten Datensätzen).
3. Die SQL-Anweisung der Form order by limit bewirkt zunächst eine sortierte Tabellensuche
4. Verstehen Sie die Nutzungsszenarien der Geschäftsseite
5. Beachten Sie beim Hinzufügen von Indizes die wichtigsten Prinzipien der Indizierung
6. Beobachten Sie die Ergebnisse. Wenn sie nicht den Erwartungen entsprechen, fahren Sie mit der Analyse ab 0 fort.

11. Langsames Protokollmanagement

Langsames Protokoll
- Ausführungszeit > 10
- Verpasster Index
- Pfad der Protokolldatei

Konfiguration:
- Erinnerung
Variablen wie „%query%“ anzeigen;
Variablen wie „%queries%“ anzeigen;
globalen Variablennamen = Wert festlegen
- Konfigurationsdatei
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

Inhalt von my.conf:
slow_query_log = EIN
slow_query_log_file = D:/....

Hinweis: Nach dem Ändern der Konfigurationsdatei müssen Sie den Dienst neu starten

MySQL-Protokollverwaltung

========================================================

Fehlerprotokoll: Zeichnet die Start-, Herunterfahr- und Betriebsfehler des MySQL-Servers auf. Binärprotokoll: Wird auch als Binärprotokoll bezeichnet. Zeichnet die Vorgänge in der Datenbank außer SELECT in einer Binärdatei auf. Abfrageprotokoll: Zeichnet die Abfrageinformationen auf. Langsames Abfrageprotokoll: Zeichnet die Vorgänge auf, deren Ausführung länger als die angegebene Zeit dauert. Relay-Protokoll: Die Standby-Datenbank kopiert das Binärprotokoll der Master-Datenbank in ihr eigenes Relay-Protokoll, um es lokal wiederzugeben. Allgemeines Protokoll: Prüft, welches Konto, in welchem ​​Zeitraum und welche Ereignisse ausgeführt wurden. Transaktionsprotokoll oder Redo-Protokoll: Zeichnet transaktionsbezogene Informationen von Innodb auf, wie z. B. Transaktionsausführungszeit, Prüfpunkte usw.
========================================================

1. Binärprotokoll

1. Aktivieren

# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[Dateiname]]
# service mysqld neu starten

2. Pause

//Nur aktuelle Sitzung SET SQL_LOG_BIN=0;
SETZEN Sie SQL_LOG_BIN=1;

3. Ansicht

Alle anzeigen:

# mysqlbinlog mysql.000002
Nach Zeit:
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# MySQLBINLOG MYSQL.000002-START-DATETIME = "2012-12-05 10:02:56"-STOP-DATETIME = "2012-12-05 11:02:54"

Von Bytes:

# MySQLBINLOG MySQL.000002-Start-Position = 260
# MySQLBINLOG MySQL.000002-STOP-POSION = 260
# MySQLBINLOG MYSQL.000002-START-POSION = 260-STOP-POSITION = 930

4. Truncate Bin-Log (neue Bin-Log-Datei generieren)

a

b.

5. Bin-Log-Dateien löschen

# Mysql -uroot -p123 -e 'Reset Master'

2. Abfrageprotokoll

Aktivieren Sie das allgemeine Abfrageprotokoll

# vim /etc/my.cnf
[mysqld]
log [= dir \ [Dateiname]]]
# service mysqld neu starten

3. Langsames Abfrageprotokoll

Aktivieren Sie langsames Abfrageprotokoll

# vim /etc/my.cnf
[mysqld]
Log-Slow-Querien [= dir \ [Dateiname]]]
long_query_time = n
# service mysqld neu starten

MySQL 5.6:

Langsamquer-Log = 1
Slow-Quer-Log-Datei = Slow.log
lange_Abfragezeit=3

Anzeigen von Protokollen langsamer Abfragen

prüfen:

Benchmark (Graf, Expr.)
Benchmark auswählen (50000000,2*3);

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.