Detaillierte Erläuterung der MySQL-Abfrageoptimierung

Dieser Artikel gibt Ihnen hauptsächlich eine ausführliche Erklärung zur MySQL-Abfrageoptimierung. Ich hoffe, er kann Ihnen helfen.

Die beliebtesten SQL-Optimierungsmethoden im Internet sind wie folgt:

1. Vermeiden Sie die Verwendung in der where-Klausel! = oder <>-Operator, andernfalls gibt die Engine die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch.

2. Um die Abfrage zu optimieren, sollten Sie zunächst in Betracht ziehen, Indizes für die beteiligten Spalten zu erstellen.

3. Vermeiden Sie es, den Nullwert des Felds in der where-Klausel zu beurteilen, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt, z. B.:
select id from t where num is null
OK Legen Sie den Standardwert 0 für „num“ fest, stellen Sie sicher, dass in der Spalte „num“ der Tabelle kein Nullwert vorhanden ist, und fragen Sie dann wie folgt ab:
select id from t where num=0
4. Versuchen Sie es Um die Verwendung von oder in der where-Klausel zum Verbinden von Bedingungen zu vermeiden, gibt die Engine die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch, z. B.:
select id from t where num=10 or num=20
Sie können eine Abfrage wie folgt durchführen:
select id from t where num=10
union all
select id from t where num=20
5. Die folgende Abfrage führt auch zu einem vollständigen Tabellenscan: (kann nicht vor dem Prozentzeichen stehen)
Wählen Sie eine ID aus t aus, wobei der Name etwa „?c %“ lautet.
Um die Effizienz zu verbessern, können Sie eine Volltextsuche in Betracht ziehen.
6. In und nicht in sollten ebenfalls mit Vorsicht verwendet werden, da sonst ein vollständiger Tabellenscan durchgeführt wird, z. B.:
select id from t where num in(1,2,3)
For Kontinuierliche Werte können Sie verwenden. Nicht dazwischen verwenden:
select id from t where num between 1 and 3
7. Wenn Parameter in der where-Klausel verwendet werden, führt dies auch zu einem vollständigen Tabellenscan. Da SQL lokale Variablen nur zur Laufzeit auflöst, kann der Optimierer die Auswahl eines Zugriffsplans nicht bis zur Laufzeit verschieben, sondern muss die Auswahl zur Kompilierungszeit treffen. Wenn der Zugriffsplan jedoch zur Kompilierungszeit erstellt wird, sind die Werte der Variablen noch unbekannt und können nicht als Eingabe für die Indexauswahl verwendet werden. Die folgende Anweisung führt beispielsweise einen vollständigen Tabellenscan durch:
select id from t where num=@num
Sie können es ändern, um zu erzwingen, dass die Abfrage den Index verwendet:
select id from t with ( index (Indexname)) where num= @num
8. 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. Zum Beispiel:
Wählen Sie die ID aus t aus, wobei Anzahl/2=100 ist
sollte geändert werden in:
Wählen Sie die ID aus t aus, wobei Anzahl=100*2 ist
9. Versuchen Sie, die Paarung von Feldern in „Wo“ zu vermeiden Klausel Führen Sie eine Funktionsoperation aus, die dazu führt, dass die Engine die Verwendung des Index abbricht und einen vollständigen Tabellenscan durchführt. Zum Beispiel:
wähle id aus t, wobei substring(name,1,3)='abc'–Namens-ID beginnt mit abc
wähle id aus t, wobei datediff(day,createdate,'2005-11-30′ )=0–'2005-11-30′ generierte ID
sollte geändert werden in:
wähle die ID aus t aus, wobei der Name etwa „abc%“ lautet.
wähle die id aus t aus, wobei das Erstellungsdatum>='2005-11 ist -30′ und createdate<'2005-12-1′
10. Führen Sie keine Funktionen, arithmetische Operationen oder andere Ausdrucksoperationen auf der linken Seite von „=" in der where-Klausel aus, da das System sonst möglicherweise nicht dazu in der Lage ist Verwenden Sie den Index richtig.
11. Wenn Sie ein Indexfeld als Bedingung verwenden und der Index ein zusammengesetzter Index ist, muss das erste Feld im Index als Bedingung verwendet werden, um sicherzustellen, dass das System den Index verwendet. Andernfalls wird der Index nicht verwendet . und die Feldreihenfolge sollte so weit wie möglich mit der Indexreihenfolge übereinstimmen.
12. Schreiben Sie keine bedeutungslosen Abfragen, wenn Sie beispielsweise eine leere Tabellenstruktur generieren müssen:
Wählen Sie col1,col2 in #t aus t aus, wobei 1=0 ist.
Dieser Codetyp wird nicht verwendet Geben Sie eine beliebige Ergebnismenge zurück. Dies verbraucht jedoch Systemressourcen und sollte wie folgt geändert werden:
create table #t(...)
13. In vielen Fällen ist es eine gute Wahl, „exists“ anstelle von zu verwenden in:
select num from a where num in( select num from b)
Ersetzen Sie durch die folgende Anweisung:
select num from a where exist(select 1 from b where num=a.num)
14. Nicht alle Indizes sind für Abfragen gültig. Die SQL-Abfrageoptimierung basiert auf den Daten in der Tabelle. Wenn die Indexspalte eine große Menge doppelter Daten enthält, verwendet die SQL-Abfrage beispielsweise den Index nicht Es gibt ein Feld „Geschlecht“ in einer Tabelle, männlich und weiblich sind nahezu gleich, und selbst wenn das Feld auf „Geschlecht“ basiert, haben Indizes keinen Einfluss auf die Abfrageeffizienz.
15. Je mehr Indizes, desto besser. Obwohl der Index die Effizienz der entsprechenden Auswahl verbessern kann, verringert er auch die Effizienz des Einfügens und Aktualisierens, da der Index möglicherweise während des Einfügens oder Aktualisierens neu erstellt wird Der Index erfordert eine sorgfältige und fallweise Betrachtung. Es ist am besten, nicht mehr als 6 Indizes für eine Tabelle zu haben. Wenn es zu viele sind, sollten Sie überlegen, ob es notwendig ist, Indizes für einige Spalten zu erstellen, die nicht häufig verwendet werden.
16. Sie sollten die Aktualisierung von Clustered-Index-Datenspalten so weit wie möglich vermeiden, da die Reihenfolge der Clustered-Index-Datenspalten der physischen Speicherreihenfolge von Tabellendatensätzen entspricht. Sobald sich der Spaltenwert ändert, führt dies zu einer Anpassung der Reihenfolge die gesamten Tabellenaufzeichnungen, was viel Geld kosten wird. Wenn das Anwendungssystem die Datenspalten des Clustered-Index häufig aktualisieren muss, müssen Sie überlegen, ob der Index als Clustered-Index erstellt werden soll.
17. Versuchen Sie, numerische Felder zu verwenden, versuchen Sie, sie nicht als Zeichenfelder zu gestalten. Dies verringert die Leistung von Abfragen und Verbindungen und erhöht den Speicheraufwand. Dies liegt daran, dass die Engine bei der Verarbeitung von Abfragen und Verbindungen jedes Zeichen in der Zeichenfolge einzeln vergleicht und für numerische Typen nur ein Vergleich ausreicht.
18. Verwenden Sie so oft wie möglich /n anstelle von /n, da Felder mit variabler Länge erstens wenig Speicherplatz haben und Speicherplatz sparen können. Zweitens ist die Sucheffizienz in einem relativ kleinen Feld offensichtlich höher.
19. Verwenden Sie „select * from t“ nirgendwo, ersetzen Sie „*“ durch eine bestimmte Feldliste und geben Sie keine nicht verwendeten Felder zurück.
20. Versuchen Sie, Tabellenvariablen anstelle von temporären Tabellen zu verwenden. Wenn die Tabellenvariable eine große Datenmenge enthält, beachten Sie, dass die Indizes sehr begrenzt sind (nur Primärschlüsselindizes).
21. Vermeiden Sie das häufige Erstellen und Löschen temporärer Tabellen, um den Verbrauch von Systemtabellenressourcen zu reduzieren.
22. Temporäre Tabellen sind nicht unbrauchbar. Wenn Sie sie richtig verwenden, können Sie bestimmte Routinen effizienter gestalten, beispielsweise wenn Sie wiederholt auf eine große Tabelle oder einen bestimmten Datensatz in einer häufig verwendeten Tabelle verweisen müssen. Für einmalige Ereignisse ist es jedoch besser, Exporttabellen zu verwenden.
23. Wenn beim Erstellen einer temporären Tabelle die auf einmal eingefügte Datenmenge groß ist, können Sie „select into“ anstelle von „create table“ verwenden, um zu vermeiden, dass eine große Anzahl von Protokollen die Geschwindigkeit erhöht Nicht groß. Um die Ressourcen der Systemtabelle zu schonen, sollten Sie zuerst eine Tabelle erstellen und dann einfügen.
24. Wenn temporäre Tabellen verwendet werden, müssen alle temporären Tabellen am Ende der gespeicherten Prozedur explizit gelöscht werden, zuerst die Tabelle abschneiden und dann die Tabelle löschen. Dadurch kann eine langfristige Sperrung von Systemtabellen vermieden werden.
25. Vermeiden Sie die Verwendung von Cursorn, da Cursor weniger effizient sind. Wenn die vom Cursor verarbeiteten Daten 10.000 Zeilen überschreiten, sollten Sie darüber nachdenken, sie neu zu schreiben.
26. Bevor Sie die Cursor-basierte Methode oder die temporäre Tabellenmethode verwenden, sollten Sie zunächst nach einer satzbasierten Lösung zur Lösung des Problems suchen. Die satzbasierte Methode ist normalerweise effektiver.
27. Cursor sind wie temporäre Tabellen nicht unbrauchbar. Die Verwendung von FAST_FORWARD-Cursoren ist bei kleinen Datensätzen oft besser als andere zeilenweise Verarbeitungsmethoden, insbesondere wenn mehrere Tabellen referenziert werden müssen, um die erforderlichen Daten zu erhalten. Routinen, die „Summen“ in einen Ergebnissatz einbeziehen, sind normalerweise schneller als die Verwendung eines Cursors. Wenn es die Entwicklungszeit zulässt, können Sie sowohl die Cursor-basierte Methode als auch die Satz-basierte Methode ausprobieren, um herauszufinden, welche Methode besser funktioniert.
28. Setzen Sie SET NOCOUNT ON am Anfang aller gespeicherten Prozeduren und Trigger und setzen Sie SET NOCOUNT OFF am Ende. Es ist nicht erforderlich, nach jeder Anweisung gespeicherter Prozeduren und Trigger eine DONE_IN_PROC-Nachricht an den Client zu senden.
29. Vermeiden Sie die Rückgabe großer Datenmengen an den Kunden. Wenn die Datenmenge zu groß ist, sollten Sie überlegen, ob die entsprechenden Anforderungen angemessen sind.
30. Versuchen Sie, große Transaktionsvorgänge zu vermeiden und die Systemparallelität zu verbessern.

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der MySQL-Abfrageoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!