Detaillierte Einführung in die MySQL-Indizierung und Abfrageoptimierung

Dieser Artikel bietet Ihnen eine detaillierte Einführung in die MySQL-Indizierung und die Abfrageoptimierung. Ich hoffe, dass er für Freunde hilfreich ist.

Der Artikel „MySQL-Abfrageanalyse“ beschreibt die Methode zur Verwendung des langsamen MySQL-Abfrage- und Erklärungsbefehls zum Auffinden von MySQL-Leistungsengpässen. Nachdem Sie die SQL-Anweisungen mit Leistungsengpässen lokalisiert haben, müssen Sie die ineffizienten SQL-Anweisungen analysieren. Optimierung. In diesem Artikel werden hauptsächlich die MySQL-Indexprinzipien und die häufig verwendete SQL-Abfrageoptimierung erläutert.

Ein einfacher Vergleichstest

Im vorherigen Fall hat die Tabelle c2c_zwdb.t_file_count nur eine automatisch inkrementierende ID und die Ausführung von SQL ohne Indizierung des FFileName-Felds lautet wie folgt:

Im obigen Bild geben Sie „type=all“, „key=null“, „rows=33777“ ein. Dieses SQL verwendet keine Indizes und ist ein sehr ineffizienter vollständiger Tabellenscan. Wenn gemeinsame Abfragen und andere Einschränkungen hinzugefügt werden, verbraucht die Datenbank wahnsinnig viel Speicher und beeinträchtigt die Ausführung des Front-End-Programms.

Fügen Sie nun einen Index zum FFileName-Feld hinzu:

alter table c2c_zwdb.t_file_count add index index_title(FFileName);

Führen Sie die obige Abfrageanweisung erneut aus. Der Kontrast ist offensichtlich :

In dieser Abbildung: type=ref, key=index name (index_title), rows=1. Dieses SQL verwendet den Index index_title und es handelt sich um einen konstanten Scan. Basierend auf dem Index wird nur eine Zeile gescannt.

Verglichen mit der Situation ohne Indizierung ist der Kontrast der Abfrageeffizienz nach dem Hinzufügen eines Index sehr offensichtlich.

MySQL-Index

Wie aus dem obigen Vergleichstest hervorgeht, ist der Index der Schlüssel zur schnellen Suche. Die Einrichtung eines MySQL-Index ist für den effizienten Betrieb von MySQL sehr wichtig. Bei einer kleinen Datenmenge sind die Auswirkungen, wenn kein geeigneter Index vorhanden ist, nicht groß, aber mit zunehmender Datenmenge nimmt die Leistung stark ab. Wenn mehrere Spalten indiziert werden (kombinierter Index), ist die Reihenfolge der Spalten sehr wichtig und MySQL kann nur effektive Suchvorgänge für das Präfix ganz links im Index durchführen.

Im Folgenden werden einige gängige MySQL-Indextypen vorgestellt.

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

1. MySQL-Indextyp

(1) Primärschlüsselindex PRIMARY KEY

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

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

(2) Eindeutiger Index UNIQUE

Der Wert der eindeutigen Indexspalte muss eindeutig sein, es sind jedoch Nullwerte zulässig. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Sie können es beim Erstellen der Tabelle angeben oder die Tabellenstruktur ändern, z. B.:

ALTER TABLE table_name ADD UNIQUE (column)

(3) Gewöhnlicher Index INDEX

Dies ist der einfachste Index, er unterliegt keinen Einschränkungen. Sie können es beim Erstellen der Tabelle angeben oder die Tabellenstruktur ändern, z. B.:

ALTER TABLE table_name ADD INDEX index_name (column)

(4 ) Kombinierter Index INDEX

Kombinierter Index, das heißt, ein Index enthält mehrere Spalten. Sie können es beim Erstellen der Tabelle angeben oder die Tabellenstruktur ändern, z. B.:

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) Volltextindex VOLLTEXT

Der Volltextindex (auch Volltextabfrage genannt) ist eine Schlüsseltechnologie, die derzeit von Suchmaschinen verwendet wird. Es kann verschiedene Algorithmen wie die Wortsegmentierungstechnologie verwenden, um die Häufigkeit und Wichtigkeit von Schlüsselwörtern im Text intelligent zu analysieren und dann die gewünschten Suchergebnisse gemäß bestimmten Algorithmusregeln intelligent herauszufiltern.

kann beim Erstellen der Tabelle angegeben werden, oder die Tabellenstruktur kann geändert werden, wie zum Beispiel:

ALTER TABLE table_name ADD FULLTEXT (column)

2 . Indexstruktur und -prinzip

B+Tree wird häufig als Index in MySQL verwendet, die Implementierung unterscheidet sich jedoch je nach Clustered-Index und Nicht-Clustered-Index. Dieser Artikel wird vorerst nicht behandelt.

Einführung in B+-Bäume

Das Bild des B+-Baums unten ist an vielen Stellen zu sehen. Der Grund, warum ich dieses Bild hier ausgewählt habe, ist, dass ich dieses Bild finde Es kann den Indexsuchprozess sehr gut erklären.

Wie oben gezeigt, handelt es sich um einen b+-Baum. Der hellblaue Block wird als Festplattenblock bezeichnet. Sie können sehen, dass jeder Festplattenblock mehrere Datenelemente (in Dunkelblau dargestellt) und Zeiger (in Gelb dargestellt) enthält. Festplattenblock 1 enthält beispielsweise die Datenelemente 17 und 35. Enthält Zeiger P1, P2 und P3. P1 steht für Plattenblöcke mit weniger als 17, P2 für Plattenblöcke zwischen 17 und 35 und P3 für Plattenblöcke mit mehr als 35.

Die tatsächlichen Daten liegen in den Blattknoten vor, nämlich 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Nicht-Blattknoten speichern keine echten Daten, sondern nur Datenelemente, die die Suchrichtung bestimmen. Beispielsweise sind 17 und 35 tatsächlich nicht in der Datentabelle vorhanden.

Suchvorgang

Wenn Sie im Bild oben das Datenelement 29 finden möchten, wird zuerst Festplattenblock 1 von der Festplatte in den Speicher geladen und Zu diesem Zeitpunkt tritt eine E/A auf. Stellen Sie mithilfe der binären Suche im Speicher fest, dass 29 zwischen 17 und 35 liegt, und sperren Sie den P2-Zeiger von Plattenblock 1. Die Speicherzeit ist vernachlässigbar, da sie sehr kurz ist (im Vergleich zur E/A der Festplatte). ) und übergeben Sie die Plattenadresse des P2-Zeigers von Plattenblock 1. Laden Sie Plattenblock 3 von der Platte in den Speicher. Der zweite E/A-Vorgang erfolgt. 29 liegt zwischen 26 und 30. Sperren Sie den P2-Zeiger von Plattenblock 3 und laden Sie Plattenblock 8 hinein Durch den Speicher durch den Zeiger tritt der dritte E/A auf, und gleichzeitig wird im Speicher eine binäre Suche durchgeführt, um 29 zu finden und die Abfrage zu beenden, insgesamt drei E/A. Die reale Situation ist, dass ein dreischichtiger B+-Baum Millionen von Daten darstellen kann. Wenn für Millionen von Datensuchen nur drei E/As erforderlich sind, ist die Leistungsverbesserung enorm. Wenn kein Index vorhanden ist, muss jedes Datenelement ein E/A haben. Dann sind insgesamt Millionen von IOs erforderlich, und die Kosten sind natürlich sehr, sehr hoch.

Eigenschaften

(1) Das Indexfeld sollte so klein wie möglich sein.

Aus dem Suchprozess des obigen b+-Baums oder aus der Tatsache, dass echte Daten in Blattknoten vorhanden sind, können wir erkennen, dass die Anzahl der IOs von der Höhe h der b+-Zahl abhängt.

Angenommen, das Datenvolumen der aktuellen Datentabelle beträgt N und die Anzahl der Datenelemente in jedem Plattenblock beträgt m, dann ist die Baumhöhe h=㏒(m+1)N, wenn das Datenvolumen beträgt N ist konstant. Je größer m ist, desto kleiner ist h. Und m = die Größe des Plattenblocks/die Größe des Datenelements. Die Größe des Plattenblocks entspricht der Größe einer Datenseite , was feststeht; wenn der vom Datenelement eingenommene Platz größer ist, ist die Höhe h des Baums umso geringer, je größer die Anzahl m der Datenelemente ist. Aus diesem Grund muss jedes Datenelement, also das Indexfeld, so klein wie möglich sein. Beispielsweise belegt int 4 Bytes, also die Hälfte weniger als bigint 8 Bytes.

(2) Das am weitesten links stehende Übereinstimmungsmerkmal des Index.

Wenn es sich bei den Datenelementen des b+-Baums um zusammengesetzte Datenstrukturen handelt, z. B. (Name, Alter, Geschlecht), wird die b+-Nummer verwendet, um den Suchbaum in der Reihenfolge von links nach rechts zu erstellen, z. B. wenn ( Zhang San ,20,F) Beim Abrufen solcher Daten vergleicht der B+-Baum den Namen vorrangig, um die nächste Suchrichtung zu bestimmen. Wenn die Namen gleich sind, vergleichen Sie nacheinander Alter und Geschlecht und erhalten Sie schließlich den Abruf Daten; wenn (20,F) Wenn Daten ohne Namen eingehen, weiß der B+-Baum nicht, welcher Knoten als nächstes überprüft werden soll, da der Name beim Erstellen des Suchbaums der erste Vergleichsfaktor ist und Sie zuerst anhand des Namens suchen müssen, um zu wissen, wo als nächstes gehen. Wenn beispielsweise Daten wie (Zhang San, F) abgerufen werden, kann der B+-Baum den Namen verwenden, um die Suchrichtung anzugeben, aber das nächste Feldalter fehlt, sodass nur alle Daten gefunden werden können, deren Name Zhang San entspricht. und dann das Geschlecht abgleichen. Dies sind die Daten von F. Dies ist eine sehr wichtige Eigenschaft, nämlich das am weitesten links liegende Übereinstimmungsmerkmal des Index.

Mehrere Prinzipien der Indexerstellung

(1) Prinzip des Präfixabgleichs ganz links

Beginnen Sie bei mehrspaltigen Indizes immer am Anfang des Index Das Feld beginnt und geht weiter, und die Mitte kann nicht übersprungen werden. Wenn Sie beispielsweise einen mehrspaltigen Index erstellen (Name, Alter, Geschlecht), wird zuerst das Feld „Name“, dann das Feld „Alter“ und dann das Feld „Geschlecht“ abgeglichen. Die Mitte kann nicht übersprungen werden. MySQL führt den Abgleich nach rechts fort, bis es auf eine Bereichsabfrage (>,

Im Allgemeinen wird beim Erstellen eines mehrspaltigen Index die am häufigsten verwendete Spalte in der where-Klausel ganz links platziert.

Sehen Sie sich ein Vergleichsbeispiel an, in dem das Komplement dem Prinzip der Übereinstimmung von Präfixen ganz links entspricht und diesem Prinzip entspricht.

Beispiel: Tabelle c2c_db.t_credit_detail hat einen Index (

,

)FlistidFbank_listid

stimmt nicht mit dem Index ganz links überein Präfix-SQL-Anweisung des Matching-Prinzips:

select * from t_credit_detail where Fbank_listid='201108010000199'G

Diese SQL verwendet direkt das zweite Indexfeld Fbank_listid und überspringt das erste Indexfeld Flistid, was nicht konform ist mit dem Präfix-Matching-Prinzip ganz links. Verwenden Sie den Befehl EXPLAIN, um den Ausführungsplan der SQL-Anweisung anzuzeigen, wie unten gezeigt:

Wie aus der obigen Abbildung ersichtlich ist, ist die SQL verwendet keine Indizes und ist ein vollständiger Tabellenscan mit geringer Effizienz.

SQL-Anweisung, die dem Präfix-Matching-Prinzip ganz links entspricht:

select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'G

Diese SQL verwendet zuerst das erste Feld Flistid des Index und dann das zweite Feld Fbank_listid des Index. In der Mitte wird nicht übersprungen, was dem Prinzip der Präfixübereinstimmung ganz links entspricht. Verwenden Sie den Befehl EXPLAIN, um den Ausführungsplan der SQL-Anweisung anzuzeigen, wie unten gezeigt:

Wie aus der obigen Abbildung ersichtlich ist, ist die SQL verwendet Indizes und scannt nur eine Zeile.

Der Vergleich zeigt, dass die Effizienz von SQL-Anweisungen, die dem Prinzip der Übereinstimmung von Präfixen ganz links entsprechen, im Vergleich zu SQL-Anweisungen, die diesem Prinzip nicht entsprechen, vom vollständigen Tabellenscan bis zum konstanten Scan erheblich verbessert ist.

(2) Versuchen Sie, Spalten mit hoher Differenzierung als Indizes auszuwählen.

Zum Beispiel wählen wir als Index die Matrikelnummer, nicht aber das Geschlecht.

(3) = und in kann außer Betrieb sein

Zum Beispiel a = 1 und b = 2 und c = 3, der (a, b, c)-Index kann erstellt werden in beliebiger Reihenfolge, MySQL-Abfrage Der Optimierer hilft Ihnen dabei, ihn in eine Form zu optimieren, die der Index erkennen kann.

(4) Indexspalten können nicht an Berechnungen teilnehmen, halten Sie die Spalten „sauber“

Zum Beispiel: Flistid+1>'2000000608201108010831508721'. Der Grund ist sehr einfach. Wenn die Indexspalte an der Berechnung beteiligt ist, wird der Index bei jedem Abruf einmal berechnet und dann verglichen.

(5) Erweitern Sie den Index so weit wie möglich und erstellen Sie keinen neuen Index.

Zum Beispiel gibt es bereits einen Index von a in der Tabelle und Sie möchten nun einen Index von (a, b) hinzufügen, dann müssen Sie nur den ursprünglichen Index ändern.

Nachteile von Indizes

Obwohl Indizes die Abfrageeffizienz verbessern können, weisen Indizes auch ihre eigenen Mängel auf.

Zusätzlicher Overhead des Index:

(1) Platz: Der Index muss Platz beanspruchen

(2) Zeit: Das Abfragen des Index nimmt Zeit in Anspruch; (3) Wartung: Indizes müssen gepflegt werden (wenn sich Daten ändern);

Es wird nicht empfohlen, Indizes zu verwenden:

(1) Tabellen mit geringem Datenvolumen

(2) Der Platz ist knapp

Zusammenfassung häufig verwendeter Optimierungen

Es gibt viele Optimierungsanweisungen und es gibt viele Dinge, auf die geachtet werden muss. Hier sind einige Punkte, die auf dem Üblichen basieren Situation:

1. Es gibt einen Index, der jedoch nicht verwendet wird (nicht empfohlen)

(1) Wenn der Like-Parameter mit einem Platzhalterzeichen beginnt

Versuchen Sie es Vermeiden Sie, dass der Like-Parameter mit einem Platzhalterzeichen beginnt, da die Datenbank-Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt.

SQL-Anweisungen, die mit Platzhaltern beginnen, zum Beispiel: select * from t_credit_detail where Flistid like '%0'G

Das ist Der vollständige Tabellenscan verwendet keine Indizes und wird nicht empfohlen.

SQL-Anweisungen, die nicht mit Platzhaltern beginnen, zum Beispiel: select * from t_credit_detail where Flistid like '2%'G

sehr offensichtlich wird dabei ein Index verwendet, der eine Bereichssuche darstellt und viel effizienter ist als SQL-Anweisungen, die mit Platzhaltern beginnen.

(2) Wenn die Where-Bedingung nicht dem Prinzip des Präfixes ganz links entspricht

Im Inhalt des Prinzips der Übereinstimmung des Präfixes ganz links wurden Beispiele gegeben.

(3) Benutzen! Vermeiden Sie die Verwendung von

, wenn Sie die Operatoren = oder verwenden! = oder -Operator, andernfalls gibt die Datenbank-Engine die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch. Es ist effizienter, > zu verwenden.

select * from t_credit_detail where Flistid != '2000000608201108010831508721'G

(4) Indexspalten nehmen an der Berechnung teil

Sie sollten versuchen, Ausdrucksoperationen für Felder in der where-Klausel zu vermeiden, da dies dazu führen würde, dass die Engine die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt.

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'G

(5) Führen Sie eine Nullwertbeurteilung für Felder durch

Sie sollten versuchen, Nullwerturteile über Felder in der where-Klausel zu vermeiden, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt, wie zum Beispiel: Ineffizienz: Wählen Sie * aus t_credit_detail aus, wo sich Flistid befindet null ;

Sie können den Standardwert 0 für Flistid festlegen, sicherstellen, dass in der Spalte Flistid in der Tabelle kein Nullwert vorhanden ist, und dann eine Abfrage wie folgt durchführen: Effizient: Wählen Sie * aus t_credit_detail aus, wobei Flistid =0;

(6) Verwenden Sie oder, um Bedingungen zu verbinden.

sollten Sie die Verwendung von oder in der where-Klausel zum Verbinden von Bedingungen vermeiden, da die Engine andernfalls die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt. Zum Beispiel: Ineffizienz: Wählen Sie * aus t_credit_detail aus, wobei Flistid = '2000000608201108010831508721' oder Flistid = '10000200001';

Sie können die folgende Abfrage verwenden, um die obige Abfrage zu ersetzen: Effizient: Wählen Sie

aus t_credit_detail aus, wobei Flistid = '2000000608201108010831508721' Union All Select

from t_credit_detail where Flistid = ' 10000200001';

2. Vermeiden Sie die Auswahl von *

Während des Analysevorgangs wird „*“ nacheinander in alle Spaltennamen umgewandelt. Dies funktioniert erfolgt durch Abfrage des Datenwörterbuchs, was bedeutet, dass es mehr Zeit in Anspruch nimmt.

Sie sollten sich also angewöhnen, alles zu sich zu nehmen, was Sie brauchen.

3. Optimierung der Order by-Anweisung

Alle Nicht-Indexelemente oder Berechnungsausdrücke in der Order by-Anweisung verlangsamen die Abfragegeschwindigkeit.

Methode: 1. Schreiben Sie die Reihenfolge nach Anweisung um, um den Index zu verwenden

  2.为所使用的列建立另外一个索引

  3.绝对避免在order by子句中使用表达式。

4. Optimierung der GROUP BY-Anweisung

Verbessern Sie die Effizienz der GROUP BY-Anweisung, indem Sie unnötige Elemente entfernen Datensätze werden vor der GRUPPE NACH

Ineffizienz herausgefiltert:

JOB AUSWÄHLEN, AVG(SAL)

FROM EMP

GRUPPE NACH JOB

JOB HABEN = 'PRÄSIDENT'

ODER JOB = 'MANAGER'

Effizient:

JOB AUSWÄHLEN, AVG(SAL)

VON EMP

WO JOB = 'PRÄSIDENT'

ODER JOB = 'MANAGER'

GRUPPE nach JOB

5. Verwendung vorhanden statt in

Oft ist es eine gute Wahl, „exists“ anstelle von „in“ zu verwenden: select num from a where num in(select num from b) Ersetzen Sie es durch die folgende Anweisung: select num from a where exist(select 1 from b where num=a.num )

6. Verwenden Sie varchar/nvarchar anstelle von char/nchar

Verwenden Sie so oft wie möglich varchar/nvarchar anstelle von char/nchar, da erstens der Speicherplatz von Feldern variabler Länge ist klein, was Speicherplatz sparen kann. Zweitens ist die Suche in einem relativ kleinen Feld offensichtlich effizienter.

7. Wenn Sie DISTINCT verwenden können, benötigen Sie kein GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice >

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

8 Wenn Sie UNION ALL verwenden können, verwenden Sie nicht UNION

UNION ALL führt SELECT DISTINCT nicht aus Funktion, die viele unnötige Ressourcen reduziert.

9. Verwenden Sie beim Zusammenführen von Tabellen äquivalente Beispieltypen und indizieren Sie sie.

Wenn die Anwendung viele JOIN-Abfragen hat, sollten Sie bestätigen, dass die Join-Felder in den beiden Tabellen indiziert erstellt werden. Auf diese Weise startet MySQL einen Mechanismus zur Optimierung der Join-SQL-Anweisung für Sie.

Außerdem sollten die für Join verwendeten Felder vom gleichen Typ sein. Beispiel: Wenn Sie ein DECIMAL-Feld mit einem INT-Feld verbinden, kann MySQL deren Indizes nicht verwenden. Für diese STRING-Typen müssen sie außerdem denselben Zeichensatz haben. (Die Zeichensätze der beiden Tabellen können unterschiedlich sein)

Dieser Artikel endet hier. Weitere Informationen zu MySQL finden Sie in der Spalte

MySQL-Tutorial

auf der chinesischen PHP-Website! ! !

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in die MySQL-Indizierung und Abfrageoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!