背景信息

当您创建表的时候,可以定义一个或者多个列为排序键(SORTKEY)。数据写入到表中之后,您可以对该表按照排序键进行排序重组。

表排序后可以加速范围限定查询,数据库会对每固定行记录每一列的min、max值。如果在查询时使用范围限定条件,AnalyticDB PostgreSQL的查询引擎可以根据min、max值在对表进行扫描(SCAN)时快速跳过不满足限定条件的数据块(Block)。

例如,假设一张表存储了7年的数据,并且这张表的数据是按照时间字段排序存储的,如果需要查询一个月的数据,那么只需要扫描 1/(7*12) 的数据,也就是说有98.8%的数据块在扫描(SCAN)时可以被过滤掉。但是如果数据没有按照时间排序的话,可能所有的磁盘上的数据块都要被扫描到。

AnalyticDB PostgreSQL支持两种排序方式:

  • 组合排序:适用于限定条件是查询的前缀子集或者完全包含排序键,更适合于查询包含首列限定条件的情况。
  • 多维排序:给每一个排序键分配相同的权重,更适合于查询条件包含任意限定条件子集的场景。

如何选择排序键

当您的查询SQL经常包含某一个列或者某几个列的等值或者范围限定条件查询时,比如时间列等,可以考虑使用这些列作为排序键,从而利用数据排序并结合粗糙索引,加速这类SQL的查询速度。在一般情况下您都应该考虑使用组合排序。

如果您的查询SQL包含的限定条件经常不是总是包含某些列的,可以使用多维排序来加速查询。多维排序最多支持8列。需要注意的是,在一般情况下多维排序MULTISORT table的时间会长于组合排序SORT table的时间。这是因为多维排序在排序过程中还需要做一些额外的数据组织工作。

如果您经常使用固定的列作为JOIN条件,可以将JOIN列同时设置为分布键和排序键,从而使用更快的MergeJoin来代替HashJoin,由于底层数据已经按照JOIN列有序,可以跳过MergeJoin耗时较高的排序阶段。

组合排序和多维排序的性能对比

以下内容会对同一张表分别做组合排序和多维排序,从而比较两种排序方式在不同的场景下,对不同查询的性能影响。在这个场景中,将创建一张表test,其包含4列,分别为id、num1、num2、value。其中id、num1、num2为排序键。这张表一共包含一千万条记录。对于ADBPG来说并不算是一张特别大的表,但是其可以显示出组合排序和多维排序的性能差异,在更大的数据集中,两者的性能差异也会更大。

  1. 创建测试表并设置表的排序键
    CREATE TABLE test(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    SORTKEY(id, num1, num2);
    
    CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar) 
    with(APPENDONLY=TRUE, ORIENTATION=column)
    DISTRIBUTED BY(id)
    SORTKEY(id, num1, num2);
  2. 写入测试数据
    INSERT INTO test(id, num1, num2, value) select g,
    (random()*10000000)::int,
    (random()*10000000)::int,
    (array['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'chlid', 'phone'])[floor(random() * 10 +1)]
    FROM generate_series(1, 10000000) as g;
    
    INSERT INTO test_multi SELECT * FROM test;
    
    adbpgadmin=# SELECT count(*) FROM test;
      count
    ----------
     10000000
    (1 row)
    
    adbpgadmin=# SELECT count(*) FROM test_multi;
      count
    ----------
     10000000
    (1 row)
  3. 对两张表分别进行组合排序和多维排序
    SORT test;
    MULTISORT test_multi;
  4. 点查询性能对比
    • 包含首列排序键限定条件
      -- Q1 包含首列限定条件
      select * from test where id = 100000;
      select * from test_multi where id = 100000;
    • 包含第二列限定条件
      -- Q2 包含第二列限定条件
      select * from test where num1 = 8766963;
      select * from test_multi where num1 = 8766963;
    • 包含二三列限定条件
      -- Q3 包含二三列限定条件
      select * from test where num1 = 100000 and num2=2904114;
      select * from test_multi where num1 = 100000 and num2=2904114;

    性能对比结果:

    排序模式 Q1 Q2 Q3
    组合排序 0.026s 3.95s 4.21s
    多维排序 0.55s 0.42s 0.071s
  5. 范围查询性能对比
    • 包含首列排序键限定条件
      -- Q1 包含首列限定条件
      select count(*) from test where id>5000 and id < 100000;
      select count(*) from test_multi where id>5000 and id < 100000;
    • 包含第二列限定条件
      -- Q2 包含第二列限定条件
      select count(*) from test where num1 >5000 and num1 <100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000;
    • 包含二三列限定条件
      -- Q3 包含二三列限定条件
      select count(*) from test where num1 >5000 and num1 <100000; and num2 < 100000;
      select count(*) from test_multi where num1 >5000 and num1 <100000 and num2 < 100000;

    性能对比结果:

    排序方式 Q1 Q2 Q3
    组合排序 0.07s 3.35s 3.64s
    多维排序 0.44s 0.28s 0.047s

结论如下:

  • 对于Q1场景,由于包含排序键的首列,所以组合排序的效果非常好,而多维排序则会相对性能弱一些。
  • 对于Q2场景,由于不包含排序键的首列,组合排序基本上失效了,而多维排序依然能维持比较稳定的性能提升。
  • 对于Q3场景,由于不包含排序键的首列,组合排序依然起不到很好的效果,并且由于比较条件的增加,需要额外的比较开销,时间更长,而多维排序表现出更好的性能,这是因为在查询时,限定条件包含的多维排序键越多,性能越好。

排序加速计算

当您执行SORT <tablename>后,系统会对表数据进行排序,当数据完成排序后,AnalyticDB PostgreSQL版即可利用数据的物理顺序,将SORT算子下推到存储层进行计算加速。如果您的SQL可以利用底层的数据顺序,则会从中获得加速收益,该特性可以基于SORT KEY加速SORT、AGG、JOIN算子。

说明
  • 排序加速计算功能需要数据完全有序,当您写入数据后需要重新执行SORT <tablename>对数据进行排序。
  • 排序加速计算功能默认开启。

以下示例将在测试表far中执行同样的查询语句,对比排序加速前与排序加速后查询时间的差距。

  1. 创建测试表far,语句如下:
    CREATE TABLE far(a int,  b int)
    WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
    DISTRIBUTED BY (a)  --分布键
    ORDER BY (a);       --排序键
  2. 写入1000000行数据,语句如下:
    INSERT INTO far VALUES(generate_series(0, 1000000), 1);
  3. 数据导入完成后,对数据进行排序,语句如下:
    SORT far;

查询性能对比如下:

说明 当前示例的查询时间仅供参考。查询时间受到数据量、计算资源、网络状况等多个因素影响,请以实际为准。
  • ORDER BY加速
    • 排序加速前(未排序)order by加速前
    • 排序加速后order by加速后
  • GROUP BY加速
    • 排序加速前(未排序)group by加速前
    • 排序加速后group by加速后
  • JOIN加速
    • 排序加速前(未排序)JOIN加速前
    • 排序加速后
      说明 JOIN排序加速需要关闭ORCA功能,打开mergejoin功能,语句如下:
      SET enable_mergejoin TO on;
      SET optimizer TO off;
      JOIN加速后
- ORDER BY GROUP BY JOIN
加速前 323.980 ms 779.368 ms 289.075 ms
加速后 6.971 ms 6.859 ms 12.315 ms