多列索引
From PostgreSQL
| PostgreSQL 9.6.0 手册 | |||
|---|---|---|---|
| 上一页 | 上一级 | 章 11. 索引 | 下一页 |
11.3. 多列索引
一个索引可以定义在表的多个列上。例如，我们有这样一个表：
CREATE TABLE test2 ( major int, minor int, name varchar );
（即将我们的/dev目录保存在数据库中）而且我们经常会做如下形式的查询：
SELECT name FROM test2 WHERE major = constant AND minor = constant;
那么我们可以在major和minor上定义一个索引：
CREATE INDEX test2_mm_idx ON test2 (major, minor);
目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件pg_config_manual.h中修改，但是修改后需要重新编译PostgreSQL）。
一个B-tree索引可以用于条件中涉及到任意索引列子集的查询，但是当先导列（即最左边的那些列）上有约束条件时索引最为有效。确切的规则是：在先导列上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。例如，在(a, b, c)上有一个索引并且给定一个查询条件WHERE a = 5 AND b >= 42 AND c < 77，对索引的扫描将从第一个具有a = 5和b = 42的项开始向上进行，直到最后一个具有a = 5的项。在扫描过程中，具有c >= 77的索引项将被跳过，但是它们还是会被扫描到。这个索引在原则上可以被用于在b和/或c上有约束而在a上没有约束的查询，但是整个索引都不得不被扫描，因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。
一个多列GiST索引可以用于条件中涉及到任意索引列子集的查询。在其余列上的条件将限制由索引返回的项，但是第一列上的条件是决定索引上扫描量的最重要因素。当第一列中具有很少的可区分值时，一个GiST索引将会相对比较低效，即便在其他列上有很多可区分值。
一个GIN索引可以用于条件中涉及到任意索引列子集的查询。与B-tree和GiST不同，GIN的搜索效率与查询条件中使用哪些索引列无关。
多列 BRIN 索引可以被用于涉及该索引被索引列的任意子集的查询条件。和 GIN 相似且不同于 B-树 或者 GiST，索引搜索效率与查询条件使用哪个索引列无关。在单个表上使用多个 BRIN 索引来取代一个多列 BRIN 索引的唯一原因是为了使用不同的pages_per_range存储参数。
当然，要使索引其作用，查询条件中的列必须要使用适合于索引类型的操作符，使用其他操作符的子句将不会被考虑使用索引。
多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能解决时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。第 11.5 节以及第 11.11 节中有对不同索引配置优点的讨论。