Explication très détaillée du moteur de stockage MySQL-InnoDB

Si vous souhaitez voir quel moteur de stockage votre base de données utilise par défaut, vous pouvez utiliser la commande :

SHOW VARIABLES LIKE 'storage_engine';

1. Moteur de stockage InnoDB

1. InnoDB est le moteur préféré pour les bases de données transactionnelles

Prend en charge les tables de sécurité des transactions (ACID)

Propriétés ACID des transactions : atomicité, cohérence, isolation, durabilité

a.

Atomicité : L'atomicité signifie que cet ensemble d'instructions sera soit exécuté entièrement, soit pas exécuté du tout. Si une erreur se produit à mi-chemin de l'exécution de la transaction, la base de données sera restaurée au point où. la transaction a commencé.

Implémentation : Principalement basé sur le mécanisme de rétablissement et d'annulation du système de journalisation MySQ. Une transaction est un ensemble d'instructions SQL dotées de fonctions telles que la sélection, la requête et la suppression. Il y aura un nœud pour chaque exécution d'instruction. Par exemple, après l'exécution de l'instruction delete, un enregistrement est enregistré dans la transaction. Cet enregistrement stocke le moment où nous avons fait ce que nous avons fait. Si quelque chose ne va pas, il sera ramené à la position d'origine. Ce que j'ai fait a été stocké dans la restauration, puis il pourra être exécuté à l'envers.

b.

Cohérence : Les contraintes d'intégrité de la base de données ne sont pas violées avant et après le début et la fin de la transaction. (ex : Par exemple, si A transfère de l'argent à B, il est impossible pour A de déduire l'argent mais B ne le reçoit pas)

c.

Isolement : En même temps, seulement une transaction est autorisée à demander les mêmes données. Différentes transactions n'interfèrent pas les unes avec les autres ;

Si l'isolement n'est pas pris en compte, plusieurs problèmes surviendront :

i,

Lecture sale : fait référence aux données d'une autre transaction non validée qui sont lues pendant le traitement (lorsqu'une transaction modifie plusieurs fois certaines données et que les multiples modifications de cette transaction n'ont pas encore été validées, alors une transaction simultanée vient accéder à ces données. rendre les données obtenues par les deux transactions incohérentes) ; (lire les données sales non validées d'une autre transaction)

ii

Lecture non répétable : dans la base de données Pour certaines données, requêtes multiples. dans une plage de transactions, a renvoyé des valeurs de données différentes. En effet, elles ont été modifiées et soumises par une autre transaction pendant l'intervalle de requête (les données soumises par la transaction précédente ont été lues et toutes les données interrogées étaient le même élément de données)

iii,

Lecture virtuelle (lecture fantôme)  : C'est un phénomène qui se produit lorsque les transactions ne sont pas exécutées indépendamment (ex : la transaction T1 lit toutes les lignes d'une table. Une donnée a été modifiée de "1 " à "2". À ce moment-là, la transaction T2 a inséré une ligne d'éléments de données dans la table, et la valeur de cet élément de données était toujours "1" et soumise à la base de données. Si l'utilisateur exécutant la transaction T1 regarde les données vient de modifier, il constatera qu'il y a encore une ligne qui n'a pas été modifiée. En fait, cette ligne a été ajoutée à partir de la transaction T2, comme s'il hallucinait (les données soumises par la transaction précédente sont lues. , pour un) ; lot de données dans son ensemble)

d.

Persistance : Une fois la transaction terminée, toutes les mises à jour de la base de données par la transaction seront enregistrées dans la base de données et ne pourront pas être annulées

2.InnoDB est le moteur de stockage par défaut de MySQL

Le niveau d'isolement par défaut est RR, et sous le niveau d'isolement de RR, il passe un Un peu plus loin, grâce au contrôle de concurrence multiversion (MVCC), il résout le problème de lecture non répétable et ajoute un verrouillage d'espacement (c'est-à-dire un contrôle de concurrence) pour résoudre le problème de lecture fantôme. Par conséquent, le niveau d'isolation RR d'InnoDB atteint réellement l'effet du niveau de sérialisation tout en conservant de meilleures performances de concurrence.

La base de données MySQL nous offre quatre niveaux d'isolement :

a,

(sérialisation) : peut éviter l'apparition de lectures sales, de lectures non répétables et de lectures fantômes ; Serializableb,

(lecture répétable) : peut éviter l'apparition de lectures sales et de lectures non répétables

Repeatable readc,

(lecture validée) : peut éviter l'apparition de lectures sales ;

Read committedd,

(lire non engagé) : le niveau le plus bas, aucune garantie dans aucune situation

Read uncommitted de a----d niveau d'isolement de haut à bas, plus le niveau est élevé ; , Plus l'efficacité d'exécution est faible

3. InnoDB prend en charge les verrous au niveau des lignes

. Les verrous au niveau des lignes peuvent prendre en charge la concurrence dans la plus grande mesure. Les verrous au niveau des lignes sont implémentés par la couche du moteur de stockage.

Verrouillage : La fonction principale du verrou est de gérer l'accès simultané aux ressources partagées et d'isoler les transactions.

Type : verrou partagé (verrouillage en lecture), verrouillage exclusif (verrouillage en écriture) >

Force du verrouillage MySQL : verrous au niveau de la table (faible surcharge, faible concurrence), généralement implémentés au niveau de la couche serveur

Verrous au niveau de la ligne (forte surcharge, forte concurrence), uniquement au niveau du moteur de stockage Implémentation

4. InnoDB est conçu pour des performances maximales pour traiter d'énormes quantités de données

.

Son efficacité CPU peut être inégalée par n'importe quel moteur de base de données relationnelle sur disque

5 Le moteur de stockage InnoDB est entièrement intégré au serveur MySQL

Le. Le moteur de stockage InnoDB gère son propre pool de tampons pour la mise en cache des données et des index dans la mémoire principale. InnoDB place ses tables et index dans un espace table logique, et l'espace table peut contenir plusieurs fichiers (ou fichiers de disque brut

6

,

InnoDB prend en charge les clés étrangères complètes sexuelles) ; retenue

Lors du stockage des données dans des tables, chaque table est stockée dans l'ordre de la clé primaire si la clé primaire n'est pas spécifiée lors de la définition de la table. InnoDB générera un ROWID de 6 octets pour chaque ligne et l'utilisera comme clé primaire

7 InnoDB est utilisé dans de nombreux grands sites de bases de données qui nécessitent des performances élevées

. 8. InnoDB n'enregistre pas le nombre de lignes dans le tableau (par exemple : lors de la sélection de count(*) dans le tableau, InnoDB doit analyser le tableau entier pour calculer le nombre de lignes lors de l'effacement) ; table entière, InnoDB est une ligne La suppression d'une ligne est très lente ;

InnoDB ne crée pas de répertoire Lors de l'utilisation d'InnoDB, MySQL créera un fichier de données automatiquement étendu de 10 Mo nommé ibdata1 et deux nommés ib_logfile0 dans MySQL. répertoire de données. et un fichier journal de 5 Mo de ib_logfile1

2. L'implémentation sous-jacente du moteur InnoDB

InnoDB a deux fichiers de stockage et les noms de suffixe sont .frm. et .idb ; Parmi eux, .frm est le fichier de définition de la table et .idb est le fichier de données de la table.

1. Le moteur InnoDB utilise la structure B+Tree comme structure d'index

B-Tree (arbre de recherche multi-chemins équilibré) : un arbre de recherche équilibré conçu pour les périphériques de stockage externes tels que disques

Lorsque le système lit les données du disque vers la mémoire, elles sont basées sur les bits du bloc de disque. Les données situées dans le même bloc de disque seront lues en même temps, plutôt que sur demande.

Le moteur de stockage InnoDB utilise les pages comme unités de lecture de données. Les pages sont la plus petite unité de gestion de disque. La taille de page par défaut est de 16 Ko.

L'espace de stockage d'un bloc de disque dans le système est de 16 Ko. souvent pas si grand, donc chaque fois qu'InnoDB demande de l'espace disque, il utilisera plusieurs blocs de disque consécutifs avec des adresses pour atteindre la taille de page de 16 Ko.

InnoDB utilisera les pages comme unité de base lors de la lecture des données du disque sur le disque. Lors de l'interrogation des données, si chaque élément de données d'une page peut aider à localiser l'emplacement de l'enregistrement de données, cela réduira le nombre de données. les E/S disque améliorent l’efficacité des requêtes.

Les données de la structure B-Tree permettent au système de trouver efficacement le bloc de disque où se trouvent les données

Chaque nœud du B-Tree peut contenir une grande quantité d'informations sur des mots clés et branches en fonction de la situation réelle. Exemple

Chaque nœud occupe un bloc d'espace disque. Il y a deux clés triées par ordre croissant sur un nœud et trois pointeurs vers le nœud racine du. sous-arbre. Les pointeurs Ce qui est stocké est l'adresse du bloc disque où se trouve le nœud enfant.

Prenons l'exemple du nœud racine. Les mots-clés sont 17 et 35. La plage de données du sous-arbre pointé par le pointeur P1 est inférieure à 17. La plage de données du sous-arbre pointé par le pointeur P2 est 17. ----35. La plage de données du pointeur P3 est 17----35. La plage de données du sous-arbre pointé est supérieure à 35 ;

simule le processus de recherche du mot-clé 29 :

.

a. Recherchez le bloc de disque 1 en fonction du nœud racine et lisez-le en mémoire. [Opération d'E/S du disque pour la première fois]

b. Comparez le mot-clé 29 dans l'intervalle (17,35) et trouvez le pointeur P2 du bloc de disque 1

c. sur le bloc de disque pointeur P2 3, lire en mémoire. [Deuxième opération d'E/S du disque]

d. Comparez le mot-clé 29 dans l'intervalle (26, 30) et trouvez le pointeur P2 du bloc de disque 3

e. pointeur Bloc de disque 8, lu en mémoire. [Opération d'E/S disque pour la troisième fois]

f. Recherchez le mot-clé 29 dans la liste de mots-clés du bloc de disque 8.

Le moteur de stockage InnoDB de MySQL est conçu pour être root. Le nœud réside en mémoire. , donc la profondeur de l'arborescence ne doit pas dépasser 3, c'est-à-dire que les E/S n'ont pas besoin de dépasser trois fois

En analysant les résultats ci-dessus, il s'avère que trois opérations d'E/S disque et trois opérations de mémoire ; des recherches sont nécessaires pour fonctionner. Étant donné que les mots-clés dans la mémoire sont une structure de liste ordonnée, la recherche binaire peut être utilisée pour améliorer l'efficacité ; trois opérations d'E/S sur disque sont le facteur décisif affectant l'efficacité de la recherche B-Tree dans son ensemble.

B+Tree

B+Tree est une optimisation basée sur B-Tree, ce qui le rend plus adapté à la mise en œuvre de structures d'index de stockage externes dans B-Tree Chaque nœud. contient des clés et des données, et l'espace de stockage de chaque page est limité. Si les données sont volumineuses, le nombre de clés pouvant être stockées dans chaque nœud (c'est-à-dire une page) sera très petit. Lorsque la quantité de données stockées est importante, la profondeur du B-Tree sera également plus grande, ce qui augmentera le nombre d'E/S disque pendant la requête, affectant ainsi l'efficacité des requêtes.

Dans B+Tree, tous les nœuds d'enregistrement de données sont stockés sur les nœuds feuilles de la même couche par ordre de valeur clé. Seules les informations sur la valeur clé sont stockées sur les nœuds non feuilles, ce qui peut augmenter considérablement la taille de chacun. node. Le nombre de valeurs de clé stockées réduit la hauteur de B+Tree ;

Habituellement, il y a deux pointeurs de tête sur B+Tree, l'un pointe vers le nœud racine. et l'autre pointe vers les nœuds feuilles clés minimaux, et il existe une structure en anneau de chaîne entre tous les nœuds feuilles (c'est-à-dire les nœuds de données).

Par conséquent, deux opérations de recherche peuvent être effectuées sur B+Tree, l'une est la recherche par plage et la recherche par pagination pour la clé primaire, et l'autre est une recherche aléatoire à partir du nœud racine.

B+Tree dans InnoDB

InnoDB est un stockage de données indexé par ID

Il existe deux fichiers de stockage de données utilisant le moteur InnoDB, l'un est un fichier de définition et l'autre sont des fichiers de données.

InnoDB indexe l'ID via la structure B+Tree, puis stocke l'enregistrement dans le nœud feuille

Si le champ indexé n'est pas l'ID de clé primaire, créez un index pour le champ, puis stockez la clé primaire de l'enregistrement dans le nœud feuille, puis recherchez l'enregistrement correspondant via l'index de clé primaire.

Pour plus de questions connexes, veuillez visiter le site Web PHP chinois : Tutoriel vidéo PHP

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!