Zusammenfassung der Wissenspunkte zur SQL-Abfrageoptimierung für MySQL-Big Data im zweistelligen Millionenbereich

1. 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.

2. Vermeiden Sie die Verwendung von Nullwertbeurteilungen für Felder in der Where-Klausel, da die Engine sonst die Verwendung von Indizes aufgibt und einen vollständigen Tabellenscan durchführt, z. B.: select id from t where num is null. Sie können einen Standardwert von 0 für num festlegen, um sicherzustellen, dass die Spalte num in der Tabelle keinen Nullwert enthält, und dann wie folgt abfragen: select id from t where num=0

3. Vermeiden Sie möglichst die Verwendung der Operatoren != oder <> in der Where-Klausel. Andernfalls gibt die Engine die Verwendung des Indexes auf und führt einen vollständigen Tabellenscan durch.

4. Vermeiden Sie die Verwendung von oder zum Verbinden von Bedingungen in der Where-Klausel, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt. Beispielsweise kann select id from t where num=10 oder num=20 wie folgt abgefragt werden: select id from t where num=10 union all select id from t where num=20

5. Verwenden Sie in und nicht in mit Vorsicht, da sonst ein vollständiger Tabellenscan durchgeführt wird, z. B.: select id from t where num in(1,2,3) Für aufeinanderfolgende Werte verwenden Sie between statt in: select id from t where num between 1 and 3

6. Die folgende Abfrage führt auch zu einem vollständigen Tabellenscan: select id from t where name like '%李%'. Zur Verbesserung der Effizienz können Sie eine Volltextabfrage in Betracht ziehen.

7. Wenn Sie Parameter in der Where-Klausel verwenden, 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: select id from t where num=@num Sie können dies ändern, um die Abfrage zur Verwendung des Indexes zu zwingen: select id from t with(index(index name)) where 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. Beispiel: „select id from t where num/2=100“ sollte geändert werden in: „select id from t where 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. Beispiel: „select id from t where substring(name,1,3)='abc'“. Die ID, deren Name mit abc beginnt, sollte in „select id from t where name like ‚abc%‘“ geändert werden.

10. Führen Sie in der Where-Klausel keine Funktionen, Rechenoperationen oder andere Ausdrucksoperationen auf der linken Seite von "=" 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 z. B. wenn Sie eine leere Tabellenstruktur generieren müssen: select col1,col2 into #t from t where 1=0. Diese Art von Code gibt keinen Ergebnissatz zurück, verbraucht aber Systemressourcen. Er sollte geändert werden in: create table #t(…).

13. In vielen Fällen ist es eine gute Wahl, „exists“ statt „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 exists(select 1 from b where 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, 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 Liste von Feldern 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 geeignete Verwendung kann bestimmte Routinen effizienter machen, beispielsweise wenn Sie wiederholt auf einen bestimmten 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 Sie eine neue temporäre Tabelle erstellen und die Menge der auf einmal einzufügenden Daten groß ist, können Sie „select into“ anstelle von „create table“ verwenden, um die Erstellung einer großen Menge an Protokollen zu vermeiden und die Geschwindigkeit zu erhöhen. Wenn die Datenmenge nicht groß ist, sollten Sie, um die Ressourcen der Systemtabelle zu schonen, zuerst die Tabelle erstellen und dann einfügen.

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 möglichst die Verwendung von Cursorn, da diese weniger effizient sind. Wenn der Cursor mehr als 10.000 Datenzeilen bearbeitet, sollten Sie ihn neu schreiben.

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 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, große Transaktionsvorgänge zu vermeiden und die Parallelitätsfunktionen des Systems zu verbessern.

30. 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.

Oben sind 30 Techniken zur Abfrageoptimierung aufgeführt, die wir zusammengefasst haben. Vielen Dank für Ihr Wissen und Ihre Unterstützung für 123WORDPRESS.COM. Wenn Sie Ergänzungen haben, wenden Sie sich bitte an den untenstehenden Herausgeber unter QQ.