Index 管理建議

部分內容由 LLM 生成，尚未經過人工驗證。

Index 的雙面性

Index 能大幅加速 SELECT 查詢，但對寫入操作有額外成本：

INSERT：每新增一筆資料，所有相關 Index 都需要同步更新
UPDATE：若更新的欄位有 Index，舊 Entry 需刪除、新 Entry 需插入
DELETE：刪除資料時，Index Entry 也需一併清除

Index 越多，寫入越慢，儲存空間也越大。冗餘或低效的 Index 應定期清理。

找出冗餘索引

SQL Server

查詢自上次重啟以來從未被使用的 Index：

sql-server-unused-indexes.sql

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
WHERE i.type_desc != 'HEAP'
  AND ISNULL(s.user_seeks, 0) = 0
  AND ISNULL(s.user_scans, 0) = 0
  AND ISNULL(s.user_lookups, 0) = 0
ORDER BY ISNULL(s.user_updates, 0) DESC;

PostgreSQL

查詢掃描次數為 0 的 Index：

postgres-unused-indexes.sql

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename, indexname;

MySQL

透過 sys schema 查詢未使用的 Index（需啟用 Performance Schema）：

mysql-unused-indexes.sql

SELECT *
FROM sys.schema_unused_indexes;

Oracle

Oracle 需手動開啟監控，再查詢使用狀況：

oracle-index-monitoring.sql

-- 1. 開啟監控
ALTER INDEX idx_name MONITORING USAGE;

-- 2. 等待足夠觀察期後，查詢使用狀況
SELECT index_name, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE index_name = 'IDX_NAME';

統計數據的限制

資料庫	重啟後重置	備註
SQL Server	✅ 是	`sys.dm_db_index_usage_stats` 隨服務重啟清空
PostgreSQL	✅ 是	`pg_stat_user_indexes` 隨服務重啟清空
MySQL	✅ 是	Performance Schema 資料可能隨重啟重置
Oracle	否	需手動觸發 `MONITORING USAGE`

如果資料庫近期曾重啟，統計數據可能不具代表性。建議在系統穩定運行一段時間後再判斷是否刪除 Index。

建立索引前的考量

在新增 Index 前，先問以下幾個問題：

是否已有相似 Index？
- 若已存在覆蓋相同欄位組合的 Index，新 Index 可能冗餘
- 檢查是否能擴展現有 Index（如新增 Included Column）
Selectivity 是否夠高？
- Selectivity = 唯一值數量 / 總筆數
- 例如：性別欄位（M/F）Selectivity 極低，加 Index 效益有限
- 建議先查詢 COUNT(DISTINCT col) / COUNT(*) 評估
是否常作過濾條件？
- 只有在 WHERE、JOIN ON、ORDER BY 等子句中頻繁出現的欄位，才值得建立 Index
- 偶爾查詢一次的欄位不需要 Index
讀寫比例如何？
- 讀多寫少（OLAP / 報表）：適合多建 Index
- 寫多讀少（OLTP / 高頻交易）：應減少 Index