DDL(2) - flussufer.de

DDL (2) DML (1)
SELECT (2)
Schlüssel und Index
SZR, LK Inf 13/1, Cs
1. Allgemeines über Schlüssel und Index
Das Gute an Schlüsseln und Indizes
Primärschlüssel haben in Datenbanktabellen eine besondere Bedeutung. Darüber hinaus ist jeder
Primärschlüssel auch ein Index. Eine Tabelle kann nur einen Primärschlüssel, aber viele Indizes haben. Der
richtige Gebrauch von Indizes kann die Performance deiner Datenbank beträchtlich beeinflussen. Vergleiche
es mit einem Buch-Index – nur wenige Seiten ermöglichen dir, einen Eintrag ganz schnell zu finden. Wenn
du das ganze Werk durchsehen müsstest, könnte dies unter Umständen eine halbe Ewigkeit dauern.
Syntax
Abweichend vom Syntaxdiagramm benutzt in MySQL bitte folgende Befehle zur Erstellung eines
Indexes/eines Primärschlüssels. Die Befehle stellen eine Erweiterung von ALTER TABLE dar:
ALTER TABLE Tabellenname ADD INDEX (Spaltenname[, ...]);
Der Indexname (den man beim Löschen braucht) entspricht dem Namen der erstgenannten Spalte.
ALTER TABLE Tabellenname ADD PRIMARY KEY (Spaltenname[, ...]);
Mit den nachfolgenden Befehlen löscht man Indizes/Schlüssel wieder. Einen Index bzw. einen Schlüssel zu
löschen hat keine Auswirkungen auf die eigentlichen Spalten oder den Datenbestand der Tabelle.
ALTER TABLE Tabellenname DROP INDEX Indexname;
ALTER TABLE Tabellenname DROP PRIMARY KEY;
Wie kriege ich raus, welche Indizes momentan in der Tabelle definiert sind?
Den entsprechenden SQL-Befehl zur Anzeige aller Indexnamen kenne ich (noch) nicht, aber es geht im
MySQL Control Center, dort im Console Manager, mit: rechte Maustaste auf die Datenbank, Connect; dann
das „+“ vor Tables klicken, mit der rechten Maustaste auf die gewünschte Tabelle, Edit Table; dort die
Registerkarte Indexes wählen, bei „Index Name“ auf den Pfeil klicken. Dort werden alle Indizes angezeigt,
und du kannst dort auch welche einrichten und löschen (alternativ zu den SQL-Befehlen).
Zusammengesetzte Schlüssel und Indizes
Wie die oben abgebildete Syntax zeigt, dürfen für einen Schlüssel auch mehrere Spalten der Tabelle
angegeben werden. Z. B. kann man einen Schlüssel (Schüler, Fach) definieren. Als Primärschlüssel sind
solche zusammengesetzten Schlüssel unverzichtbar (siehe später bei n:m-Beziehungen). Genauso kann
man auch einen zusammengesetzten Index erzeugen. Ein zusammengesetzter Index erfüllt den gleichen
Zweck wie ein Index auf seine erstgenannte Spalte. Darüber hinaus hat er den Vorteil, eine schnelle Suche
bezogen auf eine Werte-Kombination seiner ersten beiden, seiner ersten drei usw. Spalten durchführen zu
können.
2. Eindeutige Schlüssel und Primärschlüssel
Primärschlüssel
In jeder Tabelle sollte es einen Primärschlüssel geben. Der Primärschlüssel identifiziert eindeutig den
Datensatz und dient zum Verknüpfen zweier (oder mehrerer) Tabellen miteinander. Der Primärschlüssel
muss grundsätzlich immer zwei Bedingungen erfüllen:
1. NOT NULL. Nullwerte in Primärschlüsselfeldern sind verboten.
2. UNIQUE. Jeder Schlüsselwert darf nur einmal vorkommen, muss also eindeutig sein.
Es darf je Tabelle nur einen Primärschlüssel geben. Zusätzliche eindeutige Schlüssel verwenden wir nicht,
die Syntax wäre ALTER TABLE Tabellenname ADD UNIQUE (Spaltenname[, ...]); MySQL würde den ersten
in der Tabelle definierten UNIQUE-Index automatisch zum Primärschlüssel machen, sofern die Tabelle noch
keinen Primärschlüssel hat und wenn die NOT NULL-Bedingung ebenfalls erfüllt ist.
AUTO_INCREMENT
Es ist eine übliche Vorgehensweise, Tabellen dann, wenn sich kein Primärschlüssel anbietet, um eine
zusätzliche Spalte mit einer ID-Nummer zu erweitern. Man kann diese Nummern von Hand vergeben (oft
wird das auch getan, z. B. bei Personal- und Kundennummern). Buchungen werden hingegen meist mit
einer automatischen ID belegt. Dafür gibt es bei der Spaltendefinition die AUTO_INCREMENT-Option. Wenn
die ID-Spalte mit AUTO_INCREMENT definiert wird, muss man sich um eine Wertzuweisung nicht kümmern.
Jedes Einfügen eines Nullwertes in eine AUTO_INCREMENT-Spalte sorgt automatisch dafür, dass der Wert
um 1 größer wird als der momentan größte Wert der Spalte.
Nachfolgend eine Erweiterung deines Syntaxdiagramms bezüglich der Spaltendefinition bei den DDLBefehlen ALTER TABLE und CREATE TABLE. Trage die Optionen bei der Spaltendefinition, die im unten
gezeigten Syntaxbeispiel verwendet werden, bitte in dein Syntaxdiagramm ein. Ergänze auch noch die
DDL (2) DML (1)
SELECT (2)
Schlüssel und Index
SZR, LK Inf 13/1, Cs
Option „DEFAULT Wert“ aus dem DDL(1)-Papier. Achte bitte auf den Unterstrich bei AUTO_INCREMENT im
Gegensatz zur Schreibweise ohne Unterstrich bei PRIMARY KEY und NOT NULL. Übrigens: UNIQUE und
NOT NULL sind in diesem Beispiel überflüssig und können weggelassen werden, da diese beiden Optionen
in PRIMARY KEY bereits enthalten sind.
CREATE TABLE customers (
customer_number INT(8) AUTO_INCREMENT PRIMARY KEY UNIQUE NOT NULL,
last_name VARCHAR(30),
...)
3. Indizes
Fremdschlüssel
Fremdschlüssel sind Spalten in einer Tabelle, welche auf die Datensätze einer anderen Tabelle verweisen.
Zu diesem Zweck wird der zugehörige Primärschlüsselwert der anderen Tabelle dort eingetragen. MySQL
hat kein Schlüsselwort für Fremdschlüssel. (In anderen Datenbanken kann man FOREIGN KEY angeben.)
Fremdschlüssel gehören inhaltlich eigentlich in Abschnitt 2. Warum habe ich sie hier eingefügt?
Man sollte Fremdschlüsselspalten immer indizieren. Das geschieht nicht automatisch. (Das gilt nicht nur für
MySQL, sondern für viele Datenbanken.)
Indizes
Ein Index ist eine Art Extra-Tabelle in der Datenbank. Er nimmt physischen Platz auf der Festplatte der
Datenbank ein und kann recht groß werden, theoretisch sogar größer als die Haupttabelle. Einen Index bzw.
einen Schlüssel zu löschen hat aber keine Auswirkungen auf den Datenbestand.
Vorteil: Die Suche nach indizierten Werten läuft um ein vielfaches schneller ab, wenn die Tabelle viele
Datensätze enthält.
Nachteil: Bei INSERT, UPDATE und DELETE muss der Index mit gepflegt werden, so dass der Aufwand
dieser Operationen zunimmt (man spricht von „Overhead“). Deswegen kann es sogar sinnvoll sein, den
Index zunächst zu löschen und anschließend neu zu erstellen, wenn zu einer bestimmten Zeit eine Vielzahl
von Aktualisierungen in eine Datenbank-Tabelle eingespielt wird.
Welche Spalten brauchen einen Index?
Alle Fremdschlüsselspalten brauchen einen Index (siehe oben).
Alle Spalten, die oft in WHERE-Klauseln benutzt werden, brauchen einen Index.
Das Gleiche gilt für ORDER BY und GROUP BY-Klauseln sowie für die Spalten, die in MIN( ), MAX( )
benutzt werden. (AVG ( ) hingegen nicht, denn dabei müssen ohnehin alle Daten gelesen werden).
Full Table Scan
Ein „Full Table Scan“ bedeutet, dass alle Datensätze der Tabelle hintereinander durchsucht werden. Das
dauert bei Tabellen mit vielen Datensätzen sehr lange, zumal die Datensätze ja auch alle erst einmal von
der Festplatte in den Hauptspeicher geladen werden müssen. Ein „Full Table Scan“ ist die Folge davon,
wenn kein Index vorhanden ist.
Wann sollte kein Index erzeugt werden?
1. In Tabellen, die nur wenige Datensätze enthalten, macht ein „Full Table Scan“ nichts aus. Abgesehen
vom Primary Key kann man hier auf Indizes verzichten.
2. In Spalten, in denen nur wenige unterschiedliche Werte vorkommen, bringt ein Index nur wenig Vorteile,
denen der Nachteil des Overhead gegenübersteht.