Sprechen Sie über die MySQL-Speicher-Engine

Die Speicher-Engine von MySQL ist ein wichtiger Teil der MySQL-Architektur und der Kern der MySQL-Architektur. Die Plug-in-Speicher-Engine ist ein wichtiges Merkmal, das sie von anderen Datenbanken unterscheidet. Es befindet sich auf der Serverseite der MySQL-Architektur und ist die Implementierung der zugrunde liegenden physischen Struktur. Es wird auf verschiedene technische Arten zum Speichern von Daten in Dateien oder im Speicher verwendet. Verschiedene Speicher-Engines verfügen über unterschiedliche Speichermechanismen, Indizierungstechniken und Sperren Ebenen. Zu den gängigen MySQL-Speicher-Engines gehören InnoDB, MyISAM, Memory, Archive usw. Sie haben ihre eigenen Eigenschaften. Wir können entsprechende Speicher-Engine-Tabellen für verschiedene spezifische Anwendungen erstellen.

Bevor wir über verschiedene Speicher-Engines sprechen, müssen wir einige grundlegende Konzepte verstehen:

(1) Transaktion

Transaktion ist A Satz atomarer SQL-Anweisungen oder eine unabhängige Arbeitseinheit. Wenn die Datenbank-Engine diesen Satz von SQL-Anweisungen erfolgreich auf die Datenbank anwenden kann, wird sie ausgeführt. Wenn eine der Anweisungen aufgrund eines Absturzes oder aus anderen Gründen nicht ausgeführt werden kann. then all Keine der Anweisungen wird ausgeführt. Mit anderen Worten: Alle Anweisungen innerhalb der Transaktion werden entweder erfolgreich ausgeführt oder schlagen fehl.

Ein typisches Beispiel für eine Bankanwendung:

Angenommen, die Datenbank der Bank enthält zwei Tabellen: eine Schecktabelle und eine Spartabelle. Nun möchte ein Kunde A 2.000 Yuan von seinem Girokonto überweisen auf sein Sparkonto sind mindestens drei Schritte erforderlich:

Überprüfen Sie, ob der Girokontostand von A mehr als 2.000 Yuan beträgt.

c. 2.000 Yuan zum Sparkontostand von A hinzufügen.

Diese drei Schritte müssen in einer Transaktion zusammengefasst werden. Wenn ein Schritt fehlschlägt, müssen alle Schritte rückgängig gemacht werden. Andernfalls kann A als Kunde der Bank unerklärlicherweise 2.000 Yuan verlieren und etwas schief gehen . . Dies ist eine typische Transaktion. Alle Vorgänge in der gesamten Transaktion werden entweder erfolgreich übermittelt oder sind fehlgeschlagen. Dies ist auch das atomare Merkmal Transaktion.

(2) Lesesperre und Schreibsperre

Immer wenn mehrere SQLs Daten gleichzeitig ändern müssen, treten Probleme bei der Parallelitätskontrolle auf. .

Angenommen, es handelt sich um ein öffentliches Postfach, Benutzer A liest das Postfach und gleichzeitig löscht Benutzer B eine E-Mail im Postfach. Was wird das Ergebnis sein? Kunde A beendet den Vorgang möglicherweise mit einem Fehler beim Lesen oder liest möglicherweise inkonsistente Postfachdaten. Wenn Sie das Postfach als Tabelle in der Datenbank behandeln, können Sie feststellen, dass das gleiche Problem vorliegt.

Der Weg zur Lösung dieses klassischen Problems ist die Parallelitätskontrolle. Das heißt, beim gleichzeitigen Lesen oder Schreiben kann das Problem durch die Implementierung eines Sperrsystems gelöst werden, das aus zwei Arten von Sperren besteht. Diese beiden Arten von Sperren sind gemeinsame Sperren und exklusive Sperren, auch Lesesperren und Schreibsperren genannt.

Lesesperren werden gemeinsam genutzt, d. h. sie blockieren sich nicht gegenseitig. Mehrere Clients können gleichzeitig dieselbe Ressource lesen, ohne sich gegenseitig zu stören. Schreibsperren sind exklusiv, das heißt, eine Schreibsperre blockiert andere Schreibsperren und Lesesperren. Nur so kann sichergestellt werden, dass jeweils nur ein Benutzer schreiben kann und andere Benutzer nicht die gleiche Ressource lesen können, auf die geschrieben wird. Schreibsperren haben eine höhere Priorität als Lesesperren.

(3) Zeilensperren und Tabellensperren

Die Sperrung erfolgt jeden Moment im tatsächlichen Datenbanksystem, und die Sperre ist ebenfalls granular, was die gemeinsame Nutzung der Methode verbessert Der Zweck der gleichzeitigen Ausgabe von Ressourcen besteht darin, Sperren selektiver zu gestalten und zu versuchen, nur einen Teil der Daten zu sperren, die geändert werden müssen, und nicht alle Ressourcen. Daher ist eine präzise Sperre erforderlich. Das Sperren erfordert jedoch auch Ressourcen, einschließlich des Erwerbs der Sperre, der Überprüfung, ob die Sperre aufgehoben ist, der Freigabe der Sperre usw., was den Systemaufwand erhöht. Die sogenannte Sperrstrategie besteht darin, ein Gleichgewicht zwischen Sperraufwand und Datensicherheit zu finden. Dieses Gleichgewicht wirkt sich auch auf die Leistung aus.

Jede MySQL-Speicher-Engine verfügt über eine eigene Sperrstrategie und Sperrgranularität. Die beiden am häufigsten verwendeten wichtigen Sperrstrategien sind Tabellensperren und Zeilensperren.

Die Tabellensperre ist die kostengünstigste Strategie und sperrt die gesamte Tabelle. Wenn ein Benutzer in die Tabelle schreibt, muss er zunächst die Schreibsperre erhalten, die alle Lese- und Schreibvorgänge für die Tabelle durch andere Benutzer blockiert . Wenn keine Schreibsperre vorhanden ist, können andere lesende Benutzer die Lesesperre erhalten, und Lesesperren blockieren sich nicht gegenseitig. Die Zeilensperre kann die maximale gleichzeitige Verarbeitung unterstützen, bringt aber auch den maximalen Sperraufwand mit sich. Sie sperrt nur bestimmte Datensätze und andere Prozesse können weiterhin mit anderen Datensätzen in derselben Tabelle arbeiten. Sperren auf Tabellenebene sind schnell, weisen jedoch viele Konflikte auf, während Sperren auf Zeilenebene weniger Konflikte aufweisen, aber langsamer sind.​ ​  

 

Wenn wir die oben genannten Konzepte verstehen, können wir die Unterschiede zwischen verschiedenen Speicher-Engines gut unterscheiden.

InnoDB-Speicher-Engine

Die MySQL-Speicher-Engine kann in eine offizielle Speicher-Engine und eine Drittanbieter-Speicher-Engine unterteilt werden Mit relativ guter Leistung und automatischer Absturzwiederherstellung sind die Funktionen derzeit weit verbreitet und gehören zum Mainstream der aktuellen MySQL-Speicher-Engine. Sie sind sowohl im Transaktionsspeicher als auch im Nicht-Transaktionsspeicher beliebt.

Die InnoDB-Speicher-Engine unterstützt Transaktionen, Zeilensperren, nicht sperrende Lesevorgänge und Fremdschlüssel.

Sofern keine besonderen Gründe vorliegen, können Sie InnoDB als erste Wahl beim Erstellen von Tabellen in Betracht ziehen. InnoDB ist auch eine sehr gute Speicher-Engine, deren gründliches Erlernen sich lohnt. Wir planen, diese Speicher-Engine in Zukunft zu untersuchen, daher werden wir hier nicht näher darauf eingehen.

2. MyISAM-Speicher-Engine

MyISAM-Speicher-Engine ist die offiziell von MySQL bereitgestellte Speicher-Engine, bevor InnoDB erschien verbessert, aber es wird nach und nach eingestellt, hauptsächlich weil es keine Transaktionen unterstützt. Dies kann daran liegen, dass die Entwickler von MySQL glauben, dass nicht alle Anwendungen Transaktionen erfordern, sodass es diese Speicher-Engine gibt, die keine Transaktionen unterstützt.

MyISAM unterstützt keine Transaktionen, Sperren auf Zeilenebene, Tabellensperren und Volltextindizes. Der größte Fehler besteht darin, dass es nach einem Absturz nicht sicher wiederhergestellt werden kann.

MyISAM hat ein einfaches Design und die Daten werden in einem kompakten Format gespeichert, sodass die Leistung in bestimmten Szenarien sehr gut ist. Wenn Sie jedoch feststellen, dass alle Abfragen „gesperrt“ sind Ein langer Status, Tabellensperre ist der Übeltäter.

Daher können Sie für schreibgeschützte Daten oder Tabellen, die relativ klein sind und Reparaturvorgänge tolerieren, weiterhin MyISAM verwenden. Für Anwendungen, die keine Transaktionen erfordern, können Sie dies möglicherweise tun Für eine höhere Leistung sind Tabellen, die die MyISAM-Speicher-Engine verwenden, in der Standardbibliothek „information_schema“ vorhanden, die mit MySQL geliefert wird.

| TRIGGERS | CREATETEMPORARY TABLE `TRIGGERS` (
  `TRIGGER_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT'',
  `EVENT_MANIPULATION` varchar(6) NOT NULLDEFAULT '',
  `EVENT_OBJECT_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULLDEFAULT '',
  `EVENT_OBJECT_TABLE` varchar(64) NOT NULLDEFAULT '',
  `ACTION_ORDER` bigint(4) NOT NULL DEFAULT'0',
  `ACTION_CONDITION` longtext,
  `ACTION_STATEMENT` longtext NOT NULL,
  `ACTION_ORIENTATION` varchar(9) NOT NULLDEFAULT '',
  `ACTION_TIMING` varchar(6) NOT NULL DEFAULT'',
  `ACTION_REFERENCE_OLD_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_NEW_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_OLD_ROW` varchar(3) NOTNULL DEFAULT '',
  `ACTION_REFERENCE_NEW_ROW` varchar(3) NOTNULL DEFAULT '',
  `CREATED` datetime DEFAULT NULL,
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULLDEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULLDEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULLDEFAULT ''
)ENGINE=MyISAM DEFAULT CHARSET=utf8 |

3. Speicher-Engine

Speicher-Engine speichert die Daten in der Tabelle, sodass sie sehr schnell ist. Da es jedoch Tabellensperren unterstützt, ist die Parallelitätsleistung schlecht. Das Schlimmste ist, dass diese Speicher-Engine nach dem Neustart oder Absturz der Datenbank alle Daten in der Tabelle verliert wird im Allgemeinen in der MySQL-Engine zum Speichern des Zwischenergebnissatzes der Abfrage verwendet. Beispielsweise verfügt die mit MySQL gelieferte Standardbibliothek information_schema über viele Tabellen, die die Speicherspeicher-Engine verwenden.

|TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULTNULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULTNULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULLDEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8|

4. Archivspeicher-Engine

Die Archivspeicher-Engine unterstützt nur INSERT- und SELECT-Vorgänge sowie Zeilensperren, ist jedoch nicht transaktionssicher Der größte Vorteil der Speicher-Engine besteht darin, dass sie über ein gutes Komprimierungsverhältnis verfügt, das im Allgemeinen 1:10 erreichen kann, und dass dieselben Daten auf kleinerem Speicherplatz gespeichert werden können.

Die Archivspeicher-Engine eignet sich sehr gut zum Speichern archivierter Daten wie historischer Daten, Protokollinformationsdaten usw. Diese Art von Daten weist häufig eine sehr große Datenmenge auf und verfügt grundsätzlich nur über INSERT- und SELECT-Operationen . Durch die Verwendung dieser Speicher-Engine kann viel Speicherplatz gespart werden.

Nehmen Sie als Beispiel eine Verlaufstabelle mit 250 Millionen Datensätzen in einer bestimmten Bibliothek:

Als ursprünglich die InnoDB-Speicher-Engine standardmäßig verwendet wurde, betrug die Tabellengröße 12 GB.

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';
+------------+
| TABLE_ROWS |
+------------+
|  251755162 |
+------------+
1 row in set (0.01 sec)

Wenn die Archivspeicher-Engine verwendet wird, um die obige Tabelle neu zu erstellen und dieselben Daten erneut einzufügen, beträgt die Größe der Tabelle weniger als 2 GB, was zeigt, dass der Speicher ein gutes Komprimierungsverhältnis aufweist.

mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB')as  
data from TABLES where TABLE_NAME='history';
+------------+
| data       |
+------------+
| 12918.88MB |
+------------+
1 row in set (0.00 sec)

Andere Speicher-Engines werden weniger häufig verwendet und werden hier nicht besprochen.

Oben geht es um den Inhalt der MySQL-Speicher-Engine. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!