MySQL-Index

[Namenloser]

Hallo,

in einem WBB3-Forum, das ich mitbetreue, haben wir so 1–2 mal am Tag das Problem, dass für ca. 5–10 Minuten der MySQL-Server komplett blockiert ist. Dazwischen ist die Performance in Ordnung.

Ich habe jetzt mal das Slow-Query-Log eingeschaltet und einige der dort auftauchenden Queries mit EXPLAIN aufschlüsseln lassen, um zu gucken, ob Indizes benutzt werden usw..

Dabei ist mir jetzt durch Zufall was aufgefallen:

Code:

mysql> explain SELECT          postID, attachments, pollID
    ->                         FROM            wbb1_1_post post
    ->                         WHERE threadID = 15 AND isDisabled = 0
    ->                         ORDER BY        post.time ASC LIMIT 152520, 40;
+----+-------------+-------+------+---------------------+----------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys       | key      | key_len | ref   | rows   | Extra                       |
+----+-------------+-------+------+---------------------+----------+---------+-------+--------+-----------------------------+
|  1 | SIMPLE      | post  | ref  | threadID,isDisabled | threadID | 4       | const | 112119 | Using where; Using filesort |
+----+-------------+-------+------+---------------------+----------+---------+-------+--------+-----------------------------+

Man beachte: Für jede der wichtigen Spalten existiert ein separater Index: threadID, isDisabled und time (auch wenn man letzteres hier nicht sieht – warum auch immer). Dennoch wird der Index zum Sortieren nicht verwendet.

Dann hab ich (eigentlich aus Versehen) einen Index über alle 3 Spalten angelegt. Nun wieder den gleichen Query wie oben:

Code:

mysql> explain SELECT          postID, attachments, pollID
    ->                         FROM            wbb1_1_post post                         
    ->                         WHERE threadID = 15 AND isDisabled = 0                         
    ->                         ORDER BY        post.time ASC LIMIT 152520, 40;
+----+-------------+-------+------+--------------------------------+------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys                  | key        | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+--------------------------------+------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | post  | ref  | threadID,isDisabled,threadID_3 | threadID_3 | 4       | const | 112161 | Using where |
+----+-------------+-------+------+--------------------------------+------------+---------+-------+--------+-------------+

Dieses threadID_3 ist also mein Index über (threadID, isDisabled, time). Hoppla – plötzlich kein Filesort mehr nötig?

Heißt das also im Klartext, MySQL kann pro Query immer nur einen einzigen Index verwenden und nicht mehrere kombinieren? Wäre es dann nicht schlauer immer nur einen Index anzulegen, über alle möglichen Spalten? Oder gäbe es da irgendwelche Nachteile?

Ich brauch eine Erklärung :)

[sx2008]

Zunächst mal: Ein Index über ein Boolean-Feld ist sinnlos, denn die Chance beträgt 50:50 und damit kann man sich den Index sparen weil dann ein Full-Table-Scan schneller ist.
==> keinen Index über ein einzelnes Boolean-Feld anlegen

Dann: in dem 2.Fall ist kein Filesort mehr nötig, weil der Index die Daten schon in der Reihenfolge liefert, die nach ORDER-BY verwendet werden soll.

Je mehr Indexe umso schneller können potentiell Abfragen laufen und umso mehr Aufwand beim Speichern und desto höher der Resourcenverbrauch (RAM, Diskspace).

Meine Empfehlung wäre je einen Index "threadID" und "time".
Evtl. könnte ein zusammengesetzter Index aus "threadID" und "time" sparsamer und schneller sein, aber das kommt auf die Abfragen an.

[Namenloser]

Danke erstmal für die Antwort.

Zitat:

Zitat von sx2008 (Beitrag 1208615)

Meine Empfehlung wäre je einen Index "threadID" und "time".

So war es ja, aber da wurde der time-Index nicht benutzt! Ich will wissen, wieso.

[sx2008]

Zitat:

Zitat von NamenLozer (Beitrag 1208617)

Zitat:

Zitat von sx2008 (Beitrag 1208615)

Meine Empfehlung wäre je einen Index "threadID" und "time".

Aber so war es ja, und da wurde der time-Index nicht benutzt! Ich will wissen, wieso.

Ob ein Index benutzt wird oder nicht hängt davon ab wie das DBMS die Kosten für bestimmte Operationen einschätzt.
Mal angenommen die Einschränkung WHERE threadID=13 liefert 500 Datensätze und über die Einschränkung "isDisabled = 0" werden es 350 Datensätze.
Sollte es einen separaten Index für "time" geben und es gibt insgesamt 50000 Datensätze, dann ist es wahrscheinlich günstiger die 350 Datensätze einfach zu sortieren als über den gesamten Index von "time" zu gehen und in der Reihenfolge die Daten auszugeben.

Ganz grob gesagt sind weniger als 1000 Datensätze im Speicher schneller sortiert als sie mit Hilfe eine Index in die richtige Reihenfolge gebracht würden.
Nicht immer trifft das DBMS die richtige Entscheidung, denn es kommt auch darauf an wie die Daten verteilt sind.

In deinem 2. Fall ist der Index so angelegt, dass über die ersten beiden Felder gefiltert wird (WHERE threadID = 15 AND isDisabled = 0) und danach die Daten genau in der Reihenfolge des Index vorliegen.
Hier entscheidet MySQL richtig und verwendet den Index auch zur Sortierung.

Im 1. Fall ist es günstiger die Daten nur über den Index "threadID" zu ziehen, dann alles auszufiltern was nicht der Bedingung "isDisabled = 0" gehorcht und dann die Daten im Speicher nach "time" zu sortieren.
Anscheinend ist die Datenmenge zu gering, als das es einen Vorteil brächte den Index "time" zu benützen.

[Namenloser]

Ok, hab’s glaub ich verstanden. Danke für die Erklärung :thumb: