什么是索引选择性（cardinality）？基数过低的索引为什么没用？

考察要点

这道题面试官想看你是否深刻理解数据库查询优化器的核心工作原理，特别是基于成本的优化（Cost-Based Optimization, CBO）。具体来说，考察以下三点：

1. 基本概念：你是否能清晰定义索引选择性（Selectivity）和基数（Cardinality），并解释它们之间的关系。
2. 成本分析：你是否理解 MySQL（尤其是 InnoDB）使用索引和全表扫描的 I/O 成本模型，特别是“回表”的代价。
3. 优化思维：你是否知道低基数索引在什么情况下可能有用（如覆盖索引），以及如何通过建立正确的复合索引来解决这类问题。

核心概念

1. 什么是索引基数（Cardinality）？

索引基数是指数据库表中一个列（或多个列组合）的不同值的数量。它反映了该列数据的多样性程度。

• 公式：Cardinality = COUNT(DISTINCT column_name)
• 查看方式：可以通过 SHOW INDEX FROM table_name; 命令查看 MySQL 估算的基数值。

2. 什么是索引选择性（Selectivity）？

索引选择性是衡量索引筛选能力的一个指标，它等于索引基数与表总行数的比值。选择性的范围是 (0, 1]。

• 公式：Selectivity = Cardinality / Total Rows
• 选择性越高，意味着该列的值越趋于唯一，索引的筛选效果越好。主键的选择性为 1，是最高的。
• 选择性越低，意味着该列存在大量重复值，索引的筛选效果越差。例如 gender 列。

3. 为什么基数过低的索引没用？—— 成本分析

查询优化器在决定是否使用某个索引时，会估算两种主要方案的成本：

1. 使用索引（Index Scan + 回表）
2. 全表扫描（Full Table Scan）

核心矛盾在于：随机 I/O vs. 顺序 I/O

• 全表扫描的成本：主要是顺序 I/O。InnoDB 从表空间的第一个区（Extent）开始，按物理顺序读取数据页，直到表尾。顺序 I/O 的效率远高于随机 I/O。

  • Cost(Table Scan) ≈ N_pages * Cost(Sequential I/O)

• 使用索引的成本：

  1. 索引搜索：通过 B+ 树快速定位到索引项，这部分成本（log(N)级别）通常可以忽略不计。
  2. 回表（Bookmark Lookup）：对于非覆盖索引，在索引树中找到主键值后，需要根据主键值再次去聚簇索引（数据本身）中查找完整的行数据。这个过程是随机 I/O，成本非常高昂。
  • Cost(Index Scan) ≈ Cost(Index Seek) + N_matches * Cost(Row Lookup)

当基数过低时，例如 WHERE gender = 'M'，假设男女比例 1:1：

• 优化器估算需要访问表中约 50% 的行。
• 这意味着需要进行 总行数 * 50% 次回表操作。
• 大量的随机 I/O 访问，其总成本会远远超过一次全表扫描的顺序 I/O 成本。

结论：当优化器根据选择性估算出使用索引需要读取的行数比例超过某个阈值（通常是 20%-30%，但非绝对值），它会认为全表扫描更“便宜”，从而放弃使用该索引。这就是低基数索引失效的根本原因。

特例：覆盖索引 如果一个查询所需的所有列都恰好包含在索引中，那么数据库引擎只需扫描索引树，无需回表。

 1-- 假设有索引 INDEX idx_status_createtime (status, create_time)
 2SELECT status, create_time FROM orders WHERE status = 'pending';

即使 status 是低基数列，这个查询也可能非常高效，因为它触发了覆盖索引（Extra 字段显示 Using index），避免了昂贵的回表操作。

常见低分回答

1. 回答过于笼统：“因为要查的数据太多了，所以索引没用。” —— 没有点出核心成本（回表、随机 I/O）。
2. 混淆概念：“因为这个列的 Cardinality 太低了，所以 Selectivity 不好。” —— 虽然意思沾边，但术语使用不精确，未能清晰解释两者关系。
3. 只知其一不知其二：“性别这种列不能建索引。” —— 结论过于绝对。没有提到在复合索引或覆盖索引场景下，低基数列依然可以发挥作用。
4. 给出错误的阈值：“只要超过 30% 的数据，索引就一定失效。” —— 这是经验之谈，不是数据库的硬性规则。高分回答应强调这是优化器的成本估算结果，受多种因素影响。
5. 忽略覆盖索引：完全没有提及覆盖索引这个重要的特例，说明知识体系存在盲区。

工程实践与优化

在真实生产环境中，我们经常遇到对低基数列的查询需求，例如查询“待处理”状态的订单。

• 错误示范：为 orders 表的 status 列单独创建索引。

  sql

  ⧉⛶

   1CREATE INDEX idx_status ON orders(status); -- 效果极差

  对于 SELECT * FROM orders WHERE status = 'pending';，几乎必定会走全表扫描。

• 正确优化方案：创建复合索引 将低基数列与一个高选择性的列（通常是时间或ID类）组合起来，创建复合索引。遵循最左前缀原则，将低基数列放在前面。

  sql

  ⧉⛶

   1-- 正确的复合索引
   2CREATE INDEX idx_status_createtime ON orders(status, create_time);

  对于查询：

  sql

  ⧉⛶

   1SELECT * FROM orders WHERE status = 'pending' ORDER BY create_time DESC LIMIT 10;

  • 工作原理：B+ 树索引会先按 status 排序，再按 create_time 排序。
  • 优化器可以快速定位到 status = 'pending' 的索引区域，这部分数据在物理上是连续存储的。
  • 然后在这个小范围内，根据 create_time 进行扫描，大大减少了回表的数据量和扫描范围。
  • EXPLAIN 的结果中 type 可能是 ref，key 会是 idx_status_createtime。

• 如何查看和更新统计信息

  • 查看：SHOW INDEX FROM your_table; 可以看到 MySQL 估算的 Cardinality。
  • 问题：这个估算值是通过对数据页进行随机采样得到的，可能不准，尤其是在数据发生大量增删改之后。
  • 解决：ANALYZE TABLE your_table; 可以强制 MySQL 重新采样，更新索引的统计信息，帮助优化器做出更准确的判断。在 MySQL 8.0+ 中，直方图（Histograms）的引入可以为数据分布不均的列提供更精确的统计信息。

常见追问与延伸题目

1. 追问：低基数索引在什么情况下会非常有用？

   • 回答要点 1 (覆盖索引)：当查询的所有字段都包含在索引中时，即使是低基数索引，也能触发覆盖索引（Using index），避免回表。例如 SELECT status, COUNT(*) FROM orders GROUP BY status;，如果 status上有索引，就可以直接扫描索引完成，非常快。
   • 回答要点 2 (复合索引)：当低基数列作为复合索引的最左前缀，并且查询条件中包含后续的高选择性列时。例如 INDEX(status, user_id) 用于查询 WHERE status='pending' AND user_id=123，索引效率非常高。

2. 追问：我有一个 is_deleted 字段（0 或 1），基数是 2。我为它建了索引，但 WHERE is_deleted = 0 的查询走了全表扫描，而 WHERE is_deleted = 1 却走了索引，这是为什么？

   • 回答要点 1 (数据倾斜)：这很可能是因为数据分布极不均匀。表中 is_deleted = 0 的数据占了 99.9%，而 is_deleted = 1 的数据只占 0.1%。
   • 回答要点 2 (优化器决策)：对于 WHERE is_deleted = 0，优化器估算需要回表 99.9% 的数据，成本远高于全表扫描，所以放弃索引。对于 WHERE is_deleted = 1，优化器估算只需要回表 0.1% 的数据，成本很低，所以选择使用索引。
   • 回答要点 3 (解决方案)：这种情况说明业务设计上可能需要调整。可以使用历史数据归档，或者针对 is_deleted = 0 的查询创建更合适的复合索引，如 INDEX(is_deleted, update_time)。

3. 追问：复合索引 (A, B)，A 是低基数列，B 是高基数列。那么 INDEX(A, B) 和 INDEX(B, A) 哪个更好？

   • 回答要点 1 (没有绝对的好坏)：哪个更好完全取决于你的查询场景（Query Pattern）。
   • 回答要点 2 (INDEX(A, B))：适用于 WHERE A = ? 和 WHERE A = ? AND B = ? 的查询。如果你的主要场景是筛选出某个状态（A列）下的数据，并按 B 列排序或范围查找，这个索引是最佳选择。例如 WHERE status='pending' ORDER BY create_time。
   • 回答要点 3 (INDEX(B, A))：适用于 WHERE B = ? 和 WHERE B = ? AND A = ? 的查询。根据最左前缀原则，它无法优化 WHERE A = ? 的查询。
   • 结论：设计索引时，将等值查询、筛选范围更小的列放在前面通常是好的实践。但如果业务场景固定需要先按低基数列筛选，再在结果中按高基数列查找，那么 INDEX(低基数, 高基数) 的顺序就是正确的。必须结合具体 SQL 来谈论索引设计。