Optimierung von MySQL-Abfragen: Verwendung des Index

Vor zwei Wochen habe ich erklärt, wie man mit dem Slow-Query-Log die langsamsten Datenbank-Abfragen identifizieren kann. Nun möchte ich besprechen, wie man diese langsamen Queries beschleunigen kann. Nutze den Index ist die Zauberformel.

Die erste Frage: Was ist der Index? – Nun, das ist Euch bestimmt bekannt. Sonst hilft Wikipedia.

Schaut man sich an, wie die Dateien bei MySQL (MyISAM-Struktur) auf der Disk gespeichert ist, so ist die Index-Datei an der Endung MYI zu erkennen (MYD sind die effektiven Daten, frm die Tabellendefinition):

cameron tilllate # ls -lh users.*
-rw-rw---- 1 mysql mysql 65M Jan  7 16:51 users.MYD
-rw-rw---- 1 mysql mysql 90M Jan  7 16:51 users.MYI
-rw-rw---- 1 mysql mysql 15K Jan  6 06:20 users.frm

Die Index-Datei users.MYI wird bei MySQL im RAM gehalten, während die effektiven Daten (Usernamen, Passwörter, Adressen) auf der Disk bleiben. Ein Grund, dass der Zugriff schnell ist.

Wie schnell ist der Index?

Eine Query ohne Index:

mysql> SELECT id,username,geschlecht FROM users WHERE id=26040;
+-------+----------+------------+
| uid   | username | geschlecht |
+-------+----------+------------+
| 26040 | testuser |          1 |
+-------+----------+------------+
1 row in set (0.36 sec)

…braucht 0.36 Sekunden…

Ich setze einen Index:

mysql> ALTER TABLE users2 ADD KEY(id);
Query OK, 350108 rows affected (2.27 sec)
Records: 350108  Duplicates: 0  Warnings: 0

…und lasse die Query nochmals laufen…

mysql> SELECT id,username,geschlecht FROM users WHERE id=26040;
+-------+----------+------------+
| uid   | username | geschlecht |
+-------+----------+------------+
| 26040 | testuser |          1 |
+-------+----------+------------+
1 row in set (0.01 sec)

Boah! Nur noch 10ms… das ist 36x schneller… Dieser Faktor vergrössert sich, je mehr Daten in der Tabelle sind. Steigern sich die Anzahl Zeilen von 10’000 auf 100’000, so braucht die Suche ohne Index 10x länger (= lineares Wachstum). Mit dem Index dauert die Query nur gerade doppelt so lang (= logarithmisches Wachstum). Gerade bei grossen Tabellen lohnt sich eine sorgfältige Wahl des Indexes.

Welche Spalten indizieren?

Die einfache Antwort: Ich indiziere jene Spalten, welche oft in der WHERE-Clause einer Query vorkommen. Dazu gehören Primär- und Fremdschlüssel. Ausserdem jene Spalten, welche in der ORDER-Clause auftreten.

Wie finde ich nun heraus, ob MySQL die Indizes auch verwendet?

EXPLAIN SELECT weiss es

Indem ich das Keyword EXPLAIN einem SELECT-Statement voranstelle, erfahre ich, ob der Index verwendet wird. Im Beispiel oben sieht das so aus:

Ohne Index:

mysql> EXPLAIN SELECT * FROM users2 WHERE id=26040;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users2 | ALL  | NULL          | NULL | NULL    | NULL | 350108 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Mit Index:

mysql> EXPLAIN SELECT * FROM users2 WHERE id=26040;
+----+-------------+--------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | users2 | ref  | id            | id   | 4       | const |    1 |       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.01 sec)

Wichtig hier sind für mich die Spalten key und rows.

In key sehe ich, welcher der möglichen Indexes aus possible_keys für die Query verwendet wurden.

In rows steht, wieviele Zeilen die DB-Engine sequentiell auf der langsamen Festplatte durchlaufen musste, um den Datensatz zu finden (“table scan” genannt). 350’108 im Fall ohne Index. Übel!

Auf der Suche nach den Sexy-Girls

Schauen wir uns eine andere Query an, welche wir optimieren wollen. Alle weiblichen Member deren Membername mit “sexy” beginnt:

mysql> SELECT username FROM users WHERE geschlecht=2 AND username LIKE "sexy%";
+---------------------+
| username            |
+---------------------+
| sexy                |
| sexy-angie-84       |
| sexy-boriqua        |
| sexy-brasil-girl    |
| sexy-c              |
| sexy-caramel        |
[...gähn...]
| sexy_vesna          |
| sexy_xxx            |
+---------------------+
509 rows in set (2.93 sec)

Hmm, da halten sich einige für ziemlich sexy 😉

Natürlich wird kein Key verwendet. Darum auch die 2.93 Sekunden Laufzeit. Wir haben ja noch keinen Key gesetzt. Holen wir dies doch nach:

mysql> ALTER TABLE users ADD KEY username(username),ADD KEY geschlecht(geschlecht);
Query OK, 380724 rows affected (2 min 8.11 sec)
Records: 380724  Duplicates: 0  Warnings: 0

Und testen wie die Query:

mysql> EXPLAIN SELECT username FROM users WHERE geschlecht=2 AND username LIKE "sexy%";
+----+-------------+-------+-------+---------------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys       | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | username,geschlecht | username | 92      | NULL |  867 | Using where |
+----+-------------+-------+-------+---------------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

Was? Jetzt haben wir doch zwei Keys gesetzt und nur einer wird verwendet?

Nun, es kann pro Tabelle und Query nur ein Index verwendet werden. Es wird jener verwendet, welcher eine geringere Zahl von Zeilen zurück gibt. In diesem Fall ist es der Index auf username. Denn der Index auf die Spalte geschlecht würde rund die Hälfte der Datensätze zurückgeben. Also Rund 150’000 Rows.

Indizes über mehrere Spalten

Will man, das beide Indizes verwendet werden, um die Anzahl Rows in der spalte rows vom EXPLAIN-SELECT runterzubringen, müssen wir einen Index über zwei Spalten anlegen:

mysql> ALTER TABLE users ADD KEY username_geschlecht(username,geschlecht);
Query OK, 380724 rows affected (2 min 28.63 sec)
Records: 380724  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT username FROM users WHERE geschlecht=2 AND username LIKE "sexy%";
+----+-------------+-------+-------+-----------------------------------------+---------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys                           | key                 | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-----------------------------------------+---------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | range | username,geschlecht,username_geschlecht | username_geschlecht | 93      | NULL |  607 | Using where; Using index |
+----+-------------+-------+-------+-----------------------------------------+---------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Super, jetzt ist die Query noch schneller. Anzahl durchsuchte Rows von 867 auf 607 gesunken!

Ein Wermutstropfen: Diesen Kombi-Index “username_geschlecht” kann nur für Bedingungen über die Spalten username und geschlecht (WHERE username LIKE 'big%' AND geschlecht=1) oder username (WHERE username='big%') alleine verwendet werden. Eine Bedingung, welche nur eine hintere Spalte des Indexes verwendet (WHERE geschlecht=2) kann den Index nicht verwenden.

Index auf alle Spalten setzen?

Am besten, man setzt also ein Index auf alle Spalten und Spaltenkombinationen?

Nein. Denn Indizes kosten. Sie brauchen Platz auf der Disk. Teilweise sogar mehr als die Daten selbst (siehe unser Beispiel oben). Und sie müssen bei jeder Aenderung des Datenbestands geschrieben bzw. angepasst werden. Sie machen INSERTs und UPDATEs also langsamer.

Sie brauchen auch Pflege: MySQL pflegt Metainformationen über den Index, welche regelmässig mit ANALYZE TABLE aktualisiert werden müssen.

Ich würde nur Indizes setzen auf Spalten in welchen viele verschiedene Werte gespeichert werden (=hohe Kardinalität). Die Daumenregel ist folgende: Wenn eine Spalte weniger als 3 unterschiedliche Werte beinhaltet, dann bringt der Index nichts.

Dies ist nur ein kurzer Abriss über die Verwendung von Indizes bei Queries über eine Tabelle. Mehr Infos kriegt ihr bei MySQL selbst. Queries über mehrere Tabellen sind ein Thema für sich und werde ich wohl zu einem späteren Zeitpunkt besprechen.