Druckansicht
Kontakt
RSS-Newsfeed
Sitemap
Impressum
MySQL - Abfragen optimieren und slow-query auffinden
Wer ein Datenbank-Management-System wie MySQL einsetzt, der sollte die Leistungswerte im Blick haben. Einzelne SQL-Abfragen können den kompletten Webserver verlangsamen und bevor man die Hardware aufrüstet oder die Zwischenspeicher von MySQL erhöht, lohnt sich die Analyse und Optimierung der SQL-Abfragen. Fehlende Indizes, Tabellen-Joins oder Where-Bedingungen auf nicht indizierte Spalten können die Leistungsfähigkeit drastisch reduzieren.
In diesem Artikel wird anhand eines konkreten Beispiels gezeigt, wie man langsame SQL-Abfragen (slow-sql-query) identifiziert und diese optimiert.
Voraussetzung: langsame SQL-Abfragen (slow-query) und Leistungsbremsen finden
MySQL bietet eine umfangreiche Statistik an, welche mittels phpMyAdmin sehr schön angezeigt werden kann. Dazu klickt man in phpMyAdmin auf Status, so dass man die Laufzeitinformationen angezeigt bekommt. Der überwiegende Teil setzt sehr tiefgreifendes Wissen voraus. Daher sollte man sich mit den Werten beschäftigen, die MySQL bzw. phpMyAdmin rot hinterlegt. In diesem Artikel ist beispielsweise der Wert Handler_read_rnd_next überdurchschnittlich hoch:
Handler_read_rnd_next 141 M
Anzahl der Anfragen, die nächste Zeile in der Daten-Datei zu lesen. Dieser Wert wird hoch sein, wenn Sie viele Tabellen-Scans durchführen. Im Allgemeinen weist das darauf hin, dass Ihre Tabellen nicht korrekt indiziert sind, oder dass Ihre Anfragen nicht so geschrieben sind, dass Sie Vorteile aus den Indexen ziehen, die Sie haben.
Vereinfacht ausgedrückt: MySQL möchte uns sagen, dass aufgrund fehlender Indizes eine Tabelle vollständig gescannt werden muss, um darin Werte zu finden. D.h. alle Zeilen / Datensätze werden nach den gefragten Kriterien durchsucht, was bei großen Tabellen enorm Zeit kostet. Mithilfe eines Index entfällt dieser Komplett-Scan.
Zusätzlich bietet MySQL ein separates Logfile an, welches langsame SQL-Abfragen protokolliert. Dieses "slow query logfile" wird in der MySQL-Konfigurationsdatei my.cnf aktiviert. Folgende Einträge sorgen dafür, dass SQL-Abfragen, welche länger als eine Sekunde (long_query_time=1) in einer Logdatei (log-slow-queries) gespeichert werden.
# Auszug /etc/mysql/my.cnf
[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/mysql-slow-queries.log
Folgende SQL-Abfrage, welche lediglich die zehn letzten Beiträge aus einem phBB3-Forum ausliest, wurde als langsame SQL-Abfrage protokolliert:
# --- Auszug /var/log/mysql/mysql-slow-queries.log
# Time: 111123 13:20:27
# User@Host: user[user] @ localhost []
# Thread_id: 1803 Schema: usr_web_2
# Query_time: 1.096340 Lock_time: 0.000120 Rows_sent: 10 Rows_examined: 55772 Rows_affected: 0 Rows_read: 55772
SELECT post_time, post_subject, post_id, post_text, forum_id
FROM phpbb_posts
WHERE newsletter = 1
ORDER BY post_time DESC
LIMIT 10
EXPLAIN: einzelne SQL-Abfragen analysieren
Betrachten wir den oben genannten Query und setzen diesen in phpMyAdmin ab:
SELECT post_time, post_subject, post_id, post_text, forum_id
FROM phpbb_posts
WHERE newsletter = 1
ORDER BY post_time DESC
LIMIT 10
Das Ergebnis ist eine Ausführungsdauer von rund 0,08 Sekunden:
SQL-Abfrage vor der Optimierung - Ausführung in ca. 0,08 Sekunden
Nun setzen wir diesem SQL-Query den Ausdruck EXPLAIN, so dass uns MySQL etwas mehr über den Query erzählt. Der SQL-Query wird analysiert und jede Abfrage auf eine Tabelle als Ergebnis mit Informationen angezeigt. In folgendem Beispiel erfolgt nur ein einziger Zugriff auf die Tabelle phpbb_posts:
SQL-Query mit Explain
Diese SQL-Abfrage auf die Tabelle phpbb_posts ist schlecht. Aber woran erkennt man diese? Mit der Information Using Where; Using filesort sagt uns MySQL, dass nur bestimmte Datensätze mittels where abgefragt werden sollen, welche anschließend sortiert werden. Das Problem: die Spalte newsletter hat keinen Index, was man anhand von possible_keys (alle möglichen und verwendbaren Indizes in der Tabelle) und key (der Index, für den sich MySQL entschieden hat) erkennt. Aus diesem Grund werden ALLE 55772 Datensätze der Tabelle phpbb_posts auf den Wert newsletter überprüft. Das sorgt bei großen Tabellen für Leistungseinbußen.
Ein Blick in Googles Page Speed zeigt uns die Ladedauer der Webseite:
Google Page Speed zeigt die Ladedauer
Doch wie behebt man nun die Leistungsbremse? Generell kann man sich merken: Bei Where-, Order By- und Join-Abfragen sollten die entsprechenden Tabellenspalten immer als Index deklariert werden. Die Aufgabe ist es nun, der Spalte newsletter einen Index zu verpassen.
Hinweis: Eine ausführliche Erläuterung findet man auf der MySQL-Webseite unter EXPLAIN-Syntax und Wie MySQL Indizes benutzt.
Nachdem der Index auf die Spalte newslettet gesetzt wurde, benötigt dieselbe Abfrage nur noch 0,003 anstatt 0,08 Sekunden. Eine deutliche Verbesserung.
SQL-Abfrage nach der Optimierung
Betrachtet man nun den Query mittels EXPLAIN erkennt man, dass MySQL den Index auf die Spalte newsletter verwendet. Die Anzahl der zu durchsuchenden Datensätze reduziert sich von 55772 auf 476 Datensätze. Es ist also selbsterklärend, warum MySQL nach dem Setzen des Index spürbar schneller ist.
MySQL verwendet den Index newsletter
Auch Googles Page Speed bestätigt die Leistungssteigerung. Das Warten auf das HTML-Dokument verringert sich um den Zeitgewinn durch die SQL-Optimierung.
Googles Page Speed verdeutlicht den Leistungssgewinn