MySQL 實現高併發隊列：從簡單 SELECT/UPDATE 到原子操作的演進

問題場景

用表模擬隊列：每次取滿足條件的首行，再 DELETE 或更新狀態。無併發時 SELECT ... LIMIT 1; UPDATE ... 足矣。併發下，兩語句間可能有其他 SELECT 搶走同一行。

方案演進

階段 1：存儲過程加鎖（MySQL 5+）

將查詢與修改封裝為原子操作，但實現複雜，性能一般。

階段 2：利用 UPDATE 原子性 + LAST_INSERT_ID 技巧

UPDATE targets 
SET status='D', id=LAST_INSERT_ID(id) 
WHERE status='C' LIMIT 1;

SELECT * FROM targets 
WHERE ROW_COUNT() > 0 AND id=LAST_INSERT_ID();

原理：UPDATE 原子修改首行並將 id 寫入 LAST_INSERT_ID()；ROW_COUNT() 判斷是否真有行被更新，解決隊列空時 LAST_INSERT_ID() 不變的誤判。

階段 3：優先級隊列的性能陷阱

-- ❌ 帶 ORDER BY 的 UPDATE 極慢（Bug #12915）
UPDATE targets SET status='D' WHERE status='C' ORDER BY schedule ASC LIMIT 1;

-- ✅ 分離查詢與更新則極快
SELECT id FROM targets WHERE status='C' ORDER BY schedule ASC LIMIT 1;
UPDATE targets SET status='D' WHERE id=...;

MySQL 5.0.15 僅部分修復，WHERE + ORDER BY 仍慢，上述技巧在優先級場景失效。

階段 4：多表關聯 UPDATE 繞過 ORDER BY 限制

UPDATE targets,
       (SELECT id FROM targets 
        WHERE status='C' AND schedule < CURRENT_TIMESTAMP 
        ORDER BY schedule ASC LIMIT 1) tmp
SET status='D' 
WHERE targets.id = LAST_INSERT_ID(tmp.id);

SELECT * FROM targets 
WHERE ROW_COUNT() > 0 AND id = LAST_INSERT_ID();

子查詢先按優序取 ID，主 UPDATE 僅按主鍵精準匹配，規避 ORDER BY 性能陷阱。

生產建議

1. 無優先級隊列：直接用階段 2 方案，簡單高效
2. 有優先級：用階段 4 多表關聯寫法
3. 極高併發：考慮 Redis List / RabbitMQ 等專用隊列中間件