数据库原理与选型 / 系统课时

唯一、部分与表达式索引

唯一、部分与表达式索引

本课解决三类更精细的问题:阻止重复值、只索引常用数据子集,以及按计算后的值查询。完成后,你能选择唯一索引、部分索引或表达式索引,并理解它们各自的匹配边界。

学习目标

  • 使用唯一索引实现数据唯一性约束
  • 使用部分索引缩小索引体积和维护范围
  • 使用表达式索引加速规范化后的查询

唯一索引同时承担约束

CREATE UNIQUE INDEX 不仅用于查询加速,还会拒绝产生重复键值的写入。它适合邮箱、外部订单号和业务编码等必须唯一的字段。

SQLite 的唯一索引允许出现多个 NULL,因为不同的 NULL 不被视为相等。若业务要求字段必须有值,还要同时声明 NOT NULL。

部分索引只覆盖满足条件的行

部分索引在索引定义末尾增加 WHERE,只保存满足谓词的记录。它适合“活跃任务”“未删除数据”“待处理订单”等远少于全表的数据子集。

查询条件必须能够让优化器判断出部分索引谓词成立。若索引限定 status = 'ACTIVE',查询也应明确包含等价条件;只写一个无法推出该条件的宽泛表达式,索引可能不会被选择。

表达式索引保存计算结果

表达式索引对函数或运算结果建立索引,例如统一大小写、去除电话号码分隔符或计算金额绝对值。查询中的表达式应与建索引时基本一致;SQLite 不会主动把数学上等价但写法不同的表达式全部改写成同一个形式。下面同时演示三种索引。

sql
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT,
  status TEXT NOT NULL,
  phone TEXT NOT NULL
);

CREATE UNIQUE INDEX ux_accounts_email
ON accounts(email);

CREATE INDEX idx_accounts_active_email
ON accounts(email)
WHERE status = 'ACTIVE';

CREATE INDEX idx_accounts_phone_digits
ON accounts(replace(phone, '-', ''));

INSERT INTO accounts (email, status, phone) VALUES
  ('alice@example.com', 'ACTIVE', '138-0000-0001'),
  ('bob@example.com', 'DISABLED', '138-0000-0002');

SELECT id, email
FROM accounts
WHERE replace(phone, '-', '') = '13800000001';

预期输出:

text
id | email
1  | alice@example.com

如果再次插入 alice@example.com,唯一索引会拒绝该记录。

sql
INSERT INTO accounts (email, status, phone)
VALUES ('alice@example.com', 'ACTIVE', '138-0000-0099');

预期结果:

text
UNIQUE constraint failed: accounts.email

不同 SQLite 客户端可能在错误前附加错误码或异常类型,但核心约束信息一致。

选择三类索引的判断顺序

需要保证数据不重复时,先考虑唯一约束或唯一索引。只有少量行参与高频查询时,考虑部分索引。查询长期按同一确定性表达式筛选或排序时,考虑表达式索引。

表达式索引中的函数必须是确定性的,即相同输入应得到相同结果。随机数、当前时间或会随外部状态变化的函数不适合作为索引表达式。

常见错误

不要把部分索引当成自动过滤器:它不会隐藏不满足条件的行,只是减少索引内容。也不要在查询中随意改写表达式,例如索引是 replace(phone, '-', ''),查询却改用另一套清洗函数,原索引通常无法匹配。

创建特殊索引后仍应检查查询计划,确认实际查询确实使用了预期索引,而不是只验证“索引创建成功”。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

唯一、部分与表达式索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通