Explication détaillée de l'index mysql (résumé)

Dans "Analyse du processus d'exécution MySQL" ci-dessus, nous avons principalement introduit le processus d'exécution des instructions SQL au niveau de la couche serveur

Analysons-le Jetons à nouveau un coup d'œil aux étapes d'exécution d'instructions spécifiques au niveau de la couche moteur. Les opérations CRUD sont toutes liées aux index. Comprenons d'abord l'index

index

L'index. L'émergence de l'index est en fait à améliorer L'efficacité de la requête de données est comme la table des matières d'un livre

Structure des données

Les structures de données courantes incluent des tables de hachage, ordonnées tableaux et arbres de recherche

Une table de hachage est une structure qui stocke les données dans un format clé-valeur Tant que nous entrons la valeur à trouver, qui est la clé, nous pouvons trouver la valeur correspondante, qui. est la valeur. L'idée du hachage est très simple. Mettez la valeur dans le tableau, utilisez une fonction de hachage pour convertir la clé en position, puis placez la valeur dans la position correspondante du tableau

Inévitablement, plusieurs les valeurs clés passent lors de la conversion des fonctions de hachage, la même valeur apparaîtra. Une façon de gérer cette situation consiste à extraire une liste chaînée

Table de hachage. Cette structure convient aux scénarios où il n'y a que des requêtes de valeur égale

. Tableau ordonné dans Les performances dans les scénarios de requête équivalente et de requête par plage sont très bonnes

Si vous ne regardez que l'efficacité des requêtes, les tableaux ordonnés sont très bons. Cependant, cela devient gênant lorsque vous devez mettre à jour les données. Si vous insérez un enregistrement au milieu, vous devez déplacer tous les enregistrements suivants. Le coût est trop élevé.

L'index de tableau ordonné ne convient que. pour les moteurs de stockage statiques

Les caractéristiques de l'arbre de recherche binaire sont : le fils gauche de chaque nœud est plus petit que le nœud parent, et le nœud parent est plus petit que le fils droit

Bien sûr, afin de maintenir la complexité des requêtes O(log(N)), vous devez conserver l'arborescence binaire équilibrée. Afin de faire cette garantie, la complexité temporelle de la mise à jour est également O(log(N))

Les arbres binaires sont la recherche la plus efficace, mais en fait la plupart des stockages de bases de données n'utilisent pas d'arbres binaires. La raison en est que l'index existe non seulement en mémoire, mais est également écrit sur le disque

Pour qu'une requête lise le moins de disque possible, le processus de requête doit accéder au moins de blocs de données possible. Ensuite, il ne faut pas utiliser d'arbres binaires, mais des arbres "N-aires". Ici, le "N" dans l'arbre "N-aire" dépend de la taille du bloc de données

L'arbre N-aire a été largement utilisé dans les bases de données en raison de ses avantages en termes de performances en lecture et en écriture et en s'adaptant à modèles d'accès au disque. Le moteur est cassé

Modèle d'index d'InnoDB

Dans InnoDB, les tables sont stockées sous forme d'index en fonction de l'ordre des clés primaires dans ce stockage. La méthode est appelée table organisée d'index. InnoDB utilise le modèle d'index d'arbre B+, les données sont donc stockées dans l'arbre B+

Chaque index correspond à un arbre B+ dans InnoDB

Selon le contenu du nœud feuille, le type d'index est divisé en Les nœuds feuilles des index de clé primaire et des index de clé non primaire

stockent la ligne entière de données. Dans InnoDB, l'index de clé primaire est également appelé index clusterisé

Le contenu des nœuds feuilles de l'index de clé non primaire est la valeur de la clé primaire. Dans InnoDB, les index de clé non primaire sont également appelés index secondaires

Les requêtes basées sur des index de clé non primaire nécessitent d'analyser une arborescence d'index supplémentaire (retour de table). Par conséquent, nous devrions essayer d'utiliser la requête de clé primaire

Maintenance de l'index

Arbre B+ afin de maintenir l'ordre de l'index lors de l'insertion de nouvelles valeurs. Effectuer la maintenance nécessaire<.>

Si la valeur d'ID nouvellement insérée est plus petite que celle d'origine, cela sera relativement gênant. Vous devrez logiquement déplacer les données suivantes pour lui faire de la place

Et pire encore, si les données sont remplies. La page est pleine, selon l'algorithme de l'arbre B+, vous devez demander une nouvelle page de données, puis y déplacer certaines données. Ce processus est appelé fractionnement de page. Dans ce cas, les performances en pâtissent naturellement.

En plus des performances, les opérations de fractionnement de pages affectent également l'utilisation des pages de données. Les données qui étaient initialement placées sur une page sont désormais divisées en deux pages et l'utilisation globale de l'espace est réduite d'environ 50 %.

Bien sûr, il y aura des divisions et des fusions. Lorsque deux pages adjacentes sont faiblement utilisées en raison de données supprimées, les pages de données seront fusionnées. Le processus de fusion peut être considéré comme le processus inverse du processus de fractionnement

Le mode d'insertion des données de la clé primaire auto-croissante est conforme au scénario d'insertion incrémentielle que nous avons mentionné plus tôt. Chaque fois qu'un nouvel enregistrement est inséré, il s'agit d'une opération d'ajout. Elle n'implique pas le déplacement d'autres enregistrements et ne déclenche pas non plus la division des nœuds feuilles.

Lorsque des champs avec une logique métier sont utilisés comme clés primaires, il n'est souvent pas facile d'assurer une insertion ordonnée, donc le coût d'écriture des données est relativement élevé

Plus la clé primaire est petite longueur, les nœuds feuilles des index ordinaires Plus il est petit, plus l'espace occupé par les index ordinaires est petit

Ainsi, du point de vue des performances et de l'espace de stockage, l'auto-incrémentation de la clé primaire est souvent une choix plus raisonnable

Y a-t-il quelque chose. Existe-t-il des scénarios adaptés pour utiliser les champs métier directement comme clés primaires ? Par exemple, certaines exigences du scénario commercial sont les suivantes :

1 Il n'y a qu'un seul index

2.

Il s'agit d'un scénario KV typique

Indice de couverture

Si l'instruction exécutée est select ID from t, alors il vous suffit de vérifier la valeur de ID, et la valeur de ID est déjà dans l'arborescence d'index k, afin que les résultats de la requête puissent être fournis directement sans revenir à la table . En d'autres termes, dans cette requête, l'index k a « couvert » nos exigences de requête. Nous l'appelons un index de couverture

car l'index de couverture peut réduire le nombre de recherches dans l'arborescence et améliorer considérablement les performances des requêtes. , donc l'utilisation de l'index de couverture est une méthode courante d'optimisation des performances

Index pushdown

Lorsque le principe du préfixe le plus à gauche est satisfait, le préfixe le plus à gauche peut être utilisé. Localisez le enregistrement dans l'index. À ce stade, vous voudrez peut-être vous demander : qu'arrive-t-il aux parties qui ne correspondent pas au préfixe le plus à gauche ?

L'optimisation du pushdown d'index introduite dans MySQL 5.6 peut d'abord juger les champs inclus dans l'index pendant le processus de parcours d'index, filtrer directement les enregistrements qui ne remplissent pas les conditions et réduire le nombre de retours de table

Principe du préfixe le plus à gauche

Pas seulement la définition complète de l'index, tant que le préfixe le plus à gauche est respecté, l'index peut être utilisé pour accélérer la récupération

Lors de la construction d'un index commun, Comment organiser l'ordre des champs dans l'index ?

Notre critère d'évaluation ici est la réutilisabilité de l'index. Étant donné que le préfixe le plus à gauche peut être pris en charge, lorsqu'il existe déjà un index conjoint de (a, b), il n'est généralement pas nécessaire de créer un index distinct sur a. Par conséquent, le premier principe est que si un indice de moins peut être maintenu en ajustant l'ordre, alors cet ordre est souvent celui qui doit être priorisé

Indice de préfixe

Utiliser le plus Le principe du préfixe gauche permet de définir une partie d'une chaîne comme un index. Par défaut, si l'instruction avec laquelle vous créez l'index ne spécifie pas la longueur du préfixe, l'index contiendra la chaîne entière

. Cependant, la pénalité que cela entraîne est que cela peut augmenter le nombre d'analyses d'enregistrement supplémentaires car le. l'index est le même Besoin d'une comparaison plus approfondie

Utilisez l'index de préfixe et définissez la longueur, vous pouvez économiser de l'espace sans ajouter trop de coût de requête supplémentaire

Vous pouvez utiliser l'index statistique Comment de nombreuses valeurs différentes sont là pour déterminer combien de temps le préfixe doit être utilisé, réduisant ainsi le nombre d'analyses

L'impact de l'index du préfixe sur l'index de couverture

Utilisation index de préfixe Non seulement l'index de couverture optimise les performances des requêtes, c'est également un facteur que vous devez prendre en compte lorsque vous choisissez d'utiliser ou non un index de préfixe

Stockage inversé et stockage de hachage

Pour les champs tels que les boîtes aux lettres, l'utilisation d'index de préfixes peut bien fonctionner. Cependant, que devons-nous faire lorsque nous rencontrons une situation où la distinction des préfixes n'est pas assez bonne ?

La première façon consiste à utiliser le stockage par ordre inversé. Si vous stockez le numéro d'identification, stockez-le à l'envers

La deuxième façon consiste à utiliser le champ de hachage. Vous pouvez créer un autre champ entier sur la table pour enregistrer le code de vérification de la carte d'identité et créer un index sur ce champ

Tutoriel vidéo d'apprentissage gratuit recommandé : Tutoriel vidéo 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!