慢查询优化的核心结论是:任何不基于执行计划的耗时分析都是盲目的,执行计划中暴露的索引失效点才是慢查询的真正根因。以MySQL为例,一条查询从几百毫秒飙升到几秒,往往不是因为数据量暴涨,而是优化器没能正确利用索引。数据库慢查询日志记录的是表面现象,执行计划才能告诉你为什么慢。
开启慢查询日志是定位慢查询的第一步,但日志参数配置直接影响排查效率。MySQL通过long_query_time阈值控制记录范围,默认值10秒对于大多数业务场景过于宽松,建议在测试环境调整为0.5秒或1秒。
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5; SET GLOBAL log_queries_not_using_indexes = ON;
第三条参数log_queries_not_using_indexes会强制记录所有未走索引的查询,这对发现索引失效问题非常有效。采集到日志后,使用mysqldumpslow工具聚合分析,可以快速找出频率最高的慢查询模式。
据MySQL官方文档《The Slow Query Log》描述:慢查询日志包含SQL执行时间、锁等待时间、扫描行数等字段,其中扫描行数是判断索引是否有效的直接依据。
执行计划中type字段直接反映访问方式,当取值为ALL时表示全表扫描,这是索引失效最明显的信号。全表扫描意味着优化器放弃索引,原因可能是指标选择性不足,也可能是指标被查询条件破坏。
使用EXPLAIN命令可以获取执行计划,重点关注以下几个字段:
type:访问类型,从好到坏依次为system>const>eq_ref>ref>range>index>ALL。key:实际使用的索引,为NULL时说明未使用索引。rows:优化器估算需要扫描的行数,该值远大于最终返回行数时,索引往往失效。filtered:返回行数占扫描行数的百分比,数值越低说明过滤效率越差。EXPLAIN SELECT * FROM orders WHERE user_id = '1001' AND order_date > '2024-01-01';
如果上面查询的type为ALL,而user_id单列索引存在,那么你遇到了典型的索引失效场景。
索引失效不是索引本身损坏,而是查询条件导致优化器无法使用索引。以下四种场景在业务代码中出现频率最高,每种都有明确的判断特征和规避手段。
| 失效场景 | 典型SQL特征 | 失效原因 | 优化方案 |
|---|---|---|---|
| 函数作用于索引列 | WHERE DATE(created_at) = '2025-01-01' | 索引列被函数包裹,B+树无法按原值查找 | 改写为范围条件:created_at >= ... AND created_at < ... |
| 隐式类型转换 | WHERE phone = 18812345678 | 字符串与数字比较,MySQL对索引列调用转换函数 | 参数保持一致类型,或强制转换为字符串 |
| 联合索引未遵循最左前缀 | 索引(a,b,c),条件只用到b | B+树按联合索引有序存储,跳过首位无法定位 | 补充a条件,或调整联合索引顺序 |
| LIKE以通配符开头 | WHERE name LIKE '%张三%' | 无法利用索引有序性进行范围扫描 | 改用全文索引,或完全匹配加后缀通配 |
索引基数是指某列唯一值的数量,基数越高索引选择性越好。MySQL优化器依赖统计信息估算不同执行计划的代价,当统计信息过期时,优化器会做出错误判断。
在一次订单表查询场景中,执行计划显示优化器选择了status索引(过滤后仍有数万行),而user_id索引本可将扫描范围缩到几百行。原因是status列的统计信息长期未更新,优化器误以为它更具区分度。
-- 分析前执行计划 EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid'; -- 强制使用user_id索引 SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 123 AND status = 'paid';
对比两次执行计划的rows字段,你会发现强制索引后的估算行数下降了两个数量级。这种问题是统计信息陈旧造成的。定期执行ANALYZE TABLE能显著降低此类风险。
据Percona《Index Cardinality and Statistics》技术报告显示,超过70%的慢查询与优化器统计信息不准间接相关,而索引失效的直接场景中函数操作和隐式转换合计占比过半。
当慢查询出现时,按以下顺序排查可以快速定位问题源头。
EXPLAIN记录当前SQL的access type、key、rows和filtered。ANALYZE TABLE刷新统计信息后再观察执行计划变化。这套方法适用于MySQL、PostgreSQL等主流关系型数据库,不同数据库的执行计划字段名称略有差异,但分析逻辑完全一致。
执行计划不是静态的,数据分布变化和索引调整都会改变优化器决策。建立慢查询回归测试机制,在每次发布索引变更后重新执行核心SQL并对比执行计划,是避免性能退化的有效手段。
记住:慢查询优化永远从执行计划开始,从索引失效点着手。盲目提升硬件或调整参数只能掩盖问题,无法消除根因。
评论
加载中...