慢SQL分析与优化实战-编程实验室

慢SQL分析与优化实战

一、发现问题：如何发现慢查询？

慢查询的发现通常依赖于监控体系，不同规模团队分工不同：

团队角色	主要发现途径	工具示例
运维 / DBA	主导监控。负责数据库整体健康度，通过监控平台主动发现、统计并告警慢查询。	MySQL 慢查询日志、Prometheus + Grafana、APM（应用性能监控）工具（如SkyWalking, Pinpoint）、云厂商RDS控制台。
开发人员	被动接收 & 主动探查。接收DBA/运维提供的慢SQL清单；在开发测试阶段通过压测、代码Review或本地分析提前发现问题。	测试环境 EXPLAIN、压测工具、集成开发环境（IDE）的数据库插件。

核心工具：MySQL慢查询日志

配置参数：slow_query_log,long_query_time（定义“慢”的阈值，如2秒）,slow_query_log_file。
作用：自动记录所有执行时间超过阈值的SQL语句及相关信息（执行时间、锁时间、扫描行数等），是分析的根本依据。

二、分析问题：诊断慢查询的“病因”

拿到一条慢SQL后，不要盲目猜测，应进行系统化诊断。核心原因通常归为以下三类：

1. 查询未命中或低效使用索引（最常见）

现象：EXPLAIN输出中type为ALL（全表扫描）、index（全索引扫描），或key字段为NULL。
排查点：
- 索引是否创建在WHERE、ORDER BY、GROUP BY、JOIN ON的字段上？
- 索引是否失效？常见失效场景：对索引列进行函数计算、类型隐式转换、使用!=或<>、OR连接非索引列、左模糊匹配LIKE ‘%xxx’。
- 是否使用了不合适的索引？通过EXPLAIN对比possible_keys和key。

2. 查询了不必要的数据（可优化空间大）

现象：查询返回的数据量远大于业务需要。
排查点：
- 是否滥用SELECT *？只查询需要的列，特别是TEXT/BLOB大字段。
- 是否有大量重复数据被返回？考虑使用DISTINCT或优化业务逻辑。
- 是否通过多表关联（JOIN）查询了无需的关联表数据？或可以使用更高效的EXISTS/IN。
- 分页查询是否深度翻页？LIMIT 1000000, 10会先扫描1000010行，效率极低。优化方案：使用覆盖索引+延迟关联，或记录上一页最大ID作为查询起点。

3. 单表数据量过大（触及架构瓶颈）

现象：SQL和索引已优化到极致，EXPLAIN结果理想，但因数据行数过多，查询仍慢。
判断标准：这是一个在业务增长到一定阶段后必然出现的问题。

三、解决问题：针对性优化方案

根据诊断出的“病因”，实施相应优化：

针对索引问题：

添加缺失索引：为高频查询条件创建合适索引，考虑联合索引并利用最左前缀原则。
优化索引设计：避免在区分度低的字段（如“性别”）上建单列索引。考虑使用覆盖索引避免回表。
改写SQL绕过索引失效：例如，将WHERE YEAR(create_time) = 2023改为WHERE create_time BETWEEN ‘2023-01-01’ AND ‘2023-12-31’。

针对查询数据量问题：

禁用SELECT *，明确指定列。

优化分页：

-- 低效 SELECT * FROM table ORDER BY id LIMIT 1000000, 10; -- 高效：使用覆盖索引延迟关联 SELECT * FROM table t1 INNER JOIN (SELECT id FROM table ORDER BY id LIMIT 1000000, 10) t2 ON t1.id = t2.id;