【實戰】利用多執行緒優化查詢百萬級資料

碼農Amg發表於2021-11-19
執行緒優化

前言

日常開發中，難免會遇到需要查詢到資料庫所有記錄的業務場景，在索引完善的情況下，當資料量達到百萬級別或者以上的時候，全表查詢就需要耗費不少的時間，這時候我們可以從以下幾個方向著手優化

  1. 優化sql

  2. 利用多執行緒查詢

  3. 分庫分表

今天就來討論一下使用【優化sql】和【多執行緒】方式提升全表查詢效率

⚠️注意，這只是簡單測試，用於講解思路，真實情況會更加的複雜，效率可能會相對受到影響，而且也會受硬體配置的影響，所以不是絕對的

前置準備

  1. 使用InnoDb作為執行引擎

  2. 建立測試表，有自增主鍵id

  3. 往表中新增測試資料（100W以上），可以選擇在程式中匯入，也可以選擇在資料庫裡面生成測試資料，具體可以參考：生成測試資料

  4. Java程式中使用Mybatis來操作，使用自定義註解+SpringAOP的方式來記錄執行耗時，原始碼後面會給，有興趣的朋友可以下載下來實踐一下

  5. 總體目錄結構

    image

開始測試

首先確保庫中是有資料的，由於實際業務的複雜度，所以這裡模擬username的時候也讓他複雜一點，不是同一條資料進行了600多萬次複製

image

image

單執行緒+基礎sql

再下來就是基礎的全表查詢方式，這裡使用postman測試

@GetMapping("/sync")
public String getData() {
    List<User> list = userService.queryAllUseSync();
    return "查詢成功！";
}


@Override
@RecordMethodSpendAnnotation //這個註解標記的方法會被SpringAOP管理起來，計算方法耗時
public List<User> queryAllUseSync() {

    //直接就採用Mybatis全查
    return userMapper.queryAll();
}

image

我們來看一下，這個queryAll的sql，可以發現就是一個簡單的全表查詢

<select id="queryAll" resultMap="UserMap">
		select
		id, username, create_time
		from performance.user
</select>
原因分析

我們直接把sql抓出來EXPLAIN一下，可以發現是沒有走索引的，全表600多W的資料，本機耗時（多次測試取平均）：67s

image

?這是耗時著實是太慢了，所以必須得優化一下，那麼怎麼優化呢？

  1. 從sql出發，剛剛得sql是沒有走索引的，那麼首先我們得讓sql走索引，id是個自增主鍵，我們是否可以利用主鍵進行分段查詢

單執行緒迴圈+分段sql

我們優化後的sql，其中 ?代表的是分段的起始指 n代表的是分段的末尾,可以看到是有走索引的

select id,username,create_time from user where id > ? and limit n

image

@Override
	@RecordMethodSpendAnnotation
	public List<User> queryAllUseSyncAndLimit(int limit) {
		
		List<User> list = new ArrayList<>();
		Long count = userMapper.getCount();
		//迴圈次數
		long cycles = count / limit;
		
		for (int i = 0; i < cycles; i++) {
			long startIdx = i * limit;
			long endIdx = (i+1) * limit;
			if (endIdx > count)
				endIdx = count;
			list.addAll(userMapper.queryAllByLimit(startIdx,Math.toIntExact(endIdx)));
		}
		
		return list;
	}

?這個比剛剛那個還要慢太多了！！！，但是但從sql來看，確實是優化過了，那麼為什麼會慢這麼多？而且CPU記憶體使用率飆升了起來

image

原因分析

我們使用jconsole看一下，可以發現記憶體佔用量有點離譜，

image

然後再使用jstat -gcutil pid（擷取一段時間的）

image

image

可以發現垃圾收集非常的頻繁，YGC達到45次，FGC達到8次，光gc耗時就達到18秒了，我的電腦是扛不住了，所以我就不繼續跑下去了

那麼這種方式的問題就不在於sql了，而是程式的問題，私以為採用分頁+迴圈的方式，會提高效率，但是迴圈是需要耗費CPU資源的，由於請求的物件太大了，記憶體被積壓滿，所以程式就得等待有一塊合適大小的記憶體出現，才能進行下去

原本sql拎出來查速度是有提升的，但是現在程式必須得停下來等待記憶體釋放，所以CPU也會飆升，最終導致執行不下去

❌所以這種方式不可取

既然是使用分段查詢+組合的形式，那我們也可以採用多執行緒非同步的形式，每個執行緒跑完資料拿出來之後就remove掉

多執行緒+分段sql

採用執行緒池的思想，核心執行緒設定在5個，最大執行緒設定在10個，關於執行緒數的選定網上有很多資料可以查到，這裡就不贅述了

這裡同時採用Future非同步模式，提升效率，關於Future的認識，可以看這篇文章 Java Future模式的使用

@Override
@RecordMethodSpendAnnotation
public List<User> queryAllUseThreadPool(int limit) {
		
		//還是獲取到總記錄數，本機是600多W測試資料
		Long count = userMapper.getCount();
		
		List<FutureTask<List<User>>> resultList = new ArrayList<>();
		
		//分段次數
		long cycles = count / limit;
		for (int i = 0; i < cycles; i++) {
			//每一段的起始座標
			long idx = i * limit;
			log.info("idx: {}", idx);
            //具體的查詢任務
			FutureTask<List<User>> futureTask = new FutureTask<>(() -> userMapper.queryAllByLimit(idx,limit));
			
            //把任務丟給執行緒池排程執行
			threadPool.execute(futureTask);
            //future非同步模式，把任務放進去先，先不取結果
			resultList.add(futureTask);
		}
		
		List<User> result = new ArrayList<>();
		while (resultList.size() > 0) {
			Iterator<FutureTask<List<User>>> iterator = resultList.iterator();
			while (iterator.hasNext()) {
				try {
					result.addAll(iterator.next().get());
                    //獲取一個就刪除一個任務
					iterator.remove();
				} catch (InterruptedException  | ExecutionException e) {
					log.error("多執行緒查詢出現異常：{}", e.getMessage());
				}
			}
		}
		
		//最後一次資料可能不為整，需要額外操作
		if (result.size() != count)
			result.addAll(userMapper.queryAllByLimit(result.size(),Math.toIntExact(count)));
		return result;
}

image

原因分析

對比於使用單執行緒+基礎sql來看，效率提升了近乎300%，限於本機效能的問題，如果採用正常的效能高點的伺服器，效率更好了，我們通過jstat 看一下引數

可以發現YGC次數還是執行了29次，FGC執行了5次，對於本機來說，硬體效能有限，資料量確實是有夠大的，花在gc的時間足足有7秒鐘，所以如果還要優化的話，這裡也是一個著手點

image

總結

導致查詢資料慢的原因有很多種，這裡羅列幾種供參考

  1. 程式碼問題（設計缺陷、sql優化沒做好等）
  2. 硬體資源問題（記憶體、I/O、CPU等等）
  3. 資料量太大問題
  4. 網路阻塞問題

限於本人水平有限，難免會有紕漏，如果有發現文章那裡寫的不對的，歡迎指出，謝謝！

最後提供一下原始碼的獲取方式，關注微信公眾號【碼農Amg】，回覆關鍵字：優化sql

相關文章