要想db操作的性能足夠高，巧妙的設計很重要，事務的操作范圍要盡量的小。一般情況下我們都是使用某個orm框架來操作db，這一類框架多數的實現方式都是夸網絡多次交互來開啟事務上下文和執行sql操作，是個黑盒子，包括對 autocommit 設置的時機也會有一些差異，稍微不注意就會踩坑。

在大并發的情況下加上夸網絡多次交互，就不可避免的由于網絡延遲、丟包等原因導致事務的執行時間過長，出現雪崩概率會大大增加。建議在性能和并發要求比較高的場景下盡量少用orm，如果非要用盡量控制好事務的范圍和執行時間。

大并發db操作的原則就是事務操作盡量少跨網絡交互，一旦跨網絡使用事務盡量用樂觀鎖來解決，少用悲觀鎖，盡量縮短當前 session 持有鎖的時間。

下面分享兩個在mysql innodb engine 上的大并發更新行的騷操作，這兩個騷操作都是盡可能的縮小db鎖的范圍和時間。

轉化update為insert

比較常見的大并發場景之一就是熱點數據的 update，比如具有預算類的庫存、賬戶等。

update從原理上需要innodb engine 先獲取row數據，然后進行row format轉換到mysql服務層，再通過mysql服務器層進行數據修改，最后再通過innodb engine寫回。

這整個過程每一個環節都有一定的開銷，首先需要一次innodb查詢，然后需要一次row format（如果row比較寬的話性能損失還是比較大的），最后還需要一次更新和一次寫入，大概需要四個小階段。

一次update就需要上述四過程開銷。此時如果qps非常大，必然會有一定性能開銷（這里暫不考慮cache、mq之類的削峰）。那么我們能不能將單個行的熱點分散開來，同時將update轉換成insert，我們來看下如何騷操作。

我們引入 slot 概念，原來一個row 我們通過多個row來表示，結果通過sum來匯總。為了不讓slot成為瓶頸，我們 rand slot，然后將update轉換成insert，通過 on duplicate key update 子句來解決沖突問題。

我們創建一個sku庫存表。

CREATE TABLE `tb_sku_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sku_id` bigint(20) NOT NULL,
  `sku_stock` int(11) DEFAULT '0',
  `slot` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sku_slot` (`sku_id`,`slot`),
  KEY `idx_sku_id` (`sku_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4

表中唯一性索引 idx_sku_slot 用來約束同一個 sku_id 不同 slot 。

庫存增加操作和減少操作要分開來處理，我們先看增加操作。

insert into tb_sku_stock (sku_id,sku_stock,slot)
values(101010101, 10, round(rand()*9)+1) 
on  duplicate key update sku_stock=sku_stock+values(sku_stock)

我們給 sku_id=101010101 增加10個庫存，通過 round(rand()*9)+1 將slot控制在10個以內（可以根據情況放寬或縮小），當 unique key 不沖突的話就一直是insert，一旦發生 duplicate 就會執行 update。（update也是分散的）

我們來看下減少庫存，減少庫存沒有增加庫存那么簡單，最大的問題是要做前置檢查，不能超扣。

我們先看庫存總數檢查，比如我們扣減10個庫存數。

select sku_id, sum(sku_stock) as ss
from tb_sku_stock
where sku_id= 101010101
group by sku_id having ss>= 10 for update

mysql的查詢是使用mvcc來實現無鎖并發，所以為了實時一致性我們需要加上for update來做實時檢查。
如果庫存是夠扣減的話我們就執行 insert into select 插入操作。

insert into tb_sku_stock (sku_id, sku_stock, slot)
select sku_id,-10 as sku_stock,round(rand() *9+ 1)
from(
    select sku_id, sum(sku_stock) as ss
    from tb_sku_stock
    where sku_id= 101010101
    group by sku_id having ss>= 10 for update) as tmp
on duplicate key update sku_stock= sku_stock+ values(sku_stock)

整個操作都是在一次db交互中執行完成，如果控制好單表的數據量加上 unique key 配合性能是非常高的。

消除 select...for update

大型OLTP系統，都會有一些需要周期性執行的任務，比如定期結算的訂單、定期取消的協議等，還有很多兜底的檢查、對賬程序等都會檢查一定時間范圍內的狀態數據，這些任務一般都需要掃描表里的某個狀態字段。

這些查詢基本基于類似status狀態字段，由于區分度非常低，所以索引基本上在這類場景下沒有太大作用。

為了保證掃描出來的數據不會發生并發重復執行的問題會對數據加排他鎖，通常就是 select...for update，那么這部分數據就不會被重復讀取到。但是也就意味著當前db線程將block在此鎖上，就是一個串行操作。

由于是排他鎖，數據的 insert、update 都會受到影響，在 repeatable read （可重復讀）且沒有 unqiue key 的場合下還會觸發Gap lock（間隙鎖）。

我們可以通過一個方式來消除 select...for update，并且提高數據并發處理能力。

CREATE TABLE `tb_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `order_status` int(11) NOT NULL DEFAULT '0',
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

我們簡單創建一個訂單表，task_id 是任務id，先讓數據結構支持多任務并行。

select order_id from tb_order where order_status=0 limit 10 for update

一般做法是通過select...for update 鎖住行。我們換個方法實現同樣的效果同時不會存在并發執行問題。

update tb_order set task_id=10 where order_status=0 limit 10;
Query OK, 4 rows affected
select order_id from tb_order where task_id=10 limit 4;

假設我們當前有很多并行任務（1-10），假設task_id=10任務執行，先update搶占自己的數據行。這個操作基本上在單數ms內，然后再通過select 帶上自己的taskid獲取到屬于當前task的行，同時可以帶上準確的limit，因為update是會返回受影響行數。

這里會有一個問題，就是執行的task如果由于某個原因終止了怎么辦，簡單方法就是用一個兜底job定期檢查超過一定時間的task，然后將task_id置為空。

作者：王清培（趣頭條 Tech Leader）

關注微信公眾號|收獲更多干貨