Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / index-geheimnisse.php

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:

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 ;-).