MySql - Teil 1: Den richtigen Index finden

June 03, 2008 by thilo

Die Datenbak MySql ist bei Webanwendungen sehr verbreitet, unter anderem weil sie kostenlos, relativ schnell und etabliert ist. Auch bei uns verrichtet MySQL zuverlässig seinen Dienst. Grund genug mal unsere Erfahrungen in ein paar Blogeinträge zu gießen und damit das rumlavieren in der sehr umfangreichen offiziellen Dokumentation auf echte Härtefälle zu beschränken.

Zuerst schauen wir uns mal an welche Werkzeuge MySQL von Hause aus mitbringt, um beim Erstellen von sinnvollen Indizes zu helfen.

Mit EXPLAIN vor einem SELECT in der MySQL Shell lässt sich erst einmal herausfinden, wie einzelne Abfragen ausgeführt werden. So lassen sich gut Informationen über verwendete und fehlende Indizes sammeln. Das Ergebnis von EXPLAIN wird standardmäßig als Tabelle ausgegeben. Hier kurz was die Angaben bedeuten. Die Spalte table gibt den Namen der abgefragten Tabelle an. Unter type steht die Art des Lesezugriffs. Steht dort ALL wird ein Table Scan durchgeführt. Unter possible_keys stehen die Namen der verfügbaren Indizes. Die Spalte key enthält die bei der Abfrage verwendeten Index. Es kann nur ein Index verwendet werden. Unter extra stehen die Einschränkungen nach denen die Zeilen ausgewählt werden. Steht unter type ALL, aber in den Bedingungen ‘using where’ fehlt mit Sicherheit ein geeigneter Index. Ein Grund, warum selbst ein vorhandener Index nicht verwendet wird, ist, dass die WHERE-Bedingung zu weit gefasst ist. Unter diesen Umständen ist die Verwendung eines Indizes aufwendiger als ein Table Scan. Ein gutes Beispiel für eine zu weit gefasst Abfrage ist, alle Frauen aus einer Nutzertabelle zu laden, da etwa die Hälfte aller Nutzer Frauen sind, ist der Aufwand für den Einsatz von Indizes viel zu groß und wird daher von MySQL ausgeschlossen. Als Richtwert gilt, wenn 20% oder weniger Zeilen durch eine Bedingung erfasst werden ist der Einsatz eines Indexes sinnvoll. Oder anders herum gesagt, ein Index sollte eine gute Selektivität besitzen. Mit EXPLAIN tablename lässt sich herausfinden, welche Spalten einer Tabelle bereits mit einem Index versehen sind.

Aber nicht nur die Selektivität eines Index ist entscheidend, denn jeder Index kommt zu dem Preis, dass er bei einer Änderung der Tabelle mit geändert werden muss und so INSERTS, UPDATES und DELETES länger dauern. Also ist auch darauf zu achten wie viele Spalten der Index umfasst und wie lang die Schlüssel sind. Darum ist es gut ersteinmal zu ermitteln, welche Abfragen am meisten von einem Index profitieren würden. Um diese im Live-Betrieb zu ermitteln ist der Loggingmechanismus von MySQL ein praktisches Werkzeug. Das Logging lässt sich in der Konfigurationsdatei my.cnf (bei *nix Systemen in /etc/mysql/ zu finden) des Servers einrichten. Um Abfragen, die besonders lange dauern, mitzuloggen, muss dies mit log_slow_queries = /var/log/mysql/mysql-slow.log aktiviert werden. Mit dem Parameter long_query_time = 2 kann in Sekunden angegeben werden, ab welcher Ausführungsdauer eine Abfrage mitgelogged wird. Um Abfragen ins Log zu schreiben die keinen Index verwenden kann log-queries-not-using-indexes in die Konfigurationsdatein eingetragen werden. Vorsicht, diese Einstellung kann ordentlich viel Output erzeugen.

Dass waren jetzt die einfachen Bordmittel, die dabei helfen können, sinnvolle Indizes zu vergeben. Das Letzte Wort zu Indices ist damit aber noch nicht gesprochen, denn beim Locking, womit sich der nächste Post befasst, spielen sie auch eine wichtige Rolle.

This entry was posted on June 03, 2008 by thilo.