Vom Rolodex zum Datawarehouse

Mit Funktionen wie Stored Procedures und Storage Engines für Archivierung möchte MySQL attraktiver für Unternehmen werden.

Artikel erschienen in Swiss IT Magazine 2005/21

Die äusserst beliebte freie Datenbank MySQL wurde vor allem von vermeintlichen Profis als «Textdatei auf Drogen» und «elektronischer Rolodex» verspottet, da ihr bis und mit Version 4.1 etliche Funktionen fehlten, die ein ausgewachsenes RDBMS (Relational Database Management System) ausmachen – unter anderem die Unterstützung von Stored Procedures, Triggers und Views. Mit der Version 5.0 wurden die meisten dieser Limitierungen auf einen Schlag behoben, womit sich die schwedische Datenbank für noch mehr Einsatzzwecke als bisher in Unternehmen eignet.

Alles im Griff

Das neue Data Dictionary von
MySQL 5 mag zwar nicht die aufregendste Neuerung sein, allerdings vereinfacht es die Abfrage von Metadaten über Tabellen, Datenbanken und andere Objekte, die vom Server verwaltet werden. Bis MySQL 5.0 mussten Metadaten mit dem Kommando SHOW ausgelesen werden. Wer beispielsweise die in einer Datenbank vorhandenen Tabellen auslesen wollte, bediente sich mit SHOW tables FROM database. Das neu implementierte Data Dictionary ermöglicht es dagegen, auf die Metadaten wie auf eine Tabelle zuzugreifen, womit das Design nun auch Edgar F. Codds 12 Regeln für relationale Datenbanken entspricht, deren Regel 1 besagt, dass Daten immer aus Tabellen bezogen werden.
MySQLs Data Dictionary heisst information_schema und entspricht dem ANSI/ISO-Standard SQL:2003. Es enthält unter anderem Informationen über unterstützte Zeichensätze, Zugriffsrechte, Stored Procedures, Views und Indices. Da es sich um Tabellen handelt, erfolgt die Abfrage wie bei allen anderen Tabellen mit SELECT-Statements.

Den Server arbeiten lassen

Bis zur Version 5 fehlten MySQL jegliche Funktionen zur serverseitigen Ausführung von Prozeduren, zur Manipulation von Daten und zur Abstraktion von Tabellen. Mit Triggers, Views, Stored Functions und Procedures, Views und Cursors hat man als Entwickler nun fast alle Werkzeuge dazu erhalten, welche die SQL-Standards vorsehen.
Stored Procedures and Routines erlauben es, Funktionen und Abläufe auf dem Server abzulegen und auszuführen. Sie können sowohl zur serverseitigen Daten-Selektion als auch zur Daten-Manipulation verwendet werden. Stored Procedures und Routines bieten sich unter anderem dann an, wenn mit verschiedenen Applikationen auf einen Datenbestand zugegriffen wird, damit eine gewisse Funktionalität nicht immer wieder von Neuem implementiert werden muss oder wenn wie bei Finanzinstituten die Sicherheit eine grosse Rolle spielt und Applikationen und Clients keinen direkten Zugriff auf Tabellen und Daten erhalten sollen. Mit Hilfe von Cursors (bislang nur teilweise implementiert) und Control-Flow-Funktionen lässt sich auch durch Daten iterieren und im Endeffekt einfache Programme schreiben. Die Syntax für Stored Procedures und Routines entspricht dem SQL:2003-Standard, den auch IBMs DB2 verwendet.

Einen ähnlichen Zweck wie die Stored Procedures erfüllen die Views, welche Datenzugriff auf einer Ebene oberhalb von Tabellen anbieten. In einem View können eine oder in Kombination mit UNION mehrere Abfragen zusammengefasst werden, die Zugriff auf Daten aus mehreren Tabellen oder auf ein Subset der Daten einer Tabelle bieten. Neben den klassischen Views bietet MySQL 5 auch Updateable Views, das heisst, dass schreibende Operationen wie UPDATE und DELETE auf Views angewendet werden können. Allerdings müssen die aktualisierbaren Views gewissen Regeln entsprechen, damit sie beschrieben werden können. Dazu gehört vor allem, dass die unter dem View liegenden Abfragen die ursprünglichen Resultate, beispielsweise mittels Aggregatsfunktionen, nicht manipulieren dürfen.
Triggers sind Aktionen, die beim Manipulieren von Daten in Tabellen automatisch ausgelöst werden, wenn bestimmte Bedingungen eintreffen. Mit ihnen lassen sich beispielsweise Foreign Keys nachbilden, indem beim Einfügen eines Datensatzes geprüft wird, ob ein referenzierter Datensatz wirklich existiert – existiert er nicht, kann beispielsweise die Ausführung der Operation abgebrochen werden. Auch lassen sich mit der aktuellen Trigger-Implementierung Felder je nach bereitgestellten Daten befüllen oder aktualisieren.

Doch wie auch der Cursor-Implementierung fehlen den Triggers einige Funktionen. So werden Triggers momentan nur für jede eingefügte oder modifizierte Zeile ausgeführt. Die etwas weniger häufig verwendete Methode, dass ein Trigger pro Query, auch wenn mit
ihr mehrere Datensätze eingefügt oder manipuliert werden, nur
einmal ausgeführt wird, fehlt, soll aber in MySQL 5.1 nachgerüstet werden (siehe Kasten «Ausblick
auf MySQL 5.1»).

Unterstützung bei der Archivierung

MySQL kennt als einziges RDBMS das Prinzip der Storage Engines. In MySQL lassen sich mehrere dieser Storage Engines integrieren, die über ganz unterschiedliche Funktionen verfügen, und können dann über eine einheitliche Schnittstelle, den MySQL Server, angesprochen werden. Neben den Haupt-Engines wie MyISAM oder InnoDB existieren etliche weitere Tabellentypen. In MySQL 5 sind zwei weitere dazugekommen: Archive und Federated.
Archive dient, wie der Name dies schon impliziert, zur Archivierung von Daten. Entsprechend lassen sich nur Daten einfügen (INSERT) und abfragen (SELECT). Eine Datenmanipulation ist nicht möglich, ebenso fehlt die Unterstützung für Indices, wobei diese in der Zukunft noch nachgerüstet werden soll. Dafür verfügt Archive über Funktionen, welche den Platzverbrauch der zu speichernden Daten möglichst tief halten. So werden sämtliche Daten, die in die Tabellen abgefüllt werden, mit Hilfe der zlib-Bibliothek komprimiert. Im Vergleich zu einer MyISAM- oder InnoDB-Tabelle lassen sich damit über 75 Prozent des Speicherplatzes einsparen. Auch gegenüber einer komprimierten MyISAM-Tabelle lassen sich nochmals etwa 10 Prozent Speicherplatz sparen.

Archive ist sehr schnell beim Lesen und blockiert dank Row-Level-Locking und Snapshot-Reads beim Schreiben keine lesenden Prozesse und umgekehrt. Die lesende Geschwindigkeit liegt bei kleineren Datenmengen zum Teil noch deutlich unter MyISAM, aber je mehr Daten archiviert wurden, umso besser schneidet Archive im Vergleich zu MyISAM ab. Beim Schreiben lässt Archive InnoDB und MyISAM in der Regel hinter sich.
Zusammen mit der ebenfalls neuen Federated Storage Engine lässt sich MySQL problemlos für Datenarchivierung nach dem Information-Lifecycle-Management-Prinzip verwenden, bei dem Daten, die nicht mehr regelmässig benötigt werden, nicht zusammen mit den aktuellen Daten gelagert werden, aber dennoch über ein einheitliches Interface zugänglich sein sollten.
Die Federated Storage Engine erlaubt es, Tabellen von entfernten MySQL-Servern wie lokale Tabellen zu behandeln; sie stellt damit den umgekehrten Weg der Replikation dar. Dabei wird auf dem lokalen Server nur noch die Tabellen-Definition samt einem Data Source Name für die Verbindung zum entfernten Server gespeichert. Wenn nun eine Abfrage an die Federated-Tabelle gestellt wird, leitet der MySQL-Server die Anfrage wie bei jeder anderen Storage Engine an den passenden Handler weiter. Dieser schaut aber nicht wie bei MyISAM oder InnoDB auf der lokalen Festplatte nach den Daten, sondern benutzt die Client-Library, um eine Verbindung zu einem entfernten MySQL-Server herzustellen und die Anfrage an ihn weiterzuleiten. Der entfernte MySQL-Server liefert dann die Daten zurück. Für den Anwender ist dieser Vorgang komplett transparent. Der einzige Unterschied zu einem lokalen Tabellentyp besteht einerseits in der Zugriffsgeschwindigkeit und andererseits in einigen Limitierungen. So können Tabellen nicht mit Hilfe der Data Definition Language (DDL) manipuliert werden.

Um das einheitliche Interface zwischen aktuellen und archivierten Daten herzustellen, bietet es sich an, die Archive-Tabelle auf dem MySQL-Server mit den aktuellen Daten als Federated-Tabelle anzulegen und diese dann mit Hilfe eines View und UNION zu kombinieren.