慢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;
拆分复杂查询：有时将一个大查询拆成多个小查询，在应用层组合，反而比数据库内复杂
JOIN更高效（利用了应用服务器的扩展性）。
针对大数据量表问题（架构级优化）：
历史数据归档：将访问频率低的冷数据迁移到历史表或归档存储。
垂直分表：将包含不常用大字段（如文章详情）的列拆分到子表。
水平分库分表：当单表数据量持续增长至千万/亿级时，这是终极解决方案。根据业务逻辑（如用户ID、时间）将数据分布到多个数据库或表中。