Parlez du moteur de stockage MySQL

Le moteur de stockage de MySQL est une partie importante de l'architecture MySQL et le cœur de l'architecture MySQL. Le moteur de stockage du plug-in est une fonctionnalité importante qui le distingue des autres bases de données. Il se trouve au bas du côté serveur de l'architecture MySQL et constitue l'implémentation de la structure physique sous-jacente. Il est utilisé pour stocker des données dans des fichiers ou en mémoire de diverses manières techniques. Différents moteurs de stockage ont des mécanismes de stockage, des techniques d'indexation et des verrouillages différents. niveaux. Les moteurs de stockage MySQL courants incluent InnoDB, MyISAM, Memory, Archive, etc. Ils ont leurs propres caractéristiques. Nous pouvons établir des tables de moteurs de stockage correspondantes en fonction de différentes applications spécifiques.

Avant de parler des différents moteurs de stockage, nous devons comprendre quelques concepts de base :

(1) Transaction

La transaction est A ensemble d'instructions SQL atomiques ou une unité de travail indépendante Si le moteur de base de données peut appliquer avec succès cet ensemble d'instructions SQL à la base de données, il sera exécuté si l'une des instructions ne peut pas être exécutée en raison d'un crash ou pour d'autres raisons. alors toutes Aucune des instructions ne sera exécutée. En d’autres termes, toutes les instructions de la transaction s’exécutent avec succès ou échouent.

Un exemple typique d'application bancaire :

Supposons que la base de données de la banque comporte deux tables : une table de chèques et une table d'épargne. Maintenant, un client A souhaite transférer 2 000 yuans depuis son compte courant. sur son compte d'épargne, alors au moins trois étapes sont nécessaires :

a. Vérifiez que le solde du compte courant de A est supérieur à 2 000 yuans

b. Soustrayez 2 000 yuans du solde du compte courant de A ;

c. Ajoutez 2 000 yuans au solde du compte d’épargne de A.

Ces trois étapes doivent être regroupées en une seule transaction. Si une étape échoue, toutes les étapes doivent être annulées, sinon A, en tant que client de la banque, peut inexplicablement perdre 2 000 yuans et quelque chose va mal. . Il s'agit d'une transaction typique.Cette transaction est la plus petite unité de travail indivisible.Toutes les opérations de la transaction entière sont soit soumises avec succès, soit échouées et annulées.Il est également impossible d'en exécuter une partie. transaction.

(2) Verrouillage en lecture et verrouillage en écriture

Chaque fois que plusieurs SQL doivent modifier des données en même temps, des problèmes de contrôle de concurrence surviendront. .

En supposant une boîte aux lettres publique, l'utilisateur A lit la boîte aux lettres, et en même temps, l'utilisateur B supprime un e-mail dans la boîte aux lettres, quel sera le résultat ? Le client A peut quitter avec une erreur lors de la lecture ou lire des données de boîte aux lettres incohérentes. Si vous traitez la boîte aux lettres comme une table dans la base de données, vous constaterez qu’elle présente le même problème.

La façon de résoudre ce type de problème classique est le contrôle de concurrence. Autrement dit, lorsqu'il s'agit de lecture ou d'écriture simultanée, le problème peut être résolu en implémentant un système de verrouillage composé de deux types de verrous. Ces deux types de verrous sont les verrous partagés et les verrous exclusifs, également appelés verrous en lecture et verrous en écriture.

Les verrous de lecture sont partagés, c'est-à-dire qu'ils ne se bloquent pas. Plusieurs clients peuvent lire la même ressource en même temps sans interférer les uns avec les autres. Les verrous d'écriture sont exclusifs, c'est-à-dire qu'un verrou d'écriture bloque les autres verrous d'écriture et de lecture. Ce n'est qu'ainsi que l'on peut garantir qu'un seul utilisateur peut effectuer l'écriture à un moment donné, empêchant ainsi les autres utilisateurs de lire la même ressource en cours d'écriture. Les verrous en écriture ont une priorité plus élevée que les verrous en lecture.

(3) Verrous de lignes et verrous de table

Le verrouillage se produit à chaque instant dans le système de base de données réel, et les verrous sont également granulaires, ce qui améliore le partage La méthode de l'émission simultanée de ressources consiste à rendre les verrous plus sélectifs et à essayer de verrouiller uniquement une partie des données qui doivent être modifiées au lieu de toutes les ressources, un verrouillage précis est donc requis. Cependant, le verrouillage consomme également des ressources, notamment l'obtention du verrou, la vérification si le verrou est libéré et la libération du verrou, etc., ce qui augmentera la surcharge du système. La stratégie dite de verrouillage consiste à trouver un équilibre entre la surcharge de verrouillage et la sécurité des données. Cet équilibre affectera également les performances.

Chaque moteur de stockage MySQL a sa propre stratégie de verrouillage et sa propre granularité de verrouillage. Les deux stratégies de verrouillage importantes les plus couramment utilisées sont les verrous de table et les verrous de ligne.

Le verrouillage de table est la stratégie la moins coûteuse et verrouillera la table entière Lorsqu'un utilisateur écrit sur la table, il doit d'abord obtenir le verrou en écriture, qui bloquera toutes les opérations de lecture et d'écriture sur la table par les autres utilisateurs. . Lorsqu'il n'y a pas de verrou en écriture, les autres utilisateurs en lecture peuvent obtenir le verrou en lecture, et les verrous en lecture ne se bloquent pas mutuellement. Le verrouillage de ligne peut prendre en charge un traitement simultané maximal, mais il entraîne également une surcharge de verrouillage maximale. Il verrouille uniquement les enregistrements spécifiés, et d'autres processus peuvent toujours fonctionner sur d'autres enregistrements de la même table. Les verrous au niveau des tables sont rapides mais comportent de nombreux conflits, tandis que les verrous au niveau des lignes ont moins de conflits mais sont plus lents.​ ​  

 

Comprenant les concepts ci-dessus, nous pouvons bien distinguer les différences entre les différents moteurs de stockage.

Moteur de stockage InnoDB

Le moteur de stockage MySQL peut être divisé en moteur de stockage officiel et moteur de stockage tiers InnoDB est un puissant moteur de stockage tiers. avec des performances relativement bonnes et des fonctionnalités de récupération automatique en cas de crash, elles sont actuellement largement utilisées et constituent le courant dominant du moteur de stockage MySQL actuel. Il est populaire à la fois dans le stockage transactionnel et dans le stockage non transactionnel.

Le moteur de stockage InnoDB prend en charge les transactions, les verrous de ligne, les lectures non verrouillables et les clés étrangères.

Sauf raisons particulières, vous pouvez envisager d'utiliser InnoDB comme premier choix lors de la création de tables. InnoDB est également un très bon moteur de stockage qui mérite d'être étudié en profondeur. Nous prévoyons d'étudier ce moteur de stockage à l'avenir, nous n'entrerons donc pas dans les détails ici.

2. Moteur de stockage MyISAM

Le moteur de stockage MyISAM est le moteur de stockage officiellement fourni par MySQL. C'était le moteur de stockage MySQL principal avant l'émergence et l'amélioration. d'InnoDB, mais il est progressivement supprimé, principalement parce qu'il ne prend pas en charge les transactions. Cela peut être dû au fait que les développeurs de MySQL pensent que toutes les applications ne nécessitent pas de transactions, donc ce moteur de stockage qui ne prend pas en charge les transactions existe.

MyISAM ne prend pas en charge les transactions, les verrous au niveau des lignes, les verrous de table et les index de texte intégral. Le plus gros défaut est qu'il ne peut pas récupérer en toute sécurité après un crash.

MyISAM a une conception simple et les données sont stockées dans un format compact, ses performances sont donc très bonnes dans certains scénarios. Cependant, ses verrous de table entraînent des problèmes de performances si vous constatez que toutes les requêtes sont "verrouillées". Statut long, le verrouillage de la table est le coupable.

Par conséquent, pour les données ou tables en lecture seule qui sont relativement petites et peuvent tolérer des opérations de réparation, vous pouvez toujours utiliser MyISAM. Pour les applications qui ne nécessitent pas de transactions, choisissez le moteur de stockage MyISAM et vous pourrez peut-être le faire. obtenir Pour de meilleures performances, les tables utilisant le moteur de stockage MyISAM existent dans la bibliothèque information_schema par défaut fournie avec MySQL.

| 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. Moteur de stockage en mémoire

Le moteur de stockage en mémoire met les données de la table en mémoire, c'est donc très rapide, mais comme il prend en charge les verrous de table, les performances de concurrence sont médiocres. Le pire est que ce moteur de stockage perdra toutes les données de la table après le redémarrage ou le crash de la base de données. Il ne convient qu'aux tables temporaires qui stockent des données temporaires. est généralement utilisé dans le moteur MySQL pour stocker l'ensemble de résultats intermédiaire de la requête. Par exemple, la bibliothèque information_schema par défaut fournie avec MySQL contient de nombreuses tables utilisant le moteur de stockage Memory.

|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. Moteur de stockage d'archives

Le moteur de stockage d'archives prend uniquement en charge les opérations INSERT et SELECT et prend en charge les verrous de ligne, mais il n'est pas sécurisé pour les transactions. Le plus grand avantage du moteur de stockage est qu'il a un bon taux de compression, qui peut généralement atteindre 1:10, et qu'il peut stocker les mêmes données dans un espace disque plus petit.

Le moteur de stockage Archive est très approprié pour stocker des données archivées, telles que des données historiques, des données d'informations de journal, etc. Ce type de données contient souvent une très grande quantité de données et ne comporte essentiellement que des opérations INSERT et SELECT. . L'utilisation de ce moteur de stockage peut économiser beaucoup d'espace sur les disques.

Prenons comme exemple une table d'historique avec 250 millions d'enregistrements dans une certaine bibliothèque :

Lorsqu'elle était initialement définie par défaut sur le moteur de stockage InnoDB, la taille de la table était de 12 Go.

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

Lorsque le moteur de stockage Archive est utilisé pour reconstruire la table ci-dessus et réinsérer les mêmes données, la taille de la table devient inférieure à 2G, ce qui montre que le stockage a un bon taux de compression.

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)

D'autres moteurs de stockage sont moins couramment utilisés et ne seront pas abordés ici.

Ce qui précède concerne le contenu du moteur de stockage MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !