SQL性能优化全攻略

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

一、定位低效SQL语句

1. 开启慢查询日志
   配置参数记录执行超时的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。

2. 实时监控进程列表
   使用SHOW PROCESSLIST查看当前运行的SQL，锁定长时间处于"Execution"或"Sending data"状态的语句。

3. 性能分析工具

   • 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优化核心策略

1. 索引优化

   • 避免索引失效场景：
     • 隐式类型转换（如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;  -- 避免回表
     

2. 查询语句优化

   • 避免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;  -- 减少扫描量
     

3. 结构设计优化

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

三、进阶优化手段

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

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

2. 读写分离与缓存

   • 读多写少场景配置主从分离，分散负载。
   • 高频查询结果用Redis缓存，减轻数据库压力。

3. 硬件与参数调优

   • 调整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[考虑分库分表/缓存]

注：持续监控是优化核心，推荐使用Percona Toolkit等工具定期分析慢查询模式。