Mit mehr als einem index pro Tabelle ist das gefährlich?

Benötigen Sie genau so viele Indizes, wie Sie benötigen zu erstellen. Nicht mehr, nicht weniger. Es ist einfach so.

Jeder "weiß", dass ein index verlangsamt DML-Anweisungen für eine Tabelle. Aber aus irgendeinem Grund nur sehr wenige Menschen tatsächlich die Mühe, zu testen, wie "langsam" es wird in Ihrem Kontext. Manchmal bekomme ich den Eindruck, dass die Leute denken, dass das hinzufügen eines weiteren index hinzufügen mehrere Sekunden, um jeder eingefügten Zeile, so dass es ein game-changing business Kompromiss, dass einige fiktive hotshot Benutzer sollte entscheiden, in einem sitzungszimmer.

Möchte ich ein Beispiel, das ich erstellt habe auf meinem 2 Jahre alten pc mit einem standard-MySQL-installation. Ich weiß, Sie tagged die Frage, SQL-Server, aber das Beispiel sollte einfach umgerüstet werden. Ich legen 1,000,000 Zeilen in den drei Tabellen. Eine Tabelle ohne Indizes einer Tabelle mit eine index und eine Tabelle mit neun Indizes.

drop table numbers;
drop table one_million_rows;
drop table one_million_one_index;
drop table one_million_nine_index;

/*
|| Create a dummy table to assist in generating rows
*/
create table numbers(n int);

insert into numbers(n) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

/*
|| Create a table consisting of 1,000,000 consecutive integers
*/   
create table one_million_rows as
    select d1.n + (d2.n * 10)
                + (d3.n * 100)
                + (d4.n * 1000)
                + (d5.n * 10000)
                + (d6.n * 100000) as n
      from numbers d1
          ,numbers d2
          ,numbers d3
          ,numbers d4
          ,numbers d5
          ,numbers d6;


/*
|| Create an empty table with 9 integer columns.
|| One column will be indexed
*/
create table one_million_one_index(
   c1 int, c2 int, c3 int
  ,c4 int, c5 int, c6 int
  ,c7 int, c8 int, c9 int
  ,index(c1)
);

/*
|| Create an empty table with 9 integer columns.
|| All nine columns will be indexed
*/
create table one_million_nine_index(
   c1 int, c2 int, c3 int
  ,c4 int, c5 int, c6 int
  ,c7 int, c8 int, c9 int
  ,index(c1), index(c2), index(c3)
  ,index(c4), index(c5), index(c6)
  ,index(c7), index(c8), index(c9)
);


/*
|| Insert 1,000,000 rows in the table with one index
*/
insert into one_million_one_index(c1,c2,c3,c4,c5,c6,c7,c8,c9)
select n, n, n, n, n, n, n, n, n
  from one_million_rows;

/*
|| Insert 1,000,000 rows in the table with nine indexes
*/
insert into one_million_nine_index(c1,c2,c3,c4,c5,c6,c7,c8,c9)
select n, n, n, n, n, n, n, n, n
  from one_million_rows;

Meine Zeiten sind:

• 1m Zeilen in eine Tabelle ohne Indizes: 0,45 Sekunden
• 1m Zeilen in Tabelle 1 index: 1,5 Sekunden
• 1m Zeilen in der Tabelle mit 9 Indizes: 6,98 Sekunden

Ich bin besser mit SQL aus als Statistiken und Mathe, aber ich würde gerne glauben, dass:
Hinzufügen 8-Indizes in meine Tabelle Hinzugefügt (6,98-1,5) 5,48 Sekunden insgesamt. Jeder index würde dann dazu beigetragen haben, 0,685 Sekunden (5,48 /8) für alle 1.000.000 Zeilen. Das würde bedeuten, dass die zusätzlichen overhead pro Zeile pro index hätte 0,000000685 Sekunden. JEMAND ANRUFEN DER VERWALTUNGSRAT!

Abschließend möchte ich sagen, dass die oben genannten Testfall nicht beweisen Scheiße. Es zeigt nur, dass heute Abend, ich war in der Lage das einfügen von 1.000.000 aufeinanderfolgende ganze zahlen in einer Tabelle in einer single-user Umgebung. Ihre Ergebnisse wird anders sein.

Ja, definitiv - zu viele Indizes auf eine Tabelle kann schlimmer sein als gar keine Indizes überhaupt. Aber ich glaube nicht, dass es gut ist, mit den "am meisten ein index pro table" - Regel.

Für SQL Server, meine Regel ist:

• index foreign key-Felder - dies hilft JOINs und ist von Vorteil für andere Abfragen zu
• index für alle anderen Felder, wenn es Sinn macht, z.B. wenn viele intensive Abfragen davon profitieren können

Suche nach der richtigen Mischung von Indizes - Wiegen die Vorteile der Beschleunigung der Abfragen gegen die Nachteile der zusätzliche Aufwand für INSERT -, UPDATE -, DELETE - ist keine exakte Wissenschaft - es geht eher darum, know-how, Erfahrung, Messung, Messung, und Messen Sie erneut.

Jede Feste Regel gebunden ist, werden mehr contraproductive als alles andere.....

Die besten Inhalte auf die Indizierung kommt von Kimberly Tripp - die Königin der Indizierung - finden Sie Ihre blog-Beiträge hier.

Aktualisierung eines index wird einmal pro Einsatz (pro index). Speed-Gewinn ist für jeden wählen. Also, wenn Sie aktualisieren Sie nur selten und oft gelesen, dann die zusätzliche Arbeit kann es Wert sein.

Wenn du anders machen, wählt (also die Spalten, die Sie filtern Verschieden sind), dann die Aufrechterhaltung eines index für jede Art der Abfrage ist sehr nützlich. Vorausgesetzt, Sie haben eine begrenzte Gruppe von Spalten, die die Abfrage oft.

Aber der übliche Rat gilt: wenn Sie wissen möchten, welche ist am schnellsten: Profil!

So viel hängt von Ihrem schema und die Abfragen, die Sie normalerweise ausführen. Zum Beispiel: wenn Sie normalerweise brauchen, zu wählen über 60% der Zeilen der Tabelle, Indizes wird Ihnen nicht helfen, und es wird billiger sein, um beim Scannen der Tabelle als index-scan und anschließend die lookup-Zeilen. Konzentriert Abfragen, wählen Sie eine kleine Anzahl von Zeilen in die verschiedenen Teile der Tabelle oder die verwendet werden, für joins in Abfragen wird wahrscheinlich von Indizes profitieren. Die rechts-index an der richtigen Stelle kann Pause machen oder ein feature.

Indizes nehmen den Raum, so dass zu viele Indizes für eine Tabelle kann sich als kontraproduktiv für die gleichen oben genannten Gründen. Scannen 5 Indizes und dann Zeile lookups kann viel teurer sein als der einfach-Tabelle Scannen.

Gutes design ist die Synthese über ungefähr wissen, Wann zu normalisieren und Wann nicht.
Wenn Sie Häufig join auf eine bestimmte Spalte, überprüfen Sie die IO-plan mit dem index und ohne. Als Allgemeine Regel ich vermeiden, dass Tabellen mit mehr als 20 Spalten. Dies ist oft ein Zeichen dafür, dass die Daten normiert werden. Mehr als 5 Indizes auf eine Tabelle, und verwenden Sie möglicherweise mehr Raum für die Indizes als die Haupt-Tabelle, werden Sie sicher, dass ist es Wert. Diese Regeln sind nur die leichteste Führung und so viel hängt davon ab, wie die Daten in Abfragen verwendet werden, und was Ihre Daten aktualisieren Profil aussieht.

Experiment mit Ihrer Suchanfrage-Pläne zu sehen, wie die Lösung verbessert oder verschlechtert, mit einem index.

Optimierung der retrieval mit Indizes müssen sorgfältig entworfen, um die tatsächliche Abfrage-Muster. Sicherlich, für eine Tabelle mit Primärschlüssel, Sie haben mindestens einen gruppierten index (das ist, wie die Daten tatsächlich gespeichert sind), dann alle zusätzlichen Indizes nutzen das layout der Daten (Cluster-index).

Nach der Analyse von Abfragen, die gegen die Tabelle, die Sie möchten, um einen index entwerfen(s), die Sie bedecken. Das kann bedeuten, Bau eines oder mehrerer Indizes, aber das hängt stark von der Abfragen selbst. Diese Entscheidung kann nicht gemacht werden, einfach durch einen Blick auf Spalte nur die Statistiken.

Für Tabellen, bei denen es meistens die Einsätze, d.h. die ETL-Tabellen oder so etwas, dann sollten Sie nicht zu erstellen, Primärschlüssel, oder tatsächlich zu löschen Indizes und neu erstellen, wenn änderungen der Daten zu schnell oder drop/neu ganz.
Ich persönlich hätte Angst, den Schritt in eine Umgebung, hat eine hart codierte Regel von Indizes pro Tabelle-Verhältnis.