nulls first & nulls last 对索引影响-阿里云开发者社区

开发者社区> rudy_gao> 正文

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条),其明显此时使用索引不是高效的

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

相关文章
干货 | Elasticsearch 索引生命周期管理 ILM 实战指南
1、什么是索引生命周期? 关于人生,有人这么说:“人,生来一个人,死去一个人,所以,人生就是一个人生老病死的简称。”
26 0
elasticsearch 索引存储深入详解(Elasticsearch教程03)|MVP讲堂
横扫你学习 Elasticsearch 的诸多疑惑,让你少走半年弯路!
1707 0
Elasticsearch索引迁移的四种方式
本文主要讲解Elasticsearch下实现索引迁移的几种方式。 #0、引言
12 0
Elasticsearch 5.4 Indices(索引) API
前言 一索引管理 1 创建索引 2 删除索引 3 查看索引信息 4 索引是否存在 5 关闭打开索引 6 索引收缩 7 翻滚索引 二mapping管理 1 设置mapping 2 查看mapping 3 获取字...
815 0
elasticsearch创建索引 ,mapping,dynamic_templates
elasticsearch 创建索引时通常需要配置mapping。mapping的含义类似于关系数据库中的表结构。但mapping更加灵活。
84 0
【漫画】ElasticSearch原理 必知必会的倒排索引和分词
通过漫画的形式,让你迅速理解ES必知必会的原理:倒排索引和分词。它是ES搜索的基础!你一定要懂哦!
2124 0
只需一步,DLA开启TableStore多元索引查询加速!
一、背景介绍 Data Lake Analytics(简称DLA)在构建第一天就是支持直接关联分析Table Store(简称OTS)里的数据,实现存储计算分离架构,满足用户基于SQL接口分析Table Store数据需求。
205 0
+关注
385
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载