通过优化Doris表结构（Partitions & bucket数量）提升Doris查询性能

通过调整Doris建表结构提升Doris查询性能

只是喜欢玩大数据

3869人浏览 · 2022-06-09 17:19:26

只是喜欢玩大数据 · 2022-06-09 17:19:26 发布

背景：

依赖 Apache Doris 构建了一套企业级的数据中台，数据中台包含一个API服务，公司所有业务组是不能直连Doris数据仓库做查询的，必须统一调用我们的API服务，最近有业务组反馈，调用API服务时，会出现查询超时的情况，因此需要对部分API服务接口进行优化。这个优化包含两部分：

1.SQL优化（SQL中join的顺序，SQL函数的使用，正常使用谓词避免全表扫描等）；

2.Doris数据仓库表结构的优化，包括分区（Partition），分桶（Bucket），Key列的顺序等。

那么SQL的优化主要交给数据分析来做，对业务了解，修改SQL比较容易上手，而我主要做的就是Doris表结构的优化。

优化案例：

注意：每一个API服务都包含了复杂的逻辑，我为了测试单表的性能，所有的校验SQL都是单表查询SQL;

案例1：

1.根据`show data from db.tbl_name`查看基础信息。

可以看到该表的数据量为17.44G，副本数据量495个，数据条数为309546433条。具体如下：

+-----------------------------------+-----------+--------------+-----------+
| TableName                         | Size      | ReplicaCount | RowCount  |
+-----------------------------------+-----------+--------------+-----------+
| ods_***_***_***_iostock_delta     | 17.440 GB | 495          | 309546433 |
|                                   | 17.440 GB | 495          |           |
+-----------------------------------+-----------+--------------+-----------+

2.使用查询SQL看查询时长：

SELECT date_format(bu,'%Y-%m-%d') as bu, lg, mb, mb, we, eb, ebe, au, rs, rs, kd, kdp, bw, me, mei, matn, bukr, wae, dmbt, shzg, btme, bmg, bsus_sg, zje, temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, temp9, temp10, temp11, temp12, temp13, temp14, temp15, rq FROM pro_dw.ods_***_***_***_iostock_delta WHERE budat >= '2022-05-01' AND budat <= '2022-06-01' AND split_part ( temp4, '/', 1 ) IN ( '222325586563455345' , '2205142804242347223' , '224423052342011793' ) LIMIT 5000

+------------+------------+-------+-------+-------+------------+---------+-------+-------+---------+-------+-------+------------+-----------------------------------+------
---+-------+--------+-------+--------+-------+-------+-------+-------+---------------------+6 rows in set (2.814 sec)

可以看到，一个3亿条左右的数据集，单表查询结果基本在2.8秒左右，但查询SQL并没有过于复杂，没有包含聚合和分组等条件，所以是有优化空间的。

3.查看建表语句：

CREATE TABLE `ods_***_***_***_iostock_delta` (
   ...
  `temp1` varchar(100) NULL COMMENT "预留1",
  `temp2` varchar(100) NULL COMMENT "预留2",
  `temp3` varchar(100) NULL COMMENT "预留3",
  `temp4` varchar(100) NULL COMMENT "预留4",
  `temp5` varchar(100) NULL COMMENT "预留5",
  `temp6` varchar(100) NULL COMMENT "预留6",
  `temp7` varchar(100) NULL COMMENT "预留7",
  `temp8` varchar(100) NULL COMMENT "预留8",
  `temp9` varchar(100) NULL COMMENT "预留9",
  `temp10` varchar(100) NULL COMMENT "预留10",
  `temp11` varchar(100) NULL COMMENT "预留11",
  `temp12` varchar(100) NULL COMMENT "预留12",
  `temp13` varchar(100) NULL COMMENT "预留13",
  `temp14` varchar(100) NULL COMMENT "预留14",
  `temp15` varchar(100) NULL COMMENT "预留15",
) ENGINE=OLAP
UNIQUE KEY(`bu`, `lg`, `mb`, `mbl`, `we`)
COMMENT "**********"
PARTITION BY RANGE(`bu`)
(
PARTITION P_000000 VALUES [('0000-01-01'), ('2020-01-01')),
PARTITION P_202001 VALUES [('2020-01-01'), ('2020-02-01')),
PARTITION P_202002 VALUES [('2020-02-01'), ('2020-03-01')),
PARTITION P_202003 VALUES [('2020-03-01'), ('2020-04-01')),
PARTITION P_202004 VALUES [('2020-04-01'), ('2020-05-01')),
PARTITION P_202005 VALUES [('2020-05-01'), ('2020-06-01')),
PARTITION P_202006 VALUES [('2020-06-01'), ('2020-07-01')),
PARTITION P_202007 VALUES [('2020-07-01'), ('2020-08-01')),
PARTITION P_202008 VALUES [('2020-08-01'), ('2020-09-01')),
PARTITION P_202009 VALUES [('2020-09-01'), ('2020-10-01')),
PARTITION P_202010 VALUES [('2020-10-01'), ('2020-11-01')),
PARTITION P_202011 VALUES [('2020-11-01'), ('2020-12-01')),
PARTITION P_202012 VALUES [('2020-12-01'), ('2021-01-01')),
PARTITION P_202101 VALUES [('2021-01-01'), ('2021-02-01')),
PARTITION P_202102 VALUES [('2021-02-01'), ('2021-03-01')),
PARTITION P_202103 VALUES [('2021-03-01'), ('2021-04-01')),
PARTITION P_202104 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION P_202105 VALUES [('2021-05-01'), ('2021-06-01')),
PARTITION P_202106 VALUES [('2021-06-01'), ('2021-07-01')),
PARTITION P_202107 VALUES [('2021-07-01'), ('2021-08-01')),
PARTITION P_202108 VALUES [('2021-08-01'), ('2021-09-01')),
PARTITION P_202109 VALUES [('2021-09-01'), ('2021-10-01')),
PARTITION P_202110 VALUES [('2021-10-01'), ('2021-11-01')),
PARTITION P_202111 VALUES [('2021-11-01'), ('2021-12-01')),
PARTITION P_202112 VALUES [('2021-12-01'), ('2022-01-01')),
PARTITION P_202201 VALUES [('2022-01-01'), ('2022-02-01')),
PARTITION P_202202 VALUES [('2022-02-01'), ('2022-03-01')),
PARTITION P_202203 VALUES [('2022-03-01'), ('2022-04-01')),
PARTITION P_202204 VALUES [('2022-04-01'), ('2022-05-01')),
PARTITION P_202205 VALUES [('2022-05-01'), ('2022-06-01')),
PARTITION P_202206 VALUES [('2022-06-01'), ('2022-07-01')),
PARTITION P_202207 VALUES [('2022-07-01'), ('2022-08-01')),
PARTITION P_202208 VALUES [('2022-08-01'), ('2022-09-01')))
DISTRIBUTED BY HASH(`we`) BUCKETS 5
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "P_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "5",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

从这个建表语句可以看出：这个表是按月做的分区，分区数量为33个，表的分桶数量为5，副本数为3，所以这个表的tablet数量为33*5=165个。

4.判断建表语句是否合理：

Partition是否合理：

根据与业务沟通，得知这个接口基本是按月来查询的，所以表中数据按月做分区是合理的；

bucket是否合理：

因为我们都知道，分桶的数量和查询并发量是有关系的。如果分桶数量太小，即使后期再增加机器，也无法增加并行度，换句话说就是，如果分桶数过小，查询效率也上不去。因此需要来判断分桶数的数量了。

分桶数据会增加查询效率，那是不是分桶数量越大，查询效率越高呢？当然不是，因为如果分桶数量增大，对应的表的tablet数据就会增加，那么单表的元数据就会增加。当每个表个bucket都很大时，集群FE节点维护的元数据信息就会成倍增加，那么当一个查询请求触发时，所需要校验的元数据信息也越多，查询效率自然就会降低。

5.修改分桶数量，测试查询效率

修改bucket数量为10，同样的SQL语句查询时长如下：

+------------+------------+-------+-------+-------+------------+---------+-------+-------+---------+-------+-------+------------+-----------------------------------+------
---+-------+--------+-------+--------+-------+-------+-------+-------+---------------------+6 rows in set (2.269 sec)

修改bucket数量为20，同样的SQL语句查询时长如下：

+------------+------------+-------+-------+-------+------------+---------+-------+-------+---------+-------+-------+------------+-----------------------------------+------
---+-------+--------+-------+--------+-------+-------+-------+-------+---------------------+6 rows in set (1.588 sec)

修改bucket数量为30，同样的SQL语句查询时长如下：

+------------+------------+-------+-------+-------+------------+---------+-------+-------+---------+-------+-------+------------+-----------------------------------+------
---+-------+--------+-------+--------+-------+-------+-------+-------+---------------------+6 rows in set (1.545 sec)

从测试结果可以看出，当分桶数量从5个增加到20个时，查询效率基本提升了1倍（2.814s- >1.588s），但当分桶数量bucket增加到30个时，与bucket为20的查询时长做对比，并没有发生较大的差异(1.588s - > 1.545s)。

如果再增加10个bucket,那么表的分片数量（包含副本数量）会多出33x10x3=990个。这个时候就要考虑多990个分片，查询效率提升400ms有没有价值，这个具体就因人而异了。我最后是选择了bucket数量为20，重新建表导入数据。

案例2：

这个案例主要是通过count(1)来对比查询性能。

1.根据show data from db.tbl_name查看基础信息。

+-----------------------------------+----------+--------------+-----------+
| TableName                         | Size     | ReplicaCount | RowCount  |
+-----------------------------------+----------+--------------+-----------+
| ods_***_***_***_purrecode_delta   | 7.145 GB | 315          | 153756724 |
|                                   | 7.145 GB | 315          |           |
+-----------------------------------+----------+--------------+-----------+

2.使用查询SQL看查询时长：

mysql> SELECT count(1) FROM ods_***_***_***_purrecode_delta;
+-----------+
| count(1)  |
+-----------+
| 153756724 |
+-----------+
1 row in set (32.25 sec)
mysql>

通过这个count() SQL看出统计时长为32.25s，为什么一个统计SQL,会耗时这么久呢？通过建表语句我们发现，这个表也是按照MONTH做的分区Partition，但bucket的数量设置的是1。这个表的数据有1.5亿条，虽然分区合理，但只有一个bucket，这个明显是不合理的。Doris中，Partition是一级分区，Bucket是二级分区，bucket过少会导致Doris表的tablet数量较少，每个tablet数据量变大。在查询时，SQL会被解析成语法树和具体的执行计划，这些执行计划又会被划分成一个个的Fragment,FE节点会将所有的Fragment具体下发到每一个BE节点执行，然后通过ExchangeNode节点做数据的汇总和交换。所以如果bucket的数量为1时，这些Fragment只能被一个线程挨个执行，但当bucket的数量大于1时，这些Fragment会并发执行，因此查询效率会显著提升。

因此，我将bucket设置为10时，同样的SQL,执行时长如下：

mysql> SELECT count(1) FROM ods_***_***_***_purrecode_delta_tmp_0601;
+-----------+
| count(1)  |
+-----------+
| 153756724 |
+-----------+
1 row in set (6.21 sec)