数据库索引选择策略

索引选择策略笔记

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。

总结

• 单列索引：适合简单查询、高选择性列。
• 组合索引：适合多列组合查询、排序需求及复杂查询。

根据查询模式和性能需求选择合适的索引类型，定期分析和优化索引策略，确保数据库性能最佳。