mySQL 4.0 steigert Query-Performance

MySQL gehört zu den beliebtesten Open-Source-Datenbanken und wird gern für Webprojekte in Zusammenarbeit mit der Middleware PHP und dem Webserver Apache eingesetzt. Seit Mitte März ist die Version 4.0.12 für den Praxiseinsatz freigegeben (Production Release). Was die neue Version bringt und welche Fallstricke noch zu beachten sind, zeigt dieser Artikel.

mySQL goes Transaction

Die wohl gravierendste Änderung der neuen Version ist eigentlich gar keine: Inzwischen werden nämlich alle Binaries des Datenbankservers mit aktivierter InnoDB-Option ausgeliefert. Dieser Tabellentyp war auch schon in der alten Version 3.23 vorhanden, musste aber als Compileroption aktiviert werden. Damit waren InnoDB-Tabellen bisher nur dann verfügbar, wenn die Datenbank selbst neu kompiliert wurde. Gerade für Windows-Anwender war das jedoch nicht immer eine triviale Aufgabe, so dass die meisten Anwender mit den verfügbaren myIsam Tabellen vorlieb nahmen.

Der Vorteil von InnoDB ist, dass dieser Tabellentyp auch Transaktionen und Fremdschlüssel (Foreign Keys) unterstützt. Ein Beispiel:

Die beiden Tabellen "usertable" und "daten" sind über den Fremdschlüssel "user_id" verbunden. Das führt dazu, dass ein Datensatz in "usertable" nicht gelöscht werden kann, solange noch zugehörige Datensätze in "daten" vorhanden sind. Die referenzielle Integrität ist damit automatisch gewährleistet.

Gerade wegen des Fehlens von Fremdschlüsseln und Transaktionen wurde mySQL von vielen Entwicklern als vollwertige Datenbank bisher nicht ernst genommen, denn diese Funktionalitäten mussten immer im Code realisiert werden. Wer akzeptierte, dass mySQL ursprünglich auf hohe Abfragegeschwindigkeit optimiert war, konnte diese Tatsache jedoch gut verschmerzen.

Mit dem neuen Release hat man die Wahl, welchen Tabellentyp man verwenden möchte. Auch eine Mischung unterschiedlicher Typen ist möglich. Der Typ wird beim Erzeugen der Tabelle festgelegt.

Vorsichtig sollte man jedoch bei InnoDB-Tabellen in bezug auf die Geschwindigkeit sein. Standardmässig werden hier nämlich alle SQL-Abfragen im sogenannten Autocommit-Modus ausgeführt, was die Performance insbesondere beim Einfügen und Ändern von Daten stark bremst. Daher sollte dieser Tabellentyp wirklich nur dann verwendet werden, wenn die Anwendung tatsächlich mit Transaktionen arbeitet.

Query Cache für noch schnellere Abfragen

Eine Datenbank wird im Internet oft für Datenbestände benutzt, die sich nicht allzu oft ändern. So werden beispielsweise in einem Content-Management-System (CMS) nicht notwendigerweise täglich alle Daten aktualisiert. Diese Tatsache wird von mySQL clever ausgenutzt.

Bisher musste die Datenbank bei jeder Abfrage erneut sämtliche Tabellen durchsuchen. Mit der neuen Version unterstützt mySQL einen Query Cache, der den Text einer Abfrage speichert und bei neuen Abfragen prüft, ob diese nicht bereits früher schon mal durchgeführt wurden. Ähnlich wie bei einem Browser-Cache werden dann die Ergebnisse aus dem Puffer geliefert, ohne die Tabellen erneut zu durchsuchen.

Gerade bei komplexen Abfragen über mehrere Tabellen macht sich dieser Cache deutlich bemerkbar. Ausserdem wird die Datenbank-Engine entlastet, da sie dann wieder mehr Zeit für "echte" Abfragen hat.

Es besteht trotzdem keine Gefahr, veraltete Daten zu erhalten: Werden Informationen in einer der beteiligten Tabellen geändert, so löscht mySQL die entsprechende Abfrage sofort aus dem Cache. Auf diese Weise ist die Aktualität der Daten jederzeit sichergestellt.

Einen Fallstrick gibt es dennoch bei dieser Option: Obwohl sie in der Datenbank standardmässig aktiviert ist, muss noch über eine Variable ausreichend Speicherplatz für den Cache bereitgestellt werden. Das kann beispielsweise über einen Eintrag in der my.ini-Datei erfolgen:

Nach dem Neustart der Datenbank steht der Query Cache zur Verfügung und wird automatisch für Abfragen verwendet.

Führt man eine komplexe Abfrage bei eingeschaltetem Query-Cache aus, so dauert diese beim ersten Mal etwas länger. Eine Testabfrage dauerte beispielsweise 4,37 Sekunden. Bei erneuter Durchführung der gleichen Abfrage wird die Wirkung des Query-Cache deutlich. Der zweite Durchgang dauerte im Test nur noch 1,09 Sekunden.

Volltextsuche wie bei Google & Co.

Die Volltextsuche war bereits in der Vorversion vorhanden. In Version 4 wurde nun zusätzlich die Option in boolean mode eingeführt, so dass auch logische Ausdrücke wie +Zürich -Flughafen möglich sind. Da mySQL seit jeher gern für Suchmaschinen eingesetzt wurde, wundert man sich, dass diese Option nicht schon früher verfügbar war.

Zusätzlich können Suchbegriffe nun auch hinsichtlich der Wichtigkeit auf- und abgewertet werden. So ergibt die Abfrage "+apple +(>pie " wird der "apple pie" jedoch in der Wichtigkeit höher bewertet, erscheint also früher in der Ergebnisliste.

Hier ein kurzes Beispiel für eine solche Volltextsuche, wie man es auch in der mySQL Dokumentation finden kann:

Es wird hier ein zusammengesetzter Volltextindex aus den Feldern "title" und "body" verwendet, der in der folgenden Abfrage entsprechend verwendet werden kann:

Im Gegensatz zur Abfrage mit "LIKE" werden hier keine Platzhalter wie "%" verwendet. Statt dessen ermittelt mySQL einen Wert für die Relevanz der Fundstelle. Dieser Wert kann auch als Feld ausgegeben werden. Beispielsweise liefert folgende Abfrage:

Die Ergebnisse für die Relevanz lassen sich dann in absteigender Reihenfolge sortieren. Als Endresultat erhält man eine Liste nach absteigender Relevanz, so wie man es von den Suchmaschinen im Internet gewohnt ist.

SQL-Kommandos erweitert

Die neue Version enthält auch einige Änderungen bei den SQL-Kommandos. Delete beispielsweise kann jetzt aus mehreren Tabellen gleichzeitig löschen. Während man früher zum Löschen von Einträgen aus mehreren Tabellen unterschiedliche SQL-Abfragen hintereinander durchführen musste, reicht nun ein einziger Befehl wie dieser hier:

Damit werden aus den Tabellen "usertable" und "daten" gleichzeitig alle Einträge gelöscht, die zum Namen "user1" gehören. Die beiden Tabellen sind in diesem Beispiel über die Bedingung "usertable.id = daten.user_id" verbunden. Auf diese Weise kann das Löschen aus mehreren Tabellen wesentlich vereinfacht werden.

Auch das upate-Kommando wurde so erweitert, dass mehrere Tabellen gleichzeitig in einer Abfrage verwendet werden können. Daher ist jetzt folgende Abfrage möglich:

Auch dies ist eine hilfreiche Vereinfachung im Programmieralltag.

Union-Select ermöglicht das gleichzeitige Abfragen und Zusammenführen gleichartiger Tabellen. Das ist insbesondere dann vorteilhaft, wenn unterschiedliche Tabellen mit gleicher Struktur vorliegen, beispielsweise Logdaten, die jeden Monat in eine gesonderte Tabelle abgelegt werden müssen.

So ist beispielsweise jetzt folgende Abfrage möglich:

Damit werden alle Felder aus beiden Tabellen geliefert, die Daten enthalten. Merge-Tabellen sollen in der neuen Version verbessert worden sein. Leider funktionierte der Befehl im Test nicht. Wurde beispielsweise eine solche Anweisung verwendet:

Da MERGE schon in Version 3.23.25 noch als Gamma-Release gekennzeichnet war, ist es wohl besser, diese Funktion vorläufig zu meiden.

Veränderte Zugangsrechte

Die Einteilung der Rechte für die Datenbankbenutzer wurde in mySQL 4.0.12 verfeinert. Nun ist es endlich auch möglich, dem Benutzer lediglich die Rechte zum Erzeugen von temporären Tabellen zu erteilen. Bisher galt immer: alles oder nichts. Das Erzeugen von temporären Tabellen für Zwischenergebnisse ist allerdings häufig der einzige Workaround für die Erstellung von Sub-Queries, ein Feature, das erst in Version 4.1 vorhanden sein wird.

Bisher musste der Anwender jedoch das Recht haben, Tabellen generell neu zu erstellen, was nicht immer erwünscht war. Nun kann die Anwendung mit dem Recht create temporary tables solche Zwischentabellen erstellen, auch ohne das generelle Recht zum Anlegen neuer Tabellen zu haben. Eine durchaus sinnvolle Ergänzung.

Um in den Genuss der neuen Benutzerrechte zu kommen, müssen nach einem Update von Version 3.23 auf 4.0 einige Strukturen in den Rechte-Tabellen verändert werden. Das übernimmt normalerweise ein kleines Script namens "mysql_fix_privilege_tables", das im mySQL-Verzeichnis "scripts" zu finden ist. Leider handelt es sich hier um ein Shell-Script, welches nur unter Unix/Linux lauffähig ist. In der Windows-Version ist keine entsprechende Möglichkeit vorhanden, um diese Umstellung automatisch durchzuführen, so dass hier leider etwas Handarbeit notwendig ist. Sind nicht allzu viele Daten vorhanden, so kann man die bestehenden Datentabellen mit "mysqldump" sichern und die alte Version vollständig löschen. Bei kompletter Neuinstallation von mySQL werden die Systemtabellen dann ja neu angelegt und das Backup der Tabellen kann neu eingelesen werden. Bei diesem Vorgehen müssen jedoch alle Benutzer der Datenbank wieder neu angelegt werden.

Als Alternative kann man auch das vorhandene Shell-Script entsprechend umschreiben, so dass es sich unter Windows etwa als PHP-Script ausführen lässt.

Deutsche Umlaute

Nur sehr versteckt schliesslich findet man in der Dokumentation eine Neuerung, die besonders die deutschsprachigen Anwender begeistern dürfte: mySQL kennt nun auch den character_set "german1", der die deutschen Umlaute korrekt sortiert. Entgegen der Dokumentation muss der Eintrag in der my.ini deshalb nicht "latin1_de" lauten, sondern "german1":

Bei dieser Einstellung werden Umlaute vor der Sortierung in die zwei Umlautzeichen gemappt. Also wird aus "ä" ein "ae", was dazu führt, dass der Name "Meier" nun korrekt zwischen "März" und "Müller" einsortiert wird. Bei der Standardeinstellung "latin1" wurden die Umlaute stets am Ende der Liste aufgeführt.

Fazit

Die neue Version 4.0 scheint mit ihren Neuerungen durchaus gut durchdacht zu sein. Lediglich die Sub-Queries werden immer noch schmerzlich vermisst, sind aber für Version 4.1 schon angekündigt. Gerade den Windows-Anwendern kommt man mit der standardmässigen Einbindung von innoDB-Tabellen sehr entgegen, da diese nun ohne Neukompilierung zugänglich sind. Hinsichtlich Geschwindigkeit ist der neu eingeführte Query-Cache sehr zu begrüssen.