Eine vorläufige Studie zur MySQL-Speicher-Engine

Eine vorläufige Studie zur MySQL-Speicher-Engine

Verzeichnis:

1. Einführung in Speicher-Engines

2. Leistungsvergleich von InnoDB und MyISAM

3 , Vergleich der Sperren zwischen MyISAM und InnoDB

4. Vergleich der Indizes zweier Speicher-Engines

1 Einführung in die Speicher-Engine

Beschreibung: MySQL-Datenbank basierend auf 5.7.19.

Abbildung 1.1 Datenbankversion

Getestet in Navicat für MySQL ::

Eingabe-SQL: Show Engines;

Abbildung 1.2 Speicher-Engine-Kategorie

Parameterbeschreibung:

Engine: Name der Speicher-Engine

Unterstützung: Ob MySQL es unterstützt Engine

Kommentar: Beschreibung der Engine

Transaktion: Ob Transaktionsverarbeitung unterstützt wird

XA: Ob verteilte Transaktionsverarbeitung >

l InnoDB

Geeignet für

Hochleistungs- und Transaktionsverarbeitungsumgebung , unterstützt external Key, die Standard-Speicher-Engine, „out of the box“.

l

MyISAM

eignet sich für

hauptsächlich schreibgeschützte Daten in Lager-, E-Commerce- und Unternehmensanwendungen. MyISAM verwendet erweiterte Caching- und Indizierungsmechanismen, um die Geschwindigkeit des Datenabrufs und der Indizierung zu verbessern, unterstützt jedoch keine Transaktionen oder Fremdschlüssel.

l

Blackhole

Gilt für

Testanwendung schreibt tatsächlich Datenszenarien wo Sie keine Daten auf der Festplatte speichern möchten. Die Blackhole-Speicher-Engine erfüllt eine bestimmte Anforderung. Wenn die binäre Protokollierung aktiviert ist, werden SQL-Anweisungen in das Protokoll geschrieben, wobei die Blackhole-Speicher-Engine als Relay oder Proxy in einer Replikationstopologie verwendet wird. In diesem Fall verarbeitet der Relay-Agent Daten vom Master und sendet die Daten an seine Slaves, speichert aber selbst keine Daten.

l

CSV

eignet sich zum

Schreiben und Konvertieren von CSV-Protokolldateien Die Struktur Importieren Sie Geschäftsdaten schnell in Tabellenkalkulationen. Die CSV-Speicher-Engine erstellt, liest und schreibt CSV-Dateien (Comma-Separated Value) im Tabellenformat. Es bietet keinen Indexierungsmechanismus, weist bestimmte Probleme beim Speichern und Konvertieren von Datums-/Uhrzeitwerten auf und ist beim Speichern von Daten nicht effizient. Daher sollte es mit Vorsicht verwendet werden.

l Speicher

Anwendbar auf statische Daten, auf die häufig zugegriffen wird und die selten geändert werden, B. Postleitzahlenlisten, Provinz- und Städtelisten, Klassifizierungslisten usw. sowie Datenbanken, die für die Verwendung von Snapshot-Technologie geeignet sind, um auf Verteilungsdaten oder historische Daten zuzugreifen . Speicher (manchmal auch HEAP genannt) ist ein In-Memory-Speicher, der einen Hashing-Mechanismus verwendet, um häufig verwendete Daten abzurufen, was einen schnelleren Abruf ermöglicht. Da die Daten im Speicher abgelegt werden und nur innerhalb der MySQL-Sitzung gültig sind, werden die Daten beim Herunterfahren aktualisiert und gelöscht.

l Verbund

Geeignet für verteilte oder Datensatzumgebungen . Die Federated Storage Engine ermöglicht die Verknüpfung von Tabellen mehrerer Datenbankserver. Es werden keine Daten verschoben und es ist nicht erforderlich, dass die Remote-Tabelle dieselbe Speicher-Engine verwendet. Die Federated Storage Engine ist derzeit in den meisten MySQL-Distributionen deaktiviert.

l Archive

eignet sich zum Speichern und Abrufen großer Mengen von sehr wenige Zugriff auf archivierte oder historische Daten. Die Archivspeicher-Engine speichert große Datenmengen in einem komprimierten Format, unterstützt keine Indizes und kann nur über Tabellenscans aufgerufen werden.

l MRG_MYISAM

Geeignet für sehr große Datenbankanwendungen , beispielsweise ein Data Warehouse, in dem Daten in mehreren Tabellen in einer oder mehreren Datenbanken gespeichert werden. Das beste Merkmal der MRG_MYISAM-Speicher-Engine ist ihre Geschwindigkeit. Sie unterteilt eine große Tabelle in viele verschiedene kleine Tabellen, speichert sie auf verschiedenen Festplatten, führt dann diese kleinen Tabellen zusammen und greift dann schneller auf sie zu. Weil jede kleine Tabelle weniger Daten verwalten muss.

Nachteile:

l Zur Bildung einer zusammengesetzten Tabelle muss dieselbe MyISAM-Tabelle verwendet werden; 🎜>

l Der Ersetzungsvorgang ist nicht verfügbar

l Der Index ist weniger effizient als der Index einer einzelnen Tabelle.

2

InnoDB- und MyISAM-Leistungsvergleich

Hinweis: Die Testtabelle enthält 36 Felder

und enthält 988218 Datensätze .

Die Testdatenbank der InnoDB-Speicher-Engine heißt Innodbtest und enthält diese Tabelle. Der Tabellenname lautet Innodbtable. Die Testdatenbank der MyISAM-Speicher-Engine heißt Myisamtest und enthält diese Tabelle , und der Tabellenname ist Myisamtable.

Verwenden Sie InnoDB- und MyISAM-Speicher-Engines in MySQL, um die Tabelle zu testen. Führen Sie zunächst die Vorarbeiten durch:

(1) Testen Sie den Speicher Engine der MyISAM-Speicher-Engine-Tabelle wird von der Standard-InnoDB in MyISAM geändert:

alter table myisamtable engine=myisam;

Abbildung 2.1 Ändern Sie die Speicher-Engine

(2) Ändern Sie die Zeichenkodierung der Datenbank und setzen Sie sie auf utf-8

alter database myisamtest character set utf8;
alter database innodbtest character set utf8;

Abbildung 2.2 Ändern des Charakters der InnoDB-Speicher-Engine-Testbibliothek Kodierung

Abbildung 2.3 Ändern der Zeichenkodierung der MyISAM-Speicher-Engine-Testbibliothek

Einige der beiden Speicher-Engine-Funktionen zum Testen:

l Speicherstruktur

(1) InnoDB:

Tabellendaten werden in einer Datendatei mit einer Größe von 1,21 GB gespeichert – Innodbtable.ibd. Metadateninformationen zur Tabelle werden in der Datei innodbtable.frm gespeichert, einschließlich der Definition der Tabellenstruktur . Information. Einige Definitionsinformationen der Datenbank werden in db.opt definiert.

Abbildung 2.4 InnoDB-Festplattenspeicherverzeichnis

Abbildung 2.5 Inhalt der db.opt-Datei

(2) MyISAM:

.frm-Datei: Speichert Metadateninformationen zur Tabelle, einschließlich Definitionsinformationen der Tabellenstruktur usw.

.MYD-Datei: 853,34 MB groß, speichert die Daten der MyISAM-Tabelle.

.MYI-Datei: 34,11 MB groß, in der indexbezogene Informationen der MyISAM-Tabelle gespeichert sind.

db.opt: ​​​​Definiert einige Definitionsinformationen der Datenbank.

Abbildung 2.6 MyISAM-Festplattenspeicherverzeichnis

Abbildung 2.7 db.opt-Dateiinhalt

l select

(1) InnoDB:

Abbildung 2.8 InnoDB-Auswahltest

(2) MyISAM:

Abbildung 2.9 MyISAM-Auswahltest

l einfügen

(1) InnoDB:

Abbildung 2.10 InnoDB-Einfügetest

(2) MyISAM :

Abbildung 2.11 MyISAM-Einfügungstest

l aktualisieren

(1) InnoDB:

Abbildung 2.12 InnoDB-Updatetest

(2) MyISAM:

Abbildung 2.13 MyISAM-Update-Test

l löschen

(1) InnoDB:

Abbildung 2.14 InnoDB-Löschtest

(2) MyISAM:

Abbildung 2.15 MyISAM-Löschtest

l löschen, wo

(1) InnoDB:

Abbildung 2.16 InnoDB löschen wobei Test

(2) MyISAM:

Abbildung 2.17 MyISAM Lösch-Where-Test

Ich zähle ohne Wo

(1) InnoDB:

Abbildung 2.18 InnoDB-Zählung ohne wobei Test

(2) MyISAM:

Abbildung 2.19 MyISAM-Zählung ohne Where-Test

l group by

（1）InnoDB：

 

图2.20 InnoDB的group by测试

（2）MyISAM：

图2.21 MyISAM的group by测试

l 外键

创建一个新表，将测试表的主键作为新表的外键进行测试：

create table `foreigntest`(
`id` int primary key not null,
`taskid` varchar(64) not null,
`host` varchar(128) not null default '',
`month` char(8) not null,
constraint `fk_task_h_m` foreign key (`taskid`,`host`,`month`)
references `innodbtable`(`taskid`,`host`,`month`)
) charset=utf8mb4

（1）InnoDB：

 

图2.22 InnoDB的外键测试

（2）MyISAM：

 

图2.23 MyISAM的外键测试

 

总结如下表：

	InnoDB	MyISAM
存储结构	.ibd：存放表数据； .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等； 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。	每个表在磁盘上存储成三个文件： .MYD文件：存放表的数据。 .MYI文件：存放表的索引相关信息。 .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等；
存储空间	InnoDB-Tabellen erfordern mehr Arbeitsspeicher und Festplattenspeicher und richten im Hauptspeicher einen eigenen dedizierten Pufferpool zum Zwischenspeichern von Daten und Indizes ein.	MyISAM kann komprimiert werden und hat weniger Speicherplatz.
Portabilität	Kostenlos Lösungen können das Kopieren von Datendateien, das Sichern von Binlog oder die Verwendung von mysqldump sein, was relativ schwierig ist, wenn das Datenvolumen Dutzende Gigabyte erreicht	Aufgrund von MyISAM Die Daten wird in Form von Dateien gespeichert und ist daher sehr praktisch für die plattformübergreifende Datenübertragung. Vorgänge können während der Sicherung und Wiederherstellung einzeln an einer Tabelle ausgeführt werden
Transaktionssicherheit	Unterstützen Sie Transaktionen mit Transaktions- (Commit), Rollback- (Rollback) und Absturzreparaturfunktionen	Unterstützt keine Transaktionen, jede Abfrage ist atomar
Erhöhen	Besser (0,15 Sekunden)	(0,40 Sekunden)
Löschen (mit Wo)	(32,79 Sekunden)	Besser (16,51 Sekunden)
Alles löschen	(263,86 Sekunden)	ist besser (0,24 Sekunden) )
Änderung	(0,20 Sekunden)	Besser (0,12 Sekunden)
Prüfen	(139,75 Sekunden)	Mehr Ausgezeichnet (65,57 Sekunden)
Sperre	Unterstützt Tabellensperren und Zeilensperren verbessern die Möglichkeiten gleichzeitiger Vorgänge für mehrere Benutzer erheblich. Die Zeilensperre von InnoDB gilt jedoch nur für den Primärschlüssel von WHERE. Jeder Nicht-Primärschlüssel WHERE sperrt die gesamte Tabelle.	Unterstützt nur Tischsperren außer
Tasten	Unterstützt	Nicht unterstützt
Zählen ohne wo	Keine spezifische Speichertabelle Anzahl der Zeilen muss Zeile für Zeile für Statistiken gescannt werden (70,88 Sekunden)	ist besser, weil MyISAM die spezifische Zeilennummer der Tabelle speichert und nur dies tun muss einfach vorgelesen werden. (0,09 Sekunden)
gruppieren nach	(35,14 Sekunden)	Besser (4,75 Sekunden)

Hinweis:

[1]Tabellenbereich: Ein von InnoDB verwendetes Tool zum Organisieren maschinenunabhängiger Dateien, einschließlich Daten, Indizes und Rückgaben Rollmechanismus. Standardmäßig teilen sich alle Tabellen einen Tablespace (sogenannter Shared Tablespace). Freigegebene Tablespaces werden nicht automatisch in mehrere Dateien erweitert. Standardmäßig belegt ein Tablespace nur eine einzige Datei, die mit zunehmender Datenmenge wächst. Verwenden Sie die Option „Autoextend“, damit der Tablespace neue Dateien erstellen kann.

[2]Absturzreparaturfunktion: Die InnoDB-Speicher-Engine verwendet zwei festplattenbasierte Mechanismen zum Speichern von Daten, nämlich Protokolldateien und Tabellenbereiche. InnoDB verwendet diese Protokolle, um die Datenwiederherstellung wiederherzustellen, bevor es herunterfährt oder abstürzt. Beim Programmstart liest InnoDB das Protokoll und schreibt automatisch fehlerhafte Seiten auf die Festplatte, um gepufferte Aktualisierungen vor einem Systemabsturz wiederherzustellen.

3 Vergleich der Sperren zwischen MyISAM und InnoDB

(1) Sperre auf Tabellenebene: geringer Overhead, schnelles Sperren; große Sperrgranularität, höchste Wahrscheinlichkeit eines Sperrenkonflikts und niedrigste Parallelität.

(2) Sperren auf Zeilenebene: Hoher Overhead, langsame Sperren können am geringsten sein, die Wahrscheinlichkeit von Sperrenkonflikten ist am geringsten und die Parallelität ist gering das Höchste.

(3) Lesevorgänge für die MyISAM-Tabelle blockieren nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, blockieren jedoch Schreibanforderungen für dieselbe Tabelle , blockiert die Lese- und Schreibanforderungen anderer Benutzer für dieselbe Tabelle. Die Lese- und Schreibvorgänge der MyISAM-Tabelle sind seriell (wenn ein Thread eine Schreibsperre für eine Tabelle erhält, nur der Thread, der die Die Sperre kann die Tabelle aktualisieren. Die Lese- und Schreibvorgänge anderer Threads warten, bis die Sperre aufgehoben wird)

(4) Gemeinsame Sperre(n): Ermöglicht einer Transaktion das Lesen einer Zeile Dadurch wird verhindert, dass andere Transaktionen eine exklusive Sperre für denselben Datensatz erhalten.

(5) Exklusive Sperre (X): Ermöglichen Sie Transaktionen, die exklusive Sperren erwerben, das Aktualisieren von Daten und verhindern Sie, dass andere Transaktionen die gleiche gemeinsam genutzte Lesesperre und exklusive Schreibsperre für den Datensatz erwerben.

(6) Bei UPDATE-, DELETE- und INSERT-Anweisungen fügt InnoDB automatisch exklusive Sperren (X) zu den beteiligten Datensätzen hinzu; bei normalen SELECT-Anweisungen fügt InnoDB keine Sperren hinzu.

4 Vergleich zweier Storage-Engine-Indizes

l InnoDB:

l In InnoDB ist die Tabellendatendatei selbst eine durch B+Tree organisierte Indexstruktur. Dieser Baum Der Blattknoten Das Datenfeld speichert vollständige Datensätze. Der Schlüssel dieses Index ist der Primärschlüssel der Datentabelle, daher ist die InnoDB-Tabellendatendatei selbst der Primärindex.

l Der von der InnoDB-Tabelle verwendete Index ist ein Clustered-Index. Ein Clustered-Index ist eine Datenstruktur, die nicht nur den Index, sondern auch die Daten selbst speichert. Sobald ein Wert im Index gefunden wird, können die Daten daher direkt ohne zusätzliche Festplattensuchen abgerufen werden.

l Der Primärschlüsselindex oder der erste Index der Tabelle wird mithilfe eines Clustered-Index erstellt.

l Alle Hilfsindizes in InnoDB referenzieren den Primärschlüssel als Datenfeld. Wenn ein Hilfsindex erstellt wird, werden die Schlüsselwörter des Clustered-Index (Primärschlüssel, eindeutiger Schlüssel oder Zeilen-ID) auch im Hilfsindex gespeichert, sodass Sie schnell nach Schlüsselwörtern suchen und schnell die Originaldaten in erhalten können Clustered-Index. Das heißt, wenn Sie die Primärschlüsselspalte zum Scannen des Hilfsindex verwenden, muss die Abfrage nur den Hilfsindex verwenden, um Daten abzurufen.

l MyISAM:

l Indexdateien und Datendateien sind getrennt, der Index Die Datei speichert lediglich die Adresse des Datensatzes. Unter Verwendung von B+tree als Indexstruktur speichert das Datenfeld des Blattknotens die Adresse des Datensatzes.

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

l Hauptunterschiede:

l Der Unterschied zwischen Primärindizes: Die Datendateien von InnoDB selbst sind Indexdateien. Der Index und die Daten von MyISAM sind getrennt.

l Der Unterschied zwischen Hilfsindizes: Das Hilfsindexdatenfeld von InnoDB speichert den Wert des Primärschlüssels des entsprechenden Datensatzes anstelle der Adresse. Es gibt keinen großen Unterschied zwischen dem Sekundärindex von MyISAM und dem Primärindex.

Hinweis:

B+-Baum: Für einen B+-Baum m-Ordnung gilt Folgendes Eigenschaften:

l Ein Knoten mit n Teilbäumen enthält n Schlüsselwörter.

l Alle Blattknoten enthalten Informationen zu allen Schlüsselwörtern und Verweise auf Datensätze, die diese Schlüsselwörter enthalten. Und die Blattknoten selbst sind entsprechend der Größe der Schlüsselwörter in der Reihenfolge von klein nach groß verknüpft.

l Alle nichtterminalen Knoten können als Indexteile betrachtet werden, und der Knoten enthält nur das größte (oder kleinste) Schlüsselwort in seinem Unterbaum (Wurzelknoten).

l Im B+-Baum nimmt jede Suche einen Pfad von der Wurzel zum Blattknoten, unabhängig davon, ob die Suche erfolgreich ist oder nicht.

l Jeder Knoten im Baum enthält höchstens m Teilbäume.

Das obige ist der detaillierte Inhalt vonEine vorläufige Studie zur MySQL-Speicher-Engine. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!