AI 助理
文档备案控制台
官方文档
首页

排序优化

更新时间：
复制 MD 格式
我的收藏

AnalyticDB PostgreSQL版支持通过组合排序（适用于：查询SQL的等值条件或范围条件包含几个固定列）或多维排序（适用于：查询SQL包含的过滤条件不是固定的列）加速查询。

背景信息

您在创建表时，可以定义一个或多个列为排序键，当有数据写入到表中，可以对该表按照排序键进行排序重组。

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

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

AnalyticDB PostgreSQL版支持两种排序方式：

  • 组合排序（SORT）：适用于过滤条件是排序键的前缀子集，例如查询过滤条件包含首列排序键的场景。

  • 多维排序（Multisort）：给每一个排序键分配相同的权重，更适合于查询条件包含任意过滤条件子集的场景。

如何选择排序键

  • 当您的查询SQL的等值条件或范围条件经常包含几个固定列，可以考虑将这些列作为排序键，从而利用数据排序结合粗糙索引，加速这类SQL的查询速度。一般情况下应该考虑使用组合排序。

  • 当您的查询SQL包含的过滤条件不是固定的列，可以使用多维排序来加速查询。由于多维排序在排序过程中还需要进行一些额外的数据组织工作，所以一般情况下多维排序的耗时会比组合排序的耗时更久。

    说明

    多维排序最多支持8列。

  • 当您的查询SQL经常使用固定的列作为JOIN条件，可以将JOIN列同时设置为分布键和排序键，从而使用MergeJoin代替HashJoin，由于底层数据已经按照JOIN列排序，可以跳过MergeJoin耗时较高的排序阶段。

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

以下内容将对同样的两张表分别进行组合排序和多维排序，比较两种排序方式在不同场景下对不同查询的性能影响。

  1. 创建两张测试表并设置表的排序键，表中包含4列，分别为id、num1、num2、value，其中id、num1、num2为排序键。语句如下：

    CREATE TABLE test(id int, num1 int, num2 int, value varchar) 
with(APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY(id)
ORDER BY(id, num1, num2);
CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar) 
with(APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY(id)
ORDER BY(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', 'child', 'phone'])[floor(random() * 10 +1)]
FROM generate_series(1, 10000000) as g;
INSERT INTO test_multi SELECT * FROM test;

    查询两张表中数据的总行数：

    • SELECT count(*) FROM test;

      返回信息如下：

        count
----------
 10000000
(1 row)
    • SELECT count(*) FROM test_multi;

      返回信息如下：

        count
----------
 10000000
(1 row)

  3. 对两张表分别进行组合排序和多维排序。

    对test表进行组合排序：

    SORT test;

    对test_multi表进行多维排序：

    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、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. 写入一百万行数据，语句如下：

    INSERT INTO far VALUES(generate_series(0, 1000000), 1);

  3. 数据导入完成后，对数据进行排序，语句如下：

    SORT far;

查询性能对比如下：

说明

当前示例的查询时间仅供参考。查询时间受到数据量、计算资源、网络状况等多个因素影响，请以实际为准。

  • ORDER BY加速

    • 排序加速前（未排序）排序加速前，执行 ORDER BY 查询耗时 323.980 ms：

      postgres=# select * from far order by a limit 1;
 a | b
---+---
 0 | 1
(1 row)
Time: 323.980 ms

    • 排序加速后，查询性能得到提升。

      postgres=# select * from far order by a limit 1;
 a | b
----+----
 0 | 1
(1 row)
Time: 6.971 ms

  • GROUP BY加速

    • 排序加速前（未排序）

      postgres=# select a,count(*) from far  group by a limit 1;
    a    | count
---------+-------
 579229 |     1
(1 row)
Time: 779.368 ms

    • 完成排序加速。

      postgres=# select a, count(*) from far group by a limit 1;
 a | count
---+-------
 0 |     1
(1 row)
Time: 6.859 ms

  • JOIN加速

    • 排序加速前（未排序）

      postgres=# select * from far t1, far t2 where t1.a = t2.a limit 1;
 a | b | a | b
---+---+---+---
 2 | 1 | 2 | 1
(1 row)
Time: 289.075 ms

    • 排序加速后

      说明

      JOIN排序加速需要关闭ORCA功能，打开mergejoin功能，语句如下：

      SET enable_mergejoin TO on;
SET optimizer TO off;

      排序加速后执行JOIN查询，执行时间为12.315毫秒。

      postgres=# select * from far t1, far t2 where t1.a = t2.a limit 1;
 a | b | a | b
---+---+---+---
 2 | 1 | 2 | 1
(1 row)
Time: 12.315 ms

-

ORDER BY

GROUP BY

JOIN

加速前

323.980 ms

779.368 ms

289.075 ms

加速后

6.971 ms

6.859 ms

12.315 ms
上一篇：排序优化（6.0版）下一篇：列存表使用排序键和粗糙集索引加速查询