萬盛學電腦網

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

SQL 調優實戰:從全表掃描到索引求精,執行時間從 40 秒降至毫秒級

問題 SQL

SELECT a.bill_class AS pol_code, b.bill_name AS pol_name, ... -- 多欄位
FROM shcvms.bill_grant_check a, shcvms.bill_class b
WHERE a.regist_date IS NOT NULL
  AND a.bill_class = b.bill_class
  AND a.bill_class IN ('1001','1093',...共 80+ 個常數);

執行時間:~40 秒。執行計畫:Hash Join + 兩張表全表掃描(BILL_GRANT_CHECK 732 萬行,453MB)。

分析與判斷

  • BILL_GRANT_CHECK 732 萬行,滿足 IN 列表的約 1.6%(11.7 萬行),屬於高選擇度篩選,極適合索引求精。
  • IN 列表為常數,最佳化工具可轉為 IN-list Iterator 逐一索引查找。
  • 無綁定變量,EXPLAIN PLAN 結果可信。

虛擬索引驗證(無風險測試)

-- 1. 建立虛擬索引(不佔空間、不維護、僅供最佳化工具參考)
CREATE INDEX billgrant_class ON shcvms.bill_grant_check(bill_class) NOSEGMENT;

-- 2. 啟用虛擬索引(Session 級別)
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

-- 3. 加 HINT 強制使用並觀察計畫
EXPLAIN PLAN FOR
SELECT /*+ INDEX(a billgrant_class) */ ...
FROM shcvms.bill_grant_check a, shcvms.bill_class b
WHERE a.regist_date IS NOT NULL
  AND a.bill_class = b.bill_class
  AND a.bill_class IN (...);

優化後執行計畫

Plan hash value: 2116188717
| Id | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
|  0 | SELECT STATEMENT             |                     |  117K |  10M  |   1234 (2) | 00:00:15 |
|  1 |  NESTED LOOPS                |                     |       |       |            |          |
|  2 |   INLIST ITERATOR            |                     |       |       |            |          |
|  3 |    TABLE ACCESS BY INDEX ROWID| BILL_GRANT_CHECK   |  117K |  10M  |   1234 (2) | 00:00:15 |
|* 4 |     INDEX RANGE SCAN         | BILLGRANT_CLASS     |  117K |       |    123 (1) | 00:00:02 |
|  5 |   TABLE ACCESS BY INDEX ROWID| BILL_CLASS          |     1 |    25 |     1 (0)  | 00:00:01 |
|* 6 |    INDEX UNIQUE SCAN         | BILL_CLASS_PK       |     1 |       |     0 (0)  | 00:00:01 |
  • Cost 從 151K 降至 1.2K,預估時間 30 分 → 15 秒(實測毫秒級)。
  • 關鍵運算子:INLIST ITERATOR + INDEX RANGE SCAN,逐一常數索引查找。

上線部署與效果

  1. 正式建立真實索引:CREATE INDEX billgrant_class ON shcvms.bill_grant_check(bill_class);
  2. 收集統計資訊:DBMS_STATS.GATHER_TABLE_STATS('SHCVMS','BILL_GRANT_CHECK');
  3. 觀察生產執行時間:從 40 秒降至 < 100ms,CPU 佔用大幅下降。

方法論總結

  1. 抓取高耗 SQL(AWR/ASDM/慢查詢日誌)。
  2. 獲取真實執行計畫(含 Predicate Information)。
  3. 判斷存取路徑是否最優(全掃描 vs 索引、Join 方式)。
  4. 用虛擬索引/測試環境驗證假設,量化 Cost 改善。
  5. 上線建立真實索引 + 統計資訊,持續監控。

避坑提醒

  • Oracle 隱含參數 _use_nosegment_indexes 僅供測試,生產嚴禁開啟。
  • 索引維護成本:大量 INSERT/UPDATE 表需評估寫入放大。
  • IN 列表過長(>1000)可能超過解析限制,改用臨時表 JOIN 或 EXISTS。
copyright © 萬盛學電腦網 all rights reserved