7. January 2007, 18:32, by Silvan Mühlemann

Optimierung von MySQL-Abfragen: Verwendung des Index

DolphinVor 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.

Filed under: Database

12 Comments

  1. […] Wer selbst Webanwendungen entwickelt und sich mit Datenbank noch nie wirklich tiefergehend beschäftigt hat, dem lege ich die aktuelle MySQL Optimierungs-Serie vom tillate Techblog ans Herz. Im aktuellen Beitrag wird erklärt, wie man mit dem Setzen von korrekten Indizes MySQL Queries extrem beschleunigen kann. […]

    Comment by Schnellere Queries durch Indizes - Webtechnologie - blogged on — 7. January 2007 @ 19:40

  2. Cooler Artikel, aber was noch viel tooler ist: OpenId läuft bereits! Habe einfach meine Blog-Adresse angegeben (auf gut glück) und siehe da! Alles klappt, einfach so!

    Comment by http://leo.freeflux.net/blog/ — 10. January 2007 @ 16:57

  3. Leo: Klar doch, Flux CMS unterstützt OpenID schon länger als so genannter Identity Provider :)

    Comment by http://blog.bitflux.ch/ — 10. January 2007 @ 18:18

  4. Darf ich fragen welche Tabellen-Engine du verwendest, Silvan?

    Ich kämpfe momentan auch mit einer grossen Tabelle (4.5 Mio Datensätzen) rum und habe jetzt mal auf InnoDB gesetzt.

    Die Features auf http://dev.mysql.com/doc/refman/5.0/en/innodb-overview.html sehen recht vielversprechend aus.

    Gruss Simon

    Comment by Simcen — 11. January 2007 @ 09:33

  5. Das schöne an MyISAM ist die einfache Handhabung der Datenfiles. Eine Tabelle = 3 Files auf der Disk (.MYD,.MYI,.frm). Wenn ein Slave eine korrupte Tabelle hat, kann man so einfach die defekte Tabelle vom Master holen und per scp auf den Slave kopieren.

    Bei InnoDB haben wir pro Datenbank ein oder mehrere File (bei Bedarf gesplittet in 2GB-Dateien). Die Operation oben funktioniert nicht.

    Comment by Silvan Mühlemann — 12. January 2007 @ 13:13

  6. Hallo Silvan

    Du hast Recht, MyISAM ist sehr praktisch für Filesytem-basierte Backupmeachnismen.

    Aber MySQL bietet von Hause aus eine sehr gute Replikationsmöglichkeit. Damit lassen sich sehr komplexe Systeme realisieren (z.B. mit ein wenig Geschick ein DB-Loadbalancing durch Multi-Master Replikation). Ausserdem hast du eine Realtime-Backuplösung und innert weniger Minuten eine komplette Master-DB ohne Verlust wieder hergestellt.

    Comment by http://simcen.pip.verisignlabs.com/ — 21. January 2007 @ 16:07

  7. Ciao Simcen

    Wir verwenden schon seit fünf Jahren die MySQL-Replikation. Momentant haben wir drei Master im Kreis mit je ca. 8 Slaves.

    Teilweise versagt diese Replikation und eine Tabelle lässt sich selbst mit myisamchk nicht reparieren. Dann ist es praktisch, sich von einer anderen Maschine die *.MYD|MYI|frm zu holen.

    Stefan kann hier sicher noch was dazu sagen.

    Silvan

    Comment by Silvan Mühlemann — 21. January 2007 @ 20:09

  8. Irgendwie bin ich beruhigt, hätte mich schon
    gewundert wenn ihr davon nichts “wusstet” ;)

    Genau solche Konstrukte gefallen mir, aber muss sagen dass es den Administrationsaufwand nicht gerade klein hält.

    Okay ich sehs. Wenn also ein Master aus dem Ring ausfällt, könnt ihr von einem Slave die Daten zurückholen. Der Kreis sorgt dann dafür, dass der “neue” Master auf den richtigen Stand kommt, hab ich recht?

    Bei Uns setzten wir z.B. Oracle-DB’s ein, dort gibt es den DataGuard, der ziemlich ähnlich auch auf Basis von Bin-Logs arbeitet. DIes ist praktisch wenn z.B. Content-Editing nur in der gesicherten Zone (Intranet) erlaubt ist und Zugriffe von der DMZ ins Intranet nicht erlaubt sind. Über die Firewall wird dann jedes Query auf einen Readonly-Cluster geschrieben. Kostet halt was ;(

    Comment by Simcen — 21. January 2007 @ 20:49

  9. […] weiterführende sehr gute Infos gibt es z.B. hier:  Optimierung von MySQL-Abfragen: Verwendung des Index […]

    Pingback by MySQL dur Indexe beschleunigen « individual-it.net Blog — 1. September 2009 @ 18:26

  10. […] der Index-Frage: techblog.tilllate.com Optimierung von MySQL-Abfragen: Verwendung des Index MySQL Datenbankhandbuch: MySQL-Sprachreferenz mit vielen Beispielen: CREATE INDEX Wegen der […]

    Pingback by Anonymous — 25. January 2010 @ 23:43

  11. […] dieser Stelle muss ich mich bei Silvan Mühlemann von techblog.tilllate.com bedanken, sein Artikel “Optimierung von MySQL-Abfragen: Verwendung des Index” hat mich auf die richtige Spur bzw. eigentlich gleich zur richtigen Lösung gebracht: Nutzt man 1 […]

    Pingback by MySQL und Index – oder: warum %-Zeichen böse sind | martin-mueller.info — 18. May 2011 @ 15:49

  12. […] verstanden habe, was ein INDEX ist und wie man ihn am besten verwendet.Dazu haben mir sehr geholfen dieser Blogeintrag und die Antwort auf diese Frage bei stackoverflow.com.Hier noch mal eine fixe […]

    Pingback by MySQL Indexe und wofür man welche verwendet - ProkrastiNation — 20. May 2012 @ 17:17

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

© 2017 tilllate Schweiz AG - Powered by WordPress