索引並非「建立即萬事大吉」,每次 DML 都需同步維護所有相關索引,產生 **Write Amplification (寫入放大)**。本文提供三大實戰優化手段:**識別並清理無用索引、以檔案群組隔離 I/O、碎片重組與填充因子調優**,並附 T-SQL 監控腳本 (SQL Server 語法,Oracle 同理)。
原則:長期未被查詢使用的索引 = 純粹的寫入負擔 + 空間佔用 → **果斷刪除**
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;
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;
idx(a) + idx(a,b)) → 保留複合索引,刪除單列索引 (滿足最左前綴)核心邏輯:將 **非叢集索引** 放入**獨立檔案群組/表空間**,並映射至**不同物理磁碟/磁碟陣列 (LUN)**,實現:
DATA_LUN (資料)、INDEX_LUN (索引)、TEMP_LUN (暫存)、LOG_LUN (Redo)-- 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;
| 碎片類型 | 指標 | 影響 |
|---|---|---|
| 內部碎片 | 頁面填充率低 (avg_page_space_used_in_percent < 70%) | 浪費記憶體/磁碟、增加掃描頁數 |
| 外部碎片 | 邏輯順序 ≠ 物理順序 (avg_fragmentation_in_percent > 30%) | 順序掃描變隨機 I/O、預讀失效 |
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 (釋放空間回表空間)
> 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
| 表/索引特性 | 建議值 | 理由 |
|---|---|---|
| 靜態/唯讀/批次載入後唯讀 | 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 路徑 → 碎片定期重組 → 智慧填充因子」四項機制制度化、自動化,即能將索引維護成本降至最低,並持續釋放查詢效能紅利。