一、KingbaseES 对于空值的默认排序规则
KingbaseES 对于 null 值的排序默认是最大的,也就是比任何值都大。具体见以下例子:
- test=# create table test(id integer,name text);
- test=# insert into test values(1,'a');
- test=# insert into test values(2,null);
-
- test=# select * from test order by name;
- id | name
- ----+------
- 1 | a
- 2 |
- (2 rows)
-
- test=# select * from test order by name desc;
- id | name
- ----+------
- 2 |
- 1 | a
二、空值的排序规则对于性能的影响
请看以下例子:
- create table t1(id integer,name text);
- insert into t1 select generate_series(10,10000000),generate_series(10,10000000);
- create index ind_t1_name on t1(name);
1、默认null 规则,都可以使用索引排序
可以看到,不管是升序还是降序,都可以使用索引
- test=# explain analyze select * from t1 order by name limit 1;
- QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.054..0.055 rows=1 loops=1)
- -> Index Scan using ind_t1_name on t1 (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.053..0.054 rows=1 loops=1)
- Planning Time: 0.110 ms
- Execution Time: 0.070 ms
- (4 rows)
-
- test=# explain analyze select * from t1 order by name desc limit 1;
- QUERY PLAN
- --------------------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.040..0.040 rows=1 loops=1)
- -> Index Scan Backward using ind_t1_name on t1 (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.039..0.039 rows=1 loops=1)
- Planning Time: 0.108 ms
- Execution Time: 0.056 ms
- (4 rows)
2、指定null 规则
由于null 是最大的,降序排序时,如果指定 null first ,则可以正常使用索引。
- test=# explain analyze select * from t1 order by name desc nulls first limit 1;
- QUERY PLAN
- --------------------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.019..0.020 rows=1 loops=1)
- -> Index Scan Backward using ind_t1_name on t1 (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.018..0.018 rows=1 loops=1)
- Planning Time: 0.162 ms
- Execution Time: 0.034 ms
- (4 rows)
-
- test=# explain analyze select * from t1 order by name desc nulls last limit 1;
- QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------
- Limit (cost=204347.03..204347.03 rows=1 width=11) (actual time=2250.512..2250.513 rows=1 loops=1)
- -> Sort (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2250.511..2250.512 rows=1 loops=1)
- Sort Key: name DESC NULLS LAST
- Sort Method: top-N heapsort Memory: 25kB
- -> Seq Scan on t1 (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..705.418 rows=9999991 loops=1)
- Planning Time: 0.067 ms
- Execution Time: 2250.533 ms
- (7 rows)
如果使用升序排序,则指定nulls last 时,可以使用索引
- test=# explain analyze select * from t1 order by name asc nulls first limit 1;
- QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------
- Limit (cost=204347.03..204347.03 rows=1 width=11) (actual time=2218.089..2218.090 rows=1 loops=1)
- -> Sort (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2218.088..2218.089 rows=1 loops=1)
- Sort Key: name NULLS FIRST
- Sort Method: top-N heapsort Memory: 25kB
- -> Seq Scan on t1 (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..706.852 rows=9999991 loops=1)
- Planning Time: 0.068 ms
- Execution Time: 2218.113 ms
- (7 rows)
-
- test=# explain analyze select * from t1 order by name asc nulls last limit 1;
- QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.020..0.020 rows=1 loops=1)
- -> Index Scan using ind_t1_name on t1 (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.019..0.019 rows=1 loops=1)
- Planning Time: 0.076 ms
- Execution Time: 0.035 ms
- (4 rows)
哪种情况下能使用索引,还与创建索引的索引是升序还是降序有关。
三、创建索引时可以指定nulls first/last 选项
默认降序索引时,null 是first 的。 因此,如果建索引时没有指定 nulls last ,则SQL 不能使用索引。
- test=# create index ind_t1_name on t1(name desc nulls last);
- CREATE INDEX
- test=# explain analyze select * from t1 order by name desc nulls last limit 10;
- QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------------
- Limit (cost=0.43..0.80 rows=10 width=11) (actual time=0.014..0.017 rows=10 loops=1)
- -> Index Scan using ind_t1_name on t1 (cost=0.43..369161.56 rows=9999992 width=11) (actual time=0.013..0.016 rows=10 loops=1)
- Planning Time: 0.161 ms
- Execution Time: 0.028 ms
- (4 rows)