Analyse d'instance de plongée d'indexation pour l'optimisation des performances des requêtes MySQL

Commençons par une chose étrange :

Je vais créer des données pour reproduire le problème et créer une table utilisateur :

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Interroger les informations utilisateur de cet âge à travers un lot d'âges utilisateur, et vérifier le plan d'exécution SQL :

explain select * from user 
where age in (1,2,3,4,5,6,7,8,9);

Il y a 9 paramètres dans la condition Where. Concentrez-vous sur le nombre estimé de lignes analysées dans le plan d'exécution, qui est de 279 lignes.

Il n'y a pas de problème ici, l'estimation est très précise, il s'agit en réalité de 279 lignes.

Cependant, le problème se pose. Lorsque nous ajoutons un autre paramètre à la condition où, cela devient 10 paramètres. Le nombre estimé de lignes numérisées aurait dû augmenter, mais le résultat a été considérablement réduit.

explain select * from user
where age in (1,2,3,4,5,6,7,8,9,10);

Il a été soudainement réduit à 30 lignes, mais quel est le nombre réel de lignes ?

Le nombre réel est de 310 lignes et le nombre estimé de lignes numérisées est de 30 lignes. C'est vraiment une erreur de grand-mère.

Que se passe-t-il avec MySQL ? Peut-on encore l'estimer ?

Si vous ne pouvez pas le prédire, faites appel à quelqu'un d'autre !

Tout le monde a dû être confus, jusqu'à ce que j'aille sur le site officiel et que je voie un mot, Index dive.

En rapport avec ce mot, il existe également un paramètre de configuration eq_range_index_dive_limit.

MySQL5.7.3Avant la version, cette valeur par défaut était 10, et dans les versions ultérieures, cette valeur était par défaut 200.

Vous pouvez utiliser la commande pour vérifier la taille de cette valeur :

show variables like '%eq_range_index_dive_limit%';

Bien sûr, on peut aussi modifier manuellement la taille de cette valeur :

set eq_range_index_dive_limit=200;

Ceci eq_range_index_dive_limit Le rôle du La configuration est :

Quand où Lorsque le nombre de paramètres dans l'instruction en condition est inférieur à cette valeur, MySQL utilise index dive pour estimer le nombre de lignes analysées, ce qui est très précis.

Lorsque le nombre de paramètres dans la condition de l'instruction Where est supérieur ou égal à cette valeur, MySQL utilise une autre méthode Statistiques d'index (Statistiques d'index) pour estimer le nombre de lignes analysées, avec une erreur importante.

Pourquoi MySQL fait-il cela ?

Nous utilisons tous Index dive pour estimer le nombre de lignes numérisées, n'est-ce pas bien ?

En fait, cela est basé sur des considérations de coût. Plongée d'indexLe coût estimé est plus élevé et convient aux petits volumes de données. Statistiques d'indexLe coût estimé est faible et adapté aux gros volumes de données.

Généralement, l'état in de notre instruction Where n'a pas trop de paramètres, il convient donc d'utiliser index Diving pour estimer le nombre de lignes numérisées.

Il est recommandé aux étudiants qui utilisent encore la version précédente de MySQL5.7.3 de modifier manuellement les paramètres de configuration de index Diving aux valeurs appropriées.

Si la condition in dans votre projet comporte jusqu'à 500 paramètres, modifiez le paramètre de configuration en 501.

De cette façon, MySQL estime le nombre de lignes analysées avec plus de précision et peut sélectionner un index plus approprié.

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!