SQL调优详细过程：

快行出行平台的订单处理系统优化实战

在快行出行平台中，订单处理系统是核心模块之一，负责管理用户的订单创建、更新和查询。随着平台用户量的快速增长，月活跃用户超过25万，尤其是在高峰时段（如上下班高峰、节假日）和促销活动期间，订单处理系统在高并发场景下频繁出现数据库性能瓶颈，严重影响了用户体验和业务运营。

part NB

以下是我在快行出行项目中实际进行SQL调优的详细过程，涵盖问题发现、问题排查、问题解决和优化验证四个阶段。

1. 问题发现

现象描述：

1.订单创建响应时间延长：

用户在高峰时段提交订单时，接口响应时间显著增加，部分订单提交失败，用户体验下降。

2.数据库连接数激增：

高并发查询时，数据库连接数接近上限，导致新请求无法及时处理，出现超时错误。

3.慢查询增多：

通过监控工具发现慢查询日志中大量涉及orders和user_orders表的SQL语句执行时间超过300ms。

4.高锁等待：

部分SELECT和UPDATE操作导致数据库锁等待时间过长，影响整体性能。

发现途径：

1.应用性能监控（APM）工具：

使用Prometheus + Grafana监控数据库的响应时间、查询吞吐量和错误率，发现订单相关接口在高并发时段响应时间异常增长。

2.数据库慢查询日志：

启用MySQL的慢查询日志，观察到大量与orders和user_orders表相关的慢查询。

3.应用日志分析：

通过ELK Stack（Elasticsearch, Logstash, Kibana）分析应用日志，确认订单提交接口的延迟与数据库操作高度相关。

2. 问题排查

使用的工具和方法：

A. Navicat：

1. 慢查询日志分析：

通过Navicat的“查询”功能导入并解析MySQL的慢查询日志，使用其内置的分析工具识别高频且耗时的SQL语句。

2. EXPLAIN分析：

对识别出的慢查询语句，使用Navicat的EXPLAIN工具查看其执行计划，分析索引使用情况和潜在的性能瓶颈。

B. MySQL Performance Schema：

监控锁等待、查询性能和索引命中率，结合Navicat提供的性能数据，深入了解数据库的运行状况。

根本原因分析：

1.缺失复合索引：

订单查询语句涉及多个字段（如user_id、order_status、created_at）的过滤条件，但orders表仅存在单列索引，导致查询时全表扫描。

2.查询语句不优化：

部分SELECT语句使用了SELECT *，导致不必要的字段读取，增加了I/O开销。

JOIN操作中缺乏索引支持，增加了查询复杂度和执行时间。

3.数据库配置不合理：

innodb_buffer_pool_size设置不足，仅占用服务器内存的50%，缓存命中率低，频繁的磁盘I/O影响查询性能。

innodb_lock_wait_timeout设置过低，默认50秒，在高并发下导致事务频繁回滚。

3. 问题解决

优化措施：

1. 添加和优化索引

创建复合索引：

CREATE INDEX idx_user_status_created ON orders (user_id, order_status, created_at);

原因：复合索引覆盖了查询中的多个过滤条件，提高了查询效率，避免全表扫描。字段顺序按照查询条件的选择性和使用频率排列。

优化现有索引：

删除冗余的单列索引，减少索引维护开销，提升写操作性能。

DROP INDEX idx_user_id ON orders;DROP INDEX idx_order_status ON orders;

2. 重写查询语句

避免使用SELECT \*：

SELECT order_id, user_id, order_status, created_at FROM orders WHERE user_id = ? AND order_status = ? AND created_at >= ?;

原因：仅选择必要的字段，减少数据传输和I/O开销。

优化JOIN操作：

SELECT o.order_id, o.user_id, o.order_status, o.created_at, u.usernameFROM orders oJOIN users u ON o.user_id = u.idWHERE o.user_id = ? AND o.order_status = ? AND o.created_at >= ?;

原因：确保JOIN字段（o.user_id和u.id）上有索引，

减少JOIN的开销。

3. 优化数据库配置

调整InnoDB缓冲池大小：

根据服务器内存，增大innodb_buffer_pool_size至24GB：innodb_buffer_pool_size = 24G

原因：提高缓存命中率，减少磁盘I/O，提高查询性能。

调整锁等待超时时间：

增大

innodb_lock_wait_timeout

至120秒：

innodb_lock_wait_timeout = 120

原因：在高并发下，适当增加锁等待时间，

减少事务因锁等待超时而回滚，提升系统稳定性。

4. 应用层优化

使用Redis缓存：

缓存热门订单数据和用户的订单记录，减少数据库的读取压力。

设计示例：

// 获取用户的订单列表public List<Order> getUserOrders(Long userId) {    String cacheKey = "user:" + userId + ":orders";    // 尝试从Redis缓存中获取    List<Order> orders = redisTemplate.opsForList().range(cacheKey, 0, -1);    if (orders == null || orders.isEmpty()) {        // 缓存未命中，从数据库查询        orders = ordersMapper.findByUserId(userId);        if (orders != null && !orders.isEmpty()) {            // 将查询结果存入缓存，设置过期时间            redisTemplate.opsForList().rightPushAll(cacheKey, orders);            redisTemplate.expire(cacheKey, 1, TimeUnit.HOURS);        }    }    return orders;}
// 更新用户的订单数据public void updateUserOrders(Long userId, List<Order> updatedOrders) {    // 更新数据库    ordersMapper.updateOrders(updatedOrders);    // 删除缓存，使下次读取时重新加载最新数据    String cacheKey = "user:" + userId + ":orders";    redisTemplate.delete(cacheKey);}

原因：通过Redis缓存频繁读取但不常更新的数据，

减少数据库负载，提升系统响应速度。

实施效果：

1. 预期效果：

查询响应时间从300ms降至80ms以内。

减少全表扫描，提高索引命中率。

降低数据库锁等待时间，提高并发处理能力。

减少数据库I/O压力，提升整体性能。

2. 实际效果：

慢查询数量减少75%，响应时间降低70%。

数据库锁等待时间平均减少65%，事务回滚率大幅下降。

Redis缓存的引入使数据库读取压力显著减轻，系统吞吐量提升50%。

👋 大家好，我是枫哥，一名Java后端开发者！我热衷于探索新技术，并在我的微信公众号上分享关于Java 生态和后端开发的知识。欢迎关注我的公众号，期待与你一起探讨技术的无穷可能！” 目前专注于Java技术分享，覆盖春招、秋招、社招和跳槽相关内容，并提供一对一带徒学习服务。加入学徒计划，即可享受内推机会和优质资源，签订协议确保就业无忧。此外，我们还推出了‘Java跳槽网’，为你的求职之路提供全方位支持，助你快速找到理想工作

END

ITPub博客

《实战分享：快行出行订单处理系统的SQL调优之路》

最新文章