索引选择性与基数

索引选择性决定索引的过滤效果，是索引设计的重要依据。

选择性概念

定义

选择性 = 基数 / 总记录数 = DISTINCT(col) / COUNT(*)

选择性越高 → 索引过滤效果越好
选择性越低 → 索引效果越差

选择性范围

选择性 = 1：唯一索引，完美过滤
选择性 ≈ 0：所有记录相同值，索引无效
选择性 > 0.3：适合建索引
选择性 < 0.1：不建议建索引

基数概念

定义

基数（Cardinality）指列中不同值的数量。

-- 查看列基数
SELECT COUNT(DISTINCT status) AS cardinality FROM orders;

-- 或通过SHOW INDEX查看
SHOW INDEX FROM orders;

InnoDB基数估算

-- InnoDB使用采样估算基数，非精确值
SHOW INDEX FROM orders;

-- Column: Cardinality（估算值）
-- 实际基数可能不同

选择性计算

计算公式

-- 计算列选择性
SELECT
    COUNT(DISTINCT user_id) AS distinct_count,
    COUNT(*) AS total_count,
    COUNT(DISTINCT user_id) / COUNT(*) AS selectivity
FROM orders;

-- 示例结果
-- distinct_count: 10000
-- total_count: 100000
-- selectivity: 0.1 (较低，需联合其他列)

联合索引选择性

-- 计算联合索引选择性
SELECT
    COUNT(DISTINCT CONCAT(user_id, status)) AS distinct_count,
    COUNT(*) AS total_count,
    COUNT(DISTINCT CONCAT(user_id, status)) / COUNT(*) AS selectivity
FROM orders;

-- 或更精确计算
SELECT
    COUNT(*) AS total,
    COUNT(DISTINCT user_id) / COUNT(*) AS sel1,
    COUNT(DISTINCT status) / COUNT(*) AS sel2,
    COUNT(DISTINCT user_id, status) / COUNT(*) AS sel_combined
FROM orders;

选择性与索引效率

高选择性示例

-- user_id选择性高（0.9）
CREATE INDEX idx_user ON orders(user_id);

SELECT * FROM orders WHERE user_id = 1;
-- 100000条记录 → 过滤后约100条
-- 索引效果：好

低选择性示例

-- status选择性低（0.05，只有5种状态）
CREATE INDEX idx_status ON orders(status);  -- 不推荐

SELECT * FROM orders WHERE status = 1;
-- 100000条记录 → 过滤后约20000条
-- 索引效果：差，可能不如全表扫描

联合索引列顺序

按选择性排序

-- 查看各列选择性
SELECT
    COUNT(DISTINCT user_id) / COUNT(*) AS sel_user,
    COUNT(DISTINCT status) / COUNT(*) AS sel_status,
    COUNT(DISTINCT created_at) / COUNT(*) AS sel_time
FROM orders;

-- 结果：
-- sel_user: 0.9（高）
-- sel_status: 0.05（低）
-- sel_time: 0.8（较高）

-- ✅ 高选择性列放前面
CREATE INDEX idx_user_time_status ON orders(user_id, created_at, status);

考虑查询场景

-- 如果查询固定WHERE user_id = ?
-- 则user_id必须放最左
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 即使status选择性更低
-- 但user_id是固定条件，必须作为最左列

前缀索引选择性

计算最优前缀长度

-- 测试不同前缀长度选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;

-- 结果示例：
-- sel_5: 0.3
-- sel_10: 0.85
-- sel_15: 0.98
-- sel_full: 1.0

-- 选择sel_15，接近完整列选择性
CREATE INDEX idx_email ON users(email(15));

基数更新

统计信息更新

-- 手动更新统计信息
ANALYZE TABLE orders;

-- InnoDB自动更新（采样）
-- 当数据变化超过一定比例时自动触发

查看采样参数

-- 采样页数
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';

-- 自动更新阈值
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

基数不准确可能导致优化器选择错误索引，定期ANALYZE TABLE。

选择性与索引决策

选择性判断流程：

1. 计算选择性 = DISTINCT(col) / COUNT(*)
      ↓
2. 选择性 > 0.3？
      ├── 是 → 适合建单列索引
      └── 否 → 考虑联合索引
              ↓
      3. 联合后选择性 > 0.3？
              ├── 是 → 建联合索引
              └── 否 → 不建议建索引

要点总结

• 选择性 = 基数 / 总记录数，衡量索引过滤效果
• 选择性 > 0.3适合建索引，< 0.1不建议
• 联合索引列顺序：高选择性列优先或固定条件列优先
• 前缀索引选择接近完整列选择性的最短长度
• 定期ANALYZE TABLE更新统计信息
• 基数不准确可能导致索引选择错误