Über Indizes und Schlüssel

[Luckie]

Ich habe eine Datenbank mit folgenden Felder:

Code:

ID: Integer, auto_increment, primary key
name: Zeichenkette
vorname: Zeichenkette
...

Auf dem ID Feld habe ich also einen primären Schlüssel gesetzt. Das habe ich mehr so aus dem Gefühl raus gemacht. Heißt das, dass ich jetzt diese Spalte der Tabelle indiziert habe? Und wenn ja, was bringt das? Und sollte ich noch andere Spalten indiziere? Wie zum Beispiel name, vorname, da das wohl die Felder in einer Adress-Datenbank sind, die am häufigsten abgefragt werden und nach denen am häufigsten sortiert wird.

[Robert_G]

Ein Index wird normalerweise in aufsteigender Reihenfolge abgelegt. Deshalb ind Sortierungen über ein indiziertes Feld meist schneller.
Es gibt aber auch einen gewissen Overhead um vom Index auf den passenden Datensatz zu springen.
Angenommen du hast zum Beispiel 2 Tabellen:

Code:

Master:
PK   integer, -- Primärschlüssel
Name varchar(55)

Detail:
PK        integer, -- Primärschlüssel
FK        integer, -- Fremdschlüssel auf Master.PK
SomeValue varchar(55)

Die Mastertabelle enthält 10 Datensätze, die Detailtabelle 1.000.
Jetzt fragst du in der Detailtabelle alle Datensätze nach einem bestimmten Masterdatensatz ab (zum Bsp.: WHERE FK = 5).
Wenn das Feld FK indiziert ist, muss die DB nur 10 verschiedene Möglichkeiten durchlaufen, da der Index auch noch sortiert ist läuft sie nur die ersten 5 durch.
Das heißt, du hast auf eine sehr ökonomische Art die Ergebnismenge auf ca. 100 Datensätze reduziert. ;)
Eine Indexsuche auf den PK muss nicht schneller sein, da er zu jedem Indexwert den "richtigen" Datensatz suchen muss.
Suchst du zum Beispiel nach ...

SQL-Code:

WHERE (PK between 1 and 5) and Name like 'A%'

...muss er für jede Zeile erst im Index suchen, danach den passenden Datensatz finden um schließlich darin nach dem Namen zu suchen.
Da der Index aber sortiert abgelegt wird, werden Bedingungen, die nur indizierte Felder einschließen massiv schneller. ;)
Ein großer Nachteil von Indizes ist die Tatsache, dass sämtliche Datenänderungen langsamer werden (Die Sortierung muss ja neu aufgebaut werden).
Man muss also immer abwiegen, ob ein Index an dem Feld Sinn macht. Foreign Keys würde ich zum Beispiel immer indizieren, Prim Keys auch (Man greift ja oft direkt über den PK auf die Daten zu).

[Luckie]

Danke, das heißt, ein Schlüssel auf eine Spalte indiziert die selbige. Und wenn ich das jetzt richtig verstanden habe, wäre es bei mir noch sinnvoll auf den Namen und Nachnamen einen Schlüssel zu setzen. Und es wäre wohl auch ratsam nach größeren Einfüge- und Löschaktionen den Index zu aktualisieren oder?

[PRehders]

Hallo Luckie,

man sollte immer auf die Spalten einen Index setzen, die häufig für Abfragen und Zugriffe benutzt werden. Natürlich bedeuten Indizes einen Overhead beim Einfügen und Löschen. Hier muss man abwägen zwischen der reinen Lehre und der Praxis. In einer kleinen Tabelle (was klein ist, hängt sehr von der Datenbank und der Maschine ab) müssen sich Indizes nicht unbedingt lohnen, da kann die DB im Zweifelsfalle die Tabelle durchsehen (Full-Table-Scan). Manch DB tut das ohnehin, auch wenn ein Index vorhanden ist, wenn sie sieht, dass sie die Daten ohnehin braucht.
Normalerweise sollte man aber diese Betrachtung der DB überlassen und ihr helfen, indem man die Indizes anlegt.

Die Index-Dateien werden allerdings immer automatisch aktuell gehalten, was Du meinst, ist das Reorganisieren, wenn sehr viel hin- und hergeändert worden ist.
Wenn bei deiner DB häufig Masseninserts und -deletes stattfinden, gibt es noch einen kleinen Trick: Vor der Aktion Indizes löschen, danach wieder definieren. Dann muss die DB nicht während der Inserts soviel nachziehen und ausserdem weiss sie hinterher, wieviele Daten mit welcher Streuung in die Indizes aufgenommen werden müssen. Dann kann dann im Zweifelsfalle noch etwas optimiert werden.

Bis dann

Peter

[Luckie]

Danke Peter, genau das wollte ich wissen. Nur um es noch mal klar zustellen. Eine Spalte indiziere ich, in dem ich einen Schlüssel daraufanlege.

Jetzt habe ich auf die ID des Datesatzes den Primärschlüssel gesetzt, weil es ein auto inkrement Feld sein sollte und nie null. Kann man auch sekundäre Indizes anlegen? Und wenn ja wie macht man das mit mySQL?

SQL-Code:

query := CREATE TABLE Kontakte(id INT NOT NULL AUTO_INCREMENT, name varchar(20), '
    + 'vorname varchar(20), strasse varchar(55), plz int, ort varchar(50), ' +
    'telefon1 varchar(17), telefon2 varchar(17), fax varchar(17), email1 varchar(50), ' +
    'email2 varchar(50), url varchar(50), gebdat date, firma varchar(25), '
    + 'ts timestamp, PRIMARY KEY(id))'

Ich habe es schon mit SECONDARY KEY(name) probiert, doch dann sagt er mir, dass die Syntax im Query falsch wäre.

[Sharky]

Zitat:

Zitat von Luckie

....Ich habe es schon mit SECONDARY KEY(name) probiert,...

Einfach nur KEY ;-)

SQL-Code:

CREATE TABLE `test2` (
  `strasse` varchar(100) NOT NULL default '',
  `vorname` varchar(100) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`),
  KEY `key2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Luckie]

Hmpf. Auf so was kommt man natütlich nicht. Jetzt steht in der Spalte KEY der Tabelle PRI für Primary Key und MUL in den Feldern Name und Vorname. Nur der Interesse halber, für was steht MUL?

[Sharky]

Zitat:

Zitat von Luckie

... für was steht MUL?

Das mySQLControl-Center zeigt MUL als Keytyp an um zu zeigen das es ein Indey ist in dem mehrmals der selbe Wert vorkommen darf.
Es gibt auch noch Unique-Keys. Dann darf ein Wert nur einmal vorkommen.

[Luckie]

Ich nehme an, dann steht da UNI?

[Sharky]

Zitat:

Zitat von Luckie

Ich nehme an, dann steht da UNI?

Jup!
Wenn Du z.B. keinen Primärschlüssel angibst wird der erst unique-Key als Primärschlüssel verwendet da dieser ja einen Datensatz EINDEUTIG identifieziert.