数据库查询优化中的索引选择率（Index Selectivity）与复合索引设计

题目描述
索引选择率是衡量索引效率的关键指标，指不重复的索引值数量与总记录数的比例。选择率越高，索引的过滤效果越好。复合索引设计则需要考虑列的顺序、查询模式等因素，确保索引能被高效利用。理解选择率能指导我们设计更有效的复合索引，避免索引冗余或失效。

解题过程

1. 索引选择率的基本概念

   • 定义：选择率 = 不重复的索引值数量 / 总记录数。例如，某字段有10000条记录，其中9500个不重复值，选择率为0.95。
   • 意义：选择率越接近1，索引的区分度越高（如唯一索引的选择率为1）。优化器倾向于选择高选择率的索引，因为能快速过滤大量数据。
   • 低选择率示例：性别字段（仅"男/女"两个值）的选择率极低（约0.0002），对其建单列索引效果差，因为仍需扫描大量数据。

2. 选择率如何影响索引代价估算

   • 优化器通过选择率估算查询结果集大小（基数），进而计算不同执行计划的代价。
   • 公式：查询结果行数 = 总记录数 × 选择率。例如，WHERE age=25 的条件，若age字段的选择率为0.1，总记录100万，则估算结果集为10万行。
   • 索引选择逻辑：若选择率低（如<5%），优化器可能选择索引扫描；若选择率高，可能直接全表扫描（避免回表开销）。

3. 复合索引的设计原则

   • 最左前缀匹配原则：复合索引（如(A,B,C)）仅支持按最左顺序的查询（如A、A+B、A+B+C）。单独查询B或C无法利用索引。
   • 高选择率列优先：将选择率高的列放在复合索引左侧。例如，索引（城市, 性别）比（性别, 城市）更优，因为"城市"的选择率通常高于"性别"。
   • 覆盖索引优化：若索引包含查询所需全部列（如SELECT city FROM users WHERE city=‘北京’），可避免回表，进一步提升性能。

4. 实际设计案例解析

   • 场景：用户表查询，常见条件为WHERE city=‘北京’ AND gender=‘男’ AND age>20。
   • 步骤1：分析各列选择率
     • city：选择率0.2（假设有5个城市）
     • gender：选择率0.5
     • age：选择率0.8（年龄分布较散）
   • 步骤2：设计复合索引顺序
     • 错误设计：(gender, city, age)。gender选择率低，首列过滤效果差。
     • 正确设计：(city, age, gender)。city优先快速缩小范围，age进一步过滤，gender最后精炼。
   • 步骤3：验证索引利用率
     • 查询WHERE city=‘北京’ AND age>20 → 可用索引最左前缀（city, age）。
     • 查询WHERE age>20 → 无法利用索引（违反最左前缀）。

5. 选择率与统计信息的关系

   • 数据库通过定期收集统计信息（如直方图）更新选择率。若数据分布变化后未更新统计信息，优化器可能错误选择索引。
   • 示例：某字段初始值均匀分布，选择率0.8；后新增大量重复值，选择率降至0.1，但统计信息未更新，优化器仍按旧选择率决策，导致性能下降。

6. 复合索引的冗余与合并优化

   • 避免创建功能重叠的索引，如已有(A,B)，再创建(A)则冗余。
   • 合并多个单列索引：若查询常同时使用A和B，复合索引(A,B)比两个单列索引更高效（减少索引合并开销）。

通过以上步骤，可系统化地基于选择率设计复合索引，平衡查询需求与存储成本，最终提升数据库性能。