Detaillierte Erläuterung der Funktionen und Verwendung allgemeiner MySQL-Speicher-Engines

Dieser Artikel veranschaulicht anhand von Beispielen die Funktionen und die Verwendung gängiger MySQL-Speicher-Engines. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Es gibt zwei Hauptkategorien von MySQL-Speicher-Engines:

1. Transaktionssichere Tabellen : InnoDB, BDB.

2. Nicht transaktionale sichere Tabellen : MyISAM, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED usw.

Die Standardspeicher-Engine für MySQL ist MyISAM (der Standard in Version 5.7 ist InnoDB).

Legen Sie den Standardparameter der Speicher-Engine in der Konfigurationsdatei fest: default-table-type.

Fragen Sie die von der aktuellen Datenbank unterstützten Speicher-Engines ab:

Motoren anzeigen;
Variablen wie „have%“ anzeigen;

Sehen Sie sich die aktuelle Standard-Speicher-Engine an:

Variablen wie „%table_type%“ anzeigen;

Geben Sie beim Erstellen einer neuen Tabelle die Speicher-Engine an:

Tabelle erstellen(...) Engine=MyISAM;

Im Folgenden finden Sie eine detaillierte Einführung in vier häufig verwendete Speicher-Engines: MyISAM, InnoDB, MEMORY und MERGE.

1. MyISAM

1. Datendateien:

MyISAM-Datentabellen werden auf der Festplatte in Form von drei Dateien gespeichert, alle mit dem gleichen Dateinamen wie der Tabellenname. Die Erweiterungen lauten:

(1).frm: speichert die Strukturdefinition der Datentabelle.

(2).MYD: speichert Tabellendaten.

(3).MYI: Speichertabellenindex.

Unter ihnen können Datendateien und Indexdateien in verschiedenen Verzeichnissen abgelegt werden, um die E/A gleichmäßig zu verteilen und eine höhere Geschwindigkeit zu erreichen. Geben Sie den Pfad der Indexdatei und der Datendatei an. Dieser muss beim Erstellen einer Tabelle mit den Anweisungen „Datenverzeichnis“ und „Indexverzeichnis“ angegeben werden. (Der Dateipfad muss ein absoluter Pfad sein und über Zugriffsberechtigungen verfügen)

MyISAM-Tabellen können aus verschiedenen Gründen beschädigt sein. Auf eine beschädigte Tabelle kann möglicherweise nicht zugegriffen werden, und es kann sein, dass eine Reparatur erforderlich ist, oder dass nach dem Zugriff ein Fehlerergebnis zurückgegeben wird. Mit der Anweisung „check table“ können Sie den Zustand der MyISAM-Tabelle prüfen und mit der Anweisung „repair table“ eine beschädigte MyISAM-Tabelle reparieren.

2. Speicherformat:

(1) Statische Tabelle (Standard): Alle Felder haben eine nicht variable Länge (jeder Datensatz hat eine feste Länge). Der Speicher ist sehr schnell, lässt sich leicht zwischenspeichern und nach Fehlern leicht wiederherstellen. Normalerweise nimmt er mehr Platz ein als dynamische Tabellen.

(2) Dynamische Tabelle: Sie nimmt relativ wenig Platz ein, aber häufige Aktualisierungen und Löschungen von Datensätzen führen zu Fragmentierung. Um die Leistung zu verbessern, ist es notwendig, regelmäßig den Befehl optimize table oder myisamchk -r auszuführen. Außerdem ist es schwierig, sie wiederherzustellen, wenn ein Fehler auftritt.

(3) Komprimierte Tabelle: erstellt mit dem Tool myisampack, das sehr wenig Speicherplatz beansprucht. Da jeder Datensatz einzeln komprimiert wird, entsteht nur ein sehr geringer Zugriffs-Overhead.

Die Daten in der statischen Tabelle werden beim Speichern entsprechend der Spaltenbreitendefinition mit Leerzeichen aufgefüllt und diese Leerzeichen werden entfernt, bevor die Daten an die Anwendung zurückgegeben werden. Befindet sich nach dem zu speichernden Inhalt ein Leerzeichen, wird dieses bei der Rückgabe des Ergebnisses entfernt. (Tatsächlich ist dies das Verhalten des Datentyps char. Wenn sich dieser Datentyp in der dynamischen Tabelle befindet, tritt dasselbe Problem auf.)

(Statische und dynamische Tabellen werden automatisch basierend auf dem verwendeten Spaltentyp ausgewählt.)

3. Vorteile und Nachteile:

(1) Vorteile: schnelle Zugriffsgeschwindigkeit.

(2) Es unterstützt keine Transaktionen oder Fremdschlüssel.

4. Anwendbare Situationen:

Wenn die Anwendung hauptsächlich auf Lese- und Einfügevorgängen mit nur wenigen Aktualisierungs- und Löschvorgängen basiert und die Anforderungen an die Transaktionsintegrität und Parallelität nicht sehr hoch sind, ist diese Speicher-Engine sehr gut geeignet. MyISAM ist eine der am häufigsten verwendeten Speicher-Engines im Web, in Data Warehouses und anderen Anwendungsumgebungen.

2. InnoDB

1. Speichermethode:

InnoDB speichert Tabellen und Indizes auf die folgenden zwei Arten:

(1) Verwenden Sie gemeinsam genutzten Tablespace-Speicher: Die auf diese Weise erstellte Tabellenstruktur wird in der Datei .frm gespeichert, und die Daten und Indizes werden in dem durch innodb_data_home_dir und innodb_data_file_path definierten Tablespace gespeichert, bei dem es sich um mehrere Dateien handeln kann.

(2) Verwenden Sie Multi-Tablespace-Speicher: Die auf diese Weise erstellte Tabellenstruktur wird weiterhin in der FRM-Datei gespeichert, die Daten und der Index jeder Tabelle werden jedoch separat in der IDB-Datei gespeichert. Wenn es sich um eine Partitionstabelle handelt, entspricht jede Partition einer separaten IDB-Datei. Der Dateiname lautet „Tabellenname + Partitionsname“. Beim Erstellen von Partitionen können Sie den Speicherort der Datendatei für jede Partition angeben, um die E/A der Tabelle gleichmäßig auf mehrere Festplatten zu verteilen.

Um die Speichermethode mit mehreren Tablespaces zu verwenden, müssen Sie den Parameter innodb_file_per_table festlegen und den Server neu starten, damit die Änderung wirksam wird. Sie wird nur auf neu erstellte Tabellen wirksam. Es gibt keine Größenbeschränkung für Datendateien in mehreren Tablespaces. Es ist nicht erforderlich, die Anfangsgröße, das maximale Dateilimit, die erweiterte Größe und andere Parameter festzulegen. Auch im Multi-Tablespace-Speichermodus ist der gemeinsam genutzte Tablespace weiterhin erforderlich. InnoDB speichert das interne Datenwörterbuch und das Arbeitsprotokoll in dieser Datei. Daher ist es nicht möglich, die .idb-Datei direkt zu kopieren, wenn eine Tabelle gesichert wird, die die Multi-Tablespace-Funktion verwendet. Mit dem folgenden Befehl können Sie die Datensicherung in der Datenbank wiederherstellen:

ALTER TABLE, Tabellenname, DISCARD TABLESPACE;
ALTER TABLE Tabellenname IMPORT TABLESPACE;

Dadurch kann die Tabelle jedoch nur in der ursprünglichen Datenbank wiederhergestellt werden. Wenn Sie sie in einer anderen Datenbank wiederherstellen müssen, müssen Sie mysqldump und mysqlimport verwenden.

2. Datendateien:

InnoDB-Datendateien werden durch die Speichermethode der Tabelle bestimmt.

(1) Gemeinsam genutzte Tablespace-Datei: definiert durch die Parameter innodb_data_home_dir und innodb_data_file_path, wird zum Speichern von Datenwörterbüchern und Protokollen usw. verwendet.

(2).frm: speichert die Tabellenstrukturdefinition.

(3) .idb: Diese Datei wird zum Speichern von Tabellendaten und Indizes verwendet, wenn mehrere Tablespace-Speichermethoden verwendet werden. Wenn gemeinsam genutzter Tablespace-Speicher verwendet wird, existiert diese Datei nicht.

3. Fremdschlüsseleinschränkungen:

InnoDB ist die einzige MySQL-Engine, die Fremdschlüsseleinschränkungen unterstützt. Fremdschlüsseleinschränkungen ermöglichen es der Datenbank, die Datenintegrität und -konsistenz durch Fremdschlüssel sicherzustellen. Durch die Einführung von Fremdschlüsseln verringern sich jedoch Geschwindigkeit und Leistung. Beim Erstellen eines Fremdschlüssels muss die übergeordnete Tabelle über einen entsprechenden Index verfügen, und die untergeordnete Tabelle erstellt beim Erstellen eines Fremdschlüssels automatisch einen entsprechenden Index.

Beispiel für die Verwendung von Fremdschlüsseleinschränkungen:

TABELLE ERSTELLEN `dep` (
 `id` smallint(6) NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8;
CREATE TABLE `emp` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` smallint(6) NICHT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_fk_dep_id` (`dep_id`),
 CONSTRAINT `fk_emp_dep` Fremdschlüssel (`dep_id`) Referenzen `dep` (`id`) bei Update-Kaskade
)ENGINE=InnoDB STANDARD-CHARSET=utf8;

SCHLÜSSEL : Definiert den Namen der Indexeinschränkung.

CONSTRAINT : Definiert den Namen der Fremdschlüsseleinschränkung. (Sollte in der Datenbank eindeutig sein. Wenn nicht angegeben, generiert das System automatisch einen Einschränkungsnamen.)

ON : Gibt die Auswirkung von Vorgängen in der übergeordneten Tabelle auf untergeordnete Tabellen an (falls nicht definiert, wird standardmäßig „restrict“ verwendet).

Einschränken und keine Aktion : Die übergeordnete Tabelle kann nicht aktualisiert oder gelöscht werden, wenn die untergeordnete Tabelle verknüpfte Datensätze enthält.

Kaskade : Wenn die übergeordnete Tabelle aktualisiert oder gelöscht wird, werden auch die entsprechenden Datensätze in der untergeordneten Tabelle aktualisiert oder gelöscht.

Auf Null setzen : Wenn die übergeordnete Tabelle aktualisiert oder gelöscht wird, wird das entsprechende Feld der untergeordneten Tabelle auf Null gesetzt.

Wenn auf eine Tabelle durch einen Fremdschlüssel in einer anderen Tabelle verwiesen wird, kann der entsprechende Index oder Primärschlüssel dieser Tabelle nicht gelöscht werden. Wenn Sie beim Importieren von Daten aus mehreren Tabellen die Reihenfolge, in der die Tabellen importiert werden, ignorieren müssen, können Sie die Fremdschlüsselprüfung vorübergehend deaktivieren. Beim Ausführen von Ladedaten- und Tabellenänderungsvorgängen können Sie die Verarbeitung auch beschleunigen, indem Sie Fremdschlüsseleinschränkungen vorübergehend deaktivieren.

Befehl zum Herunterfahren:

setze foreign_key_checks=0;

Befehl „Öffnen“:

setze foreign_key_checks=1;

4. Vorteile und Nachteile:

(1) Vorteile: Bietet Transaktionssicherheit mit Commit-, Rollback- und Crash-Recovery-Funktionen.

(2) Nachteile: Im Vergleich zu MyISAM weist InnoDB eine geringere Schreibverarbeitungseffizienz auf und benötigt mehr Speicherplatz zum Speichern von Daten und Indizes.

5. Anwendbare Situationen:

Wenn die Anwendung relativ hohe Anforderungen an die Transaktionsintegrität stellt und Datenkonsistenz unter gleichzeitigen Bedingungen erfordert und die Datenvorgänge neben Einfüge- und Abfragevorgängen auch viele Aktualisierungs- und Löschvorgänge umfassen, ist die InnoDB-Speicher-Engine die geeignetere Wahl. Die InnoDB-Speicher-Engine reduziert nicht nur effektiv Sperren, die durch Löschungen und Aktualisierungen verursacht werden, sondern kann auch die vollständige Übermittlung und Rückgängigmachung von Transaktionen sicherstellen. Für Systeme wie Abrechnungssysteme oder Finanzsysteme, die hohe Anforderungen an die Datengenauigkeit stellen, ist InnoDB eine geeignete Wahl.

3. SPEICHER

1. Datendateien:

Jede MEMORY-Tabelle entspricht nur einer .frm-Datenträgerdatei, die zum Speichern der Tabellenstrukturdefinition verwendet wird, und die Tabellendaten werden im Speicher gespeichert. Standardmäßig wird der HASH-Index anstelle des BTREE-Index verwendet.

2. Vorteile und Nachteile:

(1) Vorteile: Die Zugriffsgeschwindigkeit ist sehr hoch, da die Daten im Speicher abgelegt werden.

(2) Nachteile: Sobald der Dienst beendet wird, gehen die Daten in der Tabelle verloren. Die Größe der Tabelle ist begrenzt.

3. Anwendbare Situationen:

Die Speicher-Engine wird hauptsächlich in Codetabellen verwendet, deren Inhalt sich nicht häufig ändert, oder als Zwischenergebnistabellen für statistische Operationen, um die Zwischenergebnisse effizient zu analysieren und die endgültigen statistischen Ergebnisse zu erhalten.

VERSCHMELZEN

1. Motorprinzip:

Die Merge-Speicher-Engine ist eine Kombination aus einer Gruppe von MyISAM-Tabellen. Diese MyISAM-Tabellen müssen genau dieselbe Struktur haben. Die Merge-Tabelle selbst enthält keine Daten. Die Merge-Tabelle kann abgefragt, aktualisiert und gelöscht werden. Diese Vorgänge werden tatsächlich an der tatsächlichen internen MyISAM-Tabelle ausgeführt.

Definieren Sie den Einfügevorgang der Zusammenführungstabelle über die Klausel insert_method: Durch die Verwendung von first oder last wird der Einfügevorgang auf die erste bzw. letzte Tabelle angewendet. Wenn Sie es nicht definieren oder als Nein definieren, bedeutet dies, dass der Einfügevorgang für diese Zusammenführungstabelle nicht ausgeführt werden kann. Der Drop-Vorgang für die Merge-Tabelle löscht lediglich die Definition der Zusammenführung und hat keine Auswirkungen auf die internen Tabellen.

2. Datendateien:

(1).frm: speichert Tabellendefinitionen.

(2) MRG: speichert Informationen über die kombinierte Tabelle, unter anderem aus welchen Tabellen die Zusammenführungstabelle besteht und auf welcher Grundlage neue Daten eingefügt werden. Sie können die Zusammenführungstabelle ändern, indem Sie die MRG-Datei ändern. Nach der Änderung müssen Sie die Tabelle jedoch leeren.

3. Anwendungsbeispiele:

TABELLE ERSTELLEN `m1` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=MyISAM STANDARD-CHARSET=utf8;
TABELLE ERSTELLEN `m2` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=MyISAM STANDARD-CHARSET=utf8;
CREATE TABLE `m` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

4. Anwendbare Situationen:

Wird verwendet, um eine Reihe identischer MyISAM-Tabellen logisch zu gruppieren und sie als einzelnes Objekt zu referenzieren. Der Vorteil der MERGE-Tabelle besteht darin, dass sie die Größenbeschränkung einer einzelnen MyISAM-Tabelle durchbrechen kann. Durch die Verteilung verschiedener Tabellen auf mehrere Festplatten kann die Zugriffseffizienz der MERGE-Tabelle effektiv verbessert werden. Dies eignet sich sehr gut für VLDB-Umgebungen wie Data Warehousing.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Kenntnisse zu gespeicherten MySQL-Prozeduren“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „Kenntnisse zu MySQL-Protokollvorgängen“, „Zusammenfassung der Kenntnisse zu MySQL-Transaktionsvorgängen“ und „Zusammenfassung der Kenntnisse zu MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.