一月 16, 2025
摘要:在本教程中,我们将对比 PostgreSQL 中的复合索引和多个索引。
目录
复合索引和多个索引
关于索引的使用,有一个最常见问题:是为每个列创建一个索引更好,还是为WHERE
子句的所有列创建一个索引更好?在大多数情况下,答案非常简单:一个包含多个列的索引会更好,即多列索引或复合索引。”多列索引“ 的文档中有详细的解释。
然而,无论您如何定义索引,都存在单个索引无法完美完成的查询;例如,带有两个或多个独立范围条件的查询,如下例所示:
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE UPPER(last_name) < ?
AND date_of_birth < ?
在没有过滤谓词的情况下,不可能定义出支持此查询的 B 树索引。为了解释,你只需要记住一个索引就是一个链表。
如果将索引定义为UPPER(LAST_NAME), DATE_OF_BIRTH
(按此顺序),则链表以 A 开头,以 Z 结尾。仅当有两名员工同名时,才会考虑出生日期。如果以DATE_OF_BIRTH, UPPER(LAST_NAME)
的方式定义索引,它将从最年长的员工开始,以最年轻的员工结束。在这种情况下,名称对排序顺序的影响很小。
无论您如何扭转和调整索引的定义,条目始终沿一条链排列。小的条目在一端,大的条目在另一端。因此,一个索引只能支持一个范围条件作为访问谓词。支持两个独立的范围条件需要第二个轴线,比如像一个棋盘。然后,上面的查询将匹配来自棋盘一角的所有条目,但索引不像一个棋盘 — 它就像一条链。没有角落。
当然,您可以接受过滤谓词,并使用多列索引。不管怎样,在许多情况下,这是最好的解决方案。然后,索引的定义应该首先提及选择率更高的列,以便它可以同访问谓词一起使用。每次创建一个复合索引时,都必须明智地选择列的顺序。但是,有一种误解是,您应该始终将选择率最高的列放在第一个位置;那是错误的。
重要:在定义一个复合索引时,最重要的考虑点是如何选择列的顺序,以便可以尽可能频繁地使用它。
另一种选择是使用两个单独的索引,每个列一个。然后,数据库必须首先扫描这两个索引,然后合并结果。只是重复的索引查找,就已经涉及更多的工作了,因为数据库必须遍历两个索引树。此外,数据库需要大量内存和 CPU 时间,来组合中间结果。
注意:一个索引的扫描比两个索引的扫描要快。
组合多个索引
PostgreSQL 能够组合多个索引,来处理单个索引扫描无法实现的情况。“组合多个索引” 的文档中详细解释了相关的算法。
在一个数据仓库的世界中,会有许多不可预测的临时查询。只需单击几下,即可将任意条件组合到您选择的查询中。无法预测出WHERE
子句中可能出现的列的组合,这使得索引的使用几乎不可能。
多个索引的优点是,它们可以很容易地组合。这意味着在单独地索引每个列时,您可以获得不错的性能。相反,如果您提前知道查询,以便您可以创建定制的多列 B 树索引,则它仍然会比组合多个索引更快。
在没有更好的访问路径的情况下,PostgreSQL 会将多个 B 树索引扫描的结果转换为内存中的位图结构。这些结果可以高效地组合起来。位图结构不是持久化存储的,而会在语句执行后被丢弃,从而避免了写数据时扩展性差的问题。缺点是它需要大量的内存和 CPU 时间。毕竟,这种方法也算是优化器最后的一种选择了。