数据表索引学习笔记

maiaimei 2025/10/14

# 列的选择性（也称为区分度）

列的选择性（也称为区分度）是指某一列中不同值的个数与表中总行数的比值。这个概念对于索引设计非常重要。

计算公式：

选择性 = 列的不同值数量 / 表的总行数

具体解释：

选择性的值范围在0到1之间
值越接近1，说明列的选择性越高
值越接近0，说明列的选择性越低

举例说明：

-- 假设有一个用户表：
CREATE TABLE users (
    id INT,
    gender VARCHAR(10),
    email VARCHAR(50),
    city VARCHAR(50)
);

-- 计算选择性的SQL
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) as selectivity
FROM table_name;

-- 例如：
-- gender列：可能只有"男"、"女"两个值，如果有1000条记录
-- 选择性 = 2/1000 = 0.002（低选择性）
SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users;

-- email列：每个用户邮箱都不同，如果有1000条记录
-- 选择性 = 1000/1000 = 1（高选择性）
SELECT COUNT(DISTINCT email) / COUNT(*) FROM users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

低选择性的特点：

列中存在大量重复值。列的选择性低就意味着数据的离散度低，也就是说数据的重复度高。
不同值的个数远少于总记录数
通常不适合单独建立索引
例如：性别、状态、级别等字段

高选择性的特点：

列中的值大多是唯一的
不同值的个数接近总记录数
适合建立索引
例如：主键、邮箱、手机号等字段

常见列的选择性参考：

主键列：选择性 = 1（最高）
唯一键列：选择性 = 1

选择性的应用：

索引设计
- 高选择性的列适合作为索引
- 在复合索引中，通常将选择性高的列放在前面
实际应用建议：
- 选择性低于0.1的列通常不适合单独建立索引
- 复合索引中，建议将选择性高的列放在前面
- 需要结合实际查询场景来权衡

注意事项：

不能单纯依据选择性来决定索引策略
需要结合查询频率、表大小等因素
要考虑数据分布的均匀性
定期评估索引的使用效果

通过合理利用列的选择性特征，可以帮助我们设计出更高效的索引策略，从而提升查询性能。但要注意，选择性只是众多考虑因素中的一个，还需要结合具体的业务场景和查询模式来综合考虑。

# 单列索引

适用场景：

列独立作为查询条件的频率高
列的选择性较高
需要支持范围查询的列

# 复合索引

复合索引(Composite Index)是数据库中一种特殊的索引类型，它包含两个或多个列的组合。

主要特点：

多列组合：由多个字段共同组成一个索引
有序性：按照创建索引时指定的列顺序进行排序
最左匹配原则：必须按照索引最左边的列开始匹配才能生效

适用场景：

多列经常一起作为查询条件
查询条件遵循最左前缀原则
列的组合具有较高的选择性（返回的记录数较少）
多列经常一起作为排序条件
有范围查询需求的场合

举例说明：

-- 创建一个复合索引
CREATE INDEX idx_your_table_1 ON your_table (column_1, column_2, column_3);

-- 以下查询可以使用该索引
SELECT * FROM your_table WHERE column_1 = 'value_1' AND column_2 = 'value_2' AND column_3 = 'value_3';
SELECT * FROM your_table WHERE column_1 = 'value_1' AND column_2 = 'value_2';  -- 符合最左匹配
SELECT * FROM your_table WHERE column_1 = 'value_1';  -- 符合最左匹配

-- 以下查询无法使用该索引
SELECT * FROM your_table WHERE column_2 = 'value_2' AND column_3 = 'value_3';  -- 没有使用第一个索引列
SELECT * FROM your_table WHERE column_2 = 'value_2';  -- 没有使用第一个索引列
SELECT * FROM your_table WHERE column_3 = 'value_3';  -- 没有使用第一个索引列

-- 以下查询不能完全使用该索引
SELECT * FROM your_table WHERE column_1 = 'value_1' AND column_3 = 'value_3'; -- 仅使用第一个索引列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

对于复合索引 idx_your_table_1 (column_1, column_2, column_3)，当查询条件只有 column_1 和 column_3 时，这个索引只能部分生效，且效率不是最优的。这是因为：

复合索引的工作原理遵循"最左前缀"原则：
- 可以使用索引的情况：使用第一列(column_1)
- 可以使用索引的情况：使用第一列和第二列(column_1, column_2)
- 可以使用索引的情况：使用全部三列(column_1, column_2, column_3)
- 不能完全使用索引的情况：跳过中间列(column_1, column_3)
在您的查询场景中(column_1和column_3)：
- 索引会使用column_1部分
- 由于跳过了column_2，column_3部分将无法使用索引
- 数据库需要在筛选出的column_1结果集上进行column_2的过滤

优点：

可以减少索引的数量
提高查询效率
优化存储空间

注意事项：

遵循最左匹配原则
合理控制索引列的数量（通常不超过5个）
考虑列的选择性（区分度）
需要权衡查询性能和写入性能

最佳实践：

将查询频率最高的列放在最左边
将选择性最高的列放在最左边
避免创建冗余的索引
定期监控索引的使用情况

# 位图索引

位图索引(Bitmap Index)是一种特殊的数据库索引，它使用位数组来表示每个唯一值的行位置。

位图索引的工作原理：

为每个唯一值创建一个位图（位向量）
位图中的每一位对应表中的一行
如果该行包含这个值，相应位置为1，否则为0

示例说明：

-- 假设有一个用户表
CREATE TABLE users (
    id INT,
    gender VARCHAR(2)  -- 值只有 'M' 和 'F'
);

-- 为列gender创建位图索引
CREATE BITMAP INDEX idx_gender ON users(gender);

-- 数据示例：
id  | gender
1   | M
2   | F
3   | M
4   | F
5   | M

-- 位图索引表示：
M: 1 0 1 0 1  -- 表示gender='M'的行
F: 0 1 0 1 0  -- 表示gender='F'的行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

不同数据库对位图索引的支持：

Oracle：完全支持位图索引
PostgreSQL：不直接支持位图索引，但有位图扫描操作
MySQL：不支持位图索引
SQL Server：不直接支持位图索引，但有类似的位图过滤器

适用场景：

低选择性的列（重复值多的列）
静态数据（数据更新频率低）
大量的批量查询操作
经常需要进行AND和OR运算的查询

优点：

存储空间效率高
适合多条件组合查询
对于低选择性列的查询性能好
支持快速的位运算

缺点：

不适合频繁更新的数据
不适合高选择性的列
DML操作（插入、更新、删除）性能较差

最佳实践：

对于低选择性的列使用位图索引
适用于数据仓库等读多写少的场景
避免在OLTP系统中使用
考虑列的基数（不同值的数量）

使用建议：

当列的不同值数量少于总行数的1%时考虑使用
适合布尔值、状态码等低选择性列
需要评估数据更新频率
考虑多个位图索引的组合效果

注意事项：

在高并发更新环境下避免使用
需要考虑存储空间和维护成本
要评估查询模式是否适合位图索引
不同数据库的实现方式可能不同

总结：

位图索引特别适合低选择性的列
主要用于数据仓库等读密集型应用
不是所有数据库都支持位图索引
需要根据具体场景和数据库特性来决定是否使用

SQL学习笔记树

初冬十月的笔记

Choose mode