Sinn und Zweck mehrspaltiger Indizes in MySQL

Wann immer wir neue Tabellen in einer MySQL Datenbank anlegen, stellt sich die Frage, welche Spalten indiziert werden müssen. Für gewöhnlich genügt ein einspaltiger Index. In manchen Fällen kann aber mit einem mehrspaltigen Index die Performance verbessert werden. Welche Vor- und Nachteile solche Indizes mit sich bringen, werde ich euch hier erläutern.

Grundwissen über den Index in MySQL

Bevor wir uns auf den mehrspaltigen Index stürzen, müssen wir uns noch klar werden wie ein Index (egal über wie viele Spalten) überhaupt eingesetzt wird.

Wichtig: MySQL verwendet bei einem Query immer nur einen Index.

Betrachten wir die Tabelle

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(7) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`col1` varchar(7) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `idx_col1` (`col1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mit diesem Query

SELECT * FROM test WHERE id > 1 AND col1 = "foo"

1	SELECT * FROM test WHERE id > 1 AND col1 = "foo"

dann zeigt uns ein EXPLAIN

possible_keys	key	key_len
PRIMARY,idx_col1	idx_col1	9

Wie erwartet, sind grundsätzlich beide Indizes für die Suche nützlich, aber verwendet wird am Ende nur einer. MySQL versucht hier (anhand statistischer Daten) zu erahnen welcher das bessere Ergebnis liefert.

Unter bestimmten Voraussetzungen führt MySQL einen Index-Merge durch. Es werden also zunächst zwei mögliche Indizes kombiniert und die Kombination dann für die Suche verwendet. Das kostet natürlich Zeit. MySQL investiert diese Zeit nur, wenn die Suche anschließend vermutlich (= statistisch gesehen) diese Zeit wieder einspart.

Speicherung und Caching des Index

Natürlich gibt es eine Datei mit dem Inhalt des Index, damit das System einen Neustart übersteht, aber MySQL hält die gesamten Informationen ebenfalls im RAM. Bereits dadurch entsteht ein Geschwindigkeitsgewinn, den wir uns auch zu Nutze machen können.

Wann hilft mir ein mehrspaltiger Index?

Ein mehrspaltiger Index kann in zwei Szenarien die Performance verbessern:

Wir selektieren nur wenige Felder
Wir suchen sehr oft mit den gleichen Kriterien

Stellen wir uns folgende Tabelle vor:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(63) NOT NULL,
  `status` varchar(15) NOT NULL,
  `newsletter` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(63) NOT NULL,

`status` varchar(15) NOT NULL,

`newsletter` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sehr spezifische SELECT-Anfragen

Nehmen wir an wir wollen aus obiger Tabelle wissen, ob unsere aktiven Nutzer einen Newsletter bestellt haben. Wir benötigen also die Felder id und newsletter.

SELECT id,newsletter FROM users WHERE status = 'active';

1	SELECT id,newsletter FROM users WHERE status = 'active';

Mit einspaltigem Index

Natürlich legen wir in diesem Fall einen Index für die Spalte status an. Andernfalls würde diese Anfrage zum Desaster.

ALTER TABLE users ADD INDEX idx_status (status);

1	ALTER TABLE users ADD INDEX idx_status (status);

Das EXPLAIN für unsere Abfrage sieht dann so aus:

possible_keys	key	Extra
idx_status	idx_status	Using where

Wie erwartet, wird der angelegte Index verwendet. Danach werden dann – wie üblich – die passenden Datensätze von der Festplatte geladen und zurück geliefert.

Mit mehrspaltigem Index

Dieses Verhalten können wir verbessern, indem wir die gewünschte Spalte newsletter in den verwendeten Index mit aufnehmen.

ALTER TABLE users ADD INDEX idx_sn (status,newsletter);

1	ALTER TABLE users ADD INDEX idx_sn (status,newsletter);

Jetzt sagt uns EXPLAIN:

possible_keys	key	Extra
idx_status,idx_sn	idx_sn	Using where; Using index

Der erste Index ist an dieser Stelle natürlich überflüssig und kann entfernt werden.

Wichtig ist hier das „Using index“. Damit signalisiert uns die Datenbank, dass die Daten der Rückgabe aus dem Index erzeugt werden. Es ist also nicht notwendig die tatsächlichen Daten von der Festplatte zu laden.

Wer genau hingeschaut hat, wird sich jetzt fragen, woher die Daten für die id herkommen, obwohl sie doch augenscheinlich nicht im verwendeten Index enthalten sind. Hierzu muss man wissen, dass InnoDB die id in jedem Index automatisch mit nimmt.

Achtung: Damit MySQL die Antwortdaten aus dem Index bezieht, muss dieser Index bereits für die Suche (WHERE-Bedingung) verwendet worden sein. Auch hier gilt wieder die Regel: Nur ein Index pro Anfrage.

Häufig auftretende Kombinationen von WHERE-Bedingungen

Offensichtlicher als der vorherige Punkt ist die Verwendung eines mehrspaltigen Index, wenn mehrere WHERE-Bedingungen (ausschließlich AND) auftreten.

SELECT * FROM users WHERE status = 'active' AND newsletter = 1;

1	SELECT * FROM users WHERE status = 'active' AND newsletter = 1;

Hier erhöht ebenfalls der oben angelegte Index idx_sn die Performance, da MySQL jetzt direkt alle relevanten Treffer bestimmen kann.

Hätten wir nur den ersten Index (idx_status), würden zunächst alle Datensätze mit dem richtigen status geladen und anschließend auf die Übereinstimmung mit der anderen Bedingungen geprüft. Die Ergebnismenge entsteht also durch Entfernen von „fälschlich“ geladenen Datensätzen.

Für welche WHERE-Bedingungen kann der mehrspaltige Index verwendet werden?

MySQL kann jeden Index nur von „vorne“ nach „hinten“ einsetzen. Heißt, nur wenn die 1. Spalte des Index in der WHERE-Bedingung enthalten ist, kommt er in Frage. Ist lediglich die 2. Spalte enthalten, kann er nicht verwendet werden.

So hilft uns oben definierter idx_sn nicht bei folgendem Query, obwohl newsletter ja in ihm enthalten wäre.

SELECT * FROM users WHERE newsletter = 1;

1	SELECT * FROM users WHERE newsletter = 1;

EXPLAIN:

type	possible_keys	key	Extra
ALL			Using where

In diesem Fall müssten wir für newsletter zusätzlich einen eigenen Index anlegen.

Für die Spalte status hingegen benötigen wir keinen eigenständigen Index. Nachfolgende Spalten im Index dürfen weggelassen werden.

Entgegen hin und wieder anzutreffender Meinung, spielt es aber keine Rolle in welcher Reihenfolge wir unsere WHERE-Bedingung hinschreiben. MySQL ist da schon schlau genug.

Fazit

Ihr solltet immer daran denken, dass jeder Index Speicherplatz (RAM) benötigt und bei jedem Update neu geschrieben werden muss. Ihr solltet also niemals einen Index „auf Verdacht“ anlegen, weil er vielleicht mal gebraucht werden könnte.

Jede zusätzliche Spalte im Index, benötigt natürlich ebenfalls mehr Speicherplatz und sollte daher schon deutlich die Performance einer Anfrage verbessern, bevor ihr bereit seid den Speicher dafür zu opfern.

In vielen Fällen schränkt bereits die erste Spalte die Ergebnismenge soweit ein, dass weitere Spalten kaum noch einen Gewinn bringen.

Als sehr sinnvolle Anwendung eines mehrspaltigen Index sehe ich den Einsatz als Primärschlüssel. Wird ein Index durch hinzufügen einer weiteren Spalte eindeutig, ist das eine sinnvolle Investition.