Nachdem wir im Dezember 2014 erfahren hatten, dass sich die Bestände der zweiten Auflage zu Ende neigen, ging es für uns Autoren an die Planung der dritten, aktualisierten Auflage des MySQL Buchs. Diese Auflage ist bereits unter dem neuen Verlagsnamen Rheinwerk Verlag am 25.05.2015 erscheinen.

Released or not Released: Eine Versionsgeschichte

Da MySQL kontinuierlich weiterentwickelt wird, stellt sich für uns immer die Frage, auf welche Version der beliebten Datenbankensoftware wir uns für das Schreiben festlegen sollen. Natürlich möchten wir so aktuell wie möglich sein, um die neuesten Features berücksichtigen zu können. Da es jedoch durchaus einige Jahre dauern kann, bis eine Developer-Version Produktivstatus erreicht und damit in die meisten Software-Repositories verfügbar wird, muss allerdings die Testversion schon soweit ausgereift sein, dass möglichst keine inkompatiblen Änderungen mehr eingeführt werden. Aus diesem Grund gibt es eben mehrere Faktoren, die wir bei der Auswahl einer Version für das Buch berücksichtigen müssen.

Um unsere Entscheidungen zu untermauern, betrachten wir die Release-Zyklen in den Release Notes auf der Webseite des Produkts. Die Entwicklung des neuen MySQL 5.7er Branches wurde von Oracle bereits im Frühjahr 2013 gestartet. Gleichzeitig wurde auch noch an der auch heute (Ende Juni 2015) noch offiziellen Version von MySQL 5.6 weiter entwickelt. Abgeleitet von der bisherigen Dauer eines GA-Releases, haben wir uns dann zuerst auf die Development-Version MySQL 5.7.5 Milestone 15 festgelegt. In einem nächsten Schritt, haben wir uns durch hunderte Seiten Release Notes gewühlt, um die gravierenden Änderungen und interessantesten neuen Features herauszuarbeiten.

Quelle: Wikipedia

Diese Aktualisierungen haben wir folge dessen in die Neuauflage des Buches eingearbeitet. Zwar waren die Änderungen dieses mal nicht so radikal wie von MySQL 5.5 auf 5.6 in der vergangenen Auflage. Dennoch gibt es einige Neuerungen, die eine starke Überarbeitung bestimmter Kapitel der dritten Auflage nötig gemacht haben. Selbstverständlich sind wir auch auf die vielen Anregungen, Ratschläge und auch Tipps eingegangen, die wir von aufmerksamen Lesern und Rezensenten erhalten haben. Wir möchten uns hier nochmal ausdrücklich bei allen Helfern bedanken, die dazu beitragen, dass unser Buch noch besser wird. Da das mitgelieferte Datenset ein sehr wichtiger Bestandteil des Buches ist, haben wir natürlich den gesamten Datenbestand neu erzeugt, die Konsistenzen überprüft und die Daten aktualisiert. Somit ist nicht nur die dritte Auflage stark überarbeitet, sondern auch das Datenset zum Testen erstrahlt in neuem Glanz.

In letzter Minute

Noch während des Schreibens kam das Milestone 16 Release MySQL 5.7.6 heraus, woraufhin wir alle neu eingeführten Änderungen erneut in das bestehende Manuskript eingearbeitet haben. Irgendwann ist jedoch Schluss und wir konnten nicht mehr länger mit Versionen spekulieren, um den Drucktermin nicht zu verpassen. Die MySQL Version 5.7.7 hat im April dann Release Candidate Status erreicht, was darauf hindeutet, dass keine gravierenden Änderungen mehr vor der GA-Version zu erwarten sind. Ich denke, dass wir das Timing trotzdem gut hinbekommen haben und somit ein sehr aktuelles Buch anbieten können.

Das Handwerk

Wir wurden schon öfter gefragt, wie denn eigentlich ein ganzes Buch entsteht. Entgegen manch einer Vermutungen arbeiten wir nicht mit Latex, sondern mit einer speziellen Formatvorlage, die uns der Verlag als Microsoft Word Plugin zur Verfügung stellt. Entgegen vieler Vorbehalte funktioniert das auch bei größeren Projekten, wie dem mehr als 800 Seiten starken Buch, sehr gut. Alle textuellen Inhalte, wie beispielsweise Kommandozeilenbefehle, Code-Snippets oder SQL-Beispiele werden von Hand mit den entsprechenden Format der Textvorlage formatiert und so gekennzeichnet. Grafiken und Screenshots gestalten wir selbst, die Referenzen von Textbausteinen, Bildern, Listings und weiteren Elementen werden ebenfalls manuell von uns gesetzt. Der Verlag fügt dann aus den einzelnen Kapiteln das gesamte Werk zusammen, hübscht die Grafiken auf und gibt dem ganzen einen einheitlichen Schliff. Natürlich werden die Texte auch lektoriert und durchlaufen ein mehrstufiges Fachgutachten.

Intern lesen wir alle unsere Kapitel mehrmals gegenseitig gegen und kommentieren die Änderungsvorschläge mit den üblichen Bordwerkzeugen von Word. Die Dateien selbst, die Bilder und Code-Beispiele versionieren wir ganz klassisch mit Subversion. Da wir alle Linux User sind, laufen sowohl die Windows-Maschine zum Schreiben, als auch mehrere Debian- und Ubuntu-Maschinen virtualisiert in VirtualBox. Somit haben wir eine sehr flexible Testumgebung, auch für die Szenarien, die mehrere Rechner benötigen, wie beispielsweise die Replikation. Das eingesetzte Datenbankenmanagementsystem ist logischerweise die MySQL Community Edition, meistens parallel in verschiedenen Versionen. Zum editieren und Testen der SQL Skripte verwenden wir natürlich auch die Workbench. Für das Erzeugen des Datensets verwenden wir Stored Procedures und PHP Skripte.

Da wir geografisch für österreichische Verhältnisse weit entfernt voneinander arbeiten, kommunizieren wir hauptsächlich über Email und haben regelmäßige Skype Konferenzen. Für die interne Dokumentation verwenden wir eine WikkaWiki Instanz.

Danke!

Wir freuen uns auch bei der dritten Auflage über umfangreiches Feedback und hoffen, dass wir viele neue Leser erreichen und für MySQL und seine Vielseitigkeit begeistern können! Sie erhalten das Buch im gut sortierten Buchhandel, direkt beim Rheinwerk Verlag (versandkostenfrei) und natürlich auch bei den Großen.

Die Virtualisierung von Betriebssystemen ist schon lange ein sehr populäres Thema und erfreut sich großer Beliebtheit im Serverbereich. Diese Technologie erlaubt es teure Serverhardware ressourcensparender zu nutzen, indem mehrere Gastsysteme virtualisiert auf einem realen Serversystem gleichzeitig laufen. Die reale Hardware (ein großer Server oder auch ein kleiner Laptop) wird als Host- oder Gastgebersystem bezeichnet. Die virtuelle Maschine (ein virtualisiertes Betriebssystem) wird Guest- bzw. Gastsystem genannt.  Die Virtualisierungssoftware wird auf dem Host installiert und gaukelt den Gastbetriebssystemen die notwendige Hardware wie CPU, RAM und Festplattenkapazitäten vor. Das Gastbetriebssystem selbst bemerkt nicht, dass es nur auf rein virtuelle Hardware zugreifen kann. Die Gastsysteme sind voneinander unabhängig, wodurch in vielen Fällen auch die Sicherheit erhöht werden kann. Durch die Virtualisierungssoftware kann genau geregelt werden, welche Ressourcen den Gastsystemen zur Verfügung stehen. Dadurch können Sie ein eigenes Testsystem installieren, in dem Sie sich nach Belieben austoben können. Ihr Hostsystem wird dabei nicht verändert und auch dort bereits installierte MySQL-Server bleiben unbehelligt.

Schematische Darstellung der Virtualisierungsschichten

In der folgenden Anleitung dient ein Laptop mit Windows 7 als sogenannter Host in dem die virtuelle Maschine ausgeführt werden soll. Als virtuelle Maschine selbst  wird Debian installiert, da in unserem Buch auf diese Linux-Distribution am detailliertesten eingegangen wird. Die Anleitung lässt sich ohne viele Änderungen auch auf einen Linux Host übertragen. Ebenso können Sie jede beliebige Betriebssystemvariante als Gastsystem sowohl unter einem Windows Host als auch unter einem Linux Host einrichten. Sie finden zu jedem der nun folgenden Schritte Screenshots, bitten scollen Sie nach Bedarf einfach runter!

Bevor es losgeht…

Sie benötigen folgende Softwarekomponenten:

• Host Betriebssystem (in diesem Beispiel Windows 7)
• VirtualBox, passend für Ihren Host (kostenloser Download)
• Linux Abbild (ISO, beispielsweise Debian als ebenfalls kostenloser Download)
• Putty (ein SSH Client für Windows, kostenloser Download)

VirtualBox installieren

Die folgende Bildergalerie beschreibt wie Sie VirtualBox installieren und darin das Debian Betriebssystem einrichten können. Folgende Schritte sind dabei notwendig:

1. Laden Sie VirtualBox, Putty und Debian herunter
2. Starten Sie die Installation und folgen Sie den Anweisungen
3. Legen Sie eine neue Virtuelle Maschine an
   1. Legen Sie den Namen der Maschine fest
   2. Wählen Sie als Typ Linux
   3. und bei der Version Debian in der passenden Systemarchitektur (32- oder 64 Bit)
4. Wählen Sie die Größe des Hauptspeichers
   1. Sie sollten nie mehr als die Hälfte des am Host verfügbaren RAMs wählen
   2. Sie können diesen Wert später jederzeit anpassen, beginnen Sie mit 512 MB RAM
5. Erzeugen Sie eine neue Festlatte mit beispielsweise 8GB Speicherplatz
   1. Verwenden Sie als Typ VDI
   2. Setzen Sie dynamische alloziierten Speicherplatz ein, so sparen Sie Platz auf der Host Festplatte
   3. Vergeben Sie einen Dateinamen in dem die Virtuelle Platte gespeichert werden soll
6. Die neue virtuelle Maschine ist angelegt, jetzt müssen Sie noch das eigentliche Betriebssystem installieren.
7. Klicken Sie auf Ändern und wählen Sie Massenspeicher
   1. Hier können Sie für die virtuelle Maschine ein CD-Laufwerk anlegen
   2. In dieses Laufwerk legen Sie – natürlich ebenfalls nur virtuell – das zuvor heruntergeladene Debian image.
   3. Klicken Sie dazu auf das kleine CD-Symbol mit dem Plus neben dem IDE-Controler
   4. Und wählen danach die ISO-Datei aus.
   5. Bestätigen Sie mit OK.
8. Starten Sie die virtuelle Maschine
   1. Der Startbildschirm von Debian erscheint.
   2. Anleitungen gibt es hier oder hier.
   3. Installieren Sie das Betriebssystem mit den Standardeinstellungen
   4. Sie benötigen keine Desktopumgebung, das spart Speicher und Ressourcen
   5. Wählen Sie den SSH Server und die Standardtools
   6. Nachdem Sie die Installation erfolgreich abgeschlossen haben haben, können Sie die virtuelle CD wieder aus dem System entfernen.
   1. Schalten Sie die VM aus.
9. Damit ihre Debian VM auf das Internet zugreifen kann, müssen Sie die Netzwerkeinstellungen anpassen.
   1. Wählen Sie dazu wieder Ändern und öffnen das Menü Netzwerk
   2. Dort setzen Sie im Reiter Adapter 1 den Wert von Angeschlossen an von NAT auf Netzwerkbrücke.
   3. Dadurch kann der Gast auf der Netzwerk des Hosts zugreifen und somit auch eine eigene Internetverbindung aufbauen.
   4. Sie benötigen diese Internerverbindung um Pakete zu installieren und das System aktuell zu halten
10. Starten Sie nun erneut die virtuelle Maschine.
    1. Melden Sie sich als root mit dem während der Installation angegebenen Passwort an
    2. Zeigen Sie die aktuelle Netzwerkadresse des Adapters 1 (eth0) an: ifconfig
    3. Notieren Sie sich die IP Adresse.
11. Installieren Sie unter Windows die Software Putty SSH
    1. Mit diesem Tool können Sie sich mit der VM verbinden, indem Sie die notierte IP-Adresse sowie die Benutzerkennung root und das von Ihnen gewählte Passwort angeben.
    2. Putty ist komfortabler als die Eingabemaske der Virtualbox Software
    3. Loggen Sie sich erneut als root ein
12. Installieren Sie MySQL
    1. Entweder Sie folgen der detaillierten Anleitung aus dem Buch oder
    2. Sie können MySQL ganz einfach mit dem integrierten apt Tool von Debian installieren. Da Debian standardmäßig versucht, die zu installierenden Pakete von einer CD zu laden, müssen Sie dieses Verhalten noch kurz unterbinden. Öffnen Sie dazu die apt Quellendatei sources.list mit einem Texteditor und kommentieren Sie die Zeile beginnend mit „cd rom“ aus, indem Sie eine Raute (#) voranstellen.
    3. Beispielsweise mit nano [bash]nano /etc/apt/sources.list[/bash]
    4. Speichern und schließen Sie die Datei.
    5. Installieren Sie MySQL mit [bash]apt-get install mysql-server[/bash]
    6. Beachten Sie, dass in den Debian-Quellen nur die Version MySQL 5.5 verfügbar ist! Details dazu stehen im Buch
13. Passen Sie die Installation an
    1. Aus Sicherheitsgründen können Sie nur von dem Rechner aus auf den MySQL-Server zugreifen, auf dem er auch installiert ist.
    2. Da der Host jedoch ebenfalls auf den MySQL-Server zugreifen können soll, müssen Sie eine Einstellung in der Datei /etc/mysql/my.cnf ändern.
    3. Öffnen Sie diese Datei wieder mit nano [bash]nano /etc/mysql/my.cnf[/bash] und kommentieren Sie folgende Zeile mit einem # ein.  [bash]# bind_address 127.0.0.1[/bash]
    4. Starten Sie den MySQL-Server neu: [bash]service mysql restart[/bash]
    5. Dadurch kann jeder Rechner auf die MySQL-Installation zugreifen
    6. Auf einem Produktivsystem ist eine solche Einstellung nicht empfehlenswert!
    7. Ebenfalls aus Sicherheitsgründen ist der Zugriff des root Users ebenfalls auf den lokalen Rechner beschränkt. Verbinden Sie sich von der Putty-Konsole mit dem MySQL-Server: [bash]mysql -u root -p[/bash]
    8. und geben Sie das gewählte Passwort ein.
    9. Geben Sie den folgenden Befehl ein um die Berechtigungen neu zusetzen: [sql]GRANT ALL PRIVILEGES ON *.* TO ‚USERNAME’@’%‘ WITH GRANT OPTION;[/sql]
    10. Bestätigen Sie die Änderungen mit [sql]FLUSH PRIVILEGES;[/sql]
14. Fertig!
    1. Jetzt können Sie sich einfach mit dem MySQL-Server der in der virtuellen Maschine läuft verbinden.
    2. Beispielsweise mit der MySQL Workbench, die Sie unter Windows installieren können.

     

Screenshots

Was passiert, wenn ein Delfin und ein Elefant aufeinander treffen? Was hat das alles mit den aktuellen Schlagwörtern Big Data, MapReduce und Hadoop zu tun? Das erfährst Du in diesem Artikel.

Der (derzeit überbeanspruchte) Begriff Big Data bezieht sich auf sehr große Datenmengen, die etwa in sozialen Netzwerken wie Facebook oder Twitter produziert werden. So entstehen bei Facebook beispielsweise tagtäglich 500 Terabyte neuer Daten, gesamt müssen mehr als 100 Petabyte an Daten analysiert werden (1 Petabyte = 1.000.000 Gigabyte). Die Analyse dieser Datenmengen, die bis zu mehreren Petabytes groß werden können, ist mittels „normaler“ Hardware nicht mehr zu bewältigen. Um beim Beispiel von Facebook zu bleiben: derartige Analysen berechnen Details zum Userverhalten oder der Vernetzung zwischen Usern, damit beispielsweise zielgerichtet neue Freunde vorgeschlagen werden können. Bereits 2004 haben Jeffrey Dean und Sanjay Ghemawat (beide arbeiten bei Google) MapReduce vorgestellt, das ein neues Paradigma für die hochverteilte Berechnung und Analyse großer Datenmengen zur Verfügung stellt. Dabei werden die Daten auf viele Rechner verteilt und die Berechnung wird gleichfalls in kleine Einzelberechnungen aufgesplittet, die dann sehr einfach parallel berechnet werden können. Durch diese Verteilung auf viele verschiedene Rechner kann der Analyseprozess skaliert werden, indem die die Anzahl an (günstigen) Rechnern und damit die Gesamt-Rechenpower erhöht wird. Das derzeit populärste System, das MapReduce umsetzt, ist Apache Hadoop. Hadoop stellt einerseits ein verteiltes Dateisystem und darauf aufbauend MapReduce-Funktionalitäten zur Verfügung. Der hohe Verteilungsgrad gepaart mit der sehr einfachen Handhabung sind das Erfolgsrezept von Apache Hadoop. Auf eine nähere Beschreibung von MapReduce und Hadoop möchten wir hier verzichten, da es dazu mehr als ausreichend Literatur bereits gibt.

Du fragst Dich jetzt bestimmt, was das alles überhaupt mit MySQL zu tun haben soll, oder? Diese Frage ist sehr einfach beantwortet: in der Datenverarbeitungs-Pipeline steht meist auch MySQL, in dem die Produktivdaten primär gespeichert werden. Hadoop ist keine Datenbank und kann deren Aufgaben nicht übernehmen, daher werden die Daten meist zunächst in MySQL gespeichert und in einem weiteren Schritt in das Hadoop-eigene Dateisystem HDFS (Hadoop Distributed File System) exportiert und dort in weiterer Folge parallelisiert analysiert. MySQL schreibt im Whitepaper „MySQL and Hadoop-Big Data Integration“, dass bis zu 80% aller Hadoop-Installationen in Kombination mit MySQL verwendet werden. Zu diesem Import von Daten aus MySQL in Hadoop stellen Apache und MySQL zwei Tools zur Verfügung: Apache Sqoop und Apache Applier, die wir im Folgenden vorstellen möchten.

Apache Sqoop

Apache Sqoop wurde von Cloudera, einer auf Hadoop-Produkte spezialisierten IT-Firma entwickelt und dient dazu, große Datenmengen von relationalen Datenbanksystem nach HDFS zu importieren (bulk loading) und die Analyseergebnisse von Hadoop auch wieder zurück in die MySQL-Datenbank zu schreiben. Sind die Daten erst mal in HDFS gespeichert, ist es natürlich möglich, diese Daten für MapReduce-Jobs, mit Hive oder auch HBase zu nutzen. Sqoop unterstützt dabei MySQL, aber beispielsweise auch Oracle, Postgres, Netezza oder Teradata. Um Dir kurz zu zeigen, wie einfach dieser Export von MySQL nach HDFS funktioniert, hier ein kurzes Beispiel für den Import einer Tabelle von MySQL in Hadoop:

sqoop import –connect jdbc:mysql://mysql_host/db_name –table tablename –hive-import

Analog können auch ganze Datenbanken von MySQL in HDFS importiert werden. Für den Export der Daten aus HDFS (nach beispielsweise MySQL) stellt Sqoop ebenfalls Funktionen zur Verfügung, mit denen etwa Ordner aus HDFS in Tabellen exportiert werden können.

Applier

Falls es für Dich nicht ausreicht, große Datenmengen beispielsweise einmal am Tag in die Hadoop-Infrastruktur zu importieren, sondern die Daten in HDFS stets aktuell sein sollen, stellt MySQL mit dem Applier das richtige Tool zur Verfügung. Applier verwendet für den Austausch das MySQL-Binlog, das wir im Buch bereits ausreichend beschrieben haben. Das Laden von Daten in HDFS funktioniert ähnlich wie Replikation in MySQL – Hadoop liest die geänderten Daten aus dem Binlog des entsprechenden MySQL-Servers aus und schreibt diese in Hadoop, wie man auch in der folgenden Abbildung erkennen kann:

Applier-Architektur (Quelle: https://innovating-technology.blogspot.co.at/2013/04/mysql-hadoop-applier-part-1.html)

Mit dieser Replikation in das HDFS-Dateisystem kann sichergestellt werden, dass die Daten in HDFS real-time upgedated werden und so alle Hadoop-Analysen stets auf den aktuellsten Daten ausgeführt werden.

Weiterführende Links

• MySQL and Hadoop Integration
• MySQL Applier for Hadoop: Real Time Data Export from MySQL to HDFS (Teil 1, Teil 2)
• MySQL and Hadoop: Guide to Big Data Integration
• Highlights of Scoop 2

Abbildung 1: Connections im Startbildschirm

Was nach dem Starten der neuen Version direkt auffällt, ist das neue Design. Übersichtlich werden die gespeicherten verfügbaren gespeicherten Connections aufgelistet.

Im Folgenden werden wir nun die wichtigsten und hilfreichsten Neuerungen Schritt für Schritt präsentieren.

Queries formulieren

Im Abfrage-Screen wird beim Verfassen einer Abfrage in der neuen  Version auf der rechten Seite Syntax-Hilfestellung gegeben. So wird passend zur aktuellen Abfrage die Syntax des entsprechenden Befehls angezeigt. Besonders hilfreich ist dies beim Verfassen von Gespeicherten Funktionen oder Prozeduren, in denen oft Statements verwendet werden, die man eventuell nicht so oft verwendet und deren Syntax man nachschlagen müsste – dies wird jetzt von der Workbench übernommen (siehe Abbildung 2).

Abbildung 2: Context Help

 Administration

Abbildung 3: Administration und Schemata

Bisher waren die Administration und die normalen Abfrage-Funktionalitäten komplett getrennt, man musste sich über zwei verschiedene Interfaces zum Server connecten, um entweder Abfragen auszuführen oder den MySQL-Server administrieren zu können. Dies wurde in der neuen Version geändert, was man auch direkt beim Connecten zu einem Server sehen kann, da auf der linken Seite des Screens einerseits (wie bisher) die verschiedenen Schemata, andererseits aber auch Management-Funktionalitäten angeboten werden. Diese beinhalten das Abrufen und Anzeigen der Server-Auslastung, der Anzahl der offenen Connections, System-Variablen und -Einstellungen, aber auch das gesamte User- und Rechtemanagement und Import- und Export-Funktionalitäten  (siehe auch Abbildungen 3 und 4).

Abbildung 4: Server Status

Abbildung 5: Schema Inspector – Maintenance

Schema Inspector

Ein neues Feature ist der sogenannte „Schema Inspector“, der es ermöglicht, sehr einfach einen Überblick über die Tabellen, Indexe, Triggers, etc. einer Datenbank zu bekommen. Dabei werden beispielsweise die Anzahl der Zeilen, der aktuelle Auto Increment-Stand oder auch die Größe der Tabelle angezeigt. Sehr bequem ist dabei die sogenannte „Maintenance“-Funktion, mit Hilfe derer Sie mit einem Click für jede Tabelle ANALYZE, OPTIMIZE, ChECK oder CHECKSUM aufrufen können, wie Abbildung 5 zeigt.

Abbildung 6: Visual Explain

Visual Explain

Eine sinnvolle – und optisch sehr ansprechende – Neuerung sind sogenannte Visual Explains. Dabei werden Ausführungspläne, die man bisher mithilfe des EXPLAIN-Statements analysiert hat, graphisch aufbereitet und übersichtlich dargestellt. So können Sie etwaige Optimierungen mit einem Blick erkennen!

Textsuche über alle Tabellen

Ein sehr praktisches neues Feature ist die Möglichkeit, über ganze Datenbanken hinweg nach Strings und auch Patterns zu suchen. Damit können Sie sehr einfach nach Datensätzen suchen, bei denen Sie sich nicht mehr sicher sind, wie diese im Detail aufgebaut waren, welche Daten sie beinhaltet haben oder in welcher Tabelle diese gespeichert wurden. Auch sehr nützlich ist diese Funktion, wenn Sie nähere Einblicke in eine neue oder importierte Datenbank bekommen möchten.

Migration

Die neue Workbench-Version bietet einen Migration Wizard an, der umfangreiche Hilfestellung bei der Migration von Datenbanken aus anderen DB-Systemen bietet.

Folgende Datenbanken werden dabei unterstützt:

• MS SQL Server 2000, 2005, 2008, 2012
• Sybase Adaptive Server Enterprise
• PostgreSQL
• Sybase SQL Server
• SQL Anywhere
• SQLite

Weitere Neuerungen

• Hilfestellung bei cascaded deletes: für angeführte Reihen wird automatisch das cascading delete-Statement berechnet und kann direkt ausgeführt werden, ohne dass Sie ggf. mühsam die Reihenfolge der Löschoperationen festlegen müssen.
• PDF-Druck von ER-Modellen wurde verbessert
• Die \G-Option der Kommandozeile zur vertikalen Ausgabe von Abfrageresultaten (eine Ergebnis-Spalte pro Zeile) wurde nun auch in die Workbench übernommen, damit können Abfragen über viele Spalten übersichtlich ausgegeben werden (Tipp: der Shortcut für das Ausführen einer Abfrage mit diesem Ausgabeformat ist: <STRG + ALT + ENTER> statt des bisher üblichen <STRG + ENTER>, das immer noch das bisher gewohnte Ausgabeformat verwendet)
• Für oft verwendete Schemata(teile) können jetzt Templates angelegt werden, mit denen Sie dann neuen Tabellen schnell über die Templates die entsprechenden Spalten hinzufügen können.
• Die Synchronisation zwischen ER-Modellen und den entsprechenden Datenbanken wurde verbessert und optimiert.

Fazit

Die neue Version der MySQL-Workbench ist ein vollwertiges Tool für alle Bedürfnisse eines MySQL-Benutzers und auch Administrators. Die Neuerungen der Version 6.0 sind durchdacht und erleichtern das Leben eines MySQL-Benutzers. Die neu gestaltete Oberfläche ist intuitiver, reagiert schneller und optisch ansprechend.

Weitere Informationen:

• MySQL Workbench
• Oracle’s MySQL-Blog
• The MySQL Community Workbench Blog
• White Paper: MySQL Workbench: Database Design. Development. Administration. Migration.

Die zweite überarbeitete Auflage: MySQL 5.6 – Das umfassende Handbuch

Nach einer intensiven Überarbeitungsphase ist es endlich soweit! Wir freuen uns, die zweite Auflage unseres Buches zur aktuellsten MySQL-Version 5.6 präsentieren zu können! Natürlich haben wir neben vielen kleinen Anpassungen auch alle neuen Features der MySQL-Version 5.6, wie zum Beispiel die topaktuelle NoSQL-Schnittstelle oder der neue Volltextindex der InnoDB-Engine, behandelt.

Weitere Informationen bei Galileo oder Amazon

In der zweiten Ausgabe lesen Sie neu:

• wie Sie MySQL effizient und performant über die NoSQL-Schnittstelle bedienen
• wie ein Volltext-Index nun auch (endlich) für InnoDB-Tabellen zur Textsuche eingesetzt werden kann
• wie Sie mit serverseitigem JavaScript über Node.js MySQL einsetzen können
• wie Sie über die neuen Sicherheits-Features Ihr System noch besser und komfortabler absichern können
• wie Sie über das erweiterte Performance-Schema Performance-Bremsen in Ihrem System aufspüren und den Turbo zünden können

Umkreissuche befasst sich – wie der Name bereits verrät – mit der Suche von Punkten innerhalb eines gewissen Umkreises. So können beispielsweise alle Orte berechnet werden, die sich in einem Umkreis von 200 km um Innsbruck befinden. Eine solche Suche lässt sich mit MySQL mit einfachen Mitteln realisieren. Grundlage dafür ist eine Tabelle, die die Koordinaten all jener Punkte enthält, die durchsucht werden sollen. MySQL stellt dazu im Rahmen der GIS-Erweiterung den Datentyp POINT zur Verfügung, der es erlaubt, Punkte die durch X- und Y-Koordinaten lokalisiert sind, zu speichern. Im folgenden Beispiel werden die Koordinaten stets durch Grad Länge und Breite definiert (WGS-Referenzsystem). In einem konkreten Beispiel erfolgt die Erzeugung einer solchen Tabelle bzw. Spalte wie folgt:
[sql]
CREATE TABLE geodaten(
stadt VARCHAR(30) NOT NULL,
geolokation POINT NOT NULL,
SPATIAL INDEX (geolokation)
) ENGINE=MyISAM;
[/sql]
Die Spalte geolokation beinhaltet dabei Werte des POINT-Datentyps. Wichtig dabei ist, dass ein räumlicher (spatial) Index für die Spalte mit den Koordinaten angelegt wird. Dieser Index wird in MySQL als sogenannter R-Baum realisiert und erlaubt performante Abfragen auf räumliche Daten. Näheres zum R-Baum findest Du im Buch. Ein solcher R-Baum ist allerdings nur in der Storage Engine MyISAM verfügbar, daher musst Du die Tabelle basierend auf dieser Storage Engine erzeugen.

Beim Einfügen von Daten in die geodaten-Tabelle werden basierend auf den X- und Y-Koordinaten (in Grad Breite bzw. Länge angegeben) der jeweiligen Punkte POINT-Objekte erzeugt und eingefügt:
[sql]
INSERT INTO geodaten(stadt, geolokation)
VALUES (‚Innsbruck‘, POINT(47.26027800, 11.343889));
[/sql]

Vorausgesetzt Du hast die Tabelle nun mit ausreichend Daten gefüllt, kannst Du – wie folgt beschrieben – eine Umkreissuche realisieren und so beispielsweise alle Orte berechnen, die in einem Umkreis von 200 km zur Stadt Innsbruck liegen. Eine sehr einfache Berechnung der Distanz zweier Orte beruht auf dem Satz von Pythagoras (a² + b² = c²). Prinzipiell lässt sich die Vorgehensweise zur Umsetzung einer Umkreissuche in folgende Schritte gliedern:

• Aufspannen eines Suchrechteckes um den Ausgangspunkt
• Berechnung der Distanz zwischen Ausgangspunkt und allen im Suchrechteck liegenden Punkten
• Verfeinerung des Ergebnisses

Diese Schritte werden im Folgenden durchgeführt und erklärt. Zu Beginn müssen der Ausgangspunkt der Umkreissuche und der gewünschte Umkreis (in Kilometern) festgelegt werden. Dazu werden die Variablen @ibk_laenge und @ibk_breite mit den Koordinaten von Innsbruck und die Variable @umkreis mit dem gewünschten Umkreis gefüllt. Im Anschluss werden die Eck-Koordinaten des Suchrechtecks festgelegt. Bei einem gewünschten Umkreis von 200 km soll das Rechteck dementsprechend 200 km nach Westen, Osten, Norden und Süden reichen. Das Suchrechteck wird in Grad berechnet, der Umkreis liegt in diesem Fall allerdings in Kilometern vor. Daher müssen diese Einheiten umgerechnet werden. Breitengrade lassen sich dabei ganz einfach umrechnen: 1 Grad entspricht 111 Kilometern. Die Umrechnung der Längengrad ist etwas kniffliger: diese hängt nämlich vom jeweiligen Breitengrad ab. So liegen zwei zwei Längengrade näher am Pol — in Kilometern gerechnet — weniger weiter auseinander als zwei Längengrade in der Nähe des Äquators. Dies wird berücksichtigt, indem zusätzlich durch den Cosinus des Radians der Breite (Absolutwert) dividiert wird.

Diese oben beschriebenen Schritte können in MySQL wie folgt realisiert werden:

[sql]

SET @ibk_laenge = 11.343889;
SET @ibk_breite = 47.26027800;
SET @umkreis = 200;
SET @breite_nord = @ibk_breite + (@umkreis / 111);
SET @breite_sued = @ibk_breite – (@umkreis / 111);
SET @laenge_west = @ibk_laenge –
(@umkreis / (ABS(COS(RADIANS(@ibk_breite))) * 111));
SET @laenge_ost = @ibk_laenge +
(@umkreis / (ABS(COS(RADIANS (@ibk_breite))) * 111));

[/sql]

Der nächste Schritt ist das Erzeugen des Suchrechteckes, das mittels eines Multipoint-Objects (einem Kantenzug) definiert wird, der die vier bereits definierten Ecken beinhaltet. Dieses Multipoint-Objekt bildet die Grundlage für das Suchrechteck, das mittels des ENVELOPE-Befehls als minimal umspannendes Rechteck definiert wird. Dies ist notwendig, um dann wirklich basierend auf diesem Rechteck die Suche einschränken zu können:

[sql]
SET @mp = CONCAT(‚MULTIPOINT(‚, @breite_sued , ‚ ‚,
@laenge_west, ‚, ‚,
@breite_nord, ‚ ‚,
@laenge_ost, ‚)‘);
SET @quadrat = ENVELOPE(GEOMFROMTEXT(@mp));
[/sql]

Der letzte Schritt ist dann die eigentliche Abfrage. Dabei wird das Suchrechteck verwendet und alle darin befindlichen Punkte selektiert (mithilfe des MBRCONTAINS-Befehls). Zusätzlich wird die Distanz aller im Suchrechteck liegenden Punkte berechnet. Dies geschieht – wie bereits erwähnt – über den Satz von Pythagoras. Beachte allerdings, dass bei dieser Formel die Erdkrümmung nicht in die Berechnung einfließt. Daher ergeben sich bei weiteren Strecken Abweichungen. Falls Du die Erdkrümmung auch berücksichtigen möchtest, sollten Du Dich an die Haversinsche Formel halten.

Wichtig bei der gezeigten Berechnung ist, dass wiederum die Umrechnung der Gradangaben in Kilometerangaben korrekt erfolgt. Dabei wird – wie bereits bei der Berechnung des Suchrechteckes – die Umrechnung der Längengrade von der jeweiligen Breite abhängig gemacht. Darauf basierend werden die Distanz in der Breite und die Distanz in der Länge (beides in km umgerechnet) quadriert (POW-Funktion) und aus der Summe dieser beiden Werte wird anschließend die Wurzel gezogen (SQRT-Funktion).

Durch die Verwendung eines Rechteckes (200 km lang und 200 km breit) als Grundlage für die Suche ist allerdings noch nicht sichergestellt, dass die Distanz zu jedem Punkt in diesem Rechteck kleiner als 200 km ist. So liegen die Eckpunkte beispielsweise weiter als 200 km vom Ausgangspunkt entfernt. Daher muss in einem letzten Schritt nochmals nachselektiert werden. Dazu machen wir uns die bereits berechnete Distanz zunutze und schränken diese mittels einer HAVING-Klausel nochmals ein.

Abschließend noch einmal das gesamte Beispiel:
[sql]
SET @ibk_laenge = 11.343889;
SET @ibk_breite = 47.26027800;
SET @umkreis = 200;
SET @breite_nord = @ibk_breite + (@umkreis / 111);
SET @breite_sued = @ibk_breite – (@umkreis / 111);
SET @laenge_west = @ibk_laenge –
(@umkreis / ABS(COS(RADIANS(@ibk_breite))) * 111);
SET @laenge_ost = @ibk_laenge +
(@umkreis / ABS(COS(RADIANS(@ibk_breite))) * 111);

SET @mp = CONCAT(‚MULTIPOINT(‚, @breite_sued , ‚ ‚,
@laenge_west, ‚, ‚,
@breite_nord, ‚ ‚,
@laenge_ost, ‚)‘);
SET @quadrat = ENVELOPE(GEOMFROMTEXT(@mp));

SELECT (
FLOOR(
SQRT(
POW((@ibk_breite – sub.breite) * 111, 2) +
POW((@ibk_laenge – sub.laenge) * 111 *
ABS(COS(RADIANS(@ibk_breite))),2)
)
)
) AS distanz, sub.stadt
FROM
(
SELECT stadt, Y(geolokation) AS laenge,
X(geolokation) AS breite
FROM geodaten
WHERE MBRCONTAINS(@quadrat, geolokation)
) AS sub
HAVING distanz <= @umkreis
ORDER BY distanz;
[/sql]

Vor kurzem habe ich für eine Datenbanken-Lehrveranstaltung einen möglichst realitätsnahen Test-Datensatz zusammengestellt. Dafür habe ich über eine API einfach direkt Artikel eines Produktkatalogs angefordert und in MySQL gespeichert. Diese API lieferte allerdings viele der Produkte mehrfach zurück und natürlich wollte ich keine Duplikate speichern. Was macht man in so einem Fall? Natürlich – man verwendet INSERT IGNORE. Diese spezielle Art des INSERTS gibt beim Versuch, einen bereits in der Tabelle vorhandenen Datensatz einzufügen, nur eine Warning zurück und fügt den neuen Datensatz nicht ein.

Dabei fiel mir auf, dass bei einem INSERT IGNORE der Zähler für das auto_increment auch erhöht wird, wenn die einzufügende Zeile ignoriert bzw. verworfen wird. Es wird also in Wirklichkeit kein weiterer Datensatz hinzugefügt, trotzdem wird der auto_increment Zähler weitergezählt. Dieses Verhalten führte zu einer großen, durchlöcherten ID-Spalte. Im folgenden Beispiel kann man gut erkennen, wie nach zwei „erfolglosen“ Einfüge-Operationen (der Hitchhiker ist in der Tabelle schon vorhanden), für das nächste eingefügte Element die ID 5 vergeben wird, obwohl es in Wirklichkeit erst das dritte eingefügte Element ist.

[sql]

CREATE TABLE `products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) NOT NULL,
PRIMARY KEY (`product_id`),
UNIQUE KEY `idx_name_unique` (`product_name`)
) ENGINE=InnoDB;

INSERT IGNORE INTO products(product_name)
VALUES („Hitchhiker’s Guide“);
INSERT IGNORE INTO products(product_name)
VALUES („Romeo and Juliet“);
INSERT IGNORE INTO products(product_name)
VALUES („Hitchhiker’s Guide“);
INSERT IGNORE INTO products(product_name)
VALUES („Hitchhiker’s Guide“);
INSERT IGNORE INTO products(product_name)
VALUES („Faust I“);

SELECT * FROM products;
+————+——————–+
| product_id | product_name       |
+————+——————–+
| 1 | Hitchhiker’s Guide |
| 2 | Romeo and Juliet |
| 5 | Faust I            |
+————+——————–+
[/sql]

Doch wie kommen nun die Löcher wirklich in den Käse? Der Grund für dieses Verhalten ist, dass InnoDB bereits vor dem Einfügen des Datensatzes den auto_increment Zähler erhöht und damit zu diesem Zeitpunkt einfach noch nicht weiß, ob der Datensatz danach wirklich eingefügt wird oder nicht. InnoDB verwendet standardmäßig den „consecutive lock mode“ für auto_increment Werte. Das bedeutet, dass für jede INSERT-Operation zu Beginn der Operation eine Sperre (siehe auch Mutex) auf den Zähler gesetzt und dieser erhöht wird. Damit wird sichergestellt, dass IDs nicht mehrfach vergeben werden. Alternativ gibt es noch den „traditional lock mode“, der für die Dauer jeder Einfügeoperation eine Sperre auf die gesamte Tabelle setzt. Damit wir der auto_increment Zähler wirklich nur dann erhöht, wenn wirklich ein neuer Datensatz eingefügt wurde (siehe auch MySQL Reference Manual).

Grundsätzlich solltet Ihr euch also nie auf dichte IDs verlassen, wenn Ihr die Daten mit InnoDB, auto_increment und INSERT IGNORE einfügt. Das Gleiche gilt auch für INSERT … ON DUPLICATE UPDATE, auch dort werden von InnoDB die auto_increments fälschlicherweise erhöht.

Falls Ihr trotzdem dichte IDs generieren möchtet (bei mir waren es zugegebenermaßen kosmetische Gründe, die mich dazu veranlasst haben), gibt es die folgenden Möglichkeiten:

• Auf MyISAM wechseln, dort werden in einem solchen Szenario dichte IDs erzeugt
• den InnoDB Lock-Mode auf „traditional“ umstellen mittels
  innodb_autoinc_lock_mode = 0 in der my.cnf oder beim Starten des MySQL-Dienstes. Allerdings muss ich auch erwähnen, dass dieser Locking-Mode natürlich über eine wesentlich schlechtere Performance verfügt, da die Tabelle für jede Einfüge-Operation gesperrt werden muss.