Ausführliche Erläuterung der SQL-Abfrageoptimierungstechniken für zig Millionen große Datenmengen in MySQL

1. 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 „Reihenfolge nach“.

2. Versuchen Sie zu vermeiden, Nullwerturteile für Felder in der where-Klausel zu fällen, 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 can be on num Stellen Sie den Standardwert 0 ein, stellen Sie sicher, dass in der Spalte „num“ in der Tabelle kein Nullwert vorhanden ist, und fragen Sie dann wie folgt ab: Wählen Sie die ID aus t aus, wobei num=0

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

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.: select id from t where num=10 or num =20 OK Abfrage wie diese: select id from t where num=10 union all select id from t where num=20

5.in und nicht in sollte ebenfalls mit Vorsicht verwendet werden, da es sonst zu a führt Vollständiger Tabellenscan, wie zum Beispiel: select id from t where num in(1,2,3) Für kontinuierliche Werte verwenden Sie nicht in, wenn Sie between verwenden können: select id from t where num between 1 and 3

6. Die folgende Abfrage führt auch zu einem vollständigen Tabellenscan: Wählen Sie eine ID aus, wobei der Name „%李%“ lautet. Um die Effizienz zu verbessern, können Sie eine Volltextsuche in Betracht ziehen.

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 Kompilierzeit 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 beispielsweise einen vollständigen Tabellenscan durch: select id from t where num=@num kann geändert werden, um zu erzwingen, dass die Abfrage den Index verwendet: select id from t with(index(index name)) 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. 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, funktionale Operationen an Feldern 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. Beispiel: Wählen Sie eine ID aus t aus, wobei Teilzeichenfolge (Name, 1,3) = „abc“ ist. Die ID, deren Name mit abc beginnt, sollte geändert werden in: Wählen Sie eine ID aus t aus, wobei der Name „abc%“ lautet.

10. Führen Sie keine Funktionen, arithmetischen Operationen oder andere Ausdrucksoperationen auf der linken Seite von „=" in der where-Klausel aus, da das System sonst den Index möglicherweise nicht korrekt 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 verwendet nicht verwendet, und die Feldreihenfolge sollte so weit wie möglich mit der Indexreihenfolge übereinstimmen.

12. Wenn Sie beispielsweise eine leere Tabellenstruktur generieren müssen: Wählen Sie col1,col2 in #t aus, wobei 1=0 ist. Dieser Codetyp gibt keine zurück Ergebnismenge, aber es wird Systemressourcen verbrauchen, sollte es wie folgt geändert werden: Tabelle erstellen #t(…).

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

14. Nicht alle Indizes sind für Abfragen wirksam, wenn eine große Menge doppelter Daten in der Indexspalte vorhanden ist. Beispielsweise gibt es in einer Tabelle ein Feld „Geschlecht“, das fast zur Hälfte männlich und zur Hälfte weiblich ist. Selbst wenn ein Index auf dem Geschlecht basiert, hat dies 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 ? 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. Sie sollten die Aktualisierung von Clustered-Index-Datenspalten so weit wie möglich vermeiden, da die Reihenfolge der Clustered-Index-Datenspalten die physische Speicherreihenfolge der Tabellendatensätze ist. Sobald sich der Spaltenwert ändert, ändert sich die Reihenfolge der gesamten Tabelle Datensätze werden angepasst. Dies verbraucht erhebliche Ressourcen. 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. Wenn Felder nur numerische Informationen enthalten, sollten Sie versuchen, 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 relativ viel Speicherplatz sparen können kleines Feld 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 das häufige Erstellen und Löschen temporärer Tabellen, um den Verbrauch von Systemtabellenressourcen zu reduzieren.

22. Temporäre Tabellen sind nicht unbrauchbar, und ihre ordnungsgemäße Verwendung kann bestimmte Routinen effizienter machen, 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, eine Exporttabelle zu verwenden.

23. Wenn beim Erstellen einer temporären Tabelle die auf einmal eingefügte Datenmenge groß 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 Die Datenmenge ist nicht groß. Um das System zu entlasten, sollten Sie bei Tabellenressourcen zuerst die Tabelle erstellen und diese 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. Dadurch kann eine langfristige Sperrung des Systems vermieden werden Tische.

25. Versuchen Sie, die Verwendung von Cursorn zu vermeiden, 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 häufig besser als andere zeilenweise Verarbeitungsmethoden, insbesondere wenn auf mehrere Tabellen verwiesen werden muss, 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. Versuchen Sie, große Transaktionsvorgänge zu vermeiden und die Systemparallelität zu verbessern.

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

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der SQL-Abfrageoptimierungstechniken für zig Millionen große Datenmengen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!