当前位置：首页 > ops >正文

SQL性能优化全攻略

ops 2025/8/19 21:09:37

以下是定位及优化SQL性能问题的系统方法，结合关键实践策略：

一、定位低效SQL语句

开启慢查询日志
配置参数记录执行超时的SQL（默认阈值10秒）：

SET GLOBAL slow_query_log = ON;  -- 开启日志
SET GLOBAL long_query_time = 2;  -- 设置超时阈值（秒）
SHOW VARIABLES LIKE 'slow_query_log%';  -- 查看配置

日志路径通过slow_query_log_file参数获取，分析其中记录的慢SQL。

实时监控进程列表
使用SHOW PROCESSLIST查看当前运行的SQL，锁定长时间处于"Execution"或"Sending data"状态的语句。
性能分析工具
- EXPLAIN分析：
```
EXPLAIN SELECT * FROM orders WHERE user_id = 100;  -- 查看执行计划
```
  关键字段解析：
  - type：访问类型（const > ref > range > index > ALL）
  - key：使用的索引
  - rows：扫描行数（越少越好）
  - Extra：避免出现Using filesort或Using temporary
- Profiling工具：
```
SET profiling = ON;
SELECT ...;  -- 执行目标SQL
SHOW PROFILE FOR QUERY 1;  -- 查看详细耗时
```

二、SQL优化核心策略

索引优化
- 避免索引失效场景：
  - 隐式类型转换（如WHERE varchar_col = 123）
  - OR条件未全覆盖索引（如WHERE a=1 OR b=2，若仅a有索引则失效）
  - 对索引列使用函数（如WHERE YEAR(create_time)=2023）
- 覆盖索引：
```
-- 未优化：需回表查数据
SELECT * FROM users WHERE age > 20; -- 优化：仅查索引覆盖字段
SELECT id, age FROM users WHERE age > 20;  -- 避免回表
```

查询语句优化

避免SELECT *
仅返回必要字段，减少网络传输与内存消耗。
用UNION ALL替代UNION
若结果允许重复，避免去重带来的性能损耗。

分页优化
深分页时避免LIMIT 10000,10：

-- 未优化：扫描10010行
SELECT * FROM logs LIMIT 10000, 10;-- 优化：通过主键关联
SELECT * FROM logs 
WHERE id >= (SELECT id FROM logs ORDER BY id LIMIT 10000, 1)
LIMIT 10;  -- 减少扫描量

结构设计优化
- 范式与反范式平衡：适当冗余高频查询字段，减少JOIN操作。
- 分区表/分库分表：对千万级大表按时间或哈希分区，分散I/O压力。
- 字段类型精简：如用INT而非VARCHAR存储数字，提升比较效率。

三、进阶优化手段

批量操作代替循环
单条插入改为批量处理，减少事务开销：

-- 低效
FOR i IN 1..1000 LOOPINSERT INTO orders VALUES (...);
END LOOP;-- 高效
INSERT INTO orders VALUES (...), (...), ...;  -- 单次提交

读写分离与缓存
- 读多写少场景配置主从分离，分散负载。
- 高频查询结果用Redis缓存，减轻数据库压力。
硬件与参数调优
- 调整innodb_buffer_pool_size至物理内存70%~80%，提升缓存命中率。
- SSD替代机械硬盘，优化I/O性能。

排查流程总结

graph LR
A[发现性能问题] --> B{{开启慢查询日志}}
B --> C[分析TOP慢SQL]
C --> D[EXPLAIN解析执行计划]
D --> E{索引是否有效？}
E -- 否 --> F[优化索引或重写SQL]
E -- 是 --> G{是否存在锁竞争？}
G -- 是 --> H[减少事务粒度/隔离级别]
G -- 否 --> I[考虑分库分表/缓存]