线上sql执行慢，分享3个优化案例

　　前段时间，博主线上项目的几个后端接口执行耗时达到了三、四秒钟以上，查看接口代码，发现sql语句执行过慢，于是开始分析sql执行这里把比较经典的优化案例分享给大家。本文所讲述MySql8。0代码演示地址：https：github。comwayn111newbeemallpro博主github地址：github。comwayn111欢迎大家关注，点个star一、or查询条件错误
　　线上有一个第三方账户扫码绑定手机号登录的接口，这个接口正常逻辑如下：使用苹果、QQ、微信获取扫描客户端登录二维码，获取用户第三方账户唯一ID后。判断第三方账户ID是否存在用户表中，存在且已绑定手机号则直接返回用户token进行登录操作。不存在则提示用户进行绑定手机号操作。用户填写手机号及短信验证码后，进行第三方账户唯一ID与手机号的绑定，成功则返回用户token进行登录操作。
　　博主记得这个接口是在21年10月上线的，到现在经历了一年多，接口执行时间是越来越慢，初步分析是用户数量持续增长，用户表记录越来越多，导致sql查询执行效率越来越低导致。这里用vcmemberbak进行举例，表结构以及数据展示，其中appleid、weixnid、qqid有建立索引
　　vcmemberbak表数据量在46万左右，开启日志分析后，发现是下面这条sql执行太慢导致SELECTFROMvcmemberWHEREqqidxxxORappidxxxOR
　　执行结果：
　　需要1。3秒左右，这是在我本地模拟的数据，线上用户在百万级别，耗时已经达到2、3秒，于是博主开始上explain，分析sql执行
　　由于explain结果中key列为空，明显可知虽然possiblekeys列有值，但是执行过程中，没有使用索引导致全表查询，从rows列为46万可以看出已经基本接近于全表查询。
　　那么问题出在哪里？我们不是已经给appleid、weixnid、qqid三个字段都添加索引了吗。
　　于是博主仔细查询sql语句发现里面有坑，查询的where条件上使用的三个条件字段是分别是appid、weixinid、qqid，而我们的索引字段是appleid、weixnid、qqid，很明显这是查询字段appleid写成appid了导致。appid没有加索引，所以在or条件查询下执行的就是全表扫描。
　　更改字段后执行结果：
　　Ok，这里发现了是查询字段写错了，那么修改查询字段正确后，查询0。18秒就正常了。二、update批量更新优化
　　博主以前线上项目（SpringBootMybatis）有一个接口需要批量更新库存，当时博主本着不能再代码for循环中执行更新逻辑的初衷，决定再xml文件中使用foreach标签执行批量更新逻辑，展示如下：updateidupdateStockNumforeachcollectionstockNumDTOSitemstockNumDTOupdatetbnewbeemallgoodsinfosetstocknumstocknum｛stockNumDTO。goodsCount｝wheregoodsid｛stockNumDTO。goodsId｝andstocknum｛stockNumDTO。goodsCount｝andgoodssellstatus0；foreachupdate
　　可以看出这个更新sql本质上就是在一条sql里执行了多个update语句。这个写法虽然不是在代码for循环中执行，但是这条sql语句执行时，MySql任然是单条单条执行的。这里用tbnewbeemallgoodsinfo表举例，表结构展示：
　　3个update语句同时执行结果如下：
　　如上，假如其中一个update语句需要耗时40毫秒，那么当有100条update语句时，接口耗时就会来到4秒，这显然是不可接受的。
　　那有没有一种高级一点的写法来执行update批量更新嘞？
　　当然是有的，博主后来使用了updatecase语句完成这个批量更新功能，
　　updatecasesql如下：UPDATEtbnewbeemallgoodsinfoSETstocknumCASEgoodsidWHEN10003THENstocknum1WHEN10004THENstocknum1WHEN10005THENstocknum1ELSEstocknumENDWHEREgoodsidIN（10003，10004，10005）
　　我们通过SETstocknumcasegoodsidwhen10004thenstocknum1。。。ELSEstocknumEND条件，可以将对应goodsid的记录的库存数量设置成我们想要的结果。
　　PS：需要注意的就是casewhenthen语句不匹配时会返回null，那就会造成不匹配的库存更新为null。所以ELSEstocknumEND条件一定要带上，当不匹配casewhenthen条件时，将库存数量设置成原本数量。where条件在这里其实可以不加，它起到的作用是限制更新范围，但是建议还是要加上，避免sql写错时，造成脏数据范围过大。
　　updatecase执行结果：
　　可以看出我们更新了3条记录，耗时50毫秒，更新记录越多时，优化效果也就明显。三、多线程优化大批量数据插入速度
　　博主线上有一个cdk兑换码业务，运营在后台创建一批cdk码时，系统会将这批码插入数据库中保存，这样可以保证用户兑换cdk时，码在数据库存在才能兑换，保障安全性。当运营创建十万条cdk记录时，线上耗时达到了十几秒。这里用cdkinfo表举例，表结构展示：
　　假如我们现在需要保存十万条cdkinfo记录，分批次保存代码如下：cdk创建TestpublicvoidcdkCreate（）｛Integernum100000；ListCdkInfolistnewArrayList（num）；DatedatenewDate（）；StringcreateUfor（Integeri0；i）｛CdkInfotempnewCdkInfo（）；temp。setCdkNo（String。valueOf（i））；temp。setCreateTime（date）；temp。setCreateUser（createUser）；list。add（temp）；｝longbeginSystem。currentTimeMillis（）；for（ListCdkInfocdkInfos：ListUtil。partition（list，1000））｛flagcdkInfoService。saveBatch（cdkInfos，cdkInfos。size（））；if（！flag）｛｝｝longendSystem。currentTimeMillis（）；log。info（执行耗时：（endbegin）ms）；Assert。isTrue（flag，批量更新失败）；｝
　　执行耗时：
　　可以看到在单一线程下，插入十万条记录差不多需要15秒了，这十万条数据之间没有关联，互不影响，那我们可以通过线程池提交单一批次的保存任务，配合CompletableFuture。allOf（futures。toArray（newCompletableFuture〔0〕））。join（）方法，等所有任务执行完成拿到结果。代码如下：通过线程池创建cdkTestpublicvoidasyncCdkCreate（）｛intnum100000；ListCdkInfolistnewArrayList（num）；DatedatenewDate（）；StringcreateUfor（Integeri0；i）｛CdkInfotempnewCdkInfo（）；temp。setCdkNo（String。valueOf（i））；temp。setCreateTime（date）；temp。setCreateUser（createUser）；list。add（temp）；｝longbeginSystem。currentTimeMillis（）；ListBooleanflagListnewArrayList（）；ListCompletableFutureVoidfuturesnewArrayList（）；for（ListCdkInfocdkInfos：ListUtil。partition（list，1000））｛CompletableFutureVoidfutureCompletableFuture。runAsync（（）｛booleanbcdkInfoService。saveBatch（cdkInfos，cdkInfos。size（））；flagList。add（b）；｝，ForkJoinPool。commonPool（））；futures。add（future）；｝CompletableFuture。allOf（futures。toArray（newCompletableFuture〔0〕））。join（）；longendSystem。currentTimeMillis（）；log。info（执行耗时：（endbegin）ms）；Assert。isTrue（flagList。stream（）。filter（aBoolean！aBoolean）。findFirst（）。orElse（true），批量更新失败）；｝
　　执行耗时：
　　可以看到执行耗时2。5秒，执行时间缩短了6倍。总结
　　到这里，本文所分享的3个优化案例就介绍完了，希望对大家日常开发有所帮助，喜欢的朋友们可以点赞加关注。