MySQL:为什么in子句中的第5个ID会彻底改变查询计划？

Question

考虑到以下两个问题：

查询#1

SELECT log.id
FROM log
WHERE user_id IN
      (188858, 188886, 189854, 203623, 204072)
      and type in (14, 15, 17)
ORDER BY log.id DESC
LIMIT 25 OFFSET 0;

查询#2-4 ID而不是5个

SELECT log.id
FROM log
WHERE user_id IN
      (188858, 188886, 189854, 203623)
      and type in (14, 15, 17)
ORDER BY log.id DESC
LIMIT 25 OFFSET 0;

解释计划

-- Query #1
1   SIMPLE  log range   idx_user_id_and_log_id  idx_user_id_and_log_id  4       41280   Using index condition; Using where; Using filesort
-- Query #2
1   SIMPLE  log index   idx_user_id_and_log_id  PRIMARY                 4       53534   Using where

为什么添加一个ID会使执行计划如此不同？我说的是毫秒到1分钟的时间差。我认为它可能与eq_range_index_dive_limit参数相关，但它无论如何都是10 (缺省值)。我知道我可以强制使用索引而不是clustered index，但我想知道为什么MySQL会这么做。

我应该试着理解这一点吗？或者有时不可能理解查询计划者的决策？

附加细节

• 表号:11
• 行数:1.08亿
• MySQL: 5.6.7
• 从IN子句中删除哪个ID并不重要。
• 索引：idx_user_id_and_log_id(user_id, id)

Answer 1

如您所示，MySQL有两个用于使用ORDER BY ... LIMIT n查询的替代查询计划。

1. 读取所有符合条件的行，对它们进行排序，并选择n行。
2. 按排序顺序读取行，并在找到n个限定行时停止。

为了决定哪个选项更好，优化器需要估计WHERE条件的过滤效果。这不是直截了当的，特别是对于没有索引的列，或者值相关的列。在您的示例中，您可能需要按排序顺序读取更多的表，以便找到前25行符合条件的行，而不是优化器所期望的。

在处理限制查询的方法上已经有了一些改进，在以后版本5.6中(在GA前版本上运行！)和在新版本(5.7、8.0)中都有改进。我建议您尝试升级到稍后的版本，看看这是否仍然是一个问题。

通常，如果您想了解查询规划器的决策，应该查看查询的优化器跟踪。

Answer 2

加入是更有效的。

使用IN运算符的值创建一个临时表。然后，在表'log‘与临时值表之间进行连接。

有关更多信息，请参考this answer 。

Answer 3

添加

INDEX(user_id, type, id),
INDEX(type, user_id, id)

每一个都是一个“覆盖”指数。因此，可以通过只查看一个索引来执行整个查询，而不触及“数据”。

我对优化器有两个选择--希望它能够选择user_id IN (...)是更有选择性的还是type IN (...)来选择更好的索引。

如果在添加了这些之后，您对idx_user_id_and_log_id(user_id, id)没有任何用处，那么就使用DROP吧。

(不，我无法解释为什么查询2选择进行表扫描。)