Grundsätzlich - kann DB mehrere Indizes kombinieren?

[BlackbirdBerlin]

Hallo zusammen.

In den Entwicklungsrichtlinien eines Kunden von mir steht sinngemäß, dass ein Index nur wenige Felder enthalten sollte und die Datenbanken in der Lage seien, zwei oder mehr Indizes gemeinsam zu nutzen, um eine Abfrage auszuführen.

Gemeint ist damit, wenn es eine Tabelle mit Kunde, Artikel, Warengruppe, ... gibt und es einen Index auf Warengruppe und einen Index auf Kunde gibt, dass dann ein Zugriff SELECT ... FROM ... WHERE Warengruppe = ... AND Kunde = ... so gut ausgeführt werden würde, als gäbe es EINEN Index auf Warengruppe UND Kunde.

Aus meinen bisherigen Datenbankerfahrungen halte ich das für Unsinn, möchte aber nicht ausschließen, dass Datenbanken im Gegensatz zu mir (in diesem konkreten Fall) dazugelernt haben...

Wenn ich mir Execution-Plans ansehe, sehe ich doch auch immer nur den einen Index, für den sich die DB entschieden hat...

Kann jemand hier die Kunden-Vorgabe bestätigen oder liege eher ich richtig?

Viele Grüße
Tim

[nahpets]

'ne richtige Antwort hab' ich nicht. Zur Lektüre eventuell dieses: http://wikis.gm.fh-koeln.de/wiki_db/...ken/SQL-Tuning
Vermutlich haben Dein Kunde und Du recht, es dürfte auf die Datenbank ankommen.

[BlackbirdBerlin]

Hi Stefan,
wenn ich das eben richtig überflogen habe, wird dort aber auch explizit vom Zugriff über einen Index gesprochen und nicht davon, dass die Datenbank in der Lage ist, mehrere Indizes zum Zugriff für eine Abfrage zu verwenden...
Viele Grüße
Tim

[nahpets]

Hallo Tim,
wenn sich diese Erkenntnis für alle Datenbanken generalisieren lässt, hast Du recht und nicht der Kunde.

Es steht also der Beweis aus, dass der Kunde recht hat. Bezieht er sich auf Datenbanken allgemein oder hat er eine bestimmte im Einsatz, so dass man hier gezielt die Richtigkeit seiner Aussage beweisen (oder halt auch widerlegen) kann?

[BlackbirdBerlin]

Der Kunde hat Oracle und weigert sich standhaft, eine Abfrage, die ohne Index ca. 10 Std. (!) läuft, auf wenige Minuten durch einen Index zu reduzieren.
Tabelle hat knapp 200 Mio Einträge...
Das ist in meinen Augen deren pers. Pech - damit kann ich gut umgehen...
Ich will nur gern wissen, ob die Entwicklungsrichtlinien Unsinn vorschreiben oder nicht, da ich gern, falls dem so ist, nicht auf altem Wissen stehen bleiben möchte. Ich finde nur nichts, was die Aussage bestätigt.

Viele Grüße
Tim

[Phoenix]

Also mir persönlich ist jetzt keine Datenbank bekannt, die Indexe kombinieren könnte. Ich habe allerdings auch nur Erfahrungen mit Microsofts SQL Server, Oracle, DB2 und MySQL* bzw. MariaDB*.

Bei denen bin ich mir sicher, dass sie bei der Ermittlung des Ausführungsplans prüfen, welcher der Indexe am besten passt, und diesen dann verwenden (und nein, der sogenannte "index_merge" den MariaDB / MySQL manchmal versucht zählt nicht, der ist eher theoretischer Natur und greift im echten Leben fast nie).

Grundsätzlich sollte man sich also anschauen:
Nach welchen Spalten wird in aller Regel gemeinsam gefiltert? Diese sollten gemeinsam in einen Index. Es sollte dabei darauf geachtet werden, lieber einen Index mehr zu machen (also z.B. einen Index auf A, B und D und einen auf A, B, D und E), als eine Spalte zu viel in den Index aufzunehmen (z.B. nur einen Index auf A, B, D und E, wenn oft nur auf A, B, D abgefragt würde).

Dabei sollte man dann allerdings die Spalten, die in aller Regel mit selektiert (aber nicht gefiltert) werden in den Index als non-key Spalten mit zu includen (sofern die DB das kann).

Hintergrund: Selbst wenn ein Index dazu führt, dass die betroffenen Spalten schnell identifiziert werden können: Ohne die included columns muss die Datenbank dann trotzdem wieder table seeks machen um die eigentlichen Daten lesen und ausliefern zu können. Wenn die Spalten aber schon als non-key im Index enthalten sind, kann sich die Datenbank den lookup sparen denn die Daten sind beim index schon gelesen und ist dann deutlichst schneller beim beantworten der queries. Hier z.B. Info dazu für den SQL Server: https://msdn.microsoft.com/en-us/library/ms190806.aspx

[Phoenix]

Zitat:

Zitat von BlackbirdBerlin (Beitrag 1315189)

Der Kunde hat Oracle und weigert sich standhaft, eine Abfrage, die ohne Index ca. 10 Std. (!) läuft, auf wenige Minuten durch einen Index zu reduzieren.

Oha. Naja, klassisches "selber schuld". Oracle nutzt den Index, der am selektivsten ist, und nur genau den (ausser sie meint, ein full table scan wäre besser).

Infos dazu z.B.: http://stackoverflow.com/questions/1...-work-together

[BlackbirdBerlin]

Hi Sebastian,
das entspricht auch in etwa meinem Wissenstand. Bis auf die Non-Key-Geschichte. Das ist mir neu und das vertiefe ich nochmal - DANKE dafür :)
Viele Grüße und besten Dank für Deine Antwort
Tim

[Uwe Raabe]

Bei Interbase gibt es da tatsächlich eine Besonderheit (aus Optimizing InterBase SQL and Metadata - emphasize by me):

Zitat:

When we design our metadata, there are a few approaches we can take:

Create a single index for only one of the columns. If there are only 10 records in the table for any given date, for example, then a single index on FOO_DATE is enough to allow us to find any record in the table quickly.
Create one index for each column in the WHERE clause (three total). InterBase will combine the indices when the query is run.
Create a single index on all three columns (one index total).
If solution (1) works for your particular application, great. If not, we need to examine (2) and (3).

Let's imagine you choose solution (2), and create indices on FOO_DATE, FOO_COLOR, and FOO_DSC. When you type a WHERE clause like the one above, the InterBase optimizer will notice that all three indices could be useful in performing the query. It makes a kind of bitmap of each index, where a bit is turned on if there's an index node for a particular value, and turned off if there isn't. It then combines the bitmaps using a binary AND operation, and uses the resulting bitmap as a single index into the table. There is some overhead in doing this, but it isn't too bad in most cases.

If you choose solution (3), then InterBase can use your multi-column index as-is, without any combination of separate indices. So (3) may produce the fastest query results for this particular SELECT. But what about other statements; will the index still be useful?

[BlackbirdBerlin]

Hi Uwe,
sowas ähnliches hab ich inzw. auch zu Oracle gefunden. Dennoch bleibt der Hinweis, dass der spezielle Index am hilfreichsten sein sollte.
Ich muss versuchen, eine ähnliche Datensammlung zu Testzwecken auf eine Maschine zu bekommen, so dass ich das mal in beiden Konstellationen prüfen kann.
Problem ist aktuell, dass die bestehenden 8 Indizes nicht jeweils aus einem Feld bestehen, sondern gezielte Indizes sind, die für die meisten Abfragen zutreffen. Ob es da sinnvoll ist, genau das eine Feld, welches zur Zeit noch fehlt, in einem separaten Index aufzunehmen, wäre vermutlich nur durch einen Versuch zu klären.
Auch Dir vielen Dank!
Grüße,
Tim