效能最佳化：Doris-億級資料秒出結果

yefy發表於2024-02-19

背景

我司有個廣告競價系統，地區資料包表的維度非常多，導致資料量急劇膨脹，達到億級別，使用 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"
);

三、資料模型的選擇建議

  1. Aggregate 模型可以透過預聚合，極大地降低聚合查詢時所需掃描的資料量和查詢的計算量，非常適合有固定模式的報表類查詢場景。
  2. Unique 模型針對需要唯一主鍵約束的場景，可以保證主鍵唯一性約束。
  3. 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",
);

六、踩過的坑

  1. 需要提前建立分割槽，不然資料無法寫入
  2. 欄位型別要一致，不然資料無法寫入，hive 表中 cost 欄位為 string，而 doris 資料庫地區表中 cost 欄位為int型別，導致資料無法寫入，需要使用 cast 欄位轉化
本作品採用《CC 協議》，轉載必須註明作者和本文連結
yefy

相關文章