Cet article vous apporte une introduction détaillée (exemple de code) sur le moteur de stockage InnoDB dans MySQL. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.
InnoDB appartient à la couche moteur de stockage dans MySQL et est intégré à la base de données sous forme de plug-in. À partir de MySQL 5.5.8, InnoDB devient son moteur de stockage par défaut. Le moteur de stockage InnoDB prend en charge les transactions et son objectif de conception est principalement destiné aux applications OLTP. Ses principales fonctionnalités incluent : la prise en charge des transactions, la conception du verrouillage de ligne pour prendre en charge une concurrence élevée, la prise en charge des clés étrangères, la récupération automatique en cas de crash, la structure de la table d'organisation des index en cluster, etc. (Recommandations associées : Tutoriel MySQL)
Architecture du système
Le moteur de stockage InnoDB est composé de trois parties : le pool de mémoire, le thread d'arrière-plan et stockage sur disque.
Threads
InnoDB utilise un modèle multi-thread, avec plusieurs threads différents en arrière-plan responsables du traitement des différentes tâches
Master Thread est le thread d'arrière-plan principal, qui est principalement responsable de l'actualisation de manière asynchrone des données du pool de mémoire tampon sur le disque pour garantir la cohérence des données. Y compris l'actualisation des pages sales, le tampon d'insertion fusionné, le recyclage des pages UNDO, etc.
Dans le moteur de stockage InnoDB, les IO asynchrones (Async IO) sont largement utilisées pour gérer les requêtes IO d'écriture. Le travail de IO Thread est principalement responsable du rappel de ces requêtes IO. .
Une fois la transaction validée, le journal d'annulation utilisé par celle-ci peut ne plus être nécessaire, donc Purge Thread est nécessaire pour recycler les pages UNDO qui ont été allouées et utilisées. InnoDB prend en charge plusieurs threads de purge, ce qui peut accélérer le recyclage des pages UNDO, augmenter l'utilisation du processeur et améliorer les performances du moteur de stockage.
Page Cleaner Thread est utilisé pour remplacer l'opération d'actualisation de page sale dans Master Thread. Son objectif est de réduire le travail du Master Thread d'origine et le blocage des threads de requête des utilisateurs. , et améliorer encore les performances du moteur de stockage InnoDB.
Mémoire
Structure de la mémoire du moteur de stockage InnoDB
Stockage InnoDB Le Le moteur est basé sur le stockage sur disque et gère les enregistrements en 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.
Le pool de tampons utilise en fait la vitesse de la mémoire pour compenser l'impact de la lenteur du disque sur les performances de la base de données. Lorsque la base de données effectue une opération de lecture, la page du disque est d'abord placée dans le pool de mémoire tampon. Lors de la prochaine lecture de la même page, les données de la page sont d'abord obtenues du pool de mémoire tampon pour servir de cache.
L'opération de modification des données modifie d'abord les données de la page dans le pool de mémoire tampon, puis les vide sur le disque à l'aide d'un mécanisme appelé Checkpoint.
La taille du pool de tampons affecte directement les performances globales de la base de données. Pour le moteur de stockage InnoDB, la configuration du pool de tampons est définie via le paramètre innodb_buffer_pool_size. Utilisez la commande SHOW VARIABLES LIKE 'innodb_buffer_pool_size' pour afficher la configuration du pool de tampons :
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size' \G *************************** 1. row *************************** Variable_name: innodb_buffer_pool_size Value: 134217728 1 row in set (0.01 sec)
Les types de pages de données mises en cache dans le pool de tampons sont : les pages d'index, les pages d'annulation, les tampons d'insertion, les index de hachage adaptatifs, les informations de verrouillage InnoDB , les informations du dictionnaire de données, etc., les pages d'index et les pages de données représentent une grande partie du pool de mémoire tampon.
Lorsque les données de page dans le pool de mémoire tampon sont plus récentes que le disque, les nouvelles données doivent être vidées sur le disque. InnoDB utilise la stratégie Write Ahead Log pour actualiser les données. Autrement dit, lorsqu'une transaction est soumise, le tampon de journalisation est d'abord écrit dans le fichier journal de réinitialisation à une certaine fréquence, puis les pages sales. sera vidé sur le disque selon le mécanisme de point de contrôle.
Le tampon de journalisation n'a pas besoin d'être très grand. Normalement, 8 M peuvent répondre à la plupart des scénarios d'application. Le journal de rétablissement prend en charge les trois situations suivantes pour déclencher l'actualisation :
Master Thread vide le tampon de journalisation dans le fichier de journalisation toutes les secondes
Le tampon de journalisation est vidé dans le fichier de journalisation à chaque fois qu'une transaction est validée
Lorsque l'espace restant dans le pool de tampons de journalisation est inférieur à la moitié, le tampon de journalisation est vidé dans le fichier de journalisation. Créer des fichiers journaux
Dans le moteur de stockage InnoDB, la gestion de la mémoire est via un processus appelé mémoire exécuté de manière tas. Lors de l'allocation de mémoire de certaines structures de données elles-mêmes, il est nécessaire d'appliquer à partir d'un pool de mémoire supplémentaire. Lorsque la mémoire dans cette zone n'est pas suffisante, elle sera appliquée à partir du pool de mémoire tampon.
Verrouillage
Les verrous pris en charge par InnoDB sont :
Verrouillage partagé et verrouillage exclusif
Verrouillage d'intention
Verrouillage d'enregistrement
Verrouillage des espaces
Verrouillage à incrémentation automatique
InnoDB Le moteur implémente deux verrous standard au niveau de la ligne, des verrous partagés (S) et des verrous exclusifs (X). Un verrou partagé permet à une transaction qui détient le verrou de lire une ligne de données, et un verrou exclusif permet à une transaction d'écrire dans une ligne d'enregistrements.
Si une transaction détient un verrou partagé, les autres transactions peuvent toujours obtenir le verrou partagé de cet enregistrement de ligne, mais ne peuvent pas obtenir le verrou exclusif de cet enregistrement de ligne. Lorsqu'une transaction acquiert un verrou exclusif sur une ligne, les autres transactions ne pourront plus acquérir de verrous partagés et de verrous exclusifs sur cette ligne.
Dans InnoDB, le verrouillage d'intention est un verrou au niveau de la table, qui est divisé en verrou partagé et verrou exclusif :
Intention partagée lock : Sur le point d'acquérir le verrou partagé d'une certaine ligne
Intention de verrouillage exclusif : Sur le point d'acquérir le verrou exclusif d'une certaine ligne
La transaction est en cours d'acquisition Avant de partager/verrous exclusifs, vous devez d'abord acquérir des verrous de partage/exclusivité d'intention. Les verrous d'intention ne bloqueront aucune autre opération sur la table. Ils indiquent simplement aux autres transactions qu'elles vont acquérir un verrou partagé ou exclusif. une certaine ligne.
L'enregistrement est une sorte de verrou qui agit sur l'index. Il verrouille l'index d'un certain enregistrement plutôt que l'enregistrement lui-même. Si la table actuelle n'a pas d'index, InnoDB créera un index cluster caché pour celui-ci et Record Locks verrouillera l'index cluster caché.
Le verrouillage des espaces et le verrouillage des enregistrements agissent également sur l'index. La différence est que le verrouillage des enregistrements n'agit que sur un seul enregistrement d'index et le verrouillage des espaces peut verrouiller une plage d'index. La seule fonction des verrous d'espacement dans InnoDB est d'empêcher d'autres transactions d'insérer des opérations, empêchant ainsi les lectures fantômes de se produire.
Le verrouillage à incrémentation automatique est un verrou spécial au niveau de la table, qui s'applique uniquement aux opérations d'insertion impliquant des colonnes à incrémentation automatique. Lorsqu'une transaction insère un élément de données, toute autre transaction doit attendre que la transaction entière termine l'opération d'insertion, puis acquérir le verrou pour effectuer l'opération d'insertion.
Transaction
ACID
La transaction est la fonctionnalité la plus importante de la base de données en tant qu'OLTP Lorsque nous parlons de transactions, nous devons mentionner les quatre bases. caractéristiques d'ACID :
Atomicité : la plus petite unité de travail d'une transaction, soit toutes réussies, soit toutes échouées
Cohérence : le début et la fin d'une transaction Par la suite, l'intégrité de la base de données ne sera pas détruite
Isolement : Les différentes transactions ne s'affectent pas les unes les autres. Les quatre niveaux d'isolement sont RU (lecture non validée), RC (Lecture. commit), RR (lecture répétable), SERIALIZABLE (sérialisation)
Durability (Durability) : Une fois la transaction validée, la modification des données est permanente, même si une panne du système ne le fera pas être perdu
L'atomicité, la persistance et la cohérence d'InnoDB sont principalement réalisées grâce aux mécanismes Redo Log, Undo Log et Force Log at Commit. Redo Log est utilisé pour récupérer des données en cas de crash, Undo Log est utilisé pour annuler l'impact des transactions et peut également être utilisé pour le contrôle multi-versions. Le mécanisme Force Log at Commit garantit que le Redo Log a été conservé une fois la transaction validée. L'isolement est garanti par des serrures et MVCC.
Dans MySQL, il existe 4 niveaux d'isolement pour les transactions, qui sont :
Lecture Lecture non validée
Lecture validée Lecture validée
Lecture répétable Lecture répétable
Sérialisable
Avant de comprendre les quatre niveaux d'isolement, nous devons comprendre trois autres termes :
Lecture sale
La transaction a lira les données non validées de la transaction b, mais la transaction b effectuera une opération de restauration pour une raison quelconque, de cette manière, les données lues par la transaction a seront indisponibles, ce qui entraînera des résultats anormaux.
Lecture non répétable
Certaines données sont interrogées plusieurs fois au cours d'un cycle de transaction, et les données sont mises à jour ou mises à jour dans le b opération de suppression de transaction. Les résultats de chaque requête pour la transaction a peuvent alors être différents.
Lecture fantôme
Le résultat de la lecture fantôme est en fait le même que celui de la lecture non répétable. La différence est que la lecture non répétable est. principalement pour d'autres La transaction a effectué des opérations d'édition (mise à jour) et de suppression (suppression). La lecture fantôme est principalement destinée aux opérations d'insertion. C'est-à-dire que pendant le cycle de vie d'une transaction, les données nouvellement insérées provenant d'une autre transaction seront interrogées.
Lecture non validée Dans ce cas, une transaction a peut voir les données non validées d'une autre transaction b Si la transaction b est annulée à ce moment, alors quelle transaction a obtient. Ce sont des données sales, ce qui est le sens d'une lecture sale.
Ce niveau d'isolement n'est généralement pas recommandé dans MySQL InnoDB.
Lecture validée, toutes les modifications apportées par une transaction depuis le début jusqu'à sa validation ne sont pas visibles par les autres transactions. Le problème de la lecture sale est résolu, mais le phénomène de lecture fantôme existe.
Lecture répétable, ce niveau garantit que les résultats de la lecture du même enregistrement plusieurs fois dans la même transaction sont cohérents et résout à la fois les lectures fantômes et non répétables dans le moteur de stockage InnoDB Lisez la question.
Le moteur InnoDB résout le problème des lectures fantômes en utilisant Next-Key Lock. Next-Key Lock est une combinaison de verrouillage de ligne et de verrouillage d'espacement. Lorsqu'InnoDB analyse l'enregistrement d'index, il ajoute d'abord un verrou de ligne (Record Lock) à l'enregistrement d'index, puis ajoute un verrou d'espacement (Gap Lock) aux espaces présents. les deux côtés de l'enregistrement d'index. Après avoir ajouté le verrou d'espacement, les autres transactions ne peuvent pas modifier ou insérer d'enregistrements dans cet espace.
Sérialisable est le niveau d'isolement le plus élevé. Il évite le problème des lectures fantômes en forçant les transactions à être exécutées en série. Cependant, Serialisable sera exécuté sur chaque ligne de données lues. sont verrouillés, cela peut donc entraîner de nombreux problèmes de délai d'attente et de conflit de verrouillage, de sorte que la concurrence diminue fortement et il n'est pas recommandé de l'utiliser dans MySQL InnoDB.
BEGIN, BEGIN WORK, START TRANSACTION
L'exécution de la commande BEGIN n'ouvrira pas réellement une nouvelle transaction au niveau de la couche moteur, une transaction définit simplement une marque pour le thread actuel et représente une transaction explicitement ouverte.
START TRANSACTION LECTURE SEULE
Activez les transactions en lecture seule Lorsque le serveur MySQL reçoit un code SQL modifiant les données, il rejette directement le message. modification et renvoie une erreur. Je n'entrerai pas dans la couche moteur pour cette erreur.
START TRANSACTION READ WRITE
Permet au super utilisateur de démarrer une transaction en lecture-écriture lorsque l'état en lecture seule du fil de discussion actuel est vrai.
START TRANSACTION AVEC UN INSTANTANÉ COHÉRANT
L'ouverture d'une transaction entrera dans la couche moteur et ouvrira un readview. Cette opération n'est valide que sous le niveau d'isolement RR, sinon une erreur sera signalée.
enregistrera le journal d'annulation correspondant lorsque les données sont modifiées. Si la transaction échoue ou est annulée, vous pouvez utiliser le journal d'annulation enregistré pour revenir en arrière. Le journal d'annulation est un journal logique qui enregistre l'image des données avant les modifications. Si les données actuelles doivent être lues en même temps lors de la modification, il peut analyser les données de la version précédente enregistrées dans cette ligne en fonction des informations de version. De plus, le journal d'annulation générera également des journaux redo, car le journal d'annulation nécessite également une protection de persistance.
Utilisez le générateur d'ID de transaction global pour générer le numéro de transaction et ajoutez le pointeur de transaction actuellement connecté (trx_t) à la liste globale des transactions de validation ( trx_serial_list) dans
marquez annuler. Si cette transaction n'utilise qu'une seule page d'annulation et que l'utilisation est inférieure à 3/4 de page, marquez cette page comme TRX_UNDO_CACHED. satisfait et si c'est insert undo, il sera marqué comme TRX_UNDO_TO_FREE, sinon si l'annulation est une annulation de mise à jour, elle sera marquée comme TRX_UNDO_TO_PURGE. Les annulations marquées TRX_UNDO_CACHED seront recyclées par le moteur.
Mettez update undo dans le undo segment de history list et incrémentez rseg_history_len (global). En même temps, mettez à jour TRX_UNDO_TRX_NO sur la page, si les données sont supprimées, réinitialisez delete_mark
et supprimez undate undo de update_undo_list, si elle est marquée comme TRX_UNDO_CACHED , puis rejoignez la update_undo_cachedfile d'attente
mtr_commit (le journal d'annulation/redo est écrit dans le tampon public à ce stade, la transaction au niveau du fichier est validée). Même si elle plante à ce moment-là, la transaction peut toujours être garantie d'être soumise après le redémarrage. La prochaine chose à faire est de mettre à jour l'état des données de la mémoire (trx_commit_in_memory)
La transaction en lecture seule n'a besoin que de supprimer readview de la liste chaînée readview globale et puis réinitialisez-le trx_tLes informations contenues dans la structure sont suffisantes. Une transaction en lecture-écriture doit d'abord définir le statut de la transaction sur TRX_STATE_COMMITTED_IN_MEMORY, libérer tous les verrous de ligne et supprimer trx_t de rw_trx_list et readview de la liste chaînée readview globale. S'il y a insert undo, supprimez-le ici. S'il y a update undo, réveillez le fil de purge pour nettoyer les ordures. Enfin, réinitialisez les informations dans trx_t pour faciliter l'utilisation de la prochaine transaction
S'il s'agit d'une transaction en lecture seule, retournez directement
pour déterminer s'il faut lancer restaurer la totalité ou une partie de la transaction, s'il s'agit d'une transaction partielle, enregistrer le nombre de journaux d'annulation qui doivent être conservés et annuler tout l'excédent
Trouver la dernière annulation de update undo et insert undo, et commencez à partir de cette annulation Rollback
Si c'est update undo, les enregistrements marqués comme supprimés seront effacés et les données mises à jour seront annulées à la version la plus ancienne. Si c'est le cas insert undo, supprimez directement l'index clusterisé et l'index secondaire
Si toutes les annulations ont été annulées ou rétablies à l'annulation spécifiée, arrêtez et supprimez le journal d'annulation
Index
Le moteur InnoDB utilise l'arborescence B+ comme structure d'index. Le champ de données du nœud feuille de l'index de clé primaire enregistre les données complètes du champ, et le nœud feuille de l'index de clé non primaire enregistre les données de valeur pointant. à la clé primaire.
L'image ci-dessus est un diagramme schématique de l'index principal d'InnoDB (également un fichier de données). Vous pouvez voir que les nœuds feuilles contiennent des enregistrements de données complets. un index clusterisé. Étant donné que les fichiers de données d'InnoDB eux-mêmes sont agrégés par clé primaire, InnoDB exige que la table ait une clé primaire si elle n'est pas explicitement spécifiée, le système MySQL sélectionnera automatiquement une colonne qui peut identifier de manière unique l'enregistrement de données comme clé primaire. n'existe pas, alors MySQL génère automatiquement un champ implicite comme clé primaire pour la table InnoDB. La longueur de ce champ est de 6 octets et le type est long.
Le champ de données d'index auxiliaire d'InnoDB stocke la valeur de la clé primaire de l'enregistrement correspondant au lieu de l'adresse. En d'autres termes, tous les index secondaires d'InnoDB font référence à la clé primaire comme champ de données. La mise en œuvre de l'index clusterisé rend la recherche par clé primaire très efficace, mais la recherche par index auxiliaire nécessite de récupérer l'index deux fois : d'abord, récupérer l'index auxiliaire pour obtenir la clé primaire, puis utiliser la clé primaire pour récupérer les enregistrements dans la clé primaire. indice.
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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
