萬盛學電腦網

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

資料庫索引優缺點全方位權衡:效能提升與維護成本的平衡藝術

索引的五大核心優勢

  1. 唯一性約束保障
    CREATE UNIQUE INDEX / PRIMARY KEY / UNIQUE CONSTRAINT 物理層面強制資料唯一,比應用層檢查可靠萬倍。
  2. 查詢速度數量級提升
    B+ 樹 O(log N) 定位 vs 全表掃描 O(N)。百萬行表,索引查詢通常 < 10ms,全掃 > 1000ms。這是建立索引的最核心理由。
  3. 加速 JOIN 連接與參考完整性
    外鍵列有索引 → Nested Loops Join 高效;無索引 → Hash Join 需建立雜湊表或 Sort-Merge Join 需排序。
    參考完整性檢查 (刪除父表行時檢查子表) 同樣受益於子表 FK 索引。
  4. 消除 Sort/Group By 運算子
    索引葉層天然有序。ORDER BY indexed_col、GROUP BY indexed_col 直接輸出有序流,省略昂貴的 Sort (記憶體/TempDB 溢寫風險)。
  5. 啟用優化器進階策略
    索引存在本身即提供統計資料,優化器可評估更多計畫選項:Index Intersection、Index Join、Skip Scan、Index Condition Pushdown 等。

索引的四大代價與風險

  1. 寫入效能折損
    每次 INSERT/UPDATE/DELETE 需同步維護所有相關索引 B+ 樹。
    實測:10 個非叢集索引的表,寫入延遲約為無索引表的 3-5 倍。
  2. 儲存空間佔用
    非叢集索引 = 鍵值 + Row Locator (RID/Clustered Key) + Included Columns。
    叢集索引 = 全表資料 (葉層即資料)。
    經驗值:索引總大小常達資料表 50-200%。
  3. 維護操作開銷
    重建/重組索引、更新統計資料、檢查完整性 (DBCC CHECKDB) 皆隨索引數量線性增長。
    大表重建可能耗時數小時、產生大量 Transaction Log、佔用 TempDB。
  4. 優化器誤判風險
    統計資料過期/不準 → 優化器選錯索引 → 效能災難。
    過多索引增加優化器編譯時間、計畫快取膨脹。

量化決策模型:建立索引的 ROI 計算

Index_ROI = (Query_Frequency × Query_Speedup_Factor × Business_Value_Per_Query)
            / (Write_Frequency × Write_Overhead_Per_Index 
               + Storage_Cost_GB_Month × Index_Size_GB
               + Maintenance_Window_Cost_Per_Hour × Maintenance_Hours)

決策檢查清單 (滿足 ≥ 4 項才建索引)

  • [ ] 查詢頻率高 (日均 > 1000 次) 或 核心業務路徑
  • [ ] 選擇性 < 5% (或範圍查詢返回 < 10% 總行數)
  • [ ] 當前執行計畫為 Scan / Hash Match / Sort 且耗時 > 500ms
  • [ ] 寫入頻率可接受 (日均寫入 < 10萬 行 或 非核心寫入路徑)
  • [ ] 可透過 INCLUDE 覆蓋查詢,避免 Key Lookup
  • [ ] 無現有索引可複用 (避免重疊索引)

應建索引的 6 類黃金列 (優先級排序)

  1. 主鍵列:叢集索引首選 (窄、唯一、不變、遞增)
  2. 高選擇性 WHERE 列:user_id, order_no, phone, email, device_id
  3. JOIN 關聯鍵:customer_id, product_id, order_id (雙邊建索引)
  4. 範圍查詢 + 排序列:create_time, update_time, score (複合索引尾部)
  5. GROUP BY / DISTINCT 高頻列:category_id, status, region
  6. 覆蓋查詢高頻投影列:INCLUDE (name, email, address)

堅決不建索引的 4 類鐵律列

  1. 極低選擇性列:性別 (M/F)、狀態碼 (0/1/2)、布爾旗標、刪除標記 → 索引掃描返回 > 30% 行,優化器直接忽略
  2. 極少查詢/從不查詢列:純寫入日誌欄位、審計欄位、備用欄位 → 純增維護成本
  3. 大型 LOB 列:TEXT, NTEXT, IMAGE, VARCHAR(MAX), VARBINARY(MAX), XML, JSON → 不進 B+ 樹,無法建立標準索引 (需 Full-Text / XML / JSON 專用索引)
  4. 高頻更新、低頻查詢列:last_login_time, view_count, balance (余額查詢走 PK 覆蓋即可) → 寫入放大嚴重

特殊場景索引策略

1. 分頁查詢優化

-- ❌ OFFSET 1000000 ROWS FETCH NEXT 20 → 掃描 100 萬行丟棄
-- ✅ 遊標分頁 (Keyset Pagination) - 需唯一遞增鍵
WHERE id > @last_seen_id ORDER BY id LIMIT 20; -- 索引 Seek 直接定位

-- ✅ 覆蓋索引 + 延遲關聯
SELECT t.* FROM (SELECT id FROM Orders WHERE status=1 ORDER BY id OFFSET 1000000 ROWS FETCH NEXT 20) t
JOIN Orders o ON o.id = t.id; -- 內層只掃索引葉層,外層 20 次 Key Lookup

2. 多租戶 / 軟刪除架構

-- 所有查詢帶 tenant_id + is_deleted=0
CREATE INDEX IX_Orders_Tenant_Status_Time ON Orders (tenant_id, is_deleted, status, create_time)
  INCLUDE (customer_id, amount);

-- Filtered Index (SQL Server/PG/Oracle) 針對未刪除資料
CREATE INDEX IX_Orders_Active ON Orders (tenant_id, status, create_time)
  WHERE is_deleted = 0; -- 索引體積縮減 90%+,維護成本同比降低

3. 時序/日誌類大表

  • 叢集索引用 (log_date, id) 而非單純 id → 範圍查詢連續、分區對齊
  • 分區表 + 滾動截斷 (SWITCH OUT + TRUNCATE) 比 DELETE + 索引維護快 1000 倍
  • 考慮 Columnstore Index (SQL Server/PG/ClickHouse) 實現 10x 壓縮 + 批次掃描

總結:索引設計是「減法」藝術

最好的索引策略,是建立最少的索引,解決最多的效能問題。

  1. 從慢查詢清單出發,而非「以防萬一」預建
  2. 複合索引一個頂三個:合併單列索引為複合索引,調整列序滿足多查詢
  3. 定期清理無用索引:user_seeks+scans+lookups = 0 AND user_updates > 0 持續 30 天 → DROP
  4. 監控索引健康度:碎片率、統計資料新鮮度、鎖爭用、TempDB 壓力

把索引當作「稀缺資源」來預算分配,每個索引都要有明確的業務正當性、效能收益量化、維護成本核算。這才是資深 DBA 與初學者的分水嶺。

copyright © 萬盛學電腦網 all rights reserved