开发者社区> rudy_gao> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

nulls first & nulls last 对索引影响

简介: -- 当我们需要排序字段时,比如order by name,如果name字段定义时没有not null时,就有可能涉及到null值的排序 -- 如果不注意,可能会造成隐藏的bug,pg默认n...
+关注继续查看
-- 当我们需要排序字段时,比如order by name,如果name字段定义时没有not null时,就有可能涉及到null值的排序
-- 如果不注意,可能会造成隐藏的bug,pg默认null是无穷大,在升序时排在最后面,当然在排序时也可以指定 nulls first 或 nulls last
-- 具体使用方法在此不在复述,本文主要是讲的是在创建索引时指定 nulls first 或 nulls last 对查询的影响


-- 基础创建索引语法如下
CREATE INDEX [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
    
-- 在升序或降序时的默认值
-- NULLS FIRST
Specifies that nulls sort before non-nulls. This is the default when DESC is specified.
-- NULLS LAST
Specifies that nulls sort after non-nulls. This is the default when DESC is not specified.



--创建测试表
create table t as select n id ,'rudy'||n as name ,n||'password' as password,now() + (n||' second')::interval as create_date from generate_seies(1,1000000) n;

postgres=# update t set name = null where mod(id,2)=0;
UPDATE 500000
-- 构造随机的null值,本次测试取对半
postgres=# create table t1 as select * from t; 
SELECT 1000000
postgres=# create index on t(name);
CREATE INDEX
postgres=# create index on t1(name nulls first);
CREATE INDEX

-- 在不指定 null first 时,由于pg默认null无限大,故当排序为升序时,t表能够使用索引,t1表却是使用全表扫描
postgres=# explain verbose select * from t where id>1000 order by name limit 10;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Limit  (cost=0.42..1.26 rows=10 width=33)
   Output: id, name, password, create_date
   ->  Index Scan using idx_t_name on public.t  (cost=0.42..83387.28 rows=999027 width=33)
         Output: id, name, password, create_date
         Filter: (t.id > 1000)
(5 rows)


postgres=# explain verbose select * from t1 where id>1000 order by name limit 10;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Limit  (cost=40949.92..40949.94 rows=10 width=33)
   Output: id, name, password, create_date
   ->  Sort  (cost=40949.92..43447.52 rows=999041 width=33)
         Output: id, name, password, create_date
         Sort Key: t1.name
         ->  Seq Scan on public.t1  (cost=0.00..19361.00 rows=999041 width=33)
               Output: id, name, password, create_date
               Filter: (t1.id > 1000)
(8 rows)


-- 在指定 nulls first 时,由于t1表创建索引时null值放在最前面,而且排序字段为升序,所以t1表使用索引,t表使用全表扫描
postgres=# explain verbose select * from t where id>1000 order by name nulls first limit 10; 
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Limit  (cost=46095.61..46095.64 rows=10 width=33)
   Output: id, name, password, create_date
   ->  Sort  (cost=46095.61..48593.18 rows=999027 width=33)
         Output: id, name, password, create_date
         Sort Key: t.name NULLS FIRST
         ->  Seq Scan on public.t  (cost=0.00..24507.00 rows=999027 width=33)
               Output: id, name, password, create_date
               Filter: (t.id > 1000)
(8 rows)


postgres=# explain verbose select * from t1 where id>1000 order by name nulls first limit 10;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.98 rows=10 width=33)
   Output: id, name, password, create_date
   ->  Index Scan using t1_name_idx on public.t1  (cost=0.42..55921.45 rows=999041 width=33)
         Output: id, name, password, create_date
         Filter: (t1.id > 1000)
(5 rows)



-- 综上,为什么会出现这样的结果,因为我们默认创建的btree索引,其叶子结点的数据是有序排列的,当创建索引不指定nulls first时,pgl默认把null值放在叶子节点的最后
-- 如果排序时只是order by name,未指定nulls first,pg只需要根据索引顺序的返回需要的数据则可,否则,如果order by name nulls last,pg如果使用索引,
-- 其可能先在叶子未尾节点返回null值的数据,再在叶子起始节点开始返回数据(假设需要返回10条数据,null值为5条,非null值有5条),其明显此时使用索引不是高效的

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

相关文章
find 删除命令rm还是delete更快
find 删除命令rm还是delete更快
0 0
Node 更新失败:cp cannot stat ‘usrlocalnversionsnode12
Node 更新失败:cp cannot stat ‘usrlocalnversionsnode12
0 0
文章没继续写是因为,ls
既然学习了就要去总结不能颓废 当复习了框架的重点都是配置文件 Struts2框架的应用 二、struts.xml配置文件详解 package:包,将Action(应用控制器)在逻辑上进行隔离,完成包中功能上的继承 name:给package起的名字,就是通过name属性完成继承 namespace:命名空间,action请求url的前缀(url的构成部分)包在地址栏的反应。
822 0
[20130226]ls -l显示的时间问题.txt
[20130226]ls -l显示的时间问题.txt# touch --date='2013-01-01 07:01:00' aa.txt# touch --date='2012-09-01 07:01:00' bb.
540 0
Linux 格式化分区 报错Could not stat --- No such file or directory 和 partprobe 命令
分区的过程正常: [root@db1 /]# fdisk -l   Disk /dev/sda: 21.4 GB, 21474836480 bytes 255 heads, 63 sectors/track, 2610 cylinders Units = cylinders of 160...
817 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载