Einige Methoden zur Optimierung der Abfragegeschwindigkeit bei der Verarbeitung großer Datenmengen durch MySQL

Bei den tatsächlichen Projekten, an denen ich teilgenommen habe, habe ich festgestellt, dass die Effizienz gewöhnlicher SQL-Abfragen drastisch abnimmt, wenn die Datenmenge in der MySQL-Tabelle Millionen erreicht, und dass die Abfragegeschwindigkeit einfach unerträglich wird, wenn die Where-Klausel viele Abfragebedingungen enthält. Ich habe einmal eine bedingte Abfrage an einer Tabelle mit mehr als 4 Millionen Datensätzen (mit Indizes) getestet. Die Abfragezeit betrug bis zu 40 Sekunden. Ich glaube, dass jeder Benutzer bei einer so hohen Abfrageverzögerung verrückt werden würde. Daher ist es sehr wichtig, die Abfrageeffizienz von SQL-Anweisungen zu verbessern. Im Folgenden sind 30 Methoden zur SQL-Abfrageoptimierung aufgeführt, die im Internet weit verbreitet sind:

1. Versuchen Sie, die Verwendung des Operators != oder <> in der Where-Klausel zu vermeiden, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt.

2. Um die Abfrage zu optimieren, sollten Sie vollständige Tabellenscans vermeiden. Erwägen Sie zunächst die Erstellung von Indizes für die Spalten, die an „where“ und „order by“ beteiligt sind.

3. Vermeiden Sie die Verwendung von Nullwertbeurteilungen für Felder in der Where-Klausel. Andernfalls verzichtet die Engine auf die Verwendung von Indizes und führt einen vollständigen Tabellenscan durch, z. B.:
wähle ID aus t, wobei Num null ist
Sie können für „num“ einen Standardwert von 0 festlegen, um sicherzustellen, dass in der Spalte „num“ der Tabelle kein Nullwert vorhanden ist, und diese dann folgendermaßen abfragen:
wähle ID aus t, wobei num=0

4. Versuchen Sie, die Verwendung von oder zum Verbinden von Bedingungen in der Where-Klausel zu vermeiden. Andernfalls verzichtet die Engine auf die Verwendung von Indizes und führt einen vollständigen Tabellenscan durch, beispielsweise:
wähle die ID aus t, wobei num=10 oder num=20
Sie können wie folgt abfragen:
wähle ID aus t, wobei Num=10
Vereinigung alle
wähle ID aus t, wobei num=20

5. Die folgende Abfrage führt ebenfalls zu einem vollständigen Tabellenscan: (kein führendes Prozentzeichen)
wähle eine ID aus t, wo der Name etwa ‚�c%‘ ist
Zur Verbesserung der Effizienz können Sie eine Volltextabfrage in Betracht ziehen.

6. Verwenden Sie „in“ und „nicht in“ mit Vorsicht, da dies sonst zu einem vollständigen Tabellenscan führt, wie zum Beispiel:
Wähle ID aus t, wobei Num in (1,2,3) ist
Für kontinuierliche Werte verwenden Sie „between“ statt „in“:
wähle id aus t, wobei num zwischen 1 und 3 liegt

7. Wenn in der Where-Klausel Parameter verwendet werden, wird auch ein vollständiger Tabellenscan durchgeführt. Da SQL lokale Variablen nur zur Laufzeit auflöst, kann der Optimierer die Auswahl eines Zugriffsplans nicht bis zur Laufzeit aufschieben; er muss die Auswahl zur Kompilierungszeit treffen. Wenn der Zugriffsplan jedoch zur Kompilierungszeit erstellt wird, ist der Wert der Variablen noch unbekannt und kann nicht als Eingabe für die Indexauswahl verwendet werden. Die folgende Anweisung führt einen vollständigen Tabellenscan durch:
wähle ID aus t, wobei num=@num
Sie können die Abfrage zwingen, stattdessen den Index zu verwenden:
wähle ID aus t mit (Index (Indexname)), wobei num = @num

8. Versuchen Sie, Ausdrucksoperationen auf Feldern in der Where-Klausel zu vermeiden, da dies dazu führt, dass die Engine die Verwendung von Indizes aufgibt und einen vollständigen Tabellenscan durchführt. wie:
wähle ID aus t, wobei num/2=100
Sollte geändert werden in:
wähle ID aus t, wobei Num=100*2

9. Vermeiden Sie nach Möglichkeit die Ausführung von Funktionsoperationen auf Feldern in der Where-Klausel, da dies dazu führt, dass die Engine die Verwendung von Indizes aufgibt und einen vollständigen Tabellenscan durchführt. wie:
select id from t where substring(name,1,3)='abc' – ID, deren Name mit abc beginnt
wähle die ID aus t, wobei datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generierte ID
Sollte geändert werden in:
wähle eine ID aus t, wo der Name etwa ‚abc%‘ ist
wähle die ID aus t, wobei Erstellungsdatum>='2005-11-30' und Erstellungsdatum<'2005-12-1'

10. Führen Sie keine Funktionen, Rechenoperationen oder andere Ausdrucksoperationen auf der linken Seite des "=" in der Where-Klausel aus, da das System sonst den Index möglicherweise nicht richtig verwenden kann.

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 sinnlosen Abfragen, wie etwa solche, die die Generierung einer leeren Tabellenstruktur erfordern:
select col1,col2 in #t von t, wobei 1=0
Dieser Codetyp gibt keinen Ergebnissatz zurück, verbraucht aber Systemressourcen. Er sollte wie folgt geändert werden:
Tabelle erstellen #t(…)

13. In vielen Fällen ist die Verwendung von exists anstelle von in eine gute Wahl:
wähle num aus a, wobei num in (wähle num aus b)
Ersetzen Sie es durch Folgendes:
wähle num aus a, wo existiert (wähle 1 aus b, wo num=a.num)

14. Nicht alle Indizes sind für Abfragen effektiv. SQL optimiert Abfragen basierend auf den Daten in der Tabelle. Wenn die Indexspalte eine große Menge wiederholter Daten enthält, verwendet die SQL-Abfrage den Index möglicherweise nicht. Wenn eine Tabelle beispielsweise ein Geschlechtsfeld mit fast der Hälfte männlich und der Hälfte weiblich hat, hat dies keinen Einfluss auf die Abfrageeffizienz, selbst wenn ein Index auf dem Geschlecht basiert.

15. Je mehr Indizes vorhanden sind, desto besser. Obwohl Indizes die Effizienz der entsprechenden Auswahl verbessern können, verringern sie auch die Effizienz von Einfügungen und Aktualisierungen, da der Index während der Einfügungen oder Aktualisierungen neu erstellt werden kann. Daher muss die Erstellung von Indizes je nach konkreter Situation sorgfältig überlegt werden. Die Anzahl der Indizes für eine Tabelle sollte 6 nicht überschreiten. Wenn es zu viele sind, sollten Sie überlegen, ob Indizes auf Spalten, die nicht oft verwendet werden, notwendig sind.

16. Vermeiden Sie die Aktualisierung von Clustered-Index-Datenspalten so weit wie möglich, da die Reihenfolge der Clustered-Index-Datenspalten die physische Speicherreihenfolge der Tabellendatensätze ist. Sobald sich der Spaltenwert ändert, wird die Reihenfolge der gesamten Tabellendatensätze angepasst, was erhebliche Ressourcen verbraucht. 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. Wenn das Feld nur numerische Informationen enthält, sollten Sie es nicht als Zeichenfeld gestalten, da dies die Leistung von Abfragen und Verbindungen verringert und den Speicheraufwand erhöht. Dies liegt daran, dass die Engine bei der Verarbeitung von Abfragen und Verbindungen jedes Zeichen in der Zeichenfolge einzeln vergleicht, für numerische Typen jedoch nur ein Vergleich ausreicht.

18. Verwenden Sie nach Möglichkeit varchar/nvarchar statt char/nchar. Erstens benötigen Felder mit variabler Länge weniger Speicherplatz, was Speicherplatz sparen kann. Zweitens ist die Suche in einem relativ kleinen Feld bei Abfragen offensichtlich effizienter.

19. Verwenden Sie nirgendwo select * from t. Ersetzen Sie "*" durch eine bestimmte Feldliste und geben Sie keine unbenutzten Felder zurück.

20. Versuchen Sie, Tabellenvariablen anstelle von temporären Tabellen zu verwenden. Wenn die Tabellenvariable viele Daten enthält, beachten Sie, dass die Indizes sehr begrenzt sind (nur der Primärschlüsselindex).

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. Ihre angemessene Verwendung kann bestimmte Routinen effizienter machen, beispielsweise wenn Sie wiederholt auf einen Datensatz in einer großen Tabelle oder einer häufig verwendeten Tabelle verweisen müssen. Für einmalige Ereignisse ist es jedoch besser, eine Exporttabelle zu verwenden.

23. Wenn beim Erstellen einer neuen temporären Tabelle die Menge der auf einmal einzufügenden Daten groß ist, kann „select into“ anstelle von „create table“ verwendet werden, um die Erstellung einer großen Menge an Protokollen zu vermeiden und die Geschwindigkeit zu erhöhen. Wenn die Datenmenge nicht groß ist, können Sie, um die Ressourcen der Systemtabelle zu schonen, zuerst „create table“ und dann „injection“ verwenden.

24. Wenn temporäre Tabellen verwendet werden, achten Sie darauf, am Ende der gespeicherten Prozedur alle temporären Tabellen explizit zu löschen. Truncate Table (Tabelle zuerst) und Drop (Tabelle löschen) ist die Tabelle. Dadurch kann vermieden werden, dass die Systemtabelle für längere Zeit gesperrt wird.

25. Vermeiden Sie die Verwendung von Cursorn, da diese nicht effizient sind. Wenn die vom Cursor bearbeiteten Daten 10.000 Zeilen überschreiten, sollten Sie eine Neuschreibung in Betracht ziehen.

26. Bevor Sie Cursor-basierte Methoden oder temporäre Tabellenmethoden verwenden, sollten Sie zunächst nach satzbasierten Lösungen suchen, um das Problem zu lösen. Satzbasierte Methoden sind normalerweise effektiver.

27. Cursor sind ebenso wie temporäre Tabellen nicht unbrauchbar. Die Verwendung eines FAST_FORWARD-Cursors ist bei kleinen Datensätzen anderen Methoden der zeilenweisen Verarbeitung häufig überlegen, insbesondere wenn zum Abrufen der erforderlichen Daten auf mehrere Tabellen verwiesen werden muss. Routinen, die „Aggregate“ im Ergebnissatz enthalten, werden im Allgemeinen schneller ausgeführt als die Verwendung von Cursorn. Wenn die Entwicklungszeit es erlaubt, probieren Sie sowohl den Cursor-basierten als auch den Set-basierten Ansatz aus, um zu sehen, welcher 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 der Ausführung jeder Anweisung in gespeicherten Prozeduren und Triggern eine DONE_IN_PROC-Nachricht an den Client zu senden.

29. Versuchen Sie, die Rückgabe großer Datenmengen an den Client zu vermeiden. Wenn die Datenmenge zu groß ist, überlegen Sie, ob die entsprechende Nachfrage angemessen ist.

30. Versuchen Sie, große Transaktionsvorgänge zu vermeiden und die Parallelitätsfunktionen des Systems zu verbessern.