Hallo, in der MySQL-Doku steht ja schon was darüber drin, aber ich möchte doch gern als Anfänger in verständlichen Worten erklärt haben, wie und warum ich in einer MySQL-Datebanktabelle einen Index setzen sollte. Ein Beispiel mit Syntaxerklärung wäre richtig nett!

Hallo Canio

Der Index dient zur Eindeutigkeit von Datensätzen und deshalb kann ein Wort oder Zahl nur einmal vorkommen.

Ich meinte eigentlich nicht den Primärschlüssel, sondern einen Index, der mit folgender Syntax erstellt wird:

CREATE TABLE test (
id INT,
INDEX(id)
);

Hallo Canio,

kurze Antwort:

damit alles viel schneller geht.

lange Antwort:

Eine Datenbanktabelle ist im Grunde nur eine Aneinanderreihung von Datensätzen(rows).

Jetzt stelle dir mal vor, du willst in einer größeren Tabelle bestimmte Datensätze finden.
Dazu muss nun jeder Datensatz mit deinem Suchkriterium verglichen und, wenn eine Übereinstimmung besteht, ausgegeben werden.

Wenn man nun einen speziellen Index hätte, in dem die gesuchten Daten sortiert vorliegen, könnte man sich das komplette Durchsuchen der entsprechenden Tabelle sparen. Das wird ermöglicht, weil für sortierte Daten sehr effiziente Suchalgorithmen existieren. Die Anzahl der Such- bzw. Vergleichsvorgänge entspricht dabei in etwa dem dualen Logarithmus der Anzahl der Datensätze(ganzzahlig aufgerundet). Das ist natürlich deutlich weniger.

Bsp.:
100 000 Datensätze ohne Index => 100 000 Zugriffe
(statistisch wird etwa nur die Hälte benötigt: also 50 000)
100 000 Datensätze mit Index => 17 Zugriffe

1 000 000 DS ohne Index => 500 000 Zugriffe
1 000 000 DS mit Index => 21 Zugriffe

Das ist der Idealfall. In der Praxis werden allerdings einige Zugriffe mehr benötigt. Die genaue Begründung lass ich hier einfach mal weg.

Dazu gibt es nun verschiedene Indextypen

- Primärschlüssel
Dieser Index ist für eine eindeutige Datensatz ID reserviert.

- Unique Index
Dieser Index ist für eindeutige IDs, die nicht der Primärschlüssel sind.

- Index

Dieser Index ist für Werte, die innerhalb einer Tabelle mehrfach auftreten können.

- Fulltext Index
Dieser Index wird für die Volltextsuche verwendet

In einem Index können auch mehrere Spalten miteinander verknüpft werden(zusammengesetzte Schlüssel).

Wann wirkt ein Index?
Bei:
- DISTINCT
- JOIN
- WHERE
- GROUP BY
- ORDER BY
- LIMIT
usw.

Nachteile gibt es allerdings auch. Bei INSERT bzw. UPDATE müssen jedesmal die betroffenen Indizes aktualisiert werden, was bei häufigem Auftreten dieser Operationen einen Geschwindigkeitsverlust bewirkt. Deshalb ist auch eine genaue Planung notwendig.

Das ist ja mal eine ausführliche Antwort, vielen Dank!

Okay, also die Zugriffe werden drastisch reduziert. D.h., wenn ich eine Tabelle "user" habe, sollte ich wohl besser mit Indizes arbeiten.
Aber wie genau realisiere ich den Index? Gehen wir mal von dieser einfachen Tabelle aus:

Code

"user" 
u_id         INT <--- Primärschlüssel 
name         VARCHAR 
vorname      VARCHAR 
[i]und noch ein paar Eigenschaften 

Wie ist dann die genaue Syntax bzw. was gehört dann zum Index? Kann ich auch u_id als Index benutzen, oder doch eher name, oder sogar name UND vorname???

Der Primärschlüssel ist doch schon ein Index mit dem Namen PRIMARY.

Für name und vorname wird nur dann ein Index benötigt, wenn eine der von mir oben beschriebenen Operationen auf diese Spalten angewendet werden.

NB:

bei CHAR bzw. VARCHAR Spalten ist es besser, den Index auf eine sinnvolle Länge zu kürzen(z.B. 20).

Also ich versteh das jetzt so.
Hat man 100.000 Datensätze in einer Tabelle und man sucht z.B. alle Felder ab mit dem Eintrag "Schmidt", so sucht MySQL sequentiell.

Sucht man nach der ID 20034, so kommt ein spezieller Suchalgorithmus zum Tragen (da ID ein PRIMARY KEY ist).

Würd ich jetzt auf das Feld "name" einen Index legen, und dann nach "Schmidt" suchen, kommt einer dieser speziellen Suchalgorithmen zum Zuge, was wesentlich schneller geht als ohne Index.

Demnach ist es also nicht nötig, auf Primärschlüssel einen Index zu setzen???

Genau das wurde aber bei der Joomla-Erweiterung VirtueMart gemacht. Dort gibt es eine Tabelle namens "joomla_vm_zone_shipping". Der Primärschlüssel heißt "zone_id", und die Tabelle hat zusätzlich noch einen Index namens "zone_id".
Siehe hier:

Code

CREATE TABLE joomla_vm_zone_shipping ( 
  zone_id INTEGER(11) NOT NULL, 
  [... ] 
  PRIMARY KEY(zone_id), 
  INDEX zone_id(zone_id) 
); 

Oder versteh ich da etwas völlig falsch???

Zitat

Demnach ist es also nicht nötig, auf Primärschlüssel einen Index zu setzen???

Genau das wurde aber bei der Joomla-Erweiterung VirtueMart gemacht.

Und genau daran sieht man unter anderem, wie schlampig joomla programmiert wurde.

Greifen die Suchalgorithmen eigentlich auch bei Fremdschlüsseln? Oder muss man die selber indizieren?

Antwort auf [ oesi50 ]

Nachteile gibt es allerdings auch. Bei INSERT bzw. UPDATE müssen jedesmal die betroffenen Indizes aktualisiert werden, was bei häufigem Auftreten dieser Operationen einen Geschwindigkeitsverlust bewirkt. Deshalb ist auch eine genaue Planung notwendig.

Hallo Oesi50 :)
Ist denn dann die Datenlast beim INSERT/UPDATE selbst das Problem? Ich habe ein Script, dass jede Minute ca. 700 neue Zeilen (a 40 Spalten) in eine bisher 6.5 Mio Einträge große Datenbank schreibt. Was kann ich da tun, um das alles zu beschleunigen?

Danke im Vorraus für jegliche Hilfe.

Hallo damarges,

das kommt darauf an, wie deine Daten strukturiert sind.

Prinzipiell gilt, je mehr Indizes angelegt sind, desto länger dauert ein INSERT.
Bei UPDATE trifft es nur zu, wenn indizierte Spalten geändert werden.

Auch ist es hilfreich, key_buffer_size auf eine Größe zu setzen, mit der alle
betreffenden Indexdaten komplett in den Speicher(Ram) passen.

btw.:
700 Zeilen pro Minute ist eigentlich relativ wenig :-)

Hm.. okay und wie kann ich herausfinden, wie groß das nun ist?
Ich kann nur 16 Spalten maximal indizieren.. ich habe nun die Spalten gewählt die oft gleich bleiben bzw. nur selten geänderte Werte haben, aber nach denen auch oft gesucht wird, war das richtig?

Antwort auf [ damarges ]

Hm.. okay und wie kann ich herausfinden, wie groß das nun ist?

Code

SHOW VARIABLES LIKE 'key%' 

Antwort auf [ damarges ]

Ich kann nur 16 Spalten maximal indizieren.. ich habe nun die Spalten gewählt die oft gleich bleiben bzw. nur selten geänderte Werte haben, aber nach denen auch oft gesucht wird, war das richtig?

Das hilft aber nur bei UPDATE.