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.本文档系腾讯云开发者社区成员共同维护，如有问题请联系 cloudcommunity@tencent.com
