线上慢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;