MySQL 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:

id name
1 Stefan Wienströer
1 Hans Wurst
1 Max Mustermann

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:

id firstname firstname
1 Stefan Wienströer
1 Hans Wurst
1 Max Mustermann

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:

firstname lastname website
Stefan Wienströer steviewswebsite.de
Stefan Wienströer applimana.com
Stefan Wienströer preisbuzzer.de

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:

user website
1 steviewswebsite.de
1 applimana.com
1 preisbuzzer.de

Und die Zweite Tabelle:

id firstname lastname
1 Stefan Wienströer

Dritte Normalform

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

user role
1 Administrator
2 Moderator
3 Moderator
4 Benutzer
5 Benutzer

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

user role
1 1
2 2
3 2
4 3
5 3

Und die neue Tabelle:

id name
1 Administrator
2 Moderator
3 Benutzer

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:

id name password E-Mail
1 stevieswebsite geheim info@stevieswebste.de
2 hanswurst tsruwsnah info@hanswurst.de
3 mustermann max max@mustermann.de
4 peter lustig peter@lustig.de
5 waltraut 12345 waltraut@examle.org

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:

id name password E-Mail
1 hanswurst tsruwsnah info@hanswurst.de
2 mustermann max max@mustermann.de
3 peter lustig peter@lustig.de
4 stevieswebsite geheim info@stevieswebste.de
5 waltraut 12345 waltraut@examle.org

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` )

Mehr Infos: MySQL: Index über mehrere Spalten (xfragger.de)

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.

Mehr Infos: Langsame Subselects bei großen InnoDB Tabellen in MySQL (ideaweb.de)

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`

Mehr dazu: Optimierung und Bereinigung der MySQL Datenbanken

5 Gedanken zu “MySQL Optimierung in 4 Schritten

  1. 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

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

  3. @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

  4. 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

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>