MySQL中如何进行SQL调优？

重点

SQL 调优的核心思想就是减少磁盘 I/O 和避免无效计算。实际操作分三步走：先定位慢 SQL，在分析执行计划、最后针对性优化。

定位慢 SQL 靠 MySQL 的慢查询日志，分析执行计划用 EXPLAIN，优化手段主要由这几类：

1. 索引层面优化
   • 合理设计联合索引，利用覆盖索引避免回表。比如查询只需要 name 和 age，那索引简称(name,age)就不用回表去主键索引拿数据了
   • 注意最左匹配原则，where b = 1吃不到a,b,c这个联合索引
   • 避免在索引上做函数运算，WHERE YEAR(create_time) = 2024会让索引失效，改成WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
2. SQL 写法优化
   • 禁止SELECT *，只查必要字段，减少网络传输和内存占用
   • 避免%LIKE前缀模糊查询，LIKE '%关键词必然全表扫描
   • 连表查询时检查字符串是否一致，utf8 和 utf8mb4 的字段 JOIN 会导致隐式转换，索引直接废掉。
3. 架构层面
   • 热点数据上 Redis 缓存，访问频率搞但变化少的数据没必要每次都查库
   • 大表考虑分库分表，单表超过 2000 万行查询性能会明显下降
   • 读写分离，把查询压力分摊到从库

还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换成分页分批查询等等。

扩展

EXPLAIN 关键指标解读

EXPLAIN 输出一堆字段，重点盯这几个：
type表示访问类型，性能从最好到差排序：system > const > en_ref > ref > range > index > ALL。看到ALL基本就是全表扫描，得加索引了。ref以上算比较健康的。
rows 是MySQL预估要扫描的行数，这个数字越小越好。如果一个查询rows显示100万，那肯定有问题。
Extra 里的信息很关键：

• Using index表示用到了覆盖索引，不用回表
• Using where表示在存储引擎返回数据后还要在Server层过滤
• Using filesort说明排序没用上索引，需要额外排序操作
• Using temporary说明用了临时表，一般出现在GROUP BY 或 DISTINCT场景

-- 一个典型的分析案例
EXPLAIN SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time;

-- 如果 Extra 显示 Using filesort，说明 create_time 排序没走索引
-- 优化方案：建联合索引 (user_id, create_time)

索引失效的常见场景

很多人以为建了索引就万事大吉，其实有一堆情况会让索引失效：

1. 对索引列做运算或函数调用

-- 索引失效
SELECT * FROM users WHERE YEAR(birthday) = 1990;
-- 优化后
SELECT * FROM users WHERE birthday >= '1990-01-01' AND birthday < '1991-01-01';

2. 隐式类型转换

-- phone 是 varchar 类型，传入数字会触发隐式转换
SELECT * FROM users WHERE phone = 13800138000;  -- 索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- 正常走索引

3. OR条件中有非索引字段

-- 假设 name 有索引，age 没有
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 全表扫描

4. 联合索引不满足最左匹配

-- 索引是 (a, b, c)
SELECT * FROM t WHERE b = 1;  -- 索引失效
SELECT * FROM t WHERE a = 1 AND c = 3;  -- 只能用到 a

5. like通配符
   比如WHERE name LIKE '%XXX'，这种是无法使用上索引的。

6. 优化器的选择
   不是有索引MySQL就一定会选，它是基于成本来选择执行计划，有时候全表扫描可能比用二级索引更快。

慢查询日志配置

生产环境排查问题，慢查询日志是第一手资料：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置阈值，执行时间超过 1 秒的 SQL 会被记录
SET GLOBAL long_query_time = 1;
-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

日志文件路径一般在/var/lib/mysql/xxx-slow.log，可以用mysqldumpslow工具分析

# 按照查询时间排序，取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/xxx-slow.log
# 按照扫描行数排序
mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log

不过现在一般都用云厂商提供的服务器了，面板上能直接查看和搜索慢SQL

大表优化策略

当单表数据量上来之后，光靠索引已经不够用了。MySQL单表数据太多的话，即使走索引，B+树层级也会变深，查询性能下降明显。
分页优化是大表场景的高频问题。LIMIT 1000000,10这种深分页会扫描前100万条数据然后丢弃，非常浪费。优化方案时用游标分页：

-- 深分页，性能差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 游标分页，性能好
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

冷热数据分离也很有效，把3个月的订单挪到历史表，主表只保留热数据，查询压力小很多。