MySQL Performance Optimierung in 4 Schritten

Bei großen Datenmengen kann eine MySQL-Abfrage schon seine Zeit brauchen. Außerdem kann zum Beispiel der Speicherbedarf einer Datenbank steigern. Ich habe mal ein paar Tipps gesammelt, wie Du deine MySQL Datenbank bzw. Deine Abfragen zur Datenbank optimieren kannst.

1. Normalisierung

Durch Normalisierung wird sichergestellt, dass keine Daten doppelt vorhanden sind und Spalten keine eigene Strukturvorweisen. Dabei gibt es mehrere Stufen:

Erste Normalform

Spalten dürfen keine eigene Sturktur aufweisen. Beispiel:

In der Spalte Name werden hier durch Leerzeichen getrennt immer vorname und nachname hintereinander geschrieben. Um diese Tabelle in die erste Normalform zu bringen müssten wir die Spalte name aufteilen:

Jetzt haben wir keine innere Struktur der Spalten -> Die erste Normalform ist erreicht.

Zweite Normalform

In der zweiten Normalform geht es darum, zusammengehörige Daten in eine eigene Tabelle auszulagern. Ein Beispiel:

Hier sind in allen Zeilen „Stefan“ und „Wienströer“ vorhanden. Für die zweite Normalform müssen wir das „Stefan“ und „Wienströer“ in eine andere Tabelle auslagern. Das sieht dann so aus:

Und die Zweite Tabelle:

Dritte Normalform

In der dritte Normalform geht es darum doppelte Einträge zu vermeiden. Beispiel:

In dieser Tabelle sind Rollen doppelt vorhanden, diese müssen nun eine eigene Tabelle und es muss über eine ID darauf zugegriffen werden:

Und die neue Tabelle:

Normalisierung hat im allgemeinen den Vorteil, dass man weniger Speicher verbraucht und zum anderen, dass falsche Daten vermieden werden. Wenn man nun zum Beispiel den Moderator in Autor umbenennen möchte, müsste man 2 Datensätze ändern und danach nur noch einen. Ein Problem würde es geben, wenn kurz nach dem Update noch ein neuer Datensatz mit der alten Beschreibung reinkäme.

Mehr Infos: Normalisierung (Datenbank) (wikipedia.org)

2. Indizes anlegen

Ein Index (Mehrzahl Indizes) sortiert die Datensätze einer Datenbank vor. Man muss sich das so vorstellen: Bei einer WHERE-Bedingung müssen Datensätze durchlaufen werden, um die zu finden, die benötigt werden. Als Beispiel mal eine kleine User-Tabelle:

Nun wollen wir uns mit dieser Abfrage das Passwort von waltraut abrufen:

SELECT password FROM user WHERE name = 'waltraut'

MySQL geht an der Stelle (vereinfacht) so vor:

Ist name von id 1 = waltraut?
Ist name von id 2 = waltraut?
Ist name von id 3 = waltraut?
Ist name von id 4 = waltraut?
Ist name von id 5 = waltraut?
Ja, Zeile zurückgeben

Insgesamt wurden nun 6 Schritte durchgeführt. Ein Index sortiert eine Tabelle vor. Dabei werden die Daten quasi zwei mal gespeichert. Legen wir nun einen Index auf name an, würden wir zusätzlich (intern im MySQL) folgendes haben:

Nun würde MySQL herausfinden, dass die Abfrage über diese „Index-Tabelle“ schneller geht. Dabei würde der Ablauf dann ungefähr so aussehen:

Ist name von id 3 = waltraut? waltraut > peter
Ist name von id 4 = waltraut? waltraut > stevieswebsite
Ist name von id 5 = waltraut?
Ja, Zeile zurückgeben

Die Abfrage hat jetzt also nur noch 4 Schritte gebraucht. Bei 5 Zeilen fällt das noch nicht auf, aber bei 10.000 Zeilen könnte das Ganze schon mal eine Geschwindigkeitsoptimierung von 70% bringen.

Ein Index hat einen eigenen Namen und kann auch durchaus über mehrere Spalten gehen. Man legt ihn folgendermaßen an:

ALTER TABLE `user` ADD INDEX `index_name` ( `name` )

3. Join statt Subselect

Mit Joins und Subselects kann man Abfragen über mehrere Tabellen erstellen. In den meisten fällen kann man ein Subselect durch ein Join ersetzen. Hier mal eine Besipeilabfrage mit Subselect:

SELECT orderid, (SELECT name FROM user WHERE id = orders.userid) FROM orders

Diese Abfrage liefert zu allen Datensätzen der Tabelle order den Namen des Users aus der Spalte userid. Dabei wird in einem Subselect (=Unterabfrage) der Name zu der ID herausgesucht. Das Gleiche kann man nun mit einem INNER JOIN erledigen. Hierbei werden 2 Tabellen „verbunden“. So könnte man auch schnell noch andere spalten der User-Tabelle zurückgeben. Bei einem INNER JOIN werden nur Daten zurückgegeben, wo zu der Spalte userid auch eine id in der user Tabelle vorhanden ist. Das INNER-JOIN für dieser Abfrage würde so aussehen:

SELECT orders.orderid, user.name FROM orders INNER JOIN user ON user.id = orders.id

Das ganze bringt wie oben auch einen Geschwindigkeitsvorteil, da das Subselect nicht bei jeder Zeile durchgeführt werden muss.

4. Overhead leeren

Wenn man in einer Datenbank einen Datensatz löscht, wird dieser eigentlich nicht wirklich gelöscht, er benötigt auch nach dem „Löschen“ weiterhin Speicherplatz. Diesen Speicherplatz kann man leeren und somit wirklich entfernen. Das sollte man zum Beispiel machen, wenn der Speicherplatz voll wird. Aber eine regelmäßige Leerung kann auch nicht schaden.

Im PhpMyAdmin kann man diesen Überhand beobachten, in den man sich bei einer Datenbank alle Tabellen anzeigen lässt (nicht links in der Spalte, sondern beim Klick auf einer DB). Ganz rechts ist eine Spalte mit den Namen „Überhang“ hier kann man sehen, wie viel Speicher noch gelöscht werden kann.

Für das wirkliche Löschen gibt es einen schönen Befehlsnamen: OPTIMIZE . Dieser sieht dann zum Beispiel so aus:

OPTIMIZE TABLE `wp_options`

Du arbeitest in einer Agentur oder als Freelancer?
Dann wirf doch mal einen Blick auf unsere Software FeatValue.

Kommentare

Michael schrieb am 23.05.2010:

Grunsätzlich schöne Übersicht, die aber allgemein nicht immer stimmt. Redundanzen sind erlaubt und notwendig, wenn das Query auf die Normalform zu aufwendig ist. Vermisse ein wenig die Unterschiede zw. InnoDB und MySQL. Und Joins sind in einigen wenigen Fällen nicht besser, als Subselects. Tip: O'Reilly: high Performance MySQL. Vg Michael

Michael schrieb am 23.05.2010:

Solltest auch erwähnen, dass optimize Table die Tabelle lockt und bei Großen datenmengen sehr lange dauert, da die Tabelle komplett kopiert wird.

Dominik schrieb am 23.05.2010:

@Michael Du meinst vielleicht den Unterschied zwischen InnoDB und MyISAM? Beides sind nämlich Storage-Engines von MySQL :). Kurz gesagt ist die Datensicherheit bei InnoDB besser, da Relationen geprüft werden. Dafür wird jedoch auch mehr Speicherplatz benötigt und MyISAM ist meistens schneller. Bemerkbar wird dies jedoch nur bei größeren Projekten. Gruß Dominik

Links der Woche XXXVIII - css3,html5,mysql,photoshop,wordpress - Webworking schrieb am 28.05.2010:

[...] MySQL Optimierung in 4 Schritten Richtig gute Hinweise zum Thema MySQL Optimierung. Die Optimierung bezieht sich dabei auf das Datenmodell, und die Abfrage der Daten, und nicht auf den Server! [...]

Chrysler schrieb am 21.04.2011:

Hallo, ich habe soeben diesen Beitrag gelesen und wollte ihn ein wenig kommentieren. Grundsätzlich hast du einen guten Denkanstoß bzgl. der MySQL Optimierung gegeben, wenn du auch nicht wirklich in die Tiefe gegangen bist und einige Punkte so nicht richtig sind. Du solltest dir vor allem nochmal das Thema Normalisierung anschauen, denn da besteht deinerseits definitiv Lernbedarf. Gruß Chrysler

Weiterlesen: ⯈ Administration

Über uns

Wir entwickeln Webanwendungen mit viel Leidenschaft. Unser Wissen geben wir dabei gerne weiter. Mehr über a coding project