数据库查询优化中的基于代价的索引选择与多索引访问优化

题目描述
在数据库查询优化中，当查询条件涉及多个列时，优化器需要决定是否使用索引、使用哪个索引或如何组合多个索引。基于代价的索引选择通过计算不同索引访问路径的代价（如I/O、CPU开销），选择最优方案；多索引访问优化则涉及如何利用多个索引共同加速查询（如索引合并）。本题将详细讲解这一过程的原理与实现步骤。

解题过程

1. 问题场景分析
假设有查询：

SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01';  

表中存在两个索引：

• idx_customer_id (customer_id)
• idx_order_date (order_date)
  优化器需要决定：
• 使用idx_customer_id索引过滤customer_id，再检查order_date？
• 使用idx_order_date索引过滤order_date，再检查customer_id？
• 同时使用两个索引，合并结果？

2. 基于代价的索引选择原理

• 代价估算要素：
  • 选择性（Selectivity）：满足条件的行数占比。选择性越高，索引效果越好。
    • 例如：customer_id=100的选择性 = 1/不同customer_id数量。
  • 索引扫描代价：遍历索引B+树的深度、需要加载的页数。
  • 回表代价：根据索引定位到主键后，访问数据页的随机I/O开销。
• 计算公式简化示例：
  • 代价 = 索引扫描代价 + 回表代价 × 预计匹配行数。
  • 优化器通过统计信息（如直方图、不同值数量）估算匹配行数。

3. 单索引选择流程

• 步骤1：计算各索引的过滤效果
  • 使用统计信息估算每个条件的选择性：
    • customer_id=100的选择性 = 1/10000 = 0.0001（假设有10000个不同客户）。
    • order_date > '2023-01-01'的选择性 = 过去一年的订单数/总订单数 ≈ 0.3。
  • 选择性越低，索引过滤效果越好。
• 步骤2：比较不同索引的代价
  • 若使用idx_customer_id：
    • 索引扫描找到所有customer_id=100的条目（代价低，因B+树高度固定）。
    • 回表检查order_date条件，回表次数 = 匹配customer_id的行数（假设10行）。
  • 若使用idx_order_date：
    • 索引扫描找到所有order_date > '2023-01-01'的条目（匹配行数多，假设3000行）。
    • 回表检查customer_id条件，回表次数高达3000次，代价显著更高。
  • 结论：优先选择idx_customer_id。

4. 多索引访问优化：索引合并（Index Merge）

• 适用场景：当多个条件通过OR或AND组合，且各有高效索引时。
• 常见策略：
  • 交集合并（Index Merge Intersection）：
    • 适用于AND条件，分别通过两个索引获取主键集合，取交集后回表。
    • 例如：同时使用idx_customer_id和idx_order_date，得到主键交集，减少回表次数。
  • 并集合并（Index Merge Union）：
    • 适用于OR条件，分别通过索引获取主键集合并去重后回表。
• 代价权衡：
  • 合并操作本身需要额外CPU开销（计算交集/并集）。
  • 若通过单个索引已能高效过滤，则合并可能不划算。

5. 优化器决策示例

• 案例1：AND条件
  • 若customer_id=100匹配10行，order_date > '2023-01-01'匹配3000行：
    • 单索引选择：idx_customer_id回表10次，代价低。
    • 索引合并：需分别扫描两个索引，取交集（假设交集仅5行），回表5次，但多出合并开销。
    • 决策：若合并开销 < 单索引回表节省的代价，则选择合并。
• 案例2：OR条件
  • 查询：WHERE customer_id=100 OR order_date > '2023-01-01'
    • 单索引无法高效覆盖，需索引合并（并集）。

6. 实际优化建议

• 统计信息更新：确保ANALYZE TABLE定期执行，避免代价估算偏差。
• 复合索引：若多列条件常同时出现，创建复合索引（如(customer_id, order_date)）比索引合并更高效。
• 优化器提示：在特殊情况下可用USE INDEX提示强制索引选择（但需谨慎）。

总结
基于代价的索引选择通过量化I/O、CPU开销，从多个索引中选出最优路径；多索引访问优化则通过合并策略进一步利用索引潜力。实际应用中需结合统计信息、查询模式与索引设计综合权衡。