MySQL 慢查询治理实战:从执行计划到索引设计的完整路径

举报
yd_252044305 发表于 2026/11/12 13:03:33 2026/11/12
【摘要】 慢查询是数据库问题里最常见也最值得投入的一类:它不炸服务,但会持续消耗连接数和 CPU,等流量高峰一来就把整个实例拖垮。本文把一次完整的慢查询治理过程拆成"发现、诊断、优化、防复发"四步,每一步都给出可以直接照做的判断标准,适用于 MySQL 5.7/8.0。一、先把慢查询日志打开,别靠业务反馈很多团队是等业务方反馈"页面卡"才去查数据库,这时候往往已经积压了大量问题。正确做法是先打开慢查询...

慢查询是数据库问题里最常见也最值得投入的一类:它不炸服务,但会持续消耗连接数和 CPU,等流量高峰一来就把整个实例拖垮。本文把一次完整的慢查询治理过程拆成"发现、诊断、优化、防复发"四步,每一步都给出可以直接照做的判断标准,适用于 MySQL 5.7/8.0。


一、先把慢查询日志打开,别靠业务反馈


很多团队是等业务方反馈"页面卡"才去查数据库,这时候往往已经积压了大量问题。正确做法是先打开慢查询日志,让数据自己说话:


1. 设置 long_query_time = 0.5(不要用默认的 10 秒,互联网场景下 500ms 以上就值得看),配合 log_queries_not_using_indexes 可以把没用索引的语句也捞出来,但这个开关噪音大,建议只在排查期临时打开。

2. 定期用 mysqldumpslow 或 pt-query-digest 聚合分析,按总耗时排序而不是按单次耗时排序。一条单次 60ms 但每分钟执行三千次的语句,危害远大于一条一天才跑一次的 5 秒大查询。

3. 排序后优先处理"总耗时占比前三"的语句,一般做完这三条,慢查询总量能下降一半以上。


二、诊断的核心是执行计划,盯住四个字段


拿到一条慢 SQL,第一反应不是改写,而是 EXPLAIN。输出有十几个字段,日常只需要盯住四个:


- type:访问类型。const、eq_ref、ref 属于健康状态;range 是索引范围扫描,尚可接受;index 是全索引扫描,通常意味着索引设计有问题;ALL 是全表扫描,在超过十万行的表上出现就必须处理。

- key 与 key_len:实际用到的索引以及使用的长度。key 是 NULL 说明没走索引;key_len 明显小于索引定义长度,说明复合索引只用了前几列。

- rows:预估扫描行数。这不是精确值,但数量级很有参考意义——如果 rows 是百万级而结果只返回几十行,说明过滤条件没有全部下推到索引层。

- Extra:Using filesort 说明排序没有走索引,需要额外排序缓冲;Using temporary 意味着用了临时表,常见于 group by 和 distinct 场景;Using index 是好状态,表示覆盖索引,连回表都省了。


一个实用技巧:EXPLAIN 之后紧跟一句 SHOW WARNINGS,能看到优化器改写后的真实查询,很多"为什么没走索引"的疑惑在这一步就有了答案。


三、索引设计:几条经过验证的规则


诊断出问题后,大多数慢查询的解法都落在索引设计上。以下规则按重要性排序:


1. 最左前缀原则要反向使用。复合索引 (a, b, c) 只能服务以 a 开头的查询条件,所以列顺序应该按"等值条件在前、范围条件在后"排列。where b = ? and a > ? 这种写法,索引应该是 (b, a) 而不是 (a, b),因为范围列放在中间会让后面的列全部失效。

2. 选择性低的列不要单独建索引。status、type 这类只有几个取值的列,单独索引几乎不会被优化器选中,还拖慢写入。但如果它们常与其他条件组合出现,可以放进复合索引做辅助过滤。

3. 用覆盖索引消灭回表。如果一个查询只 select 三四个字段,把它们全部纳入索引,Extra 出现 Using index 后,查询速度常有数量级的提升。典型场景是列表页的 count 和分页查询。

4. 索引不是越多越好。每个索引都会让写入付出代价,还会让优化器的选择空间变大、出错概率变高。经验值是单表索引不超过 6 个,重复前缀的索引(如已有 (a,b) 又建 (a))要清理。


四、写法层面的四个高频陷阱


索引没问题却依然慢,多半是写法把索引废掉了:


- 隐式类型转换:phone 字段是 varchar,where phone = 13800000000 里传了数字,MySQL 会把列转成数字比较,索引直接失效。规则很简单:字符串列用字符串传参,别依赖隐式转换。

- 对索引列做函数或运算:where date(create_time) = '2026-11-12' 会让 create_time 上的索引失效,改写成 create_time >= '2026-11-12 00:00:00' and create_time < '2026-11-13 00:00:00' 即可。8.0 之后可以建函数索引,但改写更通用。

- 前导模糊匹配:like '%abc' 无法走 B+ 树索引,like 'abc%' 可以。确实需要全文搜索就上专门的全文索引或搜索引擎,不要为难 B+ 树。

- 深分页:limit 1000000, 20 需要扫描并丢弃前一百万行。经典解法是"游标翻页":where id > 上一页最大id limit 20,把偏移量变成索引定位。无法连续翻页的场景可以让子查询先只查主键:select ... from t join (select id from t order by x limit 1000000, 20) as tmp on t.id = tmp.id,至少把回表量从一百万降到二十。


五、变更落地与防复发


改索引属于线上变更,要注意两点:一是 5.7 用 Online DDL 时务必确认 ALGORITHM=INLOCK 不存在,实际写法是 ALGORITHM=INPLACE, LOCK=NONE,同时避开业务高峰;大表改索引建议用 gh-ost 或 pt-online-schema-change,从库先行的灰度策略能进一步压低风险。二是优化完成后把这条 SQL 和它的执行计划基线记录下来,定期巡检时对比 rows 预估值的变化——数据量增长后原来的索引可能逐渐失效,提前发现比等慢查询告警更从容。


治理慢查询的本质不是背规则,而是建立"度量、诊断、优化、回归"的闭环。规则会过时,闭环不会。把每一条治理过的 SQL 沉淀成团队的案例库,新人踩过的坑基本都能在里面找到答案,这才是慢查询治理最大的复利。


【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。