Mysql PHP Index-Optimierung: Lange Index-Werte kürzen

Mit einem optimalen Index steht und fällt die Performance einer Datenbank. So ist ein optimales Datenbankdesign eine Kunst, die viel Erfahrung und Wissen verlangt. Und oft scheitert der Datenbankadministrator an den Anforderungen der Benutzer, die scheinbar unmögliches oder zumindest suboptimales von der Datenbank verlangen.

Ein Beispiel ist zum Beispiel das Speichern von URLs und die optimierte Abfrage darauf. Im ersten Schritt dürfte der Datenbankdesigner eine URL-Spalte mit varchar(100) definieren. Im Zeitalter von dynamischen Webseiten stößt man sehr schnell auf längere URLs, so dass die Beschränkung von 100 Zeichen sehr schnell an Grenzen stößt. Aber auch die beliebte Größe von 255 Zeichen wird schnell zu klein. Gemäß Microsoft kann der Internet Explorer URL mit bis zu 2.083 Zeichen verarbeiten. Also eine URL als Text anlegen? Sicher keine schlechte Idee, wenn die URL in der Datenbank nur zur Speicherung dient. Ist sie aber Teil einer Abfrage, dann muss ein Index über die URL gelegt werden.

SELECT irgendwas FROM meine_tabelle WHERE url=’http://sirmark.de’

Welche Probleme sind zu erwarten?
Wenn die Spalte URL plötzlich in ein varchar(500) geändert wird, explodiert die Größe des Index für diese Spalte förmlich. Neben gestiegenem Plattenplatz sinkt die Performance spürbar. Je nach verwendeter Storage-Engine überschreitet das feld bzw. Feldkombinationen plötzlich die zulässige Größe für den Index.

Möglichkeiten zur Optimierung des SQL-Index
Naheliegend ist natürlich, das Feld zum Speichern zu verkürzen. So könnte beispielsweise bei einer URL das „http://“ prinzipiell weggelassen werden, was uns pro Datensatz sieben Zeichen spart. Wenn im Beispielsfall alle URLs die gleiche Domain haben, kann diese weggelassen werden. Sind es nur eine „Handvoll“ Domains, könnten diese per Normalisierung ausgelagert werden. Doch sind es willkürliche URLs, kommen wir mit dieser Methode nicht sehr weit.

Speicherung per Hash: MD5 in PHP
Also stellt sich die Frage, ob wir überhaupt die URL speichern müssen bzw. diese zurückrechnen müssen. Wenn wir zum Beispiel Werte zu einer URL speichern und die eigentliche URL nur als Zuweisung vorhalten müssen, benötigen wir die genaue URL nicht. Nehmen wir einmal an, wir schreiben einen Cache. Wir möchten in einer Anwendung bestimmte Zugriffe nicht immer wieder erneut ausführen, sondern halten das Abfrageergebnis eine Weile in einer Datenbank vor (Cache). Die URL dient als Zuweisung, der eigentliche Content, also das Abfrageergebnis, können wir in einem Textfeld speichern. Da über die URL (Parameter) abgefragt wird, muss dieses Feld mit einem Index versehen werden.
In einem solchen Fall können wir uns damit begnügen, lediglich einen Hash der URL zu speichern. Zum Vergleich bei einer Datenbankabfrage genügt uns dies.

Das folgende Beispiel basiert darauf, dass wir eine Anwendung und den Cache in PHP schreiben. Gehen wir davon aus, dass wir eine Datenbank mit folgendem Aufbau haben

ALTER TABLE `local`.`meine_tabelle` CHANGE COLUMN `url` `url_hash` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
ADD COLUMN `content` TEXT NOT NULL AFTER `url_hash`;

In dieser Tabelle speichern wir die URL nicht im Klartext, sondern als sogenannter MD5-Hash. Wir speichern also einen Fingerabdruck.
Der MD5-Hash hat den Vorteil, dass beliebig lange Zeichenketten auf eine maximale Zeichenkettenlänge von 32 Zeichen eingeschrumpft werden. Wir benötigen also nur ein varchar(32).
Zum Speichern berechnen wir aus der URL in PHP mittels md5($url) den Hash. Bei der Abfrage mittels SQL-SELECT müssen wir ebenfalls aus der Abfrage-URL den Hash ermitteln und über diesen abfragen:

SELECT content FROM meine_tabelle WHERE url_hash=’“.md5($url).“’

Die Funktion MD5 ist ebenfalls in MySQL implementiert, so dass Sie auch wie folgt direct in SQL abfragen können:

SELECT MD5(‚http://sirmark.de‘) as MD5

Diese Methode ist ein erster Ansatz zur Datenbankoptimierung, hat aber noch folgende Nachteile:

  1. Alle Zeichenketten werden 32-Zeichen lang. Also auch kürzere wie beispielsweise http://www.google.de
  2. Der Hash ist NICHT eindeutig! Die Umrechnung einer Zeichenkette ist zwar immer gleich, doch es ist nicht sicher, dass zwei völlig unterschiedliche Zeichenketten den gleichen Hash ergeben. Die Wahrscheinlichkeit ist je nach Anwendung gering, aber es muss damit gerechnet werden.

Der Nachteil, dass der Hash nicht zurückgerechnet gerechnet werden kann und die nicht gegebene Eindeutigkeit könnte mit einem weiteren Feld, in diesem Fall die URL im Klartext, in der Datenbank begegnet werden. Verrückt? Nicht ganz. Wir erhöhen zwar mit dieser Methode den gesamten Speicherplatzverbrauch, da wir neben der URL im Klartext ja auch noch einen 32-Zeichen langen Hash speichern, aber der Index ist in jedem Fall deutlich kleiner, als wenn dieser über die komplette URL aufgebaut werden würde.

Wir Sie mittels MySQL einen eigenen Hash-Index aufbauen, erfahren Sie im Artikel „Mysql PHP Index-Optimierung: Index-Werte optimieren mittels CRC32-MD5-Hash„.

Schreibe einen Kommentar