Unterschiede und Vergleiche von Speicher-Engines in MySQL

MyISAM-Speicher-Engine

MyISAM basiert auf der ISAM-Speicher-Engine und erweitert diese. Es ist eine der am häufigsten verwendeten Speicher-Engines im Web, im Data Warehousing und in anderen Anwendungsumgebungen. MyISAM hat höhere Einfüge- und Abfragegeschwindigkeiten, unterstützt jedoch keine Transaktionen .

Die Hauptfunktionen von MyISAM sind:

1. Große Dateien (bis zu 63 Bit Länge) werden auf Dateisystemen und Betriebssystemen unterstützt, die große Dateien unterstützen.

2. Dynamisch dimensionierte Zeilen erzeugen weniger Fragmentierung, wenn Lösch-, Aktualisierungs- und Einfügevorgänge gemischt werden. Dies geschieht automatisch durch das Zusammenführen benachbarter gelöschter Blöcke und die Erweiterung auf den nächsten Block, wenn der nächste Block gelöscht wird.

3. Die maximale Anzahl von Indizes für jede MyISAM-Tabelle beträgt 64 und kann durch Neukompilierung geändert werden. Die maximale Anzahl von Spalten pro Index beträgt 16

4. NULL ist in der Indexspalte zulässig. Dieser Wert belegt 0 bis 1 Byte für jeden Schlüssel.

5. Sie können Datendateien und Indexdateien in verschiedenen Verzeichnissen ablegen (InnoDB wird in einem Verzeichnis abgelegt).

Die MyISAM-Engine verwendet B+Tree als Indexstruktur und das Datenfeld des Blattknotens speichert die Adresse des Datensatzes .

Die folgende Abbildung ist ein schematisches Diagramm des MyISAM-Index:

Hier nehmen wir an, dass die Tabelle drei Spalten hat. Unter der Annahme, dass Col1 der Primärschlüssel ist, ist die obige Abbildung ein Diagramm des Primärschlüssels einer MyISAM-Tabelle.

Es ist ersichtlich, dass in der MyISAM-Indexdatei nur die Adresse des Datensatzes gespeichert wird .

In MyISAM gibt es keinen strukturellen Unterschied zwischen dem Primärindex und dem Sekundärindex (Sekundärschlüssel), außer dass der Primärindex einen eindeutigen Schlüssel erfordert, während der Sekundärindexschlüssel wiederholt werden kann.

Wenn wir einen sekundären Index für Spalte 2 erstellen, sieht die Struktur dieses Index wie folgt aus:

Es handelt sich ebenfalls um einen B+Baum , und das Datenfeld speichert die Adresse des Datensatzes.

Daher besteht der Indexabrufalgorithmus in MyISAM darin, zuerst den Index gemäß dem B + Tree-Suchalgorithmus zu durchsuchen. Wenn der angegebene Schlüssel vorhanden ist, wird der Wert seines Datenfelds herausgenommen und dann der entsprechende Datensatz unter Verwendung des Werts des Datenfelds als Adresse gelesen.

Die MyISAM-Indexmethode wird auch als „nicht gruppiert“ bezeichnet, was die Unterscheidung vom gruppierten Index von InnoDB ermöglicht.

InnoDB-Speicher-Engine

InnoDB ist die bevorzugte Engine für Transaktionsdatenbanken. Sie unterstützt transaktionssichere Tabellen (ACID), Zeilensperren und Fremdschlüssel . Wie Sie in der Abbildung oben sehen können, ist InnoDB die Standard-MySQL-Engine .

Die Hauptfunktionen von InnoDB sind:

1. InnoDB stellt MySQL eine transaktionssichere (ACID-kompatible) Speicher-Engine mit Commit-, Rollback- und Crash-Recovery-Funktionen bereit. InnoDB sperrt auf Zeilenebene und ermöglicht außerdem ein Oracle-ähnliches Lesen ohne Sperren in SELECT-Anweisungen. Diese Funktionen verbessern die Bereitstellung durch mehrere Benutzer und die Leistung. In SQL-Abfragen können Sie InnoDB-Tabellen frei mit anderen MySQL-Tabellentypen mischen, sogar in derselben Abfrage.

2. InnoDB ist auf maximale Leistung bei der Verarbeitung großer Datenmengen ausgelegt. Seine CPU-Effizienz wird wahrscheinlich von keiner anderen plattenbasierten relationalen Datenbank-Engine-Sperre erreicht.

3. Die InnoDB-Speicher-Engine ist vollständig in den MySQL-Server integriert. Die InnoDB-Speicher-Engine verwaltet ihren eigenen Pufferpool zum Zwischenspeichern von Daten und Indizes im Hauptspeicher. InnoDB speichert seine Tabellen und Indizes in einem logischen Tabellenbereich , der mehrere Dateien (oder Rohdatenträgerdateien) enthalten kann. Dies unterscheidet sich von MyISAM-Tabellen, bei denen jede Tabelle in einer separaten Datei gespeichert wird . InnoDB-Tabellen können jede beliebige Größe haben, selbst auf Betriebssystemen, die die Dateigröße auf 2 GB begrenzen.

4. InnoDB unterstützt Integritätsbeschränkungen für Fremdschlüssel . Beim Speichern von Daten in einer Tabelle wird jede Tabelle in der Reihenfolge des Primärschlüssels gespeichert. Wenn beim Definieren der Tabelle der Primärschlüssel nicht angegeben wird, generiert InnoDB für jede Zeile eine 6-Byte-ROWID und verwendet diese als Primärschlüssel.

Obwohl InnoDB ebenfalls B+Tree als Indexstruktur verwendet, unterscheidet sich die spezifische Implementierung völlig von MyISAM.

Der erste große Unterschied besteht darin, dass die Datendateien von InnoDB selbst Indexdateien sind .

Aus dem Obigen wissen wir, dass MyISAM-Indexdateien und Datendateien getrennt sind und die Indexdateien nur die Adressen der Datensätze speichern.

In InnoDB ist die Tabellendatendatei selbst eine als B+Baum organisierte Indexstruktur, und das Blattknoten-Datenfeld dieses Baums speichert vollständige Datensätze .

Der Schlüssel dieses Indexes ist der Primärschlüssel der Datentabelle, daher ist die InnoDB-Tabellendatendatei selbst der Primärindex.

Die obige Abbildung ist ein schematisches Diagramm des InnoDB-Primärindex (der auch eine Datendatei ist). Sie können sehen, dass der Blattknoten vollständige Datensätze enthält.

Dieser Indextyp wird als gruppierter Index bezeichnet. Da die Datendateien von InnoDB selbst nach Primärschlüsseln gruppiert sind, erfordert InnoDB, dass die Tabelle einen Primärschlüssel haben muss (MyISAM hat möglicherweise keinen). Wenn dies nicht explizit angegeben ist, wählt das MySQL-System automatisch eine Spalte aus, die den Datensatz eindeutig als Primärschlüssel identifizieren kann. Wenn eine solche Spalte nicht vorhanden ist, generiert MySQL automatisch ein implizites Feld für die InnoDB-Tabelle als Primärschlüssel. Dieses Feld ist 6 Byte lang und vom Typ „Long Integer“.

Der zweite Unterschied zum MyISAM-Index besteht darin, dass das InnoDB-Hilfsindex-Datenfeld anstelle der Adresse den Wert des entsprechenden Primärschlüssels des Datensatzes speichert. Mit anderen Worten: Alle sekundären Indizes von InnoDB verweisen auf den Primärschlüssel als Datenfeld.

Die folgende Abbildung zeigt beispielsweise einen Hilfsindex, der in Spalte 3 definiert ist:

Als Vergleichskriterium wird hier der ASCII-Code englischer Zeichen verwendet. Die Implementierung eines gruppierten Indexes macht die Suche nach Primärschlüsseln sehr effizient, für die Suche im Hilfsindex sind jedoch zwei Indexsuchen erforderlich: Durchsuchen Sie zuerst den Hilfsindex, um den Primärschlüssel zu erhalten, und verwenden Sie dann den Primärschlüssel, um den Datensatz aus dem Primärindex abzurufen .

Das Verständnis der Indeximplementierungsmethoden verschiedener Speicher-Engines ist für die korrekte Verwendung und Optimierung von Indizes sehr hilfreich . Wenn man beispielsweise die Indeximplementierung von InnoDB kennt, ist es leicht zu verstehen, warum es nicht empfohlen wird, ein zu langes Feld als Primärschlüssel zu verwenden, da alle sekundären Indizes auf den Primärindex verweisen und ein zu langer Primärindex den sekundären Index zu groß macht.

Ein weiteres Beispiel: Es ist keine gute Idee, ein nicht-monotones Feld als Primärschlüssel in InnoDB zu verwenden, da die InnoDB-Datendatei selbst ein B+-Baum ist. Ein nicht-monotoner Primärschlüssel führt dazu, dass die Datendatei beim Einfügen neuer Datensätze häufig aufgeteilt und angepasst werden muss, um die Eigenschaften des B+-Baums beizubehalten, was sehr ineffizient ist. Die Verwendung eines Auto-Increment-Felds als Primärschlüssel ist eine gute Wahl.

MEMORY-Speicher-Engine

Die Speicher-Engine MEMORY speichert Daten in einer Tabelle im Speicher und ermöglicht schnellen Zugriff ohne Abfragen oder Referenzieren von Daten in anderen Tabellen.

Die Hauptfunktionen von MEMORY sind:

1. Jede MEMORY-Tabelle kann bis zu 32 Indizes, 16 Spalten pro Index und eine maximale Schlüssellänge von 500 Bytes haben

2. Die MEMORY-Speicher-Engine führt einen HASH- und BTREE-Mikrokosmos aus

3. Sie können in einer MEMORY-Tabelle nicht eindeutige Schlüsselwerte haben

4. Die MEMORY-Tabelle verwendet ein festes Datensatzlängenformat

5. MEMORY unterstützt keine BLOB- oder TEXT-Spalten

6. MEMORY unterstützt AUTO_INCREMENT-Spalten und Indizes für Spalten, die NULL-Werte enthalten können

7. MEMORY-Tabellen werden von allen Clients gemeinsam genutzt (genau wie jede andere nicht-TEMPORÄRE Tabelle).

8. Der Speicher der MEMORY-Tabelle wird im Speicher gespeichert. Der Speicher wird von der MEMORY-Tabelle und der internen Tabelle gemeinsam genutzt, die vom Server erstellt wird, wenn die Abfrage verarbeitet wird.

9. Wenn der Inhalt der MEMORY-Tabelle nicht mehr benötigt wird, sollten Sie zum Freigeben des von der MEMORY-Tabelle verwendeten Speichers DELETE FROM oder TRUNCATE TABLE ausführen oder die gesamte Tabelle löschen (mit DROP TABLE).

Archivspeicher-Engine Auswahl der Speicher-Engine

Verschiedene Speicher-Engines verfügen über eigene Merkmale, um unterschiedliche Anforderungen zu erfüllen, wie in der folgenden Tabelle dargestellt:

InnoDB : Wenn Sie Transaktionssicherheit (ACID-Kompatibilität) mit Commit-, Rollback- und Crash-Recovery-Funktionen bereitstellen möchten und Parallelitätskontrolle benötigen, ist InnoDB eine gute Wahl.

Unterschiede zwischen InnoDB und MyISAM:

1>. InnoDB unterstützt Transaktionen, während MyISAM keine Transaktionen unterstützt

2>.InnoDB unterstützt Sperren auf Zeilenebene, während MyISAM Sperren auf Tabellenebene unterstützt

3>.InnoDB unterstützt MVCC, MyISAM hingegen nicht

4>.InnoDB unterstützt Fremdschlüssel, MyISAM hingegen nicht

5>.InnoDB unterstützt keine Volltextindizierung, MyISAM jedoch schon. (X)

MyISAM : Wenn die Datentabelle hauptsächlich zum Einfügen und Abfragen von Datensätzen verwendet wird, kann die MyISAM-Engine (unterstützt jedoch keine Transaktionen) eine höhere Verarbeitungseffizienz bieten.

Speicher : Wenn Sie Daten nur vorübergehend speichern müssen, die Datenmenge nicht groß ist und keine hohe Datensicherheit erforderlich ist, können Sie die Daten in der Speicher-Engine speichern. MySQL verwendet diese Engine als temporäre Tabelle, um die Zwischenergebnisse der Abfrage zu speichern. Die Daten werden sehr schnell verarbeitet, die Sicherheit ist jedoch nicht hoch.

Archivieren : Wenn nur INSERT- und SELECT-Operationen vorhanden sind, können Sie Archivieren wählen. Archivieren unterstützt Einfügeoperationen mit hoher Parallelität, ist jedoch nicht transaktionssicher. Das Archiv eignet sich sehr gut zum Speichern archivierter Daten, beispielsweise zum Aufzeichnen von Protokollinformationen.

Sie müssen bei der Auswahl der zu verwendenden Engine flexibel sein. Mehrere Tabellen in einer Datenbank können unterschiedliche Engines verwenden, um verschiedene Leistungs- und praktische Anforderungen zu erfüllen. Die Verwendung einer geeigneten Speicher-Engine verbessert die Leistung der gesamten Datenbank.

Das Obige ist meine persönliche Erfahrung. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.