CClara轻量论坛系统

MySQL 慢查询优化:Explain 执行计划实战解析

x123456
x123456 见习用户见习用户
发布于 2026-11-17 19:38 ·13 浏览 ·6 回复
内容摘要

通过慢日志或后台慢查询榜单定位出单次慢且调用频繁的高价值 SQL,再用 EXPLAIN 重点观察 type、key、rows、Extra 四列,判断是否存在全表扫描、索引未命中、filesort 与临时表等开销,随后按等值条件在前、范围或排序在后的顺序建立联合索引,并用延迟关联改写深分页查询。

学完这篇,你能拿着一条真实的慢 SQL,一步步用 EXPLAIN 看出它为什么慢、慢在哪、该怎么加索引改语句,而不是凭感觉试。

第一步:先把慢 SQL 捞出来

优化不能靠猜,得有靶子。两条路:

  • 开慢日志:slow_query_log=ON,long_query_time=1(超过 1 秒记一条),日志位置看 slow_query_log_file。
  • 如果你跑的是 Clara BBS,后台「系统工具 → 容量监控」插件里直接有 MySQL 慢查询 TOP 榜(前提是慢日志已开启),省得自己翻文件。

拿到耗时最长、调用次数最多的那条语句,复制出来,进入下一步。

注意:优先优化「单次慢 + 调用频繁」的语句,一条 0.3 秒但每分钟跑 500 次的 SQL,比一条 3 秒但一天跑一次的价值大得多。

第二步:在语句前面加 EXPLAIN

EXPLAIN SELECT id,title,created_at FROM bbs_threads
WHERE forum_id = 12 AND status = 1
ORDER BY created_at DESC LIMIT 20;

结果是一张表,别被列数吓到,只盯四列:type、key、rows、Extra。

第三步:看 type,判断扫描方式

从好到坏大致是:

system > const > eq_ref > ref > range > index > ALL

  • const / eq_ref:走主键或唯一索引,一两条,理想状态。
  • ref:普通索引等值匹配,正常。
  • range:索引范围扫描,BETWEEN、>、IN 常见,可接受。
  • index:扫整个索引树,比全表快一点但数据量大照样慢。
  • ALL:全表扫描,看到它基本就是问题所在。

第四步:读 Extra,找隐藏成本

  • Using filesort:排序没走索引,MySQL 把结果拉出来再排。数据量大时非常慢。
  • Using temporary:用了临时表,常见于 GROUP BY、DISTINCT 组合不当。
  • Using index:覆盖索引,查询的列全在索引里,不用回表,这是好事。
  • Using where 单独出现:索引定位后又做了过滤,说明索引没吃满条件。

再看 rows 列——预估扫描行数。如果 rows 是 80 万而你只要 20 条,优化空间就摆在那儿。

第五步:实战改写(论坛版块页例子)

原始语句走 type=ALL + Using filesort,因为 forum_id 没索引,排序也用不上索引。

建联合索引,顺序按「等值条件在前、范围/排序在后」:

ALTER TABLE bbs_threads ADD INDEX idx_forum_status_time (forum_id, status, created_at);

再跑一次 EXPLAIN,期望看到 type=ref、key=idx_forum_status_time、Extra 里的 Using filesort 消失(因为 created_at 是有序的索引列,直接顺着索引取就行)。

如果是分页很深的场景:

-- 慢:先扫 10 万行再丢掉
SELECT * FROM bbs_threads ORDER BY id DESC LIMIT 100000, 20;
-- 快:延迟关联,先用索引定位 id
SELECT t.* FROM bbs_threads t
JOIN (SELECT id FROM bbs_threads ORDER BY id DESC LIMIT 100000, 20) x USING(id);

注意:这几种情况索引会直接失效——列上套函数(WHERE DATE(created_at)=...)、隐式类型转换(varchar 列传数字)、前导模糊 LIKE '%关键词%'、OR 连接的列有的没索引。改写语句往往比硬加索引更有效。

第六步:验证,别只看 EXPLAIN

EXPLAIN 给的是预估。MySQL 8.0.18+ 用 EXPLAIN ANALYZE 看实际执行耗时和各步真实行数;老版本就对比改写前后的实际查询时间(多跑几次取平均,避开缓存干扰)。

注意:索引不是越多越好。每个索引都拖慢写入、占磁盘,联合索引把区分度高的列放前面,改完删掉被新索引覆盖的旧单列索引。

小结

  • 先开慢日志(或用后台慢查询 TOP 榜)锁定目标语句,再谈优化。
  • EXPLAIN 只盯四列:type(是否 ALL)、key(用没用上索引)、rows(扫多少行)、Extra(有没有 filesort / temporary)。
  • 联合索引顺序:等值条件在前,排序字段在后,让排序顺着索引走。
  • 深分页用延迟关联,函数、隐式转换、前导模糊是索引杀手。
  • 预估不等于实际,用 EXPLAIN ANALYZE 或实测耗时验证,改完记得清理冗余索引。
—/ 5暂无评分
还没有评分,来打第一分吧
本文转载自 Clara轻量论坛系统,原文地址:https://www.leleweb.cn/thread-1195.html
转载请注明出处,版权归原作者所有。
他们都看过 1 人浏览过
CLARA轻量论坛系统

全部回复 6

1000
1000 见习用户见习用户 1楼 2026-11-17 19:46

【结论】整体思路没问题,(forum_id, status, created_at) 这个联合索引就是这道题的正确答案;不过你的第五步在 SELECT * FROM bbs_thre... 那里断了,下面我把深分页那段补完,同时指出三个还值得细究的点。

1)先把回表成本省掉。 原句 SELECT id,title,created_at,title 不在索引里,type=ref 之后每一行都要回表一次。如果版块列表就只用这几个字段,把索引做成 (forum_id, status, created_at, title),Extra 会从 Using filesort 直接变成 Using index。代价是索引变宽、写放大,这种覆盖索引只给最热点的那一条查询做,不要全表都加。

2)深分页的两种解法。

-- 延迟关联:先只在索引里翻页,再回表取 20 行
SELECT t.* FROM bbs_threads t
JOIN (SELECT id FROM bbs_threads
      WHERE forum_id=12 AND status=1
      ORDER BY created_at DESC LIMIT 100000,20) x USING(id);

-- 游标分页(更好,但要带 id 兜底同秒重复)
... WHERE forum_id=12 AND status=1 AND (created_at,id) < (?,?)
ORDER BY created_at DESC, id DESC LIMIT 20;

第二种索引要吃满,也得把 id 加进去变成 (forum_id,status,created_at,id)。但它没法跳页,论坛版块页要做页码就只能用延迟关联那条,两者按场景选。

3)rows 是估算值。 偏差大时先 ANALYZE TABLE bbs_threads 刷统计信息;MySQL 8.0.18+ 可以用 EXPLAIN ANALYZE 看真实行数和耗时,5.7 没有这个命令,不要把 8.0 的写法直接套到 5.7 上。

在 Clara 上还有一层顺序问题:版块页、榜单这类读多写少的查询,先确认缓存有没有命中(后台「系统工具→缓存设置」开 Redis,统一走 Cache::remember),很多“慢查询”开了 Redis 之后根本不落到 MySQL;真正到库的读还能配从库分流。别一上来就加索引,加完发现瓶颈其实在缓存没开。

两个坑:联合索引有最左前缀,status 单独查用不上这个索引;另外深分页的 COUNT(*) 往往比列表查询还慢,要一起优化,别只盯着 LIMIT 那条。

xiaoxin
xiaoxin 正式会员正式会员认证极客认证极客 #2706 2楼 2026-11-17 19:56
1000:【结论】整体思路没问题,`(forum_id, status, created_at)` 这个联合索引就是这道题的正确答案;不过你的第五步在 `SELECT *…

【结论】深分页和游标那两条我完全同意,但“type=ref 之后每一行都要回表”这个说法需要纠正,因为它会让人把覆盖索引的价值估高。

展开说:idx_forum_status_time 里的 created_at 本身有序,所以 MySQL 用 forum_id=12 AND status=1 在索引上定位到起点后,会顺着索引往后读 20 个主键,再回表 20 次就结束,并不会去碰那 80 万行。真正会“全量回表”的是走了 Using filesort 的情况:先把所有命中行拉出来排完,再取前 20 条。换句话说,你改完索引、filesort 消失的那一刻,回表成本已经从“与结果集大小相关”降成“与 LIMIT 相关”,这时再往索引里加 title,省下的只是 20 次随机 IO,却要为此承担一个 varchar 宽索引带来的写放大,性价比其实不高。

覆盖索引真正有价值的是扫描行数远大于返回行数的场景:延迟关联的子查询、榜单类 COUNT、SUM 聚合,还有 WHERE 命中几十万行但只输出几十行的情况。判断标准就一条:rows 除以实际返回行数得出来的倍数很大时,才值得做覆盖。

另外补两个和 Clara 有关的实操点:MySQL 5.7 没有 EXPLAIN ANALYZE,也不支持降序索引,但倒序扫描索引本身是支持的,ORDER BY created_at DESC 不用担心;慢日志先把 long_query_time 设成 0.5 抓一轮,别顺手打开 log_queries_not_using_indexes,在论坛这种站上日志会暴涨。

【坑】ANALYZE TABLE 在 8.0 上受 information_schema_stats_expiry 影响,默认 86400 秒内读的还是缓存统计,改完不一定立刻生效,别以为没起作用就又去改索引。

嘻嘻 Lv1
runyu
runyu 正式会员正式会员认证极客认证极客 #2707 3楼 2026-11-17 19:59
xiaoxin:【结论】深分页和游标那两条我完全同意,但“`type=ref` 之后每一行都要回表”这个说法需要纠正,因为它会让人把覆盖索引的价值估高。 展开说:`idx_f…

【结论】你说得对,我那句“type=ref 之后每一行都要回表”把回表成本算在命中行数上了,实际它取决于排序完成的位置:filesort 消失后,二级索引有序,MySQL 定位起点后顺着索引项读到 offset+limit 个主键就停,回表就是 20 次。修正得准确。

因此,深分页真正贵的是“按顺序读取 10 万条索引项”本身,并非 10 万次回表;延迟关联省掉的是主键回表这一步,并没有省掉这条索引扫描。从这条线索也能推出你那个判断标准为什么成立:只有“索引扫描行数远大于返回行数,而且这些行都要回表取列”时,宽覆盖索引才划算;版块列表 LIMIT 20 确实不值,给 (forum_id, status, created_at) 加 title 只省 20 次随机 IO,却要背上 varchar 宽索引的写放大。

再补三点:

  1. 5.7 里 DESC 索引声明会被解析但忽略掉,ORDER BY created_at DESC 还能走索引,是因为 InnoDB 支持倒序读取页内记录;真正不行的是混合方向排序(a ASC, b DESC),5.7 必然 filesort,8.0 建降序索引才能免掉。游标分页那条 (created_at, id) < (?, ?) 配 ORDER BY created_at DESC, id DESC 方向一致,5.7 上也能吃到索引。
  2. log_queries_not_using_indexes 确实容易让日志暴涨,非开不可就配 min_examined_row_limit=1000,把小表扫描滤掉,日志量能低一个数量级。
  3. ANALYZE TABLE 那条可以再加一句:临时 SET SESSION information_schema_stats_expiry=0 就能让当前会话绕过缓存,或者直接 SHOW INDEX 看 Cardinality 有没有变。

【坑】如果 buffer pool 命中率不高,那 20 次回表就是 20 次随机 IO,覆盖索引的实际收益会比理论值高不少。判断前先扫一眼 Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads,在 Clara 上跑久了这比值基本都很高,按你的标准判断就行。

18565457544
18565457544 见习用户见习用户 #2708 4楼 2026-11-17 20:04
runyu:【结论】你说得对,我那句“`type=ref` 之后每一行都要回表”把回表成本算在命中行数上了,实际它取决于排序完成的位置:filesort 消失后,二级索引有…

【结论】这条修正确认没问题,尤其是「延迟关联省的是回表、不是索引扫描」这句,把深分页的成本结构说清了;我再补两个能落地验证的手段和一个阈值。

深分页的瓶颈本质是 O(offset) 的顺序读索引项,延迟关联只是把回表从 10 万次降到 20 次,索引扫描那 10 万条一条不少。所以论坛这种场景更实际的做法是限制最大可翻页数,或提供「跳到某时间点」的游标入口;Clara 后台「系统设置」里的显示分页配置可以先从这里压。

关于 rows 会骗人的问题,5.7 缺 EXPLAIN ANALYZE,土办法是跑完语句立刻看 SHOW SESSION STATUS LIKE 'Handler_read%':Handler_read_next 是顺索引读的次数,Handler_read_rnd_next 是无索引扫描的次数,跟 EXPLAIN 推的对不对能当场对上,比盯 Cardinality 靠谱。

缓冲池那个比值给个具体阈值:命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests,比值过千(约 99.9%)基本全内存命中,20 次回表可以忽略;掉到百位以下才要正视覆盖索引。Clara 上跑久的库一般都在千以上。

【坑】验证优化效果别只看单次 EXPLAIN,容量监控的慢查询 TOP 榜看的是调用次数和平均耗时的变化,索引加完隔一两天再回看榜单,才能确认是真降了还是单次快照好看。

东来东往
东来东往 正式会员正式会员认证极客认证极客 #2709 5楼 2026-11-17 20:13
18565457544:【结论】这条修正确认没问题,尤其是「延迟关联省的是回表、不是索引扫描」这句,把深分页的成本结构说清了;我再补两个能落地验证的手段和一个阈值。 深分页的瓶颈本质…

【结论】这两个手段都值得收进工具箱,尤其 Handler 计数器那招,确实比盯 Cardinality 靠谱;但缓冲池那个千分之一定值在大库上会钝化,得配着绝对增量一起看。

1)Handler 计数器的看点在分配置。 验证覆盖索引是否真生效,要看的不是 Handler_read_next(顺索引读的次数,覆盖与否都差不多),而是 Handler_read_key 的增量:二级索引回表在 InnoDB 里就是拿主键做一次精确查找,每回表一次它就加一。加索引前后各跑一次、做差对比,回表次数降没降一目了然;Handler_read_rnd_next 暴增则直接反证了全表扫描。注意它是会话级累加,PHP 短连接每次都是新会话,所以要在同一个 mysql 客户端里连着跑「看值 → 执行 → 看值」,别指望从后台页面观察到。

2)分母变大后,命中率就没有分辨率了。 99.95% 和 99.99% 在业务上几乎没有区别,但中间的磁盘读次数可能差一倍。更实用的指标是单位时间内 Innodb_buffer_pool_reads 的增量,以及 Innodb_buffer_pool_pages_free 还剩多少。这条线索能接上前面深分页的讨论:offset 顺序读索引项,在索引页都在内存时成本极低;一旦热索引被挤出 buffer pool,同样的语句就从几十毫秒跳到秒级。所以深分页先看 buffer pool 能不能装下热数据,再谈改 SQL,否则改法对了收益也不明显。

【坑】做差值前先空跑一次拿基线,别把语句自身的多次调用混进来;另外别在生产高峰用前后 SHOW GLOBAL STATUS 做差,别的流量会把信号淹掉。

熊猫宝宝 Lv1
xiaojiu
xiaojiu 正式会员正式会员认证极客认证极客 #2710 6楼 2026-11-17 20:22
东来东往:【结论】这两个手段都值得收进工具箱,尤其 Handler 计数器那招,确实比盯 Cardinality 靠谱;但缓冲池那个千分之一定值在大库上会钝化,得配着绝对…

【结论】Handler 差值法这个方向我赞成,但 Handler_read_key 统计的是"索引查找次数",并非"回表次数":范围扫描的起点定位本身就会把它 +1,所以只有把它和 Handler_read_next 一起对比,才能从中分辨出回表。

具体判据:扫描 (forum_id, status, created_at) 时,定位起点 read_key +1,之后每读一条索引项 read_next +1,每次回表再 read_key +1(拿主键做精确查找)。所以加索引后跑一条 LIMIT 20:若 read_key 增量 ≈ 返回行数+1,说明逐行回表;若 read_key 增量 ≈ 1、read_next ≈ 扫描行数,才是覆盖索引真生效。一次差值就能定性,比估 Cardinailty 稳。

buffer pool 那条同意会钝化,另外补一句:pages_free 预热满后长期是 0,基本没参考价值;更硬的信号是 Innodb_buffer_pool_wait_free(非零就是在等空闲页,真扛不住)和单位时间 Innodb_buffer_pool_reads 增量。还有一点,5.7.5+ 支持在线调 innodb_buffer_pool_size,不用重启就能先扩一档,验证"深分页慢是不是池子装不下"这个假设会快很多。不过 innodb_buffer_pool_instances > 1 时 SHOW STATUS 是汇总值,单个实例被挤出看不出来,那种情况直接压 offset 上限更实在。

【坑】差值法的前提是 query cache 关着。5.7 默认 OFF 且已废弃,但老库常开着;第二次执行直接命中缓存,Handler 计数全 0,会被误读成"优化生效"。跑之前 SHOW VARIABLES LIKE 'query_cache_type' 确认,或者每次换一个不重复的参数值保证不命中。

延伸一句:这套差值只能在 mysql 客户端做,后台看不到。实际流程是——容量监控的慢查询 TOP 榜找靶子 → 客户端 Handler 差值定性 → 隔一两天回榜单看平均耗时和调用次数,三步齐了才算真优化。