1. 慢SQL问题排查的基本思路
遇到SQL执行缓慢的情况，我们通常会按照以下步骤进行系统性排查：
- 确认问题现象：记录SQL执行的具体耗时，区分是偶发性慢还是持续性慢
- 获取执行计划：通过EXPLAIN等工具分析SQL的执行路径
- 检查表结构和索引：确认表设计是否合理，索引是否有效
- 分析数据分布：查看相关字段的数据分布情况
- 评估系统资源：检查数据库服务器CPU、内存、IO等资源使用情况
注意：在开始优化前，务必先备份相关数据和SQL语句，避免优化过程中产生意外影响。
2. 执行计划深度解析
执行计划是分析SQL性能问题的核心工具，它能告诉我们数据库引擎如何处理这条SQL语句。
2.1 如何获取执行计划
在MySQL中，我们可以使用EXPLAIN命令：
EXPLAIN SELECT * FROM orders WHERE user_id = 100;在PostgreSQL中，除了基本EXPLAIN外，还可以使用：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;2.2 执行计划关键字段解读
执行计划结果中，以下几个字段特别值得关注：
| 字段名 | 含义 | 优化关注点 |
|---|---|---|
| type | 访问类型 | 最好能达到const/ref级别，避免ALL全表扫描 |
| key | 实际使用的索引 | 检查是否使用了预期索引 |
| rows | 预估扫描行数 | 与实际数据量对比，评估准确性 |
| Extra | 额外信息 | 注意Using filesort、Using temporary等警告 |
2.3 执行计划案例分析
假设我们有以下慢SQL：
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.register_time > '2023-01-01' ORDER BY o.create_time DESC;一个可能的问题执行计划显示：
- users表使用了register_time索引
- orders表进行了全表扫描
- 存在Using filesort排序操作
这表明我们需要在orders.user_id和orders.create_time上建立合适的索引。
3. 索引优化实战技巧
3.1 索引设计原则
- 最左前缀原则：联合索引(a,b,c)只能用于a、a,b或a,b,c条件的查询
- 选择性原则：高选择性的列(不同值多)更适合建索引
- 覆盖索引：索引包含查询所需的所有字段，避免回表
- 避免过多索引：每个索引都会增加写入开销
3.2 常见索引失效场景
对索引列使用函数或运算：
-- 索引失效 SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';隐式类型转换：
-- user_id是varchar类型时索引失效 SELECT * FROM users WHERE user_id = 100;使用LIKE以通配符开头：
-- 索引失效 SELECT * FROM users WHERE name LIKE '%张%';OR条件使用不当：
-- 如果age无索引，整个查询索引失效 SELECT * FROM users WHERE user_id = 100 OR age > 20;
3.3 多表连接优化
对于多表连接查询，特别注意：
- 连接顺序：小表驱动大表，减少中间结果集
- 连接字段：确保连接字段有索引且类型一致
- 连接方式：理解INNER JOIN、LEFT JOIN等不同连接的性能差异
优化示例：
-- 优化前 SELECT * FROM large_table l JOIN small_table s ON l.id = s.large_id; -- 优化后：明确小表在前 SELECT * FROM small_table s JOIN large_table l ON s.large_id = l.id;4. SQL语句编写最佳实践
4.1 避免SELECT *
使用SELECT * 会导致：
- 读取不必要的列，增加IO负担
- 可能使覆盖索引失效
- 网络传输数据量增大
应该明确指定需要的字段：
-- 不推荐 SELECT * FROM users WHERE ...; -- 推荐 SELECT id, name, email FROM users WHERE ...;4.2 LIMIT分页优化
常见分页问题：
-- 深度分页性能差 SELECT * FROM orders ORDER BY id LIMIT 100000, 20;优化方案：
- 使用索引覆盖扫描
- 记录上次查询的最大ID
- 使用子查询优化
优化后的SQL：
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;4.3 批量操作替代循环
避免在应用层循环执行单条SQL：
// 不推荐 for (Long id : idList) { userDao.updateStatus(id, status); }应该使用批量操作：
-- 推荐 UPDATE users SET status = 1 WHERE id IN (1,2,3,...);5. 数据库系统层面优化
5.1 参数调优
几个关键配置参数：
- innodb_buffer_pool_size：InnoDB缓冲池大小，建议设为物理内存的50-70%
- innodb_log_file_size：redo日志大小，影响写入性能
- max_connections：最大连接数，避免连接过多导致资源耗尽
5.2 监控与诊断工具
慢查询日志：记录执行时间超过阈值的SQL
-- 启用慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 超过1秒的查询性能模式(Performance Schema)：提供详细的性能指标
sys schema：MySQL提供的诊断视图
5.3 定期维护
统计信息更新：确保优化器有准确的数据分布信息
ANALYZE TABLE users;索引重建：解决索引碎片问题
ALTER TABLE users ENGINE=InnoDB;历史数据归档：将不常用的数据迁移到归档表
6. 实战案例分析
6.1 案例一：电商订单查询优化
原始SQL：
SELECT * FROM orders WHERE user_id = 100 AND status = 'completed' ORDER BY create_time DESC LIMIT 0, 20;问题分析：
- 虽然user_id有索引，但status过滤性差
- 排序操作导致filesort
优化方案：
- 建立(user_id, status, create_time)联合索引
- 改写SQL：
SELECT id, order_no, amount, create_time FROM orders WHERE user_id = 100 AND status = 'completed' ORDER BY create_time DESC LIMIT 0, 20;6.2 案例二：报表查询优化
原始SQL：
SELECT COUNT(*) as total, product_id FROM order_items WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY product_id ORDER BY total DESC LIMIT 10;问题分析：
- 需要扫描大量数据
- 分组排序操作消耗资源
优化方案：
- 建立(create_time, product_id)联合索引
- 使用物化视图预计算
- 考虑使用专门的OLAP系统处理报表
7. 高级优化技巧
7.1 索引下推优化
MySQL 5.6引入的索引条件下推(ICP)特性，可以在存储引擎层提前过滤数据。通过以下参数控制：
SET optimizer_switch = 'index_condition_pushdown=on';7.2 并行查询
对于大表查询，可以考虑使用并行查询特性（MySQL 8.0+）：
SELECT /*+ PARALLEL(4) */ * FROM large_table WHERE ...;7.3 查询重写
有时候通过等价改写SQL可以获得更好的执行计划：
-- 原始SQL SELECT * FROM users WHERE status = 1 OR status = 2; -- 改写为 SELECT * FROM users WHERE status IN (1, 2); -- 或者 SELECT * FROM users WHERE status = 1 UNION ALL SELECT * FROM users WHERE status = 2;在实际工作中，我发现很多性能问题都是由于开发人员不了解数据库工作原理导致的。通过系统地学习执行计划解读、索引原理和SQL优化技巧，可以解决大部分常见的慢SQL问题。对于特别复杂的查询，有时候重构业务逻辑或引入缓存机制可能是更彻底的解决方案。
