Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-datenbank / char-oder-varchar-statisch-oder-dynamisch.php

CHAR oder VARCHAR? Statisch oder dynamisch?

Oft steht man bei der Wahl des Datentyps für eine Tabellenspalte vor der Frage, ob man die statische CHAR– oder die dynamische VARCHAR-Version nutzt. Im Internet findet man auch unterschiedliche Meinungen dazu (vgl. Zach Nichters Beitrag und die offizielle Stellungnahme in der MySQL-Dokumentation, deshalb wollte ich mir selbst ein Urteil dazu bilden.

Kurz zur Erklärung, was der Unterschied zwischen statischer und dynamischer Speicherung ist:
Statische Spalten sind Spalten, deren Werte alle die gleiche Länge haben. Nicht genutzte Zeichen werden dabei mit Leerzeichen aufgefüllt (falls das BINARY-Flag gesetzt ist, wird mit 0x00-Bytes aufgefüllt). Im Gegensatz dazu wird bei der dynamischen Variante nur so viel Platz verbraucht, wie der eigentliche Wert jedes Datensatzes in dieser Spalte belegt.
Man kann also sagen, dass CHAR immer 100% des dieser Spalte zugeteilten Platzes nutzt, während VARCHAR im Mittel (wenn man von einer Gleichverteilung der Länge der einzelnen Werte ausgeht) 50% belegt. Das bedeutet, dass mit CHAR Platz „verschwendet“ wird – doch es muss ja einen Grund geben, warum MySQL AB dieses Datenformat eingebaut hat (und es in allen mir bekannten DBMS ebenfalls umgesetzt ist).

Wichtig zu wissen ist, dass MySQL das statische Tabellenformat nur verwenden kann, wenn alle Spalten von einem nicht-variablen Datentyp sind – es dürfen also keine VARCHAR-, VARBINARY-, BLOB- oder TEXT-Spalten in dieser Tabelle genutzt werden). Deshalb ist hier schon mal der erste Tipp offensichtlich: Wenn in einer Tabelle irgendeine Spalte einen dieser dynamischen Datentypen verwenden muss – insbesondere TEXT lässt sich oft nicht vermeiden – macht es aus Performancesicht keinen Sinn eine Spalte vom Datentyp CHAR zu nutzen, da dadurch Speicherplatz verschwendet würde, der keine Geschwindigkeitsvorteile bringen kann, weil die Tabelle das dynamische Speicherformat verwenden muss. Es sei denn man spaltet die Tabelle nochmals in statische und nichtstatische Werte, wobei dann oft ein zusätzlicher Join durchgeführt werden müsste, was den Geschwindigkeitsvorteil schrumpfen lässt.
Wenn man übrigens in Erfahrung bringen möchte, ob eine Tabelle aktuell das statische oder das dynamische Format benutzt, kann man in PHPMyAdmin – nachdem man die gewünschte Tabelle angeklickt hat – auf der rechten Seite in der ersten Zeile nachsehen (ich schreibe hier immer statisch, in PHPMyAdmin wird dafür „starr“ verwendet).

Dynamische Tabellen haben mehrere Probleme, die die Leistung bremsen können. Einzelne Datensätze können durch Updates fragmentieren, das heißt, dass der Wert dieser Spalte im Dateisystem nicht mehr an benachbarten Orten zu finden ist und die Festplatte somit länger suchen muss. Deshalb ist für dynamische Tabellen ein regelmäßiges Optimieren der Tabellen notwendig, wenn mehrere Updates oder Deletes durchgeführt wurden. Bei statischen Tabellen sind Optimizes nur nötig, wenn man Speicherplatz wieder freigeben möchte – für die Performance macht es hier keinen Unterschied.

So, nun zum Test. Ich habe eine Tabelle kopiert, die ich in einem meiner Projekte verwende, in der URLs zu Bildern gespeichert sind. Diese Tabelle hat ca 250000 Datensätze. Sie besitzt 3 Spalten: ID (Primärschlüssel), artikel_ID (Fremdschlüssel), bildurl (VARCHAR(255)). Diese habe ich kopiert und stattdessen für bildurl den Datentyp CHAR(255) verwendet.
Auf bildurl liegt kein Index (ich habe ihn entfernt, damit wir allein die Geschwindigkeit aufgrund des Datentyps vergleichen können).
Getestet wird mit „warmen Query-Caches“ – das heißt die Abfrage wird mehrmals laufen gelassen. Beide Tabellen sind optimiert, es liegt also keine Fragmentierung vor (die aber in der Praxis ein weiteres Defizit für die dynamische Variante sein könnte).

Erste Anwendung:

SELECT *  FROM bilder  ORDER BY bildurl;

Die dynamische Variante benötigt hier ca 0,45 Sekunden, während die statische 0,40 Sekunden benötigt.

SELECT *  FROM bilder  WHERE bildurl='bestimmte URL'

Auch hier schlägt die statische Variante die dynamische (statisch: 0,135 s gegenüber 0,155 s bei der dynamischen).
Nun wollen wir testen, wie es sich bei Updates verhält:

UPDATE bilder  SET bildurl='bestimmte URL'  WHERE bildurl='bestimmte URL'

Es sei angemerkt, dass nicht exakt die gleiche URL verwendet wurde, da sonst ja keine Änderung vollzogen worden wäre (so schlau ist MySQL 😉 ). Bei Updates gib es einen signifikanten Unterschied: die dynamische Version braucht 0,6 Sekunden, während die statische bereits nach 0,1 Sekunden fertig ist. Das ist eindeutig.
Beim Einfügen eines neuen Datensatzes konnte ich keinen Unterschied feststellen, beides geht sehr flott im Millisekundenbereich (nur 1 Tausendstel-Sekunde Unterschied).

Es ist also durchaus sinnvoll, wenn man statische Tabellen nutzt, um optimale Performance erreichen zu können. Es sei allerdings angemerkt, dass sich alle gemachten Aussagen auf MyISAM-Tabellen beziehen. Wie es sich bei InnoDB (oder anderen Formaten) verhält, kann ich nicht sagen.

Ein wichtiger Unterschied sei noch angemerkt: Dynamische Tabellen sind nach einem Absturz schwerer zu rekonstruieren, da durch die Fragmentierung die Werte der Datensätze verstreut liegen können.