SQL-Abfragen optimieren: So schreiben Sie leistungsstarke SQL-Anweisungen

1. Zunächst 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 und generiert, wenn beispielsweise eine SQL-Anweisung zum Abfragen eines Datensatzes verwendet wird Bei einer Tabelle mit 100.000 Datensätzen wählt der Abfrageoptimierer die Methode „Indexsuche“. Wenn die Tabelle archiviert ist und derzeit nur noch 5.000 Datensätze vorhanden sind, ändert der Abfrageoptimierer den Plan und verwendet die Methode „Vollständiger Tabellenscan“. .

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

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

(2) Sind die vom Abfrageoptimierer erhaltenen statistischen Datenbankinformationen aktuell und korrekt?

2. Vereinheitlichen Sie die Schreibmethode von SQL-Anweisungen

Bei den folgenden beiden SQL-Anweisungen gehen Programmierer davon aus, dass sie gleich sind, der Datenbankabfrageoptimierer geht jedoch davon aus, dass sie unterschiedlich sind.

select*from dual 

select*From dual

Wenn der Fall anders ist, geht der Abfrageanalysator tatsächlich davon aus, dass es sich um zwei verschiedene SQL-Anweisungen handelt und diese zweimal analysiert werden müssen. Generieren Sie 2 Ausführungspläne. Daher sollten Sie als Programmierer sicherstellen, dass überall dieselbe Abfrageanweisung konsistent ist. Selbst ein Leerzeichen mehr funktioniert nicht!

3. Schreiben Sie SQL-Anweisungen nicht zu kompliziert

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 anhand der Teilmenge ausgeführt. Diese Art von einstufiger verschachtelter Anweisung ist relativ häufig, erfahrungsgemäß jedoch, wenn es mehr als drei Verschachtelungsebenen gibt , gibt der Abfrageoptimierer leicht einen falschen Ausführungsplan aus. 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 ist, 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.

4. Verwenden Sie „temporäre Tabelle“, um Zwischenergebnisse vorübergehend zu speichern

Eine wichtige Möglichkeit zur Vereinfachung von SQL-Anweisungen besteht darin, temporäre Tabellen zum vorübergehenden Speichern von Zwischenergebnissen zu verwenden. Die temporären Ergebnisse werden jedoch in temporären Tabellen gespeichert, und nachfolgende Abfragen erfolgen in tempdb, wodurch mehrere vermieden werden können Das Scannen der Haupttabelle reduziert auch die Blockierung durch „gemeinsame Sperren“ und „Aktualisierungssperren“ während der Programmausführung erheblich, wodurch Blockierungen reduziert und die Parallelitätsleistung verbessert werden.

5. OLTP-System-SQL-Anweisungen müssen Bind-Variablen

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

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

select*from orderheader where changetime >@chgtime

Die @chgtime-Variable kann einen beliebigen Wert übergeben, sodass eine große Anzahl ähnlicher Abfragen den Ausführungsplan wiederverwenden kann, wodurch die Belastung der Datenbank durch das Parsen von SQL-Anweisungen erheblich verringert werden kann. Einmal analysieren und mehrmals wiederverwenden ist das Prinzip zur Verbesserung der Datenbankeffizienz.

6. Peeping-Bindungsvariablen

Alles hat zwei Seiten und Bind-Variablen sind auf die meisten OLTP-Prozesse 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 % der Werte 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. 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.

7. Verwenden Sie begin tran

nur bei Bedarf 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 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 oder keiner von ihnen ist erfolgreich. begin tran kann eine solche Rolle spielen. Es kann mehrere SQL-Anweisungen gleichzeitig ausführen und sie 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.

8. 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 Oracle nicht erforderlich, da Oracle über eine vernünftigere Struktur und einen Rückgängig-Tabellenbereich verfügt, um die „Datenvorschau“ zu speichern Geändert Es wurde noch nicht festgeschrieben. Was Sie also lesen, ist die Kopie vor der Änderung, die im Undo-Tabellenbereich abgelegt wird. 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 zum „Einfügen, Löschen und Ändern“ verwendet werden, kann kein Nolock hinzugefügt werden!

(2) In der abgefragten Tabelle kommt es häufig zu Seitenaufteilungen. Verwenden Sie daher nolock mit Vorsicht!

(3) Durch die Verwendung einer temporären Tabelle können auch die „vorherigen Daten“ gespeichert werden, die eine ähnliche Funktion wie der Rückgängig-Tabellenbereich von Oracle haben,

Wenn Sie temporäre Tabellen zur Verbesserung der Parallelitätsleistung verwenden können, verwenden Sie kein Nolock.

9. Der Clustered-Index basiert nicht auf den sequentiellen Feldern der Tabelle und die Tabelle ist anfällig für Seitenteilungen

In der Bestelltabelle gibt es beispielsweise die Bestellnummer orderid und die Kundennummer contactid. Zu welchem ​​Feld soll 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.

Beispielsweise ist die „Kontakt-ID“ von „Zhang San“ 001, dann müssen die Bestellinformationen von „Zhang San“ auf der ersten Datenseite dieser Tabelle platziert werden. Wenn „Zhang San“ heute eine neue Bestellung aufgibt, dann die Bestellung Die Informationen können nicht auf der letzten Seite der Tabelle platziert werden, sondern auf der ersten Seite! 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.

Die Indizes von SQL Server unterscheiden sich von den Indizes 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 änderte sich nach der Neuerstellung des Clustered-Index, da die Neuerstellung des Clustered-Index eine Neuanordnung der Daten in der Tabelle bedeutet. Die ursprünglichen Lücken sind verschwunden und die Seitenfüllrate ist beim Einfügen von Daten häufig sehr hoch, sodass 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 vermeiden, den Clustered-Index neu zu erstellen? Das ist eine Frage, über die man nachdenken sollte!

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

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

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

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

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

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

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

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

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

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件，尽量减小A、B表的结果集

(3)    如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

Das obige ist der detaillierte Inhalt vonSQL-Abfragen optimieren: So schreiben Sie leistungsstarke SQL-Anweisungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!