Setzen von Indexen
Insert mit und ohne Index
Mehrere Indexe setzen

Setzen von Indexen

nach oben

Es mutet merkwürdig an, dass bei Beschreibungen relationaler Datenbanken manchmal eher exotische Aspekte in den Vordergrund gerückt werden, während der zentrale Aspekt, nämlich das Setzen von Indexen, nur am Rande erwähnt wird. Tatsächlich ist eine relationale Datenbank, die Tabellen ohne Indexe verwendet, etwa so interessant wie ein Flatfile. Wird kein Index gesetzt, hat mysql und jede andere relationale Datenbank keine Chance, effizient nach Daten zu suchen. Sie arbeitet dann die komplette Tabelle sequentiell, das heisst ein Datensatz nach dem anderen beginnend mit dem ersten, ab. Hat man also 1 000 000 Datensätze in der Datenbank und wird der 999999zigste gesucht, wird die gesamte Tabelle durchsucht. Der entscheidende Punkt ist nun, dass es in relationalen Datenbanken möglich ist, bestimmte Spalten einer Tabelle mit einem Index zu versehen und diese Spalte separat in einer Baumstruktur gehalten wird. Sinnvollerweise setzt man den Index auf die Spalte, nach der oft gesucht wird. Der Index kann verschieden gestaltet sein. mysql verwendet den sogenannten b-tree, der die Daten in einer Art Baumstruktur hält. Dass das Suchen in einer Baumstruktur schneller ist, als die sequentielle Suche ist einleuchtend. Wir könnne uns das sehr leicht klarmachen. Alle Kataloge im Internet haben eine Baumstruktur. Wer Zahnärzte sucht in einer Stadt wird sich diesem Baum entlanghangeln: Berlin->Dienstleistungen->Ärzte->Zahnärzte. Das ist schneller als alle etwa vier Millionen Gewerbetreibende der BRD zu durchzuforsten. Bei einem Katalog wird man dann irgendwann auf einen Link stoßen, der einen tatsächlich zu der entrsprechenden Seite führt. Ähnlich ist es bei relationalen Datenbanken. Der Index hat einen Verweis auf den dahinterstehenden Datensatz. Da das Zeitraubendste beim Suchen von Daten die Zugriffe auf die Festplatte sind, versucht ein Index, der auf einem b-tree basiert die Anzahl der Zugriffe auf die Festplatte zu minimieren. Wollen wir irgendetwas testen, sehen wir uns mit unserem alten Problem konfrontiert. Wir brauchen erstmal eine Datei, die soviele Daten hat, dass man überhaupt merkt, ob ein Index gesetzt ist oder nicht. Wir verwenden hierzu eine Datei, die alle Domains mit Ortsbezug listet und die email Adresse des Domaininhabers. Es handelt sich um Orginaldaten, die unbrauchbar gemacht wurden. Insgesamt haben wir 87903 Datensätze. Es handelt sich um einen csf (comma separated file), das heisst der Delimiter ist ein Komma.

Diese Datei liegt erstmal als Textdatei vor, die man hier downloaden kann. Anschliessend muss noch eine Tabelle eingerichtet und die Daten eingespult werden. Das machen wir am besten wieder mit einem batch Prozess.

use testlauf;

create table domains(
domain char(30),
mail char(40));

load data infile 'c:\\mysql_handbuch\\resultatmod.txt' into table domains Fields terminated by ',';

Zumindest in der Version mysql 4.0.9 muss das local weggelassen werden, sonst kommt die Meldung das das verboten ist. Anstatt

load data local infile 'c:\\mysql_handbuch\\resultatmod.txt' into table Branchen Fields terminated by ',';

ist also sowas zu schreiben.

load data infile 'c:\\mysql_handbuch\\zweigtest3.txt' into table Branchen Fields terminated by ',';

Wir können die Daten, so den das oben abgebildete Programm unter dem Name reinspul.sql abgespeichert wurde, mit folgendem Kommando die Daten in die Tabelle einspeisen.

C:\>c:\mysql\bin\mysql.exe <c:\mysql_handbuch\reinspul.sql

Sucht man nun, ohne Index nach einem bestimmten Datesatz, ergibt sich diese Performance.

mysql> select * from domains where domain="steinenberg-city.de";

	domain		mail
	steinenberg-city.de		Andres_Ehmann@web.de

1 row in set (0.22 sec)
mysql>

Das heisst im Umkehrschluss, dass selbst bei einer Tabelle, die keinerlei Optimierung beinhaltet, mysql auch noch bei recht grossen Datenmenge diese in akzeptablen Zeiträumen durchwühlt. Wir wiederholen das ganze Procedere, setzen nun aber einen Index auf die Spalte Domain.

use testlauf;

create table domains(
domain char(30) NOT NULL,
mail char(40),
Index (domain));

load data infile 'c:\\mysql_handbuch\\resultatmod.txt' into table domains Fields terminated by ',';

Dazu zwei Bemerkungen. Primary Key können wir nicht verwenden, da bestimmte Domains, bedingt durch die Modifizierung, doppelt vorhanden sind. Da nun ein Index gesetzt wurde, dauert das Reinspulen der Daten spürbar länger. Dafür ist die Suche nach Datensätzen nun aber schneller.

mysql> select * from domains where domain="steinenberg-city.de";

	domain		mail
	steinenberg-city.de		Andres_Ehmann@web.de

1 row in set (0.06 sec)
mysql>

Das heisst, die Zeit die mysql braucht um einen Datensatz zu finden, wurde von 0,22 Sekunden auf 0,06 Sekunden reduziert. Das wird bei einer größeren Anzahl von Datensätze mit einer schlechteren Perfomance bei Insert erkauft, da dann der Index bei jedem Insert neu organisiert werden muss. Bei Update hängt es davon ab, ob die Index Zeile selber geändert wird, oder eine andere Spalte.
Insert ohne Index

mysql> insert into domains (Domain,mail) values
-> ("kalkstadt.de","webmaster@kalstadt.de");
Query OK, 1 row affected (0.00 sec)

mysql> update domains set mail="webmaster@kalkstadt.de"
-> where domain="kalkstadt.de";
Query OK, 1 row affected (0.22 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>

Insert mit und ohne Index

nach oben

mysql> insert into domains(Domain,mail) values
-> ("Kalkstadt.de","webmaster@kalstadt.de");
Query OK, 1 row affected (0.00 sec)

mysql> update domains set mail="webmaster@kalkstadt.de"
-> where domain="kalkstadt.de";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>

Bei diesem Beispiel haben wir allerdings zu wenig Datensätze, um eine Änderung bei Insert feststellen zu können. Bei update ist es mit Index schneller, da er den zu verändernden Datensatz schneller findet und der Index ja nicht neu geschrieben werden muss. Like mit einer Wildcard am Anfang, drückt die Performance,
da dann der Index nicht mehr verwendet werden kann.

mysql> select * from domains where domain like "%steinenberg%";

	domain		mail
	steinenberg-city.de		Andres_Ehmann@web.de

1 row in set (0.17 sec)

mysql> select * from domains where domain like 'steinenberg%';

	domain		mail
	steinenberg-city.de		Andres_Ehmann@web.de

1 row in set (0.05 sec)
mysql>

Wie deutlich zu sehen, braucht mysql 3 Mal so lange, wenn er einen Datensatz suchen muss, der mit Wildcard beginnt. Steht die Wildcard am Ende, ergeben sich keine Unterschiede hinsichtlich der Performance. (Die 1 Hunderstel Sekunde Unterschied im Vergleich zu einer Abfrage mit einer Abfrage auf Gleichheit ist wohl
eher durch die CPU Auslastung des Rechners bedingt. ). Schneller wird die Suche auch dann, wenn der Index sinnvoll verkürzt wird. Das sieht dann so aus.

use testlauf;

create table domains(
domain char(30) NOT NULL,
mail char(40),
Index (domain(10)));

load data infile 'c:\\mysql_handbuch\\resultatmod.txt' into table domains Fields terminated by ',';

Führt man nun den selben sql Statement von oben nochmal aus, erhält man sowas.

mysql> select * from domains where domain="steinenberg-city.de";

	domain		mail
	steinenberg-city.de		Andres_Ehmann@web.de

1 row in set (0.00 sec)
mysql>

Wie deutlich sichtbar, wurde die Performance im Vergleich zu der Abfrage oben, mit Index aber ganze Zeichenlänge, deutlich verbessert.

Mehrere Indexe setzen

nach oben

Bei sehr vielen Datensätzen, über einer Million, bietet sich ein anderes Vorgehen an. Es gibt Anwendungen, bei denen immer nach einer bestimmten Reihenfolge gesucht wird, etwa bei Branchenbüchern. Wenn die Telefonnummer einer bestimmten Person gesucht wird, ist es günstiger, erstmal nach der Stadt zu suchen und in der dann verbleibenden Teilmenge nach der Person. Auch hier haben wir wieder unser altes Problem. Wir brauchen eine Tabelle, die ausreichend komplex strukturiert ist. Wir gehen von einer Tabelle mit dieser Struktur aus.

Da wir aus Gründen des Datenschutzes nicht mit echten Werten arbeiten können, generieren wir uns eine solche Datei mit Perl. Das kleine Perl Programm sieht so aus.

open(kirsche,"orte.txt");
@banane=<kirsche>;
close(kirsche);

open(kirsche,"branchen.txt");
@gurke=<kirsche>;
close(kirsche);

print $#gurke."\n";
print $#banane."\n";

foreach $i(0..$#banane)
{
chomp($banane[$i]);
foreach $e(0..$#gurke)
{
chomp($gurke[$e]);
push(@fertisch,"\n$banane[$i],$gurke[$e],".int(rand(30)));
}
}

open(kirsche,">fertisch.txt");
print kirsche @fertisch;
close(kirsche);

Wir erhalten dann eine Datei, die folgendermassen strukturiert ist.

Wie ersichtlich, verwendet es die Datei, orte.txt und die Datei branchen.txt. Die man hier (Datei orte.txt) und hier (Datei branchen.txt) runterladen kann. Anschliessend kann man den Perl Skript auslösen. Man erhält dann eine Datei fertisch.txt die etwa 14 MB gross ist und etwa 573000 Datensätze hat. Diese Datei spulen wir dann in die mysql Datenbank. Zuerst ohne Index, dann mit einem Index auf Orte und anschliessend mit einem Index auf Branchen und Ort. Wer keinen Perl Interpreter zur Verfügung hat, kann die Datei auch hier herunterladen. Sie ist aber gezippt zirka 2,5 MB gross.

Fall eins: Die Tabelle besitzt keinen Index

use testlauf;

create table branchen(
ort char(30),
branchen char(40),
telefon char(4));

load data infile 'c:\\database\\fertisch.txt' into table branchen Fields terminated by ',';

Wir lösen diesen batch file mit folgendem Kommando aus.

C:\>c:\mysql\bin\mysql.exe <c:\mysql_handbuch\reinspul.sql

Anschliessend können wir noch kurz testen, ob alle Daten angekommen sind.


mysql> select count(*) from branchen;

	count(*)
	573040

1 row in set (0.00 sec)
mysql>

Anschliessend führen wir auf diese Tabelle ohne Index eine Abfrage durch.

mysql> select * from branchen where
-> ort="Feldkirchen" and branchen="Restaurant";

	ort		branchen		telefon
	Feldkirchen		Restaurant		22

1 rows in set (11.04 sec)
mysql>

Wie deutlich zu erkennen, ist das nun ohne das Setzen eines Indexes nicht mehr machbar, bzw. sehr langsam.

Fall zwei: Wir setzen einen Index auf Orte
Auf Orte einen Index setzen, ist erstmal keine gute Idee. Sinnvoller wäre es, auf Branchen einen Index zu setzen, den hiervon haben wir weniger. Wir haben
etwa 40 Branchen, aber über 11000 Städte. Umgekehrt wäre es besser. Wir setzen jetzt aber aus didaktischen Gründen erstmal einen Index auf Städte.

use testlauf;

create table branchen(
ort char(30) NOT NULL,
branchen char(40),
telefon char(4),
index(ort));

load data infile 'c:\\database\\fertisch.txt' into table branchen Fields terminated by ',';

Wir stellen fest, dass das reinspulen der Daten jetzt länger dauert. Wir lösen jetzt nochmal den gleichen sql statement wie oben aus.

mysql> select * from branchen where
-> ort="Feldkirchen" and branchen="Restaurant";

	ort		branchen		telefon
	Feldkirchen		Restaurant		22

1 rows in set (0.11 sec)
mysql>

Wie deutlich sichtbar, wurde die Performance drastisch verbessert. Wir setzen jetzt, bevor wir das machen, was man sinnvollerweise macht, nämlich einen Index auf Branchen und Orte zu setzen, einen Index auf Branchen.

use testlauf;

create table branchen(
ort char(30),
branchen char(40) NOT NULL,
telefon char(4),
index(branchen));

load data infile 'c:\\database\\fertisch.txt' into table branchen Fields terminated by ',';

Anschliessend lösen wir wieder den selben SQL Statement aus

mysql> select * from branchen where
-> ort="Feldkirchen" and branchen="Restaurant";

	ort		branchen		telefon
	Feldkirchen		Restaurant		22

1 rows in set (0.00 sec)
mysql>

Und wieder wurde unsere Datenbank schneller. In diesem Falle ist es also günstiger, den Index auf Branchen zu setzen und nicht auf Orte. Noch schneller, wenn auch jetzt nicht mehr wahrnehmbar, wird es, wenn man einen Index sowohl auf Branche als auch auf Orte setzt. Allerdings geht das nur, wenn man weiß, dass immer
in dieser Reihenfolge gesucht wird. Ein Index über Branche und Ort optimiert zwar auch für Branche, aber nicht für Ort. Wir setzen also schluss endlich noch einen Index auf Branche und Ort und starten dann drei Abfragen. Eine die nach Ort und Branche sucht, eine die nur nach Branche sucht und eine die nur nach Ort sucht.

use testlauf;

create table branchen(
ort char(30) NOT NULL,
branchen char(40) NOT NULL,
telefon char(4),
index(branchen,ort));

load data infile 'c:\\database\\fertisch.txt' into table branchen Fields terminated by ',';

Bei der Abfrage spielt sich der Unterschied jenseits der Hundertstel Sekunden Grenze ab, so dass wir den Unterschied nicht mehr feststellen können.

mysql> select * from branchen where
-> ort="Feldkirchen" and branchen="Restaurant";

	ort		branchen		telefon
	Feldkirchen		Restaurant		22

1 rows in set (0.00 sec)
mysql>

Sucht man allerdings nur nach Ort, kann dieser Index nicht genutzt werden.

mysql> select * from branchen where
-> ort="Feldkirchen" and telefon="22";

	ort		branchen		telefon
	Feldkirchen		Tischlerei		22
	Feldkirchen		Restaurant		22
	Feldkirchen		Elektromeister		22

3 rows in set (1.54 sec)
mysql>

Wie deutlich zu sehen, konnte der Index für Orte nicht genutzt werden. Bei einer Kombination von Indexen, kann nur der genutzt werde, der ganz links steht. Macht man das gleiche nochmal mit Branchen, kann der Index wieder genutzt werden.

mysql> select * from branchen where
-> branchen="Restaurant" and telefon="22" limit 5;

	ort		branchen		telefon
	Asbuettel		Restaurant		22
	BadSchmiedeberg		Restaurant		22
	Ahlbeck-Seebad		Restaurant		22
	Stolzenhagen		Restaurant		22
	Ahlstaedt		Restaurant		22

5 rows in set (0.06 sec)
mysql>

Wie aufgrund der Performance ersichtlich, kann der Index Branchen, er steht ganz links der Deklaration, genutzt werden. Es hätte sich als Alternative noch angeboten, auf die Spalten Branchen und Ort jeweils einen Index zu setzen.

vorhergehendes Kapitel	nach oben	nächstes Kapitel