萬盛學電腦網

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

索引維護與優化實戰:識別無用索引、檔案群組隔離、碎片重組三板斧

索引並非「建立即萬事大吉」,每次 DML 都需同步維護所有相關索引,產生 **Write Amplification (寫入放大)**。本文提供三大實戰優化手段:**識別並清理無用索引、以檔案群組隔離 I/O、碎片重組與填充因子調優**,並附 T-SQL 監控腳本 (SQL Server 語法,Oracle 同理)。

一、識別並清理無用索引

原則:長期未被查詢使用的索引 = 純粹的寫入負擔 + 空間佔用 → **果斷刪除**

監控腳本 (SQL Server DMV)

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks, s.user_scans, s.user_lookups, s.user_updates,
    CASE WHEN s.user_seeks+s.user_scans+s.user_lookups = 0 THEN 'UNUSED' ELSE 'USED' END AS UsageStatus
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
  AND i.name NOT LIKE 'PK%'          -- 排除主鍵
  AND OBJECT_NAME(i.object_id) <> 'sysdiagrams'
  AND s.index_id NOT IN (0,1)        -- 排除 Heap/Clustered
ORDER BY TableName, IndexName;

Oracle 對應查詢

SELECT 
    i.table_name, i.index_name,
    u.name AS owner,
    COALESCE(ius.idx_scan_count, 0) AS total_scans,
    i.monitoring
FROM user_indexes i
LEFT JOIN user_ind_statistics ius ON i.index_name = ius.index_name
WHERE i.monitoring = 'YES'
ORDER BY total_scans;

啟用監控:ALTER INDEX idx_name MONITORING USAGE; → 觀察一段代表性週期 (含批次/月結) → SELECT * FROM v$object_usage;

清理決策樹

  1. **完全未使用** (Seeks/Scans/Lookups = 0) 且非 PK/UK/FK 強制索引 → DROP
  2. **極少使用** 但維護成本高 (大表、頻繁 DML) → 評估 **Invisible Index** (11g+) 測試影響 → 確認無影響再 DROP
  3. **重疊索引** (如 idx(a) + idx(a,b)) → 保留複合索引,刪除單列索引 (滿足最左前綴)

二、檔案群組隔離 I/O (Filegroup / Tablespace 分離)

核心邏輯:將 **非叢集索引** 放入**獨立檔案群組/表空間**,並映射至**不同物理磁碟/磁碟陣列 (LUN)**,實現:

  • 讀寫分離:資料檔 (Data) 與索引檔 (Index) 並行 I/O
  • 備份並行:資料備份與索引備份可同時進行
  • 恢復彈性:索引損壞可單獨重建,無需恢復資料檔

實作步驟

  1. 規劃獨立 LUN/磁碟組:DATA_LUN (資料)、INDEX_LUN (索引)、TEMP_LUN (暫存)、LOG_LUN (Redo)
  2. 建立 Tablespace/Filegroup 對應掛載點
  3. 建表/建索引時指定:
    -- Oracle
    CREATE INDEX idx_orders_cust ON orders(customer_id) TABLESPACE index_ts;
    
    -- SQL Server
    CREATE NONCLUSTERED INDEX idx_orders_cust ON orders(customer_id) ON index_fg;
  4. 關鍵限制:叢集索引 (含 PK) **無法** 與資料分離 (葉子即資料);若強制移動叢集索引,**整張表會跟著移動**

三、碎片重組與填充因子調優

碎片類型與指標

碎片類型指標影響
內部碎片頁面填充率低 (avg_page_space_used_in_percent < 70%)浪費記憶體/磁碟、增加掃描頁數
外部碎片邏輯順序 ≠ 物理順序 (avg_fragmentation_in_percent > 30%)順序掃描變隨機 I/O、預讀失效

重組策略

  • REORGANIZE (Online, 輕量):avg_fragmentation_in_percent 5-30% → 頁面重排、壓縮、不鎖表
    ALTER INDEX idx_name REORGANIZE;  -- SQL Server
    ALTER INDEX idx_name COALESCE;      -- Oracle (合併相鄰空閒塊)
    ALTER INDEX idx_name SHRINK SPACE;  -- Oracle (釋放空間回表空間)
  • REBUILD (Heavy, 可 ONLINE):> 30% 或結構變更 → 完全重建 B+ Tree、重新計算統計資訊、可調整 FILLFACTOR/PCTFREE
    ALTER INDEX idx_name REBUILD [ONLINE] [WITH (FILLFACTOR = 80)];  -- SQL Server
    ALTER INDEX idx_name REBUILD [ONLINE] [TABLESPACE idx_ts] [PCTFREE 20]; -- Oracle

填充因子 / PCTFREE 設定指引

表/索引特性建議值理由
靜態/唯讀/批次載入後唯讀100 / PCTFREE 0最大密度、最小空間、無分裂
隨機插入為主 (UUID、雜湊鍵)70-80 / PCTFREE 20-30預留空間吸收分裂、減少頁面分裂頻率
遞增鍵 (Identity、Sequence、Timestamp)90-100 / PCTFREE 0-10永遠追加寫入,無分裂,高密度安全
頻繁 UPDATE 導致行長度增長70-80 / PCTFREE 20-30預留原地更新空間、避免 Row Migration

四、自動化維護框架

-- 概念性排程 (SQL Server Agent / Oracle Scheduler / Cron + Shell)
DAILY:
  UPDATE STATISTICS 高頻表 (SAMPLE 20%)
  REORGANIZE 碎片 5-30% 索引

WEEKLY:
  GATHER_TABLE_STATS 全庫 (AUTO_SAMPLE_SIZE, CASCADE=>TRUE)
  REBUILD 碎片 >30% 索引 (ONLINE, 低峰期)
  CHECK UNUSED INDEXES (MONITORING 4 週零使用) → GENERATE DROP SCRIPT → 人工審核 → 執行

MONTHLY:
  INDEX ADVISOR (DMV / DBMS_ADVISOR) 分析 Workload → 產出 CREATE/DROP 建議
  容量規劃:索引增長趨勢 → 擴充 TABLESPACE / FILEGROUP

將「監控無用索引 → 隔離 I/O 路徑 → 碎片定期重組 → 智慧填充因子」四項機制制度化、自動化,即能將索引維護成本降至最低,並持續釋放查詢效能紅利。

copyright © 萬盛學電腦網 all rights reserved