小巧。快速。可靠。
三选二。
部分索引
► 目录

1. 简介

部分索引是对表中一部分行建立的索引。

在普通索引中，表中的每一行在索引中都恰好对应一个条目。而在部分索引中，只有表中一部分行的对应索引条目。例如，部分索引可能会忽略索引列值为 NULL 的行对应的条目。当谨慎使用时，部分索引可以减少数据库文件的大小，并提高查询和写入性能。

2. 创建部分索引

通过在普通的 CREATE INDEX 语句末尾添加 WHERE 子句来创建部分索引。

create-index-stmt

CREATE UNIQUE INDEX IF NOT EXISTS schema-name . index-name ON table-name ( indexed-column ) , WHERE expr

expr

indexed-column

任何在末尾包含 WHERE 子句的索引都被视为部分索引。省略 WHERE 子句的索引（或在 CREATE TABLE 语句中由 UNIQUE 或 PRIMARY KEY 约束创建的索引）是普通的完整索引。

WHERE 子句后面的表达式可以包含运算符、字面量值以及被索引表的列名。WHERE 子句不能包含子查询、对其他表的引用、非确定性函数 或 绑定参数。

只有 WHERE 子句计算结果为真的表的行才会包含在索引中。如果 WHERE 子句表达式对于表中的某些行计算结果为 NULL 或 false，则这些行将从索引中省略。

部分索引的 WHERE 子句中引用的列可以是表中的任何列，而不仅仅是碰巧被索引的列。但是，部分索引的 WHERE 子句表达式通常是索引列上的简单表达式。以下是一个典型的例子

CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;

在上面的示例中，如果大多数采购订单没有“父”采购订单，则大多数 parent_po 值将为 NULL。这意味着 purchaseorder 表中只有一小部分行会被索引。因此，索引将占用更少的存储空间。并且对原始 purchaseorder 表的更改将运行得更快，因为 po_parent 索引只需要更新 parent_po 不为 NULL 的那些特殊行。但索引仍然对查询有用。特别是，如果要了解特定采购订单“？1”的所有“子订单”，则查询将是

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

上面的查询将使用 po_parent 索引来帮助找到答案，因为 po_parent 索引包含所有相关行的条目。请注意，由于 po_parent 比完整索引小，因此查询也可能会运行得更快。

2.1. 唯一部分索引

部分索引定义可以包含 UNIQUE 关键字。如果包含，则 SQLite 要求索引中的每个条目都必须是唯一的。这提供了一种机制来强制执行表中某些行的唯一性。

例如，假设您有一个大型组织成员的数据库，其中每个人都被分配到一个特定的“团队”。每个团队都有一个“领导”，该领导也是该团队的成员。该表可能如下所示

CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);

team_id 字段不能是唯一的，因为通常同一个团队有多个人。无法使 team_id 和 is_team_leader 的组合唯一，因为每个团队通常有多个非领导者。强制每个团队只有一个领导者的解决方案是在 team_id 上创建一个唯一索引，但仅限于 is_team_leader 为真的那些条目

CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;

碰巧，同一个索引也可用于查找特定团队的团队领导

SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;

3. 使用部分索引的查询

令 X 为部分索引的 WHERE 子句中的表达式，令 W 为使用被索引表的查询的 WHERE 子句。那么，如果 W⇒X，则允许查询使用部分索引，其中 ⇒ 运算符（通常发音为“蕴含”）是逻辑运算符，等价于“X 或非 W”。因此，确定部分索引是否可在特定查询中使用的问题简化为证明一阶逻辑中的定理。

SQLite没有复杂的定理证明器来确定 W⇒X。相反，SQLite 使用两个简单的规则来查找 W⇒X 为真的常见情况，并假设所有其他情况都为假。SQLite 使用的规则如下

  1. 如果 W 是 AND 连接的项，X 是 OR 连接的项，并且 W 的任何项都出现在 X 中，则部分索引可用。

    例如，令索引为

    CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;

    并令查询为

    SELECT * FROM tab1 WHERE b=6 AND a=7; -- uses partial index

    则查询可以使用该索引，因为“b=6”项同时出现在索引定义和查询中。请记住：索引中的项应为 OR 连接，查询中的项应为 AND 连接。

    W 和 X 中的项必须完全匹配。SQLite 不会进行代数运算来使它们看起来相同。“b=6”与“b=3+3”或“b-6=0”或“b BETWEEN 6 AND 6”不匹配。“b=6”将匹配“6=b”，只要“b=6”在索引中，“6=b”在查询中。如果索引中出现“6=b”形式的项，则它永远不会匹配任何内容。

  2. 如果 X 中的项为“z IS NOT NULL”形式，并且 W 中的项为“z”上的比较运算符（非“IS”），则这些项匹配。

    示例：令索引为

    CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;

    则任何对列“c”使用运算符 =、<、>、<=、>=、<>、IN、LIKE 或 GLOB 的查询都可使用部分索引，因为这些比较运算符仅在“c”不为 NULL 时才为真。因此，以下查询可以使用部分索引

    SELECT * FROM tab2 WHERE b=456 AND c<>0;  -- uses partial index

    但下一个查询无法使用部分索引

    SELECT * FROM tab2 WHERE b=456;  -- cannot use partial index

    后一个查询无法使用部分索引，因为表中可能存在 b=456 且 c 为 NULL 的行。但这些行不会出现在部分索引中。

这两条规则描述了截至本文撰写时（2013-08-01）SQLite 的查询计划程序的工作原理。并且上述规则将始终得到遵守。但是，SQLite 的未来版本可能会包含更好的定理证明器，该证明器可以找到更多 W⇒X 为真的情况，从而可能找到更多部分索引有用的实例。

4. 支持的版本

从 3.8.0 版（2013-08-26）开始，SQLite 就支持部分索引。

包含部分索引的数据库文件无法被 3.8.0 之前的 SQLite 版本读取或写入。但是，只要其模式不包含部分索引，由 SQLite 3.8.0 创建的数据库文件仍然可以被先前版本读取和写入。可以通过简单地对部分索引运行 DROP INDEX 来使无法被旧版 SQLite 读取的数据库变得可读。