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

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 通过优化Doris表结构（Partitions & bucket数量）提升Doris查询性能 -> 正文阅读

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

背景：

依赖 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)