Warum kann ich nicht einfach einen Index hinzufügen, der alle Spalten umfasst?

Question

Ich habe eine Tabelle in der SQL Server-Datenbank, in der ich so schnell wie möglich Daten suchen und abrufen möchte. Es ist mir egal, wie lange das Einfügen in die Tabelle dauert, ich bin nur an der Geschwindigkeit interessiert, mit der ich Daten abrufen kann.

Das Problem ist, dass auf die Tabelle mit 20 oder mehr verschiedenen Arten von Abfragen zugegriffen wird. Das macht es zu einer mühsamen Aufgabe, einen Index hinzuzufügen, der speziell für jede Abfrage entwickelt wurde. Ich erwäge stattdessen, einfach einen Index hinzuzufügen, der ALLE Spalten der Tabelle umfasst. Das ist nichts, was man normalerweise in einem "guten" Datenbankdesign tun würde, also nehme ich an, dass es einen guten Grund gibt, warum ich es nicht tun sollte.

Kann mir jemand sagen, warum ich das nicht tun sollte?

UPDATE: Ich vergaß zu erwähnen, dass mir auch die Größe meiner Datenbank egal ist. Es ist OK, dass es bedeutet, dass meine Datenbank größer wird, als es nötig wäre

Answer 1

Zunächst einmal kann ein Index in SQL Server nur maximal 900 Bytes in seinem Indexeintrag haben. Das allein macht es unmöglich, einen Index mit allen Spalten zu haben.

Und vor allem: Ein solcher Index macht überhaupt keinen Sinn. Was wollen Sie damit erreichen?

Bedenken Sie Folgendes: Wenn Sie einen Index auf (LastName, FirstName, Street, City) wird dieser Index ノット verwendet werden können, um Abfragen zu beschleunigen

• FirstName allein
• City
• Street

Dieser Index wäre nützlich für die Suche nach

• (LastName) , oder
• (LastName, FirstName) , oder
• (LastName, FirstName, Street) , oder
• (LastName, FirstName, Street, City)

aber wirklich nichts anderes - jedenfalls nicht, wenn Sie nur nach Street oder einfach City !

Die Reihenfolge der Spalten in Ihrem Index macht einen großen Unterschied, und der Abfrageoptimierer kann nicht einfach eine beliebige Spalte in der Mitte eines Indexes für Suchvorgänge verwenden.

Denken Sie an Ihr Telefonbuch: Es ist wahrscheinlich nach Nachname, Vorname und vielleicht Straße geordnet. Hilft Ihnen diese Indizierung also, alle "Joe's" in Ihrer Stadt zu finden? Alle Leute, die in der "Main Street" wohnen? Nein - Sie können zuerst nach dem Nachnamen suchen - dann werden Sie innerhalb dieses Datensatzes spezifischer. Ein Index über alles hilft nicht, die Suche für alle Spalten zu beschleunigen. überhaupt .

Wenn Sie die Möglichkeit haben möchten, zu suchen nach Street - müssen Sie einen separaten Index für (Street) (und möglicherweise eine oder zwei weitere sinnvolle Spalten).

Wenn Sie die Möglichkeit haben möchten, zu suchen nach Occupation oder was auch immer - dafür brauchen Sie einen anderen spezifischen Index.

Nur weil Ihre Spalte in einem Index vorhanden ist, bedeutet dies nicht, dass alle Suchvorgänge für diese Spalte beschleunigt werden!

Die Hauptregel lautet: Verwenden Sie so wenig Indizes wie möglich - zu viele Indizes können für ein System sogar schlimmer sein als gar keine Indizes.... bauen Sie Ihr System auf, überwachen Sie seine Leistung und finden Sie die Abfragen, die am meisten kosten - optimieren Sie diese dann, z. B. durch Hinzufügen von Indizes.

Indizieren Sie nicht einfach blind jede Spalte, nur weil Sie es können - das ist eine Garantie für eine lausige Systemleistung - jeder Index erfordert auch Wartung und Pflege. Je mehr Indizes Sie haben, desto mehr werden Ihre INSERT-, UPDATE- und DELETE-Operationen leiden (langsamer werden), da alle diese Indizes aktualisiert werden müssen.

Answer 2

Sie haben ein grundlegendes Missverständnis über die Funktionsweise von Indizes.

Lesen Sie diese Erklärung " wie mehrspaltige Indizes funktionieren ".

Die nächste Frage, die Sie sich stellen könnten, lautet: Warum nicht die Schaffung von ein Index pro Spalte -aber das ist auch eine Sackgasse, wenn Sie versuchen, die beste ausgewählte Leistung zu erreichen.

Sie haben vielleicht das Gefühl, dass es eine mühsam Aufgabe, aber ich würde sagen, es ist eine erforderlich Aufgabe, sorgfältig zu indizieren. Schlampige Indizierung schlägt zurück, wie in dieses Beispiel .

Hinweis: Ich bin der festen Überzeugung, dass sich die richtige Indizierung auszahlt, und ich weiß, dass viele Leute genau die gleichen Fragen haben wie Sie. Deshalb schreibe ich ein kostenloses Buch darüber. Die obigen Links verweisen auf die Seiten, die Ihnen bei der Beantwortung Ihrer Frage helfen könnten. Vielleicht möchten Sie das Buch aber auch von der Anfang .

Answer 3

Ich glaube, der Fragesteller möchte Folgendes wissen

'Warum kann ich keinen Index erstellen? :

create index index_name
on table_name
(
    *
)

Die Probleme, die damit verbunden sind, wurden behoben.

Da es sich aber so anhört, als würden sie MS-SQL-Server verwenden. Es ist nützlich zu wissen, dass Sie Nicht-Schlüsselspalten in einen Index aufnehmen können, so dass die Werte dieser Spalten für den Abruf aus dem Index verfügbar sind, aber nicht als Auswahlkriterien verwendet werden können:

create index index_name
on table_name
(
    foreign_key
)
include (a,b,c,d) -- every column except foreign key

Ich habe zwei Tabellen mit einer Million identischer Zeilen erstellt

Ich habe Tabelle A wie folgt indiziert

create nonclustered index index_name_A
on A
(
    foreign_key -- this is a guid
)

und Tabelle B wie folgt

create nonclustered index index_name_B
on B
(
    foreign_key -- this is a guid
)
include (id,a,b,c,d) -- ( every key except foreign key)

Es ist keine Überraschung, dass Tabelle A etwas schneller eingefügt werden konnte.

aber wenn ich und führte diese diese Abfragen

select * from A where foreign_key = @guid
select * from B where foreign_key = @guid

Sql-Server hat in Tabelle A nicht einmal den Index verwendet, sondern einen Tabellenscan durchgeführt und sich über einen fehlenden Index mit id,a,b,c,d beschwert.

Bei Tabelle B war die Abfrage über 50-mal schneller, und das bei viel weniger io

die Abfrage von A zur Verwendung des Index zu zwingen, hat sie nicht schneller gemacht

select * from A where foreign_key = @guid
select * from A with (index(index_name_A)) where foreign_key = @guid

Answer 4

...wenn man einen Index hinzufügt, der alle Spalten enthält, und eine Abfrage diesen Index tatsächlich verwenden könnte, würde sie ihn in der Reihenfolge des Primärschlüssels durchsuchen. Das bedeutet, dass fast jeder Datensatz abgefragt wird. Die durchschnittliche Suchzeit wäre O(n/2)... genauso lang wie die Suche in der eigentlichen Datenbank.

Sie müssen eine bit viel über Indizes.

Es könnte hilfreich sein, wenn Sie einen Index für eine Tabelle als etwas wie ein Dictionary in C# betrachten.

var nameIndex = new Dictionary<String, List<int>>();

Das bedeutet, dass die Spalte name indiziert ist und eine Liste von Primärschlüsseln zurückgibt.

var nameOccupationIndex = new Dictionary<String, List<Dictionary<String, List<int>>>>();

Das bedeutet, dass die Spalten "Name" und "Beruf" indiziert sind. Stellen Sie sich nun vor, der Index enthielte 10 verschiedene Spalten, die so tief verschachtelt sind, dass sie jede einzelne Zeile in Ihrer Tabelle enthalten.

Allerdings funktioniert das nicht ganz so. Aber es sollte Ihnen eine Vorstellung davon geben, wie Indizes funktionieren könnten, wenn sie in C# implementiert werden. Sie müssen Indizes erstellen, die auf einem oder zwei Schlüsseln basieren, die häufig abgefragt werden, so dass der Index nützlicher ist als das Scannen der gesamten Tabelle.

Answer 5

Wenn es sich um einen Data-Warehouse-Vorgang handelt, bei dem Abfragen in hohem Maße für READ-Abfragen optimiert sind, und wenn Sie 20 Möglichkeiten haben, die Daten zu zerlegen, z. B.

WHERE-Klausel beinhaltet

 Q1: status, type, customer
 Q2: price, customer, band
 Q3: sale_month, band, type, status
 Q4: customer
 etc

Und Sie haben auf jeden Fall genügend schnellen Speicherplatz zur Verfügung, dann auf jeden Fall einen Index erstellen für JEDE einzelne Spalte, separat . Eine Tabelle mit 20 Spalten wird also 20 Indizes haben, eine für jede einzelne Spalte . Ich könnte wahrscheinlich sagen, dass man Bit-Spalten oder Spalten mit niedriger Kardinalität ignorieren sollte, aber da wir schon so weit gehen, warum sich die Mühe machen (mit dieser Ermahnung). Sie werden einfach da sitzen und die WRITE-Zeit verschlingen, aber wenn Sie sich nicht um diesen Teil des Bildes kümmern, dann ist alles gut.

Analysieren Sie Ihre 20 Abfragen, und wenn Sie heiße Abfragen (die heißesten) haben, die immer noch nicht schneller werden, planen Sie sie mit SSMS (drücken Sie Strg-L) mit einer Abfrage im Abfragefenster. Es wird Ihnen sagen, welcher Index dieser Abfrage helfen kann - legen Sie ihn einfach an; legen Sie sie alle an und denken Sie daran, dass dies die Schreibkosten, die Größe der Sicherungsdatei, die Wartungszeit der Datenbank usw. noch einmal erhöht.