❝

大家好，今天咱们聊聊一个让无数后端同学头疼的问题：SQL慢查询优化。别看平时写SQL很轻松，一遇到性能问题就抓瞎！从3秒查询到3毫秒响应，从用户抱怨到老板夸奖，今天就手把手教你成为SQL优化高手！

前言：慢查询的"血泪史"

哎，说起SQL慢查询，估计每个后端都有一肚子苦水：

• 用户反馈页面卡死，一查发现某个查询跑了30秒还没结果
• 双11大促时数据库CPU飙到100%，原来是几个慢查询在"作妖"
• 老板要个数据报表，结果一个统计查询直接把数据库搞宕机了
• 明明数据量不大，为什么查询这么慢？

这些问题的根源其实就一个：没有掌握SQL优化的正确姿势！

SQL优化就像是给汽车调校发动机，调好了一路狂飙，调不好就只能慢慢爬坡。今天我就结合真实案例，教你如何让SQL查询"飞"起来！

第一步：认识慢查询这个"幕后黑手"

1.1 什么样的查询算慢？

首先咱们得有个判断标准，不能凭感觉说慢：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录到慢查询日志

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

一般来说：

• 1秒以内：正常，用户感觉不到延迟
• 1-3秒：稍慢，用户开始感觉不耐烦
• 3-10秒：很慢，用户想关页面了
• 10秒以上：超慢，用户已经在骂娘了

1.2 慢查询的常见"罪魁祸首"

让我先给大家总结下，90%的慢查询都是这几个原因：

1. 没有索引或索引失效 - 全表扫描是性能杀手
2. 索引设计不合理 - 建了索引但用不上
3. 查询语句写得有问题 - 让优化器"想歪了"
4. 数据量增长太快 - 昨天还挺快，今天就慢了
5. 表结构设计缺陷 - 一开始就埋了坑

第二步：SQL优化的"武器库"

2.1 EXPLAIN - 你的第一把"神器"

想优化SQL，先得知道它慢在哪。EXPLAIN就是你的透视镜：

-- 案例：一个典型的慢查询
EXPLAINSELECT
    u.username, 
    u.email, 
    p.title, 
    p.created_at 
FROMusers u 
JOIN posts p ON u.id = p.user_id 
WHERE u.created_at > '2023-01-01'
AND p.status = 'published'
ORDERBY p.created_at DESC
LIMIT20;

EXPLAIN结果关键字段解读：

看到这些就要小心了：

• type = ALL：全表扫描，性能杀手
• Extra = Using filesort：需要额外排序，很耗时
• Extra = Using temporary：使用临时表，内存杀手
• rows很大：扫描行数太多

2.2 索引优化 - 让查询"插上翅膀"

案例1：单列索引的威力

优化前：

-- 查询某个用户的订单，没有索引
SELECT * FROM orders WHERE user_id = 12345;

-- EXPLAIN 结果：
-- type: ALL, rows: 1000000, Extra: Using where
-- 耗时：2.5秒

优化后：

-- 给user_id加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 同样的查询
SELECT * FROM orders WHERE user_id = 12345;

-- EXPLAIN 结果：
-- type: ref, rows: 25, key: idx_user_id
-- 耗时：0.01秒

一个索引，性能提升了250倍！这就是索引的威力。

案例2：复合索引的"左前缀原则"

错误示例：

-- 建了复合索引但用不上
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 这样查询用不上索引
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2023-01-01';
-- 因为没有user_id作为前缀！

正确示例：

-- 按查询频率和选择性设计索引
CREATE INDEX idx_status_time_user ON orders(status, created_at, user_id);

-- 这些查询都能用上索引
SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2023-01-01';
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2023-01-01' AND user_id = 123;

复合索引设计口诀：

1. 等值查询的字段放前面
2. 范围查询的字段放中间
3. 排序字段放最后
4. 选择性高的字段优先

案例3：覆盖索引的极致优化

普通索引查询：

-- 普通索引，需要回表查询
CREATE INDEX idx_user_id ON orders(user_id);

SELECT order_id, user_id, total_amount 
FROM orders 
WHERE user_id = 12345;

-- 执行过程：索引查找 -> 回表查询 -> 返回结果
-- 耗时：0.05秒

覆盖索引优化：

-- 覆盖索引，包含所有需要的字段
CREATE INDEX idx_user_id_covering ON orders(user_id, order_id, total_amount);

SELECT order_id, user_id, total_amount 
FROM orders 
WHERE user_id = 12345;

-- 执行过程：索引查找 -> 直接返回结果（无需回表）
-- 耗时：0.01秒

覆盖索引的好处：

• ✅ 避免回表，减少IO操作
• ✅ 查询速度更快
• ❌ 索引占用更多存储空间

2.3 查询语句优化

案例1：避免SELECT *

不好的写法：

-- 查询所有字段，浪费IO和内存
SELECT * FROM users WHERE age > 25;

优化后：

-- 只查询需要的字段
SELECT id, username, email FROM users WHERE age > 25;

这样做的好处：

• 减少网络传输
• 节省内存使用
• 可能利用覆盖索引

案例2：WHERE条件优化

索引失效的写法：

-- 在字段上使用函数，索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2023;

-- 字段类型不匹配，索引失效
SELECT * FROM users WHERE phone = 13812345678; -- phone是varchar，但用了数字

-- 使用OR连接不同字段，部分索引失效
SELECT * FROM orders WHERE user_id = 123 OR status = 'completed';

正确的写法：

-- 使用范围查询代替函数
SELECT * FROM orders WHERE created_at >= '2023-01-01'AND created_at < '2024-01-01';

-- 保持数据类型一致
SELECT * FROMusersWHERE phone = '13812345678';

-- 用UNION ALL代替OR（当字段不同时）
SELECT * FROM orders WHERE user_id = 123
UNIONALL
SELECT * FROM orders WHEREstatus = 'completed'AND user_id != 123;

案例3：子查询 vs JOIN

慢的子查询：

-- 效率低下的子查询
SELECT u.username, u.email
FROM users u
WHERE u.id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
);

-- 执行时间：1.2秒

优化后的JOIN：

-- 使用JOIN提高效率
SELECT DISTINCT u.username, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 1000;

-- 执行时间：0.1秒

选择建议：

• 小数据量：子查询和JOIN差不多
• 大数据量：JOIN通常更快
• EXISTS比IN更高效（特别是子查询结果集很大时）

第三步：实战案例 - 3秒到3毫秒的神奇变化

案例背景

某电商系统的订单查询接口，用户反馈响应很慢。经过排查，发现一个查询语句平均耗时3秒，影响用户体验。

原始慢查询

-- 查询用户的订单列表，按时间倒序，支持多条件筛选
SELECT
    o.order_id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    u.username,
    u.phone,
    COUNT(oi.id) as item_count
FROM orders o
LEFTJOINusers u ON o.user_id = u.id
LEFTJOIN order_items oi ON o.order_id = oi.order_id
WHERE o.user_id = 12345
AND o.status IN ('pending', 'paid', 'shipped')
AND o.created_at >= '2023-01-01'
AND o.total_amount > 100
GROUPBY o.order_id, o.order_no, o.total_amount, o.status, o.created_at, u.username, u.phone
ORDERBY o.created_at DESC
LIMIT20;

-- 执行时间：3.2秒
-- 扫描行数：800万行

第一步：分析EXPLAIN结果

EXPLAIN SELECT /* 上面的查询 */;

-- 结果分析：
-- orders表：type=ALL, rows=2000000 (全表扫描！)
-- users表：type=eq_ref, rows=1 (这个还行)
-- order_items表：type=ALL, rows=5000000 (又是全表扫描！)
-- Extra: Using temporary; Using filesort (使用临时表和文件排序)

问题很明显：

1. orders表全表扫描200万行
2. order_items表全表扫描500万行
3. 需要临时表和文件排序

第二步：逐步优化

优化1：添加关键索引

-- 为orders表添加复合索引
CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, created_at, total_amount);

-- 为order_items表添加索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

优化后执行时间：0.8秒（提升了4倍）

优化2：重写查询，避免不必要的JOIN

-- 分析发现用户信息其实前端已经有了，不需要再查
-- 重写查询，去掉users表的JOIN
SELECT
    o.order_id,
    o.order_no,
    o.total_amount,
    o.status,
    o.created_at,
    COUNT(oi.id) as item_count
FROM orders o
LEFTJOIN order_items oi ON o.order_id = oi.order_id
WHERE o.user_id = 12345
AND o.status IN ('pending', 'paid', 'shipped')
AND o.created_at >= '2023-01-01'
AND o.total_amount > 100
GROUPBY o.order_id, o.order_no, o.total_amount, o.status, o.created_at
ORDERBY o.created_at DESC
LIMIT20;

优化后执行时间：0.2秒（又提升了4倍）

优化3：进一步优化索引

-- 发现order_items表的JOIN仍然较慢
-- 为order_items添加覆盖索引
CREATE INDEX idx_order_items_covering ON order_items(order_id, id);

-- 同时，为orders表创建更精确的覆盖索引
CREATE INDEX idx_orders_covering ON orders(
    user_id, 
    status, 
    created_at, 
    total_amount,
    order_id, 
    order_no
);

最终执行时间：0.003秒（3毫秒！）

第三步：优化效果对比

性能提升：1066倍！

第四步：SQL优化的"避坑指南"

坑1：盲目添加索引

-- ❌ 错误：给每个字段都加索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_total_amount ON orders(total_amount);

-- ✅ 正确：根据查询模式设计合理的复合索引
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at, total_amount);

为什么不要盲目加索引？

• 索引占用存储空间
• 影响INSERT/UPDATE/DELETE性能
• 维护成本高

坑2：忽略数据量增长

-- 今天查询很快
SELECT * FROM orders WHERE created_at >= '2023-12-01';
-- 扫描1000行，耗时10ms

-- 一个月后，数据量增长10倍
SELECT * FROM orders WHERE created_at >= '2023-12-01';
-- 扫描10000行，耗时100ms

解决方案：

• 定期监控慢查询
• 设置合理的分页大小
• 考虑数据归档策略

坑3：COUNT(*)的性能陷阱

-- ❌ 慢的计数查询
SELECTCOUNT(*) FROM orders WHEREstatus = 'pending';
-- 大表中可能扫描几百万行

-- ✅ 优化方案1：使用预计算
-- 建立计数表，定时更新
CREATETABLE order_stats (
    statusVARCHAR(20),
    countINT,
    updated_at TIMESTAMP
);

-- ✅ 优化方案2：使用近似计算
-- 对于展示用途，不需要精确计数
SELECTCOUNT(*) FROM orders 
WHEREstatus = 'pending'
AND created_at >= CURDATE() - INTERVAL30DAY;

坑4：ORDER BY + LIMIT的深分页陷阱

-- ❌ 深分页查询越来越慢
SELECT * FROM orders ORDERBY created_at DESCLIMIT100000, 20;
-- 需要排序前100020行，然后返回最后20行

-- ✅ 优化：使用游标分页
SELECT * FROM orders 
WHERE created_at < '2023-10-15 10:30:00'-- 上次查询的最后一个时间
ORDERBY created_at DESC
LIMIT20;

第五步：SQL优化的"进阶技巧"

技巧1：分区表优化

-- 对于大表，按时间分区
CREATETABLE orders (
    order_id BIGINT AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP,
    PRIMARY KEY (order_id, created_at)
) PARTITIONBYRANGE (YEAR(created_at)) (
    PARTITION p2021 VALUESLESSTHAN (2022),
    PARTITION p2022 VALUESLESSTHAN (2023),
    PARTITION p2023 VALUESLESSTHAN (2024),
    PARTITION p2024 VALUESLESSTHAN (2025)
);

-- 查询时会自动根据条件选择分区
SELECT * FROM orders 
WHERE created_at >= '2023-01-01'AND created_at < '2024-01-01';
-- 只会扫描p2023分区

技巧2：读写分离优化

// 查询走从库
@Select("SELECT * FROM orders WHERE user_id = #{userId}")
@DataSource("slave") // 指定从库
public List<Order> getOrdersByUserId(Long userId);

// 写操作走主库
@Insert("INSERT INTO orders(...) VALUES(...)")
@DataSource("master") // 指定主库
public void createOrder(Order order);

技巧3：缓存策略

@Service
publicclass OrderService {
    
    @Autowired
    private RedisTemplate redisTemplate;
    
    @Cacheable(value = "user:orders", key = "#userId")
    public List<Order> getUserOrders(Long userId) {
        // 复杂查询结果缓存到Redis
        return orderMapper.getUserOrders(userId);
    }
    
    @CacheEvict(value = "user:orders", key = "#order.userId")
    public void createOrder(Order order) {
        orderMapper.insert(order);
        // 清除相关缓存
    }
}

第六步：监控和维护

6.1 慢查询监控

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 记录超过100ms的查询

-- 定期分析慢查询日志
-- 使用mysqldumpslow工具分析
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

6.2 性能监控指标

@Component
public class SqlPerformanceMonitor {
    
    @EventListener
    public void onSlowQuery(SlowQueryEvent event) {
        if (event.getExecutionTime() > 1000) { // 超过1秒
            log.warn("慢查询告警: SQL={}, 耗时={}ms", 
                    event.getSql(), event.getExecutionTime());
            
            // 发送告警通知
            alertService.sendSlowQueryAlert(event);
        }
    }
}

6.3 定期优化检查清单

每月检查：

• [ ] 分析慢查询日志
• [ ] 检查索引使用率
• [ ] 监控数据库性能指标

每季度检查：

• [ ] 评估表结构设计
• [ ] 清理无用索引
• [ ] 考虑数据归档

年度检查：

• [ ] 整体架构优化
• [ ] 分库分表评估
• [ ] 技术栈升级

总结

SQL优化说起来复杂，但掌握了正确的方法，其实也就那么回事。核心思路就是：

1. 先发现问题 - 用EXPLAIN分析，找出性能瓶颈
2. 对症下药 - 加索引、改SQL、调架构
3. 持续监控 - 定期检查，防患于未然

优化的优先级：

1. 索引优化 - 投入产出比最高，立竿见影
2. SQL重写 - 改善查询逻辑，减少计算量
3. 架构优化 - 分库分表、读写分离等
4. 硬件升级 - 最后的大招，但治标不治本

最后给大家一个建议：不要等到出问题了才优化！在设计阶段就要考虑性能，写SQL的时候就要想着索引，这样才能防患于未然。

记住：好的SQL不是写出来的，是"调"出来的！

阅读原文：原文链接