关于后端:线上sql执行慢分享3个优化案例

前段时间，博主线上我的项目的几个后端接口执行耗时达到了三、四秒钟以上，查看接口代码，发现 sql 语句执行过慢，于是开始剖析 sql 执行这里把比拟经典的优化案例分享给大家。

本文所讲述 MySql 8.0+
代码演示地址：https://github.com/wayn111/newbee-mall-pro
博主 github 地址：https://github.com/wayn111 欢送大家关注，点个 star

线上有一个第三方账户扫码绑定手机号登录的接口，这个接口失常逻辑如下：

应用苹果、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 秒就失常了。😂

博主以火线上我的项目（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 个优化案例就介绍完了，心愿对大家日常开发有所帮忙，喜爱的敌人们能够点赞加关注😘。

关于后端:线上sql执行慢分享3个优化案例

一、or 查问条件谬误

二、update 批量更新优化

三、多线程优化大批量数据插入速度

总结

Just My Socks（注册教程内含优惠码）

	SELECT * FROM vc_member
	WHERE qq_id = 'xxx' OR app_id = 'xxx' OR weixin_id = 'xxx' ;

	<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>

	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
	)

	/**
	* 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, "批量更新失败");
	}

	/**
	* 通过线程池创立 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), "批量更新失败");
	}

关于后端:线上sql执行慢分享3个优化案例

一、or 查问条件谬误

二、update 批量更新优化

三、多线程优化大批量数据插入速度

总结

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）