为何特定用户下MySQL查询使用LIMIT 1-6时会触发超时？

问题根因 #

按timestamp类型字段排序本身没有特殊要求，你遇到的现象是MySQL优化器索引选择逻辑 + 特定用户数据分布倾斜共同导致的，和timestamp类型本身的排序规则无关。

现象解释 #

你遇到的LIMIT 1~6超时、更大LIMIT取值查询正常的表现，对应两种完全不同的执行计划：

• 当LIMIT取值较小时（你这里是≤6），优化器会判定「走timestamp_col的单列倒序索引，逐行回表过滤user='123'的条件，凑够指定行数就返回」的成本更低，因此选择该方案。
• 当LIMIT取值变大时（≥7），优化器会判定上述方案需要扫描的行数过多，转而选择「先走user字段的索引捞出所有符合user='123'的行，再在内存中排序取前N行」的方案。

仅该用户出现问题的原因：
你给出的示例中该用户仅有8条数据，走第二种执行计划时，对8条数据排序的成本可以忽略，查询速度极快。但第一种执行计划的性能取决于：要凑够N条user='123'的行，需要在timestamp_col的全局倒序索引里扫描多少条记录。如果该用户的最新记录在全表的时间排序里非常靠后，可能需要扫描几十万甚至上百万条记录、回表上百万次才能找到第一条符合条件的行，直接触发超时。
其他用户要么最新数据在全局时间排序里位置靠前，要么数据量分布让优化器不会选择错误的执行计划，因此无法复现问题。

验证方法 #

执行以下两条语句对比执行计划，即可验证上述判断：

-- 查看LIMIT 1的执行计划
EXPLAIN SELECT * FROM table_name tn WHERE tn.user = '123' ORDER BY timestamp_col DESC LIMIT 1;
-- 查看LIMIT 7的执行计划
EXPLAIN SELECT * FROM table_name tn WHERE tn.user = '123' ORDER BY timestamp_col DESC LIMIT 7;

如果上述判断正确，两条执行计划的key列会显示使用了不同的索引：LIMIT 1时用到timestamp_col的单列索引，LIMIT 7时用到user字段的单列索引。

解决方案 #

• 最优方案：创建联合索引 (user, timestamp_col DESC)。这个索引可以直接按user过滤出符合条件的行，同时行已经按timestamp_col倒序排列，不需要回表也不需要额外排序，不管LIMIT取什么值都能在毫秒级返回。
• 临时规避方案（无法新建索引时）：可以通过子查询包裹的方式干扰优化器的判断，强制走先过滤user再排序的逻辑，示例如下：

SELECT * FROM (
  SELECT * FROM table_name tn WHERE tn.user = '123'
) t ORDER BY timestamp_col DESC LIMIT 1

也可以使用FORCE INDEX语法强制查询走user字段的索引。

内容的提问来源于stack exchange，提问作者Adam