MySQL Index Nutzung prüfen und optimieren

03. Mai 2012  |  Kommentar schreiben   |  



Bei Facebook teilen

Es kann nie genug darüber disktutiert werden, wie wichtig die Nutzung von MySQL Indizes ist. Warum – ganz einfach – dies ist eine Voraussetzung für schnelle Ergebnisse, minimale Speichernutzung, minimale MySQL Last und gute Skalierbarkeit. Denn es ist manchmal ziemlich schwierig nachträglich Unmengen von Abfragen zu prüfen und diese zu optimieren, wenn es hoch kommt und der MySQL Server ausgelastet wird. Ein Programmierer sollte immer die schnellstmögliche Abfragen unter 100%-ige MySQL Index Nutzung einsetzen, um ein konsistentes Wachstum zu gewährleisten. Nachfolgend betrachten wir die Möglichkeiten zu prüfen, ob unsere Abfragen die MySQL Indizien richtig nutzen und einsetzen.

Prüfung, ob unsere MySQL Abfrage über den verfügbaren Indizes verfügt

Betrachten wir eine MySQL Beispielsabfrage Query 1:

SELECT Feld, Sortfeld FROM Tabelle1 ORDER BY Sortfeld DESC

Hier sortieren wir die Ergebnisse einer Tabelle nach Sortfeld absteigend. Wenn Sie über kein Index für die Spalte Sortfeld verfügen, so erstellt MySQL eine temporary (vorübergehende) Tabelle, sortiert dort die Ergebnisse und liefert Ihnen den Resultset. Dies ist nicht effektiv, denn hierdurch wird die Abfrage langsam, die Last auf dem MySQL Server steigt und der Server wird mehr beansprucht. Dies können und sollten Sie vermeiden, in dem Sie ein Index über die Spalte, nach der Sie sortieren anlegen. Nachdem Sie das gemacht haben, können Sie nun Prüfen, ob der Index auch genutzt wird, denn das ist nicht immer der Fall:

EXPLAIN [ABFRAGE]

Um zu prüfen, ob Ihre Abfrage den MySQL Index nutzt, schreiben Sie vor Ihrer Abfrage einfach das wort EXPLAIN. Mit freie MySQL Tools wie PHPMyAdmin sehen Sie sehr deutlich das Ergebnis. MySQL listet sämtliche Vorgänge, die sich in der Abfrage abspielen und zeigt, ob ein Index genutzt wird. Für den ORDER BY Vorgang sehen Sie nun zwei wichtige Spalten – possible_keys und key. Am besten sollte unter beiden Spalten Ihren Index stehen.

Wenn bei possible_keys einfach die NULL steht, denn findet MySQL für diesen Vorgang keinen passenden Index. Wenn der Index unter possible_keys steht, aber unter key wieder die NULL steht, so konnte der Index nicht eingesetzt werden. Warum dies passieren könnte, erklären wir gleich. Unter der Spalte rows sehen Sie die Anzahl der Datensätzen, die durchsucht werden, um die ORDER BY oder JOIN auszuführen. Wenn die Anzahl bei rows mit der tatsächElichen Anzahl der Datensätze in der größeren Tabelle (wenn es sich um einen JOIN handelt) übereinstimmt, so ist die Indexnutzung hier nicht optimal, vielleicht ist der Index falsch angelegt worden. Prüfen Sie hierzu den Wert in der Spalte Extra, dort sollte stehen – using index.

Wann ist meine Abfrage optimal was MySQL Indizien betrifft?

Optimieren Sie Ihre Anfrage bis kein Vorgang in der Abfrage ohne Indexnutzung erfolgt. Also bei der Spalte keyy sollte nie NULL vorkommen. Sie werden merken, dass die Abfrage viel schneller läuft.

Ich habe einen Index angelegt, er wird aber nicht genommen

Wenn Sie in Ihrer Abfrage beispielsweise n SELECT * FROM  nutzen, so kann der Index nie genommen werden, denn MySQL kennt den Umfang der Tabelle vor der Ausführung nicht. Daher sollten Sie nie SELECT * FROM nutzen. Eine andere Möglichkeit, wo Sie kein Index einsetzen dürfen ist wenn Sie REGEXP  in Ihrer MySQL Abfrage nutzen. Reguläre Ausdrucke und Indizes sind nicht kompatibel - Sie sollten daher Ihre Tabelle möglichst immer so entwerfen, das keine REGEXP Nutzung erforderlich wird. Es gibt auch die Möglichkeit die Indexnutzung zu erzwingen, wenn mehrere Indizes vorliegen.

MySQL und Serverressourcen

Die Optimierung von MySQL Abfragen kann ein sehr teuer Spaß werden, was Zeit angeht. Sie sollten aber dort einfach nie sparen :)  Denken Sie an Skalierbarkeit und optimale Nutzung von Server und die verfügbaren Ressourcen.

Die Optimierung meiner Abfragen klappt nicht oder es ist für mich nicht verständlich

Dann übernehmen wir das gerne für Sie – sprechen Sie uns an.

Teilen

Hinterlasse eine Antwort

Du musst angemeldet sein, um einen Kommentar abzugeben.