MySQL Indizes und Performance

Ein Index, der in keinem Query verwendet wird, der bremst. Nicht bei der 
Abfrage, dafür aber aufgrund erhöhten Aufwands bei Insert/Update/Delete 
Operationen.

Ob ein Index verwendet wird, also Nutzen bringt, hängt vom Query ab. 
Database Engines optimieren die Arbeitsweise von Queries anhand diverser 
Parameter, wie beispielsweise der Anzahl Rows in den angesprochenen 
Tabellen. Databanksysteme bieten oft Werkzeuge, mit der die tatsächlich 
umgesetzte Arbeitsweise beschrieben wird. Man sollte berücksichtigen, 
dass eine Test-Tabelle mit 200 Rows nicht unbedingt zum gleichen 
Ergebnis führt wie die Produktionsversion mit 100 Mio (1).

Der tiefere Sinn von Foreign Keys liegt in der Wahrung der Integrität 
der Datenbank. Damit lässt sich ggf. sicherstellen, dass man eine Row 
nicht löschen kann, so lange noch darauf verwiesen wird. Andernfalls hat 
man irgendwann IDs, die ins Nirvana verweisen. (2)

(1) Standardfehler von kleinen Entwicklungsbüros. Anwender klagt "zu 
langsam", Entwickler sagt, "zu wenig CPU, zu wenig Speicher, zu langsame 
IO". Admin guckt nach und sagt, "da fehlen bloss ein paar Indizes".

(2) Selbstgelernte Entwickler ohne spezifische Einsicht in relationale 
Datenbanken kommen oft ohne sie aus. Kann sich irgendwann rächen.

>Was ist nun aber mit den zahlreichen foreign keys, die in vielen
>Tabellen vorhanden sind? Jeder Termin ist z.B. einem Kursleiter und
>einem Ort zugeordnet, die in der Suche dann unter anderem als
>Filter-Kriterium verwendet werden. Setzt man auf kursleiter_id und

Normalerweise ja. Aber wenn die Query noch andere Filterkriterien diese 
Tabelle nutzt, die bereits selbst sehr selektiv sind, dann bringen 
weitere selektive Indices nicht mehr viel.
Wie andere schon sagten - muß man in Zusammenhang mit der DDL der 
Tabellen und der Abfrage sehen.

>Was ist nun aber mit den zahlreichen foreign keys, die in vielen
>Tabellen vorhanden sind? Jeder Termin ist z.B. einem Kursleiter und
>einem Ort zugeordnet, die in der Suche dann unter anderem als
>Filter-Kriterium verwendet werden. Setzt man auf kursleiter_id und

Normalerweise ja. Aber wenn die Query noch andere Filterkriterien diese 
Tabelle nutzt, die bereits selbst sehr selektiv sind, dann bringen 
weitere selektive Indices nicht mehr viel.
Wie andere schon sagten - muß man in Zusammenhang mit der DDL der 
Tabellen und der Abfrage und der Datencharakteristik sehen.

Ron schrieb:
> ich habe eine InnoDB-Datenbank, in der ich per Abfrage diverse Tabellen
> joine und per Datumsvergleich nach freien Terminen suche. Da ich nach
> freien Zeitfenstern in festgelegten Intervallen suche, nutze ich eine
> Hilfstabelle mit allen Intervallen pro Tag (00:00:00, 00:05:00, ...,
> 23:55:00) und suche dann für einen gewissen Zeitraum für jeden Tag die
> entsprechenden Zeitfenster.
> Die Such-Performance ist nicht überragend, daher habe ich versucht diese
> durch das Anlegen von Indizes zu verbessern. Die Suche ist dadurch auch
> schon signifikant schneller geworden, aber ich frage mich, in welchem
> Maße Indizes normalerweise verwendet werden?

Das solltest Du nicht Dich fragen, sondern Deine Datenbank. ;-)

Nein, im Ernst: für solche Fragen bieten die meisten Datenbanken ein 
Werkzeug, um den Ausführungsplan auszugeben, den der Query Optimizer 
erzeugt -- und da steht dann drin, welche Indizes von der Abfrage 
verwendet werden. Dieses Werkzeug heißt häufig "EXPLAIN" und wird dem zu 
analysierenden Query einfach vorangestellt, statt "SELECT foo, bar FROM 
baz" führst Du dann also "EXPLAIN SELECT foo, bar FROM baz" aus, etwa im 
Kommandozeilenclient, und bekommst dann den Ausführungsplan angezeigt.

Indizes in Datenbanken sind ein sehr umfangreiches Thema. Wie 
verschiedene Vorredner schon sehr richtig angemerkt haben, kosten 
Indizes Performance bei INSERTs und UPDATEs auf die betreffenden Felder 
und ganz allgemein bei DELETEs, weil die geänderten Daten dann natürlich 
nicht nur in der Tabelle sondern eben auch in den Indizes nachgepflegt 
werden müssen. Dafür können sie die Performance von SELECTs allerdings 
wesentlich erhöhen -- wenn sie benutzt werden, versteht sich.

Ob sie benutzt werden, läßt sich nicht einmal anhand der DDL-Schemata 
der Datenbank vorhersagen. Von meinen (zum Teil mehrere hundert GB 
großen) PostgreSQL-Datenbanken kann ich sagen, daß Indizes für kleine 
Tabellen häufig gar nicht benutzt werden, weil ein sequentieller Scan 
der Tabelle(n) dann trotz allem schneller sein kann -- das hängt aber 
von der Art des Index (B-Tree, LSM-Tree, ...) und vielen anderen 
Faktoren ab. Auch sonst können Datenbanken sehr zickig bei der Frage 
sein, welcher Query welchen Index nutzt -- oft kann da sogar die 
Reihenfolge der Felder in Index und Abfrage eine Rolle spielen.

Ein weiterer Punkt ist die Frage, ob die Indizes in den Arbeitsspeicher 
passen oder auf die Festplatte ausgelagert werden müssen. Eine Datenbank 
wie PostgreSQL führt dazu Statistiken, welche Indizes wie oft abgefragt 
worden sind, hält die am häufigsten genutzten im RAM und lagert seltener 
genutzte auf die Festplatte aus. Das macht die Sache dann noch erheblich 
komplizierter, denn solche Indizes können schneller sein als 
sequentielle Tabellenscans, müssen sie aber nicht.

Die beschriebene Komplexität macht es schwierig bis unmöglich, allgemein 
gültige Ratschläge zu geben. Aber ein paar Tipps habe ich dennoch.

1. EXPLAIN ist Dein Freund -- und zwar nicht nur nach dem Anlegen der DB 
mit ein paar kleinen Testdaten, sondern unter produktionsnahen 
Bedingungen mit einer produktiven oder zumindest produktionsähnlichen 
Workload. Solche Workloads lassen sich etwa mit sogenannten Faker-Libs 
erzeugen, die für verschiedene Programmiersprachen verfügbar sind. Auch 
im Betrieb ist es sinnvoll, immer mal wieder mit EXPLAIN zu prüfen, ob 
die Indizes noch zum Datenbankschema, zur Workload und den verwendeten 
Queries passen.

2. Wenn INSERT-, UPDATE- und / oder DELETE-Performance wichtiger sind 
als die SELECT-Performance, oder wenn Indizes nicht verwendet werden, 
sind Indizes kontraproduktiv. Bei Bulk-Loads -- wenn Du also viele Daten 
in die Datenbank pumpen willst, etwa bei einem Initial Load -- ist es 
meistens sinnvoll, die Indizes zu deaktivieren oder sogar zu löschen, 
und sie erst nach der Befüllung der DB wieder einzuschalten bzw. zu 
erzeugen.

3. Datenbanken wie PostgreSQL halten Statistiken für die Nutzung und die 
Hitrates ihrer Indizes vor. Damit lassen sich ungenutzte und 
unproduktive Indizes recht gut herausfinden; diese Statistiken fließen 
übrigens auch in die Planung des Query Optimizer mit ein.

4. Kluge Datenmodelle und Queries nutzen oft genausoviel und manchmal 
sogar mehr als das Anlegen von Indizes. Dabei darf man in begründeten 
Ausnahmen sogar schonmal von der Normalform abweichen, aber: das müssen 
Ausnahmen bleiben, und sie müssen begründet sein.

5. Datenbanken sind ziemlich empfindlich, was ihre Konfiguration angeht, 
und werden häufig sehr konservativ konfiguriert ausgeliefert. Meistens 
ist es, flankierend zum Anlegen von Indizes, sehr sinnvoll, die 
Konfiguration der Datenbank zu tunen und ihr etwa mehr Speicher 
zuzuteilen. Hier spielen auch die Anzahl der DB-Threads oder -Prozesse, 
das Festspeichermedium und weitere Faktoren eine Rolle. Das ist aber 
wieder ein sehr komplexes Thema für sich, auf das ich hier nicht 
eingehen möchte. Nur soviel dazu: häufig lohnt es sich, den Festspeicher 
der Datenbank auf ein schnelles Medium wie eine SATA-SSD oder sogar eine 
PCIe-SSD (NVMe) zu verlegen, die wesentlich höhere Datendurchsätze und 
erheblich kleinere Zugriffszeiten insbesondere bei Random-Seeks 
vorweisen können.

6. Viele Datenbanken bieten die Möglichkeit, Tabellen zu partitionieren. 
Dadurch lassen sich eine Tabelle und deren Indizes auf mehrere kleinere 
Tabellen und Indizes verteilen, was sich oft besonders bei zeitbasierten 
Daten wie etwa Log- oder Transaktionsdaten anbietet, wenn auf die alten 
Tabellen nicht mehr oder nurmehr selten zugegriffen werden muß. Dann 
kann die Datenbank die Indizes für die alten Tabellen auf die Platte 
schreiben (oder der DBA sie löschen), während die Indizes (und ggf. 
Tabellen) der aktuellen, oft genutzten Tabellen im schnellen 
Arbeitsspeicher liegen.

7. Ansonsten gelten die beiden Merksätze, die bei 
Performanceoptimierungen immer gelten: a) Measure, don't guess sowie b) 
Premature Optimization is the root of all evil. Will sagen: 
Performanceoptimierungen beruhen nicht auf Vermutungen, sondern auf 
Messungen, und Standardrezepte funktionieren nicht. Die einzig richtige 
Vorgehensweise ist also: Datenbank anlegen, mit einer produktionsnahen 
Workload befüllen, und diese mit den Queries testen die in der 
Produktion genutzt werden -- und zwar nicht nur mit den Queries selbst, 
sondern auch die Anzahlen der jeweiligen Queries sollten in etwa jenen 
der Produktion entsprechen. (Optimierungen von Queries, die selten 
ausgeführt werden, haben naturgemäß weniger Einfluß auf das Ergebnis als 
Optimierungen von Queries, die sehr oft ausgeführt werden.)

Nachdem mit dieser Vorgehensweise eine Basislinie als Referenz 
festgelegt wurde, geht erst die eigentliche Optimierung los: immer nur 
einen einzigen Parameter verändern, dann testen. Wenn der Parameter 
keine Verbesserung gebracht hat, diesen wieder zurücksetzen, ansonsten 
beibehalten. Und dann wieder von vorne: einen einzigen Parameter 
verändern, dann testen, und so weiter. Das ist eine ziemlich aufwändige 
Angelegenheit, die dadurch nicht einfacher wird, daß verschiedene 
Parameter sich gegenseitig beeinflussen. Mit der Zeit bekommt man ein 
gewisses Gefühl für die Abhängigkeiten und Einflüsse, aber darauf sollte 
man sich nie verlassen: schon mit der neuen Version der Datenbank kann 
vieles wieder ganz anders aussehen.

Darum zuletzt noch ein Rat: treib's nicht zum Exzess. Leg' Dir ein SLA 
zu, also ein Service Level Agreement zur Not mit Dir selbst, in dem Du 
dann (realistisch!) beschreibst, welche Ziele zu erreichen willst. 
Konzentrier Deine Arbeit darauf, diese Ziele zu erreichen, vor allem bei 
besonders wichtigen, besonders lange laufenden sowie besonders häufig 
ausgeführten Queries. Für PostgreSQL gibt es einen Loganalyzer namens 
pgfouine, der ausgibt, welche Queries wie häufig ausgeführt worden, und 
wie lange diese gelaufen sind -- vielleicht gibt es so etwas auch für 
MySQL. Wie dem auch sei: es hat keinen Zweck, eine "optimale" 
Performance zu erreichen, weil man dazu am Ende jedes Optimierungslaufes 
wieder von vorne beginnen müßte und dann, wie gesagt, mit der nächsten 
Major- oder Minor-Version der DB wieder alles anders aussehen kann.

In diesem Sinne: viel Spaß und Erfolg bei Deinem Vorhaben!