MySQL慢查询分析｜线上SQL优化三板斧（实战案例10s优化至200ms）

白茶清欢

498人浏览 · 2026-05-28 09:14:58

白茶清欢 · 2026-05-28 09:14:58 发布

前言

90%线上接口卡顿、请求超时、数据库CPU飙升等问题，根源都来自慢SQL。
对于后端开发者而言：线上的慢SQL，就是技术进阶的绝佳机会。

排查并优化慢SQL，通用流程可总结为一套「优化三板斧」：

开启慢查询日志，捕获执行缓慢的SQL
使用 EXPLAIN 分析执行计划，定位性能瓶颈
针对性优化：新增索引、改写SQL、热点数据加缓存

一、什么是慢查询

MySQL 会自动记录执行时长超过指定阈值的SQL语句，统一存储在慢查询日志中。
全表扫描、大偏移分页、无索引查询等低效SQL，都会被日志捕获，帮助我们快速定位线上问题。

生产环境常用标准：默认阈值为10秒，线上建议设置为0.5秒，尽早发现隐患。

二、第一步：开启慢查询日志

2.1 查看当前慢查询配置

-- 查看慢查询日志是否开启
show variables like 'slow_query_log';

-- 查看慢查询时间阈值（单位：秒）
show variables like 'long_query_time';

2.2 临时开启（MySQL重启后失效）

适合临时排查线上问题，无需修改配置文件。

-- 开启慢查询日志
set global slow_query_log = 1;

-- 设置超时阈值：执行超过0.5秒即记录为慢SQL
set global long_query_time = 0.5;

2.3 永久开启（修改配置文件）

修改MySQL核心配置文件 my.ini / my.cnf，重启MySQL服务永久生效。

# 开启慢查询日志
slow_query_log = ON
# 慢查询时间阈值
long_query_time = 0.5
# 慢查询日志存储路径
slow_query_log_file = /usr/local/mysql/slow.log

2.4 慢日志查看方式

直接读取服务器日志文件
官方工具：mysqldumpslow 统计分析慢日志
可视化工具：Navicat、DBeaver、DataGrip 图形化查看

三、第二步：EXPLAIN 执行计划（核心排查手段）

抓到慢SQL后，不要盲目加索引，优先使用 EXPLAIN 分析SQL的执行逻辑，判断是否走索引、是否存在全表扫描。

3.1 基本使用语法

-- 在正常SQL前加上 EXPLAIN 即可查看执行计划
explain select * from `order` where user_id = 1001;

3.2 核心字段详解（面试&工作高频）

type（重中之重）：SQL访问类型，直接决定查询性能。
key：SQL最终实际使用的索引，为空则表示未使用任何索引。
rows：MySQL预估需要扫描的数据行数，数值越小，查询效率越高。
Extra：额外执行信息，Using filesort（文件排序）、Using temporary（临时表）都是典型性能问题。

type 访问类型性能排序

核心要求：生产环境严禁出现 type=ALL 全表扫描。

四、第三步：慢SQL通用优化方案（三板斧）

整体优化流程如下：

4.1 方案一：新增合理索引

给 WHERE 条件字段建立索引
ORDER BY、GROUP BY 排序/分组字段，优先加入复合索引
严格遵循最左前缀原则设计复合索引

4.2 方案二：改写低效SQL

杜绝 SELECT *，只查询业务必需字段
避免左模糊、全模糊查询 LIKE '%关键词'，防止索引失效
大偏移量分页 LIMIT 100000,10 改为主键分页
索引字段不做运算、不发生隐式类型转换

4.3 方案三：热点数据增加Redis缓存

针对查询频繁、数据变动少的热点数据，使用Redis缓存分担数据库压力，彻底避免重复执行慢SQL。
执行逻辑：先查缓存，缓存无数据再查数据库；数据更新同步删除缓存。

缓存执行流程

Java 完整缓存代码示例

import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;

@Service
public class OrderService {

    // 注入Redis工具类
    @Resource
    private RedisUtil redisUtil;

    // 数据层
    @Resource
    private OrderMapper orderMapper;

    // 缓存Key前缀
    private static final String ORDER_CACHE_KEY = "order:user:";
    // 缓存过期时间：1小时
    private static final long CACHE_EXPIRE = 3600;

    /**
     * 根据用户ID查询订单列表（缓存+数据库双层查询）
     */
    public List<Order> getOrderList(Long userId, Long lastId, Integer pageSize) {
        // 1. 拼接缓存Key
        String cacheKey = ORDER_CACHE_KEY + userId + ":" + lastId;
        // 2. 优先查询Redis缓存
        List<Order> orderList = redisUtil.getList(cacheKey);
        if (orderList != null && !orderList.isEmpty()) {
            return orderList;
        }
        // 3. 缓存为空，查询数据库（优化后的SQL）
        orderList = orderMapper.selectOrderByPage(userId, lastId, pageSize);
        // 4. 查询结果写入缓存
        redisUtil.setList(cacheKey, orderList, CACHE_EXPIRE);
        return orderList;
    }

    /**
     * 更新订单后，删除对应缓存，防止脏数据
     */
    public void updateOrder(Order order) {
        // 1. 更新数据库
        orderMapper.updateOrder(order);
        // 2. 清除该用户下所有订单缓存
        String cacheKey = ORDER_CACHE_KEY + order.getUserId() + ":*";
        redisUtil.deleteKeys(cacheKey);
    }
}

五、线上真实优化案例（10秒 → 200毫秒）

5.1 业务背景

电商后台订单查询接口，初期未做性能优化，接口平均耗时 10秒以上，频繁触发超时告警，严重影响运营人员使用。

5.2 问题排查流程

查看慢查询日志：该订单查询SQL被持续标记为慢SQL
执行 EXPLAIN 分析：type=ALL 全表扫描，同时出现 Using filesort 文件排序
定位根因：无复合索引、使用大偏移分页、SELECT * 查询冗余字段、高频重复查询加重数据库压力

5.3 优化前：低效SQL

-- 问题：全表扫描 + 多余字段 + 文件排序 + 大偏移分页
SELECT * FROM `order` 
WHERE user_id = 2000 
AND status = 1 
ORDER BY create_time DESC 
LIMIT 80000,20;

5.4 优化动作（组合使用三板斧）

移除 SELECT *，只保留业务需要的字段
建立复合索引，解决全表扫描与文件排序问题
大偏移分页改造为主键分页
热门查询数据添加Redis缓存，设置1小时过期时间，拦截重复慢查询

5.5 优化后：高效SQL

-- 1. 创建复合索引（查询条件 + 排序字段）
CREATE INDEX idx_user_status_time ON `order`(user_id,status,create_time);

-- 2. 改写分页SQL
SELECT id,order_no,status,create_time 
FROM `order` 
WHERE user_id = 2000 AND status = 1 AND id > 80000
ORDER BY create_time DESC 
LIMIT 20;