萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

百萬級 MySQL SQL 優化 30 條鐵律:從全表掃描到索引覆蓋的系統化指南

面對百萬級甚至億級數據,SQL 優化不是靠直覺,而是遵循確定性原則。本文提煉 30 條通用優化鐵律,涵蓋索引設計、查詢改寫、架構決策,適用於 MySQL、PostgreSQL、Oracle 等主流關係型資料庫。

索引與訪問路徑(1-8)

  1. 避免全表掃描: WHERE、ORDER BY、JOIN ON 條件列必須建立索引。
  2. 拒絕 IS NULL 判斷: 導致索引失效,改用默認值 0/-1 或特殊標記值。
  3. 拒絕 != / <>: 同樣導致全掃,改用 IN / UNION ALL 正向表達。
  4. 拒絕 OR 連接條件: 除非兩側各有索引且優化器能合併,否則改 UNION ALL。
  5. 慎用 IN / NOT IN: 連續值用 BETWEEN;子查詢 IN 改 EXISTS。
  6. 避免前導模糊 LIKE '%abc%': 全掃必然,改全文索引或 Elasticsearch。
  7. 變量導致全掃: 優化器編譯時不知變量值,無法選擇索引。改用 FORCE INDEX 或應用層拼接常量。
  8. 拒絕索引列運算/函數: WHERE YEAR(dt)=2023 → WHERE dt BETWEEN '2023-01-01' AND '2023-12-31'。

復合索引與查詢結構(9-15)

  1. 最左前綴原則: 復合索引 (a,b,c),查詢必須從 a 開始連續匹配,範圍列後失效。
  2. 覆蓋索引: SELECT 列全部包含在索引中,Extra: Using index,避免回表。
  3. 避免無意義查詢: SELECT ... WHERE 1=0 建空表 → 用 CREATE TABLE LIKE。
  4. EXISTS 替代 IN 子查詢: 半連接語義,遇到匹配即停止,效能更穩定。
  5. 低選擇性索引無效: 性別/狀態碼(男/女各半)建索引無意義,優化器會拒絕使用。
  6. 索引不是越多越好: 單表 ≤ 5-6 個,寫入每多一個索引多一次 B+ 樹維護。
  7. 避免更新聚簇索引鍵: 主鍵物理順序變更導致頁分裂,業務主鍵盡量不可變。

數據類型與 Schema 設計(16-21)

  1. 優先數字型: 整數比較一次,字串逐字符比較,連接效能差數倍。
  2. VARCHAR 替代 CHAR: 變長節省空間,小字段檢索更快。
  3. 拒絕 SELECT *: 只取需要列,減少網絡傳輸、緩存污染、回表開銷。
  4. 表變量替代臨時表: 表變量僅主鍵索引,適合小結果集;大數據用臨時表並建索引。
  5. 臨時表生命週期管理: 大量據用 SELECT INTO 避免大量 Binlog;小數據先 CREATE 再 INSERT;結束時 TRUNCATE + DROP 釋放鎖。
  6. 遊標最後手段: >1 萬行必須改寫為集合操作(JOIN/窗口函數/CTE)。

事務與併發(22-26)

  1. 集合思維優於逐行: 先找 Set-based 方案,再考慮遊標/臨時表。
  2. 小結果集 FAST_FORWARD 遊標可接受: 多表關聯取彙總時。
  3. 存儲過程/觸發器首行 SET NOCOUNT ON: 禁用 DONE_IN_PROC 封包,減少網絡往返。
  4. 拒絕大事務: 長事務持有鎖、阻塞 Purge、膨脹 Undo Log,拆分為小批次提交。
  5. 拒絕大結果集直傳客戶端: 分頁、流式處理、異步導出,避免 OOM 與網絡阻塞。

進階技巧與架構層面(27-30)

  1. ICP (Index Condition Pushdown): 5.6+ 將 WHERE 條件下推到存儲引擎層過濾,減少回表。
  2. MRR (Multi-Range Read): 範圍查詢將離散 I/O 轉順序 I/O,配合 read_rnd_buffer_size。
  3. Histogram 統計資訊: 8.0+ 非索引列也能讓優化器估算選擇性,解決「值傾斜」誤判。
  4. Read/Write Splitting + ProxySQL: 架構層面分流讀寫,主庫專注寫入,從庫承擔分析查詢。

驗證清單:EXPLAIN 關鍵欄位

SQL 優化沒有捷徑,只有「理解優化器成本模型 → 預測訪問路徑 → 驗證 EXPLAIN → 迭代調整」的工程化閉環。將這 30 條鐵律內化為代碼審查標準,百萬級數據也能跑出毫秒級響應。

copyright © 萬盛學電腦網 all rights reserved
欄位優秀特徵危險特徵
typeref / eq_ref / rangeALL / index
key命中預期索引NULL
rows接近實際返回行數全表行數
ExtraUsing index / Using whereUsing filesort / Using temporary