Fassen Sie einige optimierte Abfragegeschwindigkeiten zusammen, wenn MySQL viele Daten verarbeitet

Vor kurzem habe ich aus beruflichen Gründen begonnen, auf die Optimierungsmethoden ausgewählter Abfrageanweisungen für die MySQL-Datenbank zu achten.

Aufgrund des tatsächlichen Projekts, an dem ich teilgenommen habe Ich habe festgestellt, dass die Datenmenge in der MySQL-Tabelle beim Erreichen des Millionenniveaus die Effizienz gewöhnlicher SQL-Abfragen stark verringert, und wenn es viele Abfragebedingungen gibt, ist die Abfragegeschwindigkeit einfach unerträglich. Ich habe einmal eine bedingte Abfrage für eine Tabelle mit mehr als 4 Millionen Datensätzen (mit Indizes) getestet und die Abfragezeit betrug bis zu 40 Sekunden. Ich glaube, dass eine so hohe Abfrageverzögerung jeden Benutzer in den Wahnsinn treiben würde. Daher ist es sehr wichtig, die Effizienz der SQL-Anweisungsabfrage zu verbessern. Die folgenden 30 Methoden zur Optimierung von SQL-Abfrageanweisungen sind im Internet weit verbreitet:

1 Versuchen Sie, die Operatoren != oder <> in der where-Klausel zu vermeiden, da die Engine sonst aufgibt Index und fahren Sie fort.

2. Um die Abfrage zu optimieren, sollten Sie zunächst die Erstellung von Indizes für die beteiligten Spalten „wo“ und „Reihenfolge“ in Betracht ziehen.

3. Vermeiden Sie die Beurteilung des Nullwerts von Feldern in der where-Klausel, 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, sicherstellen, dass in der Spalte „num“ in der Tabelle kein Nullwert vorhanden ist, und dann eine Abfrage wie folgt durchführen:

select id from t where num=0

4 Versuchen Sie es Vermeiden Sie die Verwendung von oder in der where-Klausel, um Bedingungen zu verbinden. Andernfalls 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

kann wie folgt abgefragt werden :

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)

select id from t where name like ‘�c%'

Zur Verbesserung der Effizienz , können Sie eine Volltextsuche in Betracht ziehen.

6. In und not in sollten ebenfalls mit Vorsicht verwendet werden, da es sonst zu einem vollständigen Tabellenscan führt, wie zum Beispiel:

select id from t where num in(1,2,3)

Für kontinuierliche Werte , können Sie zwischen verwenden. Nicht verwenden in:

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. Beispielsweise führt die folgende Anweisung einen vollständigen Tabellenscan durch:

select id from t where num=@num

kann geändert werden, um zu erzwingen, dass die Abfrage einen Index verwendet:

select id from t with(index(索引名)) where num=@num

8 Versuchen Sie, Where-Klauseln zu vermeiden. Führen Sie Ausdrucksoperationen für Felder aus, die dazu führen, dass die Engine die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt. Beispiel:

select id from t where num/2=100
应改为:
select id from t where num=100*2

9. Vermeiden Sie die Ausführung von Funktionsoperationen für Felder in der Where-Klausel, 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 substring(name,1,3)='abc'–name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%'
select id from t where createdate>=&#39;2005-11-30′ and createdate<&#39;2005-12-1′

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 möglicherweise nicht in der Lage ist, diese zu verwenden den Index korrekt.

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 werden, 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 Typ Der Ergebnissatz gibt nichts zurück, verbraucht aber Systemressourcen und sollte wie folgt geändert werden:

create table #t(…)

13 In vielen Fällen ist die Verwendung von „exists“ anstelle von „in“. :

select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

14. Nicht alle Indizes sind für Abfragen gültig, die auf den Daten in der Tabelle basieren Die Abfrage verwendet möglicherweise keinen Index in einer Tabelle. Es gibt fast jeweils die Hälfte von männlich und weiblich. 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. 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. Versuchen Sie, numerische Felder zu verwenden, die nur numerische Informationen enthalten. Versuchen Sie, diese 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 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, da sie bestimmte Routinen effizienter machen können, 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 „Auswählen in“ anstelle von „Tabelle erstellen“ verwenden, um zu vermeiden, dass eine große Anzahl von Protokollen die Geschwindigkeit erhöht Die Anzahl der Daten 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 der 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 ein Umschreiben in Betracht ziehen.

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. 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 vonFassen Sie einige optimierte Abfragegeschwindigkeiten zusammen, wenn MySQL viele Daten verarbeitet. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!