Opérateur MySQL 'IN' pour gérer de grands nombres

Question

J'observe un comportement étrange que j'essaie de comprendre.

Version MySQL : 5.7.33 J'ai la requête suivante :

select * from a_table where time>='2022-05-10' and guid in (102,512,11,35,623,6,21,673);

Index sur

a_table 在 time、guid 上有主键，在 guid

La requête que j'ai écrite ci-dessus a de très bonnes performances et selon le plan d'explication, c'est using index condition;使用地点；使用MRR

Lorsque j'augmente le nombre de valeurs dans la clause in, les performances sont considérablement affectées.

Après quelques exercices, j'ai obtenu un chiffre approximatif. Pour les valeurs inférieures à ~14 500, le schéma d'interprétation est le même que ci-dessus. Pour les quantités supérieures, seul le plan est expliqué 使用 where et cela prend une éternité pour exécuter ma requête.

En d'autres termes, si je mets 14 000 valeurs dans la clause in 子句中放入 14,000 个值，则解释计划将具有预期的 14,000 行。但是，如果我在 in, par exemple, le plan d'explication aurait les 14 000 lignes attendues. Cependant, si je mets 15 000 valeurs dans la clause

, l'explication comporte 221200324 lignes. Je n'ai même pas autant de lignes dans toute ma table.

J'essaie de comprendre ce comportement et de savoir s'il existe un moyen de résoudre ce problème.

Merci🎜

Answer 1

Comprenez limitation de la mémoire pour l'optimisation de la portée.

Lorsque IN() il y a un grand nombre de valeurs dans le prédicat, il utilise plus de mémoire lors de l'étape d'optimisation de la requête. Cela a été considéré comme un problème dans certains cas, c'est pourquoi les versions récentes de MySQL fixent une limite de mémoire maximale (la valeur par défaut est de 8 Mo).

Si l'optimiseur constate qu'il nécessite plus de mémoire que la limite, il n'y a aucune autre condition dans la requête à optimiser, il abandonne toute tentative d'optimisation et recourt à une analyse de table. J'en déduis que les statistiques de votre table montrent en réalité que la table comporte environ 221 millions de lignes (bien que les statistiques de la table soient une estimation inexacte).

Je ne peux pas dire que je connais la formule exacte de la quantité de mémoire requise par une liste de valeurs donnée, mais sur la base du comportement que vous avez observé, nous pouvons deviner que si l'on considère 14 000 éléments, une moyenne d'environ 600 octets par élément est efficace , mais plus ne l'est pas.

Vous pouvez définir range_optimizer_max_mem_size = 0 pour désactiver les limites de mémoire. Cela crée le risque de surcharger la mémoire, mais cela évite à l'optimiseur "d'abandonner". Nous avons défini cette valeur sur toutes les instances MySQL lors de notre travail précédent, car nous ne pouvions pas former les développeurs à éviter de créer d'énormes listes de valeurs dans leurs requêtes.