MySQL 超長 SQL 導致錯誤：max_allowed_packet 陷阱與解決方案

前言

統計查詢常需排除已訂購用戶，直覺寫法是 NOT IN (子查詢) 或將子查詢結果拼接成超長 IN (...) 列表。後者雖避開子查詢效能問題，卻可能觸發 max_allowed_packet 限制導致報錯。本文剖析風險成因與解決之道。

兩種寫法對比

寫法一：子查詢（效能較差）

SELECT COUNT(1)
FROM day_resinlog_2012_06_12
WHERE mobile_number NOT IN (
  SELECT DISTINCT mobile_number FROM tbl_cartoon_order
);

• 優點：SQL 簡潔，無長度風險。
• 缺點：MySQL 對 NOT IN 子查詢優化較弱，大資料量下極慢。

寫法二：應用層拼接 IN 列表（隱藏風險）

# 應用程式先查出手機號列表
SELECT DISTINCT mobile_number FROM tbl_cartoon_order;
# 再拼接成超長 SQL
SELECT COUNT(1)
FROM day_resinlog_2012_06_12
WHERE mobile_number NOT IN ('13800000001','13800000002', ...);

• 優點：避開子查詢，執行計畫穩定。
• 缺點：若訂購用戶達百萬，SQL 長度超過 max_allowed_packet（預設 4MB/16MB），直接報錯 Packet too large。

根因：max_allowed_packet 限制

• 控制單次 Client/Server 通訊封包大小上限。
• 適用於 mysqld、mysql client、mysqldump 等所有連線。
• 超長 INSERT、REPLACE、LOAD DATA、超長 IN 列表皆會觸發。

解決方案

方案一：調大 max_allowed_packet（治標）

[mysqld]
max_allowed_packet = 64M  # 視實際最大 SQL 長度調整

# 臨時生效（無需重啟）
SET GLOBAL max_allowed_packet = 67108864;

注意：Client 端也需同步調大，否則連線仍受限。

方案二：改用 JOIN / NOT EXISTS（治本，推薦）

SELECT COUNT(1)
FROM day_resinlog_2012_06_12 d
LEFT JOIN tbl_cartoon_order o ON d.mobile_number = o.mobile_number
WHERE o.mobile_number IS NULL;

• 單條 SQL，無長度限制。
• MySQL 5.6+ 對 NOT EXISTS/LEFT JOIN ... IS NULL 優化極佳。
• 配合 mobile_number 索引，百萬級秒出結果。

方案三：分批處理 / 臨時表

• 將手機號批次寫入臨時表，再 JOIN 或 NOT IN (SELECT ... FROM temp)。
• 適合極大資料集（千萬級以上）。

最佳實踐清單

1. 優先重寫為 LEFT JOIN ... IS NULL 或 NOT EXISTS。
2. 確保關聯欄位有索引且資料型別一致。
3. 若必須拼接長 SQL，預估長度並同步調大 Server/Client max_allowed_packet。
4. 監控慢查詢日誌，定期檢視 Packet too large 錯誤。