Ich optimiere eine Abfrage unter SQL Server 2005. Ich habe eine einfache Abfrage für mytable mit ungefähr 2 Millionen Zeilen:

SELECT id, num
FROM mytable
WHERE t_id = 587

Das Feld id ist der Primärschlüssel (Clustered-Index), und für das Feld t_id ist ein Nicht-Clustered-Index vorhanden.

Der Abfrageplan für die obige Abfrage enthält sowohl einen Clustered Index Seek als auch einen Index Seek. Anschließend wird eine verschachtelte Schleife (Inner Join) ausgeführt, um die Ergebnisse zu kombinieren. Das STATISTICS IO zeigt 3325 Seitenlesevorgänge an.

Wenn ich die Abfrage auf Folgendes ändere, führt der Server nur 6 Seitenlesevorgänge und nur eine einzelne Indexsuche ohne Join aus:

SELECT id
FROM mytable
WHERE t_id = 587

Ich habe versucht, einen Index für die Spalte num und einen Index für num und tid hinzuzufügen. Keiner der Indizes wurde vom Server ausgewählt.

Ich möchte die Anzahl der Seitenlesevorgänge reduzieren, aber trotzdem die Spalten id und num abrufen.

3
Joshua Olson 26 Juni 2019 im 02:18

3 Antworten

Beste Antwort

Der folgende Index sollte optimal sein:

CREATE INDEX idx ON MyTable (t_id)
INCLUDE (num)

Ich kann mich nicht erinnern, ob INCLUDEd-Spalten 2005 eine gültige Syntax waren. Möglicherweise müssen Sie Folgendes verwenden:

CREATE INDEX idx ON MyTable (t_id, num)

Die Spalte [id] wird in den Index aufgenommen, da es sich um den Clustered Key handelt.

2
MJH 25 Juni 2019 im 23:27

Der optimale Index wäre (t_id, num, id).

Der Grund für Ihre Abfrage ist wahrscheinlich, dass mehrere Zeilen ausgewählt werden. Ich frage mich, ob eine Umformulierung der Abfrage die Leistung verbessern würde:

SELECT t.id, t.num
FROM mytable t
WHERE EXISTS (SELECT 1
              FROM my_table t2
              WHERE t2.t_id = 587 AND t2.id = t.id
             );
1
Gordon Linoff 25 Juni 2019 im 23:27

Lassen Sie uns das Problem klären und dann die Lösungen diskutieren, um es zu verbessern:

Sie haben eine Tabelle (nennen wir sie tblTest1 und enthält 2 Millionen Datensätze) mit einem Clustered-Index für id und einem Nicht-Clustered-Index für t_id, und Sie werden die Daten abfragen, die gefiltert werden die Daten mit Non Clustered Index und Abrufen der Spalten id und num.

SQL Server verwendet also den Non Clustered Index, um die Daten zu filtern (t_id=587). Nach dem Filtern der Daten muss SQL Server jedoch die Werte in den Spalten id und num speichern. Anscheinend verwendet SQL Server diesen Index, um die in den Spalten id und num gespeicherten Daten abzurufen, da Sie über einen Clustered-Index verfügen. Dies liegt daran, dass Blätter im Baum des nicht gruppierten Index den Wert des gruppierten Index enthalten. Aus diesem Grund wird der Operator "Schlüsselsuche" im Ausführungsplan angezeigt. Tatsächlich verwendet SQL Server das Index seek(NonCluster), um das t_id=587 zu finden, und verwendet dann das Key Lookup, um die num Daten abzurufen! (SQL Server verwendet diesen Operator nicht, um den Wert abzurufen wird in der Spalte id gespeichert, da Sie einen Clustered-Index haben und die Blätter im NonClustered-Index den Wert des Clustered-Index enthalten.

enter image description here

In Bezug auf den oben genannten Screenshot benötigt SQL Server bei Index Seek(NonClustred) und einem Key Lookup einen Nested Loop Join Operator, um die Daten in der num - Spalte mithilfe von {{X4 }} Operator. Tatsächlich verfügt SQL Server in dieser Phase über zwei separate Sätze: Der eine ist das Ergebnis des nicht gruppierten Indexbaums und der andere sind Daten innerhalb des gruppierten Indexbaums.

Basierend auf dieser Geschichte ist das Problem klar! Was passiert, wenn wir dem SQL Server sagen, dass er keine Schlüsselsuche durchführen soll? Dies führt dazu, dass der SQL Server die Abfrage auf kürzere Weise ausführt (keine Notwendigkeit zur Schlüsselsuche und anscheinend keine Notwendigkeit für den Join der verschachtelten Schleife!).

Um dies zu erreichen, müssen wir die INCLUDE - Spalte num im Baum des NonClustered-Index id angeben. In diesem Fall enthält das Blatt dieses Index die Daten der id -Spalte sowie die {{X3 }} Spaltendaten! Wenn wir sagen, dass der SQL Server Daten mithilfe von NonClustred Index finden und die Spalten id und num zurückgeben soll, muss er keine Schlüsselsuche durchführen!

Schließlich müssen wir INCLUDE das num im NonClustered Index {! Dank der Antwort von @MJH:

CREATE NONCLUSTERED INDEX idx ON tblTest1 (t_id)
INCLUDE (num)

enter image description here

Glücklicherweise hat SQL Server 2005 eine neue Funktion für NonClustered-Indizes bereitgestellt, die es ermöglicht, zusätzliche Spalten ohne Schlüssel in die Blattebene der NonClustered-Indizes aufzunehmen!

Weiterlesen:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017

Aber was passiert, wenn wir die Abfrage so schreiben?

SELECT id, num
FROM tblTest1 AS t1
WHERE 
EXISTS (SELECT 1
              FROM tblTest1 t2
              WHERE t2.t_id = 587 AND t2.id = t1.id
             )

Dies ist ein großartiger Ansatz, aber sehen wir uns den Ausführungsplan an:

enter image description here

Es ist klar, dass SQL Server eine Indexsuche (NonClustered) durchführen muss, um t_id = 587 zu finden und dann die Daten von Clustered Index mithilfe von Clustered Index Seek abzurufen. In diesem Fall erhalten wir keine nennenswerte Leistungsverbesserung.

Hinweis: Wenn Sie Indizes verwenden, benötigen Sie einen geeigneten Plan, um diese zu verwalten. Wenn die Indizes fragmentiert werden, verringert sich ihre Auswirkung auf die Abfrageleistung, und nach einer Weile können Leistungsprobleme auftreten! Sie benötigen einen geeigneten Plan, um sie zu reorganisieren und neu aufzubauen, wenn sie fragmentiert werden!

Lesen Sie mehr: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

1
Vahid Farahmandian 26 Juni 2019 im 05:42