Quelle est la différence entre la requête arrière de table MySQL et la couverture d'index ?

Requête de retour de table

Les index InnoDB sont divisés en deux catégories, l'une est un index clusterisé (index clusterisé) et l'autre est un index non clusterisé (index secondaire)

Index clusterisé (index clusterisé) : les nœuds feuilles stockent la ligne entière data, si vous trouvez l'index, vous trouverez les données. L'index est les données. L'ordre physique des lignes dans la table est le même que l'ordre logique (index) des valeurs clés. index clusterisé. Parce que l'index (répertoire) ne peut être trié que d'une seule manière.

Index non clusterisé (index ordinaire, index non clusterisé, index secondaire) : le nœud feuille btree de l'index non clusterisé stocke le PK (clé primaire) de la ligne de données actuelle. Par exemple, MYISAM met d'abord en cache l'index dans la mémoire via key_buffer Lorsque les données doivent être accessibles (données accessibles via l'index), l'index est directement recherché dans la mémoire, puis les données correspondantes sur le disque sont trouvées via l'index. . C'est pourquoi l'index n'atteint pas le tampon de clé. La raison de la vitesse lente.

Pourquoi les nœuds feuilles des structures d'index de clé non primaire stockent-ils les valeurs de clé primaire ?

Réduit le travail de maintenance de l'index secondaire en cas de mouvement de ligne ou de division de page de données (lorsque les données doivent être mises à jour, l'index secondaire n'a pas besoin d'être modifié, seul l'index clusterisé doit être modifié et une table ne peut avoir qu'un seul index clusterisé, les autres sont des index secondaires, il vous suffit donc de modifier l'index clusterisé, et il n'est pas nécessaire de reconstruire l'index secondaire)

Lors de l'utilisation d'un index non clusterisé, afin d'obtenir des données spécifiques , nous devons revenir à l'index clusterisé via la clé primaire et interroger les données. Rappelez ensuite la requête de table. L'arborescence d'index a été analysée deux fois. L'efficacité est donc relativement faible.

Couverture d'index

La couverture d'index est une solution aux requêtes de retour de table. Comme son nom l'indique, toutes les colonnes de la requête sont couvertes par les colonnes d'index utilisées (il peut s'agir d'un index monocolonne ou d'un index conjoint, généralement un index conjoint. Il est difficile pour un index monocolonne de couvrir toutes les colonnes. de la requête).

Étant donné que l'index contient déjà la valeur du champ à interroger, il suffit de renvoyer directement la valeur du champ dans l'index lors de l'interrogation. Il n'est pas nécessaire d'interroger dans la table, évitant ainsi la requête secondaire de l'index de clé primaire. , c'est-à-dire une efficacité améliorée des requêtes.

id est un index clusterisé, name est un index non clusterisé :

select name, age from t where name = 'lcc';

Vous devez interroger la table

Couverture de l'index :

Interrogez uniquement le champ de nom en SQL. De cette façon, l'index de nom couvre toutes les colonnes de requête.

select name  from t where name = 'lcc';

Modifiez l'index du nom en un index conjoint (nom, âge), puis exécutez select nom, âge à partir de t où nom = 'lcc'. Cela couvre également toutes les colonnes de requête.
Étant donné que les index de couverture doivent stocker les valeurs des colonnes d'index, tandis que les index de hachage, les index spatiaux et les index de texte intégral ne stockent pas les valeurs des colonnes d'index, seules les données utilisant les index B-Tree peuvent être utilisées comme index de couverture.

Lorsque vous effectuez une requête de couverture d'index, vous pouvez voir les informations [Utilisation de l'index] dans la colonne Extra de l'explication (plan d'exécution).

Avantages de la couverture d'index

• Les entrées d'index sont généralement beaucoup plus petites que la taille de la ligne de données, car la couverture de l'index nécessite uniquement la lecture de l'index, ce qui réduit considérablement la quantité d'accès aux données.

• L'index est stocké dans l'ordre des valeurs de colonne, et la recherche de plage intensive en IO sera beaucoup plus petite que l'IO de lecture aléatoire de chaque ligne de données du disque.

• Certains moteurs de stockage tels que MyISAM mettent uniquement en cache les index en mémoire, et les données dépendent du système d'exploitation pour être mises en cache. Par conséquent, l'accès aux données nécessite un appel système. L'utilisation d'index de couverture évite cela.

• Pour les tables de base de données sous le moteur InnoDB, la couverture des index est très pratique grâce à l'index clusterisé d'InnoDB. Étant donné que l'index secondaire d'InnoDB enregistre la valeur de clé primaire de la ligne dans le nœud feuille, si l'index secondaire peut couvrir la requête, il évite une requête secondaire de l'index de clé primaire.

Quels scénarios conviennent pour utiliser la couverture d'index pour optimiser SQL

• Lorsque la ligne entière d'enregistrements n'a pas besoin d'être interrogée

• Optimisation de la requête du nombre de tables complètes ;

• lesquels Ne créez pas d'index si la table contient trop peu d'enregistrements

Tableaux ou champs fréquemment ajoutés, supprimés ou modifiés (comme le solde utilisateur)

• Ne créez pas d'index pour les champs inutilisés dans la condition Where

• Filtrage Les personnes ayant une mauvaise orientation sexuelle ne sont pas adaptées à la création d'index (tels que le sexe)

• Index pushdown
  L'optimisation du pushdown d'index a été introduite dans MySQL 5.6 pendant le processus de parcours d'index. , les champs inclus dans l'index peuvent être jugés en premier et filtrés directement. Supprimez les enregistrements qui ne remplissent pas les conditions et réduisez le nombre de retours de table
• Créez un index conjoint :

  KEY `username` (`name`,`age`) )

  Exécution :

select * from user2 where name like 'j%' and age=99;

La requête SQL ci-dessus est conforme. avec le principe de préfixe le plus à gauche de l'index, donc l'index du nom d'utilisateur

celui ci-dessus en 5.5 sera utilisé Le flux d'exécution de SQL est le suivant :

Tout d'abord, la couche serveur MySQL appelle le moteur de stockage pour obtenir le premier nom d'utilisateur commençant par j.

Une fois que le moteur de stockage a trouvé le premier enregistrement de username=‘j’, l'identifiant de clé primaire est enregistré dans le nœud feuille de B+Tree. À ce moment, via l'opération de retour de table, l'enregistrement est trouvé dans. l'index de clé primaire. Complétez les données et retournez à la couche serveur.

Une fois que la couche serveur a obtenu les données, elle détermine si l'âge de l'enregistrement est de 99. Si age=99, elle renvoie l'enregistrement au client. Si age!=99, elle supprime l'enregistrement.

Le flux d'exécution du SQL ci-dessus dans 5.6 est le suivant :

• La couche serveur de MySQL appelle d'abord le moteur de stockage pour localiser le premier nom d'utilisateur commençant par j.

• Après avoir trouvé l'enregistrement, le moteur de stockage n'est pas pressé de revenir à la table, mais continue de déterminer si l'âge de cet enregistrement est égal à 99. Si age=99, il reviendra à la table. Si l'âge n'est pas égal à 99, le tableau ne sera pas renvoyé, continuez directement la lecture de l'enregistrement suivant.

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!