MySQL幾種更新操作的案例分析

Posted on by

本文將通過一個 用戶賬戶金額更新的案例 分析幾種數據更新的操作的優劣。希望對大傢有幫助 🐶。

數據庫版本 : mysql 5.7.23

案例分析

創建數據庫的DDL： 

CREATE TABLE `hw_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

更新賬戶金額

直接更新

方案 1 查詢後更新 

# 數據查詢
select * from hw_account where id = 1;

# 數據更新
update hw_account set balance = 5 where id = 1;

存在的問題，就是分兩次操作，如果並發執行的時候，可能造成更新丟失的問題.

樂觀鎖方案

利用版本號操作，即對數據庫增加樂觀鎖的方式進行。 

# 數據查詢
select * from hw_account where id = 1;

# 數據更新
update hw_account set balance = 5 , version = version + 1 
  where id = 1 and version = n;
  
# 判斷是否成功  
if row < 1 {
   回滾
}

存在的問題，如果該條數據並發操作的時候，會導致其他的請求失敗。如果這個請求的前置鏈路比較長的話， 回滾成本比較高。

無鎖方案

不用查詢，采用數據庫的計算，也不需要版本號的操作，直接通過域值進行有效性判斷。具體的 SQL 如下： 

# 數據更新
update hw_account set balance = balance + @change_num , version = version + 1 
  where id = 1 and version = n;
  
# 判斷是否成功  
if row < 1 {
   回滾
}

這種方案修改比較簡單， 但是依賴於數據計算，感覺不是特別友好。

排隊操作

通過 redis 或者 zk 的分佈式鎖，進行數據請求進行排隊。然後在進行數據更新。 

# 偽代碼

if (獲取分佈式鎖) {
  update hw_account set balance = @balance where id = 1;
} else {
  # 進入等待，或者進行自旋獲取鎖
}

常見問題

如果數據中存在 update_time 字段受影響的行數是多少？

update_time 的字段定義如下，如果數據為id = 1, status = 1 如果執行更新數據的 sql 為 

update hw_account set `status` = 1 where id = 1;

返回的受影響的行數為 0；

如果執行 update 更新但受影響的行數為 0 會加行鎖嗎？

會的， 執行更新的語句都會加行鎖（前提，事務內）

參考資料

mysql.com

到此這篇關於MySQL幾種更新操作的案例分析的文章就介紹到這瞭,更多相關MySQL 更新操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：