• 空值的排序规则与性能


    一、KingbaseES 对于空值的默认排序规则

    KingbaseES 对于 null 值的排序默认是最大的,也就是比任何值都大。具体见以下例子:

    1. test=# create table test(id integer,name text);
    2. test=# insert into test values(1,'a');
    3. test=# insert into test values(2,null);
    4. test=# select * from test order by name;
    5. id | name
    6. ----+------
    7. 1 | a
    8. 2 |
    9. (2 rows)
    10. test=# select * from test order by name desc;
    11. id | name
    12. ----+------
    13. 2 |
    14. 1 | a

    二、空值的排序规则对于性能的影响

    请看以下例子:

    1. create table t1(id integer,name text);
    2. insert into t1 select generate_series(10,10000000),generate_series(10,10000000);
    3. create index ind_t1_name on t1(name);

    1、默认null 规则,都可以使用索引排序

    可以看到,不管是升序还是降序,都可以使用索引

    1. test=# explain analyze select * from t1 order by name limit 1;
    2. QUERY PLAN
    3. -----------------------------------------------------------------------------------------------------------------------------------
    4. Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.054..0.055 rows=1 loops=1)
    5. -> 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)
    6. Planning Time: 0.110 ms
    7. Execution Time: 0.070 ms
    8. (4 rows)
    9. test=# explain analyze select * from t1 order by name desc limit 1;
    10. QUERY PLAN
    11. --------------------------------------------------------------------------------------------------------------------------------------------
    12. Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.040..0.040 rows=1 loops=1)
    13. -> 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)
    14. Planning Time: 0.108 ms
    15. Execution Time: 0.056 ms
    16. (4 rows)

    2、指定null 规则

    由于null 是最大的,降序排序时,如果指定 null first ,则可以正常使用索引。

    1. test=# explain analyze select * from t1 order by name desc nulls first limit 1;
    2. QUERY PLAN
    3. --------------------------------------------------------------------------------------------------------------------------------------------
    4. Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.019..0.020 rows=1 loops=1)
    5. -> 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)
    6. Planning Time: 0.162 ms
    7. Execution Time: 0.034 ms
    8. (4 rows)
    9. test=# explain analyze select * from t1 order by name desc nulls last limit 1;
    10. QUERY PLAN
    11. -----------------------------------------------------------------------------------------------------------------------------
    12. Limit (cost=204347.03..204347.03 rows=1 width=11) (actual time=2250.512..2250.513 rows=1 loops=1)
    13. -> Sort (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2250.511..2250.512 rows=1 loops=1)
    14. Sort Key: name DESC NULLS LAST
    15. Sort Method: top-N heapsort Memory: 25kB
    16. -> Seq Scan on t1 (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..705.418 rows=9999991 loops=1)
    17. Planning Time: 0.067 ms
    18. Execution Time: 2250.533 ms
    19. (7 rows)

    如果使用升序排序,则指定nulls last 时,可以使用索引

    1. test=# explain analyze select * from t1 order by name asc nulls first limit 1;
    2. QUERY PLAN
    3. -----------------------------------------------------------------------------------------------------------------------------
    4. Limit (cost=204347.03..204347.03 rows=1 width=11) (actual time=2218.089..2218.090 rows=1 loops=1)
    5. -> Sort (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2218.088..2218.089 rows=1 loops=1)
    6. Sort Key: name NULLS FIRST
    7. Sort Method: top-N heapsort Memory: 25kB
    8. -> Seq Scan on t1 (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..706.852 rows=9999991 loops=1)
    9. Planning Time: 0.068 ms
    10. Execution Time: 2218.113 ms
    11. (7 rows)
    12. test=# explain analyze select * from t1 order by name asc nulls last limit 1;
    13. QUERY PLAN
    14. -----------------------------------------------------------------------------------------------------------------------------------
    15. Limit (cost=0.43..0.47 rows=1 width=11) (actual time=0.020..0.020 rows=1 loops=1)
    16. -> 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)
    17. Planning Time: 0.076 ms
    18. Execution Time: 0.035 ms
    19. (4 rows)

    哪种情况下能使用索引,还与创建索引的索引是升序还是降序有关。

    三、创建索引时可以指定nulls first/last 选项

    默认降序索引时,null 是first 的。 因此,如果建索引时没有指定 nulls last ,则SQL 不能使用索引。

    1. test=# create index ind_t1_name on t1(name desc nulls last);
    2. CREATE INDEX
    3. test=# explain analyze select * from t1 order by name desc nulls last limit 10;
    4. QUERY PLAN
    5. -----------------------------------------------------------------------------------------------------------------------------------
    6. Limit (cost=0.43..0.80 rows=10 width=11) (actual time=0.014..0.017 rows=10 loops=1)
    7. -> 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)
    8. Planning Time: 0.161 ms
    9. Execution Time: 0.028 ms
    10. (4 rows)
  • 相关阅读:
    java计算机毕业设计至臻阁古董拍卖网源码+数据库+系统+部署+lw文档
    python多进程(一)Fork模式和Spawn模式的优缺点
    RabbitMQ快速上手及讲解
    使用JMX监控ZooKeeper和Kafka
    LCD12864 (Sitronix ST7920) 4-Bits Mode 初始失败
    Playwright官方文档要点记录(java)
    2024年需要的变化
    C++ ASIO 实现异步套接字管理
    公共数据 | CnOpenData中国90座城市建筑物屋顶矢量数据集
    3.5 Keepalived双主热备
  • 原文地址:https://blog.csdn.net/lyu1026/article/details/126286678