PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.4. 索引和ORDER BY

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 11.4. 索引和ORDER BY 除了简单地查找查询要返回的行外,一个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独立的排序步骤。在PostgreSQL当前支持的索引类型中,只有B-tree可以产生排序后的输出,其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。

11.4. 索引和ORDER BY

除了简单地查找查询要返回的行外,一个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独立的排序步骤。在PostgreSQL当前支持的索引类型中,只有B-tree可以产生排序后的输出,其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。

规划器会考虑以两种方式来满足一个ORDER BY说明:扫描一个符合说明的可用索引,或者先以物理顺序扫描表然后再显式排序。对于一个需要扫描表的大部分的查询,一个显式的排序很可能比使用一个索引更快,因为其顺序访问模式使得它所需要的磁盘I/O更少。只有在少数行需要被取出时,索引才会更有用。一种重要的特殊情况是ORDER BYLIMIT n联合使用:一个显式的排序将会处理所有的数据来确定最前面的n行,但如果有一个符合ORDER BY的索引,前n行将会被直接获取且根本不需要扫描剩下的数据。

默认情况下,B-tree索引将它的项以升序方式存储,并将空值放在最后。这意味着对列x上索引的一次前向扫描将产生满足ORDER BY x(或者更长的形式:ORDER BY x ASC NULLS LAST)的结果。索引也可以被后向扫描,产生满足ORDER BY x DESCORDER BY x DESC NULLS FIRST, NULLS FIRSTORDER BY DESC的默认情况)。

我们可以在创建B-tree索引时通过ASCDESCNULLS FIRSTNULLS LAST选项来改变索引的排序,例如:

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)就可以产生这种排序。

显然,具有非默认排序的索引是相当专门的特性,但是有时它们会为特定查询提供巨大的速度提升。是否值得维护这样一个索引取决于我们会多频繁地使用需要特殊排序的查询。

本文转自PostgreSQL中文社区,原文链接:11.4. 索引和ORDER BY

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
18天前
|
SQL Java 编译器
SQL 语言:嵌入式 SQL 和动态 SQL
SQL 语言:嵌入式 SQL 和动态 SQL
22 4
|
18天前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(下)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
25 1
|
18天前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(中)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
29 1
|
5天前
|
SQL 存储 数据库
SQL 撤销索引、撤销表以及撤销数据库
SQL 撤销索引、撤销表以及撤销数据库
17 4
|
3天前
|
SQL 存储 关系型数据库
不懂索引,简历上都不敢写自己熟悉SQL优化
大家好,我是考哥。今天给大家带来MySQL索引相关核心知识。对MySQL索引的理解甚至比你掌握还重要,索引是优化SQL的前提和基础,我们一步步来先打好地基。当MySQL表数据量不大时,缺少索引对查询性能的影响都不会太大,可能都是0.0几秒;但当表数据量逐日递增时,建立一个合适且优雅的索引就至关重要了。
691 0
不懂索引,简历上都不敢写自己熟悉SQL优化
|
5天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
12天前
|
存储 算法 数据处理
惊人!PolarDB-X 存储引擎核心技术的索引回表优化如此神奇!
【6月更文挑战第11天】PolarDB-X存储引擎以其索引回表优化技术引领数据库发展,提升数据检索速度,优化磁盘I/O,确保系统在高并发场景下的稳定与快速响应。通过示例代码展示了在查询操作中如何利用该技术高效获取结果。索引回表优化具备出色性能、高度可扩展性和适应性,为应对大数据量和复杂业务提供保障,助力企业与开发者实现更高效的数据处理。
|
18天前
|
存储 SQL 数据库
SQL 语言:存储过程和触发器
SQL 语言:存储过程和触发器
32 6
|
16天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
16 2
|
16天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
18 2