nicht angemeldet
Hallo Cheatah,
Wenn ich mit zwei Indexzugriffen jeweils 0.0001%
der Datensatzmenge isolieren und dies dann mit
sich selbst JOINen könnte, dann wäre das
rasend schnell, verglichen mit einem full table
scan.
das ist richtig, nur sprechen zwei Gründe dagegen:
1.) ist es (bei "handelsüblicher" Datenmenge einer
privaten Website) sehr unwahrscheinlich, eine solche
Erfolgsquote zu haben; trotz günstigster Indizes.
Bei einer Tabelle mit 10000 Einträgen wäre der Primärschlüsselindex ein solcher. Und der ist auch der wahrscheinlichste Kandidat, überhaupt zu existieren.
In der Regel wird ein _guter_ Index rund 10% der
Daten zurückliefern
Himmel, nein!
10% ist schon fast zu schlecht, um den Index überhaupt erst anzulegen. Unter 10-15% (d. h. 7-10 verschiedene Werte) brauchst Du gar nicht erst anzufangen - da ist der full table scan wirklich besser.
Sämtliche Indexe meiner aktuellen Suchmaschinen-Implementierung projezieren um zwischen 2 und 6 Zehnerpotenzen! _Das_ sind "gute" Indexe. :-)
Der Organisationsaufwand hierzu, verbunden mit dem
Organisationsaufwand der Suche nach dem passenden
Index, ist hoch.
Das ist wahr.
Aber ein guter query optimizer wird auch einen hohen Aufwand betreiben wollen, wenn er eine oder mehrere Zehnerpotenzen gewinnen kann - vor allem, wenn dasselbe Statement immer wieder ausgeführt werden muß!
"analyze table" ist der Weg dorthin - die Frage ist lediglich, welche Informationen gespeichert werden und wer die sich zunutze macht.
2.) weiß die Datenbank normalerweise nicht, ob ein
Index (und wenn ja, welcher) derartige Erfolge
liefern kann; denn wenn er bei der einen Abfrage 1%
der Daten liefert, liefert er bei einer anderen
vielleicht 99%.
Doch - das weiß sie sehr genau, wenn sie die Tabelle per "analyze" untersucht hat.
Würde ich "analyze" implementieren, dann würde ich auf jeden Fall speichern:
- die Anzahl der verschiedenen Werte (und damit die mittlere Projektivität des Index) sowie
- eine Liste aller Werte, die mehr als 10% der Treffer liefern würden. (Das können höchstens 10 Stück sein - und die kann ich auch noch in einem Baum der Tiefe 4 speichern, wenn es denn sein muß ... ;-)
Und bei einem Indexzugriff würde ich diese Liste prüfen, um einen Indexzugriff für diese "pathologisch schlechten" Werte zu verhindern.
Je höher die Summe der Verwendungen von Werten in dieser Liste sind (also je wahrscheinlicher ich bei dieser Zusatzprüfung einen Wert finde), desto sicherer optimiere ich den Ausführungsplan dadurch, daß ich mich gegen den Indexzugriff und für den full table scan entscheide.
Ich kann also sehr wohl zwischen "guten" und "schlechten" Indexen unterscheiden - beispielsweise schon dadurch, daß bei "guten" Indexen die Stopwortliste leer ist. Außerdem kann ich auch noch die Wahrscheinlichkeit für das Auftreten eines Wertes außerhalb der Stopwortliste speichern ...
Und Oracle macht das auch intern so ähnlich - um sich beispielsweise zwischen zwei möglichen Indexzugriffen für den besseren zu entscheiden. Wahrscheinlich kennt Oracle nur die mittlere Projektivität der Tabellen - was im Schnitt auch schon zu sehr guten Ergebnissen führt (wenn Index A 10 verschiedene Werte liefert und Index B 10000 ...).
Die von mir beschriebene Zusatzprüfung der "worst case list" würde die Effektivität m. E. noch etwas erhöhen. Jedenfalls in Fällen einer entsprechenden Ungleichverteilung.
Ob eine solche tatsächlich vorliegt, weiß einerseits der Admin und andererseits die Datenbank aufgrund des "analyze"-Ergebnisses. Es wäre also genauso gut denkbar, ein solches Feature einfach immer einzuschalten, wie es über einen Konfigurationsschalter zur Verfügung zu stellen.
Zwar kann man dazu statistische Analysen
durchführen; aber selbst Oracle, bei dem sowas
gang und gäbe ist und das mit Datenmassen immenser
Größe vertraut ist, erlaubt pro Tabellenabfrage nur
einen Index.
Ich weiß. Der vor mir beschriebene Fall war auch "konstruiert".
Ich würde von keinem DBMS erwarten, dass es, nur
weil es "bei dieser Abfrage zufällig sinnvoll ist",
Dinge tut, von denen selbst die mächtigsten Systeme
Abstand nehmen.
Ich hinterfrage eben gerade dieses "Abstand nehmen".
Und es kann gut sein, daß es sich wirklich nicht lohnt, bestimmte Dinge zu implementieren.
Aber ein execution plan optimizer ist KI - da ist es sehr schwer, irgend einen Alternativplan von vorn herein als untauglich auszuschließen. Es gibt immer Anwendungsfälle, in denen genau er die Rettung wäre.
SELECT <fields> FROM <tablename>
WHERE <field1> LIKE 'x%'
AND <field2> LIKE 'y%';
Bilde das mal mit einem einzigen Index über
beiden Spalten nach ...
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BAK_CATEGORY'
2 1 INDEX (RANGE SCAN) OF 'TESTINDEX' (NON-UNIQUE)
Also, der Index wird verwendet.
Ja - für die erste der beiden WHERE-Klauseln. Aber nicht für die zweite. (Wie denn auch? ;-)
Klar erkennt Oracle, daß es mit dem zweispaltigen Index wenigstens die erste der beiden Spalten adressieren kann - aber die zweite nicht, weil es dafür einen String-Match über ein concat beider Spaltenwerte machen müßte, und das geht nicht wegen der wildcard am Ende der ersten WHERE-Klausel.
Wäre die Abfrage
SELECT <fields> FROM <tablename>
WHERE <field1> = 'x'
AND <field2> LIKE 'y%';
gewesen, dann hätte die Datenbank den kombinierten Index für _beide_ WHERE-Klauseln nutzen können.
(Und dann wäre die Reihenfolge der beiden Spalten im Indexbaum entscheidend gewesen.)
Ich kann natürlich nicht dafür sprechen, dass
MySQL das auch tut :-)
In diesem eingeschränkten Sinne - bestimmt. Es muß ja nur erkennen, daß es nur den ersten Teil des Indexbaums nutzen darf, den zweiten also praktisch als "%"-wildcard ansehen. Also:
SELECT <fields> FROM <tablename>
WHERE <field1> LIKE 'x%'
AND <field2> = 'y';
und
SELECT <fields> FROM <tablename>
WHERE <field1> LIKE 'x%'
AND <field2> LIKE '%';
müßten intern dieselben Indexzugriffe verursachen, falls ein gemeinsamer Index über (<field1>,<field2>) existiert - und auch dieselben, wie wenn der Index nur über <field1> gelegt worden wäre. Beide execution plans müssen sich genau durch den zusätzlichen Vergleich der Ergebniswerte mit 'y' unterscheiden, denke ich.
Viele Grüße
Michael