性能优化 - 日常 DB 操作优化项

“过早优化是万恶之源，但数据库优化除外” - 这次聚焦在数据库开发优化上，其中 大事务 是数据库性能的“头号杀手”，会重点展开，从识别、拆分、异步化几个角度切入，尤其要强调锁竞争和回滚风险。除了大事务，也包括一些其他优化点：索引设计、批处理、连接池调优、读写分离等等。

大事务处理：数据库的「不可承受之重」

典型事故场景（支付回调业务）


java

@Transactional // 默认60秒超时
public void payCallback(PayMessage msg) {
    // 1. 更新订单状态（锁订单行）
    orderDao.updateStatus(msg.getOrderId(), PAID);
    
    // 2. 生成会计凭证（耗时统计操作）
    accountingService.createEntries(msg); // 平均耗时45秒
    
    // 3. 发放用户权益（调用外部服务）
    benefitService.grantVoucher(msg.getUserId()); // 网络波动时超时
}

产生的影响: 订单表长期锁等待 → 支付服务线程池打满 → 整个支付链路瘫痪

破局方案：事务拆分四步法

1、业务解耦 - 异步化非核心操作


java

@Transactional
public void payCallback(PayMessage msg) {
    orderDao.updateStatus(msg.getOrderId(), PAID); // 仅保留核心事务
    
    // 异步化下游操作
    accountingAsyncService.createEntries(msg);
    benefitAsyncService.grantVoucher(msg.getUserId());
}

2、分而治之 - 拆分事务粒度


java

public void handleLargeBatch() {
    List<Data> batch = queryLargeData();
    for (int i = 0; i < batch.size(); i += 100) {
        // 每100条提交一次事务
        processBatch(batch.subList(i, Math.min(i+100, batch.size())));
    }
}

@Transactional(propagation = Propagation.REQUIRES_NEW) // 独立事务
public void processBatch(List<Data> batch) { ... }

3、状态机管理 - 避免长事务锁


java

-- 设计中间状态字段
UPDATE orders 
SET status = 'PROCESSING' -- 非终态不阻塞查询
WHERE status = 'NEW' AND id = 10086;

4、妥协策略 - 最终一致性补偿


text

支付回调 -> 本地事务： 更新订单状态
本地事务 -> 消息队列： 发送会计事件
消息队列 -> 会计服务： 消费事件（可能重试）
会计服务 -> 支付回调： 处理失败? 
支付回调 -> 补偿服务： 触发补偿机制

索引优化：别让索引成为「摆设」

高频失效场景


sql

-- 案例：1.2亿用户表查询缓慢
SELECT * FROM users 
WHERE YEAR(create_time) = 2023  -- 索引失效！ 
   AND status = 1;

索引优化四原则

左匹配原则


sql

-- 重建联合索引（避免函数计算）
ALTER TABLE users ADD INDEX idx_status_ctime(status, create_time);

-- 正确查询姿势
SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
  AND status = 1;

覆盖索引降IO


sql

-- 避免回表查询
SELECT id, name FROM users 
WHERE phone = '13800138000'; 
-- 索引覆盖：INDEX(phone, name)

拒绝过度索引


sql

-- 索引代价测试（插入10w数据）
| 索引数量 | 插入耗时  |
|---------|----------|
| 0       | 1.2s     |
| 5       | 3.8s     |
| 10      | 8.5s     |

定期索引体检


sql

-- 使用诊断工具（MySQL示例）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 10086;

-- 清理无效索引
SELECT * FROM sys.schema_unused_indexes;

批处理艺术：拒绝SQL碎片

当下的计算机CPU处理速度还是很多的，而 IO 一般是个瓶颈，如：磁盘IO、网络IO。

有这么一个场景，查询 100 个人的账户余额？

有两个设计方案：

方案一：开单次查询接口，调用方内部循环调用 100 次
方案二：服务提供方开一个批量查询接口，调用方只需查询 1 次

你觉得那种方案更好？答案不言而喻，肯定是方案二

数据库的写操作也是一样道理，为了提高性能，我们一般都是采用批量更新。

创建100条数据


java

//创建商品的controller
@RequestMapping(value = "/create",method = {RequestMethod.POST})
@ResponseBody
public CommonReturnType createItem(@RequestParam(name = "title")String title,
                                   @RequestParam(name = "description")String description,
                                   @RequestParam(name = "price")BigDecimal price,
                                   @RequestParam(name = "stock")Integer stock,
                                   @RequestParam(name = "imgUrl")String imgUrl) throws BusinessException {
    //封装service请求用来创建商品
    ItemModel itemModel = new ItemModel();
    itemModel.setTitle(title);
    itemModel.setDescription(description);
    itemModel.setPrice(price);
    itemModel.setStock(stock);
    itemModel.setImgUrl(imgUrl);
    ItemModel itemModelForReturn = null;
    for(int i = 1 ;i <=100;i ++){
        itemModel.setId(null);
        itemModelForReturn = itemService.createItem(itemModel);
    }

    ItemVO itemVO = convertVOFromModel(itemModelForReturn);
    //rocketMQTemplate.convertAndSend("item-update-mq", itemModel.getId());

    bloomFilter.add("item_" + itemModel.getId());
    return CommonReturnType.create(itemVO);
}

单独查询100条


java

@RequestMapping(value = "/getItems",method = {RequestMethod.GET})
@ResponseBody
public CommonReturnType getItems(@RequestParam(name = "startId")Integer startId,@RequestParam(name = "endId")Integer endId){
    log.info("批量获取数据开始");
    List<ItemVO> itemVOs = new ArrayList<>();
    Date startTime = new Date();
    for(int i = startId;i <= endId; i++){
        ItemModel itemModel = itemService.getItemById(i);
        ItemVO itemVO = convertVOFromModel(itemModel);
        itemVOs.add(itemVO);
    }
    Date endTime = new Date();
    log.info("批量获取数据总共耗时：{}",startTime.getTime() - endTime.getTime());
    return CommonReturnType.create(itemVOs);
}

批量获取数据


java

@RequestMapping(value = "/getItemsByBath",method = {RequestMethod.GET})
@ResponseBody
public CommonReturnType getItemsByBath(@RequestParam(name = "startId")Integer startId,@RequestParam(name = "endId")Integer endId){
    log.info("批量获取数据开始");
    List<ItemVO> itemVOs = new ArrayList<>();
    Date startTime = new Date();
    List<ItemModel> itemModels = itemService.getItemsByBath(startId,endId);
    for (ItemModel itemModel : itemModels) {
        ItemVO itemVO = convertVOFromModel(itemModel);
        itemVOs.add(itemVO);
    }

    Date endTime = new Date();
    log.info("批量获取数据总共耗时：{}",endTime.getTime() - startTime.getTime());
    return CommonReturnType.create(itemVOs);
}

连接池调优：隐藏的资源黑洞

错误配置症状

Connection timeout 频发
数据库连接数暴涨
服务重启后响应缓慢

关键参数黄金法则


yaml

# Spring Boot配置示例
spring:
  datasource:
    hikari:
      maximum-pool-size: 20   # = (核心数*2) + 磁盘数 
      minimum-idle: 5
      connection-timeout: 3000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1

动态调整策略


java

// 运行时热调整连接池
HikariPool pool = (HikariPool) dataSource.getHikariPoolMXBean();
pool.setMaximumPoolSize(30); // 应对流量高峰

sql的排查优化

检查最长事务：


sql

SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;

对核心表执行 EXPLAIN 体检
事务分析：pt-kill
慢查询分析：Percona Toolkit
压力测试：sysbench
可视化监控：Prometheus + Grafana

目录