Profi-Features in MySQL 5
Professionelle Datenbank

Installation

Profi-Features in MySQL 5

Eine schnelle und robuste Webdatenbank war MySQL schon immer. Über die Jahre hat sich in MySQL aber noch mehr getan. Mit neuen Funktionen wurde der Marktführer unter den Webdatenbanken zum ernst zu nehmenden Datenbank-Management-System. Dieser Artikel betrachtet einige Profi-Funktionen aus den Versionen 5.0 und 5.1 näher.

Aktuell ist die Version 5.0.19 das Flaggschiff von MySQL. Sie wurde vor kurzem in den Status der Produktivversion gehoben. Die nächste Version 5.1 befindet sich gerade in der Entwicklung und hat die Beta-Phase erreicht. Bei MySQL sagt das zwar nicht allzu viel, da dort große Teile des griechischen Alphabets zum Einsatz kommen. MySQL kennt im Gegensatz zu vielen anderen Software-Anbietern beispielsweise nicht nur Alpha und Beta, sondern auch Gamma. Die echte Versionsnummer der neuen 5.1-Version beträgt immerhin schon 5.1.7. Davon waren bereits fünf Versionen öffentlich und gerade in 5.1.6 sind noch neue Funktionen hinzugekommen.

Wie immer gibt es von beiden Versionen für sehr viele Plattformen passende Pakete. Für Linux stehen hier für beide Versionen RPM-Pakete zur Verfügung, die sich wie gewohnt installieren lassen. Unter Windows müssen Sie für 5.1 aktuell auf einen Installer verzichten und deswegen manuell installieren. Um MySQL trotzdem zum Laufen zu kriegen, entpacken Sie das Archiv. Dann wechseln Sie in das Hauptverzeichnis, das beim Entpacken angelegt wurde. Erstellen Sie dort aus einer der Vorlagen eine Konfigurationsdatei my.ini. Darin legen Sie in der Sektion [mysqld] das Basisverzeichnis und das Datenverzeichnis für Ihre Installation an:

basedir=C:/mysql-5.1.7 datadir=C:/mysql-5.1.7/data

Auf diesen Schritt können Sie verzichten, wenn Sie das Paket in das Verzeichnis C:\mysql packen, da dieses Verzeichnis das Standardverzeichnis ist. Wenn Sie mehrere MySQL-Instanzen auf einem System laufen haben, müssen Sie eventuell den Port anpassen.

MySQL fragt die Konfigurationsdatei in einer Standardreihenfolge ab, die mit der my.ini im Windows-Verzeichnis beginnt. In dieser Reihenfolge wird aber auch eine my.ini-Datei im Programmverzeichnis von MySQL geprüft ? in der Praxis ist dies meist die beste Wahl, die auch der Windows-Installer für MySQL 5.0 trifft. Wenn die Konfigurationsdatei angelegt ist, wechseln Sie in die Eingabeaufforderung und starten den MySQL-Server:

mysqld -console

Sie können MySQL nun über die Eingabeaufforderung oder Konsole verwalten, oder Sie verwenden eines der zahlreichen Verwaltungs-Tools. Wenn Sie in der Konsole arbeiten, rufen Sie die MySQL-Eingabe auf und geben über die Optionen die Zugangsdaten an:

mysql -h localhost -u Nutzername -p

Sie werden dann zur Eingabe des Passworts aufgefordert. Sobald das erfolgreich erledigt ist, steht vor der Eingabe mysql>. Falls Sie bei den Optionen nicht weiterwissen, verwenden Sie den Befehl help;, um Hilfe zu erhalten. Ansonsten können Sie hier nun Ihre Datenbanken und Daten auch über SQL steuern. Gerade zum Ausprobieren neuer Funktionen ist das recht praktisch.

Multi-Abfrage: Views

Profi-Features in MySQL 5

Views oder auch Sichten sind neu ab MySQL 5.0.1. Ihr Sinn ist, eine Abfrage als View zwischenzuspeichern. Eine View enthält somit unter Umständen Informationen aus mehreren Tabellen. Views erlauben dabei nur Datenabfragen mit SELECT. Das Einfügen oder Löschen von Daten ist über Sichten nicht möglich.

Hier ein Beispiel, das eine View für eine einfache Artikeldatenbank erstellt. Dazu werden aus zwei Datenbanken Daten abgefragt. Eine zusätzliche WHERE-Klausel schränkt die Datenmenge auf Daten eines Autors ein:

CREATE VIEW artikeldb.titel AS SELECT artikel.titel, autoren.autor FROM artikel, autoren WHERE autoren.autor_id = ‘1’

Neben dem Befehl CREATE haben Sie auch die Möglichkeit, eine View durch eine andere zu ersetzen. Dafür ist REPLACE verantwortlich. Beide lassen sich auch kombinieren:

CREATE OR REPLACE VIEW artikeldb.titel AS SELECT artikel.titel, autoren.autor FROM artikel, autoren WHERE autoren.autor_id = ‘1’

Mit ALTER VIEW ändern Sie eine View und mit DROP VIEW wird sie gelöscht. Um eine View einzusetzen, fragen Sie sie einfach mit normalem SELECT-Befehl ab:

SELECT titel, autor FROM titel

In der Praxis haben Views vor allem zwei Vorteile: Sie erlauben einfachen Zugriff auch bei komplexen Tabellenrelationen. Das heißt, Sie können umfangreiche Normalisierung betreiben und viele Tabellen erstellen ? die Views sind dagegen einfach. Der zweite Vorteil ist, dass Views sehr detailliert mit Rechten versehen werden können. Sie sollten allerdings beim Update von einer MySQL-Version ohne Views darauf achten, dass die Rechtetabellen von MySQL erneuert werden.

Wo es Vorteile gibt, gibt es natürlich auch Nachteile: Verstecken Views zu komplexe Abfragen, merkt man oft sehr spät, dass eine der Abfragen ein Performance-Problem birgt. Ganz abgesehen davon sind die Programmierer, die die Views nutzen, meist recht fern von der eigentlichen Datenbankstruktur.

Insgesamt schaden Views der Performance aber nicht, sondern nützen sogar eher, da die Abfragen in Views schon den Datenbank-internen Abfrageoptimierer durchlaufen haben. Nur, wenn Tabellen mit einem Index versehen werden sollten, sind Views nicht zu empfehlen, da sie keine Indizes unterstützen.

Gespeicherte Prozeduren

Profi-Features in MySQL 5

Gespeicherte Prozeduren, englisch Stored Procedures, sind in MySQL ebenfalls mit Version 5 hinzugekommen. Sie halten sich recht eng an das Muster von DB2 und an den Standard SQL:2003. Bevor Sie loslegen, einige Grundgedanken zu gespeicherten Prozeduren: Sie dienen dazu, die Datenlogik in Richtung Datenbank zu verlagern. Gespeicherte Prozeduren sind vorgefertigte Datenbankabfragen, die sich jederzeit aufrufen und damit ausführen lassen.

Legen Sie einfach einmal eine simple Prozedur an, die eine Datenbanktabelle ausliest:

CREATE PROCEDURE auslesen()
SELECT * FROM artikel;

Um die gespeicherte Prozedur zu verwenden, rufen Sie sie mit CALL auf:

CALL auslesen();

Sie sehen nun bei MySQL die Ausgabe der SELECT-Abfrage. Um die gespeicherte Prozedur wieder zu entfernen, verwenden Sie DROP:

DROP PROCEDURE IF EXISTS auslesen;

Mit IF EXISTS prüfen Sie vorab, ob die gespeicherte Prozedur überhaupt vorhanden ist.

Wenn Sie in der Eingabeaufforderung arbeiten, sollten Sie sich von Zeit zu Zeit einen Überblick verschaffen, welche gespeicherten Prozeduren vorhanden sind. Dies gelingt mit der folgenden SQL-Anweisung:

SHOW PROCEDURE STATUS

Um wichtige Charakteristika einer gespeicherten Prozedur zu ändern, setzen Sie ALTER ein. Mit SHOW CREATE PROCEDURE verrät die angesprochene gespeicherte Prozedur, mit welchem SQL-Kommando sie erstellt wurde. Letzteres ist eine MySQL-spezifische Erweiterung des SQL:2003-Standards.

Richtig interessant werden gespeicherte Prozeduren, wenn sie Parameter erhalten und Parameter zurückliefern können. Für Parameter gibt es drei Zustände: IN für einen Parameter, der Daten in die gespeicherte Prozedur bringt, OUT für einen, der Daten aus der Prozedur liefert und INOUT für einen Parameter, der in beide Richtungen arbeitet.

Prozeduren einsetzen

Profi-Features in MySQL 5

Ein Beispiel: Sie verwenden eine gespeicherte Prozedur, um einen bestimmten Artikel a
nzulegen oder abzufragen. Als Parameter werden der Artikeltitel und der Artikelinhalt übergeben. Innerhalb des BEGIN/END-Blocks stehen die SQL-Anweisungen, die die gespeicherte Prozedur enthalten.

CREATE PROCEDURE auslesen(IN id INT)
BEGIN
SELECT titel, autor FROM artikel WHERE artikel_id= id;
END

Um diese gespeicherte Prozedur einsetzen zu können, müssen Sie in der MySQL-Eingabeaufforderung noch das Abschlusszeichen für Anweisungen ändern. Standardmäßig ist das ein Strichpunkt, aber dieser wird in der Syntax der gespeicherten Prozedur verwendet. Für diesen Wechsel ist der Befehl DELIMITER, gefolgt vom Abschlusszeichen, zuständig:

DELIMITER |

Zurück wechseln Sie jederzeit mit demselben Befehl:

DELIMITER ;

Neben den gespeicherten Prozeduren verwendet MySQL auch Funktionen. Sie entstanden ursprünglich aus den benutzerdefinierten Funktionen und werden nun wie gespeicherte Prozeduren behandelt, nur mit dem Unterschied, dass sie Daten zurückliefern.

Schlüsselreiz: Trigger

Profi-Features in MySQL 5

Trigger sind Abfragen, die erst auf ein bestimmtes Ereignis hin ausgeführt werden. Gleichzeitig handelt es sich dabei um Datenbankobjekte, die mit einer bestimmten Tabelle assoziiert sind. Tritt dann das Ereignis für die Tabelle ein, wird der Trigger ausgeführt. Das Ereignis ist meist eine andere SQL-Abfrage. In MySQL sind Trigger seit Version 5.0.2 enthalten.

Einsatzzwecke für Trigger gibt es einige. Ein Beispiel: Angenommen, Sie speichern Daten an irgendeiner Stelle redundant. Dann können Sie einen Trigger auf das Einfügen von Daten an einer Stelle setzen und diese Daten an die andere Stelle übernehmen.

Im folgenden Beispiel wird beim Einfügen neuer Daten in eine Tabelle artikel gleich ein Dateneintrag in einer Tabelle artikel_stats vorgenommen. Diese Tabelle kann beispielsweise als Logfile dienen:

CREATE TRIGGER statistik AFTER INSERT ON artikel
FOR EACH ROW
INSERT INTO artikel_stats SET artikel_id=NEW.artikel_id, artikel=NEW.titel;

MySQL erlaubt Trigger bei den Ereignissen INSERT, UPDATE und DELETE. Das Laden und Löschen wird allerdings auch vom SQL-Befehl REPLACE ausgelöst. Vor dem eigentlichen Ereignis steht noch der Zeitpunkt, an dem der Trigger ausgeführt wird: AFTER bedeutet nach Ausführen des zu Grunde liegenden SQL-Kommandos, BEFORE bedeutet davor.

Um einen Trigger wieder zu löschen, verwenden Sie DROP TRIGGER. Sie sollten Trigger immer löschen, bevor Sie die Tabellen löschen. Einen Trigger nur zu ändern, ist nicht möglich. Um ihn zu ändern, müssen Sie ihn löschen und dann neu erstellen. Ein Trigger kann auch gespeicherte Prozeduren verwenden, Prepared Statements sind dagegen noch nicht erlaubt. Bei den Einschränkungen und Problemen mit Triggern, aber auch mit den anderen hier genannten Funktionen, kann es sich teilweise auch um Phänomene handeln, die bereits mit der nächsten Version beseitigt werden. MySQL befindet sich zurzeit in ständiger Entwicklung, und auch Unterversionen enthalten teils wichtige Neuerungen.

Markante Details

Profi-Features in MySQL 5

Die bisher genannten Funktionen sind die Highlights. In MySQL 5.1.x ist aber noch einiges mehr in der Pipeline. Interessant sind beispielsweise die in MySQL 5.1.6 eingeführten Events. Mit ihnen erzeugen Sie ein Ereignis, das nicht wie ein Trigger bei einem SQL-Befehl ausgeführt wird, sondern das beispielsweise in regelmäßigen Abständen zum Einsatz kommt:

CREATE EVENT
e
ON SCHEDULE
EVERY 2 WEEK
DO
INSERT INTO stats VALUES (‘Zwei Wochen sind vorüber’);

Damit die Events funktionieren, muss die Einstellung event_scheduler gesetzt sein. Dies klappt auch über die Eingabeaufforderung:

SET GLOBAL event_scheduler = 1;

Eine weitere Funktion bietet die Möglichkeit, in MySQL-Feldern gespeicherte XML-Daten mit Hilfe von XPath-Ausdrücken abzufragen (dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html). MySQL bietet hierfür zwei SQL-Funktionen: EXTRACTVALUE(XML-Feld, XPath) liest Werte per XPath-Ausdruck aus und UPDATEXML(XML-Feld, XPath, NeuerWert) ersetzt den per XPath gefundenen Wert durch einen neuen.

Eine weitere Funktion wird häufig als neu bezeichnet, ist aber im Prinzip schon älter: Transaktionen. Die Transaktionen erlauben es, mehrere SQL-Anweisungen zu gruppieren, die dann zusammen ausgeführt und bei Scheitern alle rückgängig gemacht werden. Das Paradebeispiel ist ein Einkauf, bei dem der Nutzer parallel zahlt und das Produkt für die Lieferung vorgesehen wird. Hier sind mindestens zwei Aktionen voneinander abhängig: ob die Zahlung klappt und ob das Produkt lieferbar ist und die Adressübernahme klappt. Scheitert eines von beiden, dann müssen beide Vorgänge rückgängig gemacht werden. In der Nomenklatur von Transaktionen ist das ein ROLLBACK.

Transaktionen in einer Datenbank müssen dem Acid-Prinzip folgen: Sie müssen atomar sein (Atomicity), konsistent (Consistency), isoliert (Isolation) vor allem vor anderen Transaktionen und dauerhaft (Durability), auch wenn ein Systemfehler auftritt. MySQL unterstützt diese Art von Transaktionen in den Tabellenarten BerkeleyDB und InnoDB, nicht aber in der Standard-Tabellenart MyISAM. Seit MySQL 5.0.3 gibt es die XA Distributed Transactions für InnoDB, die Transaktionen über Datenbankgrenzen hinweg erlauben.

Gerade bei InnoDB ist die zukünftige Entwicklung spannend, da der Anbieter erst vor kurzem von Oracle übernommen wurde.

Fazit

Profi-Features in MySQL 5

MySQL entwickelt sich zur mächtigen Datenbank. Ganz gleich wie man persönlich den Vergleich zu PostgreSQL oder gar Oracle und Microsoft SQL Server zieht: Die Verbesserungen im Profi-Bereich in Version 5 sind auch für die an sich in Sachen Datenbanken anspruchslose Web-Anwendungsentwicklung sehr nützlich. Der Einsatz von Views oder gespeicherten Prozeduren ist in erster Linie eine Architekturentscheidung. Mit Views können Sie komplexe Abfragen vereinfachen, erhalten aber auch eine manchmal etwas unübersichtliche Zwischenschicht.

Die gespeicherten Prozeduren wiederum verlagern Teile der Datenbanklogik aus der Programmierung in die Datenbank. Dies führt zu einem oft gepredigten Dreischichtenmodell für Web-Anwendungen.