MySQL Sichten(Views) und Prozeduren (Procedures)

Bevor ich bei meiner jetzigen Firma angestellt war, habe ich mir eingebildet etwas über SQL (MySQL) zu wissen. Dann habe ich jedoch den Microsoft-SQL-Server kennen gelernt. Ich bin generell kein Microsoft Fan, der SQL-Server ist aber schon ein sehr leistungsfähiger Server. Durch die bereits vorhandenen Strukturen musste ich mich erst einmal durchfitzen unter anderem auch durch Sichten und Prozeduren. Aus heutiger Sicht weiß ich nicht, wie ich ohne diese SQL Werkzeuge auskommen konnte.

Kommen wir als erstes zu den Sichten (Views):

Um den Sinn einer Sicht zu verstehen, versuche ich es einmal mit einem Beispiel. (dies ist ein MySQL-Code in anderen SQL-Sprachen weicht dieser aber nicht allzu sehr ab)

CREATE VIEW terms AS
select term.term_id, name, slug, term_group,
taxonomy, description, parent, count
from wp_terms as term
INNER JOIN wp_term_taxonomy as tax
ON term.term_id = tax.term_id

Mit

CREATE VIEW * AS

wird eine Sicht eingeleitet. Ich habe hier mal als Beispiel die WordPress Datenbank genommen um einen JOIN über die Tabellen wp_term_taxonomy und wp_terms zu erstellen. Die Frage die ich mir am Anfang gestellt habe war: „Wozu soll ich das so umständlich machen?“. Man kann die SQL ja auch direkt in den PHP-Quellcode schreiben. Das mag zwar fürs erste stimmen, wenn man das Ganze aber etwas weiter spinnt, wird es vielleicht deutlicher. In WordPress ist es prinzipiell möglich den Tabellenpräfix zu ändern. Wenn ich das im Nachhinein ändere, wird das in der WordPress-Programmierung automatisch erledigt, nehmen wir jetzt aber weiter an, dass ich mir ein Plugin oder eine eigene Seite mit SQL-Abfragen geschrieben habe. Ich müsste jetzt manuell durch alle Abfragen durchgehen und die Tabellen umbenennen. Wenn ich aber Sichten einsetzte muss ich dies nur einmalig zentral ändern und alle Scripte die auf die Sicht zugreifen funktionieren weiterhin. Sichten helfen Redundanz zu vermeiden. Ich muss den SQL-Code nicht mehrmals hinterlegen, sondern kann ihn an einer Stelle ändern. Für Webmaster, die sich bis jetzt nur mit „kleinen“ Seiten auseinander gesetzt haben, ist der Sinn vielleicht nicht offensichtlich, wenn man jedoch eine Webanwendung mit dutzenden von Tabellen hat, lernt man Views sehr schnell zu schätzen.

Als zweites möchte ich Prozeduren vorstellen.

Eine Prozedur ist ein universelle Werkzeug in der täglichen Arbeit mit SQL. Eine Prozedur ermöglicht es viele SQL-Anweisungen in einer Anweisung zusammen zu fassen. Nehmen wir an, dass ich den Umsatz pro Kunde und Monat ermitteln will. Das geht sicherlich recht einfach mit einigen JOINS und einer GROUP BY Klausel. Jedoch müsste ich diese Werte bei jeder Abfrage erneut generieren. Bei 10.000 Kunden mag das vielleicht noch gehen. Bei 1.000.00 Kunden wird es schon recht aufwendig für den SQL-Server. Die Last ist auf jeden Fall enorm hoch. Am einfachsten wäre es doch, diese Zahlen einmal in der Woche (oder täglich) in eine gesonderte Tabelle weg zu schreiben. Genau dass kann man mit Hilfe einer Prozedur und eines Cronjobs machen.

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_umsatz_monatlich`$$
CREATE PROCEDURE `proc_umsatz_monatlich` ()
BEGIN
INSERT INTO umsatz_monatlich
SELECT kunde_id, umsatz
FROM rechnungen
GROUP BY YEAR(datum), MONTH(datum), kunde_id
END$$
DELIMITER ;

Der Vorteil von Prozeduren liegt auf der Hand. Man kann sie mit Variablen füttern und somit das Ergebnis beeinflussen (In einer Prozedur kann man z.B. auch IF Abfragen verwenden). Die Prozedur selbst kann beliebig verwandelt und an neue Gegebenheiten angepasst werden, ohne dass sämtlicher SQL-Code umgeschrieben werden muss. Das hat vor allem Vorteile für z.B. die Suche einer Seite. Diese wir normalerweise mit wenigen Parametern angesprochen (Suchbegriff und ggf. Einschränkungen wie Zeitraum o.ä.) und gibt als Ergebnis eine Tabelle zurück. Ich musste in unsere Suche eine Tabelle zusätzlich einbinden. Das ging ohne eine Zeile im PHP-Quellcode zu verändern, nur indem ich die Prozedur verändert habe. Es gibt Programmierer, vor allem in der professionellen Programmierung, die der Meinung sind, dass ein Client (das Clientprogramm) keinen direkten Zugriff auf die Datenbank haben darf. Das ist ein recht radikaler Ansatz, dem ich mich nicht anschließen möchte. Sinnvoll wäre es aber z.B. dass man dem Client, im Falle der Webprogrammierung dürfte das zumeist „www-data“ sein, nur SELECT oder UPDATE Befehle erlaubt. DROP und DELETE sind nur Prozeduren vorbehalten. Das würde dem Schutz vor SQL-Injection einigen Vorschub leisten. Wenn man sogar soweit geht, die Einschränkung auf UPDATE Befehle auszuweiten, wäre das ein großer Sicherheitsgewinn.
Eine weitere wichtige Funktion von Prozeduren soll das nachfolgende Beispiel verdeutlichen:

CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur CURSOR FOR SELECT id,data FROM test.t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a, b;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END

Man kann in einer Prozedur durch ein Ergebnis einer SQL, Zeile für Zeile durchgehen und die Datenwerte für Bedingungen, INSERT, DELETE oder UPDATE Befehle nutzen. Man kann einen CURSOR ähnlich einer while Schleife benutzen. Das zu den Vorteilen. Der Entscheidende Nachteil ist, die Geschwindigkeit. Wenn ich ein UPDATE Befehle ausführe, selbst wenn es zehntausende Zeilen betrifft, ist der Server in meist weniger als einer Sekunde fertig (Abhängig von der Tabelle, dem Index und weiterer Faktoren). Mit einer CURSOR Operation kann ich prinzipiell die gleiche Funktion ausführen, wie mit dem UPDATE Befehl, diese würde jedoch erheblich länger Dauern. Deshalb ein Wort der Warnung. CURSOR Operationen nur mit Bedacht einsetzen und alle Alternativen vorher ausschöpfen. Als bewährtes Mittel um diesen SQL Befehl zu vermeiden, haben sich für mich temporäre Tabellen erwiesen. Man kann das Ergebnis einer SELECT Anweisung in eine Tabelle speichern und diese dann für weiter gehende Aktionen benutzen.
Dazu fällt mir wieder meine Suche ein. Als erstes erstelle ich eine Temporäre Tabelle mit bestimmten IDs die ich aus den Einschränkungen Zeitraum bzw. Thema gewinne. Diese IDs benutze ich dann als Grundlage für die weitere Recherche nach dem Suchwort. Anfänglich hat die Ausführung ca. 30 Sekunden gedauert.

Das ist auch noch ein wichtiger Punkt, den ich ansprechen möchte, obwohl es die meisten Programmierer wissen sollten. Man sollte erst seinen Code schreiben, so dass er einfach zu warten ist und den Anforderungen entspricht. Erst dann sollte man sich auf den Weg von Flaschenhälsen machen, sprich optimieren. Ein gutes Beispiel ist eine Webseite, die mehrere Zehntausend Zugriffe pro Stunde hat. Wenn man diese versucht von Anfang an zu optimieren ohne eine entsprechende Serverlast zum Testen zu haben, ist das bestenfalls verschwendete Zeit, schlimmsten Falls hat man sich auf eine falsche Annahme verlassen und die Optimierung kostet mehr Ausführungszeit als der eigentlich Code.

Zurück zur SQL. Durch schrittweises optimieren, z.B. einen zusätzlichen Index auf die durchsuchten Tabellen und dem Einsatz von temporären Tabellen konnte ich die Ausführung auf, je nach Aufruf, 0.7 – 1.5 Sekunden herunterschrauben. Die Suche geht dabei über 14 Tabellen, von denen 7 durchsucht und 7 für die Ausgabe (z.B. Bilder, Titel, Sortierung etc.) ausgewertet werden.