MySQL-Tabellentypen: Ein Vergleich

MySQL bietet verschiedene Tabellentypen bzw. -formate. Als Standard wird MyISAM verwendet, aber daneben gibt es auch InnoDB, BDB, MemoryDB und andere. Dieser Artikel soll die Eigenschaften der verschiedenen Systeme vorstellen und sie miteinander vergleichen.

Grundlegend wird in transaktionsbasierte (z.B. InnoDB, BDB, Gemini) und nicht-transaktionsbasierte Formate (z.B. MyISAM, ISAM, HEAP, MERGE) unterschieden. Transaktionen sind untrennbare Blöcke.
Ein gern genommenes Beispiel ist die Überweisung bei einer Bank:

UPDATE konten SET saldo=saldo-100 WHERE ktonr=1234;
UPDATE banken SET saldo=saldo-100 WHERE filialnr=(SELECT filialnr FROM konten WHERE ktonr=1234);
UPDATE konten SET saldo=saldo+100 WHERE ktonr=4322;
UPDATE banken SET saldo=saldo+100 WHERE filialnr=(SELECT filialnr FROM konten WHERE ktonr=4322);

Es sollten entweder alle Befehle ausgeführt werden oder gar keiner (nicht auszudenken, was passieren würde, wenn der Server nach dem ersten oder zweiten Befehl einen Fehler bekommt – dann ist das Geld im Nirwana verschwunden). Deshalb umschließt man diesen Block mit BEGIN und COMMIT;. Dann wird der DB-Zustand nur gespeichert, wenn das COMMIT erreicht wird – also alle Befehle ausgeführt wurden.

Nun aber zu den einzelnen Formaten:
MyISAM (stellvertetend für nicht-transaktionsorientierte Tabellentypen)

  • gute Kompatibilität aufgrund der Little-Endian-Architektur. Das bedeutet, dass alle Daten mit dem niederwertigsten Bit zuerst gespeicheichert werden. Dadurch sind die Daten MyISAM maschinen- und betriebssystemunabhängig.
  • eine MyISAM-Tabelle kann maximal 64 Indizes haben, was sich aber durch Rekompilieren ändern lässt: Ab Version MySQL 5.1.4 kann configure mit der Option –with-max-indexes=N aufgerufen werden, wobei N die Höchstzahl der pro MyISAM-Tabelle zulässigen Indizes ist. N muss kleiner oder gleich 128 sein.
  • Pro Index sind maximal 16 Spalten möglich
  • Intern wird eine AUTO_INCREMENT-Spalte pro Tabelle unterstützt. Diese Spalte wird bei INSERT- und UPDATE-Operationen automatisch eingefügt. Das macht AUTO_INCREMENT-Spalten schneller (um mindestens 10%) als wenn man das Verwalten der IDs selbst übernimmt.
  • NULL-Werte in indizierten Spalten sind zulässig. Hierfür werden 0 bis 1 Byte pro Schlüssel gebraucht.

InnoDB (stellvertetend für transaktionsorientierte Tabellentypen)

  • transaktionssicher (inklusive Rollback-, Commit- und Datenwiederherstellungsfähigkeit)
  • Zeilensperren möglich (statt Tabellensperren bei MyISAM), konsistentes Auslesen (SELECT) ohne Sperren möglich -> Beschleunigung paralleler Abfragen
  • Unterstützung von Fremdschlüsseln
  • sehr effiziente Ausnutzung der CPU

Memory-Engine (Heap-Tabellen)
Diese Tabellen existieren vollständig im RAM. Das ist gleichzeitig Fluch und Segen:

  • sehr schneller Zugriff
  • nach Absturz nicht wiederherstellbar

Das sind die wichtigsten Unterschiede. Natürlich gibt es auch noch andere Tabellentypen, aber in der Praxis kommen eigentlich nur diese drei zum Einsatz (was MySQL angeht).

Fazit:
InnoDB, Gemini und BDB sollten dann eingesetzt werden, wenn die Konsistenz der Datenbank auf keinen Fall gefährdet werden darf. Die Daten können nach einem Absturz von MySQL automatisch wiederhergestellt werden. Zugleich stellen Transaktionen sicher, dass nicht mehrere Anwender simultan Datensätze verändern, und sichern damit die Integrität der Datenbank.
Verknüpfte Tabellen verweisen nur auf tatsächlich existierende Datensätze und nicht ins Leere. Transaktions-sichere Tabellen sind aber langsamer als das am häufigsten verwendete MyISAM-Format.
Noch schneller als diese sind Heap Tables, die vollständig im Speicher residieren, aber nach einem Absturz oder Reboot natürlich verschwunden sind und sich deshalb nur für eine temporäre Datenhaltung eigen.

Jan hat 152 Beiträge geschrieben

17 Kommentare zu “MySQL-Tabellentypen: Ein Vergleich

  1. Christoph sagt:

    Erwähnen wir noch: MyISAM untersützt maximal 2^32 Datensätze pro Tabellen, bei InnoDB gibt es diese Beschränkungen, soweit ich mich durch Google gekämpft habe, nicht.

  2. tcomic sagt:

    @Christoph: Die einzige Beschränkung bei InnoDB ist die Maximale physische Speichergrösse der Tabellen. Allerdings ist diese Grösse mit 64Tb pro Tabelle mehr als ausreichend!

  3. Florian Spiegel sagt:

    Hallo zusammen,

    ich bin schon länger ein begeisteter Leser dieser Seite. Ich suche nur das Forum das es früher hier mal gab.

    Ich habe ein SQL Problem das ich gerne mit euch besprechen würde, da ich denke, dass ich mir weiterhelfen könnt.

    Gibt es dieses Forum hier noch, ich finde den Link leider nicht mehr.

    Gruß Florian

  4. admin sagt:

    Leider musste ich das Forum von der Seite nehmen, weil täglich 4-5 Spam-Anmeldungen dazukamen. Die Spammer sind einfach zu spezialisiert auf das PHPBB, selbst die AddOns haben nur wenig geholfen.
    Am besten Du schreibst mir per E-Mail, dann kann ich die Frage vielleicht irgendwie in nem Beitrag verarbeiten und dann können die Besucher dazu Stellung nehmen.

  5. tcomic sagt:

    Ein Forum wäre aber wirklich nicht schlecht!

    Ich betreue mehrer PHPBBs und habe eigentlich selten Probleme mit Spam-Anmeldungen (sind auch nicht sehr grosse Cummunities muss ich zugeben)…

    Aber der Einbau eines eigenen, ev. zusätzlichen, Captchas (z.B. der von JPGraph http://www.aditus.nu/jpgraph/) sollte das Spam-Problem weitgehend minimieren, da die Spamer nicht auf diesen Captcha vorbereitet sind!

  6. admin sagt:

    Wenn mir das jemand so aufsetzt oder eine kurze Beschreibung hat, wie ich vorzugehen habe, wäre ich sehr dankbar. Allerdings habe ich derzeit nicht so viel Zeit, dass ich mir nen Battle mit den ganzen Spam-Idioten liefere.

  7. Florian Spiegel sagt:

    @admin
    wie lautet den deine Emailadresse?
    Oder soll ich warten, bis ihr wieder ein Forum habt?
    Gruß
    Florian

  8. admin sagt:

    @Christian: InnoDB, denn dieser Typ unterstützt zeilenbasiertes Sperren (beim Insert wird nicht die ganze Tabelle gesperrt. Viel mehr hängen die Insert-Zeiten aber von den Indizes ab, denn mit jedem Insert müssen auch alle Indizes der Tabelle aktualisiert werden.

  9. Nils sagt:

    Was ist denn Gemini?

    BDB ist eigentlich kein Thema mehr und verschwindet sowieso bald.

    Zu InnoDB fehlt natürlich noch ein ganz interessanter punkt (hierbei auch der Vergleich mit MyISAM), InnoDB speichert die Daten nach Primärschlüssel sortiert. Weitere Indizes verweisen auf den Primärschlüssel. Hiermit kann man bei Daten entsprechend Lokalität erzeugen wenn man den Primärschlüssel anlegt, weiter ist zu beachten dass man bei zusammengesetzten Indizes den Primärschlüssel i.d.R. nicht mit in den Index aufnehmen sollte.

    Gibt es keinen Primärschlüssel wird der erste Unique Index genommen, gibt es auch hiervon keinen wird ein künstlicher wert erzeugt und gespeichert.

    MyISAM hingegen clustert nicht nach Primärschlüssel, jede Zeile hat eine eigene ID (der data pointer) und wird einfach an das Ende der Tabelle (oder dort wo Platz ist) geschrieben.

    Die Länge des Data Pointers kann man über my.cnf anpassen, so dass Tabellengrößen bis 256TB möglich werden.

    Zu diesem Thema kann man noch einiges mehr schreiben…
    Einfach mal hier schauen und staunen: http://dev.mysql.com/doc/refman/6.0/en/storage-engines.html

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>