ZoranZaric.de

MySQL speichert abhängig von der verwendeten Storage-Engine Datensätze unsortiert (z.B. MyISAM) oder nach Primärschlüssel sortiert (InnoDB). Dies hilft uns leidern nicht sonderlich weiter, wenn wir unser Ergebnis nach einer anderen Spalte sortieren, Spalten eines bestimmten Kriteriums auswählen oder mit JOINs arbeiten wollen. Wollen wir bestimmte Werte aus unsortierten Daten auswählen, so müssen wir im schlechtesten Fall alle Daten anschauen. Dies kann bei großen Datenmengen selbstverständlich sehr aufwendig werden. Hier kommen MySQLs Indizes ins Spiel.

Ein Index ist eine Datenstruktur (meißt ein Baum) in dem Verweise auf die Daten in sortierter Form vorgehalten werden. Indizes werden i.d.R. am Stück auf das Speichermedium geschrieben, damit sie möglichst schnell eingelesen werden können. Brauchen wir nun alle Datensätze sortiert nach unserer indizierten Spalte, so können die Verweise am Stück eingelesen und die restlichen Daten in der richtigen Reihenfolge nachgeholt werden. Der zweite Anwendungsfall ist das Verbinden zweier Tabellen – JOINs. Hierbei indizieren wir den Fremdschlüssel der Tochtertabelle und vereinfacenh so die Sucharbeit beim JOIN.

Um genau das zu überprüfen habe ich mich mal hingesetzt und einen kleinen Benchmark geschrieben. Dieser Test sollte nicht zu ernst genommen, er stellt nur eine Stichprobe dar.

Mein kleiner Benchmark

Für meinen Benchmark habe ich zwei Tabellen zweimal mit den selben Daten gefüllt. Einmal wurden die Tabellen ohne Index angelegt, beim zweiten Test wurde ein Index auf dem Fremdschlüssel in der Zahlen-Tabelle angelegt.

Tabellen für Test 1

CREATE TABLE test1_person
        (id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id))
        ENGINE=MyISAM;

CREATE TABLE test1_zahl
        (id INT NOT NULL AUTO_INCREMENT,
        zahl INT,
        person_id INT,
        PRIMARY KEY (id))
        ENGINE=MyISAM;

Die Tabellen für Test 2

CREATE TABLE test2_person
        (id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id))
        ENGINE=MyISAM;

CREATE TABLE test2_zahl
        (id INT NOT NULL AUTO_INCREMENT,
        zahl INT,
        person_id INT,
        PRIMARY KEY (id),
        INDEX test2_zahl_person_id (person_id))
        ENGINE=MyISAM;

Hier haben wir die Syntax zum Anlegen eines Index. Im Beispiel geben wir ihm den Namen “test2_zahl_person_id” und die Spalte oder die Spalten (zu mehrspaltigen Indizes später mehr), im Beispiel “person_id“. Optional können wir noch die Sortierreihenfolge (ASC oder DESC) und die verwendete Datenstruktur (USING BTREE, USING HASH oder USING RTREE) festlegen.

Befüllt wurden die Tabellen mit 100 Personen und 100 Zahlen je Person, also 10 100 Datensätzen. Damit die Zahlen schön zufällig eingefügt werden, habe ich hierfür ein kleines PHP-Skript geschrieben. Sehr quick, sehr dirty…

define ('ANZAHLNAMEN', 1000);
define ('ANZAHLZAHLEN', 100);
 
$personen = array();
$fotos = array();
for($i=1; $i<=ANZAHLNAMEN; $i++) {
        $personen[$i]['name'] = 'John Doe';
 
        for($j=1; $j<=ANZAHLZAHLEN; $j++) {
                $zahlen[]['person_id'] = rand(1, ANZAHLNAMEN);
        }
}
 
$db = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('test');
 
$test1_person_dropsql = 'DROP TABLE test1_person;';
mysql_query($test1_person_dropsql);
 
$test1_person_createsql ='CREATE TABLE test1_person
        (id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id))
        ENGINE=MyISAM;';
mysql_query($test1_person_createsql);
 
$test1_zahl_dropsql = 'DROP TABLE test1_zahl;';
mysql_query($test1_zahl_dropsql);
 
$test1_zahl_createsql = 'CREATE TABLE test1_zahl
        (id INT NOT NULL AUTO_INCREMENT,
        zahl INT,
        person_id INT,
        PRIMARY KEY (id))
        ENGINE=MyISAM;';
mysql_query($test1_zahl_createsql);
 
for($i=1; $i<=ANZAHLNAMEN; $i++) {
        $test1_person_insertsql = 'INSERT INTO test1_person (name) VALUES (\'' . $personen[$i]['name'] . '\');';
        mysql_query($test1_person_insertsql);
}
foreach($zahlen as $zahl) {
        $test1_zahl_insertsql = 'INSERT INTO test1_zahl (zahl, person_id) VALUES(\'1\', \'' . $zahl['person_id'] . '\');';
        mysql_query($test1_zahl_insertsql);
}
 
$test2_person_dropsql = 'DROP TABLE test2_person;';
 
$test2_person_dropsql = 'DROP TABLE test2_person;';
mysql_query($test2_person_dropsql);
 
$test2_person_createsql ='CREATE TABLE test2_person
        (id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id))
        ENGINE=MyISAM;';
mysql_query($test2_person_createsql);
 
$test2_zahl_dropsql = 'DROP TABLE test2_zahl;';
mysql_query($test2_zahl_dropsql);
 
$test2_zahl_createsql = 'CREATE TABLE test2_zahl
        (id INT NOT NULL AUTO_INCREMENT,
        zahl INT,
        person_id INT,
        PRIMARY KEY (id),
        INDEX test2_zahl_person_id (person_id))
        ENGINE=MyISAM;';
mysql_query($test2_zahl_createsql);
 
for($i=1; $i<=ANZAHLNAMEN; $i++) {
        $test2_person_insertsql = 'INSERT INTO test2_person (name) VALUES (\'' . $personen[$i]['name'] . '\');';
        mysql_query($test2_person_insertsql);
}
foreach($zahlen as $zahl) {
        $test2_zahl_insertsql = 'INSERT INTO test2_zahl (zahl, person_id) VALUES(\'1\', \'' . $zahl['person_id'] . '\');';
        mysql_query($test2_zahl_insertsql);
}

Hier die Ergebnisse:

SELECT count(test1_person.id)
	FROM test1_person
	LEFT JOIN test1_zahl
	ON test1_person.id=test1_zahl.person_id;

+------------------------+
| count(test1_person.id) |
+------------------------+
|                 100000 |
+------------------------+
1 row in set (5 min 29.05 sec)

Also 5 Minuten 29 Sekunden.

SELECT count(test2_person.id)
	FROM test2_person
	LEFT JOIN test2_zahl
	ON test2_person.id=test2_zahl.person_id;

+------------------------+
| count(test2_person.id) |
+------------------------+
|                 100000 |
+------------------------+
1 row in set (0.29 sec)

Also 1/3 Sekunde.

Der Index macht unser Query also um etwa den Faktor 1000 schneller! Das finde ich schon beachtlich.

Nachteile eines MySQL-Index

Die gesteigerte Geschwindigkeit erkaufen wir uns mit Plattenplatz. MyISAM speichert die Daten einer Tabelle in einem File namens Tabellenname.MYD und die Index-Daten in einem File namens Tabellenname.MYI.

1.3M    test1_zahl.MYD
1008K   test1_zahl.MYI

1.3M    test2_zahl.MYD
2.7M    test2_zahl.MYI

Also verbraucht unser Index mehr als das doppelte Datenvolumen als unsere eigendlichen Daten. Auch die INSERT-Geschwindigkeit leidet unter einem Index. Genau Werte hierzu habe ich jedoch leider noch nicht. Liefere ich aber nach.

Fazit

MySQL-Indizes beschleunigen unsere SELECTs in einigen Fällen, kosten jedoch Platz und verlangsamen das Lesen. Als Faustregel kann man sich im Kopf behalten Indezes nur dann zu verwenden wenn sie notwendig werden. Auch bei Anwendungen in denen mehr geschrieben als gelesen wird sollte man auf jeden Fall Tests fahren.

Filed under:
Webentwicklung von Zoran