使用created_at替代updated_at时MySQL查询超时问题排查

可能的核心原因 #

1. 数据分布差异导致结果集大小悬殊 #

同一个user_id=1234下，created_at落在2020年区间的记录数，可能远多于updated_at对应的记录数。比如该用户在2020年创建了上万条订单，但仅更新了少量订单：

• 用updated_at过滤时，结果集很小，即使走主键索引（按id排序），扫描和排序的开销都极低，所以执行很快。
• 用created_at过滤时，需要扫描的行数暴增，主键索引是按id顺序存储的，而2020年创建的订单id可能分散在整个表中，MySQL需要扫描大量离散的数据页，再对大结果集做排序，直接触发超时。

2. 联合索引回表开销过高 #

你创建的(user_id, created_at)联合索引，虽然能快速过滤出符合条件的记录，但因为查询是select *，MySQL需要通过索引中的主键值回表获取其他字段（即书签查找）：

• 如果created_at过滤后的结果集很大，回表操作会产生大量随机IO，比走主键索引的顺序扫描+排序开销还高，所以强制使用索引也会超时。
• 而(user_id, updated_at)对应的结果集小，回表的IO开销可接受，所以之前可能没暴露问题。

3. 统计信息过时导致优化器判断错误 #

MySQL优化器依赖表的统计信息估算结果集大小，如果created_at字段的统计信息未更新，优化器可能错误判断结果集规模，即使你强制指定索引，实际执行时因为数据量远超预期，也会触发超时。

4. 排序阶段的开销差异 #

两个查询都有order by id，但：

• updated_at过滤后的订单id可能相对集中（比如用户在2020年更新的订单，id大多在某个连续区间），排序时可以直接利用主键索引的有序性，甚至不需要额外排序。
• created_at过滤后的订单id可能分散在整个表中（比如用户后续又创建了很多非2020年的订单），MySQL需要对大量离散的id做排序，当结果集超过sort_buffer_size时，会触发磁盘排序，速度骤降。

排查与解决建议 #

• 验证数据量差异：执行以下语句对比两个条件的结果行数：

  SELECT COUNT(*) FROM orders WHERE user_id = 1234 AND updated_at BETWEEN '2020-01-01' AND '2020-12-31';
  SELECT COUNT(*) FROM orders WHERE user_id = 1234 AND created_at BETWEEN '2020-01-01' AND '2020-12-31';
  

• 查看执行计划：用EXPLAIN分析两个查询，重点看rows（预估扫描行数）、Extra（是否出现Using filesort等关键字）：

  EXPLAIN SELECT * FROM orders WHERE user_id = 1234 AND created_at BETWEEN '2020-01-01' AND '2020-12-31' ORDER BY id;
  

• 优化联合索引：如果业务允许，创建覆盖索引减少回表开销，比如：

  -- 如果只需要特定字段，替换为实际需要的字段列表
  CREATE INDEX idx_user_created_id ON orders(user_id, created_at, id);
  -- 若必须select *，可考虑将常用字段加入索引（不建议全字段，避免索引过大）
  

• 更新统计信息：执行ANALYZE TABLE orders;让优化器获取最新的数据分布情况，以便选择更优的执行计划。

内容的提问来源于stack exchange，提问作者New To Blue