结论先行:线上慢查询九成是索引问题,而索引失效最常见的原因不是"没建索引",而是写法让索引用不上——隐式类型转换、对列做函数运算、前导模糊匹配。正确顺序是:先开慢日志定位 Top SQL,再逐条用 explain 验证原因,最后改写并用 ANALYZE 复核,不要凭感觉改。
定位:先让慢查询自己浮出来
-- 动态开启慢日志,不需要重启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- 超过 500ms 记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 可选,注意日志量
-- 找出当前正在执行的慢 SQL
SELECT id, user, db, time, state, LEFT(info, 80) AS sql_head
FROM information_schema.processlist
WHERE command = 'Query' AND time > 3
ORDER BY time DESC;
日志文件用 pt-query-digest 聚合分析,按总耗时排序而不是单次最大耗时——批量重复的 200ms 查询,往往比偶发的 2s 更伤系统。排序指标的选择直接决定优化性价比:pt-query-digest slow.log --order-by tot 类似思路,先治"总耗时大户",再治"单次大户"。日志阈值也别一刀切:0.5 秒适合 OLTP 核心库,报表库可以放宽到 2 秒,阈值太低会把日志写爆、反而淹没真问题。
慢日志里每条记录最有诊断价值的是四个数字的组合:Query_time(执行总耗时)、Lock_time(等锁耗时)、Rows_sent(返回行数)、Rows_examined(扫描行数)。Rows_examined / Rows_sent 的比值是索引质量的直接度量——比值上百,几乎必然存在回表放大或过滤不足;而 Query_time 里 Lock_time 占比高,说明问题根本不在索引而在锁等待,优化方向完全不同。读慢日志先算这两个比值,比看 SQL 文本快得多。
卡在执行中的 SQL 还有第三条线索:processlist 里 state 为 Sending data、Creating sort index 的连接,配合 SELECT * FROM sys.session 可以直接看到谁在烧资源。慢日志是事后账本,processlist 是事发现场,两者互补。
复盘三个真实案例
案例一:隐式类型转换
phone 是 varchar,查询却写成 WHERE phone = 13800138000(数字字面量)。MySQL 会把 phone 列的值逐行转成数字再比较,索引用不上。改成字符串字面量 = '13800138000' 立刻走索引。join 时两列类型或字符集不一致,也会让被驱动表的索引失效,原理相同。这类问题在代码 review 里很难肉眼看出来,因为 SQL 在 ORM 里拼参数,参数类型到了 JDBC 层才决定——Number 传参给 varchar 列就是隐形炸弹。
案例二:对索引列做函数或运算
-- 失效:对列套函数,只能全表扫描
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';
-- 正确:改写为范围条件,走 created_at 索引
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';
统一记一条规则:运算只放在常量一侧,永远不要包在索引列外面。 同族问题还包括:id + 1 = 100 这类列上运算、JSON_EXTRACT(col, '$.k') 过滤、以及 MySQL 8.0 之前对 collation 不同的列做 join。8.0 虽然引入了函数索引,但那是对"实在改不了写法"的兜底,不是鼓励。
案例三:深分页
LIMIT 1000000, 20 慢,不是取 20 行慢,而是要回表 100 万次再丢弃 999980 行。延迟关联可以根治:
-- 优化前:回表 100 万次再丢弃
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 优化后:子查询用覆盖索引只取主键,再回表 20 次
SELECT o.*
FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id = t.id;
更彻底的方案是产品层面禁止深翻页:只允许"下一页"(记住上页末尾 id,用 WHERE id > :lastId LIMIT 20),游标式翻页成本恒定,深分页问题从源头消失。搜索引擎结果页永远只有下一页,就是这个道理。顺带一提,深分页还有个数据一致性陷阱:offset 翻页期间数据在增删,页与页之间可能重复或丢行;游标式翻页以主键为锚点,天然规避了这类抖动——用户体验与性能在这里是同一件事。
索引失效全谱系
把三个案例推广开,索引用不上的写法可以收成一张清单,review 时逐条对照:
| 失效写法 | 示例 | 改写方向 |
|---|---|---|
| 隐式类型转换 | varchar 列 = 数字字面量 | 参数与列类型对齐 |
| 列上套函数 | DATE(col) = ‘日期’ | 改写成范围条件 |
| 列参与运算 | col + 1 = 100 | 常量侧移项:col = 99 |
| 前导模糊 | like ‘%关键字’ | 逆转存储 / 全文索引 / ES |
| 缺最左前缀 | 联合索引 (a,b) 只查 b | 调整索引列序或补条件 |
| or 连接非索引列 | 索引列 or 非索引列 | union 拆分,各自走索引 |
| join 类型/字符集不一致 | int join varchar | 统一两列类型与 collation |
这张表不是要背下来,而是每次 explain 里 key=NULL 时,按表逐条排查写法,绝大多数能对号入座。
写法之外还有一层是"索引本身建得对不对":索引列的选择性(近似用 count(distinct col) / count(*) 估算)决定过滤效率,性别、状态这类低区分度列单独建索引基本无效,要进联合索引给高区分度列打头阵;字符串列可用前缀索引省空间,但前缀长度要让区分度接近全列——select count(distinct left(col, n)) 逐档测算后选拐点。索引是给优化器的"供给",选择性不足的供给,优化器弃用是正确行为。
优化手法清单
- 按需 select,能用覆盖索引就不要回表;
- 联合索引遵循最左前缀,等值条件在前、范围条件放最后;
- join 确认被驱动表走索引,小表驱动大表;
- 统计信息失真时
ANALYZE TABLE重新采集; - SQL 改写无法生效时换业务方案:预聚合表、异步出报表、搜索场景外置到 ES;
- 排序与分组尽量复用索引序,消除 filesort 与 temporary;
- 优化有边际:单表行数、行宽顶到上限后,该归档归档、该拆表拆表,SQL 层面已无解。
复盘方法论
每个慢查询优化都留下三段记录:优化前的 explain、做了什么改动、优化后的 explain 与耗时对比。一是防止"改了但没生效"的误判,二是半年后别人接手时有据可查。更进一步,把"改动的 SQL + 前后对比"沉淀进团队的慢查询案例库,同类问题第二次出现时直接检索复用——慢查询的模式高度重复,一个团队一年真正遇到的失效模式,通常不超过那张全谱系表的行数。治理常态化还有一条低成本路径:每周固定拉一次 pt-query-digest 的 Top 10 报告在例会上过一遍,新增上榜的 SQL 当周归因,不让债滚到月度大扫除。慢查询治理拼的不是单点技巧,是"有人持续盯"这个动作本身。
一句话总结:慢查询优化是流程问题不是技巧问题——日志定位、explain 归因、改写验证,三步闭环比背十个索引口诀管用。
生产案例
某次运营大屏项目上线当晚告警:报表库 CPU 跑满,核心交易库的同步读也被拖慢。pt-query-digest 排出总耗时第一名的是一条运营侧的订单明细查询,单次不到一秒,但每次活动页面刷新都会触发——总耗时占了库的一半。explain 显示 type=ALL:WHERE merchant_name LIKE '%优选%',前导模糊匹配让新建的索引全部失效。短期方案是把这条查询从交易库摘走,迁到只读报表实例;中期方案是与运营确认检索语义后,把品牌名检索外置到 ES,数据库只承担精确条件查询。当晚还要紧的一步是反向加固:给报表库连接数加了上限,防止下一个类似的查询再把主库拖下水。这次事故的复盘结论有两条:一是慢日志必须对报表流量单独设阈值,报表 SQL 不允许豁免;二是任何模糊搜索需求,评审阶段就要明确"数据库不做前导模糊"的边界。运营侧还要配套一条:大屏类查询只允许打在数仓或只读实例上,核心库的 processlist 里出现运营账号就告警——把"谁可以在核心库跑什么"变成白名单机制,而不是靠自觉。
检查清单
- 核心库慢日志常开,阈值按业务分级(OLTP 0.5 秒、报表 2 秒)
- 定期用 pt-query-digest 按总耗时排 Top SQL,而不是只看单次耗时
- review SQL 时对照索引失效全谱系:类型转换、函数、运算、前导模糊、最左前缀
- 优化前后各留一份 explain / EXPLAIN ANALYZE 证据,进案例库
- 深分页改延迟关联,或产品层改为游标式翻页
- 报表与搜索流量与 OLTP 隔离,模糊检索外置 ES
- 统计信息异常时 ANALYZE TABLE,不直接 force index