30 Tipps zur Optimierung von MySQL-Anweisungen

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

2. 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“.

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

kann in num verwendet werden. Legen Sie den Standardwert 0 in der Tabelle fest, stellen Sie sicher, dass die Num-Spalte in der Tabelle keinen Nullwert enthält, und fragen Sie dann wie folgt ab:

select id from t where num=0

4 Versuchen Sie, die Verwendung zu vermeiden oder in der where-Klausel, um Bedingungen zu verbinden, sonst gibt die Engine die Verwendung des Index auf, wie zum Beispiel:

select id from t where num=10 or num=20

, Sie können wie folgt abfragen:

select id from t where num=10union allselect id from t where num=20

5. Die folgende Abfrage führt ebenfalls zu einem vollständigen Tabellenscan: (kann nicht vor dem Prozentzeichen stehen)

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

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)

Für kontinuierliche Werte, wenn Sie between verwenden können , nicht verwenden in:

select id from t where num between 1 and 3

7 Die Verwendung von Parametern in der Klausel führt 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. Jedoch 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. 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, Ausdrücke zu vermeiden Felder in der Where-Klausel-Operation, was dazu führt, dass die Engine die Verwendung des Index abbricht 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, Funktionsoperationen für Felder in der where-Klausel auszuführen, da dies dazu führen würde, dass die Engine die Verwendung aufgibt den Index und führen Sie einen vollständigen Tabellenscan durch. 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

sollte geändert werden in:

select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10. Führen Sie keine Funktionen, arithmetischen Operationen oder andere Ausdrucksoperationen auf der linken Seite von „=" im Wo aus Andernfalls werden die Systemindizes möglicherweise nicht korrekt verwendet.

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:

select col1,col2 into #t from t where 1=0

Dieser Codetyp gibt keine Ergebnismenge zurück, aber er wird zurückgegeben verbrauchen Systemressourcen. Es sollte wie folgt geändert werden:

create table #t(…)

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

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 gültig, die auf den Daten in der Tabelle basieren. Wenn die Indexspalte eine große Menge doppelter Daten enthält Verwenden Sie den Index, wenn in einer Tabelle beispielsweise ein Feld „Geschlecht“ vorhanden ist. „Männlich“ und „Weiblich“ sind jeweils fast die Hälfte. 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. Vermeiden Sie die Aktualisierung von Clustered-Index-Datenspalten so weit wie möglich, da die Reihenfolge der Clustered-Index-Datenspalten der physischen Speicherreihenfolge der Tabellendatensätze entspricht. Sobald sich der Spaltenwert ändert, ändert sich die Reihenfolge der gesamten Tabellendatensätze angepasst werden. Es 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 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, 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 Nicht groß. Um die Ressourcen der Systemtabelle zu schonen, sollten Sie zuerst eine Tabelle erstellen und 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.

Vermeiden Sie die Verwendung von Cursorn, 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 die Ergebnismenge einbeziehen, sind in der Regel schneller als die Verwendung von Cursorn. 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.