Nach dem Test wurde eine bedingte Abfrage auf einer Tabelle mit mehr als 4 Millionen Datensätzen durchgeführt, und die Abfragezeit betrug bis zu 40 Sekunden. Daher ist es sehr wichtig, wie man die Effizienz der SQL-Statement-Abfrage verbessern kann. Im Folgenden sind mehrere Methoden zur Optimierung von Abfrageaussagen aufgeführt, die im Internet weit verbreitet sind:
Zunächst sollten Sie, wenn das Datenvolumen groß ist, versuchen, das vollständige Lesen der Tabelle zu vermeiden und stattdessen Indizes auf den Kolonnen in Wo und Sortierung zu erstellen, was die Datenabrufe erheblich beschleunigen kann. Es gibt jedoch einige Situationen, in denen Indexierung nicht funktioniert:
1. Versuchen Sie, die Verwendung von != oder <> Operatoren in der where-Klausel zu vermeiden, sonst verzichtet die Engine auf die Verwendung von Indizes und führt eine vollständige Tabellenabtastung durch.
2. Versuchen Sie, Nullwerturteile bei Feldern in der where-Klausel zu vermeiden, andernfalls verzichtet die Engine auf die Verwendung von Indizes und führt vollständige Tabellenscans durch, wie zum Beispiel:
Wähle ID aus T aus, wobei Num null ist
Du kannst den Standardwert auf 0 auf der Zahl setzen, sicherstellen, dass kein Nullwert in der Num-Spalte in der Tabelle steht, und dann so abfragen:
Wähle ID aus T aus, wobei Num=0
3. Versuche, OR in der where-Klausel nicht zu verwenden, um Bedingungen zu joinen, sonst verlässt die Engine die Verwendung des Index und führt einen vollständigen Tabellen-Scan durch, wie zum Beispiel:
Wähle id aus T aus, wobei num=10 oder num=20 sind
Du kannst so eine Anfrage machen:
Wähle ID aus T aus, wobei Num=10
Vereinigung ALLE
Wähle id aus T aus, wobei num=20
4. Die folgende Abfrage führt ebenfalls zu einem vollständigen Tabellenscan:
Wähle ID aus T aus, wo Name wie '%abc%'
Um die Effizienz zu steigern, sollten Sie eine Volltextsuche in Betracht ziehen.
5. In und nicht in sollten ebenfalls mit Vorsicht verwendet werden, sonst führt dies zu vollständigem Tischscanning, wie zum Beispiel:
Wähle ID aus T aus, wo num in(1,2,3)
Für kontinuierliche Werte, wenn du zwischen verwenden kannst, verwende nicht in:
Wähle ID aus T aus, wo Num zwischen 1 und 3 liegt
6. Wenn du den Parameter in der where-Klausel verwendest, wird auch die vollständige Tabelle gescannt. Da SQL nur lokale Variablen zur Laufzeit auflöst, aber der Optimierer die Auswahl der Zugriffspläne nicht auf die Laufzeit verschieben kann; Sie muss zur Kompilierungszeit ausgewählt werden. Wenn jedoch ein Zugriffsplan zur Kompilierungszeit erstellt wird, ist der Wert der Variable weiterhin unbekannt und kann daher nicht als Eingabeelement für die Indexauswahl verwendet werden. Die folgenden Aussagen werden vollständig eingescannt:
Wähle ID aus T aus, wobei num=@num
Sie können die Abfrage dazu zwingen, stattdessen einen Index zu verwenden:
Wählen Sie die ID aus T mit(Index(Index(Indexname)), wobei num=@num
7. Versuchen Sie, Felder in der where-Klausel nicht auszudrücken, da sie dazu führen würden, dass die Engine die Nutzung des Index aufgibt und ein vollständiges Tabellen-Scannen durchführt. Zum Beispiel:
Wähle ID aus T aus, wobei num/2=100
sollte geändert werden zu:
Wähle ID aus T aus, wobei num=100*2
8. Versuchen Sie, Funktionsoperationen auf Feldern in der where-Klausel zu vermeiden, da dies dazu führt, dass die Engine die Verwendung von Indizes aufgibt und stattdessen vollständige Tabellenscans durchführt. Zum Beispiel:
Wähle ID aus T aus, wobei Substring(name,1,3)='abc' – Name-ID, die mit abc beginnt,
Wählen Sie die ID aus T aus, wo datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generierte ID
sollte geändert werden zu:
Wähle die ID aus T aus, wo Name wie 'abc%' zum Beispiel 'abc%'
Wählen Sie die ID aus T aus, wo CreateDate>='2005-11-30′ und CreateDate<'2005-12-1′
9. Führen Sie keine Funktionen, arithmetischen Operationen oder andere Ausdrucksoperationen links vom "=" in der where-Klausel aus, da das System den Index möglicherweise nicht korrekt verwenden kann.
10. Wenn ein Indexfeld als Bedingung verwendet wird, muss bei einem zusammengesetzten Index das erste Feld im Index als Bedingung verwendet werden, um sicherzustellen, dass das System den Index verwendet, andernfalls wird der Index nicht verwendet, und die Reihenfolge der Felder sollte so weit wie möglich mit der Indexreihenfolge konsistent sein.
11. Schreibe keine bedeutungslosen Abfragen, wie zum Beispiel das Generieren einer leeren Tabellenstruktur:
Kol1,kol2 in #t aus T wählen, wobei 1=0
Dieser Codetyp liefert keine Ergebnismenge zurück, verbraucht aber Systemressourcen, daher sollte er in so etwas geändert werden:
Tabelle erstellen #t(...)
12. Oft ist es eine gute Wahl, existieren statt in:
Wähle Num aus A, wo Num in (Um aus B auswählen)
Ersetzen Sie durch folgende Aussage:
Wählen Sie Num aus A aus, wo existiert (1 aus B wählen, wo Num=A.Num)
Dinge, auf die Sie beim Erstellen eines Indexes achten sollten:
1. Nicht alle Indizes sind für Abfragen gültig, SQL basiert auf den Daten in der Tabelle, um die Abfrage zu optimieren; wenn die Indexspalte eine große Menge an Datenduplizierung aufweist, verwenden SQL-Abfragen den Index möglicherweise nicht, zum Beispiel hat eine Tabelle Felder mit Geschlecht, männlich, weiblich, fast der Hälfte, und selbst wenn der Index auf Geschlecht basiert, spielt er keine Rolle für die Abfrageeffizienz.
2. Je mehr Indizes nicht besser sind, desto besser kann der Index die Effizienz des entsprechenden Selects verbessern, aber er verringert auch die Effizienz von Einfügen und Aktualisieren, da der Index beim Einfügen oder Aktualisieren neu aufgebaut werden kann, weshalb der Aufbau eines Index je nach Situation sorgfältig überlegt werden muss. Es ist am besten, nicht mehr als 6 Indizes in einer Tabelle zu haben, und wenn es zu viele sind, überlegen Sie, ob es notwendig ist, auf selten verwendeten Spalten Indizes zu erstellen.
3. Vermeiden Sie es, die Spalten der clusterierten Indexdaten so weit wie möglich zu aktualisieren, da die Reihenfolge der clusterierten Indexdatenspalten der physischen Speicherreihenfolge der Tabellendatensätze entspricht, und sobald sich der Spaltenwert ändert, führt dies zur Anpassung der Reihenfolge der gesamten Tabellendatensätze, was erhebliche Ressourcen beansprucht. Wenn das Anwendungssystem die geclusterten Indexspalten häufig aktualisieren muss, muss es prüfen, ob der Index als geclusterter Index aufgebaut werden sollte.
Weitere wichtige Punkte:
1. Versuchen Sie, numerische Felder zu verwenden und vermeiden Sie, Felder zu entwerfen, die nur numerische Informationen als Zeichen enthalten, da dies die Leistung von Abfragen und Verbindungen verringert und den Speicheraufwand erhöht. Das liegt daran, dass die Engine jedes Zeichen der Zeichenkette einzeln vergleicht, wenn Abfragen und Joins verarbeitet werden, während sie bei numerischen Typen nur einmal verglichen werden muss.
2. Verwenden Sie nirgendwo "select * from t", ersetzen Sie "*" durch eine bestimmte Feldliste und geben Sie keine nicht verwendeten Felder zurück.
3. Versuche, Tabellenvariablen statt temporärer Tabellen zu verwenden. Wenn die Tabellenvariable eine große Datenmenge enthält, beachten Sie, dass der Index sehr begrenzt ist (nur der Primärschlüsselindex).
4. Vermeiden Sie häufiges Erstellen und Löschen temporärer Tabellen, um den Verbrauch von Systemtabellenressourcen zu verringern.
5. Temporäre Tabellen sind nicht unbenutzbar, und deren angemessene Verwendung kann bestimmte Routinen effektiver machen, zum Beispiel wenn man wiederholt auf eine große Tabelle oder einen Datensatz in einer häufig verwendeten Tabelle referenzieren muss. Für einmalige Ereignisse ist es jedoch am besten, eine Exporttabelle zu verwenden.
6. Beim Erstellen einer temporären Tabelle, wenn die gleichzeitig eingefügten Datenmenge groß ist, kann man auswählen in statt 'Tabelle erstellen' verwenden, um zu vermeiden, dass eine große Anzahl von Logs verursacht wird, um die Geschwindigkeit zu verbessern; Wenn die Datenmenge nicht groß ist, solltest du, um die Ressourcen der Systemtabelle zu erleichtern, zuerst eine Tabelle erstellen und dann einfügen.
7. Wenn eine temporäre Tabelle verwendet wird, sollten alle temporären Tabellen am Ende der gespeicherten Prozedur explizit gelöscht, zuerst die Tabelle abgeschnitten und dann die Tabelle fallen lassen, um eine lange Sperre der Systemtabelle zu vermeiden.
8. Versuchen Sie, den Cursor nicht zu verwenden, da die Effizienz des Cursors schlecht ist; wenn die vom Cursor gesteuerten Daten mehr als 10.000 Zeilen überschreiten, sollten Sie eine Umschreibung in Betracht ziehen.
9. Bevor Sie die cursorbasierte Methode oder die temporäre Tabellenmethode verwenden, sollten Sie zunächst nach mengenbasierten Lösungen suchen, um das Problem zu lösen, und die mengenbasierte Methode ist in der Regel effektiver.
10. Wie bei temporären Tabellen ist der Cursor nicht unbrauchbar. Die Verwendung FAST_FORWARD Cursors für kleine Datensätze ist oft besser als andere Reihen-für-Zeilen-Verarbeitungsmethoden, besonders wenn man mehrere Tabellen referenzieren muss, um die benötigten Daten zu erhalten. Routinen, die "total" im Ergebnisset enthalten, sind in der Regel schneller als solche, die mit dem Cursor ausgeführt werden. Wenn die Entwicklungszeit es erlaubt, können sowohl cursorbasierte als auch mengenbasierte Methoden ausprobiert werden, um herauszufinden, welche besser funktioniert.
11. Setze SET NOCOUNT ON am Anfang aller gespeicherten Prozeduren und Trigger und setze am Ende SET NOCOUNT OFF. Es ist nicht notwendig, DONE_IN_PROC Nachrichten nach Ausführung jeder Anweisung der gespeicherten Prozedur und des Triggers an den Client zu senden.
12. Versuchen Sie, große Daten nicht an den Client zurückzugeben; wenn das Datenvolumen zu groß ist, sollten Sie prüfen, ob die entsprechende Nachfrage angemessen ist.
13. Versuche, große Transaktionsoperationen zu vermeiden und die Nebenläufigkeit des Systems zu verbessern.
|
Veröffentlicht am 11.05.2018 13:57:06
|
|
|
Veröffentlicht am 17.05.2018 10:12:27