SQL查询性能优化排查指南

当 SQL 查询性能较差时，可以通过以下系统化的步骤进行排查和优化：

一、分析执行计划（最核心步骤）

1. 使用 EXPLAIN / EXPLAIN ANALYZE
   通过数据库的解释计划工具查看优化器选择的执行路径：

   EXPLAIN SELECT * FROM orders WHERE user_id = 100;
   -- PostgreSQL 支持更详细的 ANALYZE
   EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;
   

2. 关键关注点：

   • 访问类型（type in MySQL / scan type in PostgreSQL）：
     • ALL 表示全表扫描（警惕！）
     • index 表示全索引扫描
     • range 或 ref 表示较好的索引使用
   • 索引命中（key 字段）：是否使用了预期索引
   • 扫描行数（rows 字段）：估算的扫描数据量
   • 额外操作（Extra 字段）：
     • Using filesort：需要内存或磁盘排序
     • Using temporary：使用临时表（常见于 GROUP BY）

二、索引优化

1. 检查索引是否存在

   SHOW INDEX FROM orders;  -- MySQL
   \d+ orders               -- PostgreSQL
   

2. 常见索引问题：

   • 缺失索引：WHERE/JOIN/ORDER BY 涉及的字段无索引
   • 索引失效场景：
     • 对索引字段进行函数操作（WHERE YEAR(create_time) = 2023）
     • 隐式类型转换（WHERE user_id = '123'，user_id 为整型）
     • 联合索引未遵循最左前缀原则
   • 冗余索引：多个索引包含相同前缀字段

3. 索引优化技巧：

   • 对高频查询字段添加组合索引（注意字段顺序）
   • 使用覆盖索引（索引包含所有查询字段）
   • 对大文本字段考虑前缀索引（INDEX(email(10))）

三、查询语句优化

1. 减少数据量：

   • 避免 SELECT *，明确指定所需字段
   • 添加合理的 LIMIT 分页限制
   • 使用 WHERE 提前过滤数据

2. 优化复杂操作：

   • 将 IN 改为 EXISTS（数据量大时）
   • 减少子查询嵌套，改用 JOIN
   • 避免 DISTINCT、ORDER BY 非必要排序

3. 分页优化：

   -- 低效写法（OFFSET 大时）
   SELECT * FROM orders LIMIT 10 OFFSET 100000;
   
   -- 优化方案：使用游标分页（记录上次查询的ID）
   SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
   

四、数据库与系统资源检查

1. 监控数据库状态：

   SHOW STATUS LIKE 'Handler_read%';  -- MySQL 索引命中率
   SHOW PROCESSLIST;                 -- 查看当前运行查询
   

2. 系统资源瓶颈：

   • CPU：长期高于 80% 需检查是否大量计算操作
   • 内存：缓冲池命中率（innodb_buffer_pool in MySQL）
   • 磁盘 I/O：检查慢日志中 Rows_examined 与 Rows_sent 比例

3. 锁竞争：

   • 检查是否被阻塞（SHOW ENGINE INNODB STATUS）
   • 长事务导致锁持有时间过长

五、高级工具辅助

1. 开启慢查询日志：

   # MySQL 配置（my.cnf）
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/slow.log
   long_query_time = 2  # 记录超过2秒的查询
   

2. 使用性能分析工具：

   • Percona Toolkit：pt-query-digest 分析慢日志
   • pg_stat_statements（PostgreSQL）：统计高频查询

六、其他优化方向

• 数据归档：将历史数据迁移到归档表
• 读写分离：将报表类查询转移到备库
• 硬件升级：SSD 替换机械硬盘、增加内存

排查流程图

通过以上步骤，可以系统化地定位 SQL 性能瓶颈。建议优先从执行计划和索引优化入手，80% 的性能问题可通过索引解决。