Archiv der Kategorie: MySQL

Rezension: SQL Performance Explained

SQL_Performance_Explained_cover_deutsch_hires[1]

SQL Performance Explained Cover

Bemessen an der Seitenzahl ist das Buch „SQL Performance Explained“ von Markus Winand eher ein dünnes und verliert sich damit in der Reihe der dicken Wälzer im (Fach-)Bücherregal. Lass dich aber von diesem Umstand nicht täuschen. Für „SQL Performance Explained“ kann man locker 2 oder 3 dicke Wälzer wegwerfen.

Dabei lernst du eigentlich nur etwas über einen kleinen Teil von Datenbanken, der sich aber dafür umso mehr bemerkbar macht: Der Index.

Markus erklärt sehr gut und sehr anschaulich, wie so ein Index aussieht, wie er funktioniert und was er bringt – Performance nämlich und das nicht zu knapp. Auf 207 Seiten (mir liegt das PDF vor) erklärt Markus sehr praxisnah und verständlich, vernachlässigt aber auch nicht die Theorie, die einer Datenbank zugrunde liegt.

Du hattest sicherlich Datenbanktheorie im Studium und kannst dich noch stöhnend an die vielen Vorlesungen erinnern, die damit einhergingen. „SQL Performance Explained“ schafft es, bezogen auf die Kernaussage „Performance“, alles in einem übersichtlichen Werk zusammen zu fassen.

Was ich persönlich direkt aus dem Buch in meiner täglichen Arbeit umsetzen konnte kam in Kapitel 2 „Die Where-Klausel“ und zwar war dies die Erklärung der „Zusammengesetzten Schlüssel“. Die Erklärung war einfach und merkbar und so konnte ich schon in mehreren Projekten davon profitieren. So lobe ich mir ein Fachbuch.

„SQL Performance Explained“ ist kein MySQL-Buch. Es ist ein Buch über Datenbanken im Allgemeinen, beinhaltet aber Beispiele u.a. für MySQL oder Oracle oder MS-SQL Server.
Falls ein bestimmtes Beispiel für eine Datenbank nicht funktioniert, weil die Datenbank diesen Befehl nicht unterstützt, dann schreibt Markus das auch gern mit in den Text. Lobenswert!

Insgesamt ein Buch, welches ich uneingeschränkt weiterempfehlen kann und werde. Ich persönlich kann es mir nicht mehr von meinem Desktop wegdenken.

Du bekommst das Buch wahlweise als Print oder PDF oder beides unter sql-performance-explained.de

Großes Lob und danke für’s schreiben, Markus!

Vorsicht bei mysql_connect und Vielfachverbindungen

Eine der automatischen „Goodies“ bei mysql_connect (wen man es denn schon nutzen muss, warum auch immer), ist, dass, wenn man mysql_connect mit den gleichen Informationen bestückt, diese Funktion die gleiche Verbindungsid zurückgibt wie beim ersten mal … es wird also keine zweite Verbindung aufgebaut.

Das Problem ist nun leider, dass der, der die zweite Verbindung öffnen möchte, diese Informationen _ganz genau_ braucht, denn sonst erhält man unter Umständen trotzdem eine zweite Verbindung, obwohl man die vermeindlich gleichen, aber nicht selben Parameter benutzt.

Beispielcode:

$dbserver1 = '127.0.0.1';
$dbserver2 = 'localhost';

$dbuser1 = 'root';
$dbuser2 = 'Root';
$dbpass = '';

$db1 = mysql_connect($dbserver1, $dbuser1, $dbpass);
$db2 = mysql_connect($dbserver2, $dbuser2, $dbpass);
$db3 = mysql_connect($dbserver1, $dbuser2, $dbpass);
$db4 = mysql_connect($dbserver2, $dbuser1, $dbpass);
$db5 = mysql_connect($dbserver1, $dbuser1, $dbpass);

var_dump($db1, $db2, $db3, $db4, $db5);

Ausgabe:

resource(7) of type (mysql link)
resource(9) of type (mysql link)
resource(11) of type (mysql link)
resource(13) of type (mysql link)
resource(7) of type (mysql link)

Hier zeigt sich, dass insg. 4 verschiedene Connection-Ids zurück gegeben wurden, $db5 ist zur Kontrolle und zeigt, dass nur die exakt selben Parameter zum gewünschten Ergebnis kommen, nämlich die selbe ID zu benutzen, die schon bei $db1 erzeugt wurde.
Im Realfall würde dies nun 4 Prozesse bedeuten, von denen 3 überflüssig sind und nur den Server belasten … pro Aufruf im schlimmsten Fall.

webEdition: Bestehende Datenbankverbindung nutzen

In webEdition kann man viel machen, es ermöglicht einem wirklich sehr große Freiheit. Der große Vorteil diese Freiheit hat aber auch einen großen Nachteil: Man muss sich im System auskennen, um damit wirklich gute Seiten bauen zu können; ganz schnell kann man auch sehr langsame Seiten erstellen, die dann nicht nur den Besucher, sondern vor allem den Kunden verärgern.

Aus einem aktuellen Projekt stelle ich eine wirklich böse Fehlerquelle vor: Die mehrfache Datenbank Verbindung. Im Template ist es recht einfach, eine neue DB-Verbindung mit der bekannten (und veralteten) Funktion

$db = mysql_connect(...);

zu erstellen. Aber warum sollte man das machen? Wohl nur aus Unwissenheit, dass webEdition bereits eine Datenbankverbindung eröffnet hat und diese auch dem Entwickler bereitstellt.

Also, liebe webEdition-Entwickler, die bestehende, persistente und performantere DB-Verbindung könnt ihr recht einfach für eigene Zwecke benutzen:

$db = $GLOBALS['DB_WE'];
$stmt = $db->query('SELECT * FROM tblUser');
while ($data = mysql_fetch_assoc($stmt)) {
Zend_Debug::dump($data);
}

Muss man dann doch mal eine zweite Verbindung aufbauen – was im Einzelfall manchmal wirklich sein muss – dann ist dem Entwickler ja meist bewusst, was er da macht und ich hoffe, er benutzt dann dafür nicht die alten, langsamen MySQL-Funktionen, sondern entweder die MySQLi-Pendants oder eine PDO-Schnittstelle.

Leider stellt webEdition keine Instanz von Zend_Db bereit, so dass zwar die Generierung des Querys OOP stattfindet, aber es dannach mit den bekannten mysql-Funktinen weitergeht. Das ist ein recht großer Nachteil, da es die Möglichkeit nimmt, auf einem modernen (aktuellen) Niveau zu arbeiten und ich hoffe, dass dieses Manko bald durch ein aktuelles Release behoben wird.

Zeilenumbrüche in MySQL Text ersetzen

Lästige Zeilenumbrüche können direkt via SQL ersetzt werden, allerdings benutzt MySQL dazu intern zwei Stellen, nicht nur eine. Ein simples suchen nach „\n“ hilft wenig, hier die Lösung:

REPLACE(table.fieldname, CHAR(13,10), ' ') AS newfieldname

Viel Erfolg!

Schneller in PHP und MySQL mit JOIN

Mittels einfacher Kentnisse seiner Datenbank kann der Entwickler auch aus Legacy-Anwendungen sehr viel mehr Geschwindigkeit herausholen, als er vielleicht weiß.
Der Grund ist simpel: Meist bleibt der Code gleich, aber die Server Software wird aktualisiert. Während die Erstellung noch im guten alten PHP4 + MySQL4 von Statten ging, rennt der Code heute mit PHP5 + MySQL5 zwar immer noch, könnte aber dank kleiner Kniffe sehr viel schneller sein.
Ich möchte euch einen Weg dazu zeigen, die JOINs in MySQL. Es gibt sicherlich noch mehr Möglichkeiten, aber das sind auch andere Themen. Fangen wir heute mal mit alten Querys an.

Zunächst versuchen wir eine Stelle zu finden, an der wir ansetzen. Bei den meisten Legacy Codes wurde mit solch einem oder einem ähnlichen Konstrukt die Daten für „Zeige neue Blogbeiträge mit Namen des Autors“ abgerufen, wobei „autor“ und „blog“ zwei verschiedene Tabellen sind.

$_res = mysql_query("SELECT * FROM blog ORDER BY datum DESC");
while ($row = mysql_fetch_array($_res)) {

  $_resAutor = mysql_query("SELECT * FROM autor WHERE autorid = ".$row['autorid']);
  $autor = mysql_fetch_array($_resAutor);
  // Stelle Blogbeitrag mit Autor dar
}

Das ist nicht nur in der Hinsicht des Datenabrufes schlechter Code und vor lauter „code-smells“ könnte einem glatt schlecht werden. Trotzdem gibt es sowas „da draussen“ und dummerweise funktioniert das leider immer noch.

Was kann man verbessern? Zum einen fällt auf, dass es zwei Querys sind. Das muss nicht nur nicht sein, dass ist auch noch ganz schlecht, denn jede Verbindung zur Datenbank braucht Zeit und die sollten wir uns sparen. Argumente wie „mysqli / PDO benutzen“ lasse ich ganz bewusst aussen vor, es soll um Prinzip gehen (sicher wäre die Verwendung eines PDO oder ORM wie Doctrine besser, ganz klar).

Sparen wir uns also den zusätzlichen Query:

$_res = mysql_query("SELECT b.*, a.* FROM blog b, autor a WHERE b.autorid = a.autorid ORDER BY b.datum DESC");
while ($row = mysql_fetch_array($_res)) {
  // Stelle Blogbeitrag mit Autor dar
}

Schon viel besser, aber noch nicht gut genug. Was passiert, wenn ein Blogbeitrag existiert, aber der Autor nicht? Bei so einer Legacy-Anwendung fast schon der Normalfall. Der ganze Beitrag fehlt. Doof, also brauchen wir eine Mechanik, die trotz fehlendem Autor den Blog Beitrag noch anzeigt.
Ganz kurz: Das machen JOINs – und ehe ich nun von vielen gesteinigt werde: JOINs machen noch viel mehr, aber das würde hier den Rahmen sprengen und außerdem möchte ich dazu noch mehr schreiben.

Das ganze nun mit einem LEFT JOIN:

$_res = mysql_query("SELECT b.*, a.* FROM blog b LEFT JOIN autor a ON (b.autorid = a.autorid) ORDER BY b.datum DESC");
while ($row = mysql_fetch_array($_res)) {
  // Stelle Blogbeitrag mit Autor dar
}

Es ändert sich nicht viel, aber nun erscheinen auch alle Blogbeiträge ohne Autor und als kleines Extra arbeitet der letzte Query auch noch etwas schneller als der zweite; super für Legacy-Code, der schon ähnlich wie in Beispiel Zwei aufgebaut ist, denn dort muss man nicht – oder nur wenig – an den PHP Code ran und kann sich auf die reine SQL-Optimierung konzentrieren.

Diese Art der Optimierung alter Legacy-Anwendungen macht relativ wenig Arbeit und bringt dafür recht viel. Vor allem im Bereich von SQL-Code, wo Beispiel eins mehrfach vorkommt (Grundquery, dannach werden viele Querys gestartet die Detaildaten zum Grundquery abrufen, dannach wird gerechnet und wieder neue Querys abgerufen usw.) und damit die Anwendung an dieser Stelle nur sehr langsam ist, kann ein Umstieg auf JOINs und u.a. Verlagerung von Arbeit von PHP in den Query sehr viel Geschwindigkeit herausholen.

Allerdings – und das sollte ganz klar sein – kann dies keine schlechte Architektur ersetzen. Legacy Code kommt irgendwann an den Punkt, an dem ein Optimieren keinen Sinn mehr macht und man sich lieber auf die Neukonzeption konzentrieren sollte. Als Hilfsmittel, um z.B. langsame Bereiche zu beschleunigen, sollte man die Kentnisse allerdings auffrischen. Vor allem, da bei modernen Methoden (PDO, OML, …) die JOINs eine zentrale Rolle spielen.

sprintf und float-Zahlen in mySQL-Querys

Floats, also Fließkommazahlen, in eine DB zu schreiben sollte man können. Dachte ich bis vor kurzem 😉

Und deshalb, vor allem als Hinweis an mich selbst: Falls ein Query mit einem – vorher bereits vom Punkt befreiter, das Komma in Punkt umgewandelten – String plötzlich nicht mehr korrekt ausgeführt wird, dann liegt das höchstwahrscheinlich an zwei Sachen; allein, eine war mir dann neu:

  1. Du übergibst deinem Query die Parameter via sprintf (oder äquivalenten davon) und (jetzt kommt das neue)
  2. dein Server spricht plötzlich Deutsch!

Um einen fehlerhaften Query aber dauerhaft zu vermeiden, setze ich vor meinen Query nun die Sprache des Systems auf die Sprache der Datenbank und dannach wieder zurück. Den Quellcode habe ich stark vereinfacht mal hier angefügt.


// Wechsel zur internen Sprache der DB, als Beispiel fest im Code
$old = setlocale(LC_NUMERIC, NULL);
setlocale(LC_NUMERIC, 'us_US');

// Query ausführen. Werte im Query werden via sprintf eingegeben
// ...

// Zurück zur normalen Einstellung
setlocale(LC_NUMERIC, $old);

Ich hoffe, ich kann damit mal jemanden aus der Patsche helfen, wahrscheinlich bin ich aber wieder mal selbst mein bester Kunde 😉

Alles loggen, alles!

Ich stieß kürzlich auf diesen Artikel:
Log Everything All the Time
und fand diesen sehr interessant.

Es geht um’s mitloggen von Informationen und der damit verbundenen verbesserung der Debugfähigkeit von Software für dessen Entwickler. Das ganze klingt für mich sehr interessant, vor allem der Punkt, dass man (theorethisch) jeden Zeitpunkt zu jeder Zeit nachstellen kann – klingt sehr nach ZendServer 😉 – wäre für mich zumindest manchmal sehr wünschenswert.

Allerdings kann ich mich gut vorstellen, da so ein logging sehr an der Performance kratzt, aber der Autor gibt weiter unten im Artikel sehr nützliche Tipps, wie man das maximum herausholen kann, wenn man nur schon beim Design ein paar – wie ich finde fundemantal wichtige – Regeln beachtet, z.B. „Formatiere nichts, bis es nicht gebraucht wird“ oder „Nur ein Tabellenlookup pro Log“.

Von mir aus kommt da noch eins hinzu: „Benutze keine Frameworks!“. Au weia, jetzt hauen mich alle Zend-Freaks. Ich will damit sagen, dass beim Thema ‚logging‘ auch die Zeit eine sehr kritische Rolle spielt – wir reden hier ja nicht über ein oder zweimal loggen pro Aufruf, sondern jeden Request, in jeder Funktion, in jeder Klasse, quasi in jeder Zeile – und da zählt jede Nanosekunde. Frameworks haben diesbezüglich aber einen Nachteil: Sie haben Overhead.

Wie auch immer, ich würde gern eine Klasse für dieses Logging entwickeln und würde mich freuen, von euch Anregungen zu bekommen, was diese Klasse machen soll, wie Sie aufgerufen werden sollte (instanz, static, …) und wie und wo die Daten gespeichert werden sollten (spontan kommt mir da mysql in den Sinn, wegen durchsuchen usw.). Ich denke, wenn wir alle Ideen sammeln, können wir eine gute Logging Klasse entwickeln, die alle diese Informationen in einem guten Zeitrahmen erfasst und speichert. Das auslesen kann ja von mir aus wieder längern dauern, da kommt es nicht so auf die Zeit an.

*hm* Eventuell gibt es ja schon so eine Klasse, irgendwo, allein oder versteckt in einem Framerwork. In dem Fall bitte ich um einen kurzen Hinweis, dann kann ich mir die Arbeit sparen und direkt produktiv loggen.

Was denkt ihr über den Artikel? Was haltet ihr von so einer Klasse?

P.S.: Sorry, die Klasse soll primär für PHP sein, die Ideen natürlich für alle!

Mehrere MySQL-Tabellen mit ähnlichen Inhalten in einer Abfrage

Mehrere MySQL-Tabellen mit ähnlichen Inhalten in einer Abfrage abhandeln und das ganze dann auch noch am besten mit dem gleichen Code durchlaufen, obwohl in den Tabellen zwar ähnliche, aber nicht gleiche Daten stehen? Dazu noch eine Art „Flag“, dass man weiß, in welcher Zeile ein Ergebnis aus Tabelle1 und in welcher es aus Tabelle2 kommt? Kein Problem…

Sicherlich, man könnte nun die Tabellen einzeln abfragen und ebenso einzeln abhandeln, aber, hej, das kann jeder, wir, WIR, können das besser. WIR benutzen dazu nicht 2 oder x Abfragen, nur um hinterher zu sehen, dass wir alle Felder gleich behandeln, bis auf eins. WIR machen dann sowas wie hier 😉

Die Vorrausetzungen nochmal:

  • Ich brauche x Felder aus der Datenbank aus Y Tabellen.
  • Von diesen x Feldern sind x-1 Felder gleich, auch im späteren Code werden x-1 Felder völlig gleich abgearbeitet.
  • Die Spaltennamen in der Datenbank sind für diese X Felder nicht gleich, die Inhalte bzw. deren Signatur bzw. deren Datentypen aber schon.
  • Ich normalisiere die x Spalten aus den y Tabellen
  • Ich verkette diese y Tabellen miteinander nacheinander.

Zauberwort hierbei heißt „normalisieren“ und „verketten“. Zuerst funktioniert das ganze nur dann, wenn man zum einen die gleiche Anzahl Spalten hat und zum anderen die Spalten selbst auch den gleichen Typ und den gleichen Namen besitzen.

Gleiche Anzahl und (Daten)Typ, dass müsst _ihr_ sicherstellen; beim Namen kann man ja mit dem Zauberwort AS etwas „tricksen“ 😉

Trickreich wird es allerdings beim Punkt „wissen, aus welcher Tabelle das ganze kommt“, denn evtl. möchte oder muss man ja doch in der späteren Verarbeitung das eine oder andere Feld entsprechend unterschiedlich behandeln. Dazu gibt es diesen kleinen „Trick“. Man legt einen Spaltennamen für die Spalte fest, in der die Unterscheidung stattfinden soll und schreibt dann in Klammern und Hochkommata einen Bezeichner davor. Der Bezeichner gibt an, was in der Zeile steht.

Beispiel: tabelle1 enthält Produktdaten, tabelle2 die Kategorien, wir wollen u.a. die Produktnamen und die Kategorienamen auslesen und auch wissen, was wir nun haben. Als verkettung benutzen wir UNION bzw. UNION ALL. Die Unterscheidungsspalte nenne ich „herkunft“:

SELECT produkt_name AS name, ("tabelle1")herkunft, ...
FROM tabelle1
UNION ALL
SELECT kategorie_name AS name, ("tabelle2")herkunft, ...
FROM tabelle2
ORDER BY irgendwas

Das Ergebnis sieht dann in etwa so aus:

name herkunft
produkt 1 tabelle1
kategorie 1 tabelle2
kategorie 2 tabelle2
produkt 2 tabelle1
produkt 3 tabelle1
usw. usw.

Nun sehe ich sehr schön, woher die Daten stammen und kann somit die kleinen Unterschiede mittels einer Kondition in die richtigen Bahnen lenken.

Ich hoffe, ich konnte dem ein oder anderen helfen…

Achtung: Newsletter-Problem

Quizfrage: Wie speichere ich folgendes, zugegebenermaßen sehr komplexe und nicht grad oft vorkommende Szenario in einer MySQL-Datenbank ab:

Es soll gespeichert werden, ob ein Nutzer zustimmt, einen Newsletter zu bekommen oder nicht

Ja, ich weiß, realitätsfern und Spezialanwendung, akademischer Aspekt und und und. Schon klar, aber als Denksportaufgabe für angehende Doktoren in Theopraktischer-Quanteninformatik bestimmt ein netter Tagesfüller.

Ein möglicher Ansatz – nochmal: wir reden hier über wirklich experimentelle Ansätze, die noch nie ein Mensch zuvor … usw, also weiter – also ein möglich Ansatz wäre folgender:

Man benutze ein Feld vom Typ varchar. Varchar, weil man nie weiß, ob sich evtl. mal die Anforderung ändern könnte. Stichwort hierbei: Skalierbarkeit! Denn wie schnell ändert sich so eine Anforderung und sollte man dann den falschen Datentypen ausgewählt haben (weil man natürlich mal wieder mit primitiven Ansätzen fern jeder akademischen Laufbahn daran gegangen ist und dachte, man könnte als Laie ein derart komplexes System auch nur im Ansatz verstehen), dann, ja dann dampft die Kacke! Also, varchar.

Und auch die Größe des varchar soll und muss disktutiert werden. Ein sicherlich guter Ansatz wäre, das Feld auf 255 Zeichen zu begrenzen – im Spezialfall kann das natürlich noch nach oben korrigiert werden, was aber wieder zu lasten der Konfiguration der Seitlichen Ausrichtung gehen kann und wird und nicht sollte.

Den Verschiedenen Zuständen geben wir die folgenden Namen: ‚active‘ beschreibt den Zustand des „wollenden Empfängers“ (genaue Definition erfolgt in einer Subklasse) und ‚inactive‘ den des dazu konträhren Zustandes. Für alle anderen wählen wir “ als den wohlbekannten Universaloperator auf der Menge der möglichen Zustände über „Ja“ und „Nein“ (präziser wäre ein oder, was aber im allgemeinen Sprachgebrauch eher verwirrt war und nicht mehr an seinen Platz fand. Das und war so nett und sprang kurzfristig ein.).

Wie man nun sieht, kann man selbst komplexe Vorgänge be- und verarbeiten, wenn man nur ruhig und logisch an die Sache heran geht und die Aufgaben in kleinen Abschnitten betrachtet (ist auch viel einfacher, eine Lupe zu benutzen anstelle eines Verkleinerungsglasses.).

Der Vollständigkeit halber sei noch folgender Zwischenruf erwähnt, der einen Eklat innerhalb des Beratungsgremiums hervorrief, als ein hochnäsig-arronanter Programmierer in geistiger Verwirrung den Satz ausrief:

Man benutzt ein (tiny)int-Feld und 0 und 1 zum speichern der Zustände Nein und Ja!

Unvorstellbar, wie dumm doch manche sind…

FROM_UNIXTIME mit negativen Werten berechnen

Benutzt man die mySQL Funktion FROM_UNIXTIME mit negativen Werten, so gibt diese null zurück (zumindest ab einer Version nach 4.0.x).

Der Tip oder besser Workaround lautet: Berechnet das Datum dann doch bitte in der Anwendung, nicht in der Datenbank.

Ich bin generell kein Freund von Workarounds, da diese das Gefühl von “Hilfskrampe” hinterlassen. Also habe ich mal länger geforscht und bin dabei auf diese Sinnvolle Konstruktion gestoßen, die mir das gewünschte Datum korrekt zurückliefert. Das ganze kann man auch schön in mySQL auch mit DATE_FORMAT formatieren.

Im Prinzip ist es recht simpel: Ist das Feld positiv (inklusive 0), dann einfach die funktionierende Variante benutzen, ist es negativ, dann ausgehend vom UNIX_TIMESTAMP(0) via DATE_ADD in Sekundenschritten zurück gehen.

Der UNIX_TIMESTAMP steht in meinem Beispiel im Feld “geburtstag” und gibt entweder direkt den Wert zurück oder den umformatierten:

IF (geburtstag>=0,FROM_UNIXTIME(geburtstag),DATE_ADD(FROM_UNIXTIME(0),INTERVAL geburtstag SECOND) )

Ich hoffe, ich erspare auf diesem Weg dem ein oder anderen langes suchen.