Java SQL调优案例

本文目录导读：

1. 案例背景
2. 问题SQL定位
3. 执行计划分析
4. 优化方案
5. 优化效果对比
6. 其他调优技巧
7. 最佳实践总结

下面我为你整理一个完整的Java SQL调优案例，包含问题定位、分析和优化过程。

案例背景

业务场景：订单查询系统，需要按用户ID、时间范围、订单状态查询订单列表

原始问题：

• 接口响应时间平均3.5秒
• 高峰期达到8秒以上
• 数据库CPU使用率持续90%+

问题SQL定位

通过慢查询日志定位到以下SQL：

SELECT 
    o.id, o.order_no, o.user_id, o.total_amount, 
    o.status, o.created_at, u.phone, u.nickname
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.user_id = 123456
    AND o.created_at >= '2024-01-01 00:00:00'
    AND o.created_at < '2024-12-31 23:59:59'
    AND o.status IN (1, 2, 3)
ORDER BY o.created_at DESC
LIMIT 0, 20;

执行计划分析

EXPLAIN SELECT ...;
-- 执行计划结果：
+----+-------------+-------+------------+------+----------------+--------+---------+-------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys  | key    | key_len | ref   | rows    | filtered | Extra                       |
+----+-------------+-------+------------+------+----------------+--------+---------+-------+---------+----------+-----------------------------+
|  1 | SIMPLE      | o     | NULL       | ALL  | idx_user_id    | NULL   | NULL    | NULL  | 1000000 | 50.00    | Using where; Using filesort |
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY        | NULL   | NULL    | NULL  | 500000  | 10.00    | Using where                 |
+----+-------------+-------+------------+------+----------------+--------+---------+-------+---------+----------+-----------------------------+

问题分析：

1. orders表全表扫描：100万行数据全扫描
2. users表全表扫描：50万行数据全扫描
3. 使用文件排序：大数据量下ORDER BY导致性能下降
4. 索引未生效：虽然user_id有索引，但查询条件复杂导致未使用

优化方案

优化索引

-- 1. 创建复合索引（最优先）
CREATE INDEX idx_user_time_status ON orders(user_id, created_at, status);
-- 2. 或者创建针对排序的索引
CREATE INDEX idx_user_time ON orders(user_id, created_at);
-- 3. users表已存在主键索引，无需额外优化

SQL改写优化

-- 优化后的SQL
SELECT 
    o.id, o.order_no, o.user_id, o.total_amount, 
    o.status, o.created_at, u.phone, u.nickname
FROM orders o
INNER JOIN users u ON o.user_id = u.id AND u.id = 123456
WHERE o.user_id = 123456
    AND o.created_at >= '2024-01-01'
    AND o.created_at < '2024-12-31'
    AND o.status IN (1, 2, 3)
ORDER BY o.created_at DESC
LIMIT 0, 20;

Java代码层面优化

// 优化前的Mapper接口
public interface OrderMapper {
    List<OrderDetailVO> getOrderList(@Param("userId") Long userId,
                                     @Param("startTime") Date startTime,
                                     @Param("endTime") Date endTime,
                                     @Param("statusList") List<Integer> statusList);
}
// 优化后的Mapper接口
public interface OrderMapper {
    // 分批查询优化，避免深分页
    List<OrderDetailVO> getOrderListOptimized(@Param("userId") Long userId,
                                              @Param("startTime") Date startTime,
                                              @Param("endTime") Date endTime,
                                              @Param("statusList") List<Integer> statusList,
                                              @Param("lastId") Integer lastId,  // 游标
                                              @Param("size") Integer size);
}

<!-- 优化后的XML -->
<select id="getOrderListOptimized" resultType="OrderDetailVO">
    SELECT 
        o.id, o.order_no, o.user_id, o.total_amount, 
        o.status, o.created_at, u.phone, u.nickname
    FROM orders o
    INNER JOIN users u ON o.user_id = u.id
    WHERE o.user_id = #{userId}
        <if test="lastId != null">
            AND o.id > #{lastId}  <!-- 游标方式避免深分页 -->
        </if>
        AND o.created_at BETWEEN #{startTime} AND #{endTime}
        AND o.status IN
        <foreach collection="statusList" item="status" open="(" separator="," close=")">
            #{status}
        </foreach>
    ORDER BY o.id DESC  <!-- 使用主键排序，避免filesort -->
    LIMIT #{size}
</select>

缓存优化

@Service
public class OrderService {
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    // 添加缓存
    public List<OrderDetailVO> getOrdersWithCache(Long userId, Date startTime, Date endTime) {
        String key = "user:orders:" + userId + ":" + 
                     new SimpleDateFormat("yyyyMMddHHmm").format(startTime);
        // 先从缓存查询
        List<OrderDetailVO> cachedData = (List<OrderDetailVO>) redisTemplate.opsForValue().get(key);
        if (cachedData != null) {
            return cachedData;
        }
        // 数据库查询
        List<OrderDetailVO> dbData = orderMapper.getOrderList(userId, startTime, endTime);
        // 写入缓存，设置5分钟过期
        redisTemplate.opsForValue().set(key, dbData, 5, TimeUnit.MINUTES);
        return dbData;
    }
}

优化效果对比

其他调优技巧

分页优化（大OFFSET问题）

-- 原始写法（性能差）
SELECT * FROM orders 
WHERE user_id = 123456 
ORDER BY created_at DESC 
LIMIT 100000, 20;
-- 优化写法（延迟关联）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE user_id = 123456
    ORDER BY created_at DESC
    LIMIT 100000, 20
) t ON o.id = t.id;

避免SELECT *

-- 只查询需要的字段
SELECT o.id, o.order_no, o.user_id, o.total_amount 
FROM orders o 
WHERE o.user_id = 123456;

批量操作优化

// 不推荐：逐条插入
for (Order order : orderList) {
    orderMapper.insert(order);
}
// 推荐：批量插入
orderMapper.batchInsert(orderList);

<insert id="batchInsert">
    INSERT INTO orders (order_no, user_id, total_amount, status, created_at)
    VALUES
    <foreach collection="list" item="order" separator=",">
        (#{order.orderNo}, #{order.userId}, #{order.totalAmount}, 
         #{order.status}, #{order.createdAt})
    </foreach>
</insert>

最佳实践总结

1. 索引优化：

   • 为高频查询建立复合索引
   • 注意索引列的顺序
   • 定期使用ANALYZE TABLE更新统计信息

2. SQL书写规范：

   • 避免使用SELECT *
   • 避免对索引列使用函数
   • 使用EXISTS替代IN（大数据量时）

3. 架构层面：

   • 引入Redis缓存热点数据
   • 考虑分库分表（数据量超过500万时）
   • 使用读写分离

4. 监控与预警：

   • 部署慢查询监控
   • 设置数据库性能告警阈值

这个案例展示了从SQL分析、索引优化到代码层面优化的完整过程,实际应用中需要根据具体场景选择合适的优化方案。