系统讲解线上慢SQL排查的四步黄金流程_MsSqlserver

"先止损 - 再定位 - 后根治 - 防复发" 的四步黄金流程

第一步：紧急止损（线上第一优先级）

线上出问题，先保服务可用，再慢慢排查根因！

确认影响范围：通过 apm 工具（skywalking/pinpoint）定位慢接口，评估用户影响面
快速熔断降级：核心接口被打挂时，先降级非核心功能或开启限流
kill 异常慢查询：登录数据库执行show full processlist;找到耗时 > 10s 的线程，kill [id];
流量切分：读压力大时切到从库；写压力大时暂时关闭非核心写操作

第二步：精准定位问题（核心环节）

数据库层面排查（80% 的问题在这里）

-- 1. 查看当前运行的所有线程，快速定位慢sql
show full processlist;

-- 2. 查看慢查询日志（必须提前开启，阈值建议设为1s）
show variables like 'slow_query_log';
show variables like 'long_query_time';

-- 3. 分析执行计划（最关键！）
explain select * from user where name = '张三';
-- 重点关注：type(是否range/ref以上)、key(是否走索引)、rows(扫描行数)、extra(是否有using filesort/using temporary)

-- 4. 查看数据库状态
show status like 'threads_connected'; -- 连接数
show engine innodb status; -- 查看事务和锁等待情况

应用层面排查（剩下 20% 的坑在这里）

有没有n+1 查询：循环调用数据库（如查订单列表后循环查每个订单的用户）
有没有大分页查询：limit 1000000, 10 会扫描前 100 万行，性能极差
有没有长事务 / 大事务：占用连接资源，导致其他请求排队
有没有参数配置问题：连接池最大连接数太小，或 innodb_buffer_pool_size 不足

第三步：针对性根治问题

常见慢 sql 原因 & 解决方案对照表

常见原因	典型场景	解决方案
全表扫描	where 条件无索引或索引失效	创建合适的联合索引，遵循最左前缀原则
索引失效	索引列用函数 / 运算、隐式类型转换、like '% xxx'	避免在 where 子句中对索引列做操作，使用覆盖索引
大分页查询	limit offset 过大	改用游标分页：`where id > last_id limit 10`
n+1 查询	循环查询关联表	改用批量查询`where id in (...)`或关联查询
大事务	一个事务包含多个操作甚至 rpc 调用	拆分大事务，将非数据库操作移出事务
锁等待	长事务持有行锁导致其他请求阻塞	优化事务粒度，避免在事务中做耗时操作

进阶优化方案

读写分离：读请求走从库，写请求走主库
分库分表：单表数据量超过 1000 万时考虑水平分表
缓存热点数据：用 redis 缓存高频访问数据，减少数据库压力

第四步：防复发（建立长效机制）

上线前 sql 审核：用工具（阿里 druid、sonarqube）自动检测慢 sql，禁止不合格 sql 上线
压测验证：上线前做全链路压测，模拟真实流量发现潜在问题
完善监控告警：配置慢查询告警（超过 1s 自动报警），监控数据库 cpu、io、连接数等指标
定期巡检：每月做一次数据库性能巡检，清理无用索引，优化表结构

整体排查流程图

面试加分项

提到实际踩过的坑：比如 "我之前遇到过隐式类型转换导致的索引失效，varchar 字段传了 int 值导致全表扫描，改成传字符串后性能提升了上百倍"
提到具体工具：skywalking 链路追踪、druid 连接池监控、explain 执行计划分析
提到架构层面的解决方案：读写分离、分库分表、多级缓存
提到预防措施：说明你不仅会解决问题，还能从流程上避免问题再次发生

核心代码与技术亮点

springboot+druid 慢 sql 自动监控与拦截（必配）

技术亮点：无需侵入业务代码，自动采集慢 sql 并告警，提前发现潜在问题

@configuration
public class druidconfig {
    @bean
    @configurationproperties("spring.datasource.druid")
    public datasource druiddatasource() {
        druiddatasource datasource = new druiddatasource();
        // 开启慢sql统计，阈值1秒
        datasource.setslowsqlmillis(1000);
        datasource.setlogslowsql(true);
        // 开启wallfilter，拦截全表扫描、恶意sql
        datasource.setfilters("stat,wall,log4j2");
        return datasource;
    }
    // 注册druid监控面板
    @bean
    public servletregistrationbean<statviewservlet> statviewservlet() {
        servletregistrationbean<statviewservlet> bean = new servletregistrationbean<>(
                new statviewservlet(), "/druid/*");
        bean.addinitparameter("loginusername", "admin");
        bean.addinitparameter("loginpassword", "123456");
        return bean;
    }
    // 自定义慢sql告警器（生产级）
    @bean
    public slowsqllistener slowsqllistener() {
        return new slowsqllistener() {
            @override
            public void onslowsql(slowsqlevent event) {
                string sql = event.getsql();
                long timemillis = event.gettimemillis();
                // 超过3秒直接发钉钉/企业微信告警
                if (timemillis > 3000) {
                    dingtalkalertutil.sendalert(
                            "【严重慢sql告警】\nsql: " + sql + "\n耗时: " + timemillis + "ms\n数据源: " + event.getdatasourcename()
                    );
                }
            }
        };
    }
}

大分页查询优化（性能提升 1000 倍）

技术亮点：解决limit 1000000, 10全表扫描问题，支持任意深度分页

// ❌ 错误写法：扫描前100万行，性能极差
list<user> badpage(int pagenum, int pagesize) {
    int offset = (pagenum - 1) * pagesize;
    return usermapper.selectpage(offset, pagesize);
}
// ✅ 正确写法：游标分页（基于自增id）
list<user> goodpage(long lastid, int pagesize) {
    // 只扫描pagesize行，性能恒定
    return usermapper.selectbycursor(lastid, pagesize);
}
// 对应的sql
@select("select id, name, phone from user where id > #{lastid} order by id asc limit #{pagesize}")
list<user> selectbycursor(@param("lastid") long lastid, @param("pagesize") int pagesize);

n+1 查询问题根治（批量查询替代循环查询）

技术亮点：将 n 次数据库调用合并为 1 次，减少网络 io 和连接开销

// ❌ 错误写法：n+1查询（1次查订单列表，n次查用户）
list<ordervo> badgetorderlist() {
    list<order> orders = ordermapper.selectall();
    return orders.stream().map(order -> {
        ordervo vo = new ordervo();
        beanutils.copyproperties(order, vo);
        // 循环查询用户，n次数据库调用
        user user = usermapper.selectbyid(order.getuserid());
        vo.setusername(user.getname());
        return vo;
    }).collect(collectors.tolist());
}
// ✅ 正确写法：批量查询（1次查订单，1次查所有用户）
list<ordervo> goodgetorderlist() {
    list<order> orders = ordermapper.selectall();
    // 提取所有用户id，批量查询
    set<long> userids = orders.stream().map(order::getuserid).collect(collectors.toset());
    map<long, user> usermap = usermapper.selectbyids(userids).stream()
            .collect(collectors.tomap(user::getid, function.identity()));
    return orders.stream().map(order -> {
        ordervo vo = new ordervo();
        beanutils.copyproperties(order, vo);
        vo.setusername(usermap.get(order.getuserid()).getname());
        return vo;
    }).collect(collectors.tolist());
}

热点行更新优化（秒杀 / 库存扣减场景）

技术亮点：用分段锁解决单条记录行锁竞争问题，qps 提升 50 倍以上

// ❌ 错误写法：单条记录锁竞争激烈，数据库cpu飙升
@transactional
public boolean deductstock(long productid, integer num) {
    int affected = stockmapper.deductstock(productid, num);
    return affected > 0;
}
// ✅ 正确写法：分段锁（将1条库存记录拆分为10条）
@transactional
public boolean deductstockwithsegment(long productid, integer num) {
    // 随机选择一个分段
    int segment = threadlocalrandom.current().nextint(10);
    int affected = stockmapper.deductstockbysegment(productid, num, segment);
    // 如果当前分段库存不足，尝试其他分段
    if (affected == 0) {
        for (int i = 0; i < 10; i++) {
            if (i == segment) continue;
            affected = stockmapper.deductstockbysegment(productid, num, i);
            if (affected > 0) return true;
        }
        return false;
    }
    return true;
}
// 对应的sql
@update("update stock set stock = stock - #{num} where product_id = #{productid} and segment = #{segment} and stock >= #{num}")
int deductstockbysegment(@param("productid") long productid, @param("num") integer num, @param("segment") integer segment);

核心技术难点与解决方案对照表

技术难点	问题本质	典型场景	解决方案	排查 / 验证命令
隐式类型转换导致索引失效	mysql 对索引列做隐式函数转换，破坏索引有序性	varchar 类型的手机号传了 int 值；int 类型的 id 传了字符串	1. 统一参数类型2. 避免`where phone = 13800138000`这种写法	`explain select * from user where phone = 13800138000;`观察key列是否为 null
联合索引最左前缀陷阱	范围查询（>、<、between）之后的索引列失效	联合索引`idx_a_b_c`，查询`where a=1 and b>2 and c=3`，c 列索引失效	1. 将等值查询列放在最左2. 范围查询列放在最后3. 必要时使用覆盖索引	`explain select * from t where a=1 and b>2 and c=3;`观察`key_len`列，判断用到了几个索引列
大事务导致的锁等待雪崩	长事务持有行锁时间过长，导致后续请求排队阻塞	事务中包含 rpc 调用、文件 io、循环操作	1. 拆分大事务，将非数据库操作移出事务2. 开启事务超时配置3. 使用`@transactional(timeout = 5)`	`show engine innodb status;`查看`transactions`部分的事务时长
死锁排查与解决	多个事务互相等待对方持有的锁，形成循环依赖	事务 a 先更新表 1 再更新表 2；事务 b 先更新表 2 再更新表 1	1. 统一更新顺序2. 降低事务粒度3. 开启死锁检测	`show engine innodb status;`查看`latest detected deadlock`部分
mysql 查询优化器选错索引	统计信息不准确，导致优化器选择了错误的索引	数据分布不均匀，某列值大部分相同	1. 强制指定索引`force index(idx_name)`2. 更新统计信息`analyze table t;`3. 创建更合适的联合索引	`explain analyze select * from t where a=1;`对比预估行数和实际行数
using filesort/using temporary	排序或分组操作无法使用索引，需要在内存 / 磁盘临时表中完成	`order by`非索引列；`group by`非索引列	1. 为排序 / 分组列创建索引2. 使用覆盖索引避免回表3. 限制排序结果集大小	`explain select * from t order by create_time desc;`观察`extra`列是否有`using filesort`

面试加分提示

结合实际踩坑经历：比如 "我之前在用户中心项目中遇到过隐式类型转换的问题，手机号字段是 varchar 类型，前端传了 int 值，导致全表扫描，接口耗时从 10ms 变成了 3s，改成传字符串后性能直接恢复"
量化优化效果：所有优化都要说清楚优化前后的性能对比，比如 "大分页优化后，第 10000 页的查询耗时从 2.5s 降到了 1ms"
提到底层原理：比如解释为什么隐式类型转换会导致索引失效，是因为 mysql 会把索引列转换成参数的类型，相当于对索引列做了函数操作，所以无法使用索引

真实现场模拟面试

面试官：“假设生产环境数据库突然告警，出现大量慢sql，老板让你去排查。说说你的整体思路。”

我：“好的，我一般会按发现→定位→分析→优化→验证这样一个闭环来做。先用一张图概括：”“接下来每一步都有具体的工具和检查点，我可以展开说说。”

面试官：“好，那第一步快速定位到具体是哪些sql在慢，你会怎么做？”

我：“线上讲究无侵入快速定位，我一般四板斧：

慢查询日志 – 已经开启的话，配合 pt-query-digest 直接看 top n，最省事。
sys 库 / performance_schema – 比如查 sys.statement_analysis，能按总耗时排序，找出那些执行次数多、累积影响大的sql。
云监控/apm – 像阿里云das、skywalking 直接给出慢sql和调用链路，能知道是哪个接口触发。
紧急情况 – cpu 打满时直接 show full processlist，看当前正在跑的sql，重点盯 sending data、creating tmp table 这些状态。

这一步结束，手里就是一份嫌疑sql清单，包括耗时、频率、来源。”

面试官：“嗯，拿到一条具体sql了，接下来怎么分析它为什么慢？”

我：“核心就是看执行计划。mysql的话我优先用 explain analyze（8.0），因为它显示实际耗时和预估行数的误差。

然后逐字段排查，就像看化验单一样：”

关注字段	🚩 异常信号	⚠️ 实际意味着什么
type	all	全表扫描，基本是索引缺失/失效
key	null	完全没用到索引
rows	巨大	扫描行数太多，估算可能不准
extra	using filesort / using temporary	文件排序或临时表，耗内存、磁盘
filtered	很小	索引选择性差，大量数据回表后被丢弃

“同时我还会 show create table 看一眼表结构和现有索引，确认是不是有索引但没用上，比如发生了隐式类型转换。”

面试官：“分析完执行计划，一般能归纳出哪几类根因？”

我：“根据经验，90%的慢sql都跑不出这六大根因：

索引缺失/失效（榜首）：没建索引、索引列上用函数、类型转换、违反最左前缀。
数据量暴涨：表变大后回表开销大，或优化器统计信息不准选错索引。
锁等待：sql本身不慢，被其他事务阻塞，show engine innodb status 能看出来。
返回大量无用数据：select * 拖了大字段，或者深分页 limit 100000,20 实际扫了很多行。
复杂sql写法：多表join + 子查询 + or，优化器很难生成好计划。
硬件/配置瓶颈：磁盘iops满、buffer pool太小导致刷脏页，得结合 iostat、top 一起看。”

面试官 “根因找到了，那你会立刻怎么优化？”

我：“优化分紧急止血和长远根治。我常用的‘三板斧’：

1. 索引优化

建覆盖索引让查询只扫索引不回表。
调整联合索引列顺序，贴合最左前缀。
优化器选错索引时，可临时 force index 止血，但事后要分析统计信息为啥不准。

2. sql改写

深分页改成基于主键的游标式分页，或延迟关联。
or 条件改 union all。
用 join 替代部分子查询。
坚决去掉 select *，只查必要字段。

3.架构调整

表太大考虑分区、分库分表，或历史数据归档。
统计类查询引入es等异构数据源，避免直接在oltp库上跑复杂分析。”

面试官：“优化完了，怎么验证真的有效，而不是按下葫芦浮起瓢？”

我：“验证我严格走三步：

预发/低峰期测试 – 再次 explain 甚至 explain analyze，确认执行计划符合预期，实际耗时下降。
线上监控对比 – 观察慢查询数量、cpu使用率、接口rt，确保问题真正消失。
闭环复盘 – 把案例沉淀到知识库，团队内同步，同时在代码审查时加强对索引变更的关注，有条件的接入sql自动审核或慢sql熔断工具。

如果效果不理想，我会重新回到执行计划分析那一步，调整方案。”

面试官：“不错。最后用一句话总结你的排查思路。”

我：“监控先发现 → 工具抓sql → explain看计划 → 结合场景挖根因 → 建索引/改写法/调架构 → 验证闭环。永远先观测再动手，线上最怕凭直觉改代码。”

面试官：“思路很清晰。刚才你提到深分页优化、索引失效分析这些，能不能贴一段核心代码示例，展示一下你的具体实现？最好有技术亮点。”

我：“没问题，我挑两个最有代表性的场景。

1. 深分页优化：延迟关联写法

这是典型的大表 limit 100000,20 慢查询，传统做法数据库实际扫描了100020行。我用延迟关联改写：

-- ❌ 原始慢sql（扫描大量行）
select id, title, content, create_time 
from articles 
where status = 1 
order by create_time desc 
limit 100000, 20;

-- ✅ 优化后：先取主键，再回表关联
select a.id, a.title, a.content, a.create_time
from articles a
inner join (
    select id 
    from articles 
    where status = 1 
    order by create_time desc 
    limit 100000, 20
) as tmp on a.id = tmp.id;

亮点：子查询只扫索引覆盖列（id + 索引列），避免了大量回表，再由主键直接关联取出最终数据，扫描行数从10万+降为20行左右。

2. 快速诊断索引失效：检查隐式类型转换

线上有一个sql突然从毫秒变秒级，执行计划显示 type=all。我怀疑是隐式类型转换导致索引失效，用这个查询验证：

-- 假设 phone 字段是 varchar，但传入的是数字
explain select * from users where phone = 13800138000;
-- key: null, type: all  💢 索引失效

-- 验证转换发生
select 
    column_name, 
    data_type, 
    column_type 
from information_schema.columns 
where table_schema = 'mydb' 
  and table_name = 'users' 
  and column_name = 'phone';
-- 结果：varchar(20)  而传参是数字，mysql会将字段转为数字比较，导致全表扫描

修复：改成 phone = '13800138000'，或代码层统一参数类型，执行计划立刻恢复。

3. 用 sys 库揪出 top 累积慢查询

当监控曲线抖动，不是单个sql特别慢，而是高频sql累积效应，我会用这条语句快速抓凶：

-- 按总延迟排序，找出最 “费时” 的 sql
select 
    query,
    db,
    exec_count,
    total_latency,
    avg_latency,
    rows_sent_avg
from sys.statement_analysis
order by total_latency desc
limit 10;

这条能看到哪些sql虽然单次不慢，但执行次数极高，拖垮整体性能，是优先治理的对象。”

面试官：“代码很扎实。那站在技术面试角度，你觉得在这个线上慢sql排查场景里，最大的技术难点有哪些？你是怎么解决它们的？”

我：“这个场景确实有几个特别棘手的地方，我总结为三大难点：

难点	具体描述	我的解决方案
难点1：生产环境无法随意操作	不能跑 `explain analyze` 这种耗时诊断，更不能加索引验证，稍不慎会加剧故障	① 优先使用慢日志 + pt-query-digest 离线分析，零侵入；② 在从库或预发环境回放慢sql，安全地做 explain 和压测；③ 利用 `performance_schema` 的内存表采集，不锁表。
难点2：索引失效原因隐蔽	隐式类型转换、字符集不一致、函数包装、统计信息不准等，仅看执行计划不够	① 对比 `show create table` 和实际sql参数类型，计算 key_len 判断是否用到完整索引；② 打开 `optimizer_trace` 看优化器决策过程；③ 定期 `analyze table` 更新统计信息，防止选错索引。
难点3：优化效果需要量化验证，且不能回归	优化一个慢sql可能影响其他查询，或者线上数据分布不同导致预期不符	① 在影子表或预发环境用真实数据量压测，对比 `explain analyze` 实际耗时；② 使用数据库审计插件或apm记录优化前后sql响应时间，形成对比报告；③ 灰度上线，观察监控曲线，确保cpu/慢查询数量下降，且无新异常。