Index-Geheimnisse

Indizes und der richtige Umgang mit ihnen sind im Datenbankumfeld ein beliebtes Thema. Es stecken allerdings sehr viele interne Kenntnisse des DBMS hinter der optimalen Nutzung von Indizes. Wer sich bereits intensiv mit dem Einsatz von Indizes beschäftigt hat, dem möchte ich hier noch einige DB-Interna nennen, die bestimmt helfen werden, den Index-Einsatz noch weiter zu optimieren.

Es geht hier also um den optimalen Einsatz von BTREE-Indizes (auch als Key oder einfach als Index bezeichnet). UNIQUE-, PRIMARY-, FOREIGN- und FULLTEXT-Indizes gehorchen diesen Regeln teilweise auch, aber nicht unbedingt. Alle Regeln beziehen sich auf statische Abfrage-Optimierung. Wenn dynamische Statistiken vorliegen, verwendet MySQL diese, um den optimalen Abfrageweg zu bestimmen. Um die Statistiken berit zu stellen, sollte man die Tabellen regelmäßig mit ANANLYZE TABLE tabellenname analysieren.

Die wichtigste Regel zuerst: Es wird immer der Index für eine bestimmte Abfrage genutzt, dessen Spalten in der SELECT-Abfrage anteilsmäßig am meisten vorkommen.

SELECT spalte1 
FROM tabelle
WHERE name='meinname' AND num=123 AND abteilung=12
 
Index1 (name)
Index2 (num, abteilung, kostenstelle)

Hier wird Index1 genutzt, da alle Spalten dieses Indizes in der WHERE-Klausel vorkommen. Aus Index2 kommen nur 66% vor, deshalb wird er nicht genutzt.

Wenn die gleiche Anzahl von Spalten einer SQL-Abfrage von mehreren Indizes abgedeckt wird, wird der genutzt, der später erstellt wurde. Hier ist Vorsicht bei späteren Änderungen eines Indizes geboten, da die Abfragen dann eventuell plötzlich einen anderen Index verwenden, was ein schwer zu findendes Performance-Problem darstellen kann.
Abhilfe schafft hier entweder die Nutzung von FORCE_INDEX in der SQL-Abfrage oder das Duchnummerieren der Indizes, die nach Änderung eines Indizes alle in der „richtigen“ Reihenfolge neu erstellt werden.

Wird auf mehrere Spalten eines Indizes mit dem Gleicheitsoperator zugegriffen (=), so hat dies Vorrang vor anderen Operatoren wie LIKE, BETWEEN, < oder >. Beispiel:

SELECT spalte
FROM tabelle
WHERE name LIKE 'Mai%' AND num=123 AND abteilung=1 AND kategorie='Arbeiter' AND klasse='A1'
 
Index1 (kategorie, klasse, name)
Index2 (num, abteilung)

In diesem Beispiel wird Index2 verwendet, obwohl von Index1 auf alle 3 Spalten und von Index2 nur auf zwei Spalten zugegriffen wird.
Allgemein gilt aber, dass stets der Index ausgewählt wird, der eine höhere Anzahl an abgedeckten Spalten hat.

Wenn in einer WHERE-Klausel auf eine Spalte zugegriffen wird, die in einem Index als erste Spalte definiert ist, wird dieser Index verwendet.

SELECT spalte
FROM tabelle
WHERE name='name'
 
Index1 (name, klasse, kaegorie)
Index2 (klasse, name, kategorie)

Es wird Index1 genutzt, da darin name an erster Stelle steht. Deshalb sollte die Query auch so geschrieben werden, dass die erste WHERE-Bedingung diejenige ist, die am wenigsten aufwendig zu berechnen ist und am wenigsten Datensätze zurückliefert.
Diese Aussage gilt allerdings nicht, wenn über eine Funktion gearbeitet wird:

SELECT spalte
FROM tabelle
WHERE LTRIM(name)='name'

Hierbei wird kein Index verwendet.

Als letzten Tipp soll die Vorgehensweise des Query Optimizers noch für Joins beschrieben werden. MySQL wird bei Joins immer eine Reihenfolge festlegen müssen, in der die Tabellen miteinander verknüpft werden. Ziel ist es, zuerst möglichst wenig Datensäze zu ermitteln und diese dann mit den anderen Tabellen zu verknüpfen. Die Tabelle, aus der die ersten Datensätze geholt werden, bezeichnet man als „treibende Tabelle“. Die Vorgehensweise habe ich bereits im Beitrag ‚Vom Kleinen aufs Große schließen‘ behandelt. Damit das DBMS feststellen kann, welche Tabelle „treibende Kraft“ in der Abfrage ist, werden einige Regeln beachtet:

  • Genutzte Unique- oder Primärschlüssel werden zur Folge haben, dass die zugehörige Tabelle als treibende Kraft angesehen wird
  • wird auf alle Spalten eines Indizes mit dem Gleichheitsoperator zugegriffen (=), so wird dieser einem Index vorgezogen, dessen Spalten nicht alle abgedeckt werden (siehe oben) – die zugrundeliegende Tabelle wird dann als treibende Tabelle gewählt
  • es wird diejenige Tabelle bevorzugt, die einen höheren Anteil an Spalten in einem Index hat als eine Tabelle mit einem geringen Anteil an genutzten, indizierten Spalten
  • Wenn in der WHERE-Klausel auf einen Merhspalten-Index zugegriffen werden kann, wird die dahinter liegende Tabelle als treibende Tabelle verwendet, statt diejenige hinter Ein-Spalten-Indizes.
  • Wenn zwei Tabellen die gleiche Anzahl indizierter Spalten aufweisen, wird diejenige Tabelle als treibende Tabelle gewählt, die in der FROM-Klausel zuletzt genannt wird. In der folgenden Anweisung ist t2 die treibende Tabelle
    SELECT * 
    FROM t1, t2
    WHERE t1.ID=t2.t1_ID AND t2.name='Name' AND t1.abteilung=12

Man sollte grundsätzlich immer darauf achten, dass aus der zuletzt genannten Tabelle in der FROM-Klausel die wenigsten Datensätze selektiert werden.

Ich hoffe es ist mir gelungen, die Arbeitsweise des Optimizers näher zu bringen. Viel Spaß beim Überarbeiten ihrer Indizes, falls es denn nötig sein sollte ;-).

Jan hat 152 Beiträge geschrieben

4 Kommentare zu “Index-Geheimnisse

  1. GhostGambler sagt:

    Es gibt übrigens auch das nette Kommando EXPLAIN – bevor man sich hier die ganzen Absätze auswendig lernt, die eventuell doch in einigen Fällen nicht zutreffen (was nicht unwahrscheinlich ist – der Optimizer hat häufiger mal Macken), sollte man sich lieber angewöhnen seine Queries durch EXPLAIN laufen zu lassen.
    Bei simplen Queries allá „WHERE id=XYZ“ ist klar was zutun ist, aber nicht gerade selten werden bei JOINs „falsche“ Indizes genutzt, oder es fehlen schlichtweg welche, man hat irgendwas anderes übersehen, etc. pp.

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>