性能优化：Doris-亿级数据秒出结果

背景

我司有个广告竞价系统，地区数据报表的维度非常多，导致数据量急剧膨胀，达到亿级别，使用 Mysql 数据库，查询非常卡顿，急需优化，使用 Doris 数据库代替 Mysql 存储和查询，秒出结果，另外可以借助 Doirs 的分区功能，自动保留 6 个月的数据。

Doris是什么？

Apache Doris 是一个基于 MPP 架构的高性能、实时的分析型数据库，以极速易用的特点被人们所熟知，仅需亚秒级响应时间即可返回海量数据下的查询结果，不仅可以支持高并发的点查询场景，也能支持高吞吐的复杂分析场景。基于此，Apache Doris 能够较好的满足报表分析、即席查询、统一数仓构建、数据湖联邦查询加速等使用场景，用户可以在此之上构建用户行为分析、AB 实验平台、日志检索分析、用户画像分析、订单分析等应用。

发展历程：诞生于百度广告报表业务的 Palo 项目 → 2017 年正式对外开源 → 2018 年 7 月由百度捐赠给 Apache 基金会进行孵化 → 2022 年 6 月，Apache Doris 成功从 Apache 孵化器毕业，正式成为 Apache 顶级项目

使用场景：

报表分析
即席查询
统一数仓构建

二、Doris三种数据模型

Doris 数据模型上目前分为三类: AGGREGATE KEY, UNIQUE KEY, DUPLICATE KEY。三种模型中数据都是按KEY进行排序。

1、AGGREGATE KEY

AGGREGATE KEY相同时，新旧记录进行聚合，目前支持的聚合函数有SUM, MIN, MAX, REPLACE。

AGGREGATE KEY模型可以提前聚合数据, 适合报表和多维分析业务。

CREATE DATABASE IF NOT EXISTS example_db;

CREATE TABLE IF NOT EXISTS example_db.example_tbl_agg1
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

2、UNIQUE KEY

UNIQUE KEY 相同时，新记录覆盖旧记录。在1.2版本之前，UNIQUE KEY 实现上和 AGGREGATE KEY 的 REPLACE 聚合方法一样，二者本质上相同，自1.2版本我们给UNIQUE KEY引入了merge on write实现，该实现有更好的聚合查询性能。适用于有更新需求的分析业务。

CREATE TABLE IF NOT EXISTS example_db.example_tbl_unique
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `username` VARCHAR(50) NOT NULL COMMENT "用户昵称",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `phone` LARGEINT COMMENT "用户电话",
    `address` VARCHAR(500) COMMENT "用户地址",
    `register_time` DATETIME COMMENT "用户注册时间"
)
UNIQUE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

3、DUPLICATE KEY

只指定排序列，相同的行不会合并。适用于数据无需提前聚合的分析业务。

CREATE TABLE IF NOT EXISTS example_db.example_tbl_duplicate
(
    `timestamp` DATETIME NOT NULL COMMENT "日志时间",
    `type` INT NOT NULL COMMENT "日志类型",
    `error_code` INT COMMENT "错误码",
    `error_msg` VARCHAR(1024) COMMENT "错误详细信息",
    `op_id` BIGINT COMMENT "负责人id",
    `op_time` DATETIME COMMENT "处理时间"
)
DUPLICATE KEY(`timestamp`, `type`, `error_code`)
DISTRIBUTED BY HASH(`type`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

三、数据模型的选择建议

Aggregate 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。
Unique 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。
Duplicate 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）。

四、Doris分区

1、动态分区

动态分区是在 Doris 0.12 版本中引入的新功能。旨在对表级别的分区实现生命周期管理(TTL)，减少用户的使用负担。

动态分区只支持 Range 分区。

CREATE TABLE tbl1
(...)
PROPERTIES
(
    "dynamic_partition.prop1" = "value1",
    "dynamic_partition.prop2" = "value2",
    ...
)

dynamic_partition.enable true or false,默认为TRUE``
dynamic_partition.time_unit（必选参数）动态分区调度的单位。可指定为 HOUR、DAY、WEEK、MONTH、YEAR。分别表示按小时、按天、按星期、按月、按年进行分区创建或删除。
dynamic_partition.time_zone 动态分区的时区，如果不填写，则默认为当前机器的系统的时区
dynamic_partition.start 动态分区的起始偏移，为负数。根据 time_unit 属性的不同，以当天（星期/月）为基准，分区范围在此偏移之前的分区将会被删除
dynamic_partition.end（必选参数）动态分区的结束偏移，为正数。根据 time_unit 属性的不同，以当天（星期/月）为基准，提前创建对应范围的分区。
dynamic_partition.prefix（必选参数）动态创建的分区名前缀。

举例说明：
表 tbl1 分区列 k1 类型为 DATE，创建一个动态分区规则。按月分区，不删除历史分区，并且预先创建未来2个月的分区。同时设定以每月3号为起始日。

CREATE TABLE tbl1
(
    k1 DATE,
    ...
)
PARTITION BY RANGE(k1) ()
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end" = "2",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "8",
    "dynamic_partition.start_day_of_month" = "3"
);

假设当前日期为 2020-05-29。则根于以上规则，tbl1 会产生以下分区：

p202005: ["2020-05-03", "2020-06-03")
p202006: ["2020-06-03", "2020-07-03")
p202007: ["2020-07-03", "2020-08-03")

2、静态分区

见官方文档，链接：doris.apache.org/zh-CN/docs/advanc...

五、Doris简单应用

talk is cheap,show me code，以猜你地区报表为例，目前表的数据上亿，查询非常慢，急需优化

创建表：

CREATE TABLE `ad_area_reports_test` (
  `day` date NULL COMMENT '日期',
  `hour` char(2) NOT NULL COMMENT '小时',
  `province` varchar(50) NOT NULL COMMENT '省',
  `city` varchar(50) NOT NULL COMMENT '市',
  `user_id` bigint(20) NULL COMMENT '账户ID',
  `ad_id` bigint(20) NULL COMMENT '广告ID',
  `ad_group_id` bigint(20) NULL COMMENT '广告组ID',
  `ad_creative_id` bigint(20) NULL COMMENT '创意ID',
  `ad_form` tinyint(4) NULL,
  `view` bigint(20) SUM NULL DEFAULT "0" COMMENT '展现',
  `click` bigint(20) SUM NULL DEFAULT "0" COMMENT '点击',
  `cost` bigint(20) SUM NULL DEFAULT "0" COMMENT '消费金额'
) ENGINE=OLAP
AGGREGATE KEY(`day`, `hour`, `province`, `city`, `user_id`, `ad_id`, `ad_group_id`, `ad_creative_id`, `ad_form`)
COMMENT 'OLAP'
PARTITION BY RANGE(`day`)()
DISTRIBUTED BY HASH(`day`) BUCKETS 10
PROPERTIES (
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-6",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p_",
"dynamic_partition.buckets" = "8",
"dynamic_partition.start_day_of_month" = "18",
);