如何进行SQL调优？

核心概念

SQL调优是通过分析慢查询、优化索引、改写SQL、调整配置等手段，提升数据库查询性能的系统性工程。需要遵循”先定位、再分析、后优化”的科学方法论。

---

一、问题定位（发现慢SQL）

1. 开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 设置慢查询阈值（2秒）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

2. 使用监控工具

• mysqldumpslow：分析慢查询日志
• pt-query-digest：Percona工具，生成详细报告
• 监控系统：Prometheus + Grafana、阿里云RDS等

3. 实时诊断

-- 查看当前正在执行的查询
SHOW PROCESSLIST;

-- 查看正在锁定的表
SHOW OPEN TABLES WHERE In_use > 0;

---

二、性能分析（找出问题根源）

1. 执行计划分析（EXPLAIN）

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

重点关注：

• type=ALL → 全表扫描
• key=NULL → 未使用索引
• Extra=Using filesort → 需要额外排序
• rows 扫描行数过大

2. 查看SQL实际执行情况

-- 查看SQL执行状态（8.0+）
EXPLAIN ANALYZE SELECT ...;

-- 查看索引使用情况
SHOW INDEX FROM orders;

---

三、优化策略（从高到低优先级）

🔥 优先级1：索引优化

（1）添加缺失索引

-- 单列索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 联合索引（注意顺序：等值查询 > 范围查询 > 排序）
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

（2）遵循索引设计原则

• 最左前缀原则：联合索引(a,b,c)可支持a、a+b、a+b+c查询
• 选择性高的列放前面：区分度越高，过滤效果越好
• 避免索引失效：

  -- ❌ 函数操作导致索引失效
  WHERE DATE(create_time) = '2025-11-02'
    
  -- ✅ 改写为范围查询
  WHERE create_time >= '2025-11-02 00:00:00' 
    AND create_time < '2025-11-03 00:00:00'
  

（3）利用覆盖索引

-- 查询字段都在索引中，避免回表
SELECT user_id, status FROM orders WHERE user_id = 1001;
-- 索引：(user_id, status)

---

🔥 优先级2：SQL改写

（1）避免SELECT *

-- ❌ 返回不必要的字段
SELECT * FROM orders WHERE id = 1001;

-- ✅ 只查询需要的字段
SELECT id, user_id, amount FROM orders WHERE id = 1001;

（2）优化分页查询

-- ❌ 深度分页性能差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 使用主键定位（延迟关联）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- ✅ 子查询优化
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

（3）优化JOIN查询

-- 确保关联字段有索引
-- 小表驱动大表（MySQL优化器会自动选择）
-- 避免JOIN字段类型不一致
ALTER TABLE orders MODIFY user_id INT UNSIGNED;

（4）拆分复杂查询

-- ❌ 复杂子查询
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE status = 1 AND level > 5
);

-- ✅ 拆分为两个简单查询（程序中处理）
-- 1. 先查符合条件的用户ID
-- 2. 再用ID列表查订单

---

🔥 优先级3：避免索引失效

场景	错误写法	正确写法
函数操作	WHERE YEAR(create_time)=2025	WHERE create_time >= '2025-01-01'
隐式转换	WHERE user_id = '1001'（字段是INT）	WHERE user_id = 1001
前缀模糊	WHERE name LIKE '%张三%'	WHERE name LIKE '张三%'或使用全文索引
OR条件	WHERE a=1 OR b=2（b无索引）	改为UNION或为b加索引
不等于	WHERE status != 1	WHERE status IN (0,2,3)
IS NOT NULL	WHERE name IS NOT NULL	设计时避免NULL或使用默认值

---

🔥 优先级4：数据库配置调优

-- 调整缓冲池大小（物理内存的60-80%）
innodb_buffer_pool_size = 8G

-- 调整连接数
max_connections = 500

-- 调整查询缓存（MySQL 8.0已移除）
-- query_cache_size = 128M

-- 调整日志刷盘策略
innodb_flush_log_at_trx_commit = 2  -- 性能优先
sync_binlog = 0                      -- 性能优先

---

🔥 优先级5：架构层面优化

（1）读写分离

// 主库写，从库读
@Transactional
public void createOrder() {
    masterDB.insert(order);  // 写主库
}

public Order queryOrder(Long id) {
    return slaveDB.select(id);  // 读从库
}

（2）分库分表

-- 按用户ID分表
orders_0001, orders_0002, ... orders_1024
-- 路由规则：user_id % 1024

（3）引入缓存

// Redis缓存热点数据
String cacheKey = "order:" + orderId;
Order order = redis.get(cacheKey);
if (order == null) {
    order = db.query(orderId);
    redis.set(cacheKey, order, 3600);
}

---

四、调优流程总结

1. 定位慢SQL
   ↓
2. EXPLAIN分析执行计划
   ↓
3. 识别问题（索引缺失/SQL不合理/配置不当）
   ↓
4. 制定优化方案（索引优化 > SQL改写 > 配置调整）
   ↓
5. 执行优化并验证效果
   ↓
6. 持续监控（避免新的慢SQL）

---

面试答题要点

1. 先监控后优化：通过慢查询日志、EXPLAIN等工具定位问题
2. 优先索引优化：80%的慢SQL是索引问题，低成本高收益
3. 避免过度优化：小表全表扫描可能比索引更快
4. 关注业务特点：电商大促、报表查询等场景需特殊处理
5. 架构兜底：单机优化到极限后，考虑分库分表、读写分离

---

总结

SQL调优遵循“定位→分析→索引→改写→配置→架构”六步法，核心是通过索引减少扫描行数，通过改写避免全表扫描，通过架构支撑海量数据。面试中能结合实际场景（如电商订单表优化）给出系统性方案，是高级工程师必备能力。