MySQL n'utilise pas d'index avec la clause WHERE IN?

Question

Je suis en train d'optimiser certaines des requêtes de base de données dans mon application Rails, et j'en ai plusieurs qui me font perplexe. Ils sont tous à l'aide d'un DANS la dans la clause where et sont tous de faire des analyses de tables complètes, même si un indice approprié semble être en place.

Par exemple:

SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))

effectue un full table scan et d'EXPLIQUER, dit:

select_type: simple
type: all
extra: using where
possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column)
key: (none)
key_length: (none)
ref: (none)
rows: 208

Sont des index non utilisé lorsqu'une instruction est utilisée ou dois-je besoin de faire quelque chose différemment? Les requêtes sont générées par les Rails afin que je puisse revoir la façon dont mes relations sont définies, mais je pensais commencer avec un potentiel de bugs au niveau de DB en premier.

Merci à l'avance.

Answer 1

Voir Comment MySQL Utilise Les Index.

Aussi valider si MySQL effectue toujours un full table scan après vous ajoutez un supplément de 2000-ou-si les lignes de votre user_metrics table. À de petites tables, accès par index est en fait plus chères (I/O-sage) de l'analyse d'une table, et MySQL optimiseur peut prendre cela en compte.

Contrairement à mon précédent post, il s'avère que MySQL est également à l'aide d'un optimiseur basé sur les coûts, ce qui est une très bonne nouvelle, à condition que vous exécutez votre ANALYZE au moins une fois quand vous croyez que le volume de données dans votre base de données est représentative de l'avenir de la journée-à-jour d'utilisation.

Lorsque vous traitez avec des coûts basée sur les optimiseurs (Oracle, Postgres, etc.), vous devez assurez-vous d'exécuter périodiquement ANALYZE sur vos différents tableaux que leur taille augmente de plus de 10 à 15%. (Postgres le fera automatiquement pour vous, par défaut, tandis que d'autres Sgbdr va laisser cette responsabilité à un DBA, c'est à dire vous). Grâce à l'analyse statistique, ANALYZE aidera l'optimiseur de se faire une meilleure idée de la façon dont beaucoup d'I/O (et d'autres ressources associées, telles que le PROCESSEUR, nécessaire par exemple pour le tri) seront impliqués lors du choix entre les différents candidats des plans d'exécution. Incapacité à exécuter ANALYZE peut entraîner dans de très mauvaises, parfois désastreuses décisions de planification (p. ex. ordre de la milliseconde requêtes de prendre, parfois, heures à cause de la mauvaise boucles imbriquées sur JOINs).

Si la performance n'est toujours pas satisfaisante après l'exécution de l' ANALYZE, puis vous sera généralement en mesure de contourner le problème par l'utilisation d'indicateurs, par exemple, FORCE INDEX, alors que dans d'autres cas, vous pourriez avoir trébuché sur un bug MySQL (par exemple, cette ancienne, ce qui pourrait avoir mordu vous étiez-vous d'utiliser les Rails' nested_set).

Maintenant, puisque vous êtes dans une application Rails, il sera difficile (et à l'encontre du but de l' ActiveRecord) pour émettre vos requêtes personnalisées avec des notes au lieu de continuer à utiliser l' ActiveRecord-générés.

Je l'avais mentionné que dans notre application Rails tous SELECT des requêtes a chuté en dessous de 100ms après le passage à Postgres, alors que certains des jointures complexes générés par ActiveRecord , à l'occasion, prendre comme beaucoup de comme 15 ans ou plus avec MySQL 5.1 à cause de boucles imbriquées à l'intérieur des analyses de la table, même lorsque les indices étaient disponibles. Aucune optimiseur est parfait et vous devez être conscient des options. D'autres éventuels problèmes de performances d'être conscient de, outre l'optimisation de plan de requête, sont de verrouillage. C'est en dehors de la portée de votre problème.

Answer 2

Essayez de forcer cet index:

 SELECT `user_metrics`.*
FROM `user_metrics` FORCE INDEX (index_user_metrics_on_user_id)
WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))
 

Je viens de vérifier, il utilise un index sur exactement la même requête:

 EXPLAIN EXTENDED
SELECT * FROM tests WHERE (test IN ('test 1', 'test 2', 'test 3', 'test 4', 'test 5', 'test 6', 'test 7', 'test 8', 'test 9'))

1, 'SIMPLE', 'tests', 'range', 'ix_test', 'ix_test', '602', '', 9, 100.00, 'Using where'
 

Answer 3

Parfois, MySQL n'utilise pas d'index, même s'il en existe un. Cela se produit lorsque l'optimiseur estime que l'utilisation de l'index nécessiterait que MySQL accède à un très grand pourcentage des lignes de la table. (Dans ce cas, une analyse de table sera probablement beaucoup plus rapide car elle nécessite moins de recherches.)

Quel pourcentage de lignes correspond à votre clause IN?

Answer 4

Est-ce que cela ira mieux si vous supprimez les crochets redondants autour de la clause where?

Même si vous n’avez que 200 lignes environ, il se peut qu’un contrôle de table soit plus rapide. Essayez avec une table avec plus d'enregistrements.