翻译：尚凯
审核：魏波
目录
1 空值
2 多列索引
3 表达式索引
4 部分索引
5 排序
6 并发构建
空值
NULL作为表示不存在或未知值的一种方便的方法，在关系数据库中发挥着重要的作用。
但是特殊的值需要特殊的处理。常规布尔代数变为三元代数; 目前还不清楚NULL是小于还是大于常规值（这需要特殊的排序结构，NULLS FIRST和NULLS LAST）; 聚合函数是否应该考虑NULL是不明显的; 规划者需要一个特殊的统计数据......
从索引支持的角度来看，我们还不清楚是否需要对这些值进行索引。如果未对NULL进行索引，则索引可能更紧凑。但是如果对NULL进行索引，我们能够将索引用于像“ indexed-field IS[NOT] NULL“这样的条件，并且当没有为表指定条件时也可以作为覆盖索引（因为在这种情况下，索引必须返回所有表的数据行，包括那些使用NULL值的行）。
对于每种访问方法，开发人员决定是否对NULL进行索引。但通常情况下，它们会被编入索引。
多列索引
要支持多个字段的条件，可以使用多列索引。例如，我们可以在表的两个字段上构建索引：
postgres=# create index on t(a,b);postgres=# analyze t;
优化器很可能更喜欢这个索引，而不是加入位图，因为在这里我们很容易获得所需的TID而无需任何辅助操作：
postgres=# explain (costs off) select * from t where a <= 100 and b = 'a';QUERY PLAN------------------------------------------------Index Scan using t_a_b_idx on tIndex Cond: ((a <= 100) AND (b = 'a'::text))(2 rows)
多列索引还可以通过某些字段的条件(从第一个字段开始)加快数据检索速度：
postgres=# explain (costs off) select * from t where a <= 100;QUERY PLAN--------------------------------------Bitmap Heap Scan on tRecheck Cond: (a <= 100)-> Bitmap Index Scan on t_a_b_idxIndex Cond: (a <= 100)(4 rows)
通常，如果第一个字段没有附加条件，则不使用索引。但有时优化器可能会认为索引的使用比顺序扫描更有效。在考虑btree索引时，我们将扩展这个主题。
并非所有访问方法都支持在多个列上构建索引。
表达式索引
我们已经提到搜索条件必须看起来像“ 索引字段操作符表达式 ”。在下面的示例中，将不使用索引，因为使用包含字段名称的表达式而不是字段名称本身：
postgres=# explain (costs off) select * from t where lower(b) = 'a';QUERY PLAN------------------------------------------Seq Scan on tFilter: (lower((b)::text) = 'a'::text)(2 rows)
重写此特定查询并不需要太多时间，只要字段名称写入操作符的左侧。如果这不允许，表达式索引(函数索引)将会有所帮助：
postgres=# explain (costs off) select * from t where lower(b) = 'a';QUERY PLAN----------------------------------------------------Bitmap Heap Scan on tRecheck Cond: (lower((b)::text) = 'a'::text)-> Bitmap Index Scan on t_lower_idxIndex Cond: (lower((b)::text) = 'a'::text)(4 rows)
函数索引不是建立在表字段上，而是建立在任意表达式上。优化器会将此索引视为“索引表达式操作符表达式 ”之类的条件。如果要索引表达式的计算代价高昂，那么索引的更新也将需要大量的计算资源。
还请记住，为索引表达式收集了单独的统计信息。可以通过索引名称在pg_stats视图中了解这个统计信息：
postgres=# create index on t(lower(b));postgres=# analyze t;postgres=# \d tTable "public.t"Column | Type | Modifiers--------+---------+-----------a | integer |b | text |c | boolean |Indexes:"t_a_b_idx" btree (a, b)"t_a_idx" btree (a)"t_b_idx" btree (b)"t_lower_idx" btree (lower(b))postgres=# select * from pg_stats where tablename = 't_lower_idx';
如果有必要，可以使用与常规数据字段相同的方式控制直方图的栏的数量（注意，根据索引表达式，列名可能不同）：
postgres=# \d t_lower_idxIndex "public.t_lower_idx"Column | Type | Definition--------+------+------------lower | text | lower(b)btree, for table "public.t"postgres=# alter index t_lower_idx alter column "lower" set statistics 69;
部分索引
有时需要仅索引表行的一部分。这通常与高度不均匀的分布有关：通过索引搜索不频繁的值是有意义的，但通过对表的完全扫描更容易找到频繁值。
我们当然可以在«c»列上建立一个常规索引，它将以我们期望的方式工作：
postgres=# create index on t(c);postgres=# analyze t;postgres=# explain (costs off) select * from t where c;QUERY PLAN-------------------------------Index Scan using t_c_idx on tIndex Cond: (c = true)Filter: c(3 rows)postgres=# explain (costs off) select * from t where not c;QUERY PLAN-------------------Seq Scan on tFilter: (NOT c)(2 rows)
索引大小为276页：
postgres=# select relpages from pg_class where relname='t_c_idx';relpages----------276(1 row)
但由于«c»列仅对1％的行具有true值，因此99％的索引实际上从未使用过。在这种情况下，我们可以构建一个部分索引：
postgres=# create index on t(c) where c;postgres=# analyze t;
索引的大小减少到5页：
postgres=# select relpages from pg_class where relname='t_c_idx1';relpages----------5(1 row)
有时，大小和性能上的差异可能非常显著。
排序
如果访问方法以某种特定顺序返回行标识符，这将为优化器提供额外的选项来执行查询。
我们可以扫描表格，然后对数据进行排序：
postgres=# set enable_indexscan=off;postgres=# explain (costs off) select * from t order by a;QUERY PLAN---------------------SortSort Key: a-> Seq Scan on t(3 rows)
但是我们可以很容易地按照所需的顺序使用索引读取数据：
postgres=# set enable_indexscan=on;postgres=# explain (costs off) select * from t order by a;QUERY PLAN-------------------------------Index Scan using t_a_idx on t(1 row)
所有访问方法中只有«btree»可以返回排序数据，所以让我们推迟更详细的讨论，直到考虑到这种类型的索引。
并发构建
通常构建索引会获取表的SHARE锁。此锁允许从表中读取数据，但禁止在构建索引时进行任何更改。
我们可以确保这一点，例如，在表«t»中构建索引时，我们在另一个会话中执行以下查询：
postgres=# select mode, granted from pg_locks where relation = 't'::regclass;mode | granted-----------+---------ShareLock | t(1 row)
如果表足够大并且广泛用于插入，更新或删除，这可能是不允许的，因为修改进程将等待锁释放很长时间。
在这种情况下，我们可以使用并发构建索引。
postgres=# create index concurrently on t(a);
此命令以SHAREUPDATE EXCLUSIVE模式锁定表，该模式允许读取和更新（仅禁止更改表结构，以及并发清理，分析或在此表上构建另一个索引）。
然而，也有不利的一面。首先，索引将比平常构建得更慢，因为在表中完成两次传递而不是一次，并且还需要等待完成修改数据的并行事务。
其次，在并发构建索引时，可能会发生死锁或者违反唯一约束。但是，该索引将被建立，尽管尚未运行。必须删除并重建此类索引。非操作索引在psql \ d命令的输出中用INVALID字标记，下面的查询返回这些索引的完整列表：
postgres=# select indexrelid::regclass index_name, indrelid::regclass table_namefrom pg_index where not indisvalid;index_name | table_name------------+------------t_a_idx | t(1 row)
下期预告
下篇文章将关于“访问方法接口的结构”
原文请点击下方“阅读原文”获取
