Introduction détaillée au principe de l'index mysql innodb (exemple de code)

Ce que cet article vous apporte est une introduction détaillée (exemple de code) sur le principe d'indexation mysql innodb. Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Index clusterisé

La table du moteur de stockage innodb est une table organisée en index et les données de la table sont stockées dans l'ordre des clés primaires. L'index clusterisé construit un arbre B+ dans l'ordre des clés primaires de chaque table, et ses nœuds feuilles stockent les données d'enregistrement de ligne de la table entière, et ces nœuds feuilles deviennent des pages de données. (Recommandations associées : Tutoriel MySQL)

Le stockage de l'index clusterisé n'est pas physiquement continu, mais logiquement continu. Les nœuds feuilles sont triés dans l'ordre de la clé primaire et connectés via un. liste chaînée bidirectionnelle. Dans la plupart des cas, l'optimiseur de requêtes a tendance à utiliser des index clusterisés, car les index clusterisés peuvent trouver des données directement sur les nœuds feuilles et, comme l'ordre logique des données est défini, les requêtes sur les valeurs de plage sont accessibles très rapidement.

Cette fonctionnalité de l'index clusterisé détermine que les données de la table organisée en index font également partie de l'index. Étant donné que les données de la table ne peuvent être triées que selon un arbre B+, une table ne peut avoir qu'un seul index clusterisé.

Dans Innodb, l'index clusterisé est l'index de clé primaire par défaut. S'il n'y a pas de clé primaire, un index clusterisé sera construit selon les règles suivantes :

• Lorsqu'il n'y a pas de clé primaire, une colonne d'index non nulle et unique sera utilisée comme clé primaire et devenez l'index clusterisé de cette table ;
• S'il n'existe pas un tel index, InnoDB définira implicitement une clé primaire comme un index clusterisé.

Étant donné que la clé primaire utilise un index clusterisé, si la clé primaire est un identifiant à incrémentation automatique, les données correspondantes seront stockées de manière adjacente sur le disque et les performances d'écriture seront élevées. S'il se présente sous la forme d'une chaîne telle que uuid, une insertion fréquente entraînera un déplacement fréquent des blocs de disque par innodb et les performances d'écriture seront relativement faibles.

Arbre B+ (arbre de recherche équilibré multidirectionnel)

Nous savons que l'index du moteur innodb utilise la structure arborescente B+, alors pourquoi pas d'autres types de structures arborescentes, comme les arbres binaires ?

Lorsqu'un ordinateur stocke des données, il dispose d'une unité de stockage minimale, tout comme l'unité minimale de circulation du RMB est le centime. La plus petite unité du système de fichiers est un bloc. La taille d'un bloc est de 4K (cette valeur varie selon le système et peut être définie. Le moteur de stockage InnoDB possède également sa propre unité de stockage minimale - page (Page). la taille d'une page est de 16 Ko (cette valeur est également configurable).

La taille d'un fichier dans le système de fichiers n'est que de 1 octet, mais il doit occuper 4 Ko d'espace sur le disque. De la même manière, la taille de tous les fichiers de données dans innodb est toujours un multiple entier de 16 384 (16 Ko).

Ainsi, dans MySQL, un nœud de bloc qui stocke l'index occupe 16 Ko, et chaque opération d'E/S de MySQL utilisera la capacité de lecture anticipée du système pour charger 16 Ko à la fois. De cette façon, il est très inutile de mettre une seule valeur d'index dans ce nœud, car une seule valeur d'index peut être obtenue à un instant IO, donc un arbre binaire ne peut pas être utilisé.

L'arbre B+ est un arbre de recherche multidirectionnel. Un nœud peut contenir n valeurs, n = 16K / la taille de chaque valeur d'index.
Par exemple, si la taille du champ d'index est de 1 Ko, alors chaque nœud peut théoriquement stocker 16 valeurs d'index. Dans ce cas, l'arbre binaire ne peut charger qu'une seule valeur d'index par IO, tandis que l'arbre B+ peut en charger 16.

Le nombre de voies de l'arbre B+ est n+1, où n est le nombre de valeurs qui existent dans chaque nœud. Par exemple, chaque nœud stocke 16 valeurs, alors cet arbre a 17 voies.

On peut également voir à partir d'ici que les nœuds de l'arbre B+ peuvent stocker plusieurs valeurs, de sorte que l'index de l'arbre B+ ne peut pas trouver une ligne spécifique avec une valeur de clé donnée. L'arborescence B+ peut uniquement trouver la page spécifique où la ligne de données est stockée, puis lire la page dans la mémoire, puis rechercher les données spécifiées dans la mémoire.

Pièce jointe : La différence entre l'arbre B et l'arbre B+ est que les nœuds non-feuilles de l'arbre B+ contiennent uniquement des informations de navigation et ne contiennent pas de valeurs réelles. Tous les nœuds feuilles et les nœuds connectés sont connectés à l'aide de listes chaînées. faciliter la recherche et la traversée d'intervalles.

L'index auxiliaire

est également appelé index non clusterisé. Ses nœuds feuilles ne contiennent pas toutes les données enregistrées dans la ligne. En plus de la valeur clé, le nœud feuille contient l'index. ligne dans chaque nœud feuille Contient également un signet, qui est la clé d'index clusterisé pour la ligne correspondante.

La figure suivante montre la relation entre l'index auxiliaire et l'index clusterisé (l'image vient d'Internet, il suffit de regarder la signification générale) :

Lors de l'utilisation de l'index auxiliaire Lors de la recherche de données, le moteur de stockage innodb obtiendra la clé primaire qui souhaite uniquement l'index de clé primaire via le nœud feuille d'index auxiliaire, puis trouvera l'enregistrement de ligne complet via l'index de clé primaire.

Par exemple, si vous souhaitez rechercher des données dans une arborescence d'index auxiliaire d'une hauteur de 3, vous devez effectuer 3 IO sur l'arborescence d'index auxiliaire pour trouver la clé primaire spécifiée si la hauteur du cluster. L'arbre d'index est également 3, alors vous devez rechercher trois fois dans l'arbre d'index clusterisé pour enfin trouver une page où se trouvent les données complètes de la ligne, donc un total de six accès IO sont nécessaires pour obtenir la page de données finale. Les index créés par

, tels que les index conjoints, les index uniques, etc., sont des index non clusterisés.

Index conjoint

L'index conjoint fait référence à l'indexation de plusieurs colonnes de la table. L'index conjoint est également un arbre B+. La différence est que le nombre de valeurs clés dans l'index conjoint n'est pas 1, mais supérieur ou égal à 2.

Par exemple, il existe une table utilisateur avec les champs id, age et name. On constate que les deux SQL suivants sont les plus fréquemment utilisés :

Select * from user where age = ？ ;
Select * from user where age = ? and name = ?;

À l'heure actuelle, il n'est pas nécessaire d'en créer deux distincts pour l'âge et le nom, il vous suffit de créer l'index conjoint suivant :

create index idx_age_name on user(age, name)

Un autre avantage de l'index conjoint est que la deuxième valeur clé a été triée. , ce qui peut parfois éviter une opération de tri supplémentaire.

Index de couverture

Index de couverture, c'est-à-dire que vous pouvez obtenir toutes les valeurs de champ requises pour la requête à partir de l'index auxiliaire sans interroger les enregistrements de l'index clusterisé. L'avantage de l'index de couverture est que l'index auxiliaire ne contient pas toutes les informations de l'enregistrement de ligne complet, sa taille est donc beaucoup plus petite que l'index clusterisé, ce qui permet de réduire un grand nombre d'opérations d'E/S.

Par exemple, s'il existe un index conjoint (âge, nom) ci-dessus, s'il est le suivant :

select age，name from user where age=？

Vous pouvez utiliser l'index couvrant.

Un autre avantage de la couverture des index concerne les problèmes statistiques, tels que :

select count(*) from user

Le moteur de stockage innodb ne choisit pas d'interroger l'index clusterisé pour obtenir des statistiques. Puisqu'il existe un index auxiliaire sur la table utilisateur et que l'index auxiliaire est beaucoup plus petit que l'index clusterisé, la sélection de l'index auxiliaire peut réduire les opérations d'E/S.

Notes

• Construisez uniquement des index appropriés, pas des index redondants

Parce que chaque fois que des données sont ajoutées ou supprimées, l'arborescence B+ doit être ajustée si. plusieurs index sont créés, plusieurs arbres B+ doivent être ajustés. Plus il y a d'arbres et plus la structure est grande, plus cet ajustement prend du temps et des ressources. Si ces index inutiles sont réduits, l'utilisation du disque peut être considérablement réduite.

• La longueur des données de la colonne d'index peut être aussi petite que possible.

Plus la longueur des données d'index est petite, plus d'index sont stockés dans chaque bloc et plus de valeurs peuvent être obtenues dans une IO.

• Le préfixe de colonne correspondant peut être utilisé dans l'index comme 9999%, comme %9999%, comme %9999 ne peut pas utiliser l'index
• Dans et ou dans le ; Où la condition peut utiliser l'index, pas dans et , les opérations ne peuvent pas utiliser d'index

S'il n'est pas dans ou , face à l'arborescence B+, le moteur ne sait pas lequel ; nœud à partir duquel commencer.

• Faire correspondre les valeurs de plage, trier par peut également être utilisé pour indexer
• Utilisez les requêtes de colonnes spécifiées plus souvent, ne renvoyez que les colonnes de données auxquelles vous pensez, utilisez select * less;

Il n'est pas nécessaire d'interroger les champs inutiles, et si vous n'utilisez pas *, vous pouvez accéder à l'index de couverture

• Si vous ne commencez pas la recherche en fonction de la colonne la plus à gauche de l'index dans l'index conjoint, vous ne pouvez pas utiliser l'index

Principe de correspondance le plus à gauche

• Dans le joint ; index, index peut être utilisé pour faire correspondre avec précision la colonne la plus à gauche et la plage correspond à une autre colonne
• S'il existe une requête de plage pour une certaine colonne dans la requête de l'index conjoint, toutes les colonnes à sa droite ne peuvent pas être indexé

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!