线上sql执行慢，分享3个优化案例
前段时间，博主线上项目的几个后端接口执行耗时达到了三、四秒钟以上，查看接口代码，发现 sql 语句执行过慢，于是开始分析 sql 执行 这里把比较经典的优化案例分享给大家。
- 本文所讲述 MySql 8.0+
- 代码演示地址：https://github.com/wayn111/newbee-mall-pro
- 博主github地址：https://github.com/wayn111 欢迎大家关注，点个star
一、or 查询条件错误
线上有一个第三方账户扫码绑定手机号登录的接口，这个接口正常逻辑如下：
- 使用 苹果、QQ、微信获取扫描客户端登录二维码，获取用户第三方账户唯一ID后。
- 判断第三方账户ID是否存在用户表中，存在且已绑定手机号则直接返回用户 token 进行登录操作。
- 不存在则提示用户进行绑定手机号操作。
- 用户填写手机号及短信验证码后，进行第三方账户唯一ID与手机号的绑定，成功则返回用户 token 进行登录操作。
博主记得这个接口是在21年10月上线的，到现在经历了一年多，接口执行时间是越来越慢，初步分析是用户数量持续增长，用户表记录越来越多，导致 sql 查询执行效率越来越低导致。这里用 vc_member_bak 进行举例，表结构以及数据展示，其中 apple_id、weixn_id、qq_id 有建立索引：
vc_member_bak 表数据量在46万左右，开启日志分析后，发现是下面这条 sql 执行太慢导致：
SELECT * FROM vc_member
WHERE qq_id = 'xxx' OR app_id = 'xxx' OR weixin_id = 'xxx' ;
执行结果：
需要1.3秒左右，这是在我本地模拟的数据，线上用户在百万级别，耗时已经达到2、3秒，于是博主开始上 explain，分析 sql 执行：
由于 explain 结果中 key 列为空，明显可知虽然 possible_keys 列有值，但是执行过程中，没有使用索引导致全表查询，从rows 列为46万可以看出已经基本接近于全表查询。
那么问题出在哪里？我们不是已经给 apple_id、weixn_id、qq_id 三个字段都添加索引了吗。
于是博主仔细查询 sql 语句发现里面有坑，查询的 where 条件上使用的三个条件字段是分别是 app_id、weixin_id、qq_id，而我们的索引字段是 apple_id、weixn_id、qq_id，很明显这是查询字段 apple_id 写成 app_id 了导致。app_id 没有加索引，所以在 or 条件查询下执行的就是全表扫描。
更改字段后执行结果：
Ok，这里发现了是查询字段写错了，那么修改查询字段正确后，查询0.18秒就正常了。😂
二、update 批量更新优化
博主以前线上项目（Spring Boot + Mybatis）有一个接口需要批量更新库存，当时博主本着不能再代码 for 循环中执行更新逻辑的初衷，决定再 xml 文件中使用 foreach 标签执行批量更新逻辑，展示如下：
<update id="updateStockNum">
<foreach collection="stockNumDTOS" item="stockNumDTO">
update tb_newbee_mall_goods_info
set stock_num = stock_num-#{stockNumDTO.goodsCount}
where goods_id = #{stockNumDTO.goodsId}
and stock_num>=#{stockNumDTO.goodsCount}
and goods_sell_status = 0;
</foreach>
</update>
可以看出这个更新 sql 本质上就是在一条 sql 里执行了多个 update 语句。这个写法虽然不是在代码 for 循环中执行，但是这条 sql 语句执行时，MySql 任然是单条单条执行的。这里用 tb_newbee_mall_goods_info 表举例，表结构展示：
3个 update 语句同时执行结果如下：
如上，假如其中一个 update 语句需要耗时40毫秒，那么当有100条 update 语句时，接口耗时就会来到4秒，这显然是不可接受的。
那有没有一种高级一点的写法来执行 update 批量更新嘞？
当然是有的，博主后来使用了 update + case 语句完成这个批量更新功能，
update + casesql 如下：
UPDATE tb_newbee_mall_goods_info
SET stock_num =
CASE
goods_id
WHEN 10003 THEN
stock_num - 1
WHEN 10004 THEN
stock_num - 1
WHEN 10005 THEN
stock_num - 1
ELSE stock_num END
WHERE
goods_id IN (
10003,
10004,
10005
)
我们通过 SET stock_num = case goods_id when 10004 then stock_num - 1 ... ELSE stock_num END 条件，可以将对应 goods_id 的记录的库存数量设置成我们想要的结果。
PS：需要注意的就是 case when then语句不匹配时会返回 null，那就会造成不匹配的库存更新为 null。所以 ELSE stock_num END条件一定要带上，当不匹配 case when then条件时，将库存数量设置成原本数量。where 条件在这里其实可以不加，它起到的作用是限制更新范围，但是建议还是要加上，避免 sql 写错时，造成脏数据范围过大。
update + case执行结果：
可以看出我们更新了3条记录，耗时50毫秒，更新记录越多时，优化效果也就明显。
三、多线程优化大批量数据插入速度
博主线上有一个 cdk 兑换码业务，运营在后台创建一批 cdk 码时，系统会将这批码插入数据库中保存，这样可以保证用户兑换 cdk 时，码在数据库存在才能兑换，保障安全性。当运营创建十万条cdk记录时，线上耗时达到了十几秒。这里用 cdk_info 表举例，表结构展示：
假如我们现在需要保存十万条 cdk_info 记录，分批次保存代码如下：
/**
* cdk创建
*/
@Test
public void cdkCreate() {
Integer num = 100000;
List<CdkInfo> list = new ArrayList<>(num);
Date date = new Date();
String createUser = "test";
for (Integer i = 0; i < num; i++) {
CdkInfo temp = new CdkInfo();
temp.setCdkNo(String.valueOf(i));
temp.setCreateTime(date);
temp.setCreateUser(createUser);
list.add(temp);
}
long begin = System.currentTimeMillis();
boolean flag = false;
for (List<CdkInfo> cdkInfos : ListUtil.partition(list, 1000)) {
flag = cdkInfoService.saveBatch(cdkInfos, cdkInfos.size());
if (!flag) {
break;
}
}
long end = System.currentTimeMillis();
log.info("执行耗时：" + (end - begin) + "ms");
Assert.isTrue(flag, "批量更新失败");
}
执行耗时：
可以看到在单一线程下，插入十万条记录差不多需要15秒了，这十万条数据之间没有关联，互不影响，那我们可以通过线程池提交单一批次的保存任务，配合 CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join() 方法，等所有任务执行完成拿到结果。代码如下：
/**
* 通过线程池创建cdk
*/
@Test
public void asyncCdkCreate() {
int num = 100000;
List<CdkInfo> list = new ArrayList<>(num);
Date date = new Date();
String createUser = "test";
for (Integer i = 0; i < num; i++) {
CdkInfo temp = new CdkInfo();
temp.setCdkNo(String.valueOf(i));
temp.setCreateTime(date);
temp.setCreateUser(createUser);
list.add(temp);
}
long begin = System.currentTimeMillis();
List<Boolean> flagList = new ArrayList<>();
List<CompletableFuture<Void>> futures = new ArrayList<>();
for (List<CdkInfo> cdkInfos : ListUtil.partition(list, 1000)) {
CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
boolean b = cdkInfoService.saveBatch(cdkInfos, cdkInfos.size());
flagList.add(b);
}, ForkJoinPool.commonPool());
futures.add(future);
}
CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
long end = System.currentTimeMillis();
log.info("执行耗时：" + (end - begin) + "ms");
Assert.isTrue(flagList.stream().filter(aBoolean -> !aBoolean).findFirst().orElse(true), "批量更新失败");
}
执行耗时：
可以看到执行耗时2.5秒，执行时间缩短了6倍。
总结
到这里，本文所分享的3个优化案例就介绍完了，希望对大家日常开发有所帮助，喜欢的朋友们可以点赞加关注😘。
