面對百萬級甚至億級數據,SQL 優化不是靠直覺,而是遵循確定性原則。本文提煉 30 條通用優化鐵律,涵蓋索引設計、查詢改寫、架構決策,適用於 MySQL、PostgreSQL、Oracle 等主流關係型資料庫。
IS NULL 判斷: 導致索引失效,改用默認值 0/-1 或特殊標記值。!= / <>: 同樣導致全掃,改用 IN / UNION ALL 正向表達。OR 連接條件: 除非兩側各有索引且優化器能合併,否則改 UNION ALL。IN / NOT IN: 連續值用 BETWEEN;子查詢 IN 改 EXISTS。LIKE '%abc%': 全掃必然,改全文索引或 Elasticsearch。FORCE INDEX 或應用層拼接常量。WHERE YEAR(dt)=2023 → WHERE dt BETWEEN '2023-01-01' AND '2023-12-31'。(a,b,c),查詢必須從 a 開始連續匹配,範圍列後失效。Extra: Using index,避免回表。SELECT ... WHERE 1=0 建空表 → 用 CREATE TABLE LIKE。EXISTS 替代 IN 子查詢: 半連接語義,遇到匹配即停止,效能更穩定。VARCHAR 替代 CHAR: 變長節省空間,小字段檢索更快。SELECT *: 只取需要列,減少網絡傳輸、緩存污染、回表開銷。SELECT INTO 避免大量 Binlog;小數據先 CREATE 再 INSERT;結束時 TRUNCATE + DROP 釋放鎖。FAST_FORWARD 遊標可接受: 多表關聯取彙總時。SET NOCOUNT ON: 禁用 DONE_IN_PROC 封包,減少網絡往返。read_rnd_buffer_size。| 欄位 | 優秀特徵 | 危險特徵 |
|---|---|---|
| type | ref / eq_ref / range | ALL / index |
| key | 命中預期索引 | NULL |
| rows | 接近實際返回行數 | 全表行數 |
| Extra | Using index / Using where | Using filesort / Using temporary |