前段時間，博主線上專案的幾個后端介面執行耗時達到了三、四秒鐘以上，查看介面代碼，發現 sql 陳述句執行過慢，于是開始分析 sql 執行 這里把比較經典的優化案例分享給大家，

• 本文所講述 MySql 8.0+
• 代碼演示地址：https://github.com/wayn111/newbee-mall-pro
• 博主github地址：https://github.com/wayn111 歡迎大家關注，點個star

一、or 查詢條件錯誤

線上有一個第三方賬戶掃碼系結手機號登錄的介面，這個介面正常邏輯如下：

1. 使用 蘋果、QQ、微信獲取掃描客戶端登錄二維碼，獲取用戶第三方賬戶唯一ID后，
2. 判斷第三方賬戶ID是否存在用戶表中，存在且已系結手機號則直接回傳用戶 token 進行登錄操作，
3. 不存在則提示用戶進行系結手機號操作，
4. 用戶填寫手機號及短信驗證碼后，進行第三方賬戶唯一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個優化案例就介紹完了，希望對大家日常開發有所幫助，喜歡的朋友們可以點贊加關注??，

標籤：其他

上一篇：MySQL如何正確查詢字串長度

下一篇：資料倉庫建模理論及實踐-從0到1