ORDER BY
#除了查找查询要返回的行之外,索引还能以特定排序顺序提供这些行。这允许查询的 ORDER BY
规范在不单独执行排序步骤的情况下得到满足。PostgreSQL 当前支持的索引类型中,只有 B 树可以生成排序输出——其他索引类型以未指明、特定于实现的顺序返回匹配的行。
规划器会考虑通过扫描与该规范匹配的可用索引或者按物理顺序扫描数据表并执行显式排序,以满足 ORDER BY
规范。对于需要扫描数据表中大部分内容的查询来说,显式排序可能会比使用索引快,因为由于遵循顺序存取模式它需要更少的磁盘 I/O。当只需要获取几行内容时,索引更有用。一个重要的特例是与 LIMIT
n
结合使用的 ORDER BY
:显式排序必须处理所有数据以识别前 n
行,但是如果有一个与 ORDER BY
匹配的索引,则可以直接检索前 n
行,而完全不扫描剩余的数据。
B 树索引默认按列 x
的升序存储其条目,且 null 排在最后(表 TID 被当作其他相同条目之间的评判依据)。这意味着按列 x
的索引的正向扫描会生成满足 ORDER BY x
(或者更详细地说,ORDER BY x ASC NULLS LAST
)的输出。该索引还可以向后扫描,生成满足 ORDER BY x DESC
(或者更详细地说,ORDER BY x DESC NULLS FIRST
,因为 NULLS FIRST
为 ORDER BY DESC
的默认设置)的输出。
您可以在创建索引时,通过包含选项 ASC
、DESC
、NULLS FIRST
和/或 NULLS LAST
来调整 B 树索引的排序;例如
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
按升序存储的第一个值为空的索引可以满足 ORDER BY x ASC NULLS FIRST
或 ORDER BY x DESC NULLS LAST
,具体取决于在其中进行扫描的方向。
您可能好奇为什么提供所有四个选项, जबकि两个选项加上向后扫描的可能性就可以涵盖 ORDER BY
的所有变量。 在单列索引中,这些选项确实冗余,但在多列索引中,它们可能会很有用。 考虑对 (x, y)
的两列索引: 如果我们向前扫描, 这可以满足 ORDER BY x, y
; 如果我们向后扫描,则可以满足 ORDER BY x DESC, y DESC
。 但应用程序需要经常使用 ORDER BY x ASC, y DESC
。 通过普通索引,这里无法获得这种排序,但如果将索引定义为 (x ASC, y DESC)
或 (x DESC, y ASC)
则可以实现。
显然带非默认排序顺序的索引是一个专门的功能,但有时它们可以大幅提升某些查询的速度。是否值得维护此类索引,取决于您使用需要特殊排序顺序的查询的频率。