Hallo!

Ich habe grundsätzliche Fragen über Indizes in Datenbanken. Ich habe bisher nur Datenbanken ohne Indizes verwendet (ja, wirklich ) und würde gerne wissen (es geht um SQL-Datenbanken):

1) Wofür verwendet man einen Index auf ein Feld in einer Tabelle? Was ist der Vorteil? Gibt es Nachteile?

2) Welche Felder wählt man für Indizes aus? Felder, die oft in SELECTs verwendet werden, oder nach welchen Kriterien geht man vor?

3) Sollte es Abfragen mit so ziemlich allen verfügbaren Feldern geben, wäre es dann nicht sinnvoll, einfach alle Felder mit einem Index zu versehen?

4) Wenn 3) nicht sinnvoll ist, wie viele Indizes sollte man erstellen?

Vielen Dank für Eure Hilfe!

Holger

Herzlich willkommen

Ich habe mir angewöhnt, jede Tabelle einer Datenbank mindestens mit einem Primary-Key auszustatten. Viele Datenbanken haben dafür eine interne Optimierung. Dieser Index wird auch als Referenz für alle anderen Indizes verwendet. Dadurch werden die Zugriffe stark beschleunigt.

Die Felder einer Tabelle, die mit einer anderen über ein join beim Select verbunden werden, sollten in der Regel auch einen Index. Das beschleunigt die Abfrage.

Zur Sortierung ist ein Index nach meiner Erfahrung nicht mehr so wichtig.

Welche Felder du nun mit einem Index belegst, hängt davon ab, wie stark die Datenbank durch zusätliche Index-Verwaltung belastet wird. Jeder Index verursacht beim Erfassen und Bearbeiten von Datensätzen zusätzlichen Datenbank-Aufwand.
Deswegen kann man da keinen generellen rat erteilen. Das hängt doch sehr von der Anwendung und den Ressourcen ab, die dir zur Verfügung stehen.

Hallo Jasocul,

danke für die schnelle Antwort, und das Willkommen!

Was ist denn der Unterschied zwischen dem Primary Key und einem normalen Index?

Ich stelle mir das in meiner Unwissenheit ungefähr so vor:
Ohne Index muß die komplette Tabelle bei einer Abfrage durchsucht werden, also eine große Datenmenge. Mit einem Index auf dem Feld, nach dem ich suche, muß nur eine kleine Indextabelle durchsucht werden, und diese enthält dann Verweise auf die eigentlichen Datensätze. Ich greife also auf die Haupttabelle gezielt zu, anstatt sie komplett zu durchsuchen.
Wenn ich z.B. die Felder Name und Beruf in einer Tabelle habe, wie kann dann aber ein Primary Key auf Name Vorteile bei einer Suche auf Beruf bringen, wenn dort ein normaler Index "draufsitzt"? (..wird als Referenz für andere Indizes verwendet..)

Holger

Der Primary Key ist im Gegensatz zu anderen "Standard"-Indizes, der automatisch zum Sortieren verwendete Schlüssel und sorgt dafür, dass alle Daten eindeutig in der Tabelle adressiert werden können (was bei anderen Indizes nicht immer gegeben sein muss)

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

Indizes haben einen verdammt starken Einfluss auf die Geschwindigkeit, zumindest unter Oracle. Ab etwa 1000 Datensätzen konnte ich schon einen deutlichen Geschwindigkeitsunterschied bemerken (wir benutzen Oracle 9 in der Firma). Ich musste die Anwendung optimieren, weil sie bei einigen Kunden zu langsam liefen. Man hätte den Kunden ja auch sagen können "kauft euch schnellere Server" aber nein ich musste optimieren

Also wichtig sind Indizes auf Spalten auf die auch tatsächlich zugegriffen wird! (für die Geschwindigkeit) Bei uns hatten wir meist nur einen Index auf die Primärspalte, der hat auch kaum was gebracht. Schau nach im Quältext, auf welche Spalten in den Tabellen die Zugriffe stattfinden und setze/entferne die Indizes.

Nach meiner bisherigen Erfahrung lohnt sich ein Index immer, wenn die betreffende Spalte eindeutig ist, also keine doppelten Werte darin auftreten. In einem Buch fand ich den Tipp, Indizes nur dann auf Spalten zu setzen, wenn die Suchanfragen darauf höchstens 10% der Datenmenge zurückliefern können. Auf der sicheren Seite ist man, wenn es eindeutige Spalten sind.

Zuviele Indizes gibt es eigentlich nicht. Nur auf Textspalten würde ich sie eher weniger setzen. Und: Das speichern von Datensätzen wird verlangsamt. Aber wenn man nicht über Routinen hunderte von Datensätzen abspeichert, spielt das absolut keine Rolle. Bei uns passiert das speichern nur über den Anwender, und da ist es immer nur ein Datensatz auf einmal.

Indizes sind absolutes Muss, der Geschwindigkeitsunterschied war schon enorm. Es wird unter Oracle übrigens empfohlen, regelmäßig (zumindest wenn oft Datensätze angelegt/gelöscht werden) die Indizes neu anzulegen. Vorteile bringt auch das analysieren von Datenbanken (Oracle)

Edit: für die Optimierung hatte ich neue Indizes hauptsächlich auf Spalten in Tabellen gelegt, über die wir mit anderen Tabellen verknüpfen.

Amiga-Fan hat folgendes geschrieben:

Zuviele Indizes gibt es eigentlich nicht. [...]Und: Das speichern von Datensätzen wird verlangsamt.

Hab nur ich das Gefühl, dass sich das widerspricht? *g*

Indizes gibt es zuviel und vor allem gibt es falsche Indizes!

Je mehr Indizes Du hast, desto länger brauchen Änderungens-Operationen (INSERT, UPDATE, DELETE). Das hängt natürlich auch wieder von der Art des Index ab. Je nachdem, ob es ein ISAM-, B+Tree- oder HASH-Index ist, verschnellert er bestimmte Operationen. Die Art des Index hängt von der DB ab, oft kann man ihn aber selbst bestimmen.

Wie schon gesagt, sollten Indizes auf die am meisten in Bedingungen verwendeten Feldern gelegt werden. Man kann sich aber manchmal auch noch eines Tricks bedienen: Man nimmt mehrere Felder in einen Index. Das erleichtert der DB die Arbeit bei Änderungsoperationen. Man kann solche Felder in einen Index packen, die oft zusammen in Abfragen gefiltert werden. Dabei muss aber beachtet werden, dass die Felder in dem Index nur in aufsteigender Reihenfolge verwendet werden können. Hast Du also einen Index über Feld3,Feld4,Feld2.... (in dieser Reihenfolge) angelegt wird der Index bei einer Abfrage mit Feld2,feld4,feld3 für alle Felder verwendet, bei einer Abfrage mit Feld3 und Feld2 aber nur für das Feld3. Weiterhin solltest Du auf den Typ achten. Ist Feld4 z.B. ein Text und Du hast in der Bedingungen etwas mit LIKE stehen (also keinen exakten Filterwert) dann können nachfolgende Felder des Index nicht mehr verwendet werden.

Das Beste ist, wenn man erstmal seine Anwendung analysiert:
1. Welche SQL-Statements kommen vor?
2. Welche sind die häufigsten und dauern am längsten?

Danach kann man an die Optimierung gehen. Dazu hilft einem meist auch die Datenbank. Eigentlich alle unterstützen die Anzeige der Plans, also welche Indizes und Felder verwendet werden. Meist kann das mit EXPLAIN PLAN erfahren werden.

DB2 hat in diesem Zusammenhang ein sehr schönes Tool, das zeigt das ganze anhand eines Baumdiagramms an.

Zitat:

Hab nur ich das Gefühl, dass sich das widerspricht? *g*

Also gut: bei uns werden sämtliche Insert/Update/Delete-Statements vom Anwender ausgelöst! Bei uns zumindest gab es nicht zuviele Indizes.

Amiga-Fan hat folgendes geschrieben:

Also gut: bei uns werden sämtliche Insert/Update/Delete-Statements vom Anwender ausgelöst! Bei uns zumindest gab es nicht zuviele Indizes.

Gut, soweit in Ordnung.... aber werden nicht die meisten Änderungsoperationen nicht von Anwendern ausgelöst? Und muss dieser dann nicht länger warten? Ich finde, dass ist genau der Punkt, WO es zuviele Indizes sein können. Werden irgendwelche Skripte in der Nacht laufen gelassen, ist es relativ wurscht, wie lange es braucht, aber wenn ein Anwender davor sitzt und wartet?
(zum Thema Skripte nachts laufen lassen, habe ich so ne schöne Geschichte, aber die gehört hier nicht her *g*)

Zitat:

aber werden nicht die meisten Änderungsoperationen nicht von Anwendern ausgelöst? Und muss dieser dann nicht länger warten?

naja aber so schnell können die gar nicht Tippen und speichern und korrigieren, als da ein Engpaß auftreten könnte. Der Geschwindigkeitsverlust pro Datensatz ist doch minimal. Will sagen, das meistens der PC auf den Anwender wartet und nicht umgekehrt (zumindest das laden ist wichtiger). Aber ich gebe gern zu, das die Maximalgröße unserer Datenbank bei einem Kunde bis jetzt bei ca. 1,1 GB lag. Will sagen, das wir jetzt nicht die Killeranwendung haben. Das wird bei den meisten Leuten hier auch nicht der Fall sein. Wir machen keine Standardsoftware.

Edit: ich habe aber auch nicht auf jede Spalte einen Index gesetzt, so war das nicht gemeint.

Amiga-Fan hat folgendes geschrieben:

naja aber so schnell können die gar nicht Tippen und speichern und korrigieren, als da ein Engpaß auftreten könnte. Der Geschwindigkeitsverlust pro Datensatz ist doch minimal. Aber ich gebe gern zu, das die Maximalgröße unserer Datenbank bei einem Kunde bis jetzt bei ca. 1,1 GB lag. Will sagen, das wir jetzt nicht die Killeranwendung haben. Das wird bei den meisten Leuten hier auch nicht der Fall sein. Wir machen keine Standardsoftware. Edit: ich habe aber auch nicht auf jede Spalte einen Index gesetzt, so war das nicht gemeint.

Jo, dachte ich auch nicht. Ich wollte nur damit ausdrücken, dass es genau dann schlimm ist, wenn der Anwender beim Speichern warten muss.
Also die größte Datenbank, die ich bisher betreut habe, hatte etwa 6GB. Das Ganze lag auf DB2. Jede Woche wurde ein Ladeskript laufen gelassen, das ganze 13Std. durchlief. Auch in diesem Fall muss man sich dann wirklich Gedanken über Optimierungen machen
DataWareHouse eben *g*

die SV-Abrechnung bei uns war extrem langsam, der Kunde musste Stunden warten. Routinen, die automatisch Daten speichern haben wir nicht. (höchstens import-programme, aber das ist ja eine einmalige Sache). Na jedenfalls habe ich einige Indizes auf Fremdschlüssel gelegt und das Programm ist gewaltig schneller geworden Der Kunde meinte, es würde statt Stunden nur noch ein paar Minuten dauern. Konnte man auch sehr gut nachvollziehen, einfach Indizes löschen/anlegen während der Programmausführung.

Also ich denke als Faustregel kann man sagen, das das Laden wichtiger ist als das Speichern.

Ich würde als Faustregel einfach sagen: Es kommt auf die Statements und die Datenbank an bzw. was damit gemacht wird und was wichtig ist. Einen klaren, gangbaren, einfachen Weg gibt es einfach nicht

Eigentlich wurde das Thema gut erklärt. Allerdings gibt es da paar kleine ... Naja, Fehler will ich nicht sagen, aber wo ich leicht anderer Meinung bin.

Ich hole aber ein wenig aus und fange am Anfang an. Nehmen wir an wir haben eine Mitarbeitertabelle mit ca. 30.000 Mitarbeitern. Es ist ja unwahrscheinlich, daß diese Mitarbeiter in der Tabelle alphabetisch sortiert sind. Da ist wahrscheinlich Zaludkowski ganz am Anfang und Aanson ganz am Ende. Sucht man jetzt Aanson, dann müssen 30.000 Datensätze durchsucht werden. Jeder Name muß gelesen und verglichen werden. Hier kommt der Index ins Spiel. Man kann sich das so verstellen, daß der Index eine extra Tabelle mit zwei Spalten ist. In der ersten Stalte sind alle Namen aufgelistet, in der zweiten ist eine Adresse auf den Datensatz. Wir jetzt z.B. ein neuer Mitarbeiter in die Mittarbeitertabelle eingefügt, dann werden alle Namen (inc. Datensatznummer) ausgelesen, in die Indextabelle übertragen und alphabetisch sortiert. Sucht man jetzt nach Aanson, dann kann die Datenbank gezielt suchen, denn alle A-Namen sind zusammen. Auf diese Weise muß nur ein Bruchteil der Datenbank durchsucht werden. Das ist der Vorteil eines Index.

Das ganze hat auch ein Nachteil. Jedes mal wenn ein neuer Datensatz hinzugefügt wird, wird diese Prozedur durchgeführt. Ein Mitarbeiter wird hinzugefügt und schon werden alle Namen ausgelesen, in den Index gepackt und alphabetisch sortiert. Ein weiterer Mitarbeiter wird hinzugefügt und schon werden alle Namen ausgelesen, in den Index gepackt und alphabetisch sortiert. Ein weiterer Mitarbeiter ... usw. Das kann unter umständen viel Zeit kosten.

Was ist der Unterschied zwischen einem Primärschlüssel und einem Index? Ganz einfach: ein Primärschlüssel hat automatisch einem Index. Für diese Spalte braucht also nicht zusäzlich ein Index erstellt werden. Ein Primärschlüssel hat natürlich noch weitere Aufgaben, wie z.B. dafür zu sorgen, daß ein gleicher Datensatz nicht mehrmals vorkommt.

Der Datenbankentwickler kann natürlich zusäzlich zu dem Primärschlüssel noch zusätzliche Index Spalten erstellen. An diesem Punkt kommt jetzt ein großes ABER VORSICHT. Wie ich weiter vorne beschrieben wird bei jeder (wichtigen) Datenmanipulation auch der Index neu erstellt. Wenn man für jede blödsinnige Spalte ein Index hat, dann wird jedes Hinzufügen oder löschen von Datensätzen zu einer langen Angelegenheit. Deshalb so viel Index Spalten wie nötig und nicht wie möglich. Hier wiederspreche ich Amiga-Fan, der behauptet hat, daß es zuviele Indizes nicht gibt. Es kann zuviele Indizes geben. Stellen wir uns mal vor die Mittarbeitertabelle besteht aus 10 Spalten und jede ist eine Index Spalte. In diesem Fall müßten bei jeder neuen Datensatzmanipulation 300.000 Index-Daten sortiert werden.

Indizes sollte es nur da geben wo oft gesucht oder sortiert wird. Wird z.B. in der Firma oft nach Abteilung sortiert, dann lohnt sich ein Index für die Abteilung Spalte. Für die Vornamen Spalte lohnt sich kein Index, es seiden, das der Hans oder Jörg oft gesucht werden. Ist unwahscheinlich. Deshalb kein Index für die Vornamen Spalte.

Weiterhin lohnt sich ein Index nicht immer. Auch wenn oft nach der Abteilung sortiert wird, so lohnt sich das ganze nur bei bestimmten Mengen. Untersuchungen haben gezeigt, daß es sich nur dann lohnt, wenn das Ergebnis zwischen 4 und 7% der Datenmenge liegt. Liegt es außerhalb, dann hat ein Index keinerlei Vorteil. Die Sortierung der Tabelle mit Index ist dann genauso schnell oder langsam wie ohne Index.

Das waren die Punkte die ein wenig von meinem Wissen abweichen. Den Rest haben die Anderen relativ gut erklärt.

//EDIT:

Da waren wohl einige schneller als ich. Anscheinend habe ich mir zu lange Zeit gelassen.

MartinPb hat folgendes geschrieben:

Das ganze hat auch ein Nachteil. Jedes mal wenn ein neuer Datensatz hinzugefügt wird, wird diese Prozedur durchgeführt. Ein Mitarbeiter wird hinzugefügt und schon werden alle Namen ausgelesen, in den Index gepackt und alphabetisch sortiert. Ein weiterer Mitarbeiter wird hinzugefügt und schon werden alle Namen ausgelesen, in den Index gepackt und alphabetisch sortiert. Ein weiterer Mitarbeiter ... usw. Das kann unter umständen viel Zeit kosten.

Das ist so nicht ganz korrekt. Ein Neuaufbau eines Indizes dauert sehr lange, das ist richtig. Aber genau aus diesem Grund wird das nicht gemacht. Neu eingefügte Daten werden auch einfach nur in den Index eingefügt, an der passenden Stelle versteht sich. Bei einem HASH-Index ist das recht einfach, da dort meist ein Platz an der richtigen Stelle frei ist und nicht viel umorganisiert wird. Bei B+Tree wird im Baum von oben nach unten gesucht, bis die richtige Stelle gefunden wurde. Sollte dort kein Platz mehr frei werden, muss der Baum um eine Ebene erweitert werden, dass kann etwas länger dauern. Bei ISAM wird der neue Wert in den entsprechenden Block eingefügt, evtl. muss dazu auch ein neuer Block gebildet werden. Aber neu aufgebaut wird der Index normalerweise nicht automatisch, sondern vom DBA.

Ich hab das ein wenig vereinfacht dargestellt. Natürlich hat du Recht.

Hallo!

also vielen Dank Euch allen für die vielen Informationen. Jetzt bin ich schon ein ganzes Stück schlauer

Noch ne Frage: Wenn der Index nur Vorteile bringt, wenn die Ergebnismenge zwischen 4% und 7% liegt, dann würde das ja heißen, daß ein Index auf ein Feld, das einmalig ist (also Werte nicht mehrfach vorkommen können, z.B. ein ID-Feld mit autoincrement, unique) sinnlos ist, richtig?
Dann macht ein Index nur Sinn, wenn man davon ausgehen kann, daß mehrere Ergebnisse gefunden werden. (ausgegangen davon, daß die Datenbank mehr 25 Einträge enthält, was ja normal ist).

Des Weiteren macht wohl, wie glaube ich schon erwähnt, ein Index auf ein Textfeld, das größere Textmengen enthält, keinen Sinn, oder?

Gruß,
Holger

Also die Sache mit den 4 bis 7% halte ich für fragwürdig.
Bei einem Index, wie du ihn beschrieben hast, macht ein Index auch einen Sinn. Stell dir vor, du hast 1.000.000 Datensätze. Dum musst nun ausgerechnet auf Datensatz 999.950 zugreifen. Ohne Index müsste die Datenbank die gesamte Tabelle bis zum gewünschten Datensatz durchlaufen, um diesen zu holen. Mit Index wäre das nicht passiert.

Wenn man das etwas anders mit den Prozentzahlen interpretiert, macht es schon mehr Sinn. Es geht dabei um Felder der Tabelle, mit gleichem Inhalt. Wenn man das so zusammenfasst, sollten pro Index-Eintrag nicht mehr als 7% gleiche Einträge vorhanden sein.
Aber auch solche Überlegungen sind mit Vorsicht zu genießen, da die Suche in einem Index erheblich schneller ist, als in der Tabelle selbst, wenn diese besonders breit ist (Viele Felder, bzw. mehrere große Felder). Dann kann man diesen Prozentwert sicher noch nach oben korrigieren.

Zitat:

heißen, daß ein Index auf ein Feld, das einmalig ist (also Werte nicht mehrfach vorkommen können, z.B. ein ID-Feld mit autoincrement, unique) sinnlos ist, richtig?

Gerade in diesen Fällen macht es imho am meisten Sinn!! Ich habe Indizes nur auf ID-Felder gesetzt und damit den ganzen geschwindigkeitsgewinn gemacht. (darüber findet ja auch immer die Verknüpfung mit anderen Tabellen statt, also wird häufig danach gesucht)

Ich zitiere mich nochmal:

Zitat:

In einem Buch fand ich den Tipp, Indizes nur dann auf Spalten zu setzen, wenn die Suchanfragen darauf höchstens 10% der Datenmenge zurückliefern können.