Optimierung der SQL-Abfrageanweisung

Inhalt

1. Was ist ein Ausführungsplan? Von welchen Informationen hängt der Ausführungsplan ab?
2. Vereinheitlichen Sie die Schreibmethode von SQL-Anweisungen, um den Parsing-Aufwand zu reduzieren
4. Verwenden Sie „temporäre Tabellen“, um Zwischenergebnisse im SQL-Bereich zu speichern Anweisungen müssen eine feste Bindungsvariable
verwenden. 6. Bindungsvariablen-Snooping-Problem des geneigten Felds
7. Die Transaktion von begin tran sollte so klein wie möglich sein.
8. Nolock sollte zu einigen SQL-Abfrageanweisungen hinzugefügt werden
9 Nach dem Hinzufügen von Nolock kann es leicht zu übersprungenen oder wiederholten Lesevorgängen kommen.
Der Clustered-Index wird nicht erstellt sequentielles Feld der Tabelle, die Tabelle ist anfällig für Seitenaufteilungen
11 Verwenden Sie zusammengesetzte Indizes, um die Abfragegeschwindigkeit mehrerer Where-Bedingungen zu verbessern
13 Achten Sie darauf, dass Sie nicht den ersten verwenden %
14. SQL Server-Tabelle Drei Möglichkeiten zur Verbindung
15. Zeilennummer führt zum Scannen der Tabelle, es ist besser, temporäres Tabellen-Paging zu verwenden

Was ist ein Ausführungsplan? Von welchen Informationen hängt der Ausführungsplan ab?

Der Ausführungsplan ist ein von der Datenbank erstellter Abfrageplan, der auf den statistischen Informationen der SQL-Anweisung und zugehörigen Tabellen basiert. Dieser Plan wird automatisch vom Abfrageoptimierer analysiert 100.000 Datensätze extrahieren Um nach einem Datensatz in der Tabelle zu suchen, wählt der Abfrageoptimierer die Methode „Indexsuche“. Wenn die Tabelle archiviert ist und nur noch 5.000 Datensätze vorhanden sind, ändert der Abfrageoptimierer den Plan und verwendet „vollständiger Tabellenscan“. " "Weg.

Es ist ersichtlich, dass der Ausführungsplan nicht festgelegt, sondern „personalisiert“ ist. Bei der Erstellung eines korrekten „Ausführungsplans“ gibt es zwei wichtige Punkte:

Sagt die SQL-Anweisung dem Abfrageoptimierer klar, was er tun möchte?

Sind die vom Abfrageoptimierer ermittelten Datenbankstatistiken aktuell und korrekt?

Vereinheitlichen Sie die Schreibmethode von SQL-Anweisungen, um den Analyseaufwand zu reduzieren

Bei den folgenden beiden SQL-Anweisungen denken Programmierer, dass sie gleich sind, aber der Datenbankabfrageoptimierer denkt möglicherweise, dass sie unterschiedlich sind.

wählen Sie * aus Dual

Wählen Sie * aus Dual

Tatsächlich ist der Fall anders. Der Abfrageanalysator betrachtet es als zwei verschiedene SQL-Anweisungen und muss zweimal analysiert werden . . Generieren Sie 2 Ausführungspläne. Daher sollten Sie als Programmierer sicherstellen, dass überall dieselbe Abfrageanweisung konsistent ist. Selbst ein Leerzeichen mehr funktioniert nicht!

Reduzieren Sie die Verschachtelung von SQL-Anweisungen

Ich sehe oft, dass eine aus der Datenbank erfasste SQL-Anweisung beim Drucken so lang ist wie 2 A4-Blätter. Generell gilt, dass es bei solch komplexen Aussagen meist zu Problemen kommt. Ich habe diese zwei Seiten lange SQL-Anweisung verwendet, um den ursprünglichen Autor zu fragen, aber er sagte, es habe zu lange gedauert und er könne sie eine Weile nicht verstehen. Es ist denkbar, dass selbst der ursprüngliche Autor durch die SQL-Anweisung verwirrt wird und auch die Datenbank verwirrt wird.

Im Allgemeinen werden die Ergebnisse einer Select-Anweisung als Teilmenge verwendet und dann wird die Abfrage aus der Teilmenge ausgeführt. Diese Art von einstufiger verschachtelter Anweisung ist relativ häufig, aber erfahrungsgemäß, wenn es sie gibt Bei mehr als drei Verschachtelungsebenen kann der Abfrageoptimierer leicht falsche Ausführungspläne liefern. Weil es fassungslos war. Dinge wie künstliche Intelligenz sind letztendlich der menschlichen Auflösung unterlegen. Wenn den Menschen schwindelig wird, kann ich garantieren, dass auch der Datenbank schwindelig wird.

Darüber hinaus kann der Ausführungsplan wiederverwendet werden. Je einfacher die SQL-Anweisung, desto höher ist die Wahrscheinlichkeit einer Wiederverwendung. Solange sich ein Zeichen in einer komplexen SQL-Anweisung ändert, muss es erneut analysiert werden, und dann wird viel Müll in den Speicher gestopft. Es ist denkbar, wie ineffizient die Datenbank sein wird.

Verwenden Sie „temporäre Tabellen“, um Zwischenergebnisse vorübergehend zu speichern

Eine wichtige Möglichkeit, SQL-Anweisungen zu vereinfachen, besteht darin, temporäre Tabellen zum vorübergehenden Speichern von Zwischenergebnissen zu verwenden Als diese werden vorübergehend in der temporären Tabelle gespeichert, nachfolgende Abfragen werden in tempdb gespeichert. Dadurch können mehrere Scans der Haupttabelle im Programm vermieden werden. Außerdem wird die „Aktualisierungssperre“ während der Programmausführung erheblich reduziert, wodurch Blockierungen und Blockierungen reduziert werden Verbesserung der Parallelitätsleistung.

SQL-Anweisungen des OLTP-Systems müssen Bind-Variablen verwenden

Wählen Sie * aus Auftragskopf, wobei Änderungszeit > '2010-10-20 00:00:01' auswählt.

Wählen Sie * aus Auftragskopf, wobei Änderungszeit > ; '2010-09-22 00:00:01'

Der Abfrageoptimierer betrachtet die beiden oben genannten Anweisungen als unterschiedliche SQL-Anweisungen und muss zweimal analysiert werden. Wenn Sie Bind-Variablen verwenden, wählen Sie * aus orderheader aus, wobei changetime > @chgtime
@chgtime-Variable einen beliebigen Wert übergeben kann, sodass eine große Anzahl ähnlicher Abfragen den Ausführungsplan wiederverwenden kann, was die Datenbank erheblich reduzieren kann Belastung durch das Parsen von SQL-Anweisungen. Einmal analysieren und mehrmals wiederverwenden ist das Prinzip zur Verbesserung der Datenbankeffizienz.

Das Problem des Bindungsvariablen-Snoopings in verzerrten Feldern

Alles hat zwei Seiten. Bindungsvariablen sind auf die meisten OLTP-Verarbeitungen anwendbar, es gibt jedoch Ausnahmen. Zum Beispiel, wenn das Feld in der Where-Bedingung ein „schiefes Feld“ ist.

„Gekipptes Feld“ bedeutet, dass die meisten Werte in der Spalte gleich sind. Beispielsweise sind in einer Volkszählungstabelle in der Spalte „Ethnizität“ mehr als 90 % Han. Wenn also eine SQL-Anweisung die Bevölkerung der Han-Menschen im Alter von 30 Jahren abfragen möchte, muss die Spalte „ethnisch“ in die Where-Bedingung eingefügt werden. Zu diesem Zeitpunkt wird es ein großes Problem geben, wenn Sie die Bindungsvariable @nation verwenden.

Stellen Sie sich vor, wenn der erste von @nation übergebene Wert „Han“ ist, wählt der gesamte Ausführungsplan zwangsläufig den Tabellenscan aus. Dann ist der zweite übergebene Wert „Buyei“. Es liegt auf der Hand, dass der Anteil von „Buyi“ nur ein Zehntausendstel betragen darf, daher sollte die Indexsuche verwendet werden. Da jedoch der zum ersten Mal analysierte Ausführungsplan von „Han“ wiederverwendet wird, wird die Tabellenscanmethode auch zum zweiten Mal verwendet. Dieses Problem ist das berühmte „Bind-Variablen-Snooping“. Es wird empfohlen, keine Bind-Variablen für „verzerrte Felder“ zu verwenden.

Begin-Tran-Transaktionen sollten so klein wie möglich sein

In SQL Server ist eine SQL-Anweisung standardmäßig eine Transaktion und wird standardmäßig festgeschrieben, nachdem die Anweisung ausgeführt wurde. Tatsächlich handelt es sich dabei um eine minimierte Form von begin tran, genau wie am Anfang jeder Anweisung ein begin tran und am Ende ein commit impliziert wird.
In einigen Fällen müssen wir begin tran explizit deklarieren. Wenn wir beispielsweise „Einfüge-, Lösch- und Änderungsvorgänge“ durchführen, müssen wir mehrere Tabellen gleichzeitig ändern Tabellen sind erfolgreich oder keine davon ist erfolgreich. begin tran kann eine solche Rolle spielen. Es kann mehrere SQL-Anweisungen gleichzeitig ausführen und schließlich gemeinsam festschreiben. Der Vorteil besteht darin, dass die Datenkonsistenz gewährleistet ist, aber nichts perfekt ist. Der von Begin tran gezahlte Preis besteht darin, dass vor der Übermittlung alle durch SQL-Anweisungen gesperrten Ressourcen erst freigegeben werden können, wenn sie festgeschrieben werden.
Es ist ersichtlich, dass die Leistung der Datenbank schlecht ist, wenn Begin tran zu viele SQL-Anweisungen abfängt. Bevor die große Transaktion festgeschrieben wird, werden zwangsläufig andere Anweisungen blockiert, was zu vielen Blockaden führt.
Das Prinzip der Verwendung von Begin tran besteht darin, dass unter der Voraussetzung der Gewährleistung der Datenkonsistenz gilt: Je weniger SQL-Anweisungen von begin tran abgefangen werden, desto besser! In einigen Fällen können Trigger zum Synchronisieren von Daten verwendet werden, und begin tran wird nicht unbedingt verwendet.

Einige SQL-Abfrageanweisungen sollten mit Nolock hinzugefügt werden

Das Hinzufügen von Nolock zu SQL-Anweisungen ist ein wichtiges Mittel zur Verbesserung der gleichzeitigen Leistung von SQL Server. Dies ist in Oracle aufgrund der Struktur von Oracle nicht erforderlich Der Einfachheit halber gibt es einen Rückgängig-Tabellenbereich zum Speichern des „Datenvorgängers“. Wenn die Daten während der Änderung nicht festgeschrieben wurden, lesen Sie die Kopie, bevor sie geändert wurde, und platzieren Sie die Kopie der Undo-Tabellenbereich. Auf diese Weise können Oracles Lese- und Schreibvorgänge unabhängig voneinander erfolgen, weshalb Oracle weithin gelobt wird. Das Lesen und Schreiben von SQL Server blockiert sich gegenseitig, um die Parallelitätsleistung zu verbessern. Nolock kann zu einigen Abfragen hinzugefügt werden, sodass das Schreiben während des Lesens zulässig ist. Der Nachteil besteht jedoch darin, dass nicht festgeschriebene schmutzige Daten gelesen werden können. Es gibt drei Prinzipien für die Verwendung von Nolock.

(1) Wenn die Abfrageergebnisse für „Einfügen, Löschen und Ändern“ verwendet werden, kann kein Nolock hinzugefügt werden!

(2) Die abgefragte Tabelle weist häufige Seitenaufteilungen auf. Verwenden Sie Nolock daher mit Vorsicht!

(3) Temporäre Tabellen können auch zum Speichern von „Datenvorschauen“ verwendet werden, die ähnlich wie der Undo-Tablespace von Oracle funktionieren.

Wenn temporäre Tabellen zur Verbesserung der Parallelitätsleistung verwendet werden können, tun Sie dies nicht Verwenden Sie Nolock.

Nach dem Hinzufügen von Nolock können Sie Tabellen abfragen, die häufig Seitenteilungen aufweisen, was dazu führt, dass Lesevorgänge übersprungen oder wiederholt werden.

Nach dem Hinzufügen von Nolock können Sie Abfragen beim „Einfügen, Löschen und Ändern“ durchführen ". „Einfügen, Löschen, Ändern“ tritt auf. In einigen Fällen ist eine Seitenaufteilung unvermeidlich, sobald die Datenseite voll ist, und zu diesem Zeitpunkt erfolgt eine Nolock-Abfrage. Beispielsweise kann es sich um den Datensatz handeln, der auf Seite 100 gelesen wurde Da die Seite geteilt und in Seite 101 unterteilt ist, kann dies dazu führen, dass die Nolock-Abfrage beim Lesen von Seite 101 die Daten wiederholt liest, was zu einem „wiederholten Lesen“ führt. Wenn die Daten auf Seite 100 vor dem Lesen in Seite 99 aufgeteilt werden, kann es sein, dass die Nolock-Abfrage den Datensatz übersieht und ein „Überspringen“ verursacht.

Der oben erwähnte Freund hat nach dem Hinzufügen von Nolock Fehler bei einigen Vorgängen gemeldet. Es wird geschätzt, dass die Nolock-Abfrage zu wiederholten Lesevorgängen führt. Wenn zwei identische Datensätze in andere Tabellen eingefügt werden, kommt es natürlich zu einem Primärschlüsselkonflikt.

Der Clustered-Index basiert nicht auf dem Sequenzfeld der Tabelle und die Tabelle ist anfällig für Seitenteilungen

In der Bestelltabelle gibt es beispielsweise die Bestellnummer orderid und die Kundennummer, Kontakt-ID. Wo soll der Clustered-Index hinzugefügt werden? Was ist mit den Feldern? Bei dieser Tabelle werden die Bestellnummern nacheinander hinzugefügt. Wenn der Bestell-ID ein Clustered-Index hinzugefügt wird, werden die neuen Zeilen am Ende hinzugefügt, sodass es nicht häufig zu Seitenteilungen kommt. Da die meisten Abfragen jedoch auf Kundenzahlen basieren, ist es nur sinnvoll, einen Clustered-Index zu contactid hinzuzufügen. Für die Bestelltabelle ist contactid kein sequentielles Feld.

Zum Beispiel lautet die „Kontakt-ID“ von „Zhang San“ 001, dann müssen die Bestellinformationen von „Zhang San“ auf der ersten Datenseite dieser Tabelle platziert werden Bestellen Sie noch heute Eine Bestellung, dann können die Bestellinformationen nicht auf der letzten Seite der Tabelle, sondern auf der ersten Seite platziert werden! Was ist, wenn die erste Seite voll ist? Leider müssen alle Daten in dieser Tabelle nach hinten verschoben werden, um Platz für diesen Datensatz zu schaffen.

Der Index von SQL Server unterscheidet sich vom Index von Oracle. Der Clustered-Index von SQL Server sortiert die Tabelle tatsächlich in der Reihenfolge der Clustered-Index-Felder, was der indexorganisierten Tabelle von Oracle entspricht. Der Clustered-Index von SQL Server ist eine Organisationsform der Tabelle selbst und daher sehr effizient. Aus diesem Grund wird ein Datensatz beim Einfügen nicht zufällig platziert, sondern auf der Datenseite, wo er in der richtigen Reihenfolge platziert werden soll. Wenn auf dieser Datenseite kein Platz vorhanden ist, führt dies zu Seitenteilungen. Der Clustered-Index basiert also offensichtlich nicht auf den sequentiellen Feldern der Tabelle, und die Tabelle ist anfällig für Seitenteilungen.

Ich bin einmal auf eine Situation gestoßen, in der die Einfügungseffizienz eines Freundes nach der Neuindizierung einer bestimmten Tabelle erheblich abnahm. Es wird geschätzt, dass die Situation wahrscheinlich so ist. Der Clustered-Index der Tabelle wird möglicherweise nicht auf den sequentiellen Feldern der Tabelle erstellt. Die Tabelle wird häufig archiviert, sodass die Daten der Tabelle in einem spärlichen Zustand vorliegen. Beispielsweise hat Zhang San 20 Bestellungen aufgegeben, aber in den letzten drei Monaten gab es nur 5 Bestellungen. Die Archivierungsstrategie besteht darin, die Daten der letzten 15 Monate aufzubewahren, sodass 15 offene Stellen übrig bleiben werden in die Einfügung „Umgewidmet“ eingetragen, sobald sie auftreten. In diesem Fall erfolgt keine Seitenteilung, da freie Plätze verfügbar sind. Allerdings ist die Abfrageleistung relativ gering, da die Abfrage diese leeren Positionen ohne Daten scannen muss.

Die Situation hat sich nach der Neuerstellung des Clustered-Index geändert, da die Neuerstellung des Clustered-Index eine Neuanordnung der Daten in der Tabelle bedeutet und die Seitenfüllrate beim Einfügen von Daten sehr hoch ist auftreten, so dass die Leistung erheblich abnimmt.

Sollten wir für Tabellen, deren Clustered-Indizes nicht auf sequentiellen Feldern basieren, eine niedrigere Seitenfüllrate angeben? Möchten Sie den Neuaufbau des Clustered-Index vermeiden? Das ist eine Frage, über die man nachdenken sollte!

Verwenden Sie zusammengesetzte Indizes, um die Abfragegeschwindigkeit für mehrere Where-Bedingungen zu verbessern.

Zusammengesetzte Indizes weisen normalerweise eine bessere Selektivität auf als ein einzelner Index. Darüber hinaus handelt es sich um einen Index, der speziell für eine bestimmte Where-Bedingung eingerichtet wurde. Er wurde sortiert, sodass die Abfragegeschwindigkeit schneller ist als bei einem einzelnen Index. Das führende Feld des zusammengesetzten Index muss ein „hochselektives“ Feld sein. Es gibt beispielsweise drei Felder: Datum, Geschlecht und Alter. Mal sehen, welches Feld als Führungsfeld verwendet werden soll? Offensichtlich sollte „Datum“ als führendes Feld verwendet werden. Das Datum ist das selektivste der drei Felder.

Hier gibt es eine Ausnahme: Wenn das Datum auch das führende Feld des Clustered-Index ist, können Sie keinen zusammengesetzten Index erstellen und direkt zum Clustered-Index wechseln, was ebenfalls effizienter ist.

Bauen Sie einen Clustered-Index nicht in einen „zusammengesetzten Index“ um. Je einfacher der Clustered-Index, desto besser und je höher die Selektivität, desto besser! Der Clustered-Index umfasst 2 Felder und ist tolerierbar. Wenn jedoch mehr als zwei Felder vorhanden sind, sollten Sie die Erstellung eines automatisch inkrementierenden Felds als Primärschlüssel in Betracht ziehen. Der Clustered-Index muss nicht der Primärschlüssel sein.

Wenn Sie Like für Fuzzy-Abfragen verwenden, sollten Sie versuchen, nicht das erste % zu verwenden

Manchmal ist es notwendig, einige Fuzzy-Abfragen durchzuführen, wie z. B.

Wählen Sie * von Kontakt wo aus Benutzername wie „%yue%“

Schlüsselwort %yue%. Da „%“ vor yue verwendet wird, muss die Abfrage die gesamte Tabelle durchsuchen. Fügen Sie nicht % vor dem Schlüsselwort hinzu.

Drei Möglichkeiten, SQL Server-Tabellen zu verknüpfen

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000 verfügt nur über eine Join-Methode – Nested Loop Join. Wenn die Ergebnismenge A klein ist, wird sie standardmäßig als äußere Oberfläche verwendet. Jeder Datensatz in A muss in B gescannt werden gescannt entspricht der Ergebnismenge A. Die Anzahl der Zeilen x die Anzahl der Zeilen in der Ergebnismenge B. Wenn also beide Ergebnismengen groß sind, ist das Ergebnis von Join schlecht.

SQL Server 2005 hat Merge Join hinzugefügt. Wenn das Verbindungsfeld von Tabelle A und Tabelle B zufällig das Feld ist, in dem sich der Clustered-Index befindet, wurde die Reihenfolge der Tabellen einfach geändert . Der Overhead dieser Art entspricht der Anzahl der Ergebnissatzzeilen in Tabelle A plus der Anzahl der Ergebnissatzzeilen in Tabelle B. Eine davon ist die Addition und die andere die Multiplikation. Es ist ersichtlich, dass der Effekt der Zusammenführungsverknüpfung ist viel besser als Nested Loop Join.

Wenn für das verbundene Feld kein Index vorhanden ist, ist die Effizienz von SQL2000 recht gering. SQL2005 bietet jedoch einen Hash-Join, der dem vorübergehenden Hinzufügen von Indizes zu den Ergebnismengen der Tabellen A und B entspricht. Die Effizienz von SQL2005 ist höher als die von SQL2000 und wurde erheblich verbessert, und ich denke, das ist ein wichtiger Grund.

Zusammenfassend sollten Sie beim Verbinden von Tabellen die folgenden Punkte beachten:

(1) Versuchen Sie, das Feld auszuwählen, in dem sich der Clustered-Index für das Verbindungsfeld

(2) Berücksichtigen Sie sorgfältig die Where-Bedingung und versuchen Sie, die Ergebnismenge der Tabellen A und B zu reduzieren

(3) Wenn viele Join-Verbindungsfelder keine Indizes haben und Sie immer noch SQL2000 verwenden, aktualisieren Sie sofort

Row_Number führt zum Tabellenscan. Es ist besser, eine temporäre Tabelle für das Paging zu verwenden.

Testergebnisse des ROW_Number-Pagings:

ROW_Number für das Paging verwenden: CPU-Zeit = 317265 Millisekunden, belegte Zeit = 423090 Millisekunden
Temporäre Tabelle für Paging verwenden: CPU-Zeit = 1266 Millisekunden, belegte Zeit = 6705 Millisekunden

ROW_Number-Implementierung basiert auf der Reihenfolge nach und die Auswirkung der Sortierung auf die Abfrage ist offensichtlich.

Andere

Beispielsweise schränken einige Schreibmethoden die Verwendung von Indizes ein

Wählen Sie * aus Tabellenname wobei chgdate +7 < sysdate

Wählen Sie * aus Tabellenname wobei chgdate < sysdate -7