Allgemeines

Verwendete Version

Alle Aussagen in diesem Artikel beziehen sich auf die aktuelle stabile Version von MySQL, wie sie in SuSE Linux 7.3 geliefert wird. Diese Version ist 3.23.44.

Die aktuelle Version des MySQL-Clients wird beim Aufruf des Kommandos mysql genannt.

kris@valiant:~> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 217 to server version: 3.23.44-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Die aktuelle Version des MySQL-Servers kann mit dem Kommando “select version()” erfragt werden.
mysql> select version() as version;
+-------------+
| version     |
+-------------+
| 3.23.44-log |
+-------------+
1 row in set (0.00 sec)

Dieselbe Information kann man auch erfahren, indem man abfragt, welche Programmpakete installiert sind. MySQL bringt die folgenden essenziellen Pakete mit:

kris@valiant:~> rpm -qa | grep mysql
mysql-devel-3.23.44-5
mysql-shared-3.23.44-5
mysql-3.23.44-5
mysql-client-3.23.44-5

Das Paket mysql-shared enthält dabei die Client-Bibliothek libmysql.so, die von MySQL-Anwendungen wie etwa PHP, aber auch von allen anderen MySQL-Komponenten benötigt wird. Das Paket mysql-client enthält alle notwendigen Programme und Hilfsdateien um einen Datenbankserver zu connecten, das Paket mysql den Datenbank-Server sowie die Servertools. Will man Programme übersetzen, die mysql-shared verwenden, so sind die notwendigen Include-Dateien in mysql-devel zu finden. Ein fünftes Paket, mysql-bench enthält eine Reihe von Benchmark- und Testprogrammen, die so gut wie niemals benötigt werden.

Einige Programme aus den Paketen setzen außerdem ein installiertes Perl (perl.rpm) und das Suse Paket perl-DBI.rpm (Perl-Paket DBI) sowie perl-Msql-Mysql-modules.rpm (Perl Paket DBD::mysql)voraus.

MySQL online

Die Website von MySQL ist http://www.mysql.com/ . Dort findet sich neben den aktuellen Downloads auch ein sehr gutes Online-Manual http://www.mysql.com/doc/ . Anwender von Suse Linux 7.x sollten sich nicht die Downloads von mysql.com antun, sondern die SuSE-Pakete von ihren CD-ROMs oder aus dem Suse Update-Download verwenden. Weitere Informationen zu MySQL finden sich in der FAQ von de.comp.lang.php sowie in der FAQ von de.comp.datenbanken.mysql.

Bücher

Es gibt eine Reihe Bücher zu MySQL und zu Datenbanken allgemein. Eine Liste von frei verfügbaren Büchern findet sich in der ersten Frage zu MySQL in der dclp-FAQ (sql-lernen ).

Ein sehr empfehlenswertes Buch über MySQL ist das Buch von Paul Dubois (in englischer Sprache).

Ein sehr empfehlenswertes Buch über relationale Datenbanken ist das Buch von Andreas Heuer .

Ein sehr empfehlenswertes Buch über Datenbanken und Optimierung ist das Buch von Mark Gurry . Obwohl es für Oracle geschrieben ist, lässt sich die Diskussion dort weitgehend auf alle relationalen Systeme übertragen und ist generell sehr erhellend.

MySQL im System administrieren

Allgemeines

MySQL besteht aus einem Serverprozeß, der eine Reihe von Threads startet und der von Clientprogrammen kontaktiert wird, die Kommandos an den Server senden und Resultate von dort erhalten.

Das Startprogramm ist safe_mysqld, das wiederum den eigentlichen Server mysqld startet. Der Server erzeugt per Default drei Threads, die in Linux als separate Einträge in der Prozeßtabelle sichtbar werden. Jeder Connect an den Server erzeugt einen weiteren Thread, der in Linux ebenfalls als Eintrag in der Prozeßtabelle sichtbar wird.

Alle Programme verstehen die Kommandozeilenoptionen –h (--host, default: Localhost) zur Angabe des Serverrechners, -P (--port, default: 3306) zur Angabe des TCP/IP-Ports, -u (--user, default: Der Unix-Loginname) zur Angabe eines Benutzernamens und –p (--password) zur Angabe eines Passwortes.

Konfiguration

Alle Programme aus dem MySQL-Paket lesen Konfigurationsparameter aus der systemweiten Steuerdatei /etc/my.cnf. Die Datei ist aufgebaut wie eine win.ini bzw. smb.conf und enthält Abschnitte, die die jeweilige Systemkomponente bezeichnen, für die die Konfigurationsanweisungen gelten. Der MySQL-Server wird im Abschnitt [mysqld] konfiguriert. Anwender können für Client-Programme die zentralen Einstellungen überschreiben, indem sie in ihrem $HOME eine Datei .my.cnf anlegen (für den Server ist es wenig sinnvoll, dort Einträge zu machen).

Start und Stop, mysqladmin

MySQL wird mit einem Programm mysqladmin geliefert, das den Serverprozeß steuern kann. Es versteht die üblichen MySQL-Optionen. Mit dem Kommando mysqladmin shutdown kann der Server kontrolliert heruntergefahren werden.

Der Status des Servers kann mit den Kommandos mysqladmin status oder mysqladmin processlist überprüft werden. Einzelne Queries, die Amok laufen, können ohne einen vollständigen Stop des Servers mit mysqladmin kill angehalten werden. Die notwendigen PIDs können der Ausgabe von processlist entnommen werden.

In SuSE Linux ist es besser, den Server mit dem Start/Stop-Script von SuSE Linux anzuhalten und starten. Dieses Script befindet sich an der üblichen Stelle /etc/init.d/mysql und versteht die üblichen Parameter start und stop.

Mit mysqladmin variables können im übrigen Konfigurationsvariablen des Servers abgefragt werden, so wie sie in der /etc/my.cnf hinterlegt wurden.

Datenspeicher im Dateisystem

MySQL legt die Daten der Datenbank als Dateien in Verzeichnissen im normalen Dateisystem ab. Die Ablage erfolgt unterhalb des Verzeichnisses, das durch die Variable „datadir“ bezeichnet wird. valiant:~ # mysqladmin variables| grep datadir | datadir | /var/lib/mysql/ | Der physikalische Datenbankserver kann dabei eine Reihe von logischen Datenbanken hosten, die jeweils auf Verzeichnisse gleichen Namens unterhalb von „datadir“ abgebildet werden. valiant:/var/lib/mysql # find . -type d . ./test ./sync ./test_skoda ./mysql ./phpgroupware ./test_test ./test_sync valiant:/var/lib/mysql # mysqlshow +————–+ | Databases | +————–+ | mysql | | phpgroupware | | sync | | test | | test_skoda | | test_sync | | test_test | +————–+ Jede Tabelle in einer logischen Datenbank findet sich in Form von drei Dateien in dem entsprechenden Verzeichnis wieder. Die Tabellen heißen jeweils so wie die Tabelle und kommen mit einer von drei Endungen: • .frm: Dies ist die Schemadefinition, also die Festlegung, welche Spalten in der Tabelle vorkommen, wie diese heißen und welche Eigenschaften sie haben. Diese Dateien sind in der Regel so um die 8 KB groß und haben eine relativ statische Größe. • .MYD: Dies ist die eigentliche Datendatei für die Tabelle. Sie wächst in Abhängigkeit davon, wieviele Zeilen die Tabelle hat. • .MYI: Dies ist der Speicher für die Indices, die auf einer Tabelle definiert sind. Sie ist um so größer, je mehr Zeilen die Tabelle hat und je mehr Indices auf einer Tabelle definiert sind. Alle Dateien und Verzeichnisse müssen demselben User gehören, unter dem auch der Datenbankserver ausgeführt wird. In Suse Linux 7.x ist das der user „mysql“ und die Gruppe „daemon“. Die Verzeichnisse, die den logischen Datenbanken entsprechen, sind in der Regel nur für den Datenbankuser zugänglich („mysql“, 0700), alle Dateien sind in der Regel für den Datenbankuser und seine Gruppe zugänglich („mysql:daemon“, 0660). Die Datenbankdateien und alle anderen Dateien in „datadir“ sollen nicht gelesen oder geschrieben werden, solange der Datenbankserver „mysqld“ läuft. Dies ist insbesondere für die Datensicherung wichtig: Da der Server Daten unter Umständen im Cache hält, ist die Sicherung dieser Dateien mit großer Wahrscheinlichkeit inkonsistent und führt nicht zu einem funktionierenden Restore. Stattdessen sind andere Methoden der Sicherung zu wählen. Logs MySQL legt eine Reihe von Logbüchern an. Die Logs liegen normalerweise in „datadir“: Log file Description The error log Problems encountering starting, running or stopping mysqld. The isam log Logs all changes to the ISAM tables. Used only for debugging the isam code. The query log Established connections and executed queries. (–log=) The update log Deprecated: Stores all statements that changes data (–log-update=) The binary log Stores all statements that changes something. Used also for replication (–log-bin=) The slow log Stores all queries that took more than long_query_time to execute or didn’t use indexes. (–log-slow-queries=) Mit dem Kommando „flush logs“ werden die Logbücher geschlossen und neu geöffnet (wichtig für einen Logrotate). Datensicherung Siehe auch http://www.mysql.com/doc/D/i/Disaster_Prevention.html . mysqldump Je nach Wunsch kann die Sicherung von MySQL als SQL-Quelltext (mysqldump) oder in Binärformat (mysqlhotcopy) erfolgen. In jedem Fall wird eine Kopie der Daten in der Datenbank erzeugt. Während der Erzeugung der Kopie sind die betreffenden Tabellen gelockt und die Puffer geflushed, sodaß sichergestellt wird, daß die Kopie konsistent ist. $ mysqldump –opt –c database | gzip –9 > database.sql.gz Dies erzeugt ein Backup der Datenbank database mit allen Tabellendefinitionen und allen Daten. Das Backup ist eine SQL-Kommandodatei, die mit dem Kommandozeilenclient wieder eingelesen werden kann. $ gzip –dc database.sql.gz | mysql database Da die ASCII-Form von SQL hoch redundant ist, ist eine Kompression des Backups mit gzip dringend angeraten. Die ASCII-Form hat jedoch den Vorteil, daß sie mit einem Editor bearbeitet werden kann, sodaß auch ein partieller Restore möglich ist. Oft möchte man außerdem mit $ mysqldump –opt –no-data database > database.schema das Datenbankschema ohne Daten getrennt sichern. mysqlhotcopy Die Sicherung mit „mysqlhotcopy“ setzt perl und korrekt installierte Perlmodule voraus. Die Syntax ist denkbar einfach: $ mysqlhotcopy database /var/tmp/database erzeugt eine Sicherung der Datenbankfiles mit konsistentem Zustand in /var/tmp/database. Diese Kopie der Datenbank kann dann auf Band oder CD-ROM gesichert werden. Anders als die Sicherung mit „mysqldump“ handelt es sich hier um eine Binärkopie der Datenbank, die weitaus weniger Platz verbraucht, schneller zu restaurieren ist, aber nicht editiert werden kann. Verlorenes Root-Paßwort recovern Siehe auch http://www.mysql.com/doc/R/e/Resetting_permissions.html MySQL speichert seine internen Daten in der logischen Datenbank mysql. Hat man diese Datenbank zerstört oder bei Spielereien mit Zugriffsrechten die Paßworte des Datenbankadministrators überschrieben, kann man nicht mehr auf den Datenbankserver zugreifen. Der Server läßt sich wie folgt recovern: Die Datenbank mysql existiert noch

1. Herunterfahren des Servers mit „/etc/init.d/mysql stop“.
2. Einsetzen der Option „skip-grant-tables“ in der /etc/my.cnf.
3. Hochfahren des Servers mit „/etc/init.d/mysql stop“
4. Die Option „skip-grant-tables“ bewirkt nun, daß beim Connect gar keine Paßworte mehr abgefragt werden. Mit den Kommandos a. use mysql; b. update user set password=password(„…“) where user=”root” kann ein neues Paßwort für den User root gesetzt werden. Statt derr „..“ ist dabei das Kennwort einzusetzen.
5. Der Server kann nun heruntergefahren werden (siehe Schritt 1) und die Option „skip-grant-tables“ wieder aus der /etc/my.cnf entfernt werden. Danach kann der Server wieder normal gestartet werden. Die Datenbank mysql existiert nicht mehr oder ist beschädigt Wenn die Datenbank mysql so schwer beschädigt wurde, daß sie nicht mehr brauchbar ist, muß sie neu angelegt werden. Dabei gehen sämtliche Benutzerlogins und ihre Zugriffsrechte und Kennworte verloren und müssen neu angelegt werden. Die Daten in den anderen Datenbanken bleiben erhalten.
6. Herunterfahren des Servers mit „/etc/init.d/mysql stop“
7. Aufruf von mysql_install_db
8. Neues Setzen des root-Paßwortes wie in der Ausgabe von mysql_install_db beschrieben.
9. Sicherheitshalber Stoppen und Starten des Servers, Testen des Zugangs. Datenbankadministration Anlegen von Usern Siehe auch http://www.mysql.com/doc/U/s/User_Account_Management.html Benutzer werden in MySQL mit dem Kommando „grant“ angelegt und mit dem Kommando „revoke“ gelöscht. Diese Kommandos können auch verwendet werden, um Benutzern mehr Rechte zu geben oder ihnen Rechte zu entziehen. In älteren Versionen von MySQL wurde derselbe Effekt durch direktes Bearbeiten von Tabellen in der Datenbank mysql erzielt. Dies ist nicht mehr empfohlen und sollte vermieden werden. Es ist wichtig zu wissen, daß Usernamen in MySQL nichts mit Usernamen in Unix zu tun haben. Es ist möglich, in MySQL User einzurichten, die es in Unix nicht gibt und umgekehrt. Auch konzeptuell besteht ein Unterschied: Usernamen in MySQL haben immer die Form „username@host“, wobei jedoch für den Hostpart Jokerzeichen zugelassen sind und der Username-Part leer sein darf. Ebenso sind MySQL-Zugangspaßworte von Unix-Zugangspaßworten verschieden. Sie werden auch anders verschlüsselt, wenn man in der Tabelle mysql.user direkt nachsieht (Funktion password() für MySQL-Paßworte, Funktion encrypt() für Unix-Paßworte). Werden User mit den Kommandos „grant“ und „revoke“ bearbeitet, liest der Server die geänderten Zugriffsrechte sofort neu ein. Spielt man manuell an den Tabellen herum, hat dies keine Auswirkungen, bis das Kommando „flush privileges“ ausgeführt wird (Oder „mysqladmin reload“ von der Kommandozeile gestartet wird). Einige Beispiele: • grant all on sync.* for beispieluser identified by ‘password’ Dies legt einen Benutzer „beispieluser“ an, der sich mit dem Paßwort ‚password’ anmelden kann. Der User hat alle Zugriffsrechte für die Datenbank sync mit allen darin befindlichen Tabellen, kann diese Rechte jedoch nicht weitergeben. • grant all on sync.* for logical_dba identified by ‚geheim’ with grant option Dies legt einen Benutzer “logical_dba” an, der sich mit dem Paßwort “geheim” anmelden kann. Der Benutzer hat nicht nur alle Zugriffsrechte für die Datenbank sync mit allen darin befindlichen Tabellen, er kann diese Rechte auch an existierende User weitergeben. Der User ist also der Datenbankadministrator für eine logische Datenbank. • grant all on . for admin identified by ‘master’ with grant option Dies legt einen Datenbankadministrator admin an, der sich mit dem Paßwort “master” anmelden kann. Er hat alle Rechte auf allen Tabellen. • grant select on sync.log for „logview@localhost“ Dies legt einen user logview an, der sich nur von localhost anmelden kann, dies dafür aber ohne Paßwort tun kann. Der User kann nur auf die Tabelle sync.log zugreifen. Das Löschen von Usern geht analog mit „revoke“. User werden in der Tabelle mysql.user gehalten. Dort vergebene Rechte gelten global (für alle Datenbanken). Den Usern werden in der Tabelle mysql.db Datenbanken und Datenbankrechte zugewiesen. Diese Rechte gelten nur für eine Datenbank. Normale User haben in mysql.user nur „n“-Flags stehen und bekommen ihre Rechte dann immer über die mysql.db-Tabelle. Admins haben Rechte an allen Datenbanken und haben daher an den passenden Stellen in mysql.user „y“-Flags stehen. Anlegen, Löschen und Anzeigen von Datenbanken Eine logische Datenbank kann mit dem Kommando „create database …“ angelegt werden. Dies bewirkt im wesentlichen, daß das Verzeichnis in „datadir“ angelegt wird. (http://www.mysql.com/doc/C/R/CREATE_DATABASE.html ) Die Datenbank kann mit dem Kommando „drop database“ gelöscht werden. Das löscht nicht nur die Datenbank, sondern auch alle Tabellendaten und Tabellendefinitionen. Eine Rückfrage gibt es nicht, ein Undo gibt es nicht. (http://www.mysql.com/doc/D/R/DROP_DATABASE.html ) Eine Liste aller existierenden logischen Datenbanken bekommt man mit „show databases“. (http://www.mysql.com/doc/S/H/SHOW_DATABASE_INFO.html ) Alle folgenden SQL-Kommando wirken immer auf die aktuelle Datenbank. Diese wird im Kommandozeilenclient mit „use …“ festgelegt. „use …“ wirkt also wie ein chdir/cd-Kommando. (http://www.mysql.com/doc/U/S/USE.html ) Anlegen, Löschen und Anzeigen von Tabellen Siehe auch http://www.mysql.com/doc/C/o/Column_types.html und Eine Tabelle kann mit dem Kommando „create table …“ angelegt werden. Dabei müssen auch die Spaltennamen der Tabelle, ihre Typen und weitere Optionen angegeben werden (http://www.mysql.com/doc/C/R/CREATE_TABLE.html) . Die Tabelle kann mit dem Kommando „drop table …“ wieder gelöscht werden (http://www.mysql.com/doc/D/R/DROP_TABLE.html) . Der Aufbau der Tabelle kann mit dem Kommando „show columns from …“ oder kürzer „describe“ oder „desc“ angezeigt werden (http://www.mysql.com/doc/D/E/DESCRIBE.html) . Informativer ist jedoch „show create table …“. Dieses Kommando gibt das vollständige SQL-Kommando zum Anlegen der Tabelle aus. phpmyadmin Ein bequemes Tool zum Anlegen, Löschen und Bearbeiten von MySQL über das Web ist phpmyadmin (http://phpmyadmin.sourceforge.net/) . phpmyadmin ist ein sehr mächtiges Tool. Es muß auf jeden Fall in einem Ordner installiert werden, der durch eine .htaccess-Datei geschützt ist. Es ist ein beliebter Sport, mit Google oder einer anderen Suchmaschine nach offenen phpmyadmin-Installationen zu suchen. Relationales Modell Das relationale Datenbankmodell betrachtet Tabellen als Mengen (ohne Reihenfolge) von Zeilen. Eine Zeile wird als Tupel von Werten betrachtet, wobei das leicht vereinfacht dargestellt ist (Tupel haben Reihenfolge, aber Zeilen in Datenbanktabellen haben ebenfalls keine Reihenfolge, auf die man sich verlassen sollte). Das relationale Datenbankmodell definiert dann eine Reihe von Operationen, die man auf diesen Tabellen-Mengen durchführen kann und die jeweils neue Mengen liefern. Ziel ist es nun, eine Anfrage zu finden, die genau die gewünschte Ergebnismenge findet. Dies kann durch die Verknüpfung verschiedener Operationen erreicht werden, da die Operatioen jeweils wieder zu den Operatoren kompatible Ergebnismengen liefern (-> Algebra). Operationen im relationalen Datenbankmodell Projektion Die Projektion erzeugt aus den vorhandenen Spalten der verwendeten Tabellen neue Spalten. Dies kann durch Auswahl einer Teilmenge von Spalten geschehen, oder durch Erzeugung von neuen Spalten mit Hilfe von Rechenfunktionen und –operatoren. In SQL wird die Projektion als Liste von Spaltennamen und Funktionsanwendungen hinter dem SELECT-Statement geschrieben: select t1.s1, t1.s2, t2.s1, unix_timestamp(t2.s3) from t1, t2 Merke: Projektion == senkrechter Schnitt, Spaltenauswahl Selektion Die Selektion erzeugt aus den vorhandenen Zeilen der verwendeten Tabellen neue Zeilen. Dies kann durch Auswahl einer Teilmenge von Spalten geschehen. Die Auswahl kann durch Prädikate und Relationen erfolgen. In SQL wird die Selektion als Verknüpfung von Prädikaten und Relationen in der WHERE-Clause des SELECT-Statements geschrieben. select * from t1 where t1.s1 <10 and not isnull(t1.s2) Merke: Selektion == waagrechter Schnitt, Zeilenauswahl Join Der Join ist eine Verknüpfung von zwei Tabellen durch Bildung des Kreuzproduktes und Auswahl der gewünschten Teilmenge der Kombinationstabelle. Ein unqualifizierter Join erzeugt eine vollständige Kombination zweier Tabellen, das Kreuzprodukt: jede Zeile der Tabelle 1 wird mit jeder Zeile aus Tabelle 2 kombiniert, die Resultatstabelle hat count(t1) * count(t2) Zeilen. In der Regel möchte man nur eine Teilmenge des Kreuzproduktes haben, in dem man den Schlüssel von t1 mit dem Fremdschlüssel von t1 in t2 kombiniert (siehe weiter unten). select * from t1, t2 where t1.pk = t2.fk_t1 Merke : Join == Verknüpfung zweier Tabellen Rename Will man rekursive Strukturen (Bäume, Listen) erzeugen, muß man eine Tabelle gelegentlich mit sich selbst Joinen. Damit das gelingt, muß man die Elterntabelle von der Kindtabelle unterscheiden können. Dies macht eine Umbenennung notwendig. In SQL kann man Spalten und Tabellen umbenennen, indem man den neuen Namen mit AS in der Projektion oder Selektion nennt. select l.s1+l.s2 as spaltensumme from lange_tabelle as l Renames sind auch nützlich, um Rechenergebnisse (synthetische Spalten) sinnvoll zu benennen (hier: spaltensumme). Aggregation Siehe auch http://www.koehntopp.de/php/databases.html - sql-aggregation. In SQL kann man eine Reihe von Zeilen mit gleichen Eigenschaften zusammenfassen und bekommt so Zeilen, die Mengen sind. Man kann sich einen Repräsentanten dieser Mengen anzeigen lassen oder bestimmte Elemente mit Aggregatfunktionen auswählen. select max(preis) as regionaler_hoechstpreis from preise group by region Normalisierungen Wenn man eine Datenbankanwendung als Laie entwickelt, stellt sich schnell die Frage, wie man seine Daten auf verschiedene Tabellen verteilt. Man merkt schnell, dass sich bestimmte Strukturen für bestimmte Operationen als nicht sehr praktisch erweisen (siehe auch http://www.tu-chemnitz.de/wirtschaft/wi1/lehre/2001_ws/db/v6.pdf) . Ideal ist eine Speicherung der Daten ohne Redundanzen. Man gewinnt ein solches Tabellenschema durch Normalisierung. • 0 NF: Nach menschlichen Ordnungskriterien geordnete Daten, zum Beispiel Herrchen und Haustiere. • 1 NF: Ein Feld darf nur einen einzigen Wert beinhalten, d.h. ich soll nicht alle Haustiere eines Herrchens in eine Spalte quetschen und ich soll nicht Straße und Hausnummer in einer Spalte speichern. • 2 NF: Alle Werte müssen vom Primärschlüssel funktional abhängig sein, d.h. ich sollte nicht Informationen über Haustiere und deren Herrchen in ein und derselben Tabelle unterbringen. ◦ Was sind Schlüssel? ◦ Was sind Primärschlüssel? ◦ Was sind Fremdschlüssel? • 3 NF: Alle Werte müssen von einander funktional abhängig sein, d.h. ich sollte nicht die verschiedenen Wohnsitze des Herrchens mit seinen Geburtsmerkmalen in einer Tabelle unterbringen. Tabellenbeziehungen Nach einer Normalisierung hat man in der Regel eine große Anzahl von Tabellen, die zusammengefügt werden müssen, damit man wieder an die Daten kommt. Das Zusammenfügen erfolgt mit einem passenden Join, die Join-Bedingung bezeichnet in der Regel den Primärschlüssel einer Tabelle und den Fremdschlüssel dieser Tabelle in einer anderen Tabelle. Je nachdem, welche Art von Beziehung zwischen zwei Tabellen besteht, sieht der Join auch leicht anders aus. One-to-Many Relation Die häufigste Beziehung zwischen Tabellen ist die One-to-Many Relation, etwa “Eine Order enthält viele Items” oder “Ein Manager hat viele Angestellte”. Sie kann direkt auf Tabellen in einer Datenbank abgebildet werden. In einer One-to-Many Relation gibt es eine aufspannende Tabelle mit einem Primärschlüssel (z.B. die Ordertabelle oder die Managertabelle) und eine aufgespannte Tabelle. Die aufgespannte Tabelle hat eine Spalte, in der der Primärschlüssel der Zeile der aufspannenden Tabelle eingetragen ist, zu der diese Zeile gehört. OID Datum

1 11.01.2002

2 12.01.2002

3 12.01.2002

IID OID Bezeichnung

1 1 Keks

2 1 Noch ein Keks

3 2 Kein Keks

Man kann erkennen, dass jedes Item in der Itemtabelle genau einer Order zugeordnet ist. Eine Order kann keine Items, genau ein Item oder mehrere Items enthalten. One-to-One Relation Eine One-to-One Relation tritt in Datenbanken eigentlich nur in zwei Situationen auf: • Die rechte Seite der Relation ist optional (One-to-Zero Relation) und nur relativ selten mit Werten gefüllt. ◦ Ein Sonderfall dieser Situation liegt vor, wenn man eine Reihe von Spezialisierungen eines Objektes modellieren möchte. • Es muß eine Key Translation durchgeführt werden. Ein Beispiel für den ersten Fall wäre eine Liste von Personenbeschreibungen. Für einige Personen existieren erweiterte Informationen, zum Beispiel Fotos und Homepage-URLs. Der genannte Sonderfall existiert in unserer Kundendatenbank, wo es zum Beispiel technische Objekte gibt. Diese Tabelle hätte eine ID und ein Typfeld sowie alle Informationen, die allen technischen Objekten gemein sind. Je nach Typfeld findet man dann die speziellen Informationen des technisches Objektes in einer anderen Tabelle, zum Beispiel beim Typ „TO_Web“ in der Tabelle TO_Web. Eine Key Translation ergibt sich dann, wenn man zwei unterschiedliche Systeme mit überschneidendem Datenbestand zusammenführen muß, zum Beispiel Kunden mit XAL-Kundennummern in der Warenwirtschaft mit Kunden mit KundenDB-Kundennummern in der technischen Verwaltung. Hier ist dann manchmal eine Forderung, daß es sich tatsächlich um eine One-to-One Relation handelt, also der rechte Teil der Relation niemals optional ist – normalerweise könnte man diese Tabellen sonst in einer einzelnen Tabelle zusammenführen, aber hier ist dies aus technischen Gründen nicht machbar. Many-to-Many Relation Eine Many-to-Many Relation (n:m Relation) liegt dann vor, wenn zwei Objektmengen frei miteinander verknüpft werden sollen, etwa Firmen und Personen oder Seminare und Teilnehmer einander zugeordnet werden sollen. In Tabellen ist eine Many-to-Many Relation nicht direkt darstellbar. Man braucht eine Hilfstabelle, die diese Zuordnung übernimmt. Diese Hilfstabelle hat manchmal keine eigenen Attribute und keinen PK, wenn sie eine reine Hilfstabelle ist. SemID Titel

1 Kekse backen

2 Holz hacken

TeilnID Name

1 Hans Hartmacher

2 Willi Weichspüler

3 Tanja Troll

SemID TeilnID

1 2

1 3

2 2

2 3

Im Beispiel hat die Hilfstabelle zur Verknüpfung von Seminaren mit Teilnehmern keine anderen Attribute als Fremdschlüssel von Seminar und Teilnehmer. Sie bekommt per Konvention dann auch keinen eigenen Namen, sondern den systematischen Namen sem_teiln_rel. Hätte die Tabelle weitere Attribute, dann muß man sich überlegen, welche Funktion die Tabelle im System wahrnimmt. Sie bekommt dann einen eigenen Namen und einen eigenen Primärschlüssel. Im Beispiel würde die Tabelle Buchungen von Seminaren speichern und eine Buchung könnte Attribute wie „Anreise am Vortag“ oder „Abreise am Folgetag“ haben. Wir würdne die Tabelle entsprechend Buchungen nennen und ihr eine Spalte BuchID geben. Arten von Joins Tabellen werden mit Hilfe der Join-Operation miteinander verknüpft. Je nachdem, welcher Art die Beziehung zwischen zwei Tabellen ist, können die Join-Operationen unterschiedlich sein. Equijoin Der Equijoin, straight join oder symmetric join ist die einfachste Verknüpfung zweier Tabellen miteinander. select o.datum, i.bezeichnung from order as o, item as i where o.oid = i.oid Dieser Join verknüpft die Order- und Item-Tabellen aus dem 1:n-Beispiel miteinander. Ausgegeben werden alle Orders mit den dazu gehörenden Items, sofern eine Order überhaupt Items hat. Left Join Der Left Join, Outer Join verknüpft ebenfalls zwei Tabellen miteinander, ist jedoch asymmetrisch (Ein Right Join ist identisch mit einem Left Join, bei dem beide Tabellen miteinander vertauscht werden und soll hier nicht weiter betrachtet werden). Das bedeutet, daß in jedem Fall alle Werte der linken Tabelle aufgeführt werden, an Stelle der rechten Tabelle jedoch an einigen Stellen Nullwerte auftauchen können, wenn dort keine Werte vorhanden sind. select o.datum, i.bezeichnung from order as o left join item as i on o.oid = i.oid Die linke Tabelle wird als aufspannender Tabelle, die rechte Tabelle als aufgespannte Tabelle bezeichnet. Self Join Um selbstreferentielle Strukturen erzeugen zu können braucht man einen Self-Join. Das ist ein Equijoin oder Left Join einer Tabelle mit sich selbst. Da hierbei der Name einer Tabelle zweimal auftaucht, muß mindestens ein Vorkommen der Tabelle mit dem as-Operator umbenannt werden, damit man beide Vorkommen unterscheiden kann. Selbstreferentielle Strukturen sind Bäume oder Listen. Sie treten immer dann auf, wenn ein List-of-Parts-Problem vorliegt, manchmal auch, wenn man einen Subselect vermeiden möchte. select v.name, m.name from mitarbeiter as v, mitarbeiter as m where m.manager = v.mid Self-Joins beliebiger Tiefe (echte Bäume) werden schnell unübersichtlich. Eine alternative Methode zur Baumgenerierung ist in http://www.koehntopp.de/kris/artikel/sql-self-references/ , http://www.koehntopp.de/tree.phps , http://www.develnet.org/tech/tutorials/3.1.html beschrieben. Multiple Joins In komplizierten Szenarien kann es vorkommen, mehr als zwei Tabellen gleichzeitig zusammenführen zu müssen. Ein Beispiel ist ein Seminarmanagementsystem, in dem Seminare, Seminartermine, Teilnehmer und Kostenstellenverantwortliche miteinander in einer Bestelltabelle verknüpft werden müssen: Um eine Bestellung ausgeben zu können, muß über die Termin-ID einer Bestellung die Seminar-ID eines Termins gesucht werden. select s.titel, t.beginn from seminar as s, termin as t, bestellung as b where s.sid = t.sid and t.tid = b.tid Die Joins werden hier einfach hintereinander geschrieben, und für je zwei miteinander zu verbindende Tabellen wird eine Bedingung in die WHERE-Clause mit eingeführt. Im Einsatz wird dann meist noch mindestens eine weitere Bedingung eingeführt, die nicht zu den Joins gehört, sondern eine Einschränkung auf dem zusammengeführten Bestand ist. Dies kann zum Beispiel eine Teilnehmer-ID oder eine Seminar-ID sein. In der WHERE-Clause befinden sich also Join-Bedingungen und Selektionsbedingungen. Multiple Left Joins In manchen Fällen will man mehrere Left Joins miteinander verbinden. Dabei kann es zur Bildung von Sternen oder Kaskaden kommen. Ein Stern liegt vor, wenn zwei abhängige Tabellen von derselben aufspannenden Tabelle abhängen. Eine Kaskade liegt vor, wenn eine aufspannende Tabelle eine abhängige Tabelle aufspannt, die wiederum eine weitere abhängige Tabelle aufspannt. select * from kunde as k left join ord as o on k.kid = o.kid left join x on k.kid = x.kid +—–+——-+——+————+——+——+——+ | kid | name | oid | datum | kid | xid | kid | +—–+——-+——+————+——+——+——+ | 1 | Hans | 2 | 2002-03-03 | 1 | 1 | 1 | | 2 | Franz | 1 | 2002-03-04 | 2 | NULL | NULL | | 2 | Franz | 3 | 2002-03-02 | 2 | NULL | NULL | | 3 | Otto | NULL | NULL | NULL | 2 | 3 | | 3 | Otto | NULL | NULL | NULL | 3 | 3 | +—–+——-+——+————+——+——+——+ 5 rows in set (0.00 sec) select * from kunde as k left join ord as o on k.kid = o.kid left join item as i on o.oid = i.oid +—–+——-+——+————+——+——+——+—————+ | kid | name | oid | datum | kid | iid | oid | beschreibung | +—–+——-+——+————+——+——+——+—————+ | 1 | Hans | 2 | 2002-03-03 | 1 | 3 | 2 | Kein Keks | | 2 | Franz | 1 | 2002-03-04 | 2 | 1 | 1 | Keks | | 2 | Franz | 1 | 2002-03-04 | 2 | 2 | 1 | Noch ein Keks | | 2 | Franz | 3 | 2002-03-02 | 2 | NULL | NULL | NULL | | 3 | Otto | NULL | NULL | NULL | NULL | NULL | NULL | +—–+——-+——+————+——+——+——+—————+ 5 rows in set (0.02 sec) Einige Konventionen für das Tabellendesign Wir haben im Haus einige Regeln für Tabellen, die das Tabellendesign ein wenig vereinfachen sollen und den Mitarbeitern leichteres Arbeiten ermöglichen sollen: • Jede Tabelle hat einen sprechenden Namen. Der Name ist, wenn möglich, singularform. Zu der Langform des Namens gibt es außerdem eine einheitliche und eindeutige Kurzform mit einem, zwei oder drei Buchstaben. Beispielweise heißt eine Tabelle „kunde“, nicht „kunden“. Ihr Kurzname ist „k“. • Jede Tabelle hat einen Primärschlüssel. Die Primärschlüsselspalte ist immer die am weitesten links stehende Spalte einer Tabelle. Der Name des Primärschlüssels ist der Kurzname der Tabelle mit dem angehängten Kürzel „id“ (kein Unterstrich). • Tritt der Primärschlüssel einer Tabelle in einer anderen Tabelle als Fremdschlüssel auf, so heißt er dort genauso wie der Primärschlüssel. Der Fremdschlüssel in die Tabelle kunde in der Tabelle bestellung heißt also bestellung.kid. • Jede Tabelle hat eine Spalte mit dem Namen changed vom Typ timestamp. Dies ist die am weitesten rechts stehende Spalte dieser Tabelle. • Tabellen, die nur n:m-Beziehungen realisieren, ohne eigene Attribute zu haben, haben keinen Primärschlüssel und unter Umständen auch keine changed-Spalte. Sie haben einen systematischen Namen, der sich aus den Kurznamen der involvierten Tabellen und dem Kürzel „_rel“ zusammensetzt. Beispiel: Eine Bestelltabelle ohne eigene Attribute kann s_t_rel heißen. Sobald eine solche Tabelle weitere Spalten bekommt, die über Fremdschlüssel von anderen Tabellen hinausgehen, muß sie einen eigenen PK und eine changed-Spalte bekommen sowie einen sprechenden Namen erhalten. Indices Ein Index ist eine geordnete Teilkopie des Datenbestandes einer Tabelle. Dadurch, daß der Index geordnet ist, kann mit Hilfe von „Suchen durch Halbieren“ ein Eintrag im Index mit logarithmischem Aufwand gefunden werden. An diesem Eintrag klebt dann die Blocknummer des gesamten Datensatzes im Originalbestand (n=1000, k=10; n=1000000, k=20; n=1000000000, k=30 für b=2). Setzen und Auslesen von Indices Mit Hilfe von „show create table x“ kann die Definition einer Tabelle als SQL-Statement angezeigt werden. Dabei sind auch die genauen Index-Definitionen enthalten. Mit Hilfe von „alter table x add index (y)“ kann ein Index auf eine Spalte gelegt werden. Mit Hilfe von „alter table x add unique (y)“ kann ein unique Index auf eine Spalte gelegt werden. Es ist möglich, einen Index auf eine Kombination von Spalten zu legen: „alter table x add index (y, z)“. Der Index kann verwendet werden, wenn eine Query auf die Spalten y und z losgeht oder wenn eine Query nur auf die Spalte y geht. Er ist nicht nützlich, um eine Query auf eine Spalte z zu beschleunigen. Werden um Platz zu sparen Indices manchmal in Dreiecksform definiert: „(x, y, z), (y, z), (z)“. Wann machen Indices Sinn? Ein Index spart der Datenbank das Durchlesen aller Datenblöcke einer Tabelle. In einem Datenblock sind meist mehrere Datensätze gespeichert (Beispiel: Blockgröße 2048 Byte, Datensatzgröße 50 Byte: 40 Datensätze pro Block). Wenn nur ein Treffer in einem Block enthalten ist, muss dennoch der ganze Block gelesen werden. Wenn die Selektivität des Schlüssels also nicht groß genug ist, rentiert er sich bei einer Suche nicht (Beispiel: Ein Schlüssel auf das Feld „Geschlecht“ in einer Datenbank). Indices machen außerdem nur dann Sinn, wenn das Laden des Schlüssels deutlich weniger Zeit in Anspruch nimmt als das Laden der Datensätze der Tabelle, also wenn der Bestand in den Tabellen groß genug ist. MySQL weiß das, und verwendet bei sehr kleinen Tabellen keinen Index, sondern hält die gesamte Tabelle vollständig im RAM. Ein Index macht also (in absteigener Reihenfolge) Sinn für • alle Primärschlüssel als unique Index (Warum?) • alle Fremdschlüssel als Index. • alle Spalten, die in where-Clauses vorkommen, wenn die Spalte mehr als 20 verschiedene Werte enthält. • alle Spalten, nach denen gruppiert wird • alle Spalten, nac denen sortiert wird Analyse der Performance von Queries mit EXPLAIN Siehe dazu auch das ganze Kapitel http://www.mysql.com/doc/Q/u/Query_Speed.html . Das Kommando „EXPLAIN“ kann jedem SELECT vorangestellt werden. Das Resultat ist eine Tabelle, die die Badness der Query ausgibt. Die Badness der Query ist das Produkt der Zahlen in der Spalte „rows“. Die Badness von zwei unterschiedlichen Queries ist nur bedingt vergleichbar, aber bei der Optimierung einer Query ist die Badness ein wichtiger Faktor, den es zu minimieren gilt. Wichtig sind auch die Ausgaben der Spalten possible_keys und keys, die angeben, welche Indices für die Query in Frage kommen und welche tatsächlich verwendet werden.