InnoDB Buffer Pools: Warum Deine Indexgröße wichtig ist

In der Entwicklung MySQL/InnoDB-gestützter Projekte ist es allzu leicht dem „Index für alles und jeden!“-Mantra zu verfallen, speziell wenn der Kunde drängt und mehr Geschwindigkeit verlangt. Jedoch führt dies nur selten zum Ziel und in hartnäckigeren fällen so einer deutlichen Verschlechterung des Problems. Nun sind ein paar Anfragen schneller, aber alle anderen langsamer. Woran liegt es? Was ist passiert? Und was macht meine MySQL da eigentlich? Wie geht es besser?

Um dieser  Frage auf den Grund zu gehen, müssen zunächst einige elementare Fragen beantworten.

1. Wie verwaltet InnoDB seine Daten?
2. Wie funktionieren InnoDB Indizes?
3. Was ist bei Indizes zu beachten?

InnoDB Buffer Pools und Pages

InnoDB benutzt für sämtliche Dateisystemzugriffe seine Buffer Pools und Pages. In einer richtig konfigurieren InnoDB umgeht sie vollständig den Dateisystem- und Betriebssysstemcache(Pagecache) und verwaltet eigenständig Caches, Speicherseiten und Schreibzugriffe.

Dazu verwendet InnoDB einen Speicher-Pool konfigurierbarer Größe (Buffer Pool) welcher in Speicherseiten fester Größe unterteilt ist. Für alle MySQL-Versionen bis v5.5 ist eine Speicherseite immer 16KB groß, ab MySQLv5.6 ist sie konfigurierbar.

Wenn eine Leseanfrage die InnoDB erreicht, reicht sie diese weiter an den Buffer Pool. Dieser prüft, ob im Pool schon eine Speicherseite mit der entsprechenden Position in der Datenbankdatei vorhanden ist. Wenn ja gibt es die Seite zurück. Wenn nicht, liest er die entsprechende Speicherseite von der Festplatte und legt diese im Buffer Pool ab. Dabei wird, falls nötig, eine ältere Speicherseite aus dem Buffer Pool verdrängt.

Wie leicht zu erkennen ist, hat diese Art des Datenzugriffs von InnoDB den entscheidenden Vorteil, dass wahlweise nur die benötigten Ausschnitte aus größeren Tabellenreihen gelesen werden müssen, da immer nur jeweils die benötigten Speicherseiten geholt werden.

Ebenfalls offensichtlich ist, dass die Größe des Buffer Pools direkt mit der Geschwindigkeit der Datenbank korreliert. Da InnoDB den Buffer Pool auch für Indizes benutzt gilt: Je größer der Pool, desto mehr Daten im RAM, desto weniger Disk-I/O, desto schneller meine Zugriffe.

InnoDB und Indizes

InnoDb verwendet als Indexstruktur einen B+Baum, welcher zusammen mit den Tabellendaten in der gleichen Datei gespeichert wird.

Der Baum besitzt einen Root-Knoten an einer fest definierten Position innerhalb der Datei. InooDB trennt die Speicherbereiche der inneren Baumknoten und der Blätter, versucht jedoch die beiden Bereiche möglichst sequentiell zu halten. Dies hat das Ziel den Baum schnell balancieren zu können und trotzdem örtliche Lokalität der Daten zu wahren. Die Blattknoten werden ebenfalls in einem separaten, möglichst sequenziellen Speicherbereich gehalten, um optimale Table-Scan Geschwindigkeit zu gewährleisten. Dies ist insbesondere wichtig, da Datenbankreihen, welche kleiner als die halbe Speicherseitengröße sind, direkt in den Blättern des Primärindex abgelegt und nicht separat gespeichert werden.

In diesem Sinne ist jeder Zugriff in InnoDB ein Zugriff mit Index, da der Primärindex benutzt wird, um die einzelnen Tabellenreihen in den Datenbankdateien zu finden. Ist kein Primärindex definiert, so wird implizit ein versteckter Primärindex angelegt.

Die Beschaffenheit des B+Baumes ist dabei stark abhängig von der Indexgröße!

Um einen B+Baum aufzubauen, ist es nötig in jedem inneren Knoten eine Grenze anzugeben, um zu definieren, für welche Teile der entsprechende Unterbaum zuständig ist. Da die Grenze sich auf den Schlüssel bezieht ist klar, dass jeder Verweis eines Knotens auf Unterknoten eine Instanz des Primärschlüsselattributs erfordert. Daraus folgt, dass die Größe jedes Baumknotens direkt von der Größe des Schlüsselattributs abhängt, wenn nicht der Indexpräfix absichtlich verkleinert wurde.

In InnoDB muss jeder innere Knoten des Indexbaumes in weniger als eine halbe Speicherseite passen, damit es mindestens 2 Keys pro Knoten gibt. Das hat zur Folge, dass bei größeren Schlüsseln weniger Grenzangaben pro Knoten möglich sind, was die Tiefe des Baumes und damit die Anzahl nötiger Indirektionen bei Indexabfragen erhöht.

Nochmal in Kurz:

• InnoDB Indizes werden, wie alles Andere auch, seitenweise geladen
• Je größer der Schlüssel desto tiefer der Indexbaum
• Je tiefer der Baum desto mehr Indirektion
• Je mehr Indirektion, desto mehr Speicherseiten aus dem Pool, desto mehr Disk-I/O, desto weniger Speicherseiten für die eigentlichen Daten

Index Stolpersteine

Nachdem jetzt klargestellt ist, wie InnoDB seinen Speicher verwaltet und Indizes funktionieren, kommen wir nun zum Performancerelevanten Teil.

Es ist oft verlockend, als erste Reaktion bei Performanceproblemen auf jedes Feld einen Index zu legen. Ein kleines Fallbeispiel:

Wir haben die folgende Tabelle:

CREATE TABLE demo (user-id VARCHAR(255), name VARCHAR(255), PRIMARY KEY (user-id), KEY names (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Was wird hier passieren wenn Reihen eingefügt werden?

Bei einem Insert wird der names und der Primary Index-Baum erweitert (wer hätte das erwartet?). Nicht erwartet war vielleicht, dass der Schlüssel der beiden Felder jeweils 255 UTF-8 Zeichen und damit 765Byte lang ist. Das bedeutet, dass pro Baumknoten im Index nur 21 Kindknoten möglich sind. Fügen wir nun beispielsweise 1000000 Zeilen ein, sind allein unsere Reihen (765byte * 2) * 1000000 = ~1,4GB groß. Der Baum wird damit log(1000000;21) = ~5 Ebenen tief. Dazu kommt noch pro innerem Knoten beider Bäume jeweils ~765byte. Daraus folgt:

Größe der innere Knoten: 2 * (sum(1000000/(21^x); 1; 4) * 765byte = ~72MB

So weit so unspektakulär.

Die unerwartete Wendung kommt nun, wenn man sich erinnert, dass bei InnoDB jeder Zeilenzugriff über den Primärindex läuft. Das impliziert dass die Blattknoten des names Index den passenden Primärschlüsseleintrag enthalten! Dieser wird benutzt um mit Hilfe des Primärindex die indizierte Zeile zu finden. Das bedeutet, dass wir in unserem names Index sämtliche Primärschlüssel duplizieren!

Daraus folgt für die Größe der Blattknoten des names-Index: 1000000 * 765byte = ~765MB!

Das heißt, unser zusätzlicher Index benötigt 50% des Speicherplatzes des Kompletten Tabelle! Bei großen Tabellen führt dieser Umstand schnell dazu, dass Indexzugriffe die eigentlichen Tabellendaten aus dem Speicher verdrängen und damit den Nutzen des Index negieren. Das Problem wird natürlich bei weiteren Indizes noch verstärkt, wenn die Indexgröße die Größe der Nutzdaten übersteigt.

Außerdem erzeugt jeder Fremdschlüssel einen impliziten Index und dupliziert damit erneut alle Primärschlüsselfelder!

Fazit:

IMMER einen möglichst kleinen Primärschlüsse wählen! Jeder in InnoDB definierte Fremdschlüssel dupliziert die komplette referenzierte Primärschlüsselspalte. Wenn diese Spalte groß ist, führt das schnell zu Speicherverdrängung und langsamen Queries trotz Index. Im besten Fall ignoriert InnoDB die großen Indizes und sie verbrauchen nur Platz und verlangsamen Schreibvorgänge. Im schlimmsten Fall verdrängt eine Indexnutzung die nötigen Nutzdaten aus dem Speicher.