Mysql Abfragen optimieren

Entscheidend ist bei einer DB Abfrage nicht nur das man das gewünschte Ergebnis erhält sondern auch wie man es anstellt um eine möglichst geringe Last bei Mysql zu erhalten und damit auch eine absolut optimale Zeit bei der Ausführung und für die Applikation selbst.

Man muss immer daran denken das es nicht der Entwickler ist der da einsam und allein sein Ergebnis sieht und sagt - das ist gut, schnell und optimal - es sind möglicherweise tausende Nutzer gleichzeitig die den Hammer schwingen werden.

Es gibt dazu Werkzeuge die einem bei der Optimierung helfen.

PHPMyAdmin in der aktuellen Version bietet da  einiges an - auch grafisch dargestellt.
So sieht eine sehr komplexe Abfrage dann ausgewertet aus:

Die gleiche Abfrage aber dahingehend optimiert das ein Index verwendet wird:

Und nun weiter optimiert in der Form das man die enthaltenen Subselects ebenfalls auf einen Index zwingt:

Unterm Strich wird der benötigte Zeitaufwand für die Auswertung dramatisch kleiner.

Bei der Verteilung ist klar zu erkennen, das die Ausgabe der Daten die meiste Zeit beanspruchen - völlig klar wenn die anderen Zeiten schrumpfen.
Deshalb sollte man in dem Zustand auf die Ausgabe unnötiger Daten komplett verzichten also  nichts mit * wenn es nicht sein muss.

Ein Punkt bei vielen Entwicklern die keine Lust haben einen Rattenschwanz von Feldbezeichnungen zu schreiben.

Hier einmal eine ganz einfache SELECT * FROM tablename ... ORDER BY feldname Anweisung und Erzwingung einer Indexnutzung und ihre Wirkung:

und hier die gleiche Anweisung aber es ist kein Index vorgegeben - auch wenn der vorhanden ist:

Wie man sehr deutlich erkennen kann wird bei der Abfrage ohne Index - Erzwingung 52% der Zeit damit verbraten - die benötigte Gesamtzeit ist 2.5 x höher.

Das zeigt deutlich einen Schwachpunkt.

So manche Entwickler haben diverse Index angelegt und sind der Meinung Mysql würde sich stets den passenden Index heraus suchen - wenn denn einer vorhanden ist .

Großer Irrtum - nur der Entwickler weiß welche Vorteile er sich wo mit einem Index erkaufen will und muss den nicht nur definieren, ausprobieren und in der produktiven Umgebung exakt vorschreiben.

Tatsächlich gibt es auch hier eine stattliche Anzahl von größeren Opensource - Projekten in denen für diverse Tabellen Index definiert wurden aber nicht verwendet werden.
Dann sind diese Index nicht nur nutzlos sondern hinderlich da sie bei jeder Änderung natürlich von Mysql mit gepflegt werden.
Diese Schlampereien findet man u.a. bei Typo3 und manchen Shopsystemen wie Prestashop  - einer der schlimmsten Systeme diesbezüglich ist leider Cmsmadesimple.

Die hier in den Images nicht sichtbaren Zeitvorteile einer optimierten Abfrage sind relevant.
Einer der größten Gefahren ist die Ansicht mancher der Entwickler das die absolut relativ kleinen Zeitspannen nicht relevant sind.
Die vergessen aber immer wieder das in der Praxis hunderte wenn nicht gar tausende Anwender seine Applikation bedienen.

Jeder Entwickler sollte mit einem Benchmarktest wie Siege an sein Produkt heran gehen und  es mal mit 100..200 Nutzern für eine Minute befeuern - dann werden enorme Leistungsunterschiede mehr als deutlich sichtbar.
Dann findet man alles wieder - vom kompletten Zusammenbruch bis hin zur radikalen Leistungssteigerung.

Noch ergänzend 2 Images aus der Cmsmadesimple Programmierung die zeigen was passiert wenn man Index erstellt aber die falschen vorgibt - das ist bei dem Produkt aber leider die Regel und nicht die Ausnahme dazu gehört auch das es regelmäßig überhaupt keine geeigneten Indexe gibt.
Genau das machen aber auch andere Produkte.

Abfrage falsch:

SELECT * 
FROM cms_content
FORCE INDEX ( cms_index_content_by_idhier ) 
WHERE content_id
IN ( 54, 51, 52 ) 
ORDER BY hierarchy

Abfrage richtig:
SELECT * 
FROM cms_content
FORCE INDEX ( cms_index_content_by_hierarchy ) 
WHERE content_id
IN ( 54, 51, 52 ) 
ORDER BY hierarchy

Während die falsche Abfrage ein Filesort ausführt ist es bei der richtigen lediglich ein einfaches where.