PHP 處理百萬級 MySQL 數據：30 條查詢優化鐵律與架構建議

前言：百萬行不是大數據，但足以拖垮寫得爛的 SQL

單表百萬行、幾十萬活躍用戶，對 MySQL 來說屬於「輕量級」負載。若查詢慢、CPU 高、鎖等待頻發，多半是 SQL 寫法、索引設計或架構層面有問題。本文彙整 30 條經過生產驗證的優化鐵律，涵蓋 SQL 改寫、索引策略、架構模式、PHP 代碼層面，助你從「能跑」進化到「跑得快、穩得住」。

一、SQL 改寫層面（1-12 條）

1. 避免全表掃描：WHERE、ORDER BY、JOIN ON 涉及的列必須建索引。
2. 拒絕 IS NULL 判斷：WHERE num IS NULL 導致全掃 → 改 DEFAULT 0 + WHERE num=0。
3. 拒絕 !=/<>：同樣放棄索引 → 改用 UNION ALL 分段查詢。
4. 拒絕 OR 連接條件：num=10 OR num=20 → UNION ALL 兩條 num=10 / num=20。
5. 慎用 IN/NOT IN：連續值用 BETWEEN；大列表改 EXISTS 或臨時表 JOIN。
6. 拒絕前導模糊 LIKE '%abc%'：全掃 → 上全文索引或 Elasticsearch。
7. 變量導致全掃：WHERE num=@num 編譯時未知值 → 強制索引 FORCE INDEX(idx) 或改為預處理綁定參數。
8. 拒絕列上做運算/函數：num/2=100 → num=200；SUBSTRING(name,1,3)='abc' → name LIKE 'abc%'；DATEDIFF(day, dt, '2023-01-01')=0 → dt >= '2023-01-01' AND dt < '2023-01-02'。
9. 不在 = 左側做表達式：同第 8 條，破壞 SARGable（可索引搜尋論）。
10. 組合索引必須滿足最左前綴：INDEX(a,b,c) 查詢必帶 a，且順序最好一致。
11. 拒絕無意義查詢：SELECT * FROM t WHERE 1=0 建空表 → 用 CREATE TABLE ... LIKE t。
12. EXISTS 替代 IN（子查詢）：SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.num=a.num)，半連接優化、避免物化子查詢。

二、索引策略層面（13-16 條）

13. 索引非萬能：低區分度列（如性別男/女各半）建索引無效，優化器仍選全掃。
14. 索引非越多越好：單表 ≤ 6 個；寫入密集表更要克制，每個索引 = 一棵 B+ 樹維護成本。
15. 慎改聚簇索引（PK）列：PK 值變更 = 整行物理移動 + 所有二級索引更新，極重。業務主鍵建議用無業務意義的自增/UUID。
16. 覆蓋索引優先：SELECT a,b FROM t WHERE c=? → INDEX(c,a,b)，EXPLAIN 顯示 Using index，零回表。

三、字段設計與查詢習慣（17-21 條）

17. 優先數值型：字串比較逐字符，數值一次比較；存儲也更小。
18. VARCHAR 代替 CHAR：變長省空間、查詢掃描少字節。
19. 禁用 SELECT *：只取需要列，減少網路傳輸、緩存污染、覆蓋索引機會增大。
20. 表變量代替臨時表：小結果集用 DECLARE @t TABLE(...)（僅主鍵索引），大數據慎用。
21. 減少臨時表建刪頻率：重複引用大表數據集才用臨時表，一次性事件用導出表（子查詢/CTE）。

四、臨時表與游標最佳實踐（22-27 條）

22. 大量一次性插入臨時表 → SELECT INTO #tmp（少 Log、快）；小量 → CREATE TABLE #tmp + INSERT（緩解系統表鎖）。
23. 存儲過程結束 顯式清理：TRUNCATE #tmp; DROP TABLE #tmp，避免系統表長時間鎖定。
24. 游標慎用：>1 萬行必改寫；集合思維（JOIN/窗口函數/CTE）優於逐行處理。
25. 小結果集 FAST_FORWARD 游標可接受（只讀、單向、無並發）。
26. 先找集合解法，實在不行再用游標/臨時表；開發期可雙寫 Benchmark 比較。

五、事務與併發控制（28-30 條）

28. SET NOCOUNT ON（存儲過程首尾）：減少 DONE_IN_PROC 網路往返。
29. 拒絕大事務：長事務 = 長鎖 = 併發殺手。拆分、異步、補償機制替代。
30. 拒絕向客戶端返回大結果集：分頁、流式輸出、導出文件下載，單次 < 1 萬行。

六、架構層面補充建議（PHP 專屬）

• 連接池：Swoole/Workerman 長連接池或 PHP-FPM + mysqli::persistent（慎用，注意連接數上限）。
• 預處理語句：$stmt = $mysqli->prepare(...) 防注入 + 執行計劃複用。
• 只讀從庫：主寫從讀，PHP 代碼層路由 SELECT 到 Slave，降低 Master 壓力。
• 緩存策略：Redis 緩存熱點查詢結果、計數器、Session；Cache-Aside 模式 + 合理 TTL + 防穿透/雪崩/擊穿。
• 異步處理：寫入後發 MQ（Kafka/RabbitMQ），消費者異步更新搜索索引、統計表、發通知，請求響應 < 100ms。
• 監控告警：pt-query-digest 分析慢查詢、Prometheus + Grafana 監控 QPS/延遲/鎖等待/複製延遲。

結語

百萬級數據優化，核心是「讓索引生效、讓事務變短、讓架構分層」。30 條鐵律不是死規矩，而是「遇到慢查詢先檢查哪幾條」的檢查清單。養成 EXPLAIN 先行、SHOW PROFILE 定位、pt-query-digest 定期巡檢的習慣，PHP + MySQL 系統才能在業務增長中保持穩健。