Architecture MySQL

Revoir le premier chapitre de l'architecture MySQL et de l'histoire de « Haute performance MySQL »

Architecture logique MySQL 1.1

Référence

Figure 1-1 : Schéma de l'architecture logique du serveur MySQL

Les services de niveau supérieur ne sont pas propres à MySQL, la plupart sont basés sur le réseau Les outils ou services client/serveur ont une architecture similaire. Tels que le traitement de la connexion, l'authentification des autorisations, la sécurité, etc.

L'architecture de deuxième couche est la partie la plus intéressante de MySQL. La plupart des fonctions de service de base de MySQL se trouvent dans cette couche, y compris l'analyse des requêtes, l'analyse, l'optimisation, la mise en cache et toutes les fonctions intégrées (telles que les fonctions de date, d'heure, mathématiques et de chiffrement). Implémentation de la couche : procédures stockées, déclencheurs, vues, etc.

La troisième couche contient le moteur de stockage. Le moteur de stockage est responsable du stockage et de la récupération des données dans MySQL. Comme divers systèmes de fichiers sous GNU/Linux, chaque moteur de stockage a ses avantages et ses inconvénients. Le serveur communique avec le moteur de stockage via des API. Ces interfaces masquent les différences entre les différents moteurs de stockage, rendant ces différences transparentes pour le processus de requête de couche supérieure. L'API du moteur de stockage contient des dizaines de fonctions de bas niveau permettant d'effectuer des opérations telles que « démarrer une transaction » ou « extraire une ligne d'enregistrements en fonction de la clé primaire ». Cependant, le moteur de stockage n'analysera pas SQL et les différents moteurs de stockage ne communiqueront pas entre eux, mais répondront simplement à la demande du serveur supérieur.

1.2 Contrôle de concurrence

1.2.1 Verrouillage en lecture-écriture

Ces deux types de verrous sont généralement appelés verrous partagés et verrous exclusifs, également appelés verrou en lecture (read lock) et verrouillage en écriture (verrouillage en écriture). Les verrous de lecture sont partagés ou non bloquants. Plusieurs clients peuvent lire la même ressource en même temps sans interférer les uns avec les autres. Les verrous en écriture sont exclusifs, ce qui signifie qu'un verrou en écriture bloque les autres verrous en écriture et en lecture.

1.2.2 Granularité du verrouillage

Les deux stratégies de verrouillage les plus importantes : le verrouillage de la table et le verrouillage au niveau de la ligne

Verrouillage de la table (verrouillage de la table)

Table Les verrous constituent la stratégie de verrouillage la plus élémentaire dans MySQL et la stratégie la moins coûteuse. Cela verrouillera toute la table. Avant qu'un utilisateur puisse effectuer des opérations d'écriture (insertion, suppression, mise à jour, etc.) sur la table, il doit obtenir un verrou en écriture, qui bloquera toutes les opérations de lecture et d'écriture sur la table par d'autres utilisateurs. Ce n'est que lorsqu'il n'y a pas de verrou en écriture que les autres utilisateurs en lecture peuvent obtenir le verrou en lecture, et les verrous en lecture ne se bloquent pas les uns les autres.

Les verrous de table peuvent également avoir de bonnes performances dans certains scénarios. Par exemple, les verrous de table READ LOCAL prennent en charge certains types d'opérations d'écriture simultanées. De plus, les verrous en écriture ont également une priorité plus élevée que les verrous en lecture, de sorte qu'une demande de verrou en écriture peut être insérée devant la file d'attente des verrous en lecture (un verrou en écriture peut être inséré devant un verrou en lecture dans la file d'attente des verrous, alors qu'un verrou en lecture le verrou ne peut pas être inséré) à l’avant du verrou en écriture).

Verrouillage au niveau de la ligne (verrouillage de la ligne)

Le verrouillage au niveau de la ligne peut prendre en charge le traitement simultané dans la plus grande mesure (mais entraîne également la plus grande surcharge de verrouillage). Comme nous le savons tous, le verrouillage au niveau des lignes est implémenté dans InnoDB et XtraDB, ainsi que dans certains autres moteurs de stockage. Les verrous au niveau des lignes ne sont implémentés qu'au niveau de la couche moteur de stockage, mais pas au niveau de la couche serveur MySQL. La couche serveur n'a aucune connaissance de l'implémentation du verrouillage dans le moteur de stockage.

1.3 Transactions

Les transactions prennent en charge les principes ACID.

Atomicité

Une transaction doit être considérée comme une unité minimale de travail indivisible.

Cohérence

La base de données passe toujours d'un état cohérent à un autre état cohérent.

Isolement

De manière générale, les modifications apportées par une transaction ne sont pas visibles par les autres transactions avant d'être finalement validées.

Durabilité

Une fois une transaction validée, ses modifications seront enregistrées définitivement dans la base de données.

1.3.1 Niveau d'isolement

Ce qui suit est une brève introduction aux quatre niveaux d'isolement.

READ UNCOMMITTED (lecture non validée)

Au niveau READ UNCOMMITTED, les modifications dans une transaction sont visibles par les autres transactions même si elles ne sont pas validées. Les transactions peuvent lire des données non validées, également appelées lecture sale. Ce niveau peut causer de nombreux problèmes. En termes de performances, READ UNCOMMITTED n'est pas bien meilleur que les autres niveaux, mais il lui manque de nombreux avantages des autres niveaux, à moins qu'il n'y ait des raisons vraiment nécessaires, il est rarement utilisé dans les applications pratiques.

LIRE ENGAGÉ

Le niveau d'isolement par défaut de la plupart des systèmes de bases de données est READ COMMITTED (mais pas MySQL). Toutes les modifications apportées par une transaction depuis le début jusqu'à sa validation ne sont pas visibles par les autres transactions. Ce niveau est parfois appelé lecture non répétable, car exécuter deux fois la même requête peut entraîner des résultats différents.

REPEATABLE READ (lecture répétable)

　 REPEATABLE READ résout le problème de la lecture sale. Ce niveau garantit que les résultats de la lecture du même enregistrement plusieurs fois dans la même transaction sont cohérents. Cependant, en théorie, le niveau d'isolement de lecture répétable ne peut toujours pas résoudre un autre problème de lecture fantôme (Phantom Read). La lecture dite fantôme signifie que lorsqu'une transaction lit des enregistrements dans une certaine plage, une autre transaction insère un nouvel enregistrement dans la plage. Lorsque la transaction précédente lit à nouveau les enregistrements dans la plage, elle produira une ligne fantôme. Les moteurs de stockage InnoDB et XtraDB résolvent le problème des lectures fantômes grâce au contrôle de concurrence multiversion (MVCC).

La lecture répétable est le niveau d'isolation des transactions par défaut de MySQL.

SERIALIZABLE (sérialisable)

SERIALIZABLE est le niveau d'isolement le plus élevé. Il évite le problème de lecture fantôme mentionné précédemment en forçant les transactions à être exécutées en série. En termes simples, SERIALIZABLE verrouillera chaque ligne de données récupérées, ce qui peut entraîner de nombreux délais d'attente et des problèmes de conflit de verrouillage. Ce niveau d'isolement est rarement utilisé dans les applications réelles. Ce niveau ne doit être pris en compte que lorsqu'il est absolument nécessaire pour garantir la cohérence des données et qu'aucune concurrence n'est acceptable.

1.3.2 Deadlock

Deadlock fait référence à deux transactions ou plus s'occupant mutuellement sur la même ressource et demandant de verrouiller les ressources occupées les unes par les autres, ce qui entraîne phénomène de cercle vicieux. Des blocages peuvent survenir lorsque plusieurs transactions tentent de verrouiller des ressources dans des ordres différents. Un blocage peut également se produire lorsque plusieurs transactions verrouillent la même ressource en même temps.

Afin de résoudre ce problème, le système de base de données implémente divers mécanismes de détection de blocage et de délai d'attente de blocage. Les systèmes plus complexes, tels que le moteur de stockage InnoDB, sont plus capables de détecter les dépendances circulaires sans blocage et de renvoyer immédiatement une erreur. Cette solution est très efficace, sinon un blocage entraînera des requêtes très lentes. Une autre solution consiste à abandonner la demande de verrouillage lorsque le temps de requête atteint le paramètre de délai d'attente du verrouillage. Cette méthode n'est généralement pas bonne. La méthode actuelle d'InnoDB pour gérer les blocages consiste à annuler la transaction contenant le moins de verrou exclusif au niveau de la ligne (il s'agit d'un algorithme d'annulation de blocage relativement simple).

Le comportement et l'ordre des verrous sont liés au moteur de stockage. En exécutant les instructions dans le même ordre, certains moteurs de stockage produiront des blocages, tandis que d'autres ne le feront pas. Les blocages se produisent pour deux raisons : certains sont dus à de véritables conflits de données, souvent difficiles à éviter, mais d'autres sont entièrement dus à la manière dont le moteur de stockage est implémenté.

1.3.3 Journal des transactions

Grâce au journal des transactions, lorsque le moteur de stockage modifie les données de la table, il lui suffit de modifier sa copie mémoire, puis d'enregistrer la modification de la transaction log qui est conservé sur le disque dur au lieu de conserver les données modifiées elles-mêmes sur le disque à chaque fois. Le journal des transactions est annexé. Une fois le journal des transactions conservé, les données modifiées dans la mémoire peuvent être lentement renvoyées sur le disque en arrière-plan. À l'heure actuelle, la plupart des moteurs de stockage sont implémentés de cette manière, que nous appelons généralement la journalisation à écriture anticipée (Write-Ahead Logging). La modification des données nécessite une écriture deux fois sur le disque.

Si la modification des données a été enregistrée dans le journal des transactions et a persisté, mais que les données elles-mêmes n'ont pas été réécrites sur le disque et que le système plante, le moteur de stockage peut automatiquement restaurer ces données modifiées au redémarrage. La méthode de récupération spécifique dépend du moteur de stockage.

1.3.4 Transactions dans MySQL

1.4 Contrôle de concurrence multi-version

La mise en œuvre de MVCC est réalisée en enregistrant un instantané des données à un moment donné. En d’autres termes, quel que soit le temps d’exécution, les données vues par chaque transaction sont cohérentes. Selon l'heure à laquelle la transaction démarre, les données vues par chaque transaction sur la même table au même moment peuvent être différentes. Ci-dessous, nous illustrons le fonctionnement de MVCC à travers une version simplifiée du comportement d'InnoDB.

Le MVCC d'InnoDB est implémenté en enregistrant deux colonnes cachées derrière chaque ligne d'enregistrements. Parmi ces deux colonnes, l’une contient l’heure de création de la ligne et l’autre l’heure d’expiration (ou l’heure de suppression) de la ligne. Bien entendu, ce qui est stocké n’est pas la valeur temporelle réelle, mais le numéro de version du système. Chaque fois qu'une nouvelle transaction est démarrée, le numéro de version du système sera automatiquement incrémenté. Le numéro de version du système au début de la transaction sera utilisé comme numéro de version de la transaction, qui est utilisé pour comparer avec le numéro de version de chaque ligne d'enregistrements interrogés. Jetons un coup d'œil à la façon dont MVCC fonctionne spécifiquement sous le niveau d'isolement REPEATABLE READ.

SELECT

InnoDB vérifiera chaque ligne d'enregistrements en fonction des deux conditions suivantes :

a． InnoDB recherche uniquement les lignes de données dont la version est antérieure à la version actuelle de la transaction (c'est-à-dire que le numéro de version système de la ligne est inférieur ou égal au numéro de version système de la transaction). Cela garantit que les lignes sont lues par la transaction. soit existent déjà avant le début de la transaction, soit insérés, soit modifiés par la transaction elle-même.

b． La version supprimée de la ligne est soit indéfinie, soit supérieure au numéro de version actuel de la transaction. Cela garantit que les lignes lues par la transaction n'ont pas été supprimées avant le démarrage de la transaction.

Seuls les enregistrements qui remplissent les deux conditions ci-dessus peuvent être renvoyés en tant que résultats de requête.

INSERT

InnoDB enregistre le numéro de version actuel du système en tant que numéro de version de ligne pour chaque ligne nouvellement insérée.

DELETE

InnoDB enregistre le numéro de version actuel du système comme identifiant de suppression de ligne pour chaque ligne supprimée.

UPDATE

​​​​InnoDB insère une nouvelle ligne d'enregistrements, enregistre le numéro de version actuel du système en tant que numéro de version de la ligne et enregistre le numéro de version actuel du système dans la ligne d'origine en tant que suppression de ligne identifiant.

Enregistrez ces deux numéros de version supplémentaires du système afin que la plupart des opérations de lecture puissent être effectuées sans verrouillage. Cette conception rend l'opération de lecture des données très simple, les performances sont très bonnes et garantit également que seules les lignes répondant aux normes sont lues. Les inconvénients sont que chaque ligne d'enregistrements nécessite un espace de stockage supplémentaire, davantage de vérifications de lignes et des travaux de maintenance supplémentaires.

MVCC ne fonctionne que sous deux niveaux d'isolement : REPEATABLE READ et READ COMMITTED. Les deux autres niveaux d'isolement sont incompatibles avec MVCC Note 4 car READ UNCOMMITTED lit toujours la dernière ligne de données, et non la ligne de données conforme à la version actuelle de la transaction. SERIALIZABLE verrouillera toutes les lignes lues.

1.5 Moteur de stockage MySQL

Dans le système de fichiers, MySQL enregistre chaque base de données (également appelée schéma) en tant que sous-répertoire sous le répertoire de données. Lors de la création d'une table, MySQL créera un fichier .frm portant le même nom que la table dans le sous-répertoire de la base de données pour enregistrer la définition de la table. Par exemple, lorsque vous créez une table nommée MyTable, MySQL enregistrera la définition de la table dans le fichier MyTable.frm. Étant donné que MySQL utilise des répertoires et des fichiers du système de fichiers pour stocker les définitions de bases de données et de tables, la sensibilité à la casse est étroitement liée à la plate-forme spécifique. Sous Windows, la casse n'est pas sensible ; dans les cas de type Unix, elle est sensible à la casse. Différents moteurs de stockage enregistrent les données et les index de différentes manières, mais la définition des tables est gérée de manière uniforme dans la couche de service MySQL.

Vous pouvez utiliser la commande SHOW TABLE STATUS (dans les versions postérieures à MySQL 5.0, vous pouvez également interroger la table correspondante dans INFORMATION SCHEMA) pour afficher les informations relatives à la table. Par exemple, pour la table utilisateur dans la base de données mysql :

mysql> SHOW TABLE STATUS LIKE 'user' G

Nom : user

Moteur : MyISAM

Row_format : Dynamique

Lignes : 6

Avg_row_length : 59

Longueur des données : 356

Longueur maximale des données : 4294967295

Longueur de l'index : 2048

Data_free : 0

Auto_increment : NULL

Create_time : 2002-01-24 18:07:17

Update_time : 2002 - 01- 24 21 : 56 : 29

Check_time : NULL

Collation : ut f8_bin

Somme de contrôle : NULL

Create_options :

Commentaire : Utilisateurs et privilèges globaux

1 ligne dans l'ensemble (o.oo sec)

Les résultats montrent qu'il s'agit d'une table MyISAM. Il y a beaucoup d'autres informations ainsi que des statistiques dans la sortie. Vous trouverez ci-dessous une brève introduction à la signification de chaque ligne.

Nom	Nom de la table.
Moteur	Le type de moteur de stockage de la table. Dans les anciennes versions, le nom de la colonne était Type et non Moteur.
Format de ligne	format de ligne. Pour les tables MyISAM, les valeurs facultatives sont Dynamique, Fixe ou Comprimé. La longueur des lignes de Dynamic est variable et contient généralement des champs de longueur variable, tels que VARCHAR ou BLOB. La longueur de ligne fixe est fixe et contient uniquement des colonnes de longueur fixe, telles que CHAR et INTEGER. Les lignes compressées n'existent que dans les tableaux compressés.
Lignes	Le nombre de lignes dans le tableau. Pour MyISAM et certains autres moteurs de stockage, la valeur est précise, mais pour InnoDB il s'agit d'une estimation.
Moyenne_row_length	Le nombre moyen d'octets par ligne.
Data_length	La taille des données du tableau en octets.
Max- data_length	La capacité maximale des données de la table. Cette valeur est liée au moteur de stockage.
Index_length	La taille de l'index en octets.
Data_free	Pour les tables MyISAM, représente l'espace alloué mais actuellement inutilisé. Cette partie de l'espace comprend les lignes précédemment supprimées et l'espace qui pourra être utilisé par INSERT ultérieurement.
Auto_increment	La valeur du prochain AUTO INCREMENT.
Create_time	L'heure de création de la table.
Update_time	L'heure de la dernière modification des données de la table.
Check_time	L'heure à laquelle la table a été vérifiée pour la dernière fois à l'aide de la commande CKECK TABLE ou de l'outil myisamchk.
Collation	Le jeu de caractères par défaut et le classement des colonnes de caractères pour la table.
Somme de contrôle	Si cette option est activée, une somme de contrôle en temps réel de la table entière est enregistrée.
Create_options	Autres options spécifiées lors de la création du tableau.
Commentaire	Cette colonne contient des informations supplémentaires. Pour les tables MyISAM, les commentaires inclus lors de la création de la table sont enregistrés. Pour les tables InnoDB, les informations d'espace restantes de l'espace table InnoDB sont enregistrées. S'il s'agit d'une vue, cette colonne contient le mot texte "VIEW".

1.6 Chronologie MySQL

1.7 Modèle de développement MySQL

Référence : "MySQL haute performance"

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!