Frage SQL Server Clustered Index - Reihenfolge der Indexfrage


Ich habe einen Tisch wie folgt:

keyA keyB data

keyA und keyB zusammen sind eindeutig, sind der Primärschlüssel meiner Tabelle und bilden einen Clustered-Index.

Es gibt 5 mögliche Werte von keyB, aber eine unbegrenzte Anzahl von möglichen Werten von keyA ,. KeyB wird in der Regel erhöht.

Zum Beispiel können die folgenden Daten auf zwei Arten geordnet werden, abhängig davon, welche Schlüsselspalte zuerst bestellt wird:

keyA keyB data
A    1    X
B    1    X
A    3    X
B    3    X
A    5    X
B    5    X
A    7    X
B    7    X

oder

keyA keyB data
A    1    X
A    3    X
A    5    X
A    7    X
B    1    X
B    3    X
B    5    X
B    7    X

Muss ich dem gruppierten Index mitteilen, welche der Schlüsselspalten weniger mögliche Werte hat, damit die Daten zuerst nach diesem Wert sortiert werden können? Oder spielt es keine Rolle in Bezug auf die Leistung, die zuerst bestellt wird?


7
2017-12-05 15:01


Ursprung


Antworten:


Sie sollten Ihren zusammengesetzten gruppierten Index zuerst mit der selektivsten Spalte bestellen. Dies bedeutet die Spalte mit den deutlichsten Werten im Vergleich zur Gesamtzahl der Zeilen.

"B * TREE-Indizes verbessern die Leistung von Abfragen, die einen kleinen Prozentsatz von Zeilen aus einer Tabelle auswählen." http://www.akadia.com/services/ora_index_selectivity.html?

Dieser Artikel ist für Oracle, aber immer noch relevant.

Wenn Sie eine Abfrage haben, die ständig ausgeführt wird und nur wenige Felder zurückgibt, können Sie einen zusammengesetzten Index erstellen, der alle Felder enthält - er muss nicht auf die Basistabelle zugreifen, sondern stattdessen Daten aus dem Index abrufen.

Der Kommentar von ligget78, der sicherstellt, dass die erste Spalte in einem zusammengesetzten Index erwähnt wird, ist wichtig, um sich daran zu erinnern.


12
2017-12-05 16:01



Wenn Sie einen Index (unabhängig von Cluster oder nicht) mit (keyA, keyB) erstellen, werden die Werte so geordnet, z. zuerst keyA, dann keyB (dies ist der zweite Fall in Ihrer Frage). Wenn Sie es andersherum möchten, müssen Sie angeben (keyB, keyA).

Es könnte leistungsabhängig sein, hängt natürlich von Ihrer Anfrage ab. Wenn Sie beispielsweise den Schlüssel (keyA, keyB) haben und die Abfrage wie WHERE keyB = ... aussieht (ohne keyA zu erwähnen), kann der Index nicht verwendet werden.


7
2017-12-05 15:09



Wie andere bereits gesagt haben, hängt die Reihenfolge davon ab, wie Sie sie im Indexerstellungsskript (oder der PK-Einschränkung) angeben. Eine Sache über Clustered-Indizes ist jedoch, dass es eine Menge zu beachten gibt.

Sie können eine bessere Gesamtleistung erzielen, wenn Sie Ihren Clustered-Index für etwas anderes als das PK verwenden. Wenn Sie beispielsweise ein Finanzsystem schreiben und Berichte fast immer auf dem Datum und der Uhrzeit einer Aktivität basieren (alle Aktivitäten für das vergangene Jahr usw.), ist möglicherweise ein gruppierter Index für diese Datumsspalte besser. Wie HLGEM sagt, kann die Sortierung auch von der Auswahl des Clustered-Indexes beeinflusst werden.

Clustered-Indizes können die Einfügungen auch stärker beeinflussen als andere Indizes. Wenn Sie ein hohes Volumen an Einfügungen haben und Ihr Clustered-Index sich in einer IDENTITY-Spalte befindet, kann es zu Konfliktproblemen für diesen bestimmten Teil der Festplatte kommen, da alle neuen Zeilen an derselben Stelle eingefügt werden.

Bei kleinen Nachschlagetabellen lege ich den Clustered Index immer nur auf den PK. Für Tabellen mit hoher Auswirkung ist es jedoch eine gute Idee, sich die Zeit zu nehmen, verschiedene mögliche Clustered-Indizes zu betrachten (und zu testen), bevor Sie die beste auswählen.


2
2017-12-05 15:38



Ich glaube, dass SQL Server es genau so anordnet, wie Sie es sagen. Es wird davon ausgegangen, dass Sie am besten wissen, wie Sie auf Ihren Index zugreifen.

In jedem Fall würde ich sagen, dass es eine gute Idee ist, wo genau es möglich ist, genau das zu spezifizieren, was Sie wollen, anstatt zu hoffen, dass die Datenbank es herausfinden wird.

Sie können es auch auf beide Arten versuchen, eine Reihe von repräsentativen Abfragen ausführen und dann die generierten Ausführungspläne vergleichen, um herauszufinden, welche für Sie am besten ist.


1
2017-12-05 15:07



Nur für den Fall, dass dies nicht offensichtlich ist: die Sortierreihenfolge Ihrer Index Versprochen nicht viel über die Sortierreihenfolge der ergibt eine Abfrage.

In Ihren Abfragen müssen Sie noch einen hinzufügen

ORDER BY KeyA, KeyB

oder

ORDER BY KeyB, KeyA

Der Optimierer freut sich vielleicht, die Daten, die bereits physisch im Index geordnet sind, wie gewünscht zu finden und etwas Zeit zu sparen, aber jede Abfrage, die Daten in einer bestimmten Reihenfolge liefern soll, muss am Ende eine ORDER BY-Klausel haben. Ohne eine Reihenfolge von, macht SQL Server keine Versprechen in Bezug auf die Reihenfolge eines Recordsets, oder sogar, dass es in der gleichen Reihenfolge von Abfrage zu Abfrage zurückkommt.


1
2017-12-05 16:28



Das Beste, was Sie tun können, ist, beide Lösungen auszuprobieren und die Ausführungszeit zu messen.

Nach meiner Erfahrung ist Index-Tuning alles andere als genau-Wissenschaft.

Vielleicht wäre keyB vor keyA in der Reihenfolge der Indexspalten besser


0
2017-12-05 15:07



Sie geben die Spalten in der Reihenfolge an, in der sie normalerweise in Berichten und Abfragen sortiert werden sollen.

Ich würde jedoch vorsichtig sein, einen mehrspaltigen gruppierten Index zu erstellen. Je nachdem, wie groß dieser Wert ist, können Sie einen großen Einfluss auf die Größe anderer Indizes haben, die Sie erstellen, da alle nicht gruppierten Indizes den darin enthaltenen Clustered-Index-Wert enthalten. Außerdem müssen die Zeilen neu geordnet werden, wenn sich die Werte häufig ändern, und es ist die Erfahrung, dass Schlüssel, die keine Ersatzschlüssel sind, sich häufiger ändern. Daher kann das Erstellen eines Clustered-Vice-Nonclustered-Indexes viel zeitaufwändiger für Serverressourcen sein, wenn Sie Werte haben, die sich wahrscheinlich ändern. Ich sage nicht, dass Sie das nicht tun sollten, da ich nicht weiß, welche Art von Daten Ihre Spalten tatsächlich enthalten (obwohl ich vermute, dass sie komplexer sind als A1, A2 usw.); Ich sage, dass Sie über die Konsequenzen davon nachdenken müssen. Es wäre wahrscheinlich eine gute Idee, BOL gründlich über Clustered Vice Nonclustered-Indizes zu lesen, bevor Sie sich dazu verpflichten.


0
2017-12-05 15:19



Denken Sie daran, dass der gruppierte Index die physische Reihenfolge ist, in der die Tabelle auf der Festplatte gespeichert ist.

Wenn Ihr Clustered-Index als ColA definiert ist, werden ColB-Abfragen schneller ausgeführt, wenn die Reihenfolge in der Reihenfolge Ihres Clustered-Indexes liegt. Wenn SQL B, A bestellen muss, muss nach der Ausführung sortiert werden, um die richtige Reihenfolge zu erreichen.

Mein Vorschlag ist, einen zweiten nicht gruppierten Index auf B, A hinzuzufügen. Abhängig von der Größe Ihrer Datenspalte ist es auch INCLUDE (lesen Sie die enthaltene Spalte), um Schlüsselabfragen zu vermeiden. Dies ist natürlich vorausgesetzt, dass diese Tabelle nicht stark eingefügt ist, da Sie immer die Abfragegeschwindigkeit vs. Schreibgeschwindigkeit ausgleichen müssen.

Realistisch gesehen sollte Ihr Clustered-Index die Reihenfolge darstellen, in der am wahrscheinlichsten auf die Daten zugegriffen wird, und ein empfindliches Gleichgewicht zwischen den Kosten für Einfügen und Update-IO beibehalten. Wenn Ihr Clustered-Index so aussieht, dass Sie ständig in die Mitte der Seiten einfügen, können Sie dort Leistungseinbußen erleiden.

Wie andere gesagt haben, ohne die Tischlänge, Spaltengröße usw. zu kennen, gibt es keine richtige Antwort. Versuch und Irrtum mit einer hohen Dosis von Tests ist Ihre beste Wette.


0
2017-12-05 15:46



Ja, Sie sollten vorschlagen, normalerweise Abfrage-Engine versuchen, den besten Ausführungsplan und den Index zu finden, zu nutzen, aber manchmal ist es besser, Abfrage-Engine zu zwingen, den spezifischen Index zu verwenden. Bei der Planung für den Index sowie bei der Verwendung des Indexes in Ihrer Abfrage gibt es weitere Überlegungen. Zum Beispiel die Reihenfolge der Spalten im Index, die Reihenfolge der Spalten in der Where-Klausel. Sie können folgenden Link beziehen, um mehr zu erfahren über:

http://ashishkhandelwal.arkutil.com/sql-server/quick-and-short-database-indexes/

  • Best Practices für die Verwendung von Indizes
  • So erhalten Sie die besten Leistungsformindizes
  • Clustered Index Überlegungen
  • Überlegungen zu Nonclustered-Indizes

Ich bin mir sicher, dass dies Ihnen bei der Planung des Index helfen wird.


0
2018-06-26 06:27