Ausführliche Erklärung, wie Sie die Abfragegeschwindigkeit optimieren können, wenn MySQL große Datenmengen verarbeitet

Vor kurzem habe ich aus beruflichen Gründen begonnen, mich mit den zugehörigen Optimierungsmethoden für die SELECT-Anweisung der Mysql-Datenbank zu befassen Wer es braucht, kann darauf verweisen Aufgrund der tatsächlichen Projekte, an denen 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 davon gibt Abfragebedingungen, bei denen die Abfragegeschwindigkeit einfach unerträglich ist. 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 von != oder <>

Operator

in der where-Klausel, da dies sonst der Fall ist wird Die Engine gibt die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch. 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 ist null

Sie können den Standardwert 0 für „num“ festlegen, sicherstellen, dass in der Spalte „num“ der Tabelle kein Nullwert vorhanden ist, und dann eine Abfrage wie folgt durchführen:
Wählen Sie die ID aus t aus, wobei num=0 ist

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

Sie können wie folgt abfragen:
Wählen Sie die ID aus t aus, wobei die Anzahl = 10 ist Veranlassen Sie auch einen vollständigen Tabellenscan: (kann nicht vor dem Prozentzeichen stehen)
Wählen Sie eine ID aus, wobei der Name etwa „�c%“ lautet.
Um die Effizienz zu verbessern, können Sie eine Volltextsuche in Betracht ziehen.

6. In und nicht 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)

Wenn Sie bei kontinuierlichen Werten between verwenden können, verwenden Sie nicht in:
wählen Sie id aus t, wobei die Zahl zwischen 1 und 3 liegt

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

Variablen
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
kann geändert werden, um zu erzwingen, dass die Abfrage einen Index verwendet:

select id from t with (index (Indexname)) where num= @num

8 Versuchen Sie zu vermeiden, Ausdruck
-Operationen 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 keine Leistung erbringt ein vollständiger Tabellenscan. 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, Where-Klauseln zu vermeiden Funktionsoperationen werden für Felder im Feld ausgeführt, was dazu führt, dass die Engine die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt. Zum Beispiel: Wählen Sie die ID aus t aus, wobei Teilzeichenfolge(Name,1,3)='abc'–Namens-ID beginnt mit abc.
Wählen Sie die ID aus t aus, wobei Datumdiff(Tag,Erstellungsdatum,'2005-11-30′) )=0–'2005-11-30′Die generierte ID
sollte geändert werden in:
Wählen Sie die ID aus t aus, wobei der Name etwa „abc%“ lautet.

Wählen Sie die ID aus t aus, wobei das Erstellungsdatum>='2005- 11 -30′ und erstelltes Datum<'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 sonst das System dies tun kann Index nicht korrekt verwendet werden.

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 Typ Der Code gibt nichts zurück, verbraucht aber Systemressourcen und 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 where num=a.num )

14. Nicht alle Indizes sind für Abfragen effektiv, wenn die Indexspalte eine große Menge doppelter Daten enthält Wenn beispielsweise ein Feld „Geschlecht“ in einer Tabelle vorhanden ist, sind Männer und Frauen fast unterschiedlich. 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 Index ist erforderlich. Sorgfältig und von Fall zu Fall prüfen. 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, da die Reihenfolge der Datenspalten des Clustered-Index die physische Speicherreihenfolge der Tabellendatensätze ist 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, 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 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 beispielsweise wiederholt auf eine große Tabelle oder eine häufig verwendete Tabelle verweisen müssen Datensatz. Für einmalige Ereignisse ist es jedoch besser, eine Exporttabelle zu verwenden.

23. Wenn beim Erstellen einer temporären Tabelle die einmalige

Einfügung von Daten sehr umfangreich ist, können Sie „select into“ anstelle von „create table“ verwenden, um eine große Anzahl von Protokollen zu vermeiden Erhöhen Sie die Geschwindigkeit. Wenn die Datenmenge nicht groß ist, 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 Prozedur zuerst gelöscht werden. Dies kann vermieden werden Überlastung der Systemtabelle.

25. Vermeiden Sie

die Verwendung von Cursorn, da Cursor weniger effizient sind. Wenn die Daten der Cursoroperation 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 des FAST_

FORWARD-Cursors für kleine Datensätze ist häufig anderen zeilenweisen Verarbeitungsmethoden ü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

COUNT zu Beginn aller gespeicherten Prozeduren und Trigger auf ON 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, um die Systemparallelität zu verbessern.

Das obige ist der detaillierte Inhalt vonAusführliche Erklärung, wie Sie die Abfragegeschwindigkeit optimieren können, wenn MySQL große Datenmengen verarbeitet. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!