Wie schreibe ich Hochleistungs-SQL?

Zuerst müssen wir verstehen, was ein Ausführungsplan ist.

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:

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

Sind die vom Abfrageoptimierer ermittelten Datenbankstatistiken aktuell und korrekt?

Empfohlener Kurs: MySQL-Tutorial.

Einheitliche Art, SQL-Anweisungen zu schreiben

Für die folgenden beiden SQL-Anweisungen denken Programmierer, dass sie gleich sind, Datenbankabfrageoptimierer Denke, es ist anders.

select*from dual 
select*From 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!

Schreiben Sie die SQL-Anweisung nicht zu komplex

Ich sehe oft, dass eine aus der Datenbank erfasste SQL-Anweisung mit 2 A4-Blättern ausgedruckt wird So lange. 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 wird das Ergebnis einer Select-Anweisung als Teilmenge verwendet und dann wird die Abfrage aus der Teilmenge ausgeführt. Diese Art von einstufiger verschachtelter Anweisung ist erfahrungsgemäß immer noch relativ häufig Wenn mehr als drei Ebenen verschachtelter Anweisungen festgelegt sind, kann der Abfrageoptimierer leicht einen falschen Ausführungsplan 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 Tabelle“, 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 Die Vorteile temporärer Tabellen gehen jedoch weit über diese hinaus. Temporäre Ergebnisse werden vorübergehend in der temporären Tabelle gespeichert. Dadurch können mehrere Scans der Haupttabelle im Programm vermieden werden. „Shared Lock“ blockiert während der Programmausführung. „Update Lock“ reduziert Blockierungen und verbessert die Parallelitätsleistung.

SQL-Anweisungen des OLTP-Systems müssen Bindevariablen verwenden

select*from orderheader where changetime >'2010-10-20 00:00:01' 
select*from orderheader where changetime >'2010-09-22 00:00:01'

Die beiden oben genannten Anweisungen werden vom Abfrageoptimierer als unterschiedliche SQL-Anweisungen betrachtet und müssen zweimal analysiert werden. Wenn Sie die Bindungsvariable

select*from orderheader where changetime >@chgtime

@chgtime-Variable verwenden, können Sie einen beliebigen Wert übergeben, sodass eine große Anzahl ähnlicher Abfragen den Ausführungsplan wiederverwenden kann, was den Aufwand für das Parsen von SQL-Anweisungen erheblich reduzieren kann Datenbank. Einmal analysieren und mehrmals wiederverwenden ist das Prinzip zur Verbesserung der Datenbankeffizienz.

Blick auf Bindungsvariablen

Alles hat zwei Seiten, Bindungsvariablen sind auf die meisten OLTP-Verarbeitungen anwendbar, aber es gibt 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. In einem Volkszählungsformular sind beispielsweise 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 nur bei Bedarf verwenden

Eine SQL-Anweisung in SQL Server ist standardmäßig eine Transaktion und wird standardmäßig festgeschrieben, nachdem die Anweisung ausgeführt wurde. Tatsächlich handelt es sich hierbei 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ügungs-, Lösch- und Änderungsvorgänge“ durchführen, müssen wir mehrere Tabellen gleichzeitig ändern Tabellen erfolgreich ist oder keine der Änderungen erfolgreich ist. 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 schrecklich sein wird, 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 Parallelitätsleistung von SQL Server. Dies ist in nicht erforderlich Oracle, da die Struktur von Oracle vernünftiger ist und es einen Rückgängig-Tabellenbereich zum Speichern der „vorherigen Daten“ gibt. Wenn die Daten während der Änderung nicht festgeschrieben wurden, lesen Sie die Kopie, bevor sie geändert wurde Die Kopie wird im Undo-Tabellenbereich abgelegt. 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, 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) Sie können temporäre Tabellen auch zum Speichern von „Datenvoraussagen“ verwenden, die wie der Rückgängig-Tabellenbereich von Oracle funktionieren.

Wenn Sie temporäre Tabellen zur Verbesserung der Parallelitätsleistung verwenden können, verwenden Sie diese nicht nolock .

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

Zum Beispiel ist dies in der Bestelltabelle der Fall die Bestellnummer orderid und die Kundennummer contactid. In welchem ​​Feld soll dann der Clustered-Index hinzugefügt werden? 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 Kunden-IDs basieren, ist es nur sinnvoll, einen Clustered-Index zur Kontakt-ID 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 häufig sehr hoch ist . , daher sinkt die Leistung erheblich.

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!

Nolock zu Abfragetabellen hinzufügen, bei denen es häufig zu Seitenteilungen kommt, die leicht zu übersprungenen oder wiederholten Lesevorgängen führen können

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

Das obige ist der detaillierte Inhalt vonWie schreibe ich Hochleistungs-SQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!