Partial Indexes

1.介绍

2.创建部分索引

2.1.独特的部分索引

3.使用部分索引查询

4.支持的版本

部分索引是表格行的子集上的索引。

在普通索引中，表中每一行的索引中只有一个条目。在部分索引中，表中只有一部分行具有相应的索引条目。例如，部分索引可能会忽略被索引的列为NULL的条目。如果明智地使用，部分索引可能导致较小的数据库文件，并改善查询和写入性能。

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

create-index-stmt: hide

expr: show

literal-value: show

raise-function: show

select-stmt: show

common-table-expression: show

compound-operator: show

join-clause: show

join-constraint: show

join-operator: show

ordering-term: show

result-column: show

table-or-subquery: show

type-name: show

signed-number: show

indexed-column: show

包含最后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表中的一小部分行会被索引。因此，指数将占用更少的空间。原始采购订单表的更改将运行得更快，因为只需要为parent_po不为NULL的那些例外行更新po_parent索引。但是索引对于查询仍然有用。特别是，如果想知道特定采购订单“1”的所有“子女”，该查询将是：

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

上面的查询将使用po_parent索引来帮助查找答案，因为po_parent索引包含所有感兴趣的行的条目。请注意，由于po_parent小于完整索引，查询运行速度可能会更快。

2.1. Unique Partial Indexes

部分索引定义可能包括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;

假设X是部分索引的WHERE子句中的表达式，并且让W是使用索引表的查询的WHERE子句。然后，查询被允许使用部分索引，如果W⇒X，其中⇒运算符（通常发音为“隐含”）是相当于“X或不W”的逻辑运算符。因此，确定部分索引是否可用于特定查询中减少了在一阶逻辑中证明定理。

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

• 如果W是AND连接项并且X是OR连接项并且W的任何项出现为X的项，那么部分索引是可用的。 例如，让索引为CREATE INDEX ex1 ON tab1（a，b）WHERE a = 5或b = 6; 让查询为：SELECT * FROM tab1 WHERE b = 6 AND a = 7; - 使用部分索引然后索引可用于查询，因为“b = 6”项出现在索引定义和查询中。记住：索引中的术语应该是OR连接的，查询中的术语应该是AND连接的。 W和X中的术语必须完全匹配。SQLite不会做代数，试图让它们看起来一样。术语“b = 6”不匹配“b = 3 + 3”或“b-6 = 0”或“b BETWEEN 6和6”。只要“b = 6”在索引上并且“6 = b”与“6 = b”匹配，“b = 6” 在查询中。如果索引中出现“6 = b”形式的术语，它将永远不会匹配任何内容。

• 如果X中的项的形式为“z IS NOT NULL”，并且如果W中的项是除“IS”之外的“z”上的比较运算符，则这些项匹配。

示例：让索引成为

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; - 使用部分索引

但是下一个查询不能使用部分索引：

SELECT * FROM tab2 WHERE b = 456; - 不能使用部分索引

后者查询不能使用部分索引，因为表中可能有b = 456且c为NULL的行。但是这些行不在部分索引中。

这两条规则描述了SQLite的查询规划器如何在撰写本文时使用（2013-08-01）。上述规则将永远受到尊重。但是，未来版本的SQLite可能包含一个更好的定理证明器，它可以找到W⇒X为真的其他情况，因此可能会发现部分索引有用的更多实例。

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

包含部分索引的数据库文件在3.8.0之前版本的SQLite中不可读写。但是，由SQLite 3.8.0创建的数据库文件仍然可以被先前版本读写，只要其架构不包含部分索引。只有通过在部分索引上运行DROP INDEX，才能使旧版SQLite无法读取的数据库变得可读。

 SQLite is in the Public Domain.