Hier im Forum bekommt ihr bei euren fragen schnelle hilfe.Hier geht es rund um das Web SeitenProgrammieren.Alles rund ums Javascript,Html,Php,Css und Sql.Auf fast allen Fragen haben wir eine Antwort.
Der Soforthilfe-chat verspricht das ,was sein Name sagt. Hier sind Leute Online die sofort ihre hilfe anbieten.Seht in der OnlineListe nach und wenn einer Online ist werdet ihr auch antwort bekommen. Admine ,Moderatoren und Helfer sind unsere Spezialisten in Sachen Web Programierung

Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / index.php

Random Datenbank Themen, Tutorials und co

24.09.2019 06:34:28

Event-getriebene Systeme, Teil 1

In klassischen Anwendungen mit normalisierter Datenbank wird für Entitäten stets deren aktueller Zustand gespeichert. Das bedeutet im Umkehrschluss, dass der alte Zustand stets zugunsten des neuen überschrieben wird.
Im Gegensatz dazu gibt es event-getriebene Systeme (event sourced systems), bei denen nur Domain-Events, also die Datenänderung von Entitäten, gespeichert werden, nicht die tatsächlichen Werte von Entitäten. Dies kann durchaus Vorteile bezüglich der Flexibilität gegenüber zukünftigen Änderungswünschen haben. Dieser Beitrag erklärt, wie ein eventgetriebenes System funktioniert und welche Vor- und Nachteile es bietet.

Eventgetriebenes System am Beispiel erklärt

Beispiel: Wir haben ein User-Objekt (= Entität) mit den Feldern ID, username, password, email.
Zur Speicherung der Events, die User-Attribute verändern, nutzen wir eine relationale Datenbank mit der Tabelle „user-events“:

CREATE TABLE `user-events` (  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `user_id` INT(10) UNSIGNED NOT NULL,  `field` VARCHAR(255) NOT NULL,  `value` VARCHAR(255) NOT NULL,  PRIMARY KEY (`id`),  KEY `user_id` (`user_id`) )

Angenommen unsere Beispiel-Anwendung bietet die Möglichkeit einen Nutzer über die Oberfläche anzulegen. Dazu werden einfach in die Tabelle user-events die Änderungen hinzugefügt:

id user_id field value time
1 1 username Hans 2015-01-01 12:00
2 1 password abcde 2015-01-01 12:00
3 1 email hans@example.org 2015-01-01 12:00

Wenn in der Zwischenzeit Daten geändert werden, wird einfach die Datenänderung hinzugefügt:

id user_id field value time
1 1 username Hans 2015-01-01 12:00
2 1 password abcde 2015-01-01 12:00
3 1 email hans@example.org 2015-01-01 12:00
4 1 email hansi@domain.com 2015-01-02 10:00

In einer anderen Funktion unserer Beispiel-Anwendung soll ein Newsletter an die Nutzer verschickt werden. Dazu werden die aktuellen Daten jedes Nutzers benötigt.
Das kann z.B. folgendermaßen umgesetzt werden:
Es gibt ein UserRepository, das an den Controller „SendNewsletter“ per Dependency Injection übergeben wird. Dieses Repository enthält die Methode findAllUsers(), die alle dem System bekannten Nutzer in Form von User-Objekten mit den aktuellen Usernamen und E-Mail-Adressen zurückliefert. Diese User-Objekte kann der Controller „SendNewsletter“ dann weiter verarbeiten.
Zur Erklärung ist hier der (unvollständige) Code der UserRepository-Klasse:

class UserRepository {   public function findAllUsers() {     $sql = "SELECT evt.user_ID, evt.field, evt.value              FROM (                 SELECT MAX(ID) AS maxID                 FROM `user-events`                 GROUP BY user_id, field             ) AS newest              INNER JOIN `user-events` evt ON newest.maxID=evt.ID";       $users = [];     foreach ($this->dbh->query($sql) as $row) {       if(!isset($users[$row['user_ID']])) {         $users[$row['user_ID']] = new User();       }         $users[$row['user_ID']]->$row['field'] = $row['value'];     }       return $users;   } }

Wir können demzufolge den aktuellen Stand der User-Objekte aus den aufgezeichneten Events jederzeit wiederherstellen.

Vorteil: Flexibilität für neue Funktionen

Irgendwann kommt nun der Product Owner auf die Idee, eine Hinweismeldung einzublenden für Nutzer, die Ihr Passwort in den letzten 3 Monaten nicht geändert haben. Kein Problem, denn wir haben ja die Events gespeichert und wissen, wann jeder User zuletzt sein Passwort geändert hat. Hätten wir nur die einzelnen User-Attribute username, password und email in der Datenbank gespeichert, hätten wir schon hier ein Problem, da der Zeitpunkt der letzten Passwortänderung nicht aufgezeichnet wurde.

An einem anderen Tag möchte der Product Owner eine Übersicht aller Nutzer, die irgendwann mal eine E-Mail-Adresse mit der Endung @example.org angegeben hatten. Kein Problem, denn wir haben ja die komplette Historie. Mit einem klassischen Datenbankschema mit den Spalten username, password und email hätten wir auch hier ein unlösbares Problem, da die Informationen mit jeder Änderung überschrieben werden.

Vorteil: Nachvollziehbarkeit

Ein weiterer Vorteil ist, dass in einem event-gesteuerten System ganz einfach nachvollzogen werden kann, was passiert ist, damit der aktuelle Zustand eingetreten ist. Ein Audit Log ist also fest in das Konzept eingebaut.
Im Umkehrschluss kann ein eventgesteuertes System auch ganz einfach in den Zustand zu einem gegebenen Zeitpunkt wiederhergestellt werden, indem man die Events nur bis zu diesem Zeitpunkt berücksichtigt, wenn die Entitäts-Ojekte erstellt werden.

Faktisch ist das Speichern der einzelnen Events nichts Neues: Zum Beispiel in der Buchhaltung werden alle Geldein- und -ausgänge einzeln aufgezeichnet, sodass man jederzeit nachvollziehen kann, wie sich eine bestimmte Gesamtsumme ergeben hat. Auch im Bankwesen ist dies so: Es werden alle Transaktionen aufgezeichnet, die zum aktuellen Kontostand geführt haben.
Man kann aber eben nicht nur Geldtransaktionen in dieser Art abbilden sondern alle Zustandsänderungen. Ein anderes klassisches Beispiel ist ein Warenkorb. In einem eventgetriebenen System werden die einzelnen Transaktionen aufgezeichnet: Produkt 1 hinzugefügt, Produkt 2 hinzugefügt, Produkt 1 gelöscht. Und wenn man alle Events wieder abspielt, erhält man einen Warenkorb, in dem nur Produkt 2 liegt.

Der entscheidende Unterschied einer event-getriebenen Models gegenüber einem klassischen CRUD-Ansatz ist, dass man nie von Anfang an alle Funktionalitäten kennen kann, die sich in Zukunft mal jemand wünschen wird. Wenn aber jede Änderung des Systems aufgezeichnet wird und man nichts überschreibt oder gar löscht, können neue Funktionen jederzeit auch für Daten-Änderungen aus der Vergangenheit bereit gestellt werden.

Nachteile

Als erstes fällt natürlich auf, dass die Datenbank durch das Aufzeichnen aller Events wesentlich größer wird als bei einem CRUD-System. Ein wirklicher Nachteil ist das aber nicht, da die Daten ja nicht umsonst gespeichert werden, sondern ggf. früher oder später nochmal gebraucht werden.

Natürlich erkauft man sich die gewonnene Flexibilität mit der Auflösung der referenziellen Integrität in der Datenbank (denn Fremdschlüssel sind natürlich in diesem Fall nicht möglich).
Aber an dieser Stelle muss man sich überlegen, wozu eine Datenbank in einer Anwendung eigentlich dient: Sie ist ein Persistenzsystem, mit dem man Daten zwischen mehreren Aufrufen speichern und daraus Objekte wiederherstellen können soll. Welche Werte für bestimmte Datenbankfelder erlaubt sind, ist aber eine Business Rule, also Geschäfts-Logik. Demzufolge sind auch Fremdschlüssel eine Art Logik – und die hat in der Datenbank eigentlich nichts zu suchen.
Die Business Rules werden innerhalb der Anwendung umgesetzt und zwar entweder in den Setter-Methoden der Entitäts-Objekte oder über Value Objects, wobei Value Objects zu bevorzugen sind, da dadurch die Setter-Methode sich auf Ihre Single Responsibility – nämlich das Speichern des übergebenen Parameters – konzentrieren kann, während das Value Object sich um seine Single Responsibility – nämlich das Validieren eines Wertes gegenüber einer Regel – kümmern kann.

Fazit

Event-getriebene Systeme sind eine gute Alternative zum klassischen CRUD-Ansatz bei Anwendungen, bei denen sich Daten über die Zeit ändern und bei denen die Anforderungen nicht von Anfang an zu 100% feststehen bzw. bei denen neue Anforderungen später hinzukommen könnten. Für Anwendungen mit reiner Datenhaltung reicht hingegen ein CRUD-System aus.

PS: Faktisch habe ich in diesem User-Beispiel ein wenig getrickst, da ich die Datenänderungen aufgezeichnet habe und nicht die Events, die zu den Datenänderungen geführt haben. Aber dazu mehr im nächsten Beitrag…

datenbank

24.09.2019 21:23:27

Boolean-Typen in MySQL (2)

Ich hatte ja vor einiger Zeit darüber geschrieben, welcher Datentyp für boolesche Werte bzw. Werte aus einem beschränkten Wertebereich am besten geeignet ist. Dort kam ich zu dem Ergenis, dass UNSIGNED TINYINT sowie ENUM gut geeignet sind.

TINYINT ist das schnellste, bedarf aber weiterer Dokumentation, um festzuschreiben, welche Zahl als welcher Status interpretiert werden muss. ENUM dagegen ist etwas langsamer, allerdings können die Status (ja, die Mehrzahl von Status heißt Status mit langem u) dort selbstbeschreibend benannt werden, was Fehlern vorbeugen kann.

Der MySQL Performance Blog hat nun zu diesem häufigen Anwendungsfall auch einen Beitrag verfasst, den ich euch nicht vorenthalten möchte: Picking datatype for STATUS fields
Letztlich kommt der Beitrag zu recht ähnlichen Ergebnissen wie ich damals, aber trotzdem solltet ihr es euch durchlesen, um einfach mal eine andere Meinung dazu zu lesen.

datenbank

24.09.2019 06:37:25

Aufwändiges GROUPing vermeiden

Ich habe ja bereits an einigen Stellen hier im Blog geschrieben, dass man das Potential des DBMS möglichst ausschöpfen sollte und demzufolge alles dort schon berechnen, filtern und verarbeiten lässt, was das DBMS kann. In den meisten Fällen bleibe ich auch bei dieser Aussage, aber einige Fallstricke gibt es dabei. Einen möchte ich in diesem Beitrag beschreiben.

Ein Grundsatz, den ich immer wieder gern predige, ist: „SQL-Abfragen, die in Schleifen ausgeführt werden, sollten überprüft werden, ob Sie nicht bereits außerhalb der Schleife mit abgedeckt werden können.“ Oft sind die benannten Schleifen ja die while-Schleifen, die aus dem Durchlaufen der Datensätze eines Result-Sets kommen. Wenn innerhalb dieser Schleife dann in jedem Durchlauf eine Extra-Abfrage ausgeführt wird, ist das eher suboptimal. Meistens kann in die äußere Abfrage die innere (in der Schleife) mit eingebaut werden, da ja ein funktionaler Zusammenhang besteht – meistens über Fremdschlüssel, denn sonst könnte ja die innere Abfrage gar nicht für den aktuell durchlaufenen Datensatz der äußeren Abfrage ausgeführt werden. Oh Mann, ich hoffe man kann mir folgen…
Ich versuchs mal noch mit einem Beispiel zu verdeutlichen:

$rs_aussen = mysql_query("SELECT a,b FROM tabelle WHERE c=123"); while($data = mysql_fetch_assoc($rs_aussen)) {   echo $data['a']." ".$data['b'];   $rs_innen = mysql_query("SELECT x FROM tabelle2 WHERE y='".$data['a']."'");   echo mysql_result($rs_innen, 0); }

Man sieht, also, dass tabelle2.y der Fremdschlüssel von tabelle.a ist. Demzufolge könnte die innere Abfrage auch in die äußere eingebaut werden mit einem einfachen INNER JOIN:

$rs_aussen = mysql_query("SELECT a,b,x FROM tabelle INNER JOIN tabelle2 ON tabelle.a=tabelle2.y WHERE c=123"); while($data = mysql_fetch_assoc($rs_aussen)) {   echo $data['a']." ".$data['b'];   echo $data['x']; }

Bei solch einer 1:1-Relation ist das natürlich simpel. Problematischer wird es bei 1:n-Relationen – und noch schlimmer bei 1:(0..n). Denn dann entstehen durch das INNER JOIN natürlich mehrere (Pseudo-)Datensätze, die wir eigentlich gar nicht haben wollen. Dies ist oft der Fall, wenn man eine Tabelle „produkte“ sowie eine „bilder“ hat. Ein Produkt kann natürlich mehrere Bilder haben (hat aber eventuell auch gar keines). Für eine Übersichtsseite wollen wir nun aber jedes Produkt anzeigen, jedes soll aber nur maximal 1 Bild haben.
Es führt demzufolge kein Weg am GROUP BY vorbei, damit wir wirklich nur maximal 1 Bild zu jedem Produkt bekommen. Außerdem müssen wir einen LEFT JOIN einsetzen, weil sonst alle Artikel ohne Bild herausfallen würden. Unsere Abfrage sähe also so aus:

SELECT produkte.name,bilder.url  FROM produkte  LEFT JOIN bilder ON produkte.ID=bilder.produkte_ID  GROUP BY produkte.ID LIMIT 0,20

Diese Abfrage geht so natürlich nur in MySQL. Andere DBMS werfen an der Stelle einen Fehler, weil nicht alle Spalten, nach denen gruppiert werden soll in der Selektionsliste enthalten sind. Das kann man MySQL auch abgewöhnen, aber ich finds toll – erspart jede Menge Subquerys.

Und wenn man sich nun die EXPLAINs ansieht, wird es einem mitunter ganz anders. Meistens werden für das GROUP BY nämlich temporäre Tabellen angelegt und mitunter auch noch ein Filesort hinterhergeschoben (vor allem, wenn man nicht nur gruppiert sondern danach noch nach einer anderen Spalte sortiert – z.B. dem Preis). Das ist nicht unbedingt das, was man gute Performance nennt. Nun wissen wir ja, woher der Fehler kommt: Vom GROUP BY. Also haben wir doch schnell mal ins Manual geschaut. Nun wird das Problem deutlich: die Indizes können nicht fürs Gruppieren benutzt werden, weil wir eben wie bereits erwähnt, andere Spalten selektieren als nach denen wir gruppieren. Alle bei MySQL.com beschriebenen Optimierungsstrategien beziehen sich auf Anfragen, bei denen nach den Spalten gruppiert wird, die auch selektiert werden (zumindest grob gesagt – lest euch die Artikel dort aber auf jeden Fall mal durch).

Bei uns ist dies aber nicht der Fall.

Problematisch anzusehen ist dabei auch die Ausführungsreihenfolge aller DBMS: Zuerst werden die Tabellen gejoint, anschließend gruppiert und dann wird erst selektiert. Es ist allerdings anders auch nicht möglich. Das bedeutet, dass bei uns zuerst alle Bilder-Datensätze gruppiert werden (sehr aufwändig!), denn sonst könnte das LIMIT nicht garantieren, dass genau 20 herauskommen. Wenn erst selektiert und dann gruppiert würde, könnte es ja sein, dass ein Artikel mit mehreren Bildern unter den 20 ausgewählten ist. Würde danach gruppiert, blieben am Ende weniger als 20 Datensätze übrig.

Die Lösung, die ich für mich nun gefunden habe, weil ich MySQL partout nicht überreden konnte einen Index für eine ähnliche, etwas komplexere Anfrage als die obige zu verwenden, ist, dass ich die Bilder wieder in der Schleife hole. Das spart den LEFT JOIN sowie das GROUPing. Und letztlich müssen dann eben nur die Bilder für die ausgewählten 20 Artikel geholt werden. Außerdem sind die Abfragen natürlich sehr schnell, weil der Fremdschlüssel indiziert ist und ich ja noch ein LIMIT 1 anhängen kann.

Natürlich kann man auch eine Subquery benutzen, die zuerst 20 Produkte holt und zu diesen dann 0-1 Bilder holt. Auch diese Lösung funktioniert – bei mir allerdings etwas langsamer als die Bilder in der Schleife zu holen. Ich meine also so:

SELECT t.name,bilder.url FROM (   SELECT produkte.name,produkte.ID    FROM produkte    ORDER BY preis /* ORDER ist auch problemlos möglich */   LIMIT 0,20) t LEFT JOIN bilder ON t.ID=bilder.produkte_ID  GROUP BY t.ID  ORDER BY NULL /* wichtig, wenn die Reihenfolge der Subquery übernommen werden soll */

Natürlich umgehen wir so das Problem nicht, dass trotzdem der Index für das GROUP BY nicht benutzt werden kann, aber zumindest müssen nicht mehr so viele Datensätze gruppiert werden.

Man muss es eben mal durchmessen, welche Variante schneller ist. Erste Priorität sollte es immer haben, die Anfrage und die Indizes so anzupassen, dass es mit einer äußeren Abfrage unter der nutzung von Indizes schön flott geht. Gelingt das aber nicht, kann man den Weg mehrerer Abfragen zumindest mal ausprobieren. Ihr solltet dazu ein Profiling-Tool verwenden, um die Geschwindigkeiten zu messen.

datenbank

24.09.2019 06:37:26

Auslesen der zuletzt eingefügten ID (auto increment)

Recht häufig benötigt man nach einer INSERT-Operation auf eine Tabelle mit einer automatisch inkrementierten ID als Primärschlüssel den durch diese Operation erzeugten Auto-Increment-Wert. Beispielsweise dafür, um anschließend weitere Operationen mit diesem Datensatz vorzunehmen oder die ID in anderen Tabellen als Fremdschlüssel zu verwenden. Zwei unterschiedliche Wege diese ID herauszufinden sollen in diesem Beitrag verglichen werden.

Denis hat vor einiger Zeit in den Themenvorschlägen vorgeschlagen, dieses Thema einmal näher zu untersuchen und dabei folgende zwei Wege vorgeschlagen.

1. Per SQL-Abfrage
Hierbei wird nach der INSERT-Query noch eine weitere Query an MySQL abgesendet, die einen Datensatz zurückgibt, der allein die ID enthält:

mysql_query("INSERT INTO tabelle (spalte1,spalte2,...) VALUES ('wert1','wert2',...)"); $rs = mysql_query("SELECT LAST_INSERT_ID()"); $id = mysql_result($rs,0);

2. Per PHP
Eine andere Möglichkeit ist die Abfrage durch PHP erledigen zu lassen. Dies geschieht über den Befehl mysql_insert_id().

mysql_query("INSERT INTO tabelle (spalte1,spalte2,...) VALUES ('wert1','wert2',...)"); $id = mysql_insert_id();

Grundsätzlich erzeugen beide Varianten also gleiche Lösungen. Es fällt aber auf, dass man im Fall 1 zusätzlich noch das Result Set auslesen muss, um an den Wert zu gelangen.
Die Ergebnisse sehen aber folgendermaßen aus:

Datei Gesamtlaufzeit durchschnittliche Laufzeit pro Durchlauf Verhältnis zur schnellsten Variante
result_mysql_insert_id.php 6.168871 s 6.169 ms 100 %
result_last_insert_id.php 6.178885 s 6.179 ms 100 %

Es ist zu erkennen, dass beide Varianten gleich schnell sind. Hinsichtlich der Performance gibt es also keine Unterschiede.
Die Gründe kann ich nicht 100%ig verifizieren, jedoch könnte ich mir vorstellen, dass mysql_insert_id() intern exakt die gleiche SQL-Abfrage ausführt und letzten Endes bei beiden Wegen exakt dasselbe getan wird.
Ich werde weiterhin mysql_insert_id() bevorzugen, weil man dafür einfach eine Zeile weniger benötigt, aber das sei jedem selbst überlassen. Wer nach Lines of Code bezahlt wird, sollte natürlich LAST_INSERT_ID() abfragen ????

Die verwendeten Scripte und die Ergebnisse gibts natürlich zum Download.

Das war also mal ein Beitrag, der euch nicht dazu bringen wird, gleich alle Scripte zu überarbeiten. Ich hoffe aber trotzdem, dass er informativ ist und ihr euch zukünftig nicht mehr den Kopf zerbrechen müsst. ????

datenbank

24.09.2019 21:23:52

Datenbanken: Schlüssel und Indizes

Datenbanken: Schlüssel und Indizes

  1. Ziel und Zweck
  2. Das Wesen von Indizes
  3. Primärschlüssel
  4. Unique-Schlüssel
  5. Index-Schlüssel
  6. Fremdschlüssel
  7. Fulltext-Schlüssel
  8. Welche Indizes sollen nun gesetzt werden
  9. Für Fortgeschrittene: Die Reihenfolge der Spalten im Index

Ziel und Zweck

Schlüssel und Indizes (beide Wörter sind im Falle von Datenbanken gleichbedeutend) sind elementare Bestandteile einer Datenbank. Aus Geschwindigkeitsgründen aber auch aus Gründen der internen Verarbeitung durch das DBMS (Database Management System) ist es wichtig, bestimmte Spalten einer Tabelle mit Schlüsseln zu belegen. Schlüssel können stets auf eine oder mehrere Spalten einer Tabelle gelegt werden.
Als kleine Anmerkung möchte ich darauf hinweisen, dass die Mehrzahl von Index Indizes ist (es gibt keine Indexe).

Das Wesen von Indizes

Indizes werden intern vom DBMS als sortierte Listen gespeichert. Das bedeutet zweierlei: Das Eintragen eines neuen Datensatzes in eine Tabelle, auf der ein oder mehrere Indizes liegen, wird geringfügig mehr Zeit in Anspruch nehmen als ohne Indizes, da ja zu jedem Index der neue Wert in die sortierte Liste an die richtige Position eingetragen werden muss.
Im Umkehrschluss allerdings sind Indizes beim Selektieren von Daten ein enormer Geschwindigkeitsvorteil, denn ein Wert kann viel schneller in einer sortierten Liste gefunden werden. Im Abschnitt Index-Schlüssel steht dazu ein Beispiel, das verdeutlicht, wie die Beschleunigung funktioniert.
Die Anzahl von Indizes pro Tabelle ist unbegrenzt. Lediglich beim Primärschlüssel darf nur einer pro Tabelle vorhanden sein. Man muss sich aber immer überlegen, wann ein Index Sinn macht.

Primärschlüssel

Der Primärschlüssel ist kennzeichnend für einen bestimmten Datensatz. Das bedeutet, dass allein mit dem Primärschlüssel ein Datensatz eindeutig festgelegt ist und somit wiedergefunden werden kann. Pro Tabelle kann nur ein Primärschlüssel vergeben werden. In einer Tabelle Users mit den Spalten Username und E-Mail könnte man als Primärschlüssel den Usernamen wählen, denn ein Username sollte stets nur von einem User belegt sein (ansonsten würde es beim Login Probleme geben). Ebenso könnte man die E-Mail-Adresse als Primärschlüssel wählen, aber das ist unsicher, da durchaus mehrere Personen mit der gleichen E-Mail-Adresse unterwegs sein können (z.B. Familie Müller mit andi_laura_mueller@xyz.de) und da würde es Probleme geben, da ein Datensatz dadurch nicht mehr eindeutig identifizierbar ist (2 Datensätze (zum einen Andi, zum anderen Laura) unter einer E-Mail-Adresse).
Warum sollte man trotzdem eine Extra-Spalte ID hinzufügen und diese als Primärschlüssel wählen? Es gibt zwei Gründe: erstens sollte man in den seltensten Fällen so genannte ’sprechende Schlüssel‘ verwenden. Damit ist gemeint, wenn der Schlüssel bereits ein Nutzdatum darstellt, sollte er nicht als Primärschlüssel dienen. Und der zweite Grund ist die Gewohnheit, denn mit einer automatisch hochzählenden ID als Primärschlüssel kann man nichts falsch machen.
Es ist festzuhalten, dass es oft mehrere Schlüsselkandidaten gibt. Meist sollte man eine ID als Primärschlüssel wählen. Das reduziert die Arbeit für die Datenbank, weil von einem Computer mit einer Zahl grundsätzlich besser umgegangen werden kann als mit einer Zeichenkette. Ausnahmen, wo man überlegen kann, ob man einen sprechenden Primärschlüssel verwendet, sind Daten, die bereits als Zahl vorliegen. Bei der ISBN eines Buchtitels ist dies möglich, noch besser sogar bei der Fahrgestellnummer eines Autos. Aber auch in diesen Fällen schadet es keineswegs die ID als Primärschlüssel zu verwenden.
Grundsätzlich ist nur ein Primärschlüssel pro Entität bzw. Tabelle möglich.

Ein Primärschlüssel kann auch aus mehreren Spalten zusammengesetzt sein. Hat man eine Tabelle Automodell wäre der Modellname allein eventuell ein Schlüsselkandidat. Wir gehen dabei davon aus, dass kein Hersteller B den Modellnamen von Hersteller A ‚klaut‘ (wenn das nicht sogar markenrechtlich verboten ist). Wenn man aber einen Autohändler fragen würde, käme der sofort mit einem Beispiel, wo der Modellname als Primärschlüssel nicht funktioniert: Manche Hersteller entdecken nämlich alte Modellnamen wieder. Mir ist das beispielsweise bei Skoda bekannt. In den 1950er Jahren gab es bereits einen Skoda Octavia. Und dieses Modell gibt es auch heute noch oder besser wieder, allerdings mit ganz anderen inneren und äußeren Werten. Stellt man beide Autos nebeneinander, kann man schnell feststellen, dass es sich nicht um das gleiche Modell handelt. Die Datenbank aber, die nur den Modellnamen kennt, würde beide Modelle für gleich halten.
Wenn man nun als Primärschlüssel den Modellnamen und das Baujahr verknüpft, funktioniert das wieder. Dann erkennt auch die Datenbank, dass es zwei verschiedene Modelle sind. Sicherer (und in diesem Fall auch wieder performanter) ist es aber, eine automatisch hochzählende ID als Primärschlüssel zu vergeben.

Unique-Schlüssel

Unique-Schlüssel verhalten sich ähnlich wie Primärschlüssel. Sie dienen dazu, dass der Wert eines Datensatzes in der Spalte, die mit dem Unique-Schlüssel belegt ist, bei keinem anderen Datensatz vorkommen darf. In meinem Beispiel mit der Tabelle ‚Users‘ hätte ich beispielsweise auf den Usernamen einen Unique-Index gesetzt (wenn man eine ID als Primärschlüssel wählt), weil ein Username nur ein mal vorkommen darf (Stichwort Login).
Ansonsten kann auch ein Unique-Index mehrere Spalten vereinen. Das ist sogar manchmal sehr sinnvoll. Als Beispiel sei nur der Umstand aufgeführt, wenn es in einer Tabelle ‚Users‘, die neben den Spalten Username und E-Mail noch das Geburtsdatum enthält, ein Unique-Index auf E-Mail und Geburtsdatum gesetzt wird. Wenn wir davon ausgehen, dass User immer ihr wirkliches Geburtsdatum angeben, ist es somit gestattet, dass sich Personen, die eine gemeinsame E-Mail-Adresse haben, auch beide anmelden können (es sei denn sie haben am gleichen Tag Geburtstag).
Die Datenbank verhält sich so, dass sie den Versuch einen Wert in ein Feld einzutragen, der in der Tabelle schon ein Mal vorhanden ist und auf dessen Spalte ein Unique-Index liegt, einfach abwehrt. Wenn das Eintragen in PHP mittels mysql_query() versucht wird sollte man den Rückgabewert dieser Funktion auswerten (1 bzw. true bei Erfolg, 0 bzw. false bei Misserfolg) und den User darüber informieren, weshalb das Eintragen keinen Erfolg hatte, sonst weiß der nämlich nicht Bescheid und wird entnervt aufgeben.

Index-Schlüssel

Index-Schlüssel existieren alleinig zur Beschleunigung von Datenbankabfragen. Wenn sich jemand über eine langsame Datenbank ärgert, ist meistens kein Index gesetzt, der die Spalten in der WHERE-Bedingung abdeckt. Wie weiter oben erklärt speichert ein Index die Spalten-Inhalte, die zum Index gehören, mit einer Vorsortierung. Dadurch können Bedingungen und Sortierungen leichter ausgeführt werden. Am Beispiel lässt sich das leicht verdeutlichen: Angenommen die Namen im Telefonbuch wären nicht nach Städten und alphabetisch geordnet, sondern man bekäme ein Telefonbuch von ganz Deutschland und alle Namen stehen zufällig an irgendeiner Position. Wie schnell schafft man es den eigenen Namen zu finden? Und diese Zeit vergleichen wir, wenn wir ein nach Orten und in den Orten noch nach Namen sortiertes Telefonbuch bekommen. Zweitere Methode geht wohl wesentlich schneller. Und wer denkt die Zeitdifferenzen seien in diesem Beispiel extrem, der irrt. Man muss nur mal versuchen aus einer Artikeldatenbank mit 500000 Artikeln sich alle auflisten lassen mit dem Anfangsbuchstaben A. Und danach legt man einen Index auf den Artikelnamen und wiederholt das Experiment. Eine Größenordnung von 50-200-facher Beschleunigung ist keine Seltenheit.

Fremd-Schlüssel

Obwohl Sie standardmäßig von MySQL, dem führenden Open Source-DBMS, nicht unterstützt werden (nur mit dem Tabellentyp InnoDB), sollen Fremdschlüssel kurz erklärt werden.
Wenn zwei Tabellen über eine Relation verknüpft werden, geschieht dies über das Eintragen des Primärschlüssels der nicht eindeutigen Tabelle in die Partner-Tabelle (1:n, bei 1:1 genauso, da ist es egal, auf welche Seite der Primärschlüssel geschrieben wird) oder durch das Erstellen einer Zwischentabelle bei einer n:m-Beziehung.
Diese Spalten, die allein zum Zuordnen geeignet sind und nichts mit den eigentlichen Daten in der Tabelle zu tun haben, werden in einem Fremdschlüssel gespeichert. Hat man beispielsweise eine Artikeldatenbank (ID, Name, Preis) und dazugehörige Kategorien (ID, Name), fügt man der Artikel-Tabelle eine Spalte Kategorien_ID hinzu und belegt diese Spalte mit einem Fremdschlüssel, der anzeigt, auf welche Tabelle bzw. genauer auf welchen Datensatz sich die ‚Relationsspalte‘ Kategorien_ID bezieht.
Soll nun eine Kategorie gelöscht werden, der 100 Artikel zugeordnet sind, bemerkt das DBMS diese Verknüpfung. Man hat dann die Wahl zuerst alle 100 Artikel einer anderen Kategorie zuzuordnen, die Kategorie doch beizubehalten oder die Kategorie trotzdem zu löschen (dann verweist die Kategorien_ID dieser 100 Artikel allerdings ins Nichts). Fremdschlüssel sind eine schöne Funktion, um Abhängigkeiten zweier Tabellen untereinander umzusetzen.
Wie gesagt, gibt es in MySQL keine Fremd-Schlüssel. Als Empfehlung würde ich aber zumindest einen Index-Schlüssel auf die Spalte legen, die aus der Beziehung entsteht, denn oft werden viele Abfragen über das Fremdschlüssel-Attribut bedingt.

Fulltext-Schlüssel

Fulltext-Schlüssel bilden eine Ausnahme vom besagten Wesen der Schlüssel. Diese Schlüssel können nur auf Felder mit den Datentypen TEXT oder BLOB angewendet werden.
Einen Fulltext-Index benötigt man nur, wenn man mittels MATCH eine ’natürliche Ähnlichkeit‘ zweier Beiträge feststellen möchte. Wie das funktioniert soll hier vorerst nicht näher beleuchtet werden, aber so werden die häufigen ‚Ähnlichen Artikel‘ in Online-Shops und Ähnlichem dynamisch gefunden. Man übergibt der Match-Funktion einen String und sie findet in den angegebenen Spalten ähnlich lautende Artikel. Und damit das funktioniert, muss auf allen im Match-Befehl angegebenen Spalten ein FULLTEXT-Index liegen (pro MATCH-Befehl ein FULLTEXT-Index, das heißt, wenn in dem MATCH in 2 Spalten gesucht werden soll, muss 1 Fulltext-Index mit 2 Spalten angelegt werden).
Als SQL-Code sieht ein MATCH-Befehl dann so aus:

SELECT ID FROM artikel WHERE MATCH (titel, beschreibung) AGAINST ('ein grüner Wollpullover, der so kuschelig weich und warm ist')

Und ein solcher Befehl würde – vorausgesetzt man hat solche Artikel in der Artikeltabelle – dann auch ID von Artikeln mit den Titeln ‚Blauer Wollpullover‘, ‚Plüschtier-Teddy‘ (wenn in der Spalte beschreibung irgendwie etwas mit „kuschelig“ steht) usw.

Welche Indizes sollen nun gesetzt werden

Die Pflicht

Der Primärschlüssel sollte immer als erstes besetzt werden. Regeln dazu findet man im Kapitel über Primärschlüssel.
Unique-Schlüssel ergeben sich ebenfalls aus den Anforderungen. Wenn eine bestimmte E-Mail-Adresse beispielsweise in einer Newsletterdatenbank nur ein Mal enthalten sein soll, wird ein Unique-Index auf diese Tabelle gelegt.
Wenn man ein DBMS verwendet, das Fremdschlüssel unterstützt, können diese bereits anhand des ERM abgelesen werden.
Für eine ‚Ähnliche Artikel‘-Funktion wird immer ein Fulltext-Index benötigt, sonst gibt das DBMS eine Fehlermeldung aus. Wenn eine solche Funktion also geplant ist bzw. implementiert werden soll, ist dieser Index durch die beteiligten Spalten der MATCH-Funktion ebenfalls bereits vorgegeben.

Die Kür

Bei den Index-Schlüsseln muss man etwas nachdenken, denn es bringt wenig, auf jede Spalte einfach einen Index zu legen, egal ob er gebraucht wird oder nicht. Ich habe die Erfahrung gemacht, dass es für ein Webprojekt durchaus Sinn macht, für jede Funktion, die häufig ausgeführt wird, einen Index anzulegen. Ein Beispiel: Auf einer Communityseite loggen sich täglich viele User ein. Wenn wir als Abfrage im Script folgendes haben:

SELECT ID FROM users WHERE username='".$user."' AND passwort='".$pass."'

würde ich einen Index auf username und passwort legen (einen Index für beide Spalten zusammen). In einer Tabelle Personen einen Index auf die Hausnummer zu legen, macht dagegen keinen Sinn und verlangsamt das Eintragen neuer und das Updaten vorhandener Datensätze nur unnötig.
Wichtig dabei: Pro SELECT-Anweisung kann das DBMS nur einen Index auswählen. Deshalb ist ein verbundener Index im eben genannten Login-Beispiel performanter als jeweils einen Index auf username und passwort zu legen.
Für eine weitere Optimierung lege ich noch den Abschnitt zur Bedeutung der Reihenfolge der Spalten in einem Index ans Herz.

Für Fortgeschrittene: Die Reihenfolge der Spalten im Index

Wer das letzte Quäntchen aus seiner MySQL-Datenbank holen möchte, sollte sich die Reihenfolge der Spalten in den einzelnen Indizes ansehen. Die Indizes erzeugen vorsortierte Listen, das wurde bereits gesagt. Das DBMS kann den Index dann allerdings auch nur in dieser Reihenfolge abarbeiten. Am Beispiel aller Telefonnummern Deutschlands erzeugt die Sortierung nach Städten und anschließend (bei gleicher Stadt) nach Namen eine andere Liste als die Sortierung nach Namen und (bei gleichem Namen) nach der Stadt.
Daraus und aus dem Umstand, dass nur ein Index pro Abfrage genutzt werden kann, folgt, dass die Reihenfolge der Spalten im Index der der Abfrage entsprechen sollte.
Als Beispiel: Unsere Datensätze:

Name Stadt Telefonnummer
Müller Ulm 12345
Meier München 54321
Müller München 56789
Schmidt Leipzig 98765

Wir legen einen Index Name_Stadt (geben zuerst Name, dann Stadt an) an, der folgende Liste erzeugt:
(Meier,München); (Müller,München); (Müller,Ulm); (Schmidt,Leipzig)
Zum Vergleich legen wir noch einen Index Stadt_Name an. Dieser erzeugt folgende Liste: (Leipzig,Schmidt); (München,Meier); (München,Müller); (Ulm,Müller)
Im Script steht nun folgende Abfrage:

SELECT Name,Stadt FROM personen WHERE Name='Müller' AND Stadt='München'

Bei dieser Minidatenbank mag das Ergebnis nicht wirklich unterschiedlich sein, aber bei größeren Datenmengen ist der Unterschied eindeutig vorhanden. Der Index Name_Stadt würde demzufolge schnellere Ergebnisse erzielen, da zuerst eine Untermenge aller Datensätze gesucht wird, in denen der Name='Müller' ist. Der Index Stadt_Name kann hierfür nur bei der Stadt genutzt werden (das Finden aller Datensätze mit dem Namen Müller muss ohne Index erfolgen). Die Performance mit dem Index Stadt_Name ist dadurch etwas besser als ganz ohne Index, aber es ginge schneller.
Mit dem Index Name_Stadt kann dann auch sehr flott der Eintrag der Müllers aus München gefunden werden. Die Reihenfolge spielt also keine unbedeutende Rolle!
Andererseits können somit auch Indizes eingespart werden. Wenn man beispielsweise einfach alle Personen selektieren möchte, die den Namen Müller tragen:

SELECT Stadt FROM Personen WHERE Name='Müller'

In diesem Fall kann der Index Name_Stadt ebenfalls genutzt werden. Es muss also kein Extra-Index auf die Spalte Name gelegt werden.

Und natürlich wird der Index nicht nur bei der WHERE-Bedingung eingesetzt sondern auch beim Sortieren. Da das WHERE immer vor dem ORDER BY ausgeführt werden muss, würde also auch folgende Abfrage beschleunigt:

SELECT Telefonnummer FROM Personen WHERE Name='Müller' ORDER BY Stadt

Dagegen wäre der Index Name_Stadt für folgende Abfrage recht langsam sein:

SELECT Telefonnummer FROM Personen WHERE Stadt='München' ORDER BY Name

Er käme lediglich beim Sortieren zum Einsatz. Das Filtern würde recht lange dauern, falls kein anderer Index festgelegt wurde.

datenbank