SQL秘技(2): 大批量Update / UPSERT

等等個篇文有bug，如果scenario咁簡单單直接update + join就得，零分😂

夜啲重作。

情景

假設有噉嘅一個Table t:

 ID | Value
----|-------
 A  | 10
 B  | 20
 C  | 30

而分別想為A嘅Value +3，B嘅+5，C嘅+7。

最直觀嘅解法係:

UPDATE t SET Value=Value+3 WHERE ID='A';
UPDATE t SET Value=Value+5 WHERE ID='B';
UPDATE t SET Value=Value+7 WHERE ID='C';

實際情景可能係: 倉庫做完盤點，要根據盤點結果對一堆SKU嘅庫存量做偏移。

問題

直觀解法嘅問題係round trip time。

電腦之中CPU通常好快，I/O好慢。I/O之中自從NVMe成為主流之後最慢通常係Network。尤其Web同DB唔係喺同一個Availability Zone嘅話，一個來回可能就10ms。

要更新10000個SKU嘅話，順序執行就需要100秒。

Anti-pattern 1: 並行

順序(sequential)執行慢…噉不如並行(parallel)執行？

喺application層面拆開parallel做…就一定唔可以共用同一個DB transaction，因為SQL語法層面根本無得做await-async。

如果唔係同一個transaction，business logic要處理就有排煩…

Anti-pattern 2: MySQL REPLACE

MySQL有個獨有嘅INSERT變種語法 — REPLACE。

但REPLACE只可以直接提供新嘅絕對值，而唔係相對值/偏移量，即係:

REPLACE INTO t (ID, Value) VALUES ('A',13),('B',25),('C',37)

所以，通常你要先SELECT，然後再由Application計算。

若果你噉樣做，請最少喺SELECT後面加上…FOR UPDATE，否則你盤數會錯。

想像一下發生噉嘅情況

Trx #1: SELECT ID, Value FROM t WHERE ID='A';
-- 一輪運算同時…
Trx #2…