定义

基数 (Cardinality) 是指数据库表中的某一列所包含的不同值(唯一值)的数量。基数是评估索引价值的重要指标之一,通常与表的总行数结合使用来计算索引选择性。

• 高基数:列中大部分值都不同(如用户ID、邮箱)
• 低基数:列中只有少数不同的值(如性别、状态)

详细笔记

核心原理

基数与选择性的关系

选择性 = 基数 / 总行数

示例:

-- 用户表:100 万行

-- user_id 列
基数: 1000000 (每行都不同)
选择性: 1000000 / 1000000 = 1.0 (最高)

-- gender 列
基数: 2 (男、女)
选择性: 2 / 1000000 = 0.000002 (极低)

-- age 列
基数: 100 (假设年龄范围 0-99)
选择性: 100 / 1000000 = 0.0001 (低)

查看基数

MySQL

-- 方法一:SHOW INDEX
SHOW INDEX FROM users;

-- 输出包含 Cardinality 列:
-- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
-- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-- | users |    0 | PRIMARY  |    1 | id    | A   |   1000000 |   NULL | NULL |  | BTREE  |   |     |
-- | users |    1 | idx_name |    1 | name    | A   |  500000 |   NULL | NULL | YES  | BTREE  |   |     |
-- | users |    1 | idx_gender|     1 | gender  | A   |     2 |   NULL | NULL | YES  | BTREE  |   |     |
-- +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

-- 方法二:直接查询
SELECT 
  COUNT(DISTINCT column_name) AS cardinality
FROM table_name;

-- 方法三:查看所有列的基数
SELECT 
  COLUMN_NAME,
  CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'users';

PostgreSQL

-- 查看统计信息
SELECT 
  attname AS column_name,
  n_distinct,
  correlation
FROM pg_stats
WHERE tablename = 'users';

-- n_distinct:
-- > 0:估计的不同值数量
-- < 0:估计的不同值比例(绝对值 * 总行数)
-- -1:未知

SQL Server

-- 查看索引统计
DBCC SHOW_STATISTICS ('users', 'idx_name');

-- 或使用系统视图
SELECT 
  c.name AS column_name,
  s.distinct_key_count AS cardinality
FROM sys.dm_db_stats_properties(s.object_id, s.stats_id) s
JOIN sys.columns c ON s.object_id = c.object_id
WHERE s.object_id = OBJECT_ID('users');

基数的分类

一、高基数(High Cardinality)

特征:

• 基数接近总行数
• 选择性 > 0.8
• 非常适合建索引

典型例子:

• 主键、UUID
• 邮箱、手机号
• 订单号、身份证号

-- 高基数列
SELECT COUNT(DISTINCT email) / COUNT(*) AS selectivity
FROM users;
-- 结果:0.99 (几乎每行都不同)

-- 适合建索引
CREATE INDEX idx_email ON users(email);

二、中基数(Medium Cardinality)

特征:

• 基数在总行数的 1% - 80% 之间
• 选择性 0.01 - 0.8
• 可以根据查询频率决定是否建索引

典型例子:

• 城市、省份
• 商品类别
• 部门名称

-- 中基数列
SELECT COUNT(DISTINCT city) / COUNT(*) AS selectivity
FROM users;
-- 结果:0.05 (500 个城市 / 10000 用户)

-- 如果频繁查询,可以建索引
CREATE INDEX idx_city ON users(city);

三、低基数(Low Cardinality)

特征:

• 基数远小于总行数
• 选择性 < 0.01
• 通常不适合单独建索引

典型例子:

• 性别(2 个值)
• 布尔值(2 个值)
• 状态码(3-10 个值)
• 年份(少量年份)

-- 低基数列
SELECT COUNT(DISTINCT gender) / COUNT(*) AS selectivity
FROM users;
-- 结果:0.000002 (2 / 1000000)

-- 不建议单独建索引
-- 但可以作为复合索引的一部分
CREATE INDEX idx_gender_age ON users(gender, age);

基数对索引选择的影响

优化器的决策

MySQL 优化器会参考基数来决定是否使用索引:

-- 场景:100 万行数据

-- 查询一:高基数列
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ref
-- key: idx_email
-- rows: 1
-- 优化器选择使用索引 ✅

-- 查询二:低基数列
EXPLAIN SELECT * FROM users WHERE gender = '男';
-- type: ALL
-- key: NULL
-- rows: 1000000
-- Extra: Using where
-- 优化器选择全表扫描,忽略索引 ❌

-- 原因:估算使用索引需要回表 50 万次,不如全表扫描

复合索引的基数

复合索引基数计算

CREATE INDEX idx_name_age ON users(name, age);

-- 复合索引的基数
SELECT COUNT(DISTINCT name, age) AS composite_cardinality
FROM users;

-- 规则:
-- 复合索引基数 >= 任意单列基数
-- 复合索引基数 <= 总行数

示例:

users 表:100 万行

单列基数:
- name: 500000
- age: 100

复合索引 (name, age) 基数:
- 可能值:600000 (因为同名字的人年龄可能不同)
- 范围:500000 - 1000000

更新基数统计

为什么需要更新?

基数是基于统计信息的估算值,当数据发生大量变化时,统计信息可能过时,导致优化器做出错误决策。

MySQL

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

-- 自动更新(默认开启)
-- innodb_stats_on_metadata = ON
-- 当访问表元数据时自动更新

-- 查看统计信息更新情况
SHOW TABLE STATUS LIKE 'users';
-- 查看 Rows 字段(估算的行数)

PostgreSQL

-- 手动更新统计信息
ANALYZE users;

-- 自动更新(Auto-analyze)
-- 当表中变化的行数超过阈值时触发
-- 阈值 = default_statistics_target * 表行数 / 100

SQL Server

-- 手动更新统计信息
UPDATE STATISTICS users;

-- 启用自动更新
ALTER DATABASE your_db SET AUTO_UPDATE_STATISTICS ON;

基数与实际性能

案例一:基数误判导致性能问题

-- 场景:订单表,1000 万行

-- 初始状态
SELECT COUNT(DISTINCT status) FROM orders;
-- 结果:3 (待支付、已支付、已取消)

-- 随着业务发展,大部分订单变为"已支付"
-- 数据分布变得不均匀

-- 查询
EXPLAIN SELECT * FROM orders WHERE status = 0;  -- 待支付(仅 1%)

-- 问题:统计信息过时,优化器以为 status=0 占 33%
-- 选择全表扫描,实际应该用索引

-- 解决:更新统计信息
ANALYZE TABLE orders;

-- 重新 EXPLAIN
-- 现在优化器知道 status=0 只占 1%,选择使用索引

案例二:复合索引列顺序选择

-- 场景:日志表,1000 万行

-- 单列基数
SELECT 
  COUNT(DISTINCT app_id) AS app_cardinality,  -- 1000
  COUNT(DISTINCT log_level) AS level_cardinality,  -- 5
  COUNT(DISTINCT DATE(create_time)) AS date_cardinality  -- 365
FROM logs;

-- 设计复合索引
-- 方案一:(app_id, log_level, create_time)
-- 基数:1000 → 5 → 365
-- 优点:第一列基数较高

-- 方案二:(log_level, app_id, create_time)
-- 基数:5 → 1000 → 365
-- 缺点:第一列基数太低

-- 推荐:方案一
CREATE INDEX idx_app_level_time ON logs(app_id, log_level, create_time);

基数与直方图

MySQL 8.0 直方图统计

对于数据分布不均匀的列,可以使用直方图提供更准确的统计信息:

-- 创建直方图
ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 100 BUCKETS;

-- 删除直方图
ANALYZE TABLE users DROP HISTOGRAM ON age;

-- 查看直方图信息
SELECT 
  COLUMN_NAME,
  HISTOGRAM
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'age';

适用场景:

• 数据分布严重倾斜(如 90% 的记录集中在某个范围)
• 范围查询频繁
• 普通统计信息无法准确描述数据分布

监控基数变化

定期检查脚本

-- MySQL:检查所有表的基数变化
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_NAME,
  CARDINALITY,
  TABLE_ROWS,
  CARDINALITY / TABLE_ROWS AS selectivity
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY selectivity DESC;

最佳实践

1. 优先为高基数列创建索引:主键、唯一标识符等

2. 低基数列谨慎建索引:除非作为复合索引的一部分

3. 定期更新统计信息:特别是数据大量变更后

4. 监控基数变化:发现异常及时调整索引策略

5. 结合实际查询模式:基数只是参考,最终要看实际查询性能

6. 使用直方图处理数据倾斜:MySQL 8.0+ 支持

关联术语

• [[索引选择性]]
• [[统计信息]]
• [[执行计划]]
• [[最左前缀原则]]

参考资料

• MySQL 官方文档: InnoDB and MyISAM Statistics
• PostgreSQL 文档: Statistics Collector
• 《高性能 MySQL》第 5 章:统计信息与优化器
• Microsoft Docs: Statistics