Detaillierte Erläuterung der MySQL-Indexprinzipien und -Optimierung

Vorwort

Dieser Artikel wurde von einem hohen Tier von Meituan geschrieben. Er ist ziemlich gut und ich möchte ihn mit Ihnen teilen. Die im Code in HTML eingebetteten SQL-Anweisungen werden im Java-Framework geschrieben. Sie müssen nur die auszuführenden SQL-Anweisungen verstehen.

Hintergrund

Aufgrund seiner hervorragenden Leistung, seiner geringen Kosten und seiner umfangreichen Ressourcen ist MySQL für die meisten Internetunternehmen zur bevorzugten relationalen Datenbank geworden. Obwohl es eine hervorragende Leistung bietet, ist es, wie das Sprichwort sagt, „ein gutes Pferd verdient einen guten Sattel“, zu einem Pflichtkurs für Entwicklungsingenieure geworden, wie man es besser nutzt. In Stellenbeschreibungen sehen wir häufig Anforderungen wie „Kenntnisse in MySQL“, „SQL-Anweisungsoptimierung“ und „Verständnis der Datenbankprinzipien“. Wir wissen, dass in allgemeinen Anwendungssystemen das Lese-/Schreibverhältnis etwa 10:1 beträgt und Einfügevorgänge und allgemeine Aktualisierungsvorgänge selten Leistungsprobleme aufweisen. Die häufigsten und problematischsten Vorgänge sind einige komplexe Abfragevorgänge, sodass die Optimierung von Abfrageanweisungen offensichtlich oberste Priorität hat.

Seit Juli 2013 arbeite ich in der Abteilung für Kerngeschäftssysteme von Meituan an der Optimierung langsamer Abfragen. Dabei decke ich mehr als zehn Systeme ab und habe Hunderte von Fällen langsamer Abfragen gelöst und gesammelt. Mit zunehmender Komplexität des Geschäfts treten vielfältige und bizarre Probleme auf. Dieser Artikel soll die Prinzipien der Datenbankindizierung und die Optimierung langsamer Abfragen aus der Sicht eines Entwicklungsingenieurs erläutern.

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span>=<span class="hljs-number">2</span> <span class="hljs-keyword">and</span> operator_id=<span class="hljs-number">20839</span> <span class="hljs-keyword">and</span> operate_time><span class="hljs-number">1371169729</span> <span class="hljs-keyword">and</span> operate_time<<span class="hljs-number">1371174603</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">type</span>=<span class="hljs-number">2</span>;

Systembenutzer meldeten, dass eine Funktion zunehmend langsamer wurde, sodass Ingenieure das obige SQL fanden.

Und er kam aufgeregt zu mir und sagte: „Dieses SQL muss optimiert werden. Fügen Sie für mich jedem Feld Indizes hinzu.“

Ich war überrascht und fragte: „Warum muss ich jedes Feld indizieren?“

„Es geht schneller, wenn wir allen Abfragefeldern Indizes hinzufügen“, sagte der Ingenieur zuversichtlich.

„In diesem Fall können Sie einen gemeinsamen Index erstellen. Da es sich um eine Übereinstimmung mit dem Präfix ganz links handelt, muss die operation_time am Ende platziert werden, und es müssen andere zugehörige Abfragen ausgeführt und eine umfassende Auswertung durchgeführt werden.“

„Gemeinsamer Index? Ganz linkes Präfix übereinstimmen? Umfassende Auswertung?“ Der Ingenieur konnte nicht anders, als in tiefe Gedanken zu versinken.

In den meisten Fällen wissen wir, dass Indizes die Abfrageeffizienz verbessern können, aber wie sollten wir Indizes erstellen? Wie ist die Reihenfolge der Indizes? Viele wissen es nur ungefähr. Tatsächlich ist es nicht schwer, diese Konzepte zu verstehen, und die Prinzipien der Indizierung sind weit weniger kompliziert als gedacht.

Index Zweck

Der Zweck des Index besteht darin, die Abfrageeffizienz zu verbessern. Er kann mit einem Wörterbuch verglichen werden. Wenn wir das Wort „mysql“ nachschlagen möchten, müssen wir den Buchstaben m finden, dann von unten nach unten den Buchstaben y und dann den Rest von SQL. Wenn kein Index vorhanden ist, müssen Sie möglicherweise alle Wörter durchsuchen, um das Gewünschte zu finden. Was ist, wenn ich ein Wort finden möchte, das mit m beginnt? Oder Wörter, die mit ze beginnen? Glauben Sie, dass diese Aufgabe ohne einen Index nicht zu bewältigen ist?

Indexierungsprinzip

Beispiele für Indizes gibt es nicht nur in Wörterbüchern, sondern auch überall im Leben, z. B. in Zugfahrplänen an Bahnhöfen, in Buchkatalogen usw. Ihre Prinzipien sind dieselben: Sie filtern die endgültigen gewünschten Ergebnisse heraus, indem sie den Umfang der Daten, die sie erhalten möchten, kontinuierlich einschränken und gleichzeitig zufällige Ereignisse in sequenzielle Ereignisse umwandeln. Das heißt, wir sperren die Daten immer mit derselben Suchmethode.

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. Aber wir haben hier ein wichtiges Problem übersehen. Das Komplexitätsmodell basiert jedes Mal auf den gleichen Operationskosten. Die Datenbankimplementierung ist relativ komplex und die Daten werden auf der Festplatte gespeichert. Um die Leistung zu verbessern, können bei jedem Mal einige der Daten zur Berechnung in den Speicher eingelesen werden. Da wir wissen, dass die Kosten für den Zugriff auf die Festplatte etwa 100.000 Mal höher sind als die für den Zugriff auf den Speicher, ist es für einen einfachen Suchbaum schwierig, komplexe Anwendungsszenarien zu erfüllen.

Disk-E/A und Read-Ahead

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 gut, aber man muss wissen, dass eine 500-MIPS-Maschine 500 Millionen Anweisungen pro Sekunde ausführen kann, da Anweisungen von der Natur der Elektrizität abhängen. Mit anderen Worten: Die Zeit für die Ausführung eines IO kann 400.000 Anweisungen ausführen. 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:

Verschiedene System-Software-Hardware-Latenzen

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.

Indexdatenstruktur

Wir haben bereits über Beispiele für Indizes im Alltag, die Grundprinzipien von Indizes, die Komplexität von Datenbanken und das relevante Wissen über Betriebssysteme gesprochen. 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.

Detaillierte Erklärung des B+-Baums

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.

Der Suchvorgang des B+-Baums

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

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 Datenträgerblock ist m, dann ist h=㏒(m+1)N. Wenn das Datenvolumen N konstant ist, gilt: Je größer m, desto kleiner h; und m = Datenträgerblockgröße/Datenelementgröße. Die Datenträgerblockgröß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.

Wenn die Datenelemente des B+-Baums zusammengesetzte Datenstrukturen sind, wie etwa (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 die Namen gleich sind, werden Alter und Geschlecht nacheinander verglichen, um schließlich die abgerufenen Daten zu erhalten. Wenn jedoch Daten ohne Namen wie etwa (20, W) kommen, weiß der B+-Baum nicht, welcher Knoten als nächstes überprüft werden soll, da der Name der erste Vergleichsfaktor beim Erstellen des Suchbaums ist und es notwendig ist, zuerst basierend auf 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.

Das Prinzip des MySQL-Index ist relativ langweilig. Sie müssen es nur oberflächlich verstehen, aber nicht sehr gründlich und tiefgreifend. Lassen Sie uns auf die langsame Abfrage zurückblicken, über die wir am Anfang gesprochen haben. Haben Sie, nachdem Sie das Indexprinzip verstanden haben, irgendwelche Ideen? Lassen Sie uns zunächst die Grundprinzipien der Indizierung zusammenfassen:

Mehrere wichtige Prinzipien zum Erstellen von Indizes: Das Prinzip der Übereinstimmung mit dem am weitesten links stehenden Präfix, ein sehr wichtiges Prinzip. MySQL führt die Übereinstimmung nach rechts weiter aus, bis es auf eine Bereichsabfrage (>, <, zwischen, wie) stößt, und beendet dann die Übereinstimmung. Wenn beispielsweise a = 1 und b = 2 und c > 3 und d = 4 ist und Sie einen Index in der Reihenfolge (a, b, c, d) erstellen, wird d im Index nicht verwendet. Wenn Sie einen Index in der Reihenfolge (a, b, d, c) erstellen, können alle verwendet werden und die Reihenfolge von a, b, d kann beliebig angepasst werden. = 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. Versuchen Sie, Spalten mit hoher Unterscheidungskraft als Indizes auszuwählen. Die Formel für die Unterscheidungskraft 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 Unterscheidungskraft eines eindeutigen Schlüssels beträgt 1, während einige Status- und Geschlechtsfelder angesichts großer Datenmengen eine Unterscheidungskraft von 0 aufweisen können. Jemand könnte fragen, ob es für dieses Verhältnis einen empirischen Wert gibt. Dieser Wert ist aufgrund unterschiedlicher Verwendungsszenarien schwer zu bestimmen. Im Allgemeinen verlangen wir, dass der Wert des zu verknüpfenden Felds über 0,1 liegt, was bedeutet, dass für jedes Feld durchschnittlich 10 Datensätze gescannt werden. Indexspalten können nicht an Berechnungen teilnehmen. 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. Daher sollte die Anweisung als create_time = unix_timestamp('2014-05-29') geschrieben werden. Versuchen Sie den Index so weit wie möglich zu erweitern und erstellen Sie keinen neuen Index. Wenn in der Tabelle beispielsweise bereits ein Index für a vorhanden ist und Sie einen Index für (a,b) hinzufügen möchten, müssen Sie nur den ursprünglichen Index ändern. Kehren Sie zur langsamen Abfrage am Anfang zurück

Gemäß dem Prinzip der Übereinstimmung ganz links sollte der Index der ersten SQL-Anweisung der gemeinsame Index von Status, Operator-ID, Typ und Operate_Time sein. Die Reihenfolge von Status, Operator-ID und Typ kann umgekehrt werden. Daher habe ich gesagt, dass alle zugehörigen Abfragen dieser Tabelle umfassend gefunden und analysiert werden sollten. Beispielsweise gibt es auch die folgenden Abfragen:

<span class="hljs-keyword">Auswählen</span> * <span class="hljs-keyword">aus</span> Aufgabe <span class="hljs-keyword">wobei</span> <span class="hljs-keyword">Status</span> = <span class="hljs-number">0</span> <span class="hljs-keyword">und</span> <span class="hljs-keyword">Typ</span> = <span class="hljs-number">12</span> <span class="hljs-keyword">Limit</span> <span class="hljs-number">10</span>;

<span class="hljs-keyword">Auswählen</span> <span class="hljs-keyword">Anzahl</span>(*) <span class="hljs-keyword">von</span> Aufgabe <span class="hljs-keyword">wobei</span> <span class="hljs-keyword">Status</span> = <span class="hljs-number">0</span>;

Dann ist es sehr richtig, einen Index von (Status, Typ, Operator-ID, Betriebszeit) zu erstellen, da dieser alle Situationen abdecken kann. Dies ist das Prinzip der äußersten linken Übereinstimmung des Index.

Abfrageoptimierungstool – Befehl „explain“

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.

Grundlegende Schritte zur Optimierung langsamer Abfragen: Führen Sie die Abfrage zuerst aus, um zu sehen, ob sie wirklich langsam ist. Beachten Sie, dass Sie die Bedingung SQL_NO_CACHEwhere für einzelne Tabellenabfragen festlegen und die Tabelle mit den Datensätzen mit der Mindestrückgabe sperren sollten. Dieser Satz bedeutet, dass die Where-Klausel der Abfrageanweisung auf die Tabelle mit der geringsten Anzahl zurückgegebener Datensätze angewendet werden soll. Beginnen Sie mit der Abfrage jedes Felds einer einzelnen Tabelle, um zu sehen, welches Feld die höchste Unterscheidungskraft hat. Erklären Sie, ob der Ausführungsplan mit den Erwartungen in Schritt 1 übereinstimmt (beginnen Sie mit der Abfrage von der Tabelle mit weniger Datensätzen). Die SQL-Anweisung in der Form „Order by Limit“ ermöglicht es, zuerst die sortierte Tabelle zu durchsuchen. Verstehen Sie die Nutzungsszenarien der Geschäftsseite. Beachten Sie die wichtigsten Prinzipien der Indizierung, wenn Sie Indizes hinzufügen. Beobachten Sie die Ergebnisse. Wenn sie nicht den Erwartungen entsprechen, fahren Sie mit der Analyse mehrerer langsamer Abfragefälle ab Schritt 0 fort.

Die folgenden Beispiele erläutern ausführlich, wie langsame Abfragen analysiert und optimiert werden.

Wie man komplexe Sätze schreibt

In vielen Fällen schreiben wir SQL nur, um Funktionen zu implementieren. Dies ist nur der erste Schritt. Verschiedene Methoden zum Schreiben von Anweisungen weisen oft wesentliche Unterschiede in der Effizienz auf. Dies erfordert ein sehr klares Verständnis des Ausführungsplans und der Indexprinzipien von MySQL. Bitte beachten Sie die folgende Anweisung:

<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> cert.emp_id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> ( <span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">as</span> emp_id, emp_cert.id <span class="hljs-keyword">as</span> cert_id <span class="hljs-keyword">from</span> employee emp <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> emp_certificate emp_cert <span class="hljs-keyword">on</span> emp.id = emp_cert.emp_id <span class="hljs-keyword">where</span> emp.is_deleted=<span class="hljs-number">0</span> ) cert <span class="hljs-keyword">on</span> ( cl.ref_table=<span class="hljs-string">'Employee'</span> <span class="hljs-keyword">and</span> cl.ref_oid= cert.emp_id ) <span class="hljs-keyword">or</span> ( cl.ref_table=<span class="hljs-string">'EmpCertificate'</span> <span class="hljs-keyword">and</span> cl.ref_oid= cert.cert_id ) <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span>;

Führen Sie es zuerst aus, 53 Datensätze in 1,87 Sekunden, und es werden keine Aggregationsanweisungen verwendet, was langsam ist

53 Zeilen im <span class="hljs-keyword">Satz</span> (<span class="hljs-number">1,87</span> Sek.)

erklären

+<span class = "hljs-coment"> ----+------------- Zeilen | ASTEME | NULL | ------------+------------+-------+-----------------------------+---------------------------+----------+-------------------+-------+----------------------------+</span>

Um den Ausführungsplan kurz zu beschreiben: Zuerst durchsucht MySQL die cm_log-Tabelle basierend auf dem Index idx_last_upd_date, um 379 Datensätze zu erhalten. Dann sucht es in der Tabelle nach und durchsucht 63.727 Datensätze, die in zwei Teile unterteilt sind. Abgeleitet bedeutet eine konstruierte Tabelle, d. h. eine nicht vorhandene Tabelle, die einfach als ein durch eine Anweisung gebildeter Ergebnissatz verstanden werden kann, und die Zahl dahinter stellt die Anweisungs-ID dar. Derived2 gibt an, dass die Abfrage mit der ID = 2 eine virtuelle Tabelle erstellt und 63727 Datensätze zurückgegeben hat. Schauen wir uns an, was die Anweisung mit der ID = 2 macht, um eine so große Datenmenge zurückzugeben. Zuerst wird die Tabelle employee vollständig nach 13.317 Datensätzen durchsucht. Dann wird die Tabelle emp_certificate mit dem Index emp_certificate_empid verknüpft. Rows = 1 bedeutet, dass jede Verknüpfung nur einen Datensatz sperrt, was effizienter ist. Nach dem Erhalt wird es gemäß den Regeln mit den 379 Datensätzen von cm_log verknüpft. Aus dem Ausführungsprozess ist ersichtlich, dass zu viele Daten zurückgegeben werden und die meisten der zurückgegebenen Daten von cm_log nicht verwendet werden, da cm_log nur 379 Datensätze sperrt.

Wie kann es optimiert werden? Sie können sehen, dass wir nach dem Ausführen noch eine Verbindung mit cm_log herstellen müssen. Können wir also vorher eine Verbindung mit cm_log herstellen? Eine sorgfältige Analyse der Anweisung zeigt, dass die Grundidee darin besteht, dass, wenn die ref_table von cm_log EmpCertificate ist, sie mit der emp_certificate-Tabelle verknüpft ist, und wenn die ref_table Employee ist, sie mit der employee-Tabelle verknüpft ist. Wir können sie vollständig in zwei Teile aufteilen und sie mit union verbinden. Beachten Sie, dass hier union statt union all verwendet wird, da die ursprüngliche Anweisung „distinct“ hat, um den eindeutigen Datensatz zu erhalten, und union zufällig diese Funktion hat. Wenn in der ursprünglichen Anweisung kein Unterschied besteht und keine Deduplizierung erforderlich ist, können wir direkt „Union All“ verwenden, da die Verwendung von „Union“ eine Deduplizierung erfordert, die die SQL-Leistung beeinträchtigt.

Die optimierten Anweisungen lauten wie folgt:

<span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> employee emp <span class="hljs-keyword">on</span> cl.ref_table = <span class="hljs-string">'Employee'</span> <span class="hljs-keyword">and</span> cl.ref_oid = emp.id <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span> <span class="hljs-keyword">and</span> emp.is_deleted = <span class="hljs-number">0</span> <span class="hljs-keyword">union</span><span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> emp_certificate ec <span class="hljs-keyword">on</span> cl.ref_table = <span class="hljs-string">'EmpCertificate'</span> <span class="hljs-keyword">and</span> cl.ref_oid = ec.id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> employee emp <span class="hljs-keyword">on</span> emp.id = ec.emp_id <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span> <span class="hljs-keyword">and</span> emp.is_deleted = <span class="hljs-number">0</span>

Sie müssen das Geschäftsszenario nicht verstehen, Sie müssen lediglich dafür sorgen, dass die Ergebnisse der transformierten Anweisungen mit denen vor der Transformation konsistent bleiben.

Vorhandene Indizes können diese Anforderung erfüllen, und es ist keine Indexerstellung erforderlich

Das Experimentieren mit der geänderten Anweisung dauert nur 10 ms, was fast 200-mal schneller ist!

+<Spon-Class = " | idx_last_upd | Last_upd | EQ_REF | +--------+---------------------------------+-------------------+---------+-----------------------+------+-----------------+</span> 53 Zeilen in <span class = "hljs-keyword"> set </span> (<span class = "hljs-number"> 0.01 </span> sence)

Anwendungsszenarien identifizieren

Der Zweck dieses Beispiels besteht darin, unser Verständnis der Unterscheidbarkeit von Spalten umzukehren. Generell glauben wir, dass es umso einfacher ist, weniger Datensätze zu sperren, je unterscheidbarer eine Spalte ist. In einigen Sonderfällen hat diese Theorie jedoch Einschränkungen.

<span class="hljs-keyword">select</span> * <span class="hljs-keyword">von</span> stage_poi sp <span class="hljs-keyword">wobei</span> sp.accurate_result=<span class="hljs-number">1</span> <span class="hljs-keyword">und</span> ( sp.sync_status=<span class="hljs-number">0</span> <span class="hljs-keyword">oder</span> sp.sync_status=<span class="hljs-number">2</span> <span class="hljs-keyword">oder</span> sp.sync_status=<span class="hljs-number">4</span> );

Mal sehen, wie lange die Ausführung dauert. 6,22 Sekunden für 951 Daten, das ist wirklich langsam.

951 Zeilen im <span class="hljs-keyword">Satz</span> (<span class="hljs-number">6,22</span> Sek.)

Erklären Sie zunächst: Die Anzahl der Zeilen erreicht 3,61 Millionen und Typ = ALL zeigt einen vollständigen Tabellenscan an.

+<span class="hljs-comment">----+----------+-------+-----------+---------------+---------+---------+------+---------+---------+---------+</span>| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref | Zeilen | Extra |+<span class="hljs-comment">----+-----------+------+------+--------------+-----+---------+------+---------+---------+</span>| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | NULL | 3613155 | Verwenden von „where“ |+<span class="hljs-comment">----+----------+---+----------+-------------+------+---------+---------+---------+---------+---------+</span>

Alle Felder werden abgefragt, um die Anzahl der Datensätze zurückzugeben. Da es sich um eine einzelne Tabellenabfrage handelt, wurden 951 Datensätze erstellt.

Sorgen Sie dafür, dass die Anzahl der erklärten Zeilen möglichst nahe bei 951 liegt.

Sehen Sie sich die Anzahl der Datensätze an, bei denen „accuracy_result“ = 1 ist:

<span class="hljs-keyword">Auswählen</span> <span class="hljs-keyword">Anzahl</span>(*), genaues_Ergebnis <span class="hljs-keyword">von</span> Stage-POI <span class="hljs-keyword">Gruppieren</span> <span class="hljs-keyword">nach</span> genaues_Ergebnis;+<span class="hljs-comment">----------+----------------+</span>| Anzahl(*) | genaues_Ergebnis |+<span class="hljs-comment">----------+----------------+</span>| 1023 | -1 || 2114655 | 0 || 972815 | 1 |+<span class="hljs-comment">----------+----------------+</span>

Wir können sehen, dass das Feld accuracy_result eine sehr geringe Unterscheidungsfähigkeit aufweist. Die gesamte Tabelle hat nur drei Werte: -1, 0 und 1. Selbst mit dem Index ist es nicht möglich, eine besonders kleine Datenmenge zu sperren.

Werfen wir einen Blick auf das Feld sync_status:

<span class="hljs-keyword">Auswählen</span> <span class="hljs-keyword">Anzahl</span>(*), Synchronisierungsstatus <span class="hljs-keyword">von</span> Stage-POI <span class="hljs-keyword">Gruppieren</span> <span class="hljs-keyword">nach</span> Synchronisierungsstatus;+<span class="hljs-comment">----------+-------------+</span>| Anzahl(*) | Synchronisierungsstatus |+<span class="hljs-comment">----------+-------------+</span>| 3080 | 0 || 3085413 | 3 |+<span class="hljs-comment">----------+-------------+</span>

Die Unterscheidungsfähigkeit ist ebenfalls sehr gering und theoretisch nicht für die Indizierung geeignet.

Nach der Analyse des Problems bis zu diesem Punkt scheinen wir zu dem Schluss gekommen zu sein, dass diese Tabelle nicht optimiert werden kann. Der Unterschied zwischen den beiden Spalten ist sehr gering. Selbst mit dem Hinzufügen eines Indexes kann sie sich nur an diese Situation anpassen und es ist schwierig, eine allgemeine Optimierung vorzunehmen. Wenn beispielsweise sync_status 0 und 3 gleichmäßig verteilt sind, liegt die Anzahl der gesperrten Datensätze ebenfalls im Millionenbereich.

Kommunizieren Sie mit der Geschäftsseite und schauen Sie sich die Nutzungsszenarien an. So verwendet die Geschäftsseite diese SQL-Anweisung. Sie scannt alle fünf Minuten die qualifizierten Daten und ändert das Feld sync_status nach der Verarbeitung auf 1. Die Anzahl der qualifizierten Datensätze in fünf Minuten ist nicht zu groß, etwa 1.000. Nachdem Sie die Nutzungsszenarien der Geschäftsseite verstanden haben, wird die Optimierung dieses SQL einfach, da die Geschäftsseite das Ungleichgewicht der Daten sicherstellt. Wenn ein Index hinzugefügt wird, können die meisten unnötigen Daten herausgefiltert werden.

Verwenden Sie gemäß den Indexerstellungsregeln die folgende Anweisung, um einen Index zu erstellen

<span class="hljs-keyword">ändern</span> <span class="hljs-keyword">Tabelle</span> stage_poi <span class="hljs-keyword">hinzufügen</span> <span class="hljs-keyword">Index</span> idx_acc_status(accurate_result,sync_status);

Bei der Beobachtung der erwarteten Ergebnisse stellten wir fest, dass es nur 200 ms dauert, was mehr als 30-mal schneller ist.

952 Zeilen im <span class="hljs-keyword">Satz</span> (<span class="hljs-number">0,20</span> Sek.)

Lassen Sie uns den Prozess der Problemanalyse noch einmal durchgehen. Abfragen für einzelne Tabellen sind relativ einfach zu optimieren. Meistens müssen Sie den Feldern in der Where-Bedingung nur gemäß den Regeln Indizes hinzufügen. Wenn dies nur eine „hirnlose“ Optimierung ist, werden auch einige Spalten mit sehr geringer Differenzierung und Spalten, die nicht indiziert werden sollten, indiziert, was erhebliche Auswirkungen auf die Einfüge- und Aktualisierungsleistung hat und sich auch auf andere Abfrageanweisungen auswirken kann. Daher ist das Anwendungsszenario unseres vierten Schritts zum Debuggen von SQL sehr kritisch. Nur wenn wir dieses Geschäftsszenario kennen, können wir Abfrageanweisungen besser analysieren und optimieren.

Anweisungen, die nicht optimiert werden können

<span class="hljs-keyword">select</span> c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) <span class="hljs-keyword">as</span> created_time, from_unixtime(c.last_modified) <span class="hljs-keyword">as</span> last_modified, c.last_modified_user_id <span class="hljs-keyword">from</span> contact c <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> contact_branch cb <span class="hljs-keyword">on</span> c.id = cb.contact_id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> branch_user bu <span class="hljs-keyword">on</span> cb.branch_id = bu.branch_id <span class="hljs-keyword">and</span> bu.status <span class="hljs-keyword">in</span> ( <span class="hljs-number">1</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> org_emp_info oei <span class="hljs-keyword">on</span> oei.data_id = bu.user_id <span class="hljs-keyword">and</span> oei.node_left >= <span class="hljs-number">2875</span> <span class="hljs-keyword">and&llt;/span> oei.node_right <= <span class="hljs-number">10802</span> <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> c.created_time <span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">0</span> , <span class="hljs-number">10</span>;

Noch ein paar Schritte.

Sehen wir uns zunächst an, wie lange die Ausführung der Anweisung dauert. Für 10 Datensätze dauerte es 13 Sekunden, was unerträglich ist.

10 Zeilen im <span class="hljs-keyword">Satz</span> (<span class="hljs-number">13,06</span> Sek.)

erklären

+<span class = "hljs-coment"> ----+------------- | Category_Left_Right, IDX_CATIVE, DIESEM _ID | 108 |

Ausgehend vom Ausführungsplan prüft MySQL zunächst die Tabelle org_emp_info, um 8849 Datensätze zu scannen, verwendet dann den Index idx_userid_status, um eine Verknüpfung mit der Tabelle branch_user herzustellen, verwendet dann den Index idx_branch_id, um eine Verknüpfung mit der Tabelle contact_branch herzustellen, und stellt schließlich unter Verwendung des Primärschlüssels eine Verknüpfung mit der Tabelle contact her.

Die Anzahl der zurückgegebenen Zeilen ist sehr gering und es sind keine Anomalien erkennbar. Wenn wir uns die Anweisung ansehen, stellen wir fest, dass am Ende eine Order by + Limit-Kombination vorhanden ist. Könnte es sein, dass der Sortierungsumfang zu groß ist? Also vereinfachen wir das SQL, entfernen Order By und Limit am Ende und schauen, wie viele Datensätze tatsächlich zum Sortieren verwendet werden.

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">from</span> contact c <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> contact_branch cb <span class="hljs-keyword">on</span> c.id = cb.contact_id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> branch_user bu <span class="hljs-keyword">on</span> cb.branch_id = bu.branch_id <span class="hljs-keyword">and</span> bu.status <span class="hljs-keyword">in</span> ( <span class="hljs-number">1</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> org_emp_info oei <span class="hljs-keyword">on</span> oei.data_id = bu.user_id <span class="hljs-keyword">and</span> oei.node_left >= <span class="hljs-number">2875</span> <span class="hljs-keyword">and</span> oei.node_right <= <span class="hljs-number">10802</span> <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span> +<span class="hljs-comment">----------+</span>| <span class="hljs-keyword">count</span>(*) |+<span class="hljs-comment">----------+</span>| <span class="hljs-number">778878</span> |+<span class="hljs-comment">----------+</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">5.19</span> sec)

Es wurde festgestellt, dass 778.878 Datensätze vor dem Sortieren gesperrt wurden. Wenn wir den Ergebnissatz von 700.000 sortieren, wäre das katastrophal. Kein Wunder, dass es so langsam ist. Können wir unsere Denkweise ändern und zuerst nach der Erstellungszeit des Kontakts sortieren und ihn dann verbinden? Wird das schneller sein?

Es wird daher in die folgende Anweisung umgewandelt, die auch mit straight_join optimiert werden kann:

Wählen Sie C.Id, C.Name, C.Position, C.Sex, C.phone, C.office_phone, C.Feature_info, C. Birthday, C.Creator_id, C.is_keyPerson, C.GiveUp_reason, C.Status, C.Data_Source, von C.Created_time, C.Modifydytimed) _idfrom contact c where existiert (select 1 aus contact_branch cbinner joong_user buon cb.branch_id = bu.branch_idand bu.status in (1, 2) Inner Join org_emp_info oeion OEI.Data_ID = bu.user_Idand OEI.Node_LEIDE_LEIDE_LEIDE_LEIDE_LEIDE_LEIDE_LEIDE_LEIDE_LEIDE_LEIDLE ATEGORY = - 1WO C.ID = CB.CONTACT_ID) Bestellung von C.Created_time Desc Limit 0, 10;

Überprüfen Sie den erwarteten Effekt

Innerhalb von <span class = "hljs-number"> 1 </span> ms erhöhte es um mehr als <span class = "hljs-number"> 13000 </span> mal! sql <span class = "hljs-number"> 10 </span> Zeilen <span class = "hljs-keyword"> in </span> <span class = "hljs-keyword"> set </span> (<span class = "hljs-number"> 0.00 </span> Sek.

Wir dachten, wir waren damit fertig, aber wir haben in der vorherigen Analyse ein Detail übersehen. Der allgemeine Ausführungsprozess ist: MySQL Sortiert nach Index, um die ersten 10 Datensätze zu erhalten, und filtert dann, wenn es weniger als 10 Datensätze gibt.

Testen Sie SQL mit unterschiedlichen Parametern:

<span class = "hljs-keyword"> auswählen </span> -Keeword "> als </span> erstellt_time, aus_unixtime (c.last_modified) <span class =" hljs-keyword "> als </span> last_modified, c.last_modified_user_id class =" hljsword "> aus </span> span conact c <span classe =" hljs-kloses. " > (<span class = "hljs-keyword"> select </span> <span class = "hljs-number"> 1 </span> <span class = "hljs-keyword"> aus </span> contact_branch cb cls = "hlJs-word"> Innere </span> <span klasse = "hljs-kewword"> joon </span> span> hljs-kewword "> joon </span> span> span klasse =" hljs-kewword " /span> cb.branch_id = bU.branch_id <span class = "hljs-keyword"> und </span> bufatus <span class = "hljs-keyword"> in </span> (<spann klassifiziert = "hljs-number">, </span klasse = "hljs-number"> 2 </span>) </span> </span> span-Span-number ">"> 2 </span> " HlJS-Keeword "> Join </span> org_emp_info oei <span class =" hljs-keword "> on </span> oei.data_id = buf.user_id <span class =" hljs-word "> und </span> oei > und </span> oei.node_right <= <span class = "hljs-number"> 2875 </span> <span class = "hljs-keyword"> und </span> oEI.org_category =-<span class = "hljs-number"> 1 </span> <span> <span) </span> span) hljs-keyword " S-Keyword "> Bestellung </span> <span class =" hljs-keyword "durch </span> created_time <span class =" hljs-keword "> desc </span> <span class =" hljs-keyword " "> set </span> (<span class =" hljs-number "> 2 </span> <span class =" hljs-keyword "> min </span> <span class =" hljs-number "> 18.99 </span> Sek.)

2 min 18.99 Sek.! Es ist viel schlimmer als zuvor. Aufgrund des verschachtelten Schleifenmechanismus von MySQL ist es in dieser Situation im Grunde unmöglich, zu optimieren. Letztendlich kann diese Anweisung nur an das Anwendungssystem übergeben werden, um ihre eigene Logik zu optimieren. Aus diesem Beispiel können wir feststellen, dass nicht alle Aussagen optimiert werden können. Erwarten Sie daher nicht, dass alle Aussagen über SQL optimiert werden können.

Dies ist das Ende der Analyse von langsamen Abfragenfällen. Während des Optimierungsprozesses haben wir mehr als 1.000 Zeilen von "Junk SQL" mit 16 Tischanschlüssen begegnet. Unabhängig davon, wie viele Fälle es gibt, sind sie nur die Ansammlung von Erfahrung.

In diesem Artikel wird ein langsamer Abfragefall verwendet, um die MySQL -Indexprinzipien und einige Methoden zur Optimierung langsamer Abfragen einzuführen. Nachdem ich so lange an der Optimierung der Anweisung gearbeitet habe, stellte ich fest, dass jede Optimierung auf Datenbankebene nicht so gut ist wie die Optimierung des Anwendungssystems. Um kürzlich ein beliebtes Sprichwort zu paraphrasieren: "Abfragen ist einfach, optimieren ist nicht, also schreiben und schätzen Sie es!"