慢sql优化是数据库性能调优的核心任务之一，有一个比较通用的解决思路：定位问题→分析原因→针对性优化→验证效果

一、定位问题

1.1 MySQL配置

MySQL提供了一套慢sql查询的配置，当sql的执行时间超过用户配置的时间阈值后，就会自动将执行的日志信息记录到日志文件或者表中。

  • 开启配置（my.cnf 或 my.ini）

slow_query_log = 1                # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log  # 日志路径
long_query_time = 1               # 阈值（秒），记录执行超1秒的SQL
log_queries_not_using_indexes = 1 # 可选：记录未使用索引的SQL（即使时间短）

  • 生效方式：重启MySQL或者执行 set global slow_query_log=1;动态生效

1.2 日志分析

1.2.1 mysqldumpslow工具

MySQL自带了日志分析工具 mysqldumpslow 可按执行时间，扫描行数等聚合统计

示例：mysqldumpslow -s t -t 10 /var/log/mysql/slow.log 按总时间排序前10条）

1.2.2 项目监控工具

java项目中有很多工具可以监控sql的执行状况，如Prometheus+Grafana 或者 skywalking 可以在项目中可视化的监控mysql的执行状况。

1.2.3 navicat monitor

navicat monitor是一款强大的商业化数据库监控工具，监控的指标。除了慢sql以外还可以监控数据库的cpu和内存使用率，并且可以自定义报警。

二、分析原因

2.1 EXPLAIN执行计划

分析慢sql的原因，就离不开关键字EXPLAIN，通过EXPLAIN 关键字可以分析执行路径，判断是否存在索引失效，全表扫描，临时表等问题。详细使用方式可以在这篇文章EXPLAIN详解了解。

需要重点关注的几个字段

  • type：访问类型，性能从优到差：system > const > eq_ref > ref > range > index > ALL。最低需要在range之前。如果为ALL（全表扫描）或index（全索引扫描），通常需要优化索引。

  • key：实际使用的索引。如果是NULL，说明没有使用索引。

  • rows：预估需要扫描的行数，数值越大，效率越低。

  • Extra：额外信息，如Using filesort，额外排序 ，Using temporary 生成临时表（常见于GROUP BY/ORDER BY无索引）

2.2 常见问题诊断

通过Explain的结果，一般可以定位到如下问题：

  1. 无索引或索引未使用：如查询字段没有建立索引，联合索引使用错误

  2. 索引选择错误：优化器选择了低效索引（可以通过FORCE INDEX）强制指定

  3. 全表扫描或全索引扫描：type=ALL 或 type=INDEX

  4. 额外排序：order by的字段没有使用索引

  5. 临时表：group by的字段没有索引

三、针对性优化

一般的优化策略可以从一下几个方面考虑

3.1 索引优化方向

  1. 索引优化：针对where join order by group by 的字段添加索引。如 select * from user where age=20 order by create_time; 可以添加联合索引 （age ，create_time）覆盖过滤条件和排序条件。

  2. 避免索引失效：

    1. 禁止索引列上使用函数/表达式（如WHERE YEAR(create_time)=2023 → 改为create_time >= '2023-01-01' AND create_time < '2024-01-01'）。

    2. 避免隐式类型转换（如id字段是VARCHAR，查询用数字WHERE id=123 → 改为'123'）。

    3. 遵循最左匹配原则（联合索引(a,b,c)支持a、a,b、a,b,c查询，但不支持b或c单独查询）。

  3. 避免回表：查询尽量不使用 select * from，可以避免回表查询，如果可以只查询索引中的字段，可以避免回表。

  4. 删除冗余索引：避免重复索引，如已有（a, b）联合索引，就不需要索引 a

3.2 SQL语句优化方向

  1. 避免子查询：

    1. 子查询会创建临时表，增加CPU和IO资源消耗。

    2. 子查询需要将结果方会给外层查询，如果结果集较大，会增加网络传输和内存占用

  2. 优化JOIN操作：确保关联字段又索引 使用小表驱动大表

  3. 分页优化：使用limit关键字时，要防止深层分页，如limit 100000,20 则会扫描100020行，可通过上一页查询的最大索引进行分页 如：select * from user where id = 100000 limit 20

3.3 表结构与数据优化

  1. 拆分大表：如果单表数据量过大，可以按照时间或者业务维度拆分。

  2. 调整字段类型：使用更紧凑的类型，如INT代替VARCHAR保存状态码 DATETIME代替字符串保存时间等

  3. 分离大字段：TEXT/BLOB等大字段可以单独存放到扩张表，减少主表扫描开销

3.4 数据库配置调优

  1. 缓冲池优化：增大数据库缓冲池，可以缓存更多数据和索引。

  2. 增大临时表内存配置：在必须用到临时表时，可以避免临时表频繁写入磁盘。

四、验证效果

  1. 测试环境验证，对比优化前后的执行时间，扫描行数等关键信息。

  2. 长期监控：使用Prometheur+Grafana或MySQL自带的监控，跟踪SQL的执行效率