30 gängige Methoden zur Optimierung von SQL
- 大家讲道理Original
- 2017-04-11 14:05:291297Durchsuche
MySQLEinige Optimierungen bei der Verarbeitung großer DatenmengenAbfrageGeschwindigkeitsmethode
Vor kurzem habe ich aus beruflichen Gründen fing an, auf relevante Optimierungsmethoden für die SELECT-Abfrageanweisung der MySQL-Datenbank zu achten.
Denn in dem eigentlichen Projekt, an dem ich teilgenommen habe, wurde festgestellt, dass die Effizienz gewöhnlicher SQL-Abfragen sinkt, wenn das Datenvolumen der MySQL-Tabelle Millionen erreicht, und wenn es viele Abfragebedingungen gibt, wo die Abfrage erfolgt Geschwindigkeit ist einfach unerträglich. Ich habe einmal eine bedingte Abfrage an einer Tabelle getestet, die mehr als 4 Millionen Datensätze enthält (mit Index ), und die Abfragezeit betrug bis zu 40 Sekunden. Ich glaube, dass eine so hohe Abfrageverzögerung jeden Benutzer in den Wahnsinn treiben wird . Daher ist es sehr wichtig, die Effizienz der SQL-Anweisungsabfrage zu verbessern. Im Folgenden sind 30 Methoden zur Optimierung von SQL-Abfrageanweisungen aufgeführt, die im Internet weit verbreitet sind:
1. Vermeiden Sie die Verwendung der Operatoren != oder <> in where-Klauseln , andernfalls gibt die Engine die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch.
2. Um die Abfrage zu optimieren, versuchen Sie, vollständige Tabellenscans zu vermeiden. Erwägen Sie zunächst die Erstellung von Indizes für die beteiligten Spalten „wo“ und „ordnen“ nach. 3. Versuchen Sie, eine
Null-Wertbeurteilung von Feldern in der where-Klausel zu vermeiden, 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 Sie können den Standardwert 0 für num festlegen, um sicherzustellen, dass in der Num-Spalte der Tabelle kein Nullwert vorhanden ist, und dann eine Abfrage wie folgt durchführen:
select id from t where num =0
4. Vermeiden Sie die Verwendung von oder in der where-Klausel zum Verbinden von Bedingungen, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt, z. B.:
Sie können eine Abfrage wie folgt durchführen:
Wählen Sie die ID aus t aus, wobei num=10
Union
alle Wählen Sie die ID aus t where num=20
5. Die folgende Abfrage führt auch zu einem vollständigen Tabellenscan: (kann nicht vor dem Prozentzeichen stehen)
Um die Effizienz zu steigern, 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.:
Wenn Sie bei kontinuierlichen Werten between verwenden können, verwenden Sie nicht in:
select id from t where num between 1 and 3
7. Wenn Parameter in der where-Klausel verwendet werden, wird dies der Fall sein Außerdem wird ein vollständiger Tabellenscan durchgeführt. Da SQL nur lokale
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)), wobei num= @num
8. Sie sollten versuchen, die Ausführung von
-Operationen für Felder in der where-Klausel zu vermeiden, da dies dazu führen würde, dass die Engine die Verwendung von aufgibt indizieren und einen vollständigen Tabellenscan durchführen. Zum Beispiel: select id from t where num/2=100 sollte geändert werden in:
select id from t where num=100*2
9. Versuchen Sie zu vermeiden, Funktionsoperationen für Felder in der where-Klausel auszuführen, 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 von dort aus
substring(name,1,3)='abc'–Namens-ID beginnend mit abc
wähle die ID aus t aus, wobeiDatumdiff ( day,createdate,'2005-11-30′)=0–'2005-11-30′Die generierte ID
sollte geändert werden in:
select id from t where name like 'abc%'
Wählen Sie die ID von t aus
createdate>='2005-11-30′ undcreatedate<'2005-12-1′
10. Führen Sie keine Funktionen, arithmetischen Operationen oder andere Ausdrucksoperationen auf der linken Seite von „=" im Where aus Andernfalls kann das System den Index möglicherweise nicht korrekt verwenden.
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 verwendet 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, wobei 1=0 ist.
Diese Art von Der Code gibt nichts zurück, verbraucht aber Systemressourcen. Er sollte wie folgt geändert werden:
create table #t(…)
13. In vielen Fällen wird „exists“ anstelle von „in“ verwendet ist eine gute Wahl:
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
wobei num=a.num)
14. Nicht alle Indizes sind für Abfragen gültig, die auf den Daten in der Tabelle basieren SQL-Abfragen können möglicherweise keine Indizes verwenden, wenn beispielsweise in einer Tabelle ein Feld „Geschlecht“ vorhanden ist und fast die Hälfte davon männlich und die Hälfte weiblich ist. Selbst wenn ein Index auf dem Geschlecht basiert, hat dies keine Auswirkung 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 ? Die Indizierung erfordert sorgfältige Überlegungen und hängt von den Umständen ab. 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. Vermeiden Sie die Aktualisierung Datenspalten des Clustered-Index so weit wie möglich, da die Reihenfolge der Datenspalten des Clustered-Index der physischen Speicherreihenfolge der Tabellendatensätze entspricht Der Tabellendatensatz wird sein. Das Anpassen der Reihenfolge wird erhebliche Ressourcen verbrauchen. 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, die nur numerische Informationen enthalten. 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 varchar/nvarchar anstelle von char/nchar, da Felder mit variabler Länge erstens wenig Speicherplatz haben und zweitens bei Abfragen in einem relativ kleinen Feld Speicherplatz sparen können SuchenDie Effizienz ist 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 häufiges 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 in einer großen Tabelle oder einer häufig verwendeten Tabelle wiederholt auf bestimmte Daten verweisen müssen Zeit einstellen. Für einmalige Ereignisse ist es jedoch besser, eine Exporttabelle zu verwenden.
23. Wenn beim Erstellen einer temporären Tabelle die auf einmal eingefügte Datenmengegroß ist, können Sie „Auswählen in“ anstelle von „Tabelle erstellen“ verwenden, um zu vermeiden, dass eine große Anzahl von Protokollen die Geschwindigkeit erhöht ; wenn die Datenmenge nicht groß ist Um die Ressourcen der Systemtabelle zu entlasten, sollten Sie zuerst die Tabelle erstellen und dann einfügen. 24. Wenn temporäre Tabellen verwendet werden, müssen alle temporären Tabellen am Ende der
gespeicherten Prozedurexplizit gelöscht werden. Zuerst die Tabelle abschneiden und dann die Tabelle löschen, um Systemfehler zu vermeiden Tabellenbeschädigung für lange Zeit gesperrt. 25. Vermeiden Sie
die Verwendung von Cursorn, da Cursor weniger effizient sind. Wenn die Daten der Cursoroperation 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. Für kleine Datensätze verwenden FAST_
FORWARD-Cursor sind anderen zeilenweisen Verarbeitungsmethoden häufig überlegen, 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 NO
COUNTON zu Beginn 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ängezu vermeiden, um die Systemparallelität zu verbessern.
Das obige ist der detaillierte Inhalt von30 gängige Methoden zur Optimierung von SQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!