So verbessern Sie die Geschwindigkeit der Datenbankabfrage für Millionen von Elementen

1. Um die Abfrage zu optimieren, sollten Sie versuchen, vollständige Tabellenscans zu vermeiden. Sie sollten zunächst in Erwägung ziehen, Indizes für die beteiligten Spalten in „Where“ und „Sortierung nach“ zu erstellen.

2. Versuchen Sie, Nullwerturteile für Felder in der where-Klausel zu vermeiden, 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 für die Tabelle fest, stellen Sie sicher, dass in der Num-Spalte der Tabelle kein Nullwert vorhanden ist, und fragen Sie dann wie folgt ab: 　

select id from t where num=0

3. Versuchen Sie, die Operatoren != oder a8093152e673feb7aba1828c43532094 in der where-Klausel zu vermeiden, da die Engine sonst die Verwendung des Index aufgibt und einen vollständigen Tabellenscan durchführt.

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

kann so abgefragt werden: 　

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

5. In und nicht in sollten ebenfalls mit Vorsicht verwendet werden, da sonst ein vollständiger Tabellenscan durchgeführt wird, z. B.: 　

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

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

select id from t where num between 1 and 3

6. Die folgende Abfrage führt ebenfalls zu einem vollständigen Tabellenscan: 　

select id from t where name like '%abc%'

Um die Effizienz zu verbessern, können Sie eine Volltextsuche in Betracht ziehen.

7. Wenn in der where-Klausel Parameter 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: 8. Versuchen Sie zu vermeiden, Ausdrucksoperationen 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 einen vollständigen Tabellenscan durchführt. Beispiel:

select id from t where num=@num

sollte geändert werden in:

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

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 des Index aufgibt und einen vollständigen Tabellenscan durchführt. Zum Beispiel: 　

　select id from t where num/2=100

Sollte geändert werden in:

select id from t where num=100*2

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 nicht verwendet Achten Sie darauf, dass die Feldreihenfolge so weit wie möglich mit der Indexreihenfolge übereinstimmt.

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

12. Schreiben Sie keine bedeutungslosen Abfragen, wenn Sie beispielsweise eine leere Tabellenstruktur generieren müssen: 　

select id from t where name like 'abc%'
　　select id from t where createdate>=&#39;2005-11-30&#39; and createdate<&#39;2005-12-1&#39;

Dieser Codetyp gibt keine Ergebnismenge zurück, verbraucht jedoch Systemressourcen. Er sollte wie folgt geändert werden:

　

13. Oft ist es eine gute Wahl, „exists“ anstelle von „in“ zu verwenden: 　

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

Ersetzen Sie durch Folgendes:

　create table #t(...)

14. Nicht alle Indizes sind für Abfragen wirksam. SQL optimiert Abfragen basierend auf den Daten in der Tabelle. Wenn die Indexspalte eine große Menge doppelter Daten enthält, verwendet die SQL-Abfrage möglicherweise nicht den Index, z. B. eine Tabelle mit Feldern Geschlecht, männlich, weiblich beträgt fast die Hälfte. Selbst wenn ein Index also 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 sorgfältig überlegt werden. Das hängt von der jeweiligen Situation 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.

　select num from a where num in(select num from b)

16. Sie sollten die Aktualisierung von Clustered-Index-Datenspalten möglichst vermeiden, da die Reihenfolge der Clustered-Index-Datenspalten der physischen Speicherreihenfolge der Tabellendatensätze entspricht. Sobald sich der Spaltenwert ändert, wird die Reihenfolge der gesamten Tabellendatensätze angepasst 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, versuchen Sie, 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 erstens der Speicherplatz von Feldern variabler Länge klein ist, wodurch Speicherplatz gespart werden kann. Zweitens ist die Sucheffizienz in einem relativ kleinen Feld geringer 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 einen 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 beim Erstellen einer temporären Tabelle die auf einmal eingefügte Datenmenge groß ist, können Sie „select into“ anstelle von „create table“ verwenden, um zu vermeiden, dass eine große Anzahl von Protokollen entsteht, und die Geschwindigkeit zu verbessern, wenn die Datenmenge nicht groß ist 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, zuerst die Tabelle abschneiden und dann die Tabelle löschen. Dadurch kann eine langfristige Sperrung von 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 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 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 Parallelitätsfähigkeiten des Systems zu verbessern.

30. Vermeiden Sie die Rückgabe großer Datenmengen an den Kunden. Wenn die Datenmenge zu groß ist, sollten Sie prüfen, ob die entsprechenden Anforderungen angemessen sind.

[Verwandte Empfehlungen]

1. Kostenloses MySQL-Video-Tutorial

2.5 häufige Fehler beim Datenbankdesign

3. Wie man MySQL dazu bringt, am Ende Inhalts-SQL-Anweisungen hinzuzufügen und dabei den vorhandenen Inhalt beizubehalten

4. Eine Zusammenfassung, wie man häufig verwendete SQL-Anweisungen in MySQL schreibt

5.

Detaillierte Erklärung, wie man MySQL zum Generieren von Zufallszahlen und zum Verbinden von Zeichenfolgen verwendet

Das obige ist der detaillierte Inhalt vonSo verbessern Sie die Geschwindigkeit der Datenbankabfrage für Millionen von Elementen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!