696
PostgreSQL NULL值相對位置與QUERY優化 - 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、改進索引掃描方法，支持環形掃描。
最後更新：2017-11-13 14:34:16