Explication détaillée de l'architecture MySQL passionnante et des connaissances du moteur de stockage InnoDB

Cet article vous apporte des connaissances sur l'architecture avancée du moteur de stockage MYSQL et InnoDB. J'espère qu'il vous sera utile.

Schéma d'architecture de base de MySQL

De manière générale, MySQL peut être divisé en deux parties : la couche serveur et la couche moteur de stockage.

La couche serveur comprend des connecteurs, des caches de requêtes, des analyseurs, des optimiseurs, des exécuteurs, etc., couvrant la plupart des fonctions de base du service MySQL, ainsi que toutes les fonctions intégrées (telles que la date, l'heure, les fonctions mathématiques et de chiffrement, etc. ), toutes croisées- Les fonctions du moteur de stockage sont implémentées dans cette couche, telles que les procédures stockées, les déclencheurs, les vues, etc.

Connecteur

Le connecteur est ce que vous utilisez lors de la connexion à la base de données. Il est responsable de l'établissement d'une connexion avec le client, de l'obtention des autorisations, du maintien et de la gestion de la connexion.

Commande : mysql -h$ip -P$port -u$user -p, appuyez sur Entrée et saisissez le mot de passe. Vous pouvez également saisir le mot de passe après -p, mais il existe un risque de fuite de mot de passe.

afficher la liste des processus, vous pouvez vérifier l'état de la connexion. Il y a une veille dans la colonne Commande pour indiquer que la connexion est inactive.

Les connexions inactives seront déconnectées pendant 8 heures par défaut et peuvent être configurées par le paramètre wait_timeout.

Dans la base de données, une connexion longue signifie qu'une fois la connexion réussie, si le client continue de faire des requêtes, la même connexion sera toujours utilisée. Une connexion courte signifie que la connexion est déconnectée après l'exécution de quelques requêtes et qu'une nouvelle est rétablie pour la requête suivante.

Étant donné que l'établissement d'une connexion consomme des ressources, il est recommandé d'utiliser autant que possible des connexions longues. Cependant, après avoir utilisé des connexions longues, la mémoire occupée par MySQL augmente très rapidement. En effet, la mémoire temporairement utilisée par MySQL lors de l'exécution est gérée. dans l'objet de connexion. Ces ressources seront libérées lorsque la connexion sera déconnectée. Par conséquent, si de longues connexions s'accumulent, elles peuvent occuper trop de mémoire et être interrompues de force par le système (MOO). À en juger par le phénomène, MySQL redémarre anormalement.

Solution :

Déconnectez régulièrement les connexions longues. Après l'avoir utilisé pendant un certain temps, ou après que le programme a déterminé qu'une requête volumineuse occupant de la mémoire a été exécutée, la connexion est déconnectée, puis la requête est requise puis reconnectée.

Si vous utilisez MySQL 5.7 ou une version plus récente, vous pouvez réinitialiser la ressource de connexion en exécutant mysql_reset_connection chaque fois que vous effectuez une opération relativement importante. Ce processus ne nécessite pas de reconnexion ni de vérification des autorisations, mais restaurera la connexion à l'état où elle vient d'être créée.

Cache de requêtes

Le cache de requêtes met en cache les instructions précédemment exécutées et leurs résultats en mémoire sous la forme de paires clé-valeur. La clé est l'instruction de requête et la valeur est le résultat de la requête. Si votre requête parvient à trouver la clé directement dans ce cache, alors la valeur sera renvoyée directement au client.

Le cache de requêtes a été supprimé dans MYSQL8 En raison des échecs fréquents du cache de requêtes, le taux de réussite est faible.

Analyzer

L'analyseur effectuera d'abord une "analyse lexicale" pour identifier quelles sont les chaînes à l'intérieur et ce qu'elles représentent. Ensuite, vous devez effectuer une « analyse syntaxique » pour déterminer si l'instruction SQL que vous avez saisie satisfait à la syntaxe MySQL.

Optimizer

Executor

La couche moteur de stockage est responsable du stockage et de la récupération des données. Son modèle architectural est plug-in et prend en charge plusieurs moteurs de stockage tels que InnoDB, MyISAM et Memory. Le moteur de stockage le plus couramment utilisé est désormais InnoDB, qui est devenu le moteur de stockage par défaut depuis MySQL 5.5.5.

Un processus d'exécution de l'instruction Select

La figure ci-dessus prend le moteur de stockage InnoDB comme exemple Le processus de traitement est le suivant :

• L'utilisateur envoie une requête à Tomcat et la connexion. est établi via le pool de liens Tomcat et le pool de connexions MySQL. Envoyez ensuite l'instruction SQL à MySQL via la connexion

• MySQL a un thread d'écoute séparé, lisez les données de la requête et obtenez l'instruction SQL demandée dans la connexion ;

• Envoyer les données SQL obtenues à l'interface SQL Exécution ;

• L'interface SQL envoie SQL à l'analyseur SQL pour analyse ;

• envoie le SQL analysé à l'optimiseur de requêtes, trouve le chemin de requête optimal, puis l'envoie. à l'exécuteur testamentaire ;

• L'exécuteur appelle l'interface du moteur de stockage selon le plan d'exécution optimisé et l'exécute dans un certain ordre et dans certaines étapes.

• Par exemple, l'exécuteur peut d'abord appeler une interface du moteur de stockage pour obtenir la première ligne de données de la table "users", puis déterminer si la valeur du champ "id" de ces données est égale à ce nous attendons une valeur, sinon, continuez à appeler l'interface du moteur de stockage pour obtenir la ligne de données suivante dans la table "utilisateurs". Sur la base de l'idée ci-dessus, l'exécuteur utilisera un ensemble de plans d'exécution générés par notre optimiseur, puis appellera en permanence diverses interfaces du moteur de stockage pour compléter le plan d'exécution de l'instruction SQL, qui consiste en gros à mettre à jour ou à extraire en continu. les données sortent.

Plusieurs questions se posent ici :

Qu'est-ce que le pilote MySQL exactement ?

Prenons Java comme exemple. Si nous voulons accéder à une base de données MySQL dans le système Java, nous devons ajouter un pilote MySQL aux dépendances du système. Par exemple, dans Maven, nous devons ajouter

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

Alors, qu'est-ce que c'est exactement. Pilote MySQL ? Quoi ? En fait, le pilote L établira une connexion réseau avec la base de données au niveau de la couche inférieure, une fois qu'il y aura une connexion réseau, il pourra alors envoyer une requête au serveur de base de données ! Laissez le système écrit dans le langage accéder à la base de données via le pilote MySQL, comme indiqué ci-dessous

À quoi sert le pool de connexions à la base de données ?

Supposons qu'un service Web soit développé en Java et déployé sur Tomcat. Tomcat peut traiter les requêtes simultanément avec plusieurs threads, donc la première chose est qu'il est impossible de créer une seule connexion à la base de données (plusieurs requêtes pour récupérer une seule connexion, c'est beaucoup inefficace).

Deuxièmement, que se passe-t-il si une connexion à la base de données est créée pour chaque requête ? C'est également très mauvais, car cela prend beaucoup de temps d'établir une connexion à la base de données à chaque fois. Il n'est pas facile d'établir la connexion, d'exécuter l'instruction SQL, puis supprimez la base de données. Les connexions entraînent des problèmes de performances en termes de destruction, de création et de destruction fréquentes.

Utilisez donc généralement un pool de connexions à la base de données, c'est-à-dire maintenez plusieurs connexions à la base de données dans un pool, laissez plusieurs threads y utiliser différentes connexions à la base de données pour exécuter des instructions SQL, puis après avoir exécuté l'instruction SQL, ne détruisez pas la connexion à la base de données. Au lieu de cela, la connexion est remise dans le pool et peut être réutilisée ultérieurement. Sur la base d'un tel mécanisme de pool de connexions à la base de données, le problème de plusieurs threads utilisant simultanément plusieurs connexions à la base de données pour exécuter des instructions SQL peut être résolu, et le problème de la destruction des connexions à la base de données après utilisation peut être évité.

À quoi sert le pool de connexions de la base de données MySQL ?

Le pool de connexions de la base de données MySQL a la même fonction que le pool de connexions de l'application Java, et tous deux jouent le rôle de réutilisation des connexions.

Moteur de stockage InnoDB

Brève analyse de l'architecture InnoDB

Comme le montre l'image, le moteur de stockage InnoDB se compose de trois parties : un pool de mémoire, un thread d'arrière-plan et un fichier disque

Un autre un pour mettre en évidence les points clés Image :

InnoDB Storage Engine Première partie : Structure de la mémoire

Buffer Pool

Le moteur de stockage InnoDB est basé sur le stockage sur disque et gère les enregistrements dans les pages Mais. en raison de l'écart entre la vitesse du processeur et la vitesse du disque, les systèmes de bases de données sur disque utilisent souvent des enregistrements de pool de mémoire tampon pour améliorer les performances globales de la base de données.

Lors d'une opération de lecture dans la base de données, la page lue à partir du disque est placée dans le pool de mémoire tampon. La prochaine fois que la même page est lue, déterminez d'abord si la page se trouve dans le pool de mémoire tampon. Si c'est dans le pool tampon, on dit que la page est atteinte dans le pool tampon et la page est lue directement. Sinon, la page sur le disque est lue.

Pour l'opération de modification des pages de la base de données, la page dans le pool de tampons est d'abord modifiée, puis actualisée sur le disque à une certaine fréquence. L'opération de rafraîchissement de la page du pool de tampons vers le disque n'est pas déclenchée. chaque fois que la page est mise à jour, elle est renvoyée sur le disque via un mécanisme appelé CheckPoint. Par conséquent, la taille du pool de tampons affecte directement les performances globales de la base de données. Elle peut être définie via le paramètre de configuration innodb_buffer_pool_size. Le pool de tampons par défaut est de 128 Mo, ce qui est encore un peu petit si votre base de données est une base de données de 32 Go à 16 cœurs. machine, vous pouvez alors donner à Buffer Pool alloue 2 Go de mémoire.

Étant donné que le pool de tampons n'est pas infini, comme les pages de données sur le disque sont continuellement chargées dans le pool de tampons, le pool de tampons sera toujours utilisé. À ce stade, certaines pages de cache ne peuvent être éliminées que par la méthode d'élimination. le dernier L'algorithme le moins utilisé (LRU), en particulier, introduit une nouvelle liste chaînée LRU. Grâce à cette liste chaînée LRU, vous pouvez savoir quelles pages de cache sont les moins récemment utilisées. Ensuite, lorsque vous devez libérer une page de cache, videz-la. sur le disque À ce stade, vous pouvez sélectionner la page de cache la moins récemment utilisée dans la liste LRU à éliminer.

Les types de pages de données mises en cache dans le pool de mémoire tampon incluent : les pages d'index, les pages de données, les pages d'annulation, les tampons d'insertion, les index de hachage adaptatifs, les informations de verrouillage et les informations du dictionnaire de données stockées dans InnoDB.

Page de données et page d'index

Page (Page) est la structure la plus basique du stockage Innodb et la plus petite unité de gestion de disque Innodb. Tout le contenu lié à la base de données est stocké dans la structure Page. Les pages sont divisées en plusieurs types, les pages de données et les pages d'index étant les deux types les plus importants.

Insert Buffer

Lors de l'exécution d'opérations d'insertion sur le moteur InnoDB, il est généralement nécessaire d'insérer dans l'ordre de la clé primaire, afin d'obtenir des performances d'insertion plus élevées. Lorsqu'il y a un index non unique non clusterisé dans une table, lors de l'insertion, les pages de données sont toujours stockées dans l'ordre selon la clé primaire, mais l'insertion des nœuds feuilles d'index non clusterisés n'est plus séquentielle à ce moment. , il est nécessaire que l'accès discret aux pages d'index non clusterisées entraîne une dégradation des performances des opérations d'insertion en raison de la présence de lectures aléatoires.

Le moteur de stockage InnoDB a donc été le pionnier de la conception d'Insérer un tampon. Pour les opérations d'insertion ou de mise à jour d'un index non clusterisé, au lieu d'insérer directement dans la page d'index à chaque fois, il détermine d'abord si la page d'index non clusterisée insérée se trouve dans le tampon. pool. S'il est là, il sera inséré directement ; s'il n'est pas là, il sera d'abord placé dans un objet Insert Buffer, ce qui semble être de la triche. L'index non clusterisé de la base de données a été inséré dans le nœud feuille, mais il n'y est pas réellement. Il est simplement stocké dans un autre emplacement. Ensuite, l'opération de fusion du tampon d'insertion et des sous-nœuds de la page d'index auxiliaire est effectuée avec une certaine fréquence et situation. À ce stade, plusieurs insertions peuvent généralement être fusionnées en une seule opération (car elles se trouvent dans une seule page d'index). ce qui améliore considérablement l'efficacité. Amélioration des performances pour les insertions d'index non clusterisées.

Cependant, l'utilisation d'Insert Buffer doit remplir les deux conditions suivantes en même temps :

• L'index est un index secondaire (index secondaire) ;

• L'index n'est pas unique (unique).

Lorsque les deux conditions ci-dessus sont remplies, le moteur de stockage InnoDB utilisera Insert Buffer, ce qui peut améliorer les performances des opérations d'insertion. Mais considérons une situation dans laquelle l'application effectue un grand nombre d'opérations d'insertion, qui impliquent toutes des index non clusterisés non uniques, c'est-à-dire l'utilisation d'Insert Buffer. Si la base de données MySQL tombe en panne à ce moment-là, il y aura un grand nombre de tampons d'insertion qui ne seront pas fusionnés dans l'index non clusterisé réel.

La récupération à cette période peut donc prendre beaucoup de temps, voire plusieurs heures dans les cas extrêmes. L'index auxiliaire ne peut pas être unique car lors de l'insertion du tampon, la base de données ne consulte pas la page d'index pour déterminer le caractère unique de l'enregistrement inséré. Si vous effectuez une recherche, il y aura certainement des lectures discrètes, ce qui fera perdre à Insert Buffer son sens.

Vous pouvez afficher les informations du tampon d'insertion via la commande SHOW ENGINE INNODB STATUS

seg size indique que la taille actuelle du tampon d'insertion est de 11336 × 16 Ko, soit environ 177 Mo de liste libre len ​​représente la longueur de ; la taille de la liste libre représente le nombre de pages d'enregistrement fusionnées. La deuxième ligne en gras est peut-être ce qui intéresse vraiment les utilisateurs, car elle montre l'amélioration des performances d'insertion. Les insertions représentent le nombre d'enregistrements insérés ; les enregistrements fusionnés représentent le nombre d'enregistrements insérés fusionnés ; les fusions représentent le nombre de fusions, qui est le nombre de lectures de pages réelles. fusions : les enregistrements fusionnés sont d'environ 1:3, ce qui signifie que le tampon d'insertion réduit les requêtes logiques d'E/S discrètes pour les pages d'index non clusterisées d'environ 2/3.

Comme mentionné précédemment, il y a actuellement un problème avec Insert Buffer : dans le cas d'une écriture intensive, le tampon d'insertion occupera trop de mémoire du pool de tampons (pool de tampons innodb par défaut, il peut en occuper jusqu'à la moitié). le pool de mémoire tampon. Voici l'opération d'initialisation du tampon d'insertion dans le code source du moteur de stockage InnoDB :

Change Buffer

InnoDB a introduit Change Buffer depuis la version 1.0.x, qui peut être considérée comme une version améliorée d'Insert Buffer. , InnodB Le moteur de stockage peut mettre en mémoire tampon les opérations DML - INSERT, DELETE et UPDATE. Ce sont : Insert Buffer, Delete Buffer et Purge buffer. Bien sûr, comme le précédent Insert Buffer, les objets applicables de Change Buffer ne sont toujours pas uniques. index auxiliaires.

L'opération UPDATE sur un enregistrement peut être divisée en deux processus :

• marquer l'enregistrement comme supprimé

• supprimer réellement l'enregistrement

Par conséquent, Supprimer le tampon correspond au premier processus de l'opération UPDATE, Il s'agit de marquer l'enregistrement pour suppression. PurgeBuffer correspond au deuxième processus de l'opération UPDATE, qui s'apprête à enregistrer la suppression proprement dite. Dans le même temps, le moteur de stockage InnoDB fournit le paramètre innodb_change_buffering, qui est utilisé pour activer diverses options de Buffer. Les valeurs facultatives de ce paramètre sont : Insérations, suppressions, purges, modifications, tout et aucun. Les insertions, les suppressions et les purges sont les trois situations évoquées précédemment. changes signifie activer les insertions et les suppressions, all signifie activer tout, et none signifie n'activer aucun. La valeur par défaut de ce paramètre est all.

从 InnoDB1.2.x版本开始,可以通过参数 innodb_change_buffer_max_size 来控制Change Buffer最大使用内存的数量:

mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.05 sec)

innodb_change_buffer_max_size 值默认为25,表示最多使用1/4的缓冲池内存空间。

而需要注意的是,该参数的最大有效值为50在 MySQL5.5版本中通过命令 SHOW ENGINE INNODB STATUS,可以观察到类似如下的内容:

可以看到这里显示了 merged operations和 discarded operation,并且下面具体显示 Change Buffer中每个操作的次数。 Insert 表示 Insert Buffer; delete mark表示 Delete Buffer; delete表示 Purge Buffer; discarded operations表示当 Change Buffer发生 merge时,表已经被删除,此时就无需再将记录合并(merge)到辅助索引中了。

自适应哈希索引

InnoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB 存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

自适应哈希索引通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须一样的，也就是说其查询的条件必须完全一样，而且必须是连续的。

锁信息（lock info）

我们都知道，InnoDB 存储引擎会在行级别上对表数据进行上锁，不过 InnoDB 打开一张表，就增加一个对应的对象到数据字典。

数据字典

对数据库中的数据、库对象、表对象等的元信息的集合。在 MySQL 中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容，MySQL INFORMATION_SCHEMA 库提供了对数据局元数据、统计信息、以及有关MySQL Server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。

预读机制

MySQL的预读机制，就是当你从磁盘上加载一个数据页的时候，他可能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去！

举个例子，假设现在有两个空闲缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓存里去了，正好每个数据页放入一个空闲缓存页！

哪些情况下会触发MySQL的预读机制？

• 有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。

• 如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去这个机制是通过参数innodb_random_read_ahead来控制的，他默认是OFF，也就是这个规则是关闭的。

所以默认情况下，主要是第一个规则可能会触发预读机制，一下子把很多相邻区里的数据页加载到缓存里去。

预读机制的好处为了提升性能。假设你读取了数据页01到缓存页里去，那么接下来有可能会接着顺序读取数据页01相邻的数据页02到缓存页里去，这个时候，是不是可能在读取数据页02的时候要再次发起一次磁盘IO？

所以为了优化性能，MySQL才设计了预读机制，也就是说如果在一个区内，你顺序读取了好多数据页了，比如数据页01到数据页56都被你依次顺序读取了，MySQL会判断，你可能接着会继续顺序读取后面的数据页。那么此时就提前把后续的一大堆数据页（比如数据页57到数据页72）都读取到Buffer Pool里去。

缓冲池内存管理

这里需要了解三个链表（Free List、Flush List、LRU List），

• Les pages de données et les pages de cache sur le disque Free List sont en correspondance individuelle, les deux font 16 Ko et une page de données correspond à une page de cache. La base de données concevra une liste chaînée gratuite pour le Buffer Pool, qui est une structure de données de liste chaînée bidirectionnelle, chaque nœud est l'adresse d'une page de cache gratuite décrivant le bloc de données. tant qu'une page de cache est libre, alors son bloc de données de description sera placé dans cette liste chaînée gratuite. Lorsque la base de données est démarrée pour la première fois, toutes les pages de cache peuvent être libres, car il peut s'agir d'une base de données vide sans aucune donnée, donc les blocs de données de description de toutes les pages de cache seront mis dans la liste chaînée libre à ce moment-là. , cette liste chaînée gratuite a un nœud de base, qui fait référence au nœud principal et au nœud de queue de la liste chaînée, elle stocke également le nombre de nœuds décrivant les blocs de données qu'il y a dans la liste chaînée, c'est-à-dire le nombre de pages de cache libres. .

• Flush List est similaire à la liste chaînée Free List. L'essence de la liste chaînée flush est d'utiliser deux pointeurs dans le bloc de données de description de la page de cache pour former une liste chaînée bidirectionnelle en utilisant le bloc de données de description de la page de cache. page de cache modifiée. Toute page de cache qui a été modifiée verra son bloc de données de description ajouté à la liste des liens de vidage. Flush signifie qu'il s'agit de pages sales et qu'elles seront vidées sur le disque à l'avenir.
  

• Liste LRU Puisque la taille du pool de mémoire tampon est certaine, en d'autres termes, les données de la page de cache libre dans la liste chaînée gratuite sont certaines lorsque vous continuez à charger les pages de données sur le disque dans la page de cache libre, Les pages de cache gratuites sont constamment supprimées de la liste chaînée. Tôt ou tard, il y aura un moment où il n'y aura plus de pages de cache gratuites dans la liste chaînée gratuite. À ce stade, certaines pages de cache devront être éliminées. éliminé ? Cela nécessite l'utilisation du taux de réussite du cache. Ceux qui ont le plus de succès dans le cache sont couramment utilisés, et ceux qui ne le sont pas couramment peuvent être éliminés. Par conséquent, la liste chaînée LRU est introduite pour déterminer quelles pages de cache ne sont pas couramment utilisées.
  

Quelle est la stratégie d'élimination de la liste chaînée LRU ?

Supposons que lorsque nous chargeons une page de données du disque vers la page de cache, nous plaçons le bloc de données de description de cette page de cache dans l'en-tête de la liste chaînée LRU. Ensuite, tant qu'il y aura une page de cache avec des données, elle le sera. être dans le LRU, et récemment La page de cache des données chargées sera placée en tête de la liste chaînée LRU, puis une certaine page de cache sera ajoutée à la queue. Tant qu'une requête se produit, elle sera déplacée. à la tête, puis la queue devra être éliminée.

Mais est-ce vraiment bien ?

Dans le premier cas, le mécanisme de lecture anticipée est détruit

Étant donné que le mécanisme de lecture anticipée chargera dans le cache les pages de données adjacentes qui n'ont pas été consultées, en réalité, une seule page du cache est consultée et l'autre est consultée. via le mécanisme de lecture anticipée En fait, personne n'a accédé à la page de cache chargée pour le moment, les deux pages de cache se trouvent devant la liste chaînée LRU, comme indiqué ci-dessous

À ce moment, s'il n'y en a pas. pages de cache gratuites, alors une nouvelle doit être chargée à ce moment-là. S'il y a une page de données, devons-nous retirer la "page de cache la moins récemment utilisée" de la fin de la liste chaînée LRU, la vider sur le disque, puis libérez une page de cache libre. C'est évidemment très déraisonnable.

La deuxième situation peut entraîner l'élimination des pages de cache fréquemment consultées.

L'analyse complète de la table l'amène à charger directement toutes les pages de données de la table du disque vers le pool de tampons en même temps. A ce moment, toutes les pages de données de cette table peuvent être chargées dans chaque page de cache une par une ! À l'heure actuelle, il est possible qu'une grande liste de pages de cache au début de la liste chaînée LRU soit toutes des pages de cache chargées via une analyse complète de la table ! Et si après cette analyse complète du tableau, les données de ce tableau étaient à peine utilisées à l'avenir ? À l'heure actuelle, la fin de la liste chaînée LRU peut être toutes des pages mises en cache qui ont été fréquemment consultées auparavant ! Ensuite, lorsque vous souhaitez éliminer certaines pages de cache pour libérer de l'espace, vous éliminerez les pages de cache fréquemment consultées à la fin de la liste LRU, laissant un grand nombre de pages rarement consultées qui ont été chargées par l'analyse complète précédente de la table. Page en cache !

Optimiser l'algorithme LRU : concevoir la liste chaînée LRU basée sur l'idée de séparation des données chaudes et froides

Lorsque MySQL conçoit la liste chaînée LRU, il adopte en fait l'idée de séparer les données chaudes et froides . La liste chaînée LRU sera divisée en deux parties, l'une étant des données chaudes et l'autre des données froides. Le rapport entre les données chaudes et froides est contrôlé par le paramètre innodb_old_blocks_pct. Sa valeur par défaut est 37, ce qui signifie que les données froides représentent 37. %. Lorsque la page de données est chargée dans le cache pour la première fois, la page de cache sera en fait placée en tête de la liste chaînée dans la zone de données froides.

Ensuite, MySQL a défini une règle. Il a conçu un paramètre innodb_old_blocks_time. La valeur par défaut est 1000, soit 1000 millisecondes. Après le chargement d'une page de données dans la page de cache, vous accédez à la page de cache après 1 seconde. être déplacé en tête de la liste chaînée dans la zone de données chaudes. Parce que supposons que vous chargez une page de données dans le cache, puis que vous accédez à cette page de cache après 1 seconde, ce qui signifie que vous y accéderez probablement fréquemment à l'avenir. La limite de temps est de 1 seconde, vous n'accéderez donc à cette page mise en cache qu'après 1 seconde. . Page de cache, il placera la page de cache en tête de la liste chaînée dans la zone de données chaudes pour vous.

Dans ce cas, les données pour la pré-lecture et l'analyse complète du tableau se trouveront uniquement dans l'en-tête des données froides et n'entreront pas dans la zone des données chaudes depuis le début.

L'algorithme LRU est extrêmement optimisé

Les règles d'accès à la zone de données chaudes de la liste chaînée LRU sont optimisées, c'est-à-dire uniquement lorsque les pages de cache dans les derniers 3/4 de la zone de données chaudes sont accessibles , vous serez déplacé vers la tête de la liste chaînée. Si le premier quart des pages de cache dans la zone de données chaudes est consulté, elles ne seront pas déplacées vers l'en-tête de la liste chaînée.

Par exemple, en supposant qu'il y ait 100 pages de cache dans la liste chaînée de la zone de données chaudes, les 25 premières pages de cache ne seront pas déplacées vers la tête de la liste chaînée même si elles sont consultées. Mais pour les 75 pages de cache suivantes, tant qu'elles seront consultées, elles seront déplacées en tête de la liste chaînée. De cette façon, il peut réduire autant que possible le mouvement des nœuds dans la liste chaînée.

Moment de l'élimination des pages de cache par liste chaînée LRU

Lorsque MySQL exécute CRUD, il exploite d'abord un grand nombre de pages de cache et plusieurs listes chaînées correspondantes. Ensuite, lorsque les pages de cache sont pleines, vous devez trouver un moyen de vider certaines pages de cache sur le disque, puis vider ces pages de cache, puis charger les pages de données requises dans les pages de cache !

Nous savons déjà qu'il élimine les pages de cache basées sur la liste chaînée LRU, alors quand a-t-il vidé les pages de cache dans la zone de données froides de la liste chaînée LRU sur le disque ? En fait, il dispose des trois opportunités suivantes :

Vider régulièrement certaines pages de cache à la fin du LRU sur le disque

• Le thread d'arrière-plan exécute une tâche planifiée. Cette tâche planifiée videra la zone de données froides de. la liste chaînée LRU de temps en temps. Videz certaines pages de cache à la fin de la page sur le disque, effacez ces pages de cache et rajoutez-les à la liste chaînée gratuite.

Videz périodiquement certaines pages de cache de la liste chaînée de vidage sur le disque

Si vous videz simplement les pages de cache dans la zone de données froides de la liste chaînée LRU sur le disque, ce n'est pas suffisant, car il y a de nombreuses pages de cache dans la zone de données chaudes de la liste chaînée. Elles peuvent également être modifiées fréquemment. Ne seront-elles jamais vidées sur le disque ?

Ainsi, ce fil d'arrière-plan videra également toutes les pages de cache de la liste de liens de vidage sur le disque lorsque MySQL n'est pas très occupé. De cette façon, les données que vous avez modifiées seront vidées sur le disque tôt ou tard !

Tant qu'une vague de pages de cache dans la liste chaînée flush est vidée sur le disque, ces pages de cache seront également supprimées de la liste chaînée flush et de la liste chaînée lru, puis ajoutées à la liste chaînée gratuite !

Donc, l'effet global est de charger continuellement des données dans la page de cache, d'interroger et de modifier constamment les données du cache, puis les pages de cache dans la liste chaînée gratuite continuent de diminuer et les pages de cache dans la liste chaînée vide continuent d'augmenter. , les pages de cache dans la liste chaînée lru augmentent et bougent constamment.

De l'autre côté, votre thread d'arrière-plan vide constamment les pages de cache de la zone de données froides de la liste chaînée lru et les pages de cache de la liste chaînée vide dans le disque pour vider les pages de cache, puis vider le liste chaînée et les pages de cache dans la liste chaînée lru En diminuant, les pages de cache dans la liste libre augmentent.

La liste chaînée gratuite n'a pas de pages de cache gratuites

Si toutes les listes chaînées gratuites sont utilisées, si vous souhaitez charger la page de données du disque dans une page de cache libre à ce moment-là, elle sera chargée à partir du zone de données froides de la liste chaînée LRU Si une page de cache est trouvée à la fin, ce doit être la page de cache la moins fréquemment utilisée ! Ensuite, videz-le sur le disque et effacez-le, puis chargez la page de données dans la page de cache gratuite !

Pour résumer, l'utilisation des trois listes chaînées, lorsque le pool de tampons est utilisé, il chargera en fait fréquemment les pages de données du disque dans sa page de cache, puis la liste chaînée gratuite, la liste chaînée vide et la liste chaînée lru. seront tous chargés en même temps. Par exemple, si les données sont chargées dans une page de cache, la page de cache sera supprimée de la liste chaînée gratuite, puis le chef de la zone de données froides de la liste chaînée lru le sera. être placé dans la page de cache.

Ensuite, si vous modifiez une page de cache, la page sale sera enregistrée dans la liste chaînée flush, et la liste chaînée lru peut également vous déplacer de la zone de données froides vers la tête de la zone de données chaudes.

Si vous interrogez une page de cache, la page de cache sera déplacée vers la zone de données chaudes dans la liste chaînée lru, ou elle peut être déplacée vers l'en-tête dans la zone de données chaudes.

Redo log Buffer redo log buffer

InnoDB dispose d'un pool de tampons (bp pour faire court). bp est le cache des pages de la base de données. Toute opération de modification d'InnoDB sera d'abord effectuée sur la page de bp. Ensuite, ces pages seront marquées comme sales (pages sales) et placées par la suite sur le thread principal ou A. le fil de nettoyage dédié écrit périodiquement ces pages sur le disque (disque ou SSD).

L'avantage de ceci est d'éviter d'utiliser le disque pour chaque opération d'écriture, ce qui entraîne une grande quantité d'E/S aléatoires. Un brossage périodique peut fusionner plusieurs modifications de la page en une seule opération d'E/S. En même temps, l'écriture asynchrone réduit également le nombre d'E/S. retard d'accès. Cependant, si le serveur est arrêté anormalement avant que la page sale ne soit vidée sur le disque, ces opérations de modification seront perdues. Si l'opération d'écriture est en cours, la base de données peut même être indisponible en raison de fichiers de données endommagés.

Afin d'éviter les problèmes ci-dessus, Innodb écrit toutes les opérations de modification de la page dans un fichier spécial et effectue les opérations de récupération à partir de ce fichier au démarrage de la base de données. Ce fichier est le fichier de journalisation. Cette technologie retarde l'actualisation des pages bp, améliorant ainsi le débit de la base de données et réduisant efficacement la latence d'accès.

Le problème est la surcharge supplémentaire liée à l'écriture des opérations de redo log (E/S séquentielles, bien sûr rapides) et le temps nécessaire pour reprendre les opérations au démarrage de la base de données.

Le journal redo se compose de deux parties : le tampon de journalisation et le fichier de journalisation (introduit dans la section fichier disque). Innodb est un moteur de stockage qui prend en charge les transactions. Lorsqu'une transaction est validée, tous les journaux de la transaction doivent d'abord être écrits dans le fichier de journalisation. L'ensemble de l'opération de transaction n'est pas terminée tant que l'opération de validation de la transaction n'est pas terminée. Chaque fois que le tampon de journalisation est écrit dans le fichier de journalisation, une opération fsync doit être appelée, car le tampon de journalisation écrit d'abord le contenu dans le système tampon du système d'exploitation et ne garantit pas qu'il est écrit directement. sur le disque. Une opération fsync doit être effectuée. Par conséquent, les performances du disque déterminent également dans une certaine mesure les performances de soumission des transactions (le mécanisme de suppression de disque de redo log sera présenté plus tard).

Le moteur de stockage InnoDB placera d'abord les informations de journalisation dans le tampon de journalisation, puis les videra dans le fichier de journalisation à une certaine fréquence. Le tampon de journalisation n'a généralement pas besoin d'être défini très grand. Étant donné que le tampon de journalisation est généralement vidé dans le fichier journal toutes les secondes, il peut être contrôlé par le paramètre de configuration Innodb_log_buffer_size, dont la valeur par défaut est 8 Mo.

Double Write

Si Insert Buffer apporte une amélioration des performances au moteur de stockage InnoDB, alors Double wtite apporte la fiabilité de la page de données au moteur de stockage InnoDB.

La taille de la page d'InnoDB est généralement de 16 Ko, et sa vérification des données est également calculée sur la base de ces 16 Ko. L'écriture des données sur le disque est effectuée en unités de page. Nous savons que, comme le système de fichiers n'est pas atomique dans la plupart des cas pour les pages de données volumineuses (telles que les 16 Ko d'InnoDB), cela signifie que si le serveur est en panne, seule une partie de l'écriture peut être effectuée. Lorsque 16 Ko de données ont été écrites en 4K, une panne de courant du système s'est produite et une panne du système d'exploitation s'est produite. Seule une partie de l'écriture a réussi. Dans ce cas, il s'agissait d'un problème d'écriture de page partielle.

Les administrateurs de base de données expérimentés peuvent penser que si un échec d'écriture se produit, MySQL peut récupérer en fonction du journal de rétablissement. C'est une méthode, mais il faut bien comprendre que ce qui est enregistré dans le redo log est la modification physique de la page, comme le décalage 800, écrivez l'enregistrement 'aaaa'. Si la page elle-même est endommagée, cela ne sert à rien de la refaire. MySQL vérifie la somme de contrôle de la page pendant le processus de récupération. La somme de contrôle est le dernier numéro de transaction de la page. Lorsqu'un problème d'écriture de page partielle se produit, la page a été endommagée et le numéro de transaction dans la page est introuvable. Selon InnoDB, ces pages de données ne peuvent pas passer la vérification de la somme de contrôle et ne peuvent pas être récupérées. Même si nous le forçons à réussir la vérification, nous ne pouvons pas nous remettre du crash car certains des types de journaux actuels dans InnoDB sont des opérations logiques et ne peuvent pas être idempotents.

Afin de résoudre ce problème, InnoDB implémente un double tampon d'écriture. En termes simples, avant d'écrire la page de données, il écrit d'abord la page de données dans un emplacement de fichier physique indépendant (ibdata), puis écrit sur la page de données. De cette façon, lorsque la machine est arrêtée et redémarrée, si la page de données est endommagée, avant d'appliquer le journal de rétablissement, la page doit être restaurée via une copie de la page, puis le journal de rétablissement est refait. Il s'agit d'une double écriture. . Ce que la technologie de double écriture apporte au moteur de stockage innodb, c'est la fiabilité des pages de données. La technologie de double écriture est analysée ci-dessous

如上图所示，Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过 double write buffer 再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write 页的写入后，再将 double wirite buffer 中的页写入各个表空间文件中。

在这个过程中，doublewrite 是顺序写，开销并不大，在完成 doublewrite 写入后，在将 double write buffer写入各表空间文件，这时是离散写入。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的double write 中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

InnoDB 存储引擎第二部分：后台线程

IO 线程

在 InnoDB 中使用了大量的 AIO（Async IO） 来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有4个 IO Thread，分别是 write，read，insert buffer和log thread，后来版本将 read thread和 write thread 分别增大到了4个，一共有10个了。

• - read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

• - write thread：负责写操作，将缓存脏页刷新到磁盘。4个

• - log thread：负责将日志缓冲区内容刷新到磁盘。1个

• - insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

Purge 线程

事务提交之后，其使用的 undo 日志将不再需要，因此需要 Purge Thread 回收已经分配的 undo 页。show variables like '%innodb*purge*threads%';

Page Cleaner 线程

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能达到 redo log 循环使用的目的。会调用write thread线程处理。show variables like '%innodb*page*cleaners%';

InnoDB 存储引擎第三部分：磁盘文件

InnoDB 的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是 redo 日志文件和归档文件。

二进制文件（binlong）等文件是 MySQL Server 层维护的文件，所以未列入 InnoDB 的磁盘文件中。

系统表空间和用户表空间

系统表空间包含 InnoDB 数据字典（元数据以及相关对象）并且 double write buffer , change buffer , undo logs 的存储区域。

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

系统表空间是一个共享的表空间，因为它是被多个表共享的。

系统表空间是由一个或者多个数据文件组成。默认情况下,1个初始大小为10MB，名为 ibdata1 的系统数据文件在MySQL的data目录下被创建。用户可以使用 innodb_data_file_path 对数据文件的大小和数量进行配置。

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性:

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件组成系统表空间。如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。两个文件的文件名之后都跟了属性，表示文件 ibdata1 的大小为1000MB，文件 ibdata2 的大小为1000MB，而且用完空间之后可以自动增长。

设置 innodb_data_file_path 参数之后，所有基于 InnoDB 存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数 innodb_file_per_table ，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立的用户空间。

用户表空间的命名规则为：表名.ibd。通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

下图显示 InnoDB 存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义。

Fichiers de journalisation et fichiers d'archive

Par défaut, il existe deux fichiers nommés ib_logfile0 et ib_logfile1 dans le répertoire de données du moteur de stockage InnoDB. Il s'agit du fichier de journalisation d'InnoDB), qui enregistre le journal des transactions pour. le moteur de stockage InnoDB.

Lorsqu'une erreur se produit dans le fichier de stockage de données d'InnoDB, le fichier de journalisation peut s'avérer utile. Le moteur de stockage InnoDB peut utiliser des fichiers de journalisation pour restaurer les données à l'état correct afin de garantir l'exactitude et l'intégrité des données.

Chaque moteur de stockage InnoDB possède au moins 1 fichier de journalisation et chaque groupe de fichiers possède au moins 2 fichiers de journalisation, plus les fichiers de journalisation ib_logfile0 et ib_logfile1 par défaut.

Afin d'obtenir une plus grande fiabilité, les utilisateurs peuvent configurer plusieurs groupes de journaux miroir et placer différents groupes de fichiers sur différents disques pour améliorer la haute disponibilité des journaux redo.

Chaque fichier de journalisation du groupe de journaux a la même taille et fonctionne de manière [écriture en boucle]. Le moteur de stockage InnoDB écrit d'abord le fichier de journalisation 1. Lorsque le fichier est plein, il passe au fichier de journalisation 2. Lorsque le fichier de journalisation 2 est également plein, il passe au fichier de journalisation 1.

Les utilisateurs peuvent utiliser Innodb_log_file_size pour définir la taille du fichier de journalisation, ce qui a un impact important sur les performances du moteur de stockage InnoDB.

Si le fichier de journalisation est trop volumineux, la récupération peut prendre beaucoup de temps en cas de perte de données ; par contre, s'il est trop petit, le fichier de journalisation sera trop petit, ce qui entraînera un point de contrôle. Les vérifications basées sur les erreurs doivent être actualisées fréquemment. Les pages sales sont transférées sur le disque, ce qui entraîne des perturbations des performances.

Mécanisme de vidage des journaux de rétablissement

InnoDB suit deux règles : WAL (écriture anticipée du journal de rétablissement) et Force-log-at-commit pour le vidage des fichiers de données et des fichiers journaux, qui garantissent tous deux la durabilité des transactions sexuelles. WAL exige qu'avant que les modifications des données ne soient écrites sur le disque, le journal en mémoire doit d'abord être écrit sur le disque ; Force-log-at-commit exige que lorsqu'une transaction est validée, tous les journaux générés doivent être vidés sur le disque si le journal est effectué après. l'actualisation réussit, si la base de données plante avant que les données du pool de mémoire tampon ne soient actualisées sur le disque, la base de données peut récupérer les données du journal au redémarrage.

Comme le montre la figure ci-dessus, lorsqu'InnoDB modifie les données dans le pool de tampons, il écrira d'abord les modifications pertinentes dans le tampon de journalisation, puis les écrira sur le disque à temps (comme par seconde mécanisme d'actualisation) ou lorsque la transaction est validée, ce qui est conforme au principe Force-log-at-commit seulement après l'écriture du journal redo sur le disque, les données modifiées dans le pool de mémoire tampon seront écrites sur le disque selon ; le mécanisme de point de contrôle, qui respecte le principe WAL.

Dans le mécanisme de synchronisation du point de contrôle, on estime que le fichier de journalisation est plein. Par conséquent, comme mentionné ci-dessus, si le fichier de journalisation est trop petit et est souvent rempli, le point de contrôle écrira fréquemment les données modifiées. dans le disque, provoquant une instabilité des performances.

Le système de fichiers du système d'exploitation dispose d'un cache. Lorsque InnoDB écrit des données sur le disque, elles ne peuvent être écrites que dans le cache du système de fichiers, et il n'y a pas de véritable « sécurité ».

L'attribut innodb_flush_log_at_trx_commit d'InnoDB peut contrôler le comportement d'InnoDB à chaque fois qu'une transaction est validée. Lorsque la valeur de l'attribut est 0, lorsque la transaction est validée, le journal redo ne sera pas écrit, mais attendra que le thread principal écrive à temps ; lorsque la valeur de l'attribut est 1, lorsque la transaction est validée, le journal redo sera écrit. être écrit dans le fichier Le système met en cache et appelle fsync du système de fichiers pour écrire réellement les données dans le tampon du système de fichiers sur le stockage sur disque afin de garantir qu'aucune perte de données ne se produit lorsque la valeur de l'attribut est 2, le fichier journal sera également écrit ; au système de fichiers lorsque la transaction est validée. Cache, mais n'appelle pas fsync, mais laisse le système de fichiers déterminer quand écrire le cache sur le disque.

Le mécanisme de vidage du journal est illustré dans la figure ci-dessous :

Innodb_flush_log_at_commit est un paramètre de base pour le réglage des performances d'InnoDB, impliquant l'efficacité d'écriture et la sécurité des données d'InnoDB. Lorsque la valeur du paramètre est 0, l'efficacité d'écriture est la plus élevée, mais la sécurité des données est la plus faible ; lorsque la valeur du paramètre est 1, l'efficacité d'écriture est la plus faible, mais la sécurité des données est la plus élevée lorsque la valeur du paramètre est 2 ; , les deux sont à un niveau moyen, et il est généralement recommandé de définir l'attribut La valeur est définie sur 1 pour une sécurité plus élevée, et ce n'est que lorsqu'elle est définie sur 1 que la durabilité de la transaction peut être garantie.

Utilisez une instruction UPDATE pour en savoir plus sur le moteur de stockage InnoDB

Avec l'introduction ci-dessus de l'architecture de base du moteur de stockage InnoDB, analysons le processus spécifique d'une mise à jour des données UPDATE.

Nous divisons cette image en parties supérieure et inférieure. La partie supérieure est le flux de traitement de la couche MySQL Server, et la partie inférieure est le flux de traitement du moteur de stockage MySQL InnoDB.

Flux de traitement de la couche MySQL Server

Cette partie du flux de traitement n'a rien à voir avec le moteur de stockage. Elle est traitée par la couche serveur. Les étapes spécifiques sont les suivantes :

• Diverses opérations utilisateur déclenchent l'exécution de SQL en arrière-plan via le pool de connexion à la base de données. qui accompagne le projet Web : comme dbcp, c3p0, druid, etc., établissez une connexion réseau avec le pool de connexions à la base de données du serveur de base de données

• Une fois que le thread du pool de connexions à la base de données a écouté la requête, il répond à l'analyseur de requête via l'interface SQL avec l'instruction SQL reçue, et la requête analyse Le serveur analyse le SQL selon la syntaxe de SQL pour déterminer quels champs de quelle table interroger et quelles sont les conditions de requête

•  ; est ensuite traité par l'optimiseur de requêtes pour sélectionner l'ensemble optimal de plans d'exécution pour le sq

• puis l'exécuteur Responsable d'appeler une série d'interfaces du moteur de stockage, d'exécuter le plan et de terminer l'exécution de l'intégralité du SQL ; Cette partie du processus est fondamentalement cohérente avec l’analyse d’un processus de traitement de demande Select analysé ci-dessus.

L'instruction d'exécution spécifique doit être complétée par le moteur de stockage, comme indiqué dans la figure ci-dessus :

Mettre à jour les données avec id=10 dans la table des utilisateurs . Si mis en mémoire tampon S'il n'y a pas de données de ce type dans le pool, les données originales des données mises à jour doivent d'abord être chargées du disque dans le pool de mémoire tampon.

• Dans le même temps, afin d'assurer la sécurité des données mises à jour simultanément, ces données seront d'abord verrouillées pour empêcher la mise à jour d'autres transactions.

• Ensuite, sauvegardez la valeur avant la mise à jour et écrivez-la dans le journal d'annulation (pour faciliter la récupération des anciennes données lorsque la transaction est annulée. Par exemple, l'instruction de mise à jour stocke la valeur avant le champ mis à jour).

• Mettez à jour les données du cache dans le pool de mémoire tampon avec les dernières données, puis les données dans la mémoire à ce moment sont des données sales (les données dans la mémoire et les données dans le disque sont incohérentes)

Après avoir mis à jour les données dans le pool de mémoire tampon, vous devez écrire ces informations de mise à jour dans le Redo Log dans l'ordre, car les données dans la mémoire ont été modifiées, mais les données sur le disque n'ont pas été modifiées si la machine était là. MySQL est en panne, les données modifiées dans la mémoire seront inévitablement perdues. Le journal redo enregistre les modifications que vous avez apportées aux données, telles que "la valeur du champ nom est modifiée en xxx dans l'enregistrement de "id=". 10"" , il s'agit d'un journal utilisé pour restaurer vos données mises à jour lorsque MySQL plante soudainement. Cependant, veuillez noter que Redo Log n'a pas encore été placé dans le fichier journal pour le moment.

Je réfléchis à une question en ce moment : que se passe-t-il si MySQL tombe en panne avant que la transaction ne soit soumise ?

Nous savons ci-dessus que nous avons modifié les données de la mémoire jusqu'à présent, puis enregistré le tampon du journal Redo Log Buffer. Si MySQL plante à ce moment-là, les données de la mémoire et les données du Redo Log Buffer seront perdues, mais cela n'a pas d'importance. les données sont perdues à ce moment-là, car une mise à jour Si l'instruction n'a pas soumis la transaction, cela signifie qu'elle n'a pas été exécutée avec succès à ce moment-là, même si MySQL est en panne et que les données en mémoire sont perdues, vous trouverez. que les données sur le disque restent telles quelles.

L'étape suivante consiste à valider la transaction. À ce stade, le journal de rétablissement sera vidé du tampon de journalisation vers le fichier disque selon une certaine stratégie. À ce stade, cette stratégie est configurée via innodb_flush_log_at_trx_commit.

innodb_flush_log_at_trx_commit=0, ce qui signifie que la soumission d'une transaction ne videra pas les données du tampon de journalisation dans le fichier disque. À ce stade, vous avez peut-être soumis la transaction et, par conséquent, MySQL est en panne, puis tout. les données en mémoire sont perdues, cette approche n'est donc pas recommandée.

innodb_flush_log_at_trx_commit=1, le journal redo est vidé de la mémoire vers le fichier disque. Tant que la transaction est soumise avec succès, le journal redo doit être sur le disque, donc si MySQL plante à ce moment, les données peuvent être restaurées. basé sur le journal Redo Log.

innodb_flush_log_at_trx_commit=2, lors de la validation d'une transaction, écrivez le journal redo dans le cache du système d'exploitation correspondant au fichier disque au lieu d'entrer directement dans le fichier disque. Cela peut prendre 1 seconde avant que les données du cache du système d'exploitation ne soient écrites dans le fichier disque. . Entrez.

Lors de la soumission d'une transaction, binlog sera écrit en même temps. Binlog a également différentes stratégies de vidage de disque. Il existe un paramètre sync_binlog qui peut contrôler la stratégie de vidage de binlog. Sa valeur par défaut est 0. À ce moment-là, lorsque vous écrivez. binlog to disk , en fait, il n'entre pas directement dans le fichier disque, mais entre dans le cache mémoire du système d'exploitation. Généralement, afin de garantir que les données ne sont pas perdues, nous configurons une stratégie double 1 et sélectionnons 1 pour les stratégies de placement de disque Redo Log et Binlog.

Une fois le Binlog placé, le nom du fichier Binlog, les informations sur le chemin du fichier et la marque de validation sont écrits dans le journal Redo dans la séquence de synchronisation. L'importance de cette étape est de maintenir la cohérence du journal Redo avec le journal Binlog. La marque de validation est un critère important pour déterminer si une transaction est soumise avec succès. Par exemple, si MySQL plante après l'exécution réussie de l'étape 5 ou de l'étape 6, cette fois parce qu'il n'y a pas de marque de validation de transaction finale dans le journal redo, donc cette transaction. peut être considéré comme un échec. On ne dira pas qu'il y a un journal de cette mise à jour dans le fichier redo log, mais il n'y a pas de journal de cette mise à jour dans le fichier journal binlog, il n'y aura donc aucun problème d'incohérence des données.

Après avoir terminé ce qui précède, les données de la mémoire ont été modifiées, la transaction a été soumise et le journal a été placé sur le disque, mais les données du disque n'ont pas été modifiées simultanément. Il y a un thread IO en arrière-plan du moteur de stockage InnoDB Pendant la faible période de pointe de pression sur la base de données, les données du pool de tampons qui ont été mises à jour par les transactions mais n'ont pas encore eu le temps d'être écrites sur le disque (données sales). , car les données du disque et les données de la mémoire ne sont plus disponibles) cohérentes) sont vidées sur le disque pour terminer la persistance de la transaction.

Le processus d'écriture d'InnoDB peut donc être représenté par l'image ci-dessous

Apprentissage recommandé : Tutoriel vidéo MySQL

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!