开发者社区> 德哥> 正文

PostgreSQL 数据库NULL值的默认排序行为与查询、索引定义规范 - nulls first\last, asc\desc

简介:
+关注继续查看

标签

PostgreSQL , NULLS FIRST , NULLS LAST , ASC , DESC , 默认行为 , sort


背景

在数据库中NULL值是指UNKNOWN的值,不存储任何值,在排序时,它排在有值的行前面还是后面通过语法来指定。

例如

-- 表示null排在有值行的前面  
select * from tbl order by id nulls first;  
  
-- 表示null排在有值行的后面  
select * from tbl order by id nulls last;  

同时对于有值行,可以指定顺序排还是倒序排。

-- 表示按ID列顺序排  
select * from tbl order by id [asc];  
  
-- 表示按ID列倒序排  
select * from tbl order by id desc;  

默认的排序规则如下:

desc nulls first : null large small    
  
asc nulls last : small large null    

当nulls [first|last]与asc|desc组合起来用时,是这样的。

值的顺序如下:

1、DEFAULT:(认为NULL比任意值都大)

desc nulls first : 顺序:null large small    
  
asc nulls last   : 顺序:small large null    

2、NON DEFAULT: (认为NULL比任意值都小)

desc nulls last : 顺序:large small null       
  
asc nulls first : 顺序:null small large       

由于索引是固定的,当输入排序条件时,如果排序条件与索引的排序规则不匹配时,会导致无法使用索引的实惠(顺序扫描)。导致一些不必要的麻烦。

索引定义与扫描定义不一致引发的问题

1、建表,输入测试数据

create table cc(id int not null);  
  
insert into cc select generate_series(1,1000000);  

2、建立索引(使用非默认配置,null比任意值小)

create index idx_cc on cc (id asc nulls first);  
  
或  
  
create index idx_cc on cc (id desc nulls last);  

3、查询,与索引定义的顺序(指NULL的相对位置)不一致时,即使使用索引,也需要重新SORT。

select * from table order by id desc nulls first limit 1;   
select * from table order by id [asc] nulls last limit 1;   

用到了额外的SORT

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cc order by id limit 1;  
                                                                 QUERY PLAN                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=27969.43..27969.43 rows=1 width=4) (actual time=263.972..263.972 rows=1 loops=1)  
   Output: id  
   Buffers: shared hit=7160  
   ->  Sort  (cost=27969.43..30469.43 rows=1000000 width=4) (actual time=263.970..263.970 rows=1 loops=1)  
         Output: id  
         Sort Key: cc.id  
         Sort Method: top-N heapsort  Memory: 25kB  
         Buffers: shared hit=7160  
         ->  Bitmap Heap Scan on public.cc  (cost=8544.42..22969.42 rows=1000000 width=4) (actual time=29.927..148.733 rows=1000000 loops=1)  
               Output: id  
               Heap Blocks: exact=4425  
               Buffers: shared hit=7160  
               ->  Bitmap Index Scan on idx_cc  (cost=0.00..8294.42 rows=1000000 width=0) (actual time=29.380..29.380 rows=1000000 loops=1)  
                     Buffers: shared hit=2735  
 Planning time: 0.098 ms  
 Execution time: 264.009 ms  
(16 rows)  

3、查询,与索引定义一致(指NULL的相对位置)时,索引有效,不需要额外SORT。

select * from table order by id desc nulls last limit 1;   
select * from table order by id [asc] nulls first limit 1;   

不需要额外SORT

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cc order by id nulls first limit 1;  
                                                              QUERY PLAN                                                                 
---------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.42..0.45 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)  
   Output: id  
   Buffers: shared hit=4  
   ->  Index Only Scan using idx_cc on public.cc  (cost=0.42..22719.62 rows=1000000 width=4) (actual time=0.013..0.013 rows=1 loops=1)  
         Output: id  
         Heap Fetches: 1  
         Buffers: shared hit=4  
 Planning time: 0.026 ms  
 Execution time: 0.022 ms  
(9 rows)  

小结

在PostgreSQL中顺序、倒序索引是通用的。不同的是null的相对位置。

因此在创建索引时,务必与业务的需求对齐,使用一致的NULL相对顺序(nulls first 或 nulls last 与asc,desc的搭配)(即NULL挨着large value还是small value),而至于值的asc, desc实际上是无所谓的。 

如果业务需求的顺序与索引的顺序不一致(指null的相对顺序),那么会导致索引需要全扫,重新SORT的问题。

内核改进

1、当约束设置了not null时,应该可以不care null的相对位置,因为都没有NULL值了,优化器应该可以不管NULL的相对位置是否与业务请求的SQL的一致性,都选择非Sort模式扫描。

2、改进索引扫描方法,支持环形扫描。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【学习资料】第15期快速入门PostgreSQL应用开发与管理 - 5 数据定义
大家好,这里是快速入门PostgreSQL应用开发与管理 - 5 数据定义
94 0
分析型数据库 PostgreSQL版本实例规格定义变更
信息摘要: 实例由按多个“计算组”组成,改为由按“节点”组成。一个节点只对应一个数据分区,简化规格定义,遵循标准集群数据库的模式。适用客户: 数据仓库/大数据/数据分析/数据库用户;政府/金融/零售/互联网等行业版本/规格功能: AnalyticDB for PostgreSQL 实例规格定义由多个“计算组”组成改为由多个“节点”组成。
902 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义
第 5 章 数据定义 目录 5.1. 表基础 5.2. 默认值 5.3. 约束 5.3.1. 检查约束 5.3.2. 非空约束 5.3.3. 唯一约束 5.3.4. 主键 5.3.5. 外键 5.
1013 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.13. 依赖跟踪
5.13. 依赖跟踪 当我们创建一个涉及到很多具有外键约束、视图、触发器、函数等的表的复杂数据库结构时,我们隐式地创建了一张对象之间的依赖关系网。例如,具有一个外键约束的表依赖于它所引用的表。
1046 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.12. 其他数据库对象
5.12. 其他数据库对象 表是一个关系型数据库结构中的核心对象,因为它们承载了我们的数据。但是它们并不是数据库中的唯一一种对象。有很多其他种类的对象可以被创建来使得数据的使用和刮泥更加方便或高效。
904 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.11. 外部数据
5.11. 外部数据 PostgreSQL实现了部分的SQL/MED规定,允许我们使用普通SQL查询来访问位于PostgreSQL之外的数据。这种数据被称为外部数据(注意这种用法不要和外键混淆,后者是数据库中的一种约束)。
1064 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.10. 表分区
5.10. 表分区 5.10.1. 概述 5.10.2. 声明式分区 5.10.3. 使用继承的实现 5.10.4. 划分和约束排除 PostgreSQL支持基本的表划分。本小节介绍为何以及怎样把划分实现为数据库设计的一部分。
1113 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.9. 继承
5.9. 继承 5.9.1. 警告 PostgreSQL实现了表继承,这对数据库设计者来说是一种有用的工具(SQL:1999及其后的版本定义了一种类型继承特性,但和这里介绍的继承有很大的不同)。
1048 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.8. 模式
5.8. 模式 5.8.1. 创建模式 5.8.2. 公共模式 5.8.3. 模式搜索路径 5.8.4. 模式和权限 5.8.5. 系统目录模式 5.8.6. 惯用法 5.8.7. 可移植性 一个PostgreSQL数据库集簇中包含一个或更多命名的数据库。
1193 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.7. 行安全性策略
5.7. 行安全性策略 除可以通过GRANT使用 SQL 标准的 特权系统之外,表还可以具有 行安全性策略,它针对每一个用户限制哪些行可以 被普通的查询返回或者可以被数据修改命令插入、更新或删除。
961 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
云数据库RDS MySQL从入门到高阶
立即下载
PolarDB for PostgreSQL 源码与应用实战
立即下载
PolarDB for PostgreSQL 开源必读手册
立即下载