Principe de mise en œuvre de l'index Mysql

Discussion sur l'index MySQL

Dans MySQL, l'index est un concept au niveau du moteur de stockage. Différents moteurs de stockage implémentent les index de différentes manières. Cet article traite principalement de l'implémentation des index des deux moteurs de stockage MyISAM et InnoDB. .

Implémentation de l'index MyISAM

Le moteur MyISAM utilise B Tree comme structure d'index et le champ de données du nœud feuille stocke l'adresse de l'enregistrement de données. La figure suivante est le diagramme schématique de l'index MyISAM :

Supposons que la table comporte trois colonnes au total. En supposant que nous utilisons Col1 comme clé primaire, la figure 8 est. l'index primaire d'une table MyISAM (clé primaire) l'indique. On peut voir que le fichier d'index de MyISAM enregistre uniquement l'adresse de l'enregistrement de données. Dans MyISAM, il n'y a pas de différence structurelle entre l'index primaire et l'index secondaire (clé secondaire), sauf que l'index primaire nécessite que la clé soit unique, tandis que la clé de l'index secondaire peut être répétée. Si nous créons un index auxiliaire sur Col2, la structure de cet index est la suivante :

est également un B Tree, et le champ de données stocke l'adresse des données enregistrer. Par conséquent, l'algorithme de récupération d'index dans MyISAM consiste à rechercher d'abord l'index selon l'algorithme de recherche B Tree. Si la clé spécifiée existe, la valeur de son champ de données est supprimée, puis la valeur du champ de données est utilisée comme valeur. adresse pour lire l'enregistrement de données correspondant.

La méthode d'indexation de MyISAM est également appelée "non-cluster". La raison pour laquelle elle est appelée ainsi est pour la distinguer de l'index clusterisé d'InnoDB.

Implémentation de l'index InnoDB

Bien qu'InnoDB utilise également B Tree comme structure d'index, la méthode d'implémentation spécifique est complètement différente de MyISAM.

La première grande différence est que les fichiers de données d'InnoDB eux-mêmes sont des fichiers d'index. Comme nous le savons d'après ce qui précède, le fichier d'index MyISAM et le fichier de données sont séparés et le fichier d'index enregistre uniquement l'adresse de l'enregistrement de données. Dans InnoDB, le fichier de données de la table lui-même est une structure d'index organisée par B Tree. Le champ de données du nœud feuille de cet arbre enregistre des enregistrements de données complets. La clé de cet index est la clé primaire de la table de données, donc le fichier de données de la table InnoDB lui-même est l'index primaire.

La figure 10 est un diagramme schématique de l'index principal d'InnoDB (également un fichier de données). Vous pouvez voir que les nœuds feuilles contiennent des enregistrements de données complets. Ce type d'index est appelé index clusterisé. Étant donné que les fichiers de données d'InnoDB eux-mêmes sont agrégés par clé primaire, InnoDB exige que la table ait une clé primaire (MyISAM peut ne pas en avoir une). Si elle n'est pas explicitement spécifiée, le système MySQL sélectionnera automatiquement une colonne pouvant identifier les données de manière unique. record comme clé primaire. Si elle n'existe pas, Pour ce type de colonne, MySQL génère automatiquement un champ implicite comme clé primaire pour la table InnoDB. La longueur de ce champ est de 6 octets et le type est long.

La deuxième différence par rapport à l'index MyISAM est que le champ de données d'index auxiliaire d'InnoDB stocke la valeur de la clé primaire de l'enregistrement correspondant au lieu de l'adresse. En d'autres termes, tous les index secondaires d'InnoDB font référence à la clé primaire comme champ de données. Par exemple, la figure 11 est un index auxiliaire défini sur Col3 :

Ici, le code ASCII des caractères anglais est utilisé comme critère de comparaison. L'implémentation de l'index clusterisé rend la recherche par clé primaire très efficace, mais la recherche par index auxiliaire nécessite de récupérer l'index deux fois : d'abord, récupérer l'index auxiliaire pour obtenir la clé primaire, puis utiliser la clé primaire pour récupérer les enregistrements dans l'index primaire.

Comprendre l'implémentation des index des différents moteurs de stockage est très utile pour l'utilisation correcte et l'optimisation des index. Par exemple, après avoir connu l'implémentation des index d'InnoDB, il est facile de comprendre pourquoi il n'est pas recommandé d'utiliser des champs trop longs. clés primaires, car tous les index auxiliaires font tous référence à l'index primaire. Un index primaire long rendra l'index auxiliaire trop grand. Pour un autre exemple, utiliser des champs non monotones comme clés primaires n'est pas une bonne idée dans InnoDB car le fichier de données InnoDB lui-même est un arbre B. Les clés primaires non monotones entraîneront une mise à jour fréquente du fichier de données lors de l'insertion de nouveaux enregistrements dans l'ordre. pour conserver les caractéristiques de l'arbre B. L'ajustement de division est très inefficace et l'utilisation d'un champ à incrémentation automatique comme clé primaire est un bon choix.

Ce qui précède est le contenu du principe d'implémentation de l'index Mysql. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !