ORDER BY
#除了简单地查找查询要返回的行之外,索引还可以按特定的排序顺序返回这些行。这使得查询的 ORDER BY
规范可以在不进行单独排序步骤的情况下得到满足。在 PostgreSQL 当前支持的索引类型中,只有 B-tree 能够产生排序输出——其他索引类型返回匹配的行时,顺序是不确定的、依赖于实现的。
查询规划器会考虑通过扫描匹配该规范的可用索引,或者通过按物理顺序扫描表并执行显式排序来满足 ORDER BY
规范。对于需要扫描表中大部分行的情况,显式排序可能比使用索引更快,因为它由于顺序访问模式而需要的磁盘 I/O 更少。当只需要获取少量行时,索引更有用。一个重要的特殊情况是 ORDER BY
与 LIMIT
n
结合使用:显式排序需要处理所有数据来确定前 n
行,但如果存在匹配 ORDER BY
的索引,则可以直接检索前 n
行,而无需扫描剩余的部分。
默认情况下,B-tree 索引按升序存储其条目,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-tree 索引的排序顺序;例如:
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST); CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
以升序存储且 NULL 值排在前面的索引,根据扫描方向的不同,可以满足 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)
,则可以实现。
显然,具有非默认排序顺序的索引是一种相当特殊的特性,但有时它们可以为某些查询带来巨大的速度提升。维护这样一个索引是否值得,取决于您使用需要特殊排序顺序的查询的频率。
如果您在文档中发现任何不正确之处、与您在使用该特定功能时的经验不符之处,或者需要进一步说明之处,请使用 此表单 来报告文档问题。