MySQL 8慢查询优化求助：42000ms查询语句优化方案咨询

问题根源分析 #

你的原SQL使用相关子查询，导致p_ranking表每一行数据都要单独查询一次p_summary表，再加上全表扫描+文件排序（filesort），这是耗时42秒的核心原因。从执行计划也能看出：p_ranking走了全表扫描（type: ALL），还触发了文件排序（Extra: Using filesort），这两个操作在万级数据量下会严重拖慢查询速度。

具体优化措施 #

1. 重构SQL：用JOIN替代相关子查询

相关子查询是逐行循环查询，换成JOIN后可以批量关联，大幅减少查询次数。根据你的需求（只保留p_summary中deleted=0的玩家），用INNER JOIN最适合：

SELECT p_ranking.id, p_ranking.atk + p_ranking.def AS s
FROM p_ranking
INNER JOIN p_summary 
  ON p_ranking.id = p_summary.id 
  AND p_summary.deleted = 0  -- 直接在关联条件里过滤，提前筛选数据
ORDER BY s DESC 
LIMIT 30;

如果存在p_ranking有数据但p_summary没有的玩家，原逻辑是排除这些（ifnull返回1，不满足=0），所以INNER JOIN正好符合需求，只会保留两边都存在且deleted=0的记录。

2. 创建针对性索引，消除全表扫描和filesort

索引是解决这类性能问题的关键，针对你的场景推荐两个索引：

• p_ranking表：函数索引（覆盖排序+查询）
  MySQL 8.0支持函数索引，可以直接基于atk+def创建排序索引，让排序操作直接走索引，避免filesort：

  CREATE INDEX idx_atk_def_desc ON p_ranking ((atk + def) DESC, id);
  

  这个索引包含了排序字段和需要返回的id，属于覆盖索引，查询时无需回表，效率最高。

• p_summary表：联合索引（可选，进一步优化关联）
  虽然p_summary的id是主键（已走eq_ref），但如果deleted字段经常作为过滤条件，可以创建联合索引让关联时直接获取deleted值，避免回表：

  CREATE INDEX idx_id_deleted ON p_summary (id, deleted);
  

  不过因为主键索引已经包含所有字段，这个索引的收益相对有限，可根据实际情况选择。

3. 其他辅助优化

• 确认atk和def字段是数值类型，避免隐式转换拖慢计算；
• 定期清理表碎片（OPTIMIZE TABLE），尤其是InnoDB引擎，碎片过多会影响索引效率；
• 检查MySQL配置，比如调整sort_buffer_size（排序缓冲区），不过优先通过索引解决filesort才是根本。

优化后效果预期 #

重构SQL+添加索引后，执行计划应该会显示p_ranking走索引扫描（type: range/ref），且不再出现Using filesort，查询耗时会降到毫秒级。

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