Grundsätzlich - kann DB mehrere Indizes kombinieren?

[frankyboy1974]

hallo Blackbird,

ich verstehe die Frage jetzt immer noch nicht,

Zitat:

Wie realisieren Datenbanken einen Index???

Warum legt nicht die Datenbank automatisch auf jedes Feld einen Index an. Dann würde die Suche nach diesem Feld wohl schneller funktionieren?? Und wenn ich nach zwei Felder suche, dann kombiniert die DB einfach die beide Inizes und ist dann immer noch schneller als ein Tablescan? Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

mfg

[mkinzler]

Ein Index beschleunigt die Suche, verlangsamt aber Insert/Update/Delete Vorgänge, weil auf Grund dieser ja Aktualsierung der Indizes notwendig wird. Deshalb wird automatisch nur ein Index für den Primärindex erzeugt.

[Sir Rufo]

Jeder Index auf einer Tabelle ist intern eine weitere Tabelle mit den Inhalten der indizierten Felder. Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Jeder Index belegt auch Speicher. Jeder Index muss wie die Tabelle aktualisiert werden. Mit einem Index-Wildwuchs kann ich den Plattenplatz verbraten und die Einfüge-Geschwindigkeit in den Keller drücken.

Es ist also ein Balance-Akt zwischen Speicherplatz und Abfrage-/Einfüge-Geschwindigkeit.

Für riesige Datenmengen mit vielen Abhängigkeiten werden für die Auswertung auch gerne mal separate Systeme genutzt. Die sind dann zwar nicht unbedingt absolut auf dem aktuellsten Stand, dafür aber bei der Abfrage höllenschnell. Wenn ich wissen will was letzten Monat passiert ist, dann ist mir aber der Stand von vor einer Sekunde so was von egal.

[BlackbirdBerlin]

Zitat von Sir Rufo:

...Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Kann man uneingeschränkt so nicht gelten lassen, finde ich.

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Beispiel:

Code:

 Tabelle:
  BelegNr
  BelegJahr
  Kunde
  Artikel

 Index:
  Kunde
  Artikel
  BelegJahr
  BelegNr

Hier würde der Index m.E. durchaus Sinn ergeben, obwohl alle Felder enthalten sind!

Aber auch hier lasse ich mich gern eines Besseren belehren

Viele Grüße und sehr vielen Dank für Eure Beteiligung!
Tim

[nahpets]

Versuchen wir ein Beispiel und sei dazu folgende Tabelle gegeben:
Kundennummer, Postleitzahl, Ort, Rufnummer, eMail

Code:

Select * from tabelle where kundennummer = 1

Ein Index auf Kundennummer wäre hier sinnvoll.

Code:

Select * from tabelle where Ort = "Bärlin"

Ein Index auf Ort wäre hier sinnvoll.

Code:

Select * from tabelle where Kundennummer = 1 and Ort = "Bärlin"

Ein Index auf die Kombination aus Kundennummer und Ort wäre hier sinnvoll.

Der Kunde sagt aber: Ein Index auf Kundennummer und ein Index auf Ort reichen aus.

Hier im Beispiel hätten wir (im Kundenidealfall) auf jede der Spalten einen Index. Bei Abfragen, die über beliebige Kombinationen der Spalten gehen, soll gefälligst die Datenbank schauen, welcher Index bzw. welche Kombination aus 1:n Indizes hier die richtige ist.

frankyboy1974 macht dazu einen Vorschlag in der Art:

Code:

select * from (
  select * from tabelle where kundennummer = 1
) where Ort = "Bärlin"

Wenn die Datenbank "klug" ist, nutzt sie für die innere Abfrage den Index auf Kundennummer, aber ob sie für die so gefundene Teilmenge noch den Index auf die Spalte Ort der Tabelle nutzen kann, wage ich zu bezweifeln. Bei der äußeren Abfrage macht sie eher ein Full-Table-Scan, und wenn das Ergebnis komplett im Speicher liegt, geht das sicherlich sehr schnell. Ist die Datenmenge aber riesig (ein paar Millionen Sätze) so muss sie da wohl eher im Temp-Table-Space "rumwühlen".
Aber: Die Erfahrung, dass derartige Konstrukte deutlich performanter sind, als eine "innere" Abfrage, die alles abfackelt, habe ich schon mehr als nur einmal gemacht. Es kommt hier wohl wieder auf das Datenbanksystem und ein bisserl Versuch und Irrtum an.

Zumindest bei Oracle haben wir uns regelmäßig die Ausführungspläne angeschaut und ggfls. einen neuen Index angelegt, der für die Abfrage sinnvoller zu nutzen war, als das, was die Datenbank mit den vorhandenen Indizes machen konnte. Sehr vereinzelt kam es auch vor, dass die Datenbank nicht den bestmöglichen Index genutzt hat, dann wurde das SQL-Statement mit einem entsprechenden Hint versehen.

Bei Oracle müsste man aber doch eigentlich aus den Ausführungsplänen der Abfragen entnehmen können, ob der Kunde mit seiner Aussage recht hat oder eben auch nicht.

@BlackbirdBerlin
Hast Du beim Kundensystem irgendwie die Möglichkeit, für alle Abfragen an die Ausführungspläne zu kommen und sie dahingehend zu überprüfen, ob die Aussage des Kunden (zumindest in einem Fall) zutreffend sein könnte?

Da es sich hier ja eher um eine akademische Diskussion, die sich hauptsächlich um die Theorie dreht (und ggfls. die Fakten außen vor lässt), handelt, kannst Du die Kundenbehauptung vermutlich nur durch handfeste Fakten widerlegen.

Zitat von frankyboy1974:

Warum legt nicht die Datenbank automatisch auf jedes Feld einen Index an. Dann würde die Suche nach diesem Feld wohl schneller funktionieren?? Und wenn ich nach zwei Felder suche, dann kombiniert die DB einfach die beide Inizes und ist dann immer noch schneller als ein Tablescan? Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

Die Frage hier ist aber doch gerade: kann die Datenbank "dann kombiniert die DB einfach die beide Inizes" das?

Sorry, wenn ich das jetzt mal überspitzt umformuliere:

Zitat von frankyboy1974:

Also mein Gedanke wäre immer noch, bei der ersten Abfrage veruchst du einen Index zu erwischen, und den Rest programmierst du selbst.

Wenn die Datenbank so schlecht ist, programmiere doch selber eine.

Das halte ich für eine sehr schlechte Alternative. Warum nicht die Fähigkeiten der Datenbank vollumfänglich ausnutzen, sondern nur, weil der Kunde irgendwelche Restriktionen einführt, die die Leistungsfähigkeit der Datenbank einschränken, irgendwas drumherum programmieren?

Zitat von mkinzler:

Ein Index beschleunigt die Suche, verlangsamt aber Insert/Update/Delete Vorgänge, weil auf Grund dieser ja Aktualsierung der Indizes notwendig wird. Deshalb wird automatisch nur ein Index für den Primärindex erzeugt.

Update und Delete machen eine DB bei der Pflege der Daten sicherlich langsamer, durch die Indexpflege. Aber der zu pflegende Index sorgt auch dafür, dass die zu ändernden oder zu löschenden Daten schneller gefunden werden, als durch einen Full-Table-Scan.
Es wäre jetzt also der Beweis zu erbringen, was aufwändiger ist: Die zusätzliche Pflege oder die Suche der zu ändernden/löschenden Datensätze ohne Index.

Zitat von Sir Rufo:

Jeder Index auf einer Tabelle ist intern eine weitere Tabelle mit den Inhalten der indizierten Felder. Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Jeder Index belegt auch Speicher. Jeder Index muss wie die Tabelle aktualisiert werden. Mit einem Index-Wildwuchs kann ich den Plattenplatz verbraten und die Einfüge-Geschwindigkeit in den Keller drücken.

Es ist also ein Balance-Akt zwischen Speicherplatz und Abfrage-/Einfüge-Geschwindigkeit.

Ein Index oder eine Indexkombination über mehrere Felder ist natürlich nur für solche Felder sinnvoll, auf die häufig zur Datenauswahl zugegriffen wird.
Die unterschiedliche Reihenfolge identischer Spalten in mehrere Indizes kann dagegen durchaus sinnvoll sein. Nicht nur für die Auswahl einer zu selektierenden Teilmenge, sondern auch für deren sortierte Ausgabe in der im Index enthaltenen Reihenfolge.
Selbst bei einer uneingeschränkten Ausgabe aller Daten in der im Index festgelegten Reihenfolge, kann dies zu deutlichen Laufzeitverkürzungen führen.

Der letzte Satz von Sir Rufos Aussage ist sehr wesentlich, es ist ein Balance-Akt, der hier aber vom Kunden durch Vorgaben ggfls. sehr starkt behindert werden kann.
Wenn der optimale Index für eine große, immer wiederkehrende Abfrage ein Index aus einer Spaltenkombination ist, so wird hier durch die Einschränkung ein Index = eine Spalte, die Möglichkeit des Ausbalanzierens doch deutlich eingeschränkt.

[OT]die Steigerung von optimal, optimaler, am Optimalsten nutzen wir eigentlich nur, wenn irgendetwas so richtig Sch.....adedaskeinsalzdranwar ist.[/OT]

[mkinzler]

Zitat:

Update und Delete machen eine DB bei der Pflege der Daten sicherlich langsamer, durch die Indexpflege. Aber der zu pflegende Index sorgt auch dafür, dass die zu ändernden oder zu löschenden Daten schneller gefunden werden, als durch einen Full-Table-Scan.
Es wäre jetzt also der Beweis zu erbringen, was aufwändiger ist: Die zusätzliche Pflege oder die Suche der zu ändernden/löschenden Datensätze ohne Index.

Wenn automatisch für jedes Feld ein Index angelegt wird, wird m.E. bei zunehmender Anzahl der Felder schnell das Finden des DS belanglos werden.

Zitat:

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Der Index auf den Schlüssel hätte sda aber den selben Effekt.

[nahpets]

@BlackbirdBerlin
Kennst Du diese Seite Database Administrator's Guide, 16 Managing Indexes?

Unter Order Index Columns for Performance wird (meiner Meinung nach) Deine Aussage bestätigt:

Zitat von BlackbirdBerlin:

Zitat von Sir Rufo:

...Darum macht z.B. ein Index mit allen Feldern keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Kann man uneingeschränkt so nicht gelten lassen, finde ich.

Es müsste heißen: Darum ergibt z.B. ein Index mit allen Feldern in identischer Reihenfolge keinen Sinn, weil ich damit nur die Tabelle dupliziere.

Beispiel:

Code:

 Tabelle:
  BelegNr
  BelegJahr
  Kunde
  Artikel

 Index:
  Kunde
  Artikel
  BelegJahr
  BelegNr

Hier würde der Index m.E. durchaus Sinn ergeben, obwohl alle Felder enthalten sind!

Aber auch hier lasse ich mich gern eines Besseren belehren

Viele Grüße und sehr vielen Dank für Eure Beteiligung!
Tim

[mm1256]

Zitat von BlackbirdBerlin:

...Die Frage war aber, ob es Sinn ergibt, mehrere "einfeldige" Indizes anzulegen, anstelle spezieller Indizes mit mehreren Feldern und darauf zu bauen, dass die DB diese in Kombination verwendet (was wohl unter dem Schlagwort Bitmapped Indexes so zu sein scheint).
Und wenn es die Steigerung von optimal gäbe, dann wäre es wohl am "optimalsten", wenn man einen konkreten Index definiert, welcher die Schlüsselfelder der Abfrage indiziert.

Nun, ich bin ja ein eher praktisch denkender Mensch, und darum würde ich das Problem einfach mal eben auch von der praktischen Seite angehen. Denn, so schön die Diskussion hier mitzulesen ist, und so unterschiedlich die DB's das handhaben, Klarheit wird letztendlich nur ein Test bringen.

Da die Struktur der DB bekannt ist, ist das Anlegen von ein paar Mio Datensätzen in drei Test-Tabellen in allen drei Varianten (Original wie beim Kunden, mit Einzel-Index und mit zusammengesetztem Index) keine Hexerei.

Wenn meine Annahme stimmt, hau ich dem Kunden das Ergebnis um die Ohren und bin der King. Wenn nicht, verkriech ich mich in mein stilles Örtchen und warte bis der Anfall vorüber ist

[BlackbirdBerlin]

Hi Otto,

das Problem beim Mal-Eben-Testen ist, dass selbst die QS in einer Hochverfügbarkeitsumgebung läuft, weil da viele Projekte parallel laufen und mal eben einen Index für knapp 200 Mio Datensätze anlegen oder drei Testtabellen ins System kippen und damit zig ( oder mehr ) Leute bis zur Behebung des Problems arbeitsunfähig machen, geht leider nicht.
In einer "eigenen" Umgebung solche Tests fahren, ist natürlich kein Problem. Aber da hat man eben nicht die Gewissheit, dass die Systemumgebung und die Datenkonstellation zu dem gleichen Ergebnis führen.

Viele Grüße
Tim

[mm1256]

Zitat von BlackbirdBerlin:

In einer "eigenen" Umgebung solche Tests fahren, ist natürlich kein Problem. Aber da hat man eben nicht die Gewissheit, dass die Systemumgebung und die Datenkonstellation zu dem gleichen Ergebnis führen.

Ich habe natürlich "eigene Umgebung" gemeint.

Bitte korrigiere mich, wenn ich da etwas falsch sehe:

Systemumgebung: Auch wenn die DB auf deinem eigenen Test-Server mit anderer Hardware läuft, sollten zuverlässige Ergebnisse möglich sein. Dass die Ergebnisse/Zeiten natürlich von der realen Umgebung beim Kunden abweichen, ist mir schon klar. Die Relation der Unterschiede untereinander dürfte jedoch eindeutig zu erkennen sein. Und darum geht es doch letzendlich. Wenn die Query-Zeiten ohne Index und mit Index z.B. im Verhältnis 10:3 wären, dann können die Unterschiede zur Systemumgebung des Kunden nicht ausschlaggebend sein. Denn ob es beim Kunden dann 10:5 oder 10:2 wäre, ist unerheblich. Die Steigerung wäre enorm. Etwas anders sieht es aus, wenn das Verhältnis nahezu gleich wäre. Aber auch dann hast du wenigstens einen Anhaltspunkt, was Sache ist.

Datenkonstellation: Wenn kein Join involviert ist...

Zitat von BlackbirdBerlin:

Es handelt sich um keinen Join sondern um eine einfache Tabelle, zu welcher bisher das für meinen Zugriff erforderliche Schlüsselfeld nicht indiziert ist.

...dürfte das auch kein Problem sein.