資料庫索引選擇策略

槑孒發表於2024-08-26
資料庫索引

索引選擇策略筆記

1. 單列索引

  • 適用場景：

    • 單列查詢：查詢條件僅涉及單個列。
    • 高選擇性列：列中的值較為唯一（選擇性高），能有效過濾資料。
    • 簡化管理：查詢簡單，使用單列索引即可滿足需求。

  • 示例：

    CREATE INDEX users_email_idx ON users (email);
    • 適用於查詢：SELECT * FROM users WHERE email = 'example@example.com';

2. 組合索引

  • 適用場景：

    • 多列組合查詢：查詢中經常同時涉及多個列作為條件。
    • 排序與範圍查詢：查詢中涉及排序或範圍查詢（如 ORDER BY 或 BETWEEN）。
    • 字首匹配：組合索引的列順序很重要，查詢可以利用索引的“字首”。

  • 示例：

    CREATE INDEX orders_customer_date_idx ON orders (customer_id, order_date);
    • 適用於查詢：SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-08-26';

3. 選擇組合索引的時機

  • 多列組合查詢頻繁：當查詢中頻繁使用多列組合作為過濾條件時，使用組合索引。
  • 資料選擇性差：單列選擇性較差時，透過組合索引進一步過濾資料。
  • 排序和範圍查詢：當查詢中有排序或範圍查詢需求時，組合索引可以提升效能。

4. 選擇單列索引的時機

  • 查詢只涉及一列：查詢通常只涉及單個列，單列索引即可滿足需求。
  • 簡單性與維護：單列索引更易於管理和維護。

5. 組合索引的列順序

  • 訪問頻率高的列放在最前：優先將最常用的列放在組合索引的前面，以提高索引的使用率。
  • 選擇性高的列優先：選擇性高的列放在前面，有助於最大化查詢的過濾效果。

6. 多列索引與多個單列索引的權衡

  • 覆蓋索引：組合索引可以成為覆蓋索引，避免查詢時訪問表資料。
  • 索引開銷：多個單列索引增加資料庫維護成本，但支援更廣泛的查詢。組合索引更專注於特定查詢，維護成本較低。

7. 實際案例中的選擇

  • 案例1：電商訂單查詢：如果頻繁查詢訂單的客戶ID和訂單日期，使用組合索引 (customer_id, order_date)。
  • 案例2：使用者登入查詢：如果頻繁查詢使用者郵箱，使用單列索引 email。

總結

  • 單列索引：適合簡單查詢、高選擇性列。
  • 組合索引：適合多列組合查詢、排序需求及複雜查詢。

根據查詢模式和效能需求選擇合適的索引型別，定期分析和最佳化索引策略，確保資料庫效能最佳。

相關文章