Quels sont les inconvénients de l'index clusterisé MySQL ?

L'index clusterisé n'est pas un type d'index distinct, mais une méthode de stockage de données (pas une structure de données, mais une structure de stockage). Les détails spécifiques dépendent de son implémentation, mais l'index clusterisé d'innodb est en fait l'index et les données btree. les lignes sont enregistrées dans la même structure.

Lorsqu'une table a un index, ses lignes de données sont en fait stockées dans les pages feuilles de l'index. Le clustering signifie que les lignes de données et les valeurs clés adjacentes sont stockées ensemble de manière compacte, car les lignes de données. ne peut pas être stocké en même temps. Stocké à deux endroits différents, une table ne peut donc avoir qu'un seul index clusterisé. Étant donné que le moteur de stockage est responsable de l'implémentation de l'index, tous les moteurs de stockage ne prennent pas en charge les index clusterisés. Ce qui suit présente principalement innodb, mais les principes discutés ci-dessous sont applicables à tout moteur prenant en charge les index clusterisés :

La page feuille contient toutes les données de la ligne, mais la page nœud ne contient que la colonne d'index (ou elle on peut dire que ce n'est pas une feuille. Les pages de nœud du nœud contiennent l'index de la valeur d'index, car les valeurs contenues dans ces pages de nœud sont extraites de la colonne d'index).

Innodb agrégera les données par clé primaire. S'il n'y a pas de clé primaire définie, Innodb choisira à la place le premier index unique non vide. S'il n'y a pas d'index unique non vide, Innodb définira implicitement un 6. Clé primaire rowid d'un octet en tant qu'index clusterisé. InnoDB regroupe uniquement les enregistrements dans la même page, les pages contenant des valeurs clés adjacentes peuvent être éloignées les unes des autres.

Remarque : les clés primaires en cluster peuvent améliorer les performances, mais elles peuvent également entraîner de graves problèmes de performances, en particulier lorsque le moteur de stockage de la table est converti d'innodb vers un autre moteur.

Les données agrégées présentent certains avantages importants :

R : Les données associées peuvent être enregistrées ensemble. Par exemple, lors de la mise en œuvre du courrier électronique, vous pouvez agréger les données en fonction de l'ID utilisateur, il vous suffit donc de les collecter. les données de Tous les e-mails d'un utilisateur peuvent être obtenues en lisant un petit nombre de pages de données à partir du disque. Si l'index clusterisé n'est pas utilisé, chaque e-mail peut provoquer une E/S disque

B : L'accès aux données est plus rapide, et l'index clusterisé indexera et Les données sont conservées dans le même btree, donc obtenir des données à partir d'un index clusterisé est généralement plus rapide que de rechercher dans un index non clusterisé

C : Les requêtes utilisant une analyse d'index de couverture peuvent utilisez directement la valeur de la clé primaire dans le nœud de la page

Inconvénients de l'index clusterisé :

A : Les données clusterisées maximisent les performances des applications gourmandes en E/S, mais si les données sont toutes placées en mémoire, l'ordre d'accès n'est pas si important Non, l'index clusterisé n'a aucun avantage

B : La vitesse d'insertion dépend fortement de l'ordre d'insertion L'insertion dans l'ordre de la clé primaire est le moyen le plus rapide de charger des données. la table innodb, mais si elle n'est pas chargée dans l'ordre des données de clé primaire, alors il est préférable d'utiliser la commande optimiser la table pour réorganiser la table une fois le chargement terminé

C : Mise à jour des colonnes d'index clusterisé est très coûteux car cela oblige innodb à déplacer chaque ligne mise à jour vers un nouvel emplacement

D : Lorsqu'une table basée sur un index clusterisé insère une nouvelle ligne, ou lorsque la clé primaire est mise à jour et que la ligne doit être déplacé, il peut être confronté au problème de fractionnement de page. Lorsque la valeur de clé primaire de la ligne nécessite que la ligne soit insérée dans un certain. Lorsque la page est pleine, le moteur de stockage divise la page en deux pages pour s'adapter à la ligne. Il s'agit d'une opération de fractionnement de page. Le fractionnement de page fera que la table occupera plus d'espace disque

E : Les index d'agrégation peuvent ralentir l'analyse complète de la table, en particulier lorsque les lignes sont clairsemées ou que le stockage des données est discontinu en raison de fractionnements de pages

F : L'index secondaire peut être plus grand que prévu, car les nœuds Leaf de l'index secondaire contiennent les colonnes de clé primaire des lignes de référence.

G : L'accès à l'index secondaire nécessite deux recherches d'index au lieu d'une

Parce que ce qui est stocké dans le nœud feuille d'index secondaire n'est pas le pointeur vers l'emplacement physique de la ligne, mais la valeur de clé primaire de la ligne. Cela signifie que lors de la recherche de lignes via l'index secondaire, le moteur de stockage doit trouver le nœud feuille de l'index secondaire pour obtenir la valeur de clé primaire correspondante, puis utiliser cette valeur de clé primaire pour trouver la ligne correspondante dans l'index clusterisé. Un travail répété est effectué ici. Deux recherches btree au lieu d'une seule. Pour innodb, les index de hachage adaptatifs peuvent réduire ce travail répété.

Comparaison de la distribution des données entre le stockage physique innodb et myisam :

Myisam :

Il est stocké sur le disque dans l'ordre d'insertion des données. niveau dans myisam Il n'y a pas de différence dans la structure de l'index. L'index de clé primaire est un index unique non vide nommé primaire.

innodb :

Parce qu'innodb prend en charge les index clusterisés, il utilise une manière très différente de stocker les mêmes données. L'index clusterisé innodb contient les données de la table entière, pas seulement l'index, car. dans innodb , l'index clusterisé est une table, il ne nécessite donc pas de stockage de lignes indépendant comme myisam. Chaque nœud feuille de l'index cluster contient la valeur de la clé primaire, l'ID de transaction, le pointeur d'annulation pour la transaction et MVCC, ainsi que les valeurs de toutes les colonnes restantes. Si la clé primaire est un index de préfixe de colonne, InnoDB contient également la clé primaire complète. colonne et les valeurs de colonne restantes.

Une autre chose qui diffère de myisam est que l'index secondaire d'innodb est très différent de l'index clusterisé. Les nœuds feuilles de l'index secondaire d'innodb ne stockent pas le pointeur de ligne, mais la valeur de la clé primaire, et. utilisez-le comme pointeurs vers les lignes.Cette stratégie réduit le travail de maintenance de l'index secondaire lorsque les lignes sont déplacées ou que les pages de données sont divisées. L'utilisation de la valeur de la clé primaire comme pointeur fera que l'index secondaire occupera plus d'espace. n'a pas besoin de mettre à jour ce pointeur dans l'index secondaire lors du déplacement de lignes.

Insérez des lignes dans la table innodb dans l'ordre de la clé primaire. Si vous utilisez la table Innodb et qu'il n'y a aucune donnée à agréger, vous pouvez définir une clé de substitution comme clé primaire. ne devrait rien avoir à voir avec l'application. La méthode la plus simple consiste à utiliser auto_increment pour incrémenter automatiquement la colonne, ce qui peut garantir que les lignes de données sont insérées dans l'ordre et que les performances des opérations d'association basées sur la clé primaire seront meilleures.

N'utilisez pas l'UUID comme index clusterisé, sinon les performances seront terribles, car cela rend l'insertion de l'index cluster complètement aléatoire, rendant les données sans aucune caractéristique de clustering. Parce que l'UUID est utilisé comme clé primaire pour insérer des lignes, non seulement cela prend plus de temps, mais l'index est également plus grand. D'un autre côté, cela est sans doute dû au temps plus long. causé par le fractionnement des pages et le changement d'index causé par la fragmentation importante. Étant donné que les valeurs de la clé primaire sont séquentielles, Innodb stocke chaque enregistrement après l'enregistrement précédent lorsque le facteur de remplissage maximum de la page est atteint (le facteur de remplissage maximum par défaut d'InnoDB est de 15/16 de la taille de la page, laissant (pour en libérer). espace pour une modification ultérieure), l'enregistrement suivant sera écrit sur une nouvelle page. Une fois les données chargées dans cette séquence, la page de clé primaire sera approximativement remplie d'enregistrements séquentiels, ce qui correspond exactement aux résultats attendus (cependant, les pages d'index secondaire peuvent être différentes).

Sous la clé primaire UUID, comme la valeur de clé primaire de la ligne nouvellement insérée n'est pas nécessairement plus grande que la précédente, innodb ne peut pas simplement toujours insérer la nouvelle ligne à la fin de l'index, mais doit trouver la nouvelle ligne. L'emplacement approprié est généralement l'emplacement central des données existantes, et l'allocation d'un nouvel espace ajoutera beaucoup de travail supplémentaire et entraînera une distribution de données moins qu'optimale. Voici quelques inconvénients de l'utilisation de l'UUID comme clé primaire :

R : La page cible écrite peut avoir été vidée sur le disque et supprimée du cache, ou elle n'a pas été chargée dans le cache avant. l'insérer. Cela entraînera de nombreuses E/S aléatoires

B : Comme les écritures sont dans le désordre, innodb doit effectuer fréquemment des opérations de fractionnement de page pour allouer de l'espace pour de nouvelles lignes. Le fractionnement de page entraînera une grande quantité. de données à déplacer et à insérer en même temps Au moins trois pages doivent être modifiées au lieu d'une page

C : En raison des divisions de pages fréquentes, les pages deviendront clairsemées et remplies de manière irrégulière, de sorte que les données finales seront fragmenté

Après avoir chargé ces valeurs aléatoires dans l'index clusterisé, vous devrez peut-être faire une table d'optimisation pour reconstruire la table et optimiser le remplissage des pages. Lorsque vous utilisez InnoDB, vous devez autant que possible insérer les données dans l'ordre de la clé primaire et utiliser un simple incrément de la valeur de la clé de clustering pour insérer de nouvelles lignes autant que possible.

Remarque : Quand les clés primaires séquentielles entraînent-elles de moins bons résultats ?

Pour les charges de travail à forte concurrence, l'insertion dans l'ordre des clés primaires dans Innodb peut provoquer des conflits évidents. La limite supérieure de la clé primaire sera appelée point chaud, car toutes les insertions se produisent ici, donc des insertions simultanées peuvent provoquer. Conflit de verrouillage d'espacement, un autre point chaud peut être le mécanisme de verrouillage auto_increment. Si vous rencontrez ce problème, vous devrez peut-être reconcevoir la table ou l'application, ou modifier la configuration innodb_autoinc_lock_mode.

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!