MySQL中索引的区分度

原创于 2025-07-29 07:23:17 发布 · 1.3k 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 中索引的区分度（Selectivity）是衡量索引列中数据唯一性程度的关键指标，它直接决定了索引的过滤效率和查询性能。理解其概念、计算方式和应用场景对数据库优化至关重要。

一、核心概念

区分度 = 不重复的索引值数量 / 数据表总行数

公式：
$\text{Distinctiveness} = \frac{\text{COUNT(DISTINCT column)}}{\text{COUNT(*)}}$
范围：值在 (0, 1] 之间，越接近 1 表示区分度越高（唯一性越强）。

✅ 高区分度示例：

手机号（phone）：
100 万行数据，100 万个唯一号码 → 区分度 = 100%
身份证号（id_card）：接近唯一值 → 区分度 ≈ 1

❌ 低区分度示例：

性别（gender）：
100 万行数据，只有 “男/女” 2 个值 → 区分度 = 0.0002%
状态标志（status）：10 种状态值 → 区分度 = 0.001%

二、为什么区分度重要？

1. 减少扫描数据量（IO 成本）

高区分度索引：通过索引能快速定位少量数据页（如手机号查 1 行）。
低区分度索引：索引可能需扫描大量行（如性别查 50 万行），甚至不如全表扫描。

2. 优化索引有效性

优化器决策：MySQL 优先选择区分度高的索引（见 EXPLAIN 中的 key）。
索引失效风险：低区分度列建索引可能被优化器忽略（尤其是复合索引中靠前的低区分度列）。

3. 避免冗余索引

低区分度索引占用空间，但查询加速效果微弱，应避免创建。

三、如何计算区分度？

方法 1：直接计算

SELECT 
  COUNT(DISTINCT column_name) / COUNT(*) AS selectivity 
FROM table_name;

方法 2：查看统计信息（更高效）

SHOW INDEX FROM table_name;

输出中的 Cardinality 列即为索引唯一值数量的估算值（基于采样统计），区分度 ≈ Cardinality / 表总行数。

⚠️ 注意：Cardinality 是近似值，非实时精确统计。可用 ANALYZE TABLE table_name; 更新统计信息。

四、区分度的应用场景

1. 索引创建决策

优先选择高区分度列：如订单号、用户名、手机号。
谨慎使用低区分度列：如性别、类型、布尔标志（除非复合索引中配合高区分度列使用）。

2. 复合索引列顺序

原则：高区分度列放前面
例如索引 (category_id, product_id)：
- ❌ 错误：category_id（低区分度）在前 → 可能需扫描大量相同分类。
- ✅ 正确：product_id（高区分度）在前 → 直接定位单个商品。

3. 前缀索引优化

对长文本列（如 VARCHAR(200)），可仅索引前 N 个字符：

ALTER TABLE table_name ADD INDEX (column_name(20));

目标：选择最小的 N，使区分度接近完整列（如 N=20 时区分度达 95%）。

计算方法：

SELECT 
  COUNT(DISTINCT LEFT(column_name, 20)) / COUNT(*) AS prefix_selectivity
FROM table_name;

五、区分度经验参考值

区分度范围	索引效果	建议
> 30%	优秀（如主键、唯一约束）	强烈推荐建索引
10% ~ 30%	良好	适合建索引
< 10%	较差（如状态、类型字段）	需结合查询场景谨慎评估
< 5%	极低	通常不建单列索引

六、区分度 vs. 索引选择性

索引选择性（Index Selectivity）：与区分度是同一概念，数值越高索引效率越佳。
选择基数（Selectivity Cardinality）：即 Cardinality 值，是计算选择性的分子。

七、低区分度索引的特殊场景

即使区分度低，索引在以下情况仍有价值：

覆盖索引（Covering Index）：
索引包含所有查询字段，无需回表。

SELECT status FROM orders WHERE status = 'shipped'; -- 低区分度但覆盖索引

复合索引配合高区分度列：

ALTER TABLE users ADD INDEX (country, city, phone); -- 低区分度列在前，但配合高区分度列使用

数据分布倾斜时的等值查询：
如 90% 数据为状态 A，查询状态 B 时索引有效。

总结

索引区分度本质是“通过索引能过滤多少数据”：

✅ 高区分度 → 索引高效 → 优化器青睐 → 加速查询
⚠️ 低区分度 → 索引低效 → 可能被优化器跳过 → 谨慎创建

优化建议：

用 SHOW INDEX 或直接计算评估区分度；
优先为高区分度列建索引；
复合索引按区分度降序排列；
低区分度列需结合覆盖索引或复合索引策略使用。

掌握区分度概念，能从根本上提升索引设计效率，避免“无效索引”带来的性能陷阱。