无ORDER BY/GROUP BY的MySQL查询出现filesort及指定查询调优求助

我来帮你拆解这两个MySQL性能问题，一步步解决：

一、无ORDER BY/GROUP BY却触发Using filesort/Using temporary的异常排查 #

你遇到的这种情况其实不算罕见，MySQL有时会因为隐式操作触发这两个状态，不一定需要显式的ORDER BY/GROUP BY：

• 可能诱因：
  • 使用DISTINCT：MySQL为了去重，可能会隐式排序生成临时表
  • UNION操作：默认会去重并排序结果集（换成UNION ALL可以避免，如果你不需要去重）
  • 某些聚合函数（如MAX()/MIN()）：当没有合适索引时，MySQL会扫描全表并临时存储数据来找极值
  • 关联查询中，MySQL需要临时存储中间结果来完成关联逻辑
• 排查&解决步骤：
  1. 用EXPLAIN查看完整执行计划，定位到底是哪个表、哪个操作触发了这两个状态
  2. 检查是否有可以替换的语法：比如把UNION改成UNION ALL，去掉不必要的DISTINCT
  3. 给查询中用到的过滤、关联字段添加合适的索引，减少MySQL需要临时处理的数据量

二、指定查询的调优方案 #

先把你的原查询和给出的执行计划整理清楚：

原查询 #

SELECT jsondata_c, id_c 
FROM accounts 
LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c 
WHERE ispropagated_c = 0 AND deleted=0 AND jsondata_c is not null 
ORDER BY accounts.date_modified DESC 
LIMIT 5

部分执行计划 #

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: accounts_cstm
partitions: NULL
type: ALL
possible_keys: PRIMARY,id_c
key: NULL
key_len: NULL
ref: NULL
rows: 1325982
filtered: ...

从执行计划能明显看到问题：accounts_cstm表做了全表扫描（type: ALL），而且没用到任何索引，这是性能瓶颈的核心。下面是具体调优步骤：

1. 针对性创建复合索引

索引是解决这类问题的关键，我们需要创建覆盖过滤、关联、排序场景的索引：

• 给accounts_cstm创建覆盖WHERE条件和关联字段的索引：

  CREATE INDEX idx_cstm_ispropagated_deleted_jsondata_id ON accounts_cstm (ispropagated_c, deleted, jsondata_c, id_c);
  

  这个索引包含了所有WHERE过滤条件，同时把关联字段id_c也加进去，MySQL可以直接从索引中拿到需要的数据，不用回表查询原数据。
• 给accounts创建关联+排序字段的索引：

  CREATE INDEX idx_accounts_id_datemodified ON accounts (id, date_modified DESC);
  

  因为是LEFT JOIN，accounts.id是关联键，加上date_modified DESC可以直接用索引完成排序，避免触发Using filesort。

2. 优化查询逻辑（可选但推荐）

你当前用的是LEFT JOIN，但WHERE条件里用到了accounts_cstm的字段（ispropagated_c=0、jsondata_c IS NOT NULL），这其实会自动把LEFT JOIN转换成INNER JOIN（因为这些条件会过滤掉accounts_cstm中没有匹配的记录）。如果业务逻辑允许，直接改成INNER JOIN能让MySQL优化器更高效地选择关联顺序：

SELECT ac.jsondata_c, ac.id_c 
FROM accounts a
INNER JOIN accounts_cstm ac ON a.id = ac.id_c 
WHERE ac.ispropagated_c = 0 AND a.deleted=0 AND ac.jsondata_c IS NOT NULL 
ORDER BY a.date_modified DESC 
LIMIT 5

3. 验证优化效果

执行EXPLAIN查看优化后的执行计划，确认：

• accounts_cstm的type变成ref或range（不再是全表扫描），并且用到了我们创建的索引
• Extra字段里不再出现Using temporary和Using filesort（或者filesort被消除）

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