MySQL SQL性能调优实战指南

# MySQL SQL性能调优实战指南

一、核心思想：系统化调优四步法

SQL调优不是盲目加索引，而是一个系统化工程，应遵循 测量 -> 分析 -> 优化 -> 验证 的闭环流程。

测量：通过慢查询日志、监控系统定位需要优化的慢SQL。
分析：使用EXPLAIN等工具分析SQL的执行计划，找到性能瓶颈。
优化：根据分析结果，采取索引优化、SQL重写、架构调整等手段。
验证：在测试环境验证优化效果，并上线监控。

二、性能诊断与分析工具

1. 慢查询日志

作用：记录执行时间超过阈值的SQL语句。

配置：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置阈值（如0.1秒）
SET GLOBAL long_query_time = 0.1;
-- 查看日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

2. EXPLAIN 执行计划分析

作用：查看MySQL如何执行你的SQL，是调优的核心工具。
关键字段解读：
- type：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。
  - ALL：全表扫描，性能最差，必须优化。
  - range：范围扫描，使用了索引，常见于BETWEEN、>、LIKE 'abc%'。
  - ref：非唯一性索引查找。
- possible_keys：可能用到的索引。
- key：实际使用的索引。
- rows：MySQL估计需要扫描的行数，这个值越小越好。
- Extra：额外信息，关键提示：
  - Using index：覆盖索引，查询的列都在索引中，无需回表，性能高。
  - Using where：需要回表过滤数据。
  - Using filesort：文件排序，需要优化（通常需要为ORDER BY字段建立索引）。
  - Using temporary：使用临时表，常见于GROUP BY或DISTINCT，性能差。

使用方式：

EXPLAIN SELECT * FROM users WHERE age > 25;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;  -- 更详细的结构化信息
-- MySQL 8.0+ 可以查看实际执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

三、索引优化策略（核心）

索引是提升查询性能的关键，但也是一把双刃剑（占用空间、影响写入性能）。

1. 索引设计原则

高选择性字段优先：字段的唯一性越高，索引效果越好。

-- 计算选择性：值越接近1，选择性越高
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM orders;

最左前缀原则：复合索引 (a, b, c) 会创建 (a), (a,b), (a,b,c) 三个索引。
- 查询条件必须包含索引的最左列，否则索引失效。
- 索引列顺序：等值查询放前面，范围查询放后面。
避免冗余索引：尽量复用现有索引，删除无用索引。
```
-- 查看表上的索引
SHOW INDEX FROM table_name;
```

2. 索引优化实战

覆盖索引：查询的列都在索引中，避免回表。

-- 假设有复合索引 idx_status_covering(status, user_id, order_no)
SELECT user_id, order_no FROM orders WHERE status = 'pending';  -- 走覆盖索引

范围查询优化：

-- 原始（慢）：索引可能只用到create_time
CREATE INDEX idx_time ON orders(create_time);
SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31' AND status = 'completed';

-- 优化后：将等值条件放在前面
CREATE INDEX idx_status_time ON orders(status, create_time);

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';

隐式类型转换：数据类型不匹配。

-- ❌ 失效（phone是varchar类型）
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 优化
SELECT * FROM users WHERE phone = '13800138000';

前导通配符：LIKE以%开头。

-- ❌ 失效
SELECT * FROM users WHERE name LIKE '%john%';
-- ✅ 可能有效（后缀通配符）
SELECT * FROM users WHERE name LIKE 'john%';

OR条件：如果OR连接的条件中有一个没有索引，整个查询都可能不走索引。
NOT IN 或 !=：可能导致索引失效（或效果变差）。

四、SQL语句优化技巧

1. 查询重写

用JOIN代替子查询：子查询会创建临时表，JOIN通常性能更好。

-- ❌ 低效的子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level > 3);
-- ✅ 高效JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip_level > 3;

用EXISTS代替IN：当外表数据量大时，EXISTS可能更优。
避免SELECT *：只查询需要的字段，特别是对于TEXT/BLOB等大字段。
HAVING改为WHERE：HAVING用于分组后过滤，尽量将过滤条件放在WHERE中提前过滤。

2. 分页查询优化

问题：LIMIT 1000000, 20 会扫描100万条记录，性能极差。

方案一：游标分页：基于有序且唯一的列（如自增ID）进行翻页。

-- 每次记录上一页最后一条的ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

方案二：延迟关联：先通过覆盖索引查询出主键，再回表获取其他字段。

SELECT * FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

3. 大数据量处理

分批处理：避免一次性操作大量数据，应分批次循环处理。

-- 每次处理1000条
UPDATE orders SET status = 'processed' WHERE status = 'pending' LIMIT 1000;

分区表：对于历史数据，可以使用分区表进行物理隔离，按时间或键值分区。

CREATE TABLE logs (
    id BIGINT,
    log_date DATE
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

五、数据库架构与配置优化

1. 架构优化

读写分离：主库负责写入，从库负责读取，分散压力。
分库分表：当单表数据量过大（如超过千万级）时，垂直分库（按业务拆分）或水平分表（按ID取模）。
引入缓存：热点数据（如用户信息、商品详情）使用Redis等缓存，减少数据库压力。

2. 配置参数调优（关键）

在my.cnf或my.ini中调整：

InnoDB缓冲池（最重要）：设置为物理内存的50%-80%。
```
innodb_buffer_pool_size = 16G
```
日志缓冲：
```
innodb_log_buffer_size = 64M
```
连接数：
```
max_connections = 1000
```

排序/临时表：

sort_buffer_size = 2M
tmp_table_size = 64M

六、实战调优案例：电商订单查询

场景

查询用户近3个月内状态为pending或shipped的订单，按时间倒序取前20条。

SELECT * FROM orders 
WHERE user_id = 100 
AND status IN ('pending', 'shipped') 
AND create_time BETWEEN '2024-01-01' AND '2024-03-01'
ORDER BY create_time DESC 
LIMIT 20;

问题分析

原表可能没有索引，导致全表扫描。
ORDER BY导致Using filesort。

优化步骤

分析：EXPLAIN 显示 type=ALL，Extra=Using where; Using filesort。
建立复合索引：遵循最左前缀原则，将等值查询的字段user_id和status放在前面，范围查询字段create_time放在后面，且确保排序的字段也在索引中。
```
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
```

改写查询：利用覆盖索引，只查需要的字段，减少回表。

SELECT id, user_id, order_no, amount, create_time FROM orders 
WHERE user_id = 100 
AND status IN ('pending', 'shipped') 
AND create_time BETWEEN '2024-01-01' AND '2024-03-01'
ORDER BY create_time DESC 
LIMIT 20;

验证：再次EXPLAIN，type变为range或ref，rows大幅减少，Extra中不再有Using filesort，且出现Using index（覆盖索引）。

七、调优检查清单

[ ] 慢查询日志：是否已开启并定期分析？
[ ] 索引设计：高频查询条件是否有索引？复合索引顺序是否合理？
[ ] 索引失效：是否对索引列使用了函数、计算、隐式转换？
[ ] SQL写法：是否避免了SELECT *？分页是否使用了大偏移量？
[ ] 表结构：字段类型是否选择合适（如能用int就不用varchar）？
[ ] 架构层面：读写分离、缓存、分库分表是否按需引入？
[ ] 配置参数：缓冲池大小是否合理？连接数是否充足？

总结

MySQL SQL调优的核心是通过EXPLAIN理解SQL的执行路径，并利用索引减少需要扫描的数据行数。它是一个持续优化的过程，需要结合业务场景、数据规模和系统架构来综合考量。记住口诀：测量先行，索引为王，SQL避坑，架构护航。