MySQL效能最佳化盲區（高併發情況下，事務內的資料先更新還是先查詢？）

小松聊PHP进阶發表於2024-03-15
MySql

近期看到了一個前阿里資深開發的學術分析影片：
高併發情況下，一個事務內有更新操作還有查詢操作，那是先更新好，還是先無鎖查詢好？
僅70秒的影片，深感學問太深，但是海哥講的有待補充，於是寫下了這篇文章，作為補充。

鳴謝：前阿里資深開發極海Channel的技術分享。

先說答案

這是個開放性的問題，必須看業務場景，拋開業務場景談架構設計，都是耍流氓。

  • 場景1：如果update語句的引數操作依賴於查詢操作，那麼必須先查詢，再更新，否則update語句的引數都湊不齊。
  • 場景2：如果update語句的引數操作不依賴於查詢操作，但兩個操作的是一張表，業務強制要求select獲取的資料必須是最新的，則也需要先更新再讀取。
  • 場景3：以上兩種情況除外，則優先考慮先查詢再更新。

場景1受引數限制，順序毋庸置疑。
場景2受業務限制，順序毋庸置疑。
場景3是為了效能最佳化，才去選擇的方案。

測試表

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '數字列',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (1, 1);
INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (2, 2);
INSERT INTO `temp`.`cs` (`id`, `num`) VALUES (3, 3);


假設場景2的程式碼如下：
start transaction;
update cs set num = 13 where id = 1;
SELECT * FROM `cs`;
commit;


場景3的程式碼如下：
start transaction;
SELECT * FROM `cs`;
update cs set num = 13 where id = 1;
commit;

對場景2，同一個事務內，先執行update，事務沒提交，能讀取到最新的資料嗎？

能。

步驟 SQL 補充
1 start transaction; 開啟事務
2 select num from cs where id = 1; num值是1
3 update cs set num = 12 where id = 1; 更新num值為12
4 select num from cs where id = 1; num值是12
5 rollback; 回滾事務

都說不加鎖的select是快照讀，為什麼select還能讀取當前事務的最新資料？

根據現象反推，個人認為：
不加鎖的select是快照讀，針對的是事務之外的不加鎖的select，MySQL RR的隔離級別，只要更新的事務未提交，其它事務就讀取不到更改的新資料。
當前事務內的select，就是不加鎖的當前讀（個人稱謂）。當然，真正的當前讀概念是為了保證讀的最新資料，必須加鎖。

對場景3的效能最佳化原理分析

得知道4個前提：

  1. 場景3的實現，不會影響業務和開發。
  2. 因為select沒有加S或X鎖，所以不會阻塞。
  3. update語句會上X鎖，可能是行鎖、間隙鎖，或者表鎖（受索引、where條件的影響），所以併發請求過來後，其它事務的update有被阻塞的可能。
  4. 用事務，就需要InnoDB引擎，InnoDB引擎支援行級鎖，如果確定update的X鎖，在併發情況下鎖定的範圍沒有交集，這種最佳化方式起不到作用，這意味著不會阻塞。
  • 如果先更新再查詢：
    分析原理：
    事務A的update語句會上鎖，併發情況下阻塞事務B的update操作，如果事務A的select是個慢查詢，事務A的X鎖釋放需要等到事務提交，而不是update語句本身執行完畢，這就意味著事務A select的環節，X鎖也未釋放，從而阻塞其它事務的update，降低效能。
    實操模擬：
步驟 事務A 事務B 補充
1 start transaction; start transaction; 雙方開啟事務，模擬併發請求
2 update cs set num = 1234 where id = 1; update cs set num = 1234; 兩個不同where範圍的update，模擬線上的場景
3 / 阻塞 事務A的行X鎖，阻塞了事務B的表X鎖
4 select * from cs where id = 1; 阻塞 這一步很重要，最佳化就是為了避免這一步的阻塞耗時，特別是慢查詢
5 commit; 阻塞 事務A提交
6 / select * from cs where id = 1; 事務A完成，事務B不會再阻塞了
7 / commit; 結束事務B
  • 如果先查詢再更新：
    分析原理：事務A的select語句不會上鎖，此時事務不會導致事務B阻塞，如果執行到事務A執行到update，才回去上X鎖，直到事務提交鎖資源釋放，即使事務A的select是一個慢查詢，也不會加大事務A釋放鎖資源的事件，進而減少事務B的阻塞時間。
    實操模擬：
步驟 事務A 事務B 補充
1 start transaction; start transaction; 雙方開啟事務，模擬併發請求
2 select * from cs where id = 1; select * from cs where id = 1; 兩個事務不加鎖不阻塞，這一步的阻塞時間省了
3 update cs set num = 1234 where id = 1; update cs set num = 1234; 兩個事務更新
4 / 阻塞 兩個X鎖範圍有衝突，阻塞
5 commit; 阻塞 事務A提交
6 / commit; 事務B提交

所以說，只要業務允許，調整SQL語句的執行順序，高併發情況下，就能得到不小的效能提升，但是這一點很容易忽略。

相關文章