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.

Jan hat 152 BeitrÀge geschrieben

5 Kommentare zu “CHAR oder VARCHAR? Statisch oder dynamisch?

  1. Daniel sagt:

    Leider kann man gar keinen Kontakt zu dir aufnehmen, ich habe aber eine Frage an dich die ich dir gerne persönlich stellen würde. Kannst du mir mal eine E-Mail schicken?

  2. admin sagt:

    Klar kann man Kontakt zu mir aufnehmen. Im Forum geht das beispielswiese wunderbar (Username daryl). Wenn Du mir private Nachrichten schicken möchtest, kannst Du die PM-Funktion im Forum nutzen oder die dort hinterlegte ICQ-Nummer nutzen.

  3. Henry sagt:

    Hi Jan,

    danke erstmal für den Artikel. Hierbei muss allerdings angemerkt werden das sowohl für BLOB als auch für TEXT diese Einschränkung nicht gilt, da hier nur Zeiger in der Tabelle selbst von statischer Länge gespeichert werden. Diese Zeiger zeigen dann auf den eigentlichen Ort der Daten, die aber nicht im Tabellenkörper selbst enthalten sind.

    Der Speicherbedarf einerTEXT column in der Originaltabelle beträgt je nach storage engine und Datentyp 9 bis 12 byte

    Zitat: „BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.“
    von: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

    Eine Ausnahme gibt es, die „NDB Cluster storage engine“, hier sind es immer 256 byte, aber diese Länge ist auch statisch („fixed size“). von: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

    Es ist natürlich korrekt dass bei einem Select über Felder, die auch „TEXT“ columns inkludieren eine dynmische Tabelle erstellt werden muss, aber wenn in einem Select nicht auf die „TEXT“ columns zugegriffen wird, stören diese auch nicht, denn die Zeilenlänge in der Originaltabelle ist nach wie vor statisch.

    Berichtigungen oder Ergänzungen meiner hier vorgebrachten Meinung sind selbstverständlich gern willkommen.

    Grüße

    Henry

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>