MySQL 基礎調度策略深度解析：讀寫優先級、鎖機制與查詢修飾符實戰

核心調度原則

MySQL 採用**表級鎖** (MyISAM) 或**行級鎖** (InnoDB) 實現並發控制，其預設調度策略遵循兩條鐵律：

1. 寫入請求按到達順序處理：先來先服務，公平隊列。
2. 寫入優先級高於讀取：寫操作可插隊至等待中的讀操作之前。

設計初衷：保護數據一致性——寫操作修改數據結構，必須獨佔訪問；讀操作僅需共享鎖，允許並發讀。

鎖相容性矩陣 (MyISAM 表鎖)

InnoDB 行級鎖更細粒度，但意向鎖 (IS/IX) 邏輯一致。

三大查詢修飾符改變調度行為

1. LOW_PRIORITY (寫操作降級)

INSERT LOW_PRIORITY INTO logs ...;
UPDATE LOW_PRIORITY accounts SET balance = balance - 100 WHERE id = 1;
DELETE LOW_PRIORITY FROM temp_data WHERE created < NOW() - INTERVAL 1 DAY;

• 適用：INSERT, UPDATE, DELETE, REPLACE, LOAD DATA。
• 效果：寫操作等待**所有當前及後續到達的讀操作**完成才執行。
• 風險：高讀負載下寫入可能**永久飢餓**。
• 場景：非即時性批量匯入、日誌寫入、資料清理任務。

2. HIGH_PRIORITY (讀操作升級)

SELECT HIGH_PRIORITY * FROM orders WHERE status = 'pending';

• 適用：SELECT。
• 效果：讀操作插隊至**正在等待的寫操作**之前 (即使寫操作非 LOW_PRIORITY)。
• 風險：可能導致寫入飢餓，破壞數據一致性時間窗口。
• 場景：實時儀表板、緊急人工介入查詢。

3. DELAYED (異步寫入)

INSERT DELAYED INTO access_log (url, ip) VALUES ('/home', '1.2.3.4');
REPLACE DELAYED INTO cache (key, value) VALUES ('k1', 'v1');

• 適用：INSERT, REPLACE (MyISAM/MEMORY/ARCHIVE 引擎；InnoDB 忽略 DELAYED 警告但按普通 INSERT 處理)。
• 機制：客戶端立即返回 OK，行數據進入內存隊列，專用執行緒批量寫入磁盤。
• 讀操作到來時隊列掛起，讀完恢復寫入。
• 風險：崩潰丟失未刷盤數據；無法獲取 AUTO_INCREMENT 值；LAST_INSERT_ID() 返回 0。
• 場景：高頻非關鍵日誌、點擊流、監控指標寫入。

版本支援對照表

實戰調優決策樹

是否為關鍵業務寫入？
├─ 是 → 不使用任何修飾符，接受預設高優先級
└─ 否 → 讀負載高且寫入可延遲？
        ├─ 是 → INSERT/UPDATE/DELETE 加 LOW_PRIORITY
        └─ 否 → 極高頻非關鍵日誌？
                ├─ 是 → MyISAM/MEMORY 表用 INSERT DELAYED
                └─ 否 → 保持預設，優化索引/查詢結構

監控關鍵指標

• Table_locks_waited / Table_locks_immediate：鎖等待比率。
• Innodb_row_lock_waits / Innodb_row_lock_time_avg：行鎖等待次數與平均耗時。
• Slow_queries 配合 long_query_time 發現被阻塞的慢查詢。

理解調度策略與鎖機制的交互關係，能幫助你在高併發場景下做出正確的權衡：是在數據一致性、寫入吞吐、讀取延遲三者間找到業務最優解，而非盲目追求單一指標極致。