Grundsätzlich - kann DB mehrere Indizes kombinieren?

[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

[IBExpert]

Mehrere Indizes pro Tabelle in einer Abfrage kombinieren ist bei Firebird kein Problem. Wenn es passende Multicolumn Indizes gibt, dann werden meistens die vom Optimierer bevorzugt, ansonsten werden aber auch mehrere Indizes pro Tabelle kombiniert benutzt. Wenn mehrere Indizes kombiniert werden, entscheidet die Selektivität über die Reihenfolge

Beispiel auf der IBExpert Demo DB, Tabelle Product mit 10000 Datensätzen, Firebird 2.5.4

Code:

select * from product where category_id=1 and special=1

Code:

PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Datenmengen:
category_id=1 -> 327 Datensätze (insgesamt gibt es 17 unterschiedliche Werte, daher ist die Selektivität 1/17)
special=1 -> 184 Datensätze (es gibt relativ wenige Specials mit 1, der Rest ist 0, Selektivität 1/2)

Der Optimierer beginnt mit dem selektiveren Index, nutzt aber weitere Indizes sofern sinnvoll trotzdem.

Wenn man o.a. SQL ausführt bekommt man die Ergebnismenge von 9 Datensätzen mit 9 indizierten Reads

Code:

Query
------------------------------------------------
select * from product
where category_id=1 and
special=1

Plan
------------------------------------------------
PLAN (PRODUCT INDEX (IX_PROD_CATEGORY, IX_PROD_SPECIAL))

Query Time
------------------------------------------------
Prepare       : 15,00 ms
Execute       : 31,00 ms
Avg fetch time: 3,44 ms

Memory
------------------------------------------------
Current: 34.728.216
Max    : 35.150.104
Buffers: 2.048

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 27
Marks  : 0


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT                        |         0 |         9 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Wichtig: Die Feldreihenfolge im SQL ist für den Plan unwichtig, sofern alle den gleichen Operator (in diesem Falle = ) benutzen


Metadaten

Code:

/******************************************************************************/
/****              Generated by IBExpert 08.09.2015 23:46:34               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE PRODUCT (
    ID           NUMERIC(18,0) NOT NULL,
    CATEGORY_ID  NUMERIC(18,0) NOT NULL,
    TITLE        VARCHAR(50) NOT NULL,
    ACTOR        VARCHAR(50) NOT NULL,
    PRICE        NUMERIC(12,2) NOT NULL,
    SPECIAL      SMALLINT
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (ID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX IX_PROD_ACTOR ON PRODUCT (ACTOR);
CREATE INDEX IX_PROD_CATEGORY ON PRODUCT (CATEGORY_ID);
CREATE INDEX IX_PROD_SPECIAL ON PRODUCT (SPECIAL);
CREATE INDEX IX_PROD_TITLE ON PRODUCT (TITLE);
CREATE DESCENDING INDEX PRODUCT_IDX2 ON PRODUCT (ID);


/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/

[jobo]

Ich habe nur mit Oracle in nennenswerten Größenordnungen Erfahrung. Die ist aber genau so, dass die Indizierung einzelner Felder besser ist, als kombinierte Indizes.
Die Regel, die Du da nennst, würde ich demnach bestätigen. Was Du aber später schreibst, widerspricht ja dieser Regel oder habe ich das mit den 8 gezielten Indizes (sprich kombiniert über die notwendigen Felder?) falsch verstanden?

Wenn man von 200Mio DS spricht, ist natürlich ein Index zum Testen nicht mal eben per Augenblinzeln erstellt. Und wenn er da ist, ist noch nicht in allen Lebenslagen getestet.
Das Problem, dass die sich sträuben, liegt glaub ich eher darin begründet, dass so etwas eine Menge Ausführungspläne für Reports oder sonst irgendwelche online Kram dramatisch über den Haufen werfen kann. Das macht man wohl nicht ohne Not für eine Abfrage, die nur in irgendeinem Report mal gebraucht wird.

Wenn das auf einem Testsystem ausprobiert wird (was anders ist nicht empfehlenswert), sollte das System möglichst identisch sein zur Produktion, was Mengengerüste und vor allem die Statistiken angeht (im Zweifel die Statistiken mit aus dem Prodsystem übernehmen und Statistik Aktualisierungen im Testsystem abschalten).

Meine Erfahrungswerte aus V10 und V11 sind da mit dem Optimizer ganz gut. Da würde ich bei der Gelegenheit nach Deinem Test mit einem zusätzlichen Index im Nachgang aus Spaß und Neugier gleich alle kombinierten Indizes löschen und durch einzelne ersetzen, testhalber. Prognose: Kann nur besser werden. Das muss allein schon massig Platz sparen, wahrscheinlich auch redundanten Platz (also faktorenmäßig), weil ein Feld in mehreren Indizes eingebunden ist und so mehrfach Indexplatz kostet. (Das müsste man separat mit vorher/nachher Tablespace- bzw. Segmentanalysen abfragen). Einzelne Indizes, also kleinere, bringen in der Größenordnung vielleicht auch noch mal extra Performance, unabhängig von den Ausführungsplänen.

Andererseits: Wenn die Index "Politik" des Kunden so rigide ist, ist es auch möglich, dass einzelne Abfragen von den Entwicklern mangels Alternativen auf die verfügbaren Indizes hin optimiert wurden. Wenn dann dort gerodet oder umgepflanzt wird, können besonders optimierte Abfragen (siehe auch Optimizer Hints) zu Zombies werden.

Unter V12 hatte ich bis jetzt eine derbe Enttäuschung (eine komplexe Abfrage), also Verschlechterung nach Upgrade. Da würde ich mehr testen.

[Dejan Vu]

Hier Einiges zum Thema:
http://use-the-index-luke.com/sql/wh...ge-performance

Und speziell für Oracle:
http://www.dba-oracle.com/art_9i_indexing.htm

[frankyboy1974]

hallo,

wenn der optimizer einer Datenbank, nicht den richtigen Weg wählt, um zum richtigen Weg auf optimale Weise zu gelangen, würde ich verschachteltest SQL empfehlen. Du überlegst dir selber, wie der optimale Weg ist und zwingst die Datenbank diesen Weg zu nehmen. Anstatt also zu hoffen das Oracle immer den richtigen Weg bei 5 Tabellen findet, splittest du die Anfrage in 4 (verschachtelte)Anfrage aus und zwingst die Datenbank, immer den dafür vorhergesehenden Index zu benutzen.

mfg

[BlackbirdBerlin]

Hi nochmal.
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.
Viele Grüße
Tim

[Blup]

Variante 1: Ein Index, der die selben Spalten in der gleichen Reihenfolge und Ausrichtung (desc) enthält, wie diese in der Abfragebedingung auftreten.
- optimale Geschwindigkeit bei der Abfrage
- wenn sehr viele Indexe, Zeitaufwand beim Insert/Update
- hohe Entwicklungskosten und Pflegeaufwand

Variante 2: Für jede Spalte die in der Abfragebedingung auftaucht einen eigenen Index.
- Zeitaufwand bei der Abfrage

Meine Erfahrungen decken sich mit dem Zitat von Uwe Raabe:

Bei Interbase ist Variante 1 fast schon zwingend.
Variante 2, erfordert die Abfrage auf dem Server sehr viel Speicher und ist um Größenordnungen langsamer.

Unter Firebird sind Variante 1 und 2 dagegen fast genauso schnell bei der Abfrage.
Deshalb würde ich in der Regel dort Variante 2 einsetzen.

[IBExpert]

kleiner Hinweis in dem Zusammenhang übrigens in Bezug auf Performanceunterschiede bei viel/wenig Indizes
Folgende Werte habe ich vor kurzem mal in einer Kundenschulung ermittelt:

Tabelle

Code:

create table test
(id bigint not null primary key,
txt varchar(80))

Variante 1: auf der Spalte TXT wurde genau 1 Index angelegt
Variante 2: auf der Spalte TXT wurden 100 Indizes angelegt

Test Insert von 10000 Datensätzen

Laufzeit Variante 1 mit einem Index auf TXT: 0,35 Sekunden
Laufzeit Variante 2 mit 100 Indizes auf TXT: 12,31 Sekunden

Zu viele Indizes anlegen, die man eigentlich nicht braucht, ist also ein erheblicher Zeitfaktor beim Schreiben.
Vorteile ergeben sich daher bei Firebird für die Variante single column indizes, weil der Optimierer diese
nach Bedarf kombiniert benutzen kann. Für andere Datenbanken gilt diese Regel aber eben nicht, weil die meisten
eben keine Indizes kombiniert benutzen können.

Ich sehe auch immer wieder Datenbanken beim Kunden, wo gleichartige Indzes mehrfach existieren. Das lässt sich
mit einem SQL in Firebird über RDB$INDICES und RDB$INDEXSEGMENTS relativ einfach finden. Auch non unique Indizes
auf Feldern, wo eh schon unique indizes existieren, ergeben selten Sinn.

Es ist auch hier wie fast immer: Es kommt drauf an, was man braucht und wie die eigene Umgebung damit umgeht.

Ach ja, und ergänzend: Bei Tabellen mit 200 Mio Datensätzen oder noch mehr würde ich auch versuchen, kombinierte
Indizes gemäß der SQL Anforderungen bereitzustellen, weil bei wirklich großen Datenmengen (meiner Meinung nach
ab 10 mio Datensätze) die Kombination von Single Column Indizes nicht unbedingt den gleichen Speed ergibt.
Wenn die Kombinierten Indizes aber nicht passen oder halt was fehlt, wie hier bei der ursprünglichen Kundentabelle,
dann sollte man eben ggf. die verschlechterte Schreibleistung in Kauf nehmen, weil am Ende der Server weniger
Last durch unsinnige Lesevorgänge hat und über alles betrachtet fast immer trotzdem bessere Performance haben wird.


Testgrundlage war hier wieder Firebird 2.5.4

[Dejan Vu]

Es ist grundsätzlich keine gute Idee, in einer zeitkritischen Umgebung irgendwelche Abfragen. Ich würde in solcher einer Umgebung alles auf die Produktivperformance legen und dann über einen Synchronisationsmechanismus (Replikation, Shadow o.ä.) eine 'Reporting-DB' pflegen, die dann eher abfrageoptimiert ist.