MYSQL_Introduction au contrôle de concurrence multiversion, au moteur de stockage et à l'indexation

Contrôle de concurrence multi-versions

La plupart des moteurs de stockage transactionnels de MySQL n'implémentent pas de simples verrous au niveau des lignes. Basés sur l'amélioration des performances de concurrence, ils implémentent généralement un contrôle de concurrence multi-versions en même temps.

MVCC peut être considéré comme une variante du verrouillage au niveau des lignes, mais il évite les opérations de verrouillage dans de nombreux cas car la surcharge est moindre.

Le MVCC d'InnoDB est implémenté via deux colonnes cachées enregistrées à la fin de chaque enregistrement de ligne, l'une enregistre l'heure de création de la ligne et l'autre enregistre l'heure d'expiration (ou l'heure de suppression) de. la ligne. ), bien sûr, ce qui est stocké n'est pas la valeur temporelle réelle, mais la version du système. Chaque fois qu'une nouvelle transaction est démarrée, le numéro de version du système est 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 les numéros de version de chaque ligne interrogée.

Sous le niveau d'isolement REPEATABLE READ, l'implémentation de MVCC :

• SELECT

• La version de recherche de InnoDB est antérieur à la ligne de données du numéro de version de la transaction actuelle. Cela garantit que les lignes lues par la transaction existent déjà avant le début de la transaction ou ont été insérées ou modifiées par la transaction elle-même. 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 la ligne lue par la transaction n'a pas été supprimée avant le début de la transaction.

• 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 en tant qu'identifiant de suppression de ligne pour chaque ligne supprimée.

• MISE À JOUR

• InnoDB enregistre le numéro de version actuel du système en tant que numéro de version de ligne pour insérer un nouvel enregistrement de la compagnie aérienne Un, et en même temps Enregistrez le numéro de version actuel du système sur la ligne d'origine en tant que numéro de version de suppression de ligne.

MVCC ne fonctionne qu'à deux niveaux d'isolement : REPEATABLE READ et READ COMMITED. Les deux autres niveaux d'isolement sont incompatibles avec MVCC. Parce que READ UNCOMMITED 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 de données lues.

Moteur de stockage

Moteur de stockage InnoDB

InnoDB est le moteur transactionnel par défaut de MYSQL, et c'est aussi le plus Moteur de stockage important et le plus utilisé. Sauf raison très particulière d’utiliser un autre moteur de stockage, le moteur InnoDB doit être prioritaire.

InnoDB utilise MVCC pour prendre en charge une concurrence élevée et implémente quatre niveaux d'isolation standard. Le niveau par défaut est REPEATABLE READ, et la stratégie Gap Lock + MVCC empêche les lectures fantômes. Permet à InnoDB non seulement de verrouiller les lignes de la conception de la requête, mais également de verrouiller les espaces dans l'index pour empêcher l'insertion de lignes fantômes.

Verrouillage des espaces : lorsque nous récupérons des données en utilisant des conditions de plage au lieu de conditions d'égalité et demandons des verrous partagés ou exclusifs, InnoDB verrouillera les entrées d'index des enregistrements de données existants qui remplissent les conditions des valeurs clés dans les conditions ; Les enregistrements qui se trouvent dans la plage mais qui n'existent pas sont appelés "espaces". InnoDB verrouillera également cet "espace". Ce mécanisme de verrouillage est ce qu'on appelle le verrou d'espacement (Next-Key lock).
Référence : Gap Lock (Next-Key Lock)

L'index principal est un index clusterisé, qui enregistre les données dans l'index pour éviter la lecture directe à partir du disque, améliorant ainsi considérablement les performances des requêtes.

InnoDB a apporté de nombreuses optimisations internes, notamment une lecture anticipée prévisible lors de la lecture des données à partir du disque, un index de hachage adaptatif qui peut créer automatiquement un index de hachage en mémoire pour accélérer les opérations et une capacité à accélérer les insertions. Opérations d'insertion de tampon, etc.

Moteur de stockage MyISAM

Dans mysql5.1 et les versions précédentes, MyISAM est le moteur de stockage par défaut. MyISAM fournit un grand nombre de fonctionnalités, notamment l'indexation de texte intégral, la compression, les fonctions spatiales, etc., mais il ne prend pas en charge les transactions et les verrous au niveau des lignes, et il ne fait aucun doute qu'il ne peut pas récupérer en toute sécurité après un crash.

Pour les données en lecture seule, ou si la table est relativement petite et peut tolérer des opérations de réparation, le moteur MyISAM peut toujours être utilisé.

Lors de la création d'une table MyISAM, si l'option DELAY_KEY_WRITE est spécifiée, lorsque chaque modification est terminée, les données d'index modifiées ne seront pas écrites immédiatement sur le disque, mais seront écrites dans le tampon de clé en mémoire. Le bloc d'index est écrit sur le disque uniquement lorsque le tampon de clé est effacé ou que la table est fermée. Cette méthode peut grandement améliorer les performances d'écriture, mais elle entraînera des dommages à l'index lorsque la base de données ou l'hôte tombe en panne, nécessitant des opérations de réparation.

Comparez

• Transaction : InnoDB prend en charge les transactions, MyISAM ne prend pas en charge les transactions.

• Granarité du verrouillage : InnoDB prend en charge les verrous au niveau de la table et les verrous au niveau de la ligne, tandis que MyISAM ne prend en charge que les verrous au niveau de la table.

• Clés étrangères : InnoDB prend en charge les clés étrangères.

• Sauvegarde : InnoDB prend en charge la sauvegarde à chaud, mais des outils sont requis.

• Récupération après crash : la probabilité que MyISAM soit endommagé après un crash est beaucoup plus élevée que celle d'InnoDB, et la vitesse de récupération est également plus lente.

• Autres fonctionnalités : MyISAM prend en charge l'indexation de texte intégral, la compression, les fonctions spatiales et d'autres fonctionnalités.

Type de sauvegarde

• sauvegarde à froid : le service MySQL doit être désactivé et les demandes de lecture et d'écriture ne sont pas autorisées

  ;

• sauvegarde à chaud : le service est en ligne, mais ne prend en charge que les demandes de lecture et n'autorise pas les demandes d'écriture

• sauvegarde à chaud : lors de la sauvegarde, l'entreprise ; ne sera pas affecté.

Index

Les indices (également appelés « clés ») sont utilisés par les moteurs de stockage pour trouver rapidement des enregistrements et des données. structurer en .

Index B-Tree

La plupart des moteurs MySQL prennent en charge cet index.

Bien que le terme « B-Tree » soit utilisé, différents moteurs de stockage peuvent utiliser différentes structures de stockage. Le moteur de stockage du cluster NDB utilise en fait T-Tree en interne, tandis qu'InnoDB utilise B+Tree.

L'index B-Tree peut accélérer l'accès aux données car le moteur de stockage n'a pas besoin d'effectuer une analyse complète de la table pour obtenir les données requises. Au lieu de cela, il commence la recherche à partir du nœud racine de l'index, donc la recherche. la vitesse sera beaucoup plus rapide.

B-Tree organise et stocke séquentiellement les colonnes d'index, ce qui est très approprié pour rechercher des données de plage. Étant donné que l'arborescence d'index est ordonnée, outre la recherche des utilisateurs, elle peut également être utilisée pour le tri et le regroupement.

Vous pouvez spécifier plusieurs colonnes comme colonnes d'index, et plusieurs colonnes d'index forment ensemble une clé d'index. L'index B-Tree convient à la recherche de valeur de clé complète, de plage de valeurs de clé ou de préfixe de clé, où la recherche de préfixe de clé n'est applicable qu'à la recherche basée sur le préfixe le plus à gauche. La recherche doit commencer à partir de la colonne la plus à gauche de l'index.

Structure des données de l'index B-Tree

B-Tree

Afin de décrire B-Tree, définissez d'abord un élément de données L'enregistrement est un tuple [clé, données]. La clé est la valeur clé de l'enregistrement. Pour différents enregistrements de données, la clé est différente les unes des autres. Les données sont les données de l'enregistrement de données, à l'exception de la clé.

• Tous les nœuds ont la même profondeur, ce qui signifie que le B-Tree est équilibré.

• Les clés d'un nœud sont disposées de manière non décroissante de gauche à droite.

• Si les touches adjacentes gauche et droite d'un pointeur sont respectivement keyi et keyi+1, et ne sont pas nulles, alors toutes les touches pointées par le pointeur sont supérieures ou égales à keyi et inférieur ou égal à keyi+ 1.

Algorithme de recherche : Effectuez d'abord une recherche binaire sur le nœud racine. S'il est trouvé, renvoyez les données du nœud correspondant. Sinon, recherchez de manière récursive sur le nœud pointé par le pointeur dans le fichier. intervalle correspondant.

Étant donné que l'insertion et la suppression de nouveaux enregistrements de données détruiront les propriétés de B-Tree, lors de l'insertion et de la suppression, l'arborescence doit être divisée, fusionnée, pivotée, etc. pour conserver les propriétés de B-Tree.

B+Tree

Par rapport à B-Tree, B+Tree présente les caractéristiques suivantes :

• La limite supérieure du pointeur de chaque nœud est 2d au lieu de 2d+1 (d est le degré du B-Tree).

• Les nœuds internes ne stockent pas de données, seuls les nœuds externes ne stockent pas de pointeurs.

B+Tree avec pointeurs d'accès séquentiels

Généralement dans un système de base de données ou un système de fichiers Le B +Les structures arborescentes utilisées sont optimisées sur la base du B+Tree classique et des pointeurs d'accès séquentiels sont ajoutés.

Le but de cette optimisation est de fournir des performances d'accès par intervalles, par exemple, si vous souhaitez interroger tous les enregistrements avec les clés 18 à 49 dans la figure.

Avantages

Les arbres équilibrés tels que les arbres rouge-noir peuvent également être utilisés pour implémenter des index, mais les systèmes de fichiers et les systèmes de bases de données utilisent généralement B-Tree comme structure d'index. . Les principales sont les suivantes Deux raisons :

• De meilleurs temps de récupération : La complexité temporelle de la récupération des données dans un arbre équilibré est égale à la hauteur de l'arbre h, et la hauteur de l'arbre est à peu près égale à la hauteur de l'arbre. O(h) = O(logN), où d est le degré de sortie de chaque nœud. Le degré extérieur de l'arbre rouge-noir est de 2, tandis que le degré extérieur du B-Tree est généralement très grand. La hauteur de l'arbre h de l'arbre rouge-noir est évidemment beaucoup plus élevée que celle du B-Tree, donc le nombre de recherches est plus important. B+Tree est plus adapté aux index de mémoire externe que B-Tree car le champ de données est supprimé des nœuds dans B+Tree, il peut donc avoir un degré de sortie plus grand et l'efficacité de récupération sera plus élevée.

• Utilisation des fonctionnalités de lecture anticipée de l'ordinateur : afin de réduire les E/S des disques, les disques ne sont souvent pas lus strictement à la demande, mais lus à l'avance à chaque fois. La base théorique en est le principe de localité bien connu en informatique : lorsqu’une donnée est utilisée, les données proches sont généralement utilisées immédiatement. Pendant le processus de lecture anticipée, le disque est lu séquentiellement. La lecture séquentielle ne nécessite pas de recherche de disque et ne nécessite qu'un court temps de rotation, la vitesse est donc très rapide. Le système d'exploitation divise généralement la mémoire et le disque en blocs de taille solide, chaque bloc est appelé une page et la mémoire et le disque échangent des données en unités de pages. Le système de base de données définit la taille d'un nœud dans l'index sur la taille de la page, de sorte qu'un nœud puisse être complètement chargé en une seule E/S, et que les nœuds adjacents puissent également être préchargés à l'aide de la fonction de lecture anticipée.

Référence : Structure des données et principes d'algorithme derrière les index MySQL

Indice de hachage

Le moteur InnoDB a une fonction spéciale appelée " Adaptive hash index", lorsqu'une valeur d'index est utilisée très fréquemment, un index de hachage sera créé au-dessus de l'index B+Tree, de sorte que l'index B+Tree présente certains avantages de l'index de hachage, tels qu'une recherche rapide de hachage.

L'index de hachage peut être recherché en un temps O(1), mais il perd son ordre. Il présente les limitations suivantes :

• L'index de hachage ne contient que des hachages. La valeur suit le. pointeur de ligne et ne stocke pas la valeur du champ, vous ne pouvez donc pas utiliser la valeur dans l'index pour éviter de supprimer toutes les lignes.

• ne peut pas être utilisé pour le tri et le regroupement.

• ne prend en charge que la recherche précise et ne peut pas être utilisé pour la recherche partielle et la recherche par plage.

• Lorsqu'une collision de hachage se produit, le moteur de stockage doit parcourir tous les pointeurs de ligne de la liste chaînée.

Index de données spatiales (R-Tree)

La table MyISAM prend en charge l'index spatial et peut être utilisée comme stockage de données géographiques. Les index spatiaux indexent les données de toutes les dimensions et les requêtes peuvent être combinées en fonction de n'importe quelle dimension.

Les fonctions liées au SIG de MySQL telles que MBRONTAINS() doivent être utilisées pour conserver les données.

Index de texte intégral

L'index de texte intégral est un type spécial d'index qui recherche des mots-clés dans le texte au lieu de comparer directement les valeurs dans l'index. La condition de recherche utilise MATCH AGAINST au lieu de simple WHERE.

L'index de texte intégral est généralement implémenté à l'aide d'un index trié inversé, qui enregistre le mappage du document où le mot-clé expire.

Le moteur de stockage MyISAM prend en charge l'indexation de texte intégral, et le moteur de stockage InnoDB prend également en charge l'indexation de texte intégral dans Mysql 5.6.4.

Avantages des index

• Réduisez considérablement le nombre de lignes de données que le serveur doit analyser.

• Aide le serveur à éviter de trier et de créer des tables temporaires (les index B+Tree sont ordonnés et peuvent être utilisés pour les opérations Trier par et Regrouper par).

• Convertissez les E/S aléatoires en E/S séquentielles (l'index B+Tree est ordonné, c'est-à-dire que les données adjacentes sont stockées ensemble).

Articles associés :

Analyse du principe d'implémentation de la base de données MySQL InnoDB contrôle multi-version du moteur de stockage (MVCC)

Introduction au moteur de stockage 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!