MySQL Batch 與 Transaction

標籤：使用   io   資料   ar   問題   cti   時間   資料庫   

最近在資料庫上經常遇到死結問題. 表現的問題有

 

1. 有一個查詢為:

1) 一個複雜的 select 查處一組大資料

2) 使用事務 update 這組資料的狀態

為了讓鎖定的時間變短, 我將這整個大事務切分成了多個小事務, 也就是每次查詢並更新 1W 資料變為了每次查詢並更新 100 資料, 但是需要查詢更新 100 次, 總資料量還是 1W

這樣就造成了一個問題, 因為這個 select 是個複雜查詢, 這裡 100 次 select 使得整個操作時間急劇上升.

所以我認為最好的做法是, 先把 1W 資料用 select 查詢出來, 然後 update 操作分組, 例如每次 update 1000, 如果這次事務 update 失敗, 則將 update 失敗的這些資料從 select 結果集中去掉

 

2. 有一個大量的結果集更新, 例如 5W 條, 原來我的做法是切分為 10 個小事務, 每次更新 5000 條, 順序更新

實際上這裡沒有必要用事務, 因為我對他們沒有一致性和原子性要求. 使用事務的好處是效率提高, 因為 MySQL 預設是 autocommit = true, 相當於沒條語句是一個事務. 但是使用事務的缺點是 update 的鎖的持有時間會加長, 容易造成死結.

最後的解決方案是使用多線程同時執行, 並且不適用事務, 但是使用 preparedStatement (batch).

 

最後看看我的測試資料, 一共測試了 10W 的資料

1. 使用 batch 一次性執行完畢, 共耗費 55s
2. 使用多線程並發執行, 每個線程 batch 數量為 5000, 線程池大小是 CPU核心數 * 2, 共耗費 11s

3. 不適用 batch, 但是用多線程執行, 每個線程負責 5000 資料插入, 共耗費 24s

4. 使用事務, 但是不用 batch, 使用多線程執行, 每個線程負責 5000 資料, 共耗費 14s

5. 使用事務, 同時使用 batch, 使用多線程, 每個線程 5000 資料, 共耗費 8s

可見使用事務, 同時 batch 效率最高, 但是使用事務會加長鎖的持有時間, 這點需要注意