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(默认值)。我知道我可以强制使用索引而不是聚集索引，但我想知道为什么 MySQL 会这样决定。

我应该尝试去理解吗？或者有时无法理解查询规划器的决策？

额外细节

• 表大小:11GB
• 行数: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 的后续版本(您运行的是 pre-GA 版本!)和较新的版本(5.7、8.0)中，处理 LIMIT 查询的方式有了一些改进。我建议您尝试升级到更高版本，看看这是否仍然是一个问题。

一般来说，如果您想了解查询规划器的决策，您应该查看查询的优化器跟踪。