SQLite中文网
部分索引
▼ 目录

一、简介

部分索引是对表的行的子集的索引。

在普通索引中，表中的每一行在索引中只有一个条目。在部分索引中，表中只有部分行具有相应的索引条目。例如，部分索引可能会忽略被索引的列为 NULL 的条目。如果使用得当，部分索引可以减少数据库文件并提高查询和写入性能。

2.创建部分索引

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

创建索引 stmt：

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

表达式：

索引列：

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

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

只有表中 WHERE 子句的计算结果为 true 的行才会包含在索引中。如果 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 为 true 的那些条目：

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 or not 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”时为真不为空。所以下面的查询可以使用部分索引： 

    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 的行。但是这些行不会在部分索引中。

这两条规则描述了 SQLite 的查询规划器在撰写本文时 (2013-08-01) 是如何工作的。上面的规则将永远得到遵守。然而，SQLite 的未来版本可能会包含一个更好的定理证明器，它可以找到 W⇒X 为真的其他情况，因此可能会找到更多部分索引有用的实例。

4.支持的版本

SQLite 从3.8.0 (2013-08-26) 版本 开始支持部分索引。

3.8.0 之前的 SQLite 版本无法读取或写入包含部分索引的数据库文件。然而，由 SQLite 3.8.0 创建的数据库文件仍然可以被以前的版本读写，只要它的模式不包含部分索引。旧版 SQLite 无法读取的数据库只需 在部分索引上运行DROP INDEX即可使其可读。