Zusammenfassung: MySQL hat mehrere Speicher-Engines, jede mit ihren eigenen Vor- und Nachteilen. Sie können die am besten geeignete für Ihren Einsatz auswählen: MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL hat mehrere Speicher-Engines, jede mit ihren eigenen Vor- und Nachteilen. Sie können die am besten geeignete für Ihren Einsatz auswählen:

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE.

MySQL unterstützt mehrere Speicher-Engines als Handler für verschiedene Tabellentypen. Zu den MySQL-Speicher-Engines gehören Engines, die transaktionssichere Tabellen verwalten, und Engines, die nicht transaktionssichere Tabellen verwalten:

• MyISAM verwaltet nicht transaktionale Tabellen. Es bietet schnelle Speicherung und Abfrage sowie Volltextsuchfunktionen. MyISAM wird in allen MySQL-Konfigurationen unterstützt und ist die standardmäßige Speicher-Engine, es sei denn, Sie konfigurieren MySQL so, dass standardmäßig eine andere Engine verwendet wird.

• Die MEMORY-Speicher-Engine stellt "In-Memory"-Tabellen bereit. Die MERGE-Speicher-Engine ermöglicht es, eine Sammlung gleichartig verarbeiteter MyISAM-Tabellen als eine einzelne Tabelle zu behandeln. Wie MyISAM verwalten die Speicher-Engines MEMORY und MERGE nicht transaktionale Tabellen, und beide Engines sind standardmäßig in MySQL enthalten.

  Hinweis: Die MEMORY-Speicher-Engine war früher als HEAP-Engine bekannt.

• Die Speicher-Engines InnoDB und BDB bieten transaktionssichere Tabellen. BDB ist in den MySQL-Max-Binärdistributionen enthalten, die für Betriebssysteme veröffentlicht wurden, die es unterstützen. InnoDB ist außerdem standardmäßig in allen MySQL 5.1-Binärdistributionen enthalten. Sie können MySQL so konfigurieren, dass Sie eine der beiden Engines nach Ihren Wünschen aktivieren oder deaktivieren.

• Die EXAMPLE Storage-Engine ist eine "Stub"-Engine, die nichts tut. Sie können Tabellen mit dieser Engine erstellen, aber es werden keine Daten darin gespeichert oder daraus abgerufen. Der Zweck dieser Engine ist es, als Beispiel im MySQL-Quellcode zu dienen, das zeigt, wie man mit dem Schreiben einer neuen Storage-Engine beginnt. Ebenso ist sie hauptsächlich für Entwickler von Interesse.

• NDB Cluster ist die Storage-Engine, die von MySQL Cluster verwendet wird, um Tabellen zu implementieren, die über mehrere Computer partitioniert sind. Sie wird in den MySQL-Max 5.1-Binärdistributionen bereitgestellt. Diese Storage-Engine wird derzeit nur unter Linux, Solaris und Mac OS X unterstützt. In zukünftigen MySQL-Distributionen beabsichtigen wir, Unterstützung für diese Engine auf anderen Plattformen hinzuzufügen, einschließlich Windows.

• Die ARCHIVE Storage-Engine wird verwendet, um große Datenmengen ohne Indizes mit sehr geringem Speicherbedarf zu speichern.

• Die CSV Storage-Engine speichert Daten in Textdateien im kommagetrennten Format.

• Die BLACKHOLE Storage-Engine akzeptiert Daten, speichert sie aber nicht, und beim Abrufen wird immer eine leere Menge zurückgegeben.

• Die FEDERATED Storage-Engine speichert Daten in einer entfernten Datenbank. In MySQL 5.1 funktioniert sie nur mit MySQL und verwendet die MySQL C Client API. In zukünftigen Distributionen beabsichtigen wir, sie mit anderen Treibern oder Client-Verbindungsmethoden an andere Datenquellen anbinden zu können.

Die häufiger verwendeten sind MyISAM und InnoDB

Wie wählt man zwischen den MySQL-Speicher-Engines MyISAM und InnoDB?

Obwohl die Speicher-Engines in MySQL nicht nur MyISAM und InnoDB sind, sind diese beiden die häufig verwendeten. Manche Webmaster haben den Speicher-Engines von MySQL vielleicht keine Beachtung geschenkt. Tatsächlich ist die Speicher-Engine auch ein wichtiger Punkt im Datenbankdesign. Welche Speicher-Engine sollte ein Blog-System also verwenden?

Im Folgenden betrachten wir die Unterschiede zwischen den beiden Speicher-Engines.

• Erstens unterstützt InnoDB Transaktionen, während MyISAM dies nicht tut. Das ist sehr wichtig. Eine Transaktion ist eine fortgeschrittene Verarbeitungsmethode. Wenn beispielsweise in einer Reihe von Einfügungen, Löschungen und Änderungen ein Fehler auftritt, kann sie zurückgerollt und wiederhergestellt werden, aber MyISAM kann das nicht.
• Zweitens ist MyISAM für Anwendungen geeignet, die auf Abfragen und Einfügungen ausgerichtet sind, während InnoDB für Anwendungen mit häufigen Änderungen und höheren Sicherheitsanforderungen geeignet ist.
• Drittens unterstützt InnoDB Fremdschlüssel, während MyISAM dies nicht tut.
• Viertens ist die Standard-Speicher-Engine für MySQL-Versionen vor 5.1 MyISAM und für Versionen nach 5.1 InnoDB.
• Fünftens unterstützt InnoDB keine FULLTEXT-Typ-Indizes.
• Sechstens: InnoDB speichert nicht die Zeilenanzahl einer Tabelle. Wenn zum Beispiel select count(*) from table ausgeführt wird, muss InnoDB die gesamte Tabelle durchsuchen, um zu berechnen, wie viele Zeilen vorhanden sind, aber MyISAM kann einfach die gespeicherte Zeilenanzahl auslesen. Beachten Sie, dass MyISAM auch die gesamte Tabelle durchsuchen muss, wenn die count(*)-Anweisung eine WHERE-Bedingung enthält.
• Siebtens: Für Auto-Increment-Felder muss InnoDB einen Index enthalten, der nur dieses Feld umfasst, aber in MyISAM-Tabellen kann zusammen mit anderen Feldern ein zusammengesetzter Index erstellt werden.
• Achtens: Beim Leeren der gesamten Tabelle löscht InnoDB Zeile für Zeile, was sehr ineffizient ist. MyISAM baut die Tabelle stattdessen neu auf.
• Neuntens: InnoDB unterstützt Zeilensperren (in einigen Fällen sperrt es jedoch weiterhin die gesamte Tabelle, wie z. B.update table set a=1 where user like '%lee%'

Basierend auf den oben genannten neun Unterschieden und den Eigenschaften persönlicher Blogs wird empfohlen, dass persönliche Blog-Systeme MyISAM verwenden, weil in einem Blog die Hauptoperationen Lesen und Schreiben sind und es nur wenige Verkettungsoperationen gibt. Daher macht die Wahl der MyISAM-Engine die Effizienz beim Öffnen der Seiten Ihres Blogs höher als bei Blogs, die die InnoDB-Engine verwenden. Natürlich ist dies nur eine persönliche Empfehlung; die meisten Blogs sollten trotzdem sorgfältig je nach den tatsächlichen Umständen auswählen.

Einige Hinweise zur Wahl zwischen MyISAM und InnoDB:

MYISAM und InnoDB sind zwei von der MySQL-Datenbank bereitgestellte Speicher-Engines. Jede hat ihre eigenen Vor- und Nachteile. InnoDB unterstützt erweiterte relationale Datenbankfunktionen wie Transaktionen und Zeilensperren, was MyISAM nicht unterstützt. MyISAM hat eine bessere Leistung und belegt weniger Speicherplatz. Daher hängt die Wahl der Speicher-Engine von der konkreten Anwendung ab.

Wenn Ihre Anwendung definitiv Transaktionen erfordert, sollten Sie zweifellos die InnoDB-Engine wählen. Beachten Sie jedoch, dass das Row-Level-Locking von InnoDB an Bedingungen geknüpft ist. Wenn die WHERE-Bedingung keinen Primärschlüssel verwendet, wird die gesamte Tabelle dennoch gesperrt. Zum Beispiel eine DELETE-Anweisung wie DELETE FROM mytable.

Wenn Ihre Anwendung hohe Anforderungen an die Abfrageleistung hat, sollten Sie MyISAM verwenden. MyISAM trennt Indizes von Daten, und seine Indizes sind komprimiert, wodurch der Arbeitsspeicher besser genutzt werden kann. Daher ist seine Abfrageleistung deutlich besser als die von InnoDB. Die komprimierten Indizes sparen zudem etwas Speicherplatz. MyISAM unterstützt Volltextindizes, was die Effizienz von LIKE-Abfragen erheblich optimieren kann.

Manche sagen, dass MyISAM nur für kleine Anwendungen verwendet werden kann, aber das ist eigentlich nur ein Vorurteil. Wenn die Datenmenge relativ groß ist, sollte dies durch einen Ausbau der Architektur gelöst werden, etwa durch Tabellenpartitionierung und Datenbank-Sharding, anstatt sich allein auf die Speicher-Engine zu verlassen.

Einige andere Aussagen:

Heutzutage wird im Allgemeinen InnoDB gewählt. Der Hauptgrund ist, dass MyISAM Tabellensperren verwendet, was zu Problemen bei der Serialisierung von Lese- und Schreibvorgängen und zu Konkurrenz um Tabellensperren führt, wodurch die Nebenläufigkeitseffizienz gering ist. MyISAM wird für lese- und schreibintensive Anwendungen im Allgemeinen nicht gewählt.

Bezüglich der Standard-Speicher-Engine der MySQL-Datenbank:

MyISAM und InnoDB sind zwei Speicher-Engines von MySQL. Bei einer Standardinstallation sollte es InnoDB sein. In der Datei my.ini finden Sie default-storage-engine=INNODB. Natürlich können Sie beim Erstellen einer Tabelle die entsprechende Speicher-Engine angeben. Die relevanten Informationen können Sie durch Ausführen von show create table xx sehen.

Vergleich von InnoDB und MyISAM in MySQL

MyISAM：

Jede MyISAM-Tabelle wird auf der Festplatte als drei Dateien gespeichert. Der Name der ersten Datei beginnt mit dem Tabellennamen, und die Erweiterung gibt den Dateityp an. Die .frm-Datei speichert die Tabellendefinition. Die Datendatei hat die Erweiterung .MYD (MYData).

MyISAM-Tabellen können komprimiert werden, und sie unterstützen die Volltextsuche. Sie unterstützen keine Transaktionen oder Fremdschlüssel. Wenn eine Transaktion zurückgerollt wird, führt dies zu einem unvollständigen Rollback und es fehlt die Atomarität. Bei der Ausführung von UPDATE wird eine Tabellensperre verwendet, daher ist die Nebenläufigkeit relativ gering. Wenn Sie eine große Anzahl von SELECT-Anweisungen ausführen, ist MyISAM die bessere Wahl.

MyISAM trennt Indizes von Daten, und die Indizes werden komprimiert, was die Speichernutzung erheblich verbessert. Es können mehr Indizes geladen werden. InnoDB hingegen bindet Indizes und Daten eng zusammen, ohne Komprimierung, wodurch InnoDB erheblich größer als MyISAM wird.

MyISAM speichert Indizes im Arbeitsspeicher zwischen, nicht Daten. InnoDB speichert Daten im Arbeitsspeicher zwischen. Relativ betrachtet: Je größer der Arbeitsspeicher des Servers, desto größer ist der Vorteil, den InnoDB nutzen kann.

Vorteile:Das Abfragen von Daten ist relativ schnell, eignet sich für eine große Anzahl von SELECT-Anweisungen und unterstützt Volltextindizierung.

Nachteile:Unterstützt keine Transaktionen, unterstützt keine Fremdschlüssel, hat eine geringe Nebenläufigkeit und eignet sich nicht für eine große Anzahl von UPDATE-Anweisungen.

InnoDB:

Dieser Typ ist transaktionssicher. Er weist dieselben Merkmale wie der BDB-Typ auf, und sie unterstützen auch Fremdschlüssel. InnoDB-Tabellen sind sehr schnell. Sie bieten noch reichhaltigere Funktionen als BDB. Wenn Sie also eine transaktionssichere Speicher-Engine benötigen, wird empfohlen, sie zu verwenden. Während UPDATE wird Zeilensperrung verwendet, daher ist die Nebenläufigkeit relativ hoch. Wenn Ihre Daten eine große Anzahl von INSERT- oder UPDATE-Vorgängen umfassen, sollten Sie aus Leistungsgründen InnoDB-Tabellen verwenden.

Vorteile:Unterstützt Transaktionen, unterstützt Fremdschlüssel, hat eine hohe Nebenläufigkeit und eignet sich für eine große Anzahl von UPDATE-Anweisungen.

Nachteile:Das Abfragen von Daten ist relativ schnell, aber nicht für eine große Anzahl von SELECT-Anweisungen geeignet.

Bei InnoDB-Tabellen, die Transaktionen unterstützen, ist der Hauptgrund, der die Geschwindigkeit beeinträchtigt, dass die Standardeinstellung AUTOCOMMIT aktiviert ist und das Programm BEGIN nicht explizit aufruft, um eine Transaktion zu starten, wodurch jede eingefügte Zeile automatisch festgeschrieben wird, was die Geschwindigkeit stark beeinträchtigt. Sie können BEGIN vor der Ausführung von SQL aufrufen, und mehrere SQL-Anweisungen bilden eine Transaktion (selbst wenn Autocommit aktiviert ist), was die Leistung stark verbessert.

Die grundlegenden Unterschiede sind:Der MyISAM-Typ unterstützt keine erweiterte Verarbeitung wie Transaktionsverarbeitung, während der InnoDB-Typ sie unterstützt.

Tabellen vom Typ MyISAM sind auf Leistung ausgelegt, und ihre Ausführungsgeschwindigkeit ist höher als die von Tabellen vom Typ InnoDB, aber sie bieten keine Transaktionsunterstützung. InnoDB bietet Transaktionsunterstützung und erweiterte Datenbankfunktionen wie Fremdschlüssel.

Weitere Vergleiche:

MyIASM ist eine neue Version der ISAM-Tabelle mit den folgenden Erweiterungen:

• Portabilität auf Binärebene.
• Indizierung von NULL-Spalten.
• Geringere Fragmentierung für Zeilen variabler Länge als bei ISAM-Tabellen.
• Unterstützt große Dateien.
• Bessere Indexkomprimierung.
• Bessere statistische Verteilung der Schlüssel.
• Bessere und schnellere Behandlung von auto_increment.

Im Folgenden werden einige Unterschiede in Details und der konkreten Implementierung genannt:

• 1. InnoDB unterstützt keine Indizes vom Typ FULLTEXT.
• 2. InnoDB speichert nicht die exakte Anzahl von Zeilen in einer Tabelle. Das heißt, bei der Ausführung von select count(*) from table muss InnoDB die gesamte Tabelle durchsuchen, um die Anzahl der Zeilen zu berechnen, während MyISAM nur die gespeicherte Zeilenanzahl lesen muss. Beachten Sie, dass der Vorgang bei beiden Tabellen derselbe ist, wenn die count(*)-Anweisung eine WHERE-Bedingung enthält.
• 3. Für AUTO_INCREMENT-Felder muss InnoDB einen Index enthalten, der nur dieses Feld umfasst, aber in MyISAM-Tabellen kann ein zusammengesetzter Index zusammen mit anderen Feldern erstellt werden.
• 4. Bei der Ausführung von DELETE FROM table baut InnoDB die Tabelle nicht neu auf; es löscht Zeilen einzeln.
• 5. Die Operation LOAD TABLE FROM MASTER funktioniert nicht bei InnoDB. Die Lösung besteht darin, die InnoDB-Tabelle zuerst in eine MyISAM-Tabelle zu ändern, die Daten zu importieren und sie dann wieder in eine InnoDB-Tabelle zu ändern. Dies ist jedoch nicht auf Tabellen anwendbar, die zusätzliche InnoDB-Funktionen (wie Fremdschlüssel) verwenden.

Darüber hinaus ist das Sperren auf Zeilenebene von InnoDB nicht absolut. Wenn MySQL bei der Ausführung einer SQL-Anweisung den zu durchsuchenden Bereich nicht bestimmen kann, sperren InnoDB-Tabellen auch die gesamte Tabelle, zum Beispiel: update table set num=1 where name like "%aaa%".

Kein Tabellentyp ist allmächtig. Nur durch die angemessene Auswahl des Tabellentyps entsprechend dem Geschäftstyp können die Leistungsvorteile von MySQL maximiert werden.

Update-Vergleich zwischen InnoDB und MyISAM:

InnoDBs Datenorganisation ist ein B+-Baum, der gemäß dem Primärschlüssel aufgebaut wird. Wenn kein Primärschlüssel explizit definiert ist, wählt InnoDB einen NOT NULL UNIQUE-Schlüssel als Primärschlüssel aus. Wenn immer noch keiner vorhanden ist, erstellt InnoDB einen 6-Byte-Primärschlüssel. Der Primärschlüsselindex zeigt auf eine Seite, nicht auf eine bestimmte Zeilenposition.

Ein nicht inkrementierender Primärschlüssel macht das Einfügen sehr langsam, zum Beispiel wenn eine Mobiltelefonnummer oder Personalausweisnummer als Primärschlüssel verwendet wird, also nutzen Sie AUTO_INCREMENT sinnvoll.

Eine große Tabelle ist nicht beängstigend; beängstigend sind COUNT oder ein LIMIT mit hohem Offset. Sie können ein großes LIMIT big durch LIMIT max_id, xxxxx ersetzen.

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

Für InnoDB ist es sinnlos, eine Tabelle nach einer bestimmten Spalte zu partitionieren und zu hoffen, die Leistung auf einem einzelnen Server zu verbessern.

Einfügegeschwindigkeit und Abfragegeschwindigkeit sind manchmal unvereinbare Widersprüche.

Es ist falsch zu sagen, dass InnoDB nicht für COUNT geeignet ist. MyISAM ist genauso langsam. Es ist nur so, dass MyISAM die Zeilenanzahl der gesamten Tabelle zwischenspeichert, daher ist das Zählen der gesamten Tabelle schnell. Wenn es eine Abfragebedingung gibt und es keine Primärschlüsselabfrage ist, gibt es keinen Unterschied. Der Grund, warum COUNT per Primärschlüssel langsam ist, ist, dass InnoDB nach dem Primärschlüssel organisiert ist. Beim Zählen per Primärschlüssel werden Daten geladen.

InnoDBs seitenbasierte Speicherung erleichtert das Volltabellen-Caching und Hot Backups.

Wenn eine Tabelle viele Indizes hat, dann ist InnoDBs Aktualisierungsgeschwindigkeit größer als die von MyISAM. Weil InnoDBs Sekundärindizes mit dem Primärschlüssel der Tabelle verknüpft sind, der ein logischer Wert ist, während alle MyISAM-Indizes mit der physischen Position der Daten verknüpft sind. Bei Aktualisierungen kann sich die physische Position der Daten ändern. Wenn sie sich ändert, müssen alle Indizes aktualisiert werden.

InnoDB speichert nicht die genaue Anzahl der Zeilen in einer Tabelle. Das heißt, bei der Ausführung von select count(*) from table muss InnoDB die gesamte Tabelle scannen, um die Anzahl der Zeilen zu berechnen, während MyISAM nur die gespeicherte Zeilenanzahl lesen muss. Beachten Sie, dass bei der count(*)-Anweisung mit einer WHERE-Bedingung die Operation auf beiden Tabellen gleich ist.

Originaladresse: https://my.oschina.net/junn/blog/183341