Heute erkläre ich Ihnen wie Sie Ihre Performance vom Zugriff 
auf eine MySQL Datenbank steigern können.
1.0 Einführung
Es gibt zwei Punkte in RDBMS’s (Relational Database Management Systems)
welche nicht zu einander passen.
Sie können die höchste Performance von Zugriffzeiten nur dann rausholen,
wenn Sie die relationen innerhalb Ihrer Datenbank gering halten.
Die Nachteile dessen sind dann Anomalien und Redundanz.
Andererseits wenn Sie Anomalien und Redundanz vermeiden möchten um insbesondere
die Datenmenge die Sie haben gering wie möglich zu halten, müssen Sie dafür sorgen,
dass Ihre Datensätze normalisiert sind.
Beides zusammen ist allerdings nicht möglich. Wenn Sie die Performance ins höchste Detail
optimieren wollen, so erhöht sich Ihr Datenvolume.
Es ist anwendungsbedingt wie Sie Ihre Daten halten müssen.
Allerdings gibt es sonst für alles was nicht die höchste Performance erfordert eine gewisse
Zwischenlösung um Redundanz und Anomalien zu vermeiden und trotz dessen die Performance zu steigern, welche gerne als standard Lösung eingesetzt wird.
[AdSense-A]
2.0 Allgemein
2.1 Datenbankschema
Sie sollten dafür sorgen, dass Ihr Datenbankschema (sofern es sich nicht um eine Echtzeitanwendung handelt)
allen gängigen Normalisierungsregeln entspricht, dies heißt Sie dürfen keinerlei Anomalien oder Redundanz
in Ihrer Datenbank halten. Dies ist die erste Vorraussetzung für ein performante Datenbank.
Dazu sorgen Sie in Ihrem DDL-Part (Data Definition Language) dafür, dass Informationen die mehrfach in ihrer
Datenbank auftreten können ausgelagert werden in weitere Relationen.
Beispiel:
Sie haben folgende Informationen die Sie in Ihrer Datenbank unterbringen wollen:
Produktgruppe, Artikel, Artikelbeschreibung, Verkäufer, Preis
Beispieldatensätze:
Computer & Hardware, Grafikkarte Geforce GTX 570, Eine hochleistungsfähige Grafikkarte, Muster GmbH, 10 €
Dekoration, Aufkleber (Blau), Ein cooler Aufkleber in Form eines Stop-Schildes, ABC Tuning OHG, 5 €
Computer & Hardware, Seagate 512 GB HDD, Eine tolle neue Festplatte, Muster GmbH, 15 €
Dabei sehen Sie hier, dass Redundanz auftritt. Was zur Folge hat, wenn Sie beispielsweise die Produktgruppe
“Computer & Hardware” einen anderen Namen geben möchten, müssen Sie dafür sorgen, dass alle Ihre bestehenden
Datensätze geupdated werden. Außerdem halte Sie ein höheres Datenvolume in Ihrer Datenbank als Sie eigentlich
müssten.
Dies können Sie ummodelieren in:
Tabelle Reseller:
ResellerID, Verkäufer
Tabelle Artikel:
ArtikelID, Artikelname, Artikelbeschreibung, Preis
Tabelle Produkt:
ProduktID, Produktname
Tabelle Item:
ItemID, ResellerID, ArtikelID
Dadurch vermeiden Sie Redundanz und mittels des Einsatzes von Referentieller Integrität auch Anomalien.
Mehr dazu finden Sie unter: http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
2.2 Bestimmung von Datentypen
Ein weiterer Punkt den Sie beachten müssen ist die Wahl von korrekten Datentypen.
Wählen Sie keine reinen Textfelder für alle Attribute Ihrer einzelnen Tabellen, dies ist nicht performant
und in den meisten Fällen reservieren Sie für ein Feld mehr Speicherplatz als letzendlich notwendig ist.
Möchten Sie den Datentyp eines Preises festlegen sollten Sie die Datentypen Double oder Integer verwenden.
Integer verwenden Sie nur dann, wenn Ihre Preise ganzzahlige Werte beinhalten werden (10, 15, 13, 4, 3).
Nachkomma stellen sind unter dem Integer nicht möglich. Sollen Nachkomma-Stellen implementiert werden,
sollten Sie den Datentypen Double verwenden, dieser ermöglicht Ihnen zu Bestimmen wie viele Nachkomma-
zahlen sie für Ihr Attribut implementieren möchten.
Da Preise auch nicht negativ sein sollten, weil Sie nicht interessiert sind Ihre Artikel zu verschenken
oder gar Geld dafür zu zahlen diese los zu werden, sollten Sie hier als weiteres Attribut für Ihr Feld
den Typen “UNSIGNED” implementieren. Dieser bedeutet das Zahlen unter 0 nicht möglich sind.
Und Sie verschieben den Zahlenstrahl bei beispielsweise einem 32 Bit Integer von -2.147.483.648
bis 2.147.483.647 zu 0 bis 4.294.967.295.
3.0 Das Eingemachte
Da Sie nun in etwa wissen wie Sie Ihr Datenbankschema modelieren müssen, nach den gängigen Normalisierungs-
regeln können wir fortfahren mit dem eigentlichen Performance-Part.
Folgende Regeln müssen Sie einhalten:
a) Halten Sie den Primary-Key immer möglichst klein von der Länge.
Nutzen Sie hier beispielsweise keine Telefonnummern als Primary-Key auch wenn diese einmalig sind.
b) Nutzen Sie für Fremdschlüssel immer einen INDEX, zur Suchoptimierung für Joins und anderes.
c) Nutzen Sie für Attribute die Sie in einer WHERE Bedingung oft verwenden INDEXES.
d) Speichern Sie keine IP-Adressen als char oder varchar, konvertieren Sie lieber die jeweilige IP-Adresse
in einen Long wert und hinterlegen diesen als Integer-Wert in Ihrer Tabelle.
e) Nutzen Sie von einem Client mit dem Sie auf die Datenbank zugreifen, beispielsweise in PHP keine persistente
Verbindung.
3.1 Engine Archive
Die Engine Archive sollten Sie dann nutzen, wenn Sie performant Einträge in eine Tabelle schreiben oder
löschen möchten. Diese Engine ermöglicht zwar keine Updates bietet aber dafür eine hohe Leistungssteigerung
im Schreiben von neuen Datensätzen.
Diese Engine ist optimal für das Schreiben von Logs / CDRs.
3.2 Engine MEMORY
Die Memory-Engine speichert die in der Tabelle beinhalten Daten nur temporär. Diese Datensätze werden bis zum
nächsten Neustart des Datenbank-Servers in der Datenbank gehalten. Der Zugriff auf diese Datensätze benötigt
wesentlich weniger Zeit. Einmal aus dem Grunde, dass Sie immer ein geringes Volume beinhaltet und andererseits
werden diese Daten im RAM (Arbeitspeicher) gehalten und nicht auf der Festplatte.
Dieser Typ eignet sich beispielsweise gut für das Speichern von Sessions.
3.3 EXPLAIN
Das Schlüselwort EXPLAIN ist ein mächtiger Befehl in MySQL.
Einerseits tut EXPLAIN exakt das selbe wie Show oder DESCRIBE in Benutzung mit einem Tabellennamen.
Anderseits können Sie damit rausfinden wie performant Ihr SQL-Query ist. Das einzige was dafür notwendig ist,
ist dieses Schlüsselwort vor Ihr SELECT-Statement zu setzen.
Dabei sollten Sie vermeiden, dass Ihr SQL-Query als Typen für einen Join ein “ALL” rauswirft. “ALL” bedeutet
so viel, dass die komplette Tabelle analysiert werden muss, dies zieht die Zeit für einen Zugriff auf mehrere
Tabellen in die Höhe. Versuchen Sie immer dies zu vermeiden, entweder durch setzen von Indexes oder durch das
vermeiden von Subquerys im FROM-Statement.
Des Weiteren gibt es hier bei auch einen großen Unterschied zwischen dem Typen “index” und “using index”.
Dieser besteht darin, dass bei “using index” ein voller index Scan innerhalb einer Tabelle erfolgt.
Dies ist nicht performant.
3.4 Stored Procedures
Vermeiden Sie es stored procedures zu nutzen. Diese erzeugen eine Erhöhung der Zugriffszeit von rund 7-8 %.
Sie können diese allerdings dort verwenden, wo Sie auf die Datensätze nicht all zu oft zugreifen müssen.
3.5 Indexes
Nutzen sie keine Funktionen oder Bit-Operatoren zusammen im Einsatz auf Indexes.
Dies führt dazu, dass die jeweiligen gesetzen Indexes nicht benutzt werden.
Im Falle von diesem würde Ihr Explain dort einen Typ von “ALL” auswerfen.
Des Weiteren denken Sie daran, dass ein Index den lesenden Zugriff auf eine Spalte optimiert,
aber den schreibenden Zugriff auf diese Spalte verlangsamt.
Benutzen Sie auch dort Indexes wo Sie entweder mittels WHERE oft suchen oder wonach Sie Ihre Datensätze
mittels GROUP BY gruppieren.
4.0 Flachhaltung
Halten Sie Ihre Abfragen flach. Desto weniger Komplex diese werden, desto schneller kann der Zugriff auf diese
Datensätze erfolgen. Jede weitere nicht nötige tabellenübergreifende Aktion führt zu längeren Lese-Zeiten.
Beispiel:
Einfach:
mysql> EXPLAIN SELECT -> p.* -> FROM payment p -> WHERE p.payment_date = -> ( SELECT MAX(payment_date) -> FROM payment -> WHERE customer_id=p.customer_id);
Zeit: 0.00 sec
Komplex:
mysql> EXPLAIN SELECT -> p.* -> FROM ( -> SELECT customer_id, MAX(payment_date) as last_order -> FROM payment -> GROUP BY customer_id -> ) AS last_orders -> INNER JOIN payment p -> ON p.customer_id = last_orders.customer_id -> AND p.payment_date = last_orders.last_order;
Zeit: 0.10 sec
Beides liefert Ihnen die gleichen Datensätze.
Komplex:
mysql> SELECT -> p.* -> FROM payment p -> WHERE p.payment_date = -> ( SELECT MAX(payment_date) -> FROM payment -> WHERE customer_id=p.customer_id);
Zeit: 0.49 sec
Einfach:
mysql> SELECT -> p.* -> FROM ( -> SELECT customer_id, MAX(payment_date) as last_order -> FROM payment -> GROUP BY customer_id -> ) AS last_orders -> INNER JOIN payment p -> ON p.customer_id = last_orders.customer_id -> AND p.payment_date = last_orders.last_order;
Zeit: 0.09 sec
5.0 Quellen
Diese Informationen basieren auf dem MySQL-Guide von Jay aus dem MySQL-Team.
Sie finden sich ebenso wieder in dem offiziellen PDF von Jay.
Titel: 15 Ways to kill MySQL Application Performance
