数据库核心术语知识库

定义

索引选择性 (Index Selectivity) 是指索引列中不同值的数量与总行数的比值,用于衡量索引的过滤能力。选择性越高(接近 1.0),索引的过滤效果越好,查询性能越优;选择性越低(接近 0),索引的效果越差,甚至可能不如全表扫描。

计算公式:

索引选择性 = COUNT(DISTINCT column) / COUNT(*)

详细笔记

核心原理

选择性的意义

选择性 = 1.0:每行的值都不同(如主键、UUID),索引效果最佳
选择性 ≈ 0:大部分行的值相同(如性别、状态),索引效果差

示例:

sql

-- 用户表:100 万行数据

-- 情况一:user_id (主键)
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM users;
-- 结果:1.0 (每行都不同)
-- 索引选择性:极高,非常适合建索引

-- 情况二:gender (性别)
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users;
-- 结果:2 / 1000000 = 0.000002
-- 索引选择性:极低,不适合单独建索引

-- 情况三:city (城市)
SELECT COUNT(DISTINCT city) / COUNT(*) FROM users;
-- 假设结果:500 / 1000000 = 0.0005
-- 索引选择性:中等,可以根据查询频率决定是否建索引

计算索引选择性

单列选择性

sql

-- 查看各列的选择性
SELECT 
  COUNT(*) AS total_rows,
  COUNT(DISTINCT user_id) AS distinct_user_id,
  COUNT(DISTINCT email) AS distinct_email,
  COUNT(DISTINCT name) AS distinct_name,
  COUNT(DISTINCT age) AS distinct_age,
  COUNT(DISTINCT gender) AS distinct_gender,
  
  COUNT(DISTINCT user_id) / COUNT(*) AS selectivity_user_id,
  COUNT(DISTINCT email) / COUNT(*) AS selectivity_email,
  COUNT(DISTINCT name) / COUNT(*) AS selectivity_name,
  COUNT(DISTINCT age) / COUNT(*) AS selectivity_age,
  COUNT(DISTINCT gender) / COUNT(*) AS selectivity_gender
FROM users;

典型输出:

+------------+---------------------+------------------+---------------+--------------+----------------+---------------------------+---------------------+-------------------+---------------+--------------------+
| total_rows | distinct_user_id  | distinct_email | distinct_name | distinct_age | distinct_gender| selectivity_user_id   | selectivity_email | selectivity_name  | selectivity_age| selectivity_gender |
+------------+---------------------+------------------+---------------+--------------+----------------+---------------------------+---------------------+-------------------+---------------+--------------------+
|  1000000 |     1000000 |    1000000 |    500000 |    100 |      2 |       1.000 |     1.000 |     0.500 |   0.0001|      0.000002|
+------------+---------------------+------------------+---------------+--------------+----------------+---------------------------+---------------------+-------------------+---------------+--------------------+

分析:

user_id, email:选择性 1.0,非常适合建索引
name:选择性 0.5,适合建索引
age:选择性 0.0001,不太适合单独建索引
gender:选择性 0.000002,几乎不适合建索引

复合索引选择性

sql

-- 复合索引的选择性取决于列的组合
CREATE INDEX idx_name_age ON users(name, age);

-- 复合索引的选择性
SELECT COUNT(DISTINCT name, age) / COUNT(*) AS selectivity_name_age
FROM users;

-- 如果结果 = 0.6,说明 (name, age) 组合的选择性为 0.6

重要规则:复合索引的选择性 >= 任意单列的选择性

选择性与查询性能

高选择性索引的优势

sql

-- 场景:100 万行数据

-- 查询一:使用高选择性索引(user_id)
SELECT * FROM users WHERE user_id = 12345;
-- 索引过滤后:1 行
-- 回表次数:1 次
-- 执行时间:< 1ms

-- 查询二:使用低选择性索引(gender)
SELECT * FROM users WHERE gender = '男';
-- 索引过滤后:50 万行
-- 回表次数:50 万次
-- 执行时间:可能比全表扫描还慢

优化器的选择

MySQL 优化器会根据选择性决定是否使用索引:

sql

-- 如果选择性太低,优化器可能选择全表扫描
EXPLAIN SELECT * FROM users WHERE gender = '男';

-- 可能的输出:
-- type: ALL (全表扫描)
-- key: NULL (未使用索引)
-- rows: 1000000
-- Extra: Using where

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

选择性阈值参考

选择性范围	评价	建议
0.8 - 1.0	极高	非常适合建索引
0.5 - 0.8	高	适合建索引
0.1 - 0.5	中等	根据查询频率决定
0.01 - 0.1	低	谨慎建索引,考虑复合索引
< 0.01	极低	通常不适合单独建索引

注意:这些阈值不是绝对的,需要结合实际查询模式和性能测试。

提升索引选择性的策略

策略一:使用前缀索引

对于长字符串列,可以使用前缀索引提高选择性:

sql

-- 原始列:email VARCHAR(200)
-- 选择性:1.0 (很高)

-- 但索引过大,使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(20));

-- 验证前缀长度是否合适
SELECT 
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
  COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS prefix_20,
  COUNT(DISTINCT LEFT(email, 30)) / COUNT(*) AS prefix_30,
  COUNT(DISTINCT email) / COUNT(*) AS full_email
FROM users;

-- 输出:
-- prefix_10: 0.95
-- prefix_20: 0.99
-- prefix_30: 1.0
-- full_email: 1.0

-- 结论:前缀长度 20 已经足够,选择性 0.99,索引大小更小

策略二:使用复合索引

当单列选择性较低时,可以通过复合索引提高选择性:

sql

-- 单列选择性
-- gender: 0.000002 (极低)
-- age: 0.0001 (低)

-- 复合索引
CREATE INDEX idx_gender_age ON users(gender, age);

-- 复合索引选择性
SELECT COUNT(DISTINCT gender, age) / COUNT(*) AS selectivity
FROM users;
-- 结果:0.0002 (虽然仍然不高,但比单列好)

-- 更好的组合
CREATE INDEX idx_name_age ON users(name, age);
-- 选择性:0.6 (高)

策略三:添加哈希列

对于某些场景,可以添加哈希列提高选择性:

sql

-- 场景:URL 列很长,但需要频繁等值查询
ALTER TABLE pages ADD COLUMN url_hash INT UNSIGNED;
UPDATE pages SET url_hash = CRC32(url);

CREATE INDEX idx_url_hash ON pages(url_hash);

-- 查询时使用哈希
SELECT * FROM pages 
WHERE url_hash = CRC32('https://example.com')
  AND url = 'https://example.com';  -- 二次确认,避免哈希冲突

-- url_hash 的选择性远高于完整 URL 索引

实际应用场景

场景一:电商商品搜索

sql

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category_id INT,
  brand_id INT,
  price DECIMAL(10, 2),
  status TINYINT,
  product_name VARCHAR(200),
  INDEX idx_category (category_id),
  INDEX idx_brand (brand_id),
  INDEX idx_status (status)
);

-- 检查选择性
SELECT 
  COUNT(*) AS total,
  COUNT(DISTINCT category_id) AS distinct_category,
  COUNT(DISTINCT brand_id) AS distinct_brand,
  COUNT(DISTINCT status) AS distinct_status,
  
  COUNT(DISTINCT category_id) / COUNT(*) AS sel_category,
  COUNT(DISTINCT brand_id) / COUNT(*) AS sel_brand,
  COUNT(DISTINCT status) / COUNT(*) AS sel_status
FROM products;

-- 假设输出(100 万商品):
-- sel_category: 0.001 (1000 个类别)
-- sel_brand: 0.0005 (500 个品牌)
-- sel_status: 0.00001 (3 个状态:上架/下架/审核中)

-- 分析:
-- 1. status 选择性极低,单独索引效果差
-- 2. category_id 和 brand_id 选择性中等
-- 3. 建议创建复合索引

-- 优化方案
DROP INDEX idx_status ON products;  -- 删除低选择性索引
CREATE INDEX idx_cat_brand ON products(category_id, brand_id);
-- 复合索引选择性更高

场景二:日志系统

sql

CREATE TABLE logs (
  log_id BIGINT PRIMARY KEY,
  app_id INT,
  log_level TINYINT,
  create_time DATETIME,
  message TEXT
);

-- 分析选择性(1000 万条日志)
SELECT 
  COUNT(DISTINCT app_id) / COUNT(*) AS sel_app,  -- 0.0001 (1000 个应用)
  COUNT(DISTINCT log_level) / COUNT(*) AS sel_level,  -- 0.0000005 (5 个级别)
  COUNT(DISTINCT DATE(create_time)) / COUNT(*) AS sel_date  -- 0.001 (365 天)
FROM logs;

-- 设计索引策略:
-- 1. log_level 选择性极低,不适合作为索引第一列
-- 2. app_id 选择性中等,适合作为第一列
-- 3. create_time 适合范围查询

CREATE INDEX idx_app_time ON logs(app_id, create_time);
-- app_id 等值过滤 + create_time 范围扫描 + 排序

监控与维护

定期更新统计信息

sql

-- MySQL:更新表的统计信息
ANALYZE TABLE users;

-- PostgreSQL:更新统计信息
ANALYZE users;

-- SQL Server:更新统计信息
UPDATE STATISTICS users;

查看统计信息

sql

-- MySQL:查看索引统计
SHOW INDEX FROM users;

-- 输出包含:
-- Cardinality:索引的基数(不同值的数量)
-- 选择性 = Cardinality / 总行数

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

-- n_distinct:不同值的数量估计
-- correlation:物理存储顺序与索引顺序的相关性

选择性与基数的关系

**基数 (Cardinality)**是指索引列中不同值的数量:

sql

-- 基数
SELECT COUNT(DISTINCT column) FROM table;

-- 选择性
SELECT COUNT(DISTINCT column) / COUNT(*) FROM table;

-- 关系:选择性 = 基数 / 总行数

两者通常一起使用:

基数:绝对值,表示有多少不同值
选择性:相对值,表示不同值占总行的比例

关联术语

[[基数]]
[[最左前缀原则]]
[[覆盖索引]]
[[执行计划]]
[[统计信息]]

参考资料

MySQL 官方文档: How MySQL Uses Indexes
《高性能 MySQL》第 5 章:索引选择性分析
Use The Index, Luke: Selectivity
Percona Blog: Understanding Index Selectivity

定义 ​

详细笔记 ​

核心原理 ​

选择性的意义 ​

计算索引选择性 ​

单列选择性 ​

复合索引选择性 ​

选择性与查询性能 ​

高选择性索引的优势 ​

优化器的选择 ​

选择性阈值参考 ​

提升索引选择性的策略 ​

策略一:使用前缀索引 ​

策略二:使用复合索引 ​

策略三:添加哈希列 ​

实际应用场景 ​

场景一:电商商品搜索 ​

场景二:日志系统 ​

监控与维护 ​

定期更新统计信息 ​

查看统计信息 ​

选择性与基数的关系 ​

关联术语 ​

参考资料 ​

定义