COUNT与GROUP BY导致MySQL查询阻塞变慢的原因排查求助

核心现象 #

• 带COUNT(*)的左连接查询耗时超30秒，卡在“Sending data”状态；无COUNT时聚合11万条数据耗时不到1秒
• 带GROUP BY tr.reference的查询同样出现性能骤降
• id和reference为表主键（默认带索引），添加新列到text_reference前一切正常

可能原因 #

1. 统计信息过期：添加新列后，表的统计信息未同步更新，MySQL优化器无法生成最优执行计划
2. 执行计划退化：优化器选择了低效的连接顺序或索引策略，比如放弃覆盖索引转而进行全表扫描
3. 主键索引碎片化：添加列操作可能导致主键索引产生碎片，影响查询时的索引遍历效率

具体解决步骤 #

1. 更新表统计信息 #

强制更新两张表的统计信息，让优化器获取最新的表数据分布：

ANALYZE TABLE text_reference, text_file;

2. 排查执行计划 #

用EXPLAIN查看慢查询的执行计划，确认索引使用是否正常：

EXPLAIN SELECT count(*) FROM `text_reference` tr LEFT JOIN `text_file` tf ON tf.`id_text_reference`=tr.`id`;
EXPLAIN SELECT * FROM `text_reference` tr LEFT JOIN `text_file` tf ON tf.`id_text_reference`=tr.`id` GROUP BY tr.`reference`;

• 重点关注type列，理想状态应为ref或eq_ref（高效连接类型）
• 查看Extra列，若出现Using filesort或Using temporary，会严重拖慢GROUP BY的执行速度

3. 优化COUNT查询 #

左连接场景下，COUNT(*)会统计所有匹配行（包括tf表无匹配的行），可改为统计主表主键，利用主键索引的覆盖特性：

SELECT COUNT(tr.id) FROM `text_reference` tr LEFT JOIN `text_file` tf ON tf.`id_text_reference`=tr.`id`;

如果业务仅需统计主表行数，左连接完全多余，直接用：

SELECT COUNT(*) FROM `text_reference`;

4. 优化GROUP BY查询 #

由于tr.reference是主键（唯一值），GROUP BY主键等价于直接查询主表（不会合并任何行）。如果业务仅需排序，用ORDER BY替代GROUP BY（主键本身有序，执行效率更高）：

SELECT tr.*, tf.* 
FROM `text_reference` tr 
LEFT JOIN `text_file` tf ON tf.`id_text_reference`=tr.`id` 
ORDER BY tr.reference;

若确实需要GROUP BY，确保text_file.id_text_reference有独立索引，或创建联合索引：

CREATE INDEX idx_tf_id_text_ref ON text_file(id_text_reference);

5. 修复索引碎片 #

添加列可能导致索引碎片，业务低峰期执行以下命令整理：

OPTIMIZE TABLE text_reference;

注意：该操作会锁表，需避开业务高峰。

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