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

-- 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; -- 查看事务和锁等待情况

@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()
                    );
                }
            }
        };
    }
}

// ❌ 错误写法：扫描前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查询（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());
}

// ❌ 错误写法：单条记录锁竞争激烈，数据库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);

-- ❌ 原始慢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;

-- 假设 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会将字段转为数字比较，导致全表扫描

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