MySQL 如何优化慢查询
MySQL 慢查询问题是性能调优的核心之一。本笔记详细介绍 慢查询的定位、分析、优化方法，包括 SQL 优化、索引优化、执行计划分析、表设计优化、数据库配置优化 等内容。
一、慢查询的定义
慢查询（Slow Query）：执行时间超过 long_query_time 阈值的 SQL 语句。
查看慢查询日志配置
-- 查看是否开启慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;-- 设置慢查询阈值（默认 10 秒）
SET GLOBAL long_query_time = 1;-- 查看慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';
二、慢查询优化的总体思路
核心步骤
- 确认慢 SQL：通过慢查询日志、
SHOW PROCESSLIST、Performance Schema。 - 分析执行计划：使用
EXPLAIN、EXPLAIN ANALYZE。 - 优化 SQL 语句：减少数据扫描量，改写语句。
- 优化索引：添加合适索引，避免索引失效。
- 优化表结构：拆分表、调整字段类型。
- 优化数据库配置：调整缓存、连接池。
三、常见慢查询原因
- 未命中索引（全表扫描）。
- 索引失效（函数、隐式转换、OR 等）。
- 返回数据量过大（缺少分页或 LIMIT）。
- SQL 语句不合理（子查询、嵌套、IN 大集合）。
- 表设计不合理（字段类型不合适，缺少主键）。
- 数据库参数配置问题（Buffer Pool 太小）。
四、定位慢查询
方法 1：慢查询日志
开启慢查询日志，找到耗时最长的 SQL。
SHOW VARIABLES LIKE 'slow_query_log';
方法 2：Performance Schema
实时监控 SQL 执行情况。
方法 3：SHOW PROCESSLIST
查看当前正在执行的 SQL。
五、慢查询优化方法
1. 使用 EXPLAIN 分析执行计划
EXPLAIN 查看 SQL 的执行过程：
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
EXPLAIN 关键字段：
- type：连接类型（ALL、index、range、ref、const、eq_ref、system）。
- key：使用的索引。
- rows：扫描的行数。
- Extra：是否使用文件排序（Using filesort）、临时表（Using temporary）。
优化目标：
- 避免
ALL（全表扫描），尽量使用ref或const。 - 使用合适的索引。
2. SQL 语句优化技巧
**(1) 避免 SELECT ***
只查询需要的字段，减少数据量：
-- 慢
SELECT * FROM users;-- 优化
SELECT id, name FROM users;
(2) 避免使用 OR，用 IN 或 UNION ALL 替代
-- 慢
SELECT * FROM users WHERE status = 1 OR age = 20;-- 优化
SELECT * FROM users WHERE status IN (1, 20);
(3) 避免在索引列上使用函数
-- 慢（索引失效）
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';-- 优化
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
(4) 避免隐式类型转换
-- 慢（字段是 VARCHAR，条件写成数字）
SELECT * FROM users WHERE id = 100;-- 优化
SELECT * FROM users WHERE id = '100';
(5) LIMIT 分页优化
大偏移量分页会导致性能差：
-- 慢
SELECT * FROM orders LIMIT 1000000, 10;-- 优化（记录上次最大 ID）
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;
3. 索引优化
(1) 添加合适的索引
- 单列索引、联合索引。
- 最左前缀原则。
(2) 避免索引失效场景
- LIKE 左模糊（
LIKE '%xx'）。 - OR 条件。
- 函数、表达式。
- 隐式类型转换。
(3) 使用覆盖索引
只查询索引列，避免回表：
SELECT user_id FROM orders WHERE user_id = 100; -- 如果 user_id 有索引，可以直接覆盖
4. 表设计优化
- 字段类型选择合适（如 INT 优于 VARCHAR）。
- 避免大字段（TEXT/BLOB）频繁查询。
- 表分区、分库分表。
5. 数据库参数优化
- 调整 InnoDB Buffer Pool Size（缓存更多索引和数据页）。
- 调整 query_cache_size（MySQL 8.0 已移除）。
- 调整 sort_buffer_size。
六、常用工具
- EXPLAIN / EXPLAIN ANALYZE：执行计划分析。
- SHOW PROFILE：查看 SQL 执行各阶段耗时。
- 慢查询日志。
- pt-query-digest（Percona 工具）：分析慢日志。
七、面试高频问答
Q1：如何排查 MySQL 慢查询？
- 开启慢查询日志，分析日志。
- 使用
EXPLAIN查看执行计划。 - 查看索引是否命中。
- 使用
SHOW PROFILE分析 SQL 执行阶段。
Q2：EXPLAIN 中 type 字段，哪个性能最好？
- 最好：
const、eq_ref。 - 最差：
ALL（全表扫描）。
Q3：为什么 LIMIT 大偏移量会慢？如何优化？
- 因为需要扫描并跳过大量记录。
- 优化方法：记录上次最大 ID，或者使用 延迟关联。
Q4：如何避免索引失效？
- 遵循最左匹配原则。
- 避免函数、隐式转换、OR。
- LIKE 避免左模糊。
Q5：如何快速定位最慢的 SQL？
- 开启慢查询日志。
- 使用
pt-query-digest工具。
八、总结优化 checklist
SQL 语句优化：
- 避免 SELECT *。
- 避免 OR，使用 IN。
- 避免函数、表达式操作。
- 避免隐式类型转换。
索引优化：
- 建立合理索引。
- 使用覆盖索引。
- 遵守最左前缀原则。
表设计优化：
- 字段类型选择合理。
- 避免大字段频繁查询。
数据库参数优化：
- 调整 Buffer Pool。
- 优化连接池。
核心原则：
减少扫描行数 + 使用索引 + 合理 SQL 改写 + 缓存热点数据 + 监控分析工具