Quel index MySQL est le plus efficace pour les requêtes par plage : en tête avec une colonne de cardinalité élevée ou faible ?

Optimisation d'index pour les requêtes de plage

Les colonnes avec une cardinalité plus élevée contribuent à des index plus efficaces dans MySQL. Cependant, dans le cas de requêtes par plage, une exception s'applique.

Énoncé du problème

Considérons un tableau avec la structure suivante :

CREATE TABLE `files` (
  `did` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `filename` VARBINARY(200) NOT NULL,
  `ext` VARBINARY(5) DEFAULT NULL,
  `fsize` DOUBLE DEFAULT NULL,
  `filetime` DATETIME DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- Option 1
  KEY `ef` (`ext`,`filetime`)           -- Option 2
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Les durées de fichier sont distinctes, alors qu'il existe un nombre limité de valeurs ext (c'est-à-dire une cardinalité plus élevée pour la durée du fichier, une cardinalité inférieure pour ext). Une requête implique les deux colonnes avec les conditions suivantes :

WHERE ext = '...'
AND filetime BETWEEN ... AND ...

Quel index, fe ou ef, est le plus optimal ?

Réponse

Étonnamment , l'index avec ext comme première colonne, malgré sa cardinalité plus faible, est plus efficace pour cela requête.

Explication

L'optimiseur de MySQL analyse les alternatives d'index et choisit celle avec le coût le plus bas. En utilisant la trace de l'optimiseur, nous pouvons observer le raisonnement derrière ce choix.

Pour fe (filetime en premier), MySQL estime qu'il lui faudrait analyser 16684 lignes pour trouver les fichiers 'gif', même avec la condition de plage activée. filetime.

Pour ef (ext first), cependant, il estime qu'il peut utiliser les deux colonnes d'index et accéder rapidement aux lignes appropriées, ce qui entraîne un coût de seulement 646.61. MySQL choisit cet index car il peut utiliser plus d'éléments clés, ce qui rend la recherche plus efficace.

Conclusions

• Donner la priorité aux colonnes impliquées dans les tests d'égalité dans l'index, quelle que soit la cardinalité.
• Le plan de requête ne s'étendra pas au-delà de la première colonne « plage ».
• Dans ce Dans le contexte, la cardinalité n'est pas pertinente pour les index composites et les requêtes de plage.
• InnoDB peut utiliser des colonnes d'index au-delà de celles utilisées pour le filtrage ("Utilisation de la condition d'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!