萬盛學電腦網

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

SQL 資料庫效能調優大全:百萬級資料優化 34 條實戰準則

SQL 資料庫效能調優大全:百萬級資料優化 34 條實戰準則

網路上 SQL 優化教程雜亂無章,本文整理 34 條經實戰驗證的優化準則,涵蓋表順序、WHERE 條件順序、索引使用、函數陷阱、統計資訊、緩存、鎖定、分頁等核心主題,適合百萬級以上資料量的 OLTP/OLAP 場景。

一、查詢結構優化

  1. FROM 表順序(RBO):Oracle 由右至左解析,將記錄最少的表放在最後(基礎表)。多表聯結時選「交叉表」作為基礎表。
  2. WHERE 條件順序(RBO):自下而上解析,表聯結條件寫在最前,過濾性最強的條件寫在最後。
  3. 避免 SELECT *:會查詢數據字典轉換欄位名,增加解析開銷。
  4. 減少資料庫往返:批次處理、Array Binding、Stored Procedure 減少網路延遲。
  5. 調大 ARRAYSIZE:SQL*Plus/Forms/Pro*C 中設 200,減少 Fetch 次數。
  6. 用 DECODE/CASE 避免重複掃描:單次掃描完成多條件分支。
  7. 整合無關查詢:多個簡單查詢合併為一條(即使無關聯)。

二、DML 優化

  1. 刪除重複資料用 ROWID:DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
  2. 全表清空用 TRUNCATE 代替 DELETE:不產生 UNDO、不觸發 Trigger、重置 HWM、極快。(注意:TRUNCATE 是 DDL,不可回滾)
  3. 多用 COMMIT:釋放鎖、UNDO 空間、Redo Log Buffer、內部管理開銷。
  4. 用 WHERE 代替 HAVING:HAVING 在彙總後過濾,WHERE 在彙總前過濾,減少中間結果集。
  5. 減少子查詢對表的重複存取:改寫為 JOIN 或使用分析函數。

三、索引與存取路徑

  1. 善用內部函數提升效率:掌握分析函數、KEEP/DENSE_RANK 等進階語法。
  2. 使用表別名並加前綴:減少解析時間、避免欄位歧義。
  3. EXISTS 代替 IN / NOT EXISTS 代替 NOT IN:NOT IN 會觸發全表掃描與排序合併;EXISTS 遇滿足即返回。
  4. 識別低效 SQL:查詢 V$SQLAREA 找出緩存命中率低、磁碟讀取高的語句。
  5. 索引策略:大表建索引效果明顯;小表全掃描可能更快。索引需維護(INSERT/DELETE/UPDATE 額外 4-5 次 I/O),定期 ALTER INDEX REBUILD。
  6. EXISTS 代替 DISTINCT:一對多查詢去重時,EXISTS 只需掃描一次即停止。
  7. SQL 關鍵字大寫:Oracle 解析時會將小寫轉大寫,大寫省去轉換。
  8. Java 中少用 + 串聯 SQL:用 PreparedStatement + 參數綁定。
  9. 避免索引列用 NOT:導致全表掃描。
  10. 避免索引列參與運算/函數:SAL*12>25000 → SAL>25000/12。
  11. >= 代替 >:DEPTNO>=4 直接定位;>3 需先定位 3 再掃描。
  12. UNION 代替 OR(索引列):OR 導致全掃;UNION 可分別走索引再合併。若有未索引列則視情況。
  13. IN 代替 OR:LOC_ID IN (10,20,30) 通常優於多個 OR。
  14. 避免索引列 IS NULL/IS NOT NULL:NULL 不存入索引(單列/全空複合索引),改用 >=0 等等價條件。
  15. 複合索引必須引用第一欄:否則優化器忽略索引。
  16. UNION ALL 代替 UNION:免去排序去重,但會保留重複行,需業務允許。
  17. WHERE 代替 ORDER BY:ORDER BY 僅在嚴格條件下用索引;WHERE 可直接走索引篩選。
  18. 避免隱式類型轉換:EMP_TYPE=123 導致 TO_NUMBER(EMP_TYPE) 索引失效,顯式轉換或參數化。
  19. 警惕 WHERE 陷阱:!=、||、+、同欄位自我比較皆導致全掃。
  20. 大表小表判斷:超過 30% 資料量用索引優勢不明顯;但通常索引仍比全掃快數倍。
  21. 避免昂貴操作:DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 觸發 SORT,需足夠 SORT_AREA_SIZE。
  22. 優化 GROUP BY:先 WHERE 過濾再 GROUP BY,如 WHERE JOB IN ('PRESIDENT','MANAGER') GROUP BY JOB。

小結

優化無銀彈,需結合執行計劃、統計資訊、業務模式綜合判斷。建立「先測量、再優化、再驗證」的閉環習慣,才能在百萬級資料下穩定產出高效能 SQL。

copyright © 萬盛學電腦網 all rights reserved