MySQL 慢查询完整排查

1、开启慢查询日志（抓慢 SQL）

1. slow_query_log=ON开启慢查询日志
2. long_query_time：慢查询阈值，单位秒
   • 普通业务：1~2s
   • 金融交易、高并发核心链路：500ms(0.5)
3. slow_query_log_file：慢日志文件路径
4. log_queries_not_using_indexes：记录没有使用索引的 SQL（测试环境开启，生产谨慎，压力大）

注意：long_query_time统计的是SQL 实际执行时间，不包含锁等待时间；如果 SQL 因为锁等待卡住，不会被慢日志捕获。

工具：mysqldumpslow分析慢日志，汇总相同 SQL 的执行次数、平均耗时。

2、explain 执行计划重点字段（面试高频）

重点看：type、key、rows、ref、Extra

type 访问类型（优先级从差到优）

ALL>index>range>ref>eq_ref>const/system

1. ALL：全表扫描，性能最差，必须优化，没有可用索引
2. index：扫描整个索引树，比 all 好，但依然大量 IO
3. range：范围查询，> < >= <= in between，用到索引范围扫描
4. ref：非唯一索引等值匹配，命中多行
5. eq_ref：唯一索引等值，最多匹配一行（主键 / 唯一索引关联）
6. const：常量匹配，主键 / 唯一索引直接定位一行

生产目标：type 尽量达到ref/range，禁止大量 SQL 出现ALL

key

• key：实际真正使用到的索引，null 代表没走索引
• possible_key：理论上可以选用的索引，不一定真正用到

rows

MySQL 预估需要扫描的行数，数值越大性能越差，是预估值，不是真实返回行数。

ref

索引匹配时使用的列 / 常量，显示索引列匹配的是常量还是其他表字段。

Extra 额外信息，重点坑点

1. Using index✅ 覆盖索引，不需要回表，性能优秀
2. Using temporary❌ 创建临时表，常见于group by、distinct、union，消耗内存 / 磁盘，性能差
3. Using filesort❌ 文件排序，不是磁盘文件，是内存排序，order by 无法利用索引排序，需要额外排序，大结果集非常慢
4. Using where：存储引擎返回数据后，server 层再过滤条件
5. Using join buffer：关联查询没用到索引，使用连接缓冲区

只要出现Using temporary/Using filesort就要重点优化。

3、四大层面优化手段

① 索引层面（最常用）

1. 给 where、join、order by、group by 字段建立合适索引
2. 遵循最左前缀原则，联合索引顺序：等值条件 > 范围条件 > 排序分组字段
3. 避免索引失效：
   • 不要对索引列做函数运算、隐式类型转换
   • like %xxx前缀模糊查询不走 B + 树索引
   • or 左右两边字段都要建索引，否则索引失效
4. 使用覆盖索引，减少回表
5. 删除冗余、重复、很少使用的索引，索引不是越多越好，会加重写操作负担

② SQL 语句书写层面

1. 避免 select *，只查需要的字段，利于覆盖索引
2. 大表禁止select count(*)统计全量；limit 大偏移量分页优化（延迟关联）
3. 减少 in 里面大量集合元素，大 in 可以改成 join
4. 避免order by rand()
5. group by 尽量利用索引，避免 Using temporary;filesort
6. 拆分大 SQL，不要一次性查询超大结果集；避免一次性查出几万行以上数据到应用内存
7. 少用子查询，优先 join；避免 not in，改用 not exists 或者 left join

③ 架构层面

1. 读写分离：读压力大，主写从读，分担查询压力
2. 分库分表：单表数据量千万级别以上，水平拆分，降低单表扫描行数
3. 引入缓存 Redis，热点查询直接缓存，绕过 MySQL 查询
4. 业务层限制查询，分页做上限，禁止无边界查询

④ 数据库设计 & 配置层面

数据库设计

1. 合理字段类型，尽量小，避免大 text/blob 字段；大字段单独拆分出去
2. 范式适度，适当反范式，减少多表 join
3. 避免大事务，事务时间过长会锁等待、MVCC 开销

配置参数

1. join_buffer_size、sort_buffer_size、tmp_table_size，不要调太大，每个连接都会分配内存
2. innodb_buffer_pool_size：核心，缓存索引和数据页，一般设置机器内存 50%~70%
3. 慢查询阈值根据业务调整，核心链路调小
4. 锁相关：排查行锁表锁，长事务导致锁等待引发慢查询

4、补充容易踩坑点

1. explain 只是预估执行计划，不一定完全等于真实运行；MySQL 优化器会根据数据量选择索引，统计信息不准会选错索引，可以 analyze table 更新统计信息。
2. 慢查询日志抓不到锁等待耗时；锁等待问题要看 show engine innodb status、performance_schema。
3. 索引优化不是万能，写多的业务，索引越多插入更新越慢。