1、背景

貨品活動(dòng)運(yùn)營在圈選所需貨品清單時(shí)，需要操作自助取數(shù)、智能運(yùn)營系統(tǒng)、數(shù)據(jù)報(bào)表等多個(gè)系統(tǒng)工具才能完成。需要一個(gè)以供給側(cè)盤貨為核心需求的盤貨工具，實(shí)現(xiàn)運(yùn)營各場景盤貨及貨品分析訴求，提升運(yùn)營效率。但是實(shí)現(xiàn)起來由以下幾個(gè)難點(diǎn)。

• 指標(biāo)復(fù)雜。除了常用的聚合函數(shù)SUM,MIN,MAX等，還有各種占比、期末 、水平等。
• 查詢條件靈活。幾乎每一列數(shù)據(jù)都可以作為查詢條件進(jìn)行篩選。常規(guī)的數(shù)據(jù)庫無法承擔(dān)此類查詢。
• 數(shù)據(jù)量大。總計(jì)幾十億的數(shù)據(jù)量。

業(yè)內(nèi)比較流行的OLAP數(shù)據(jù)庫主要有ClickHouse和StarRocks。ClickHouse使用成本較高，非標(biāo)準(zhǔn)SQL協(xié)議，對JOIN支持不好，對靈活的業(yè)務(wù)開發(fā)并不友好。StarRocks支持標(biāo)準(zhǔn)的SQL協(xié)議，且對JOIN支持較好，MPP+向量化的查詢引擎，性能也得到保障。并且在與其他數(shù)據(jù)庫的性能測試對比中，StarRocks表現(xiàn)也十分亮眼。

上圖測試對比結(jié)果來自于：https://benchmark.clickhouse.com/

2、模型選擇

StarRocks 支持四種數(shù)據(jù)模型，分別是明細(xì)模型、聚合模型、更新模型和主鍵模型 。這四種數(shù)據(jù)模型能夠支持多種數(shù)據(jù)分析場景，例如日志分析、數(shù)據(jù)匯總分析、實(shí)時(shí)分析等。

盤貨底層的數(shù)據(jù)按照多維度存儲，多個(gè)維度之間可以通過join?來互相關(guān)聯(lián)，并且最終聚合計(jì)算結(jié)果會按照spu的維度在前臺展示。因?yàn)閿?shù)據(jù)需要保存明細(xì)，以滿足豐富多變的查詢條件組合，所以首先排除了聚合模型?。而在實(shí)時(shí)和頻繁更新的場景下，主鍵模型?相較于更新模型?能夠帶來更加高效的查詢體驗(yàn)。所以在數(shù)據(jù)模型選擇上，對于數(shù)據(jù)量較大的T+1維度表我們選擇了明細(xì)模型+物化視圖?的方式（因?yàn)?.5版本前的主鍵模型?不支持物化視圖的自動(dòng)查詢改寫，需要在SQL中指定異步物化視圖的名稱來查詢），對而于數(shù)據(jù)量較小的基礎(chǔ)數(shù)據(jù)、可更新的(spu信息)場景我們選擇了主鍵模型。

3、數(shù)據(jù)寫入

設(shè)計(jì)完表結(jié)構(gòu)后，我們考慮如何進(jìn)行數(shù)據(jù)的導(dǎo)入。StarRocks支持多種數(shù)據(jù)源的導(dǎo)入，下圖展示了在各種數(shù)據(jù)源場景下，應(yīng)該選擇哪一種導(dǎo)入方式。

！盤貨的指標(biāo)數(shù)據(jù)我們選擇通過DataWorks（DataX）方式進(jìn)行導(dǎo)入。而在導(dǎo)入的過程中經(jīng)常會碰到以下兩個(gè)問題。

3.1   列數(shù)量不匹配

默認(rèn)設(shè)置下，數(shù)據(jù)會被轉(zhuǎn)化為字符串，以 CSV 格式通過 Stream Load 導(dǎo)入至 StarRocks。字符串以 \t? 作為列分隔符，\n 作為行分隔符。

實(shí)際場景，我們的數(shù)據(jù)可能正好包含了\t? 和 \n?，再按照默認(rèn)的規(guī)則進(jìn)行分割，會導(dǎo)致列的數(shù)量不匹配或者數(shù)據(jù)錯(cuò)誤。這個(gè)時(shí)候可以通過在參數(shù) SteamLoad? 請求參數(shù)中添加以下配置，以更改分隔符，StarRocks 支持設(shè)置長度最大不超過 50 個(gè)字節(jié)的 UTF-8 編碼字符串作為列分隔符。盡可能縮短分隔符的長度，如果分割符太長，會變相的導(dǎo)致CSV數(shù)據(jù)包變大，從而導(dǎo)致導(dǎo)入速度變慢。

另外，倘若在以CSV的格式導(dǎo)入時(shí)難以確定合適的分隔符的話，可以考慮使用json格式進(jìn)行數(shù)據(jù)的導(dǎo)入，能夠很好地避免分隔符問題帶來的煩擾。但是json格式相比CSV格式，數(shù)據(jù)集中多出很多譬如“{}"、列名稱等的字符，會導(dǎo)致導(dǎo)入數(shù)據(jù)量不變的情況下，導(dǎo)入數(shù)據(jù)的行數(shù)變少，從而使得導(dǎo)入的速度變慢。

• 錯(cuò)誤現(xiàn)象

• 解決示例

{
  "row_delimiter": "\\\\x02",
  "column_separator": "\\\\x01"
}

3.2   版本號超出限制

單表在導(dǎo)入千萬級別的數(shù)據(jù)后，經(jīng)常會報(bào)“Too many version”，導(dǎo)致同步任務(wù)失敗，原因是導(dǎo)入頻率太快，數(shù)據(jù)沒能及時(shí)合并，從而導(dǎo)致版本數(shù)超過參數(shù)限制的tablet最大版本數(shù)。默認(rèn)支持的最大未合并版本數(shù)為 1000。此時(shí)有兩種方式解決：一是數(shù)據(jù)庫服務(wù)端調(diào)高BE的合并參數(shù)，加快數(shù)據(jù)的合并，但注意此時(shí)會增加數(shù)據(jù)庫CPU、內(nèi)存等資源的消耗；二是數(shù)據(jù)導(dǎo)入端可以通過增大單次導(dǎo)入數(shù)據(jù)量，減少導(dǎo)入的頻率來解決，StarRocks的通過以下3個(gè)參數(shù)來控制導(dǎo)入，但需要將同步任務(wù)轉(zhuǎn)換為代碼模式，并在Writer?節(jié)點(diǎn)的parameter參數(shù)中進(jìn)行添加。

• 錯(cuò)誤現(xiàn)象

• 解決示例

4、性能優(yōu)化

4.1   執(zhí)行計(jì)劃

• Query Plan

通過以下命令查看 Query Plan。

# 查看SQL執(zhí)行計(jì)劃
EXPLAIN sql_statement;
# 查看SQL包含列統(tǒng)計(jì)信息的執(zhí)行計(jì)劃
EXPLAIN COSTS sql_statement;

我們以如下SQL舉例子：

EXPLAIN
select
  a.si, a.tt, c.pv
from
  (
    select
      si, tt
    from
      table_a
    where
      status = 1
  ) a
  inner join (
    select
      si,
      sum(pv) as pv
    from
      table_b
    where
      date = '2023-02-26'
    group by
      spu_id
  ) c on a.si = c.si
where
  c.pv <= 10000
order by
  si
limit 0, 20;

執(zhí)行explain后展示如下：

PLAN FRAGMENT 0
 OUTPUT EXPRS:1: si | 2: tt | 55: sum
  PARTITION: UNPARTITIONED
  RESULT SINK
  8:MERGING-EXCHANGE
     limit: 20


PLAN FRAGMENT 1
 OUTPUT EXPRS:
  PARTITION: RANDOM


  STREAM DATA SINK
    EXCHANGE ID: 08
    UNPARTITIONED


  7:TOP-N
  |  order by: <slot 1> 1: si ASC
  |  offset: 0
  |  limit: 20
  |  
  6:Project
  |  <slot 1> : 1: si
  |  <slot 2> : 2: tt
  |  <slot 55> : 55: sum
  |  
  5:HASH JOIN
  |  join op: INNER JOIN (COLOCATE)
  |  colocate: true
  |  equal join conjunct: 1: si = 36: si
  |  
  |----4:AGGREGATE (update finalize)
  |    |  output: sum(38: pv)
  |    |  group by: 36: si
  |    |  having: 55: sum <= 10000
  |    |  
  |    3:Project
  |    |  <slot 36> : 36: si
  |    |  <slot 38> : 38: pv
  |    |  
  |    2:OlapScanNode
  |       TABLE: table_a
  |       PREAGGREGATION: ON
  |       PREDICATES: 37: date = '2023-02-26'
  |       partitinotallow=1/104
  |       rollup: table_b
  |       tabletRatio=8/8
  |       tabletList=60447373,60447377,60447381,60447385,60447389,60447393,60447397,60447401
  |       cardinality=2957649
  |       avgRowSize=20.0
  |       numNodes=0
  |    
  1:Project
  |  <slot 1> : 1: si
  |  <slot 2> : 2: title
  |  
  0:OlapScanNode
     TABLE: table_a
     PREAGGREGATION: ON
     PREDICATES: 14: status = 1
     partitinotallow=1/1
     rollup: table_a
     tabletRatio=8/8
     tabletList=60628875,60628879,60628883,60628887,60628891,60628895,60628899,60628903
     cardinality=318534
     avgRowSize=43.93492
     numNodes=0

核心指標(biāo)主要有以下幾個(gè)：

• Query Profile

如果想看更為詳細(xì)的執(zhí)行計(jì)劃，需要通過profile的方式獲取。2.5之前的版本需要指定以下參數(shù)（session級別），然后可以在starrocks的控制臺上查看到執(zhí)行計(jì)劃。

set is_report_success = true;

4.2   索引

• 前綴索引

在建表時(shí)，可以指定一個(gè)或多個(gè)列作為排序鍵 。表中的行會根據(jù)排序鍵進(jìn)行排序后再落盤。查詢數(shù)據(jù)時(shí)可以按照二分的方式進(jìn)行掃描，避免了全表掃描。同時(shí)為減少內(nèi)存開銷，StarRocks 在排序鍵的基礎(chǔ)上又引入了前綴索引。前綴索引是一種稀疏索引。表中每 1024 行數(shù)據(jù)構(gòu)成一個(gè)邏輯數(shù)據(jù)塊 (Data Block)。每個(gè)邏輯數(shù)據(jù)塊在前綴索引表中存儲一個(gè)索引項(xiàng)，索引項(xiàng)的長度不超過 36 字節(jié)，其內(nèi)容為數(shù)據(jù)塊中第一行數(shù)據(jù)的排序列組成的前綴，在查找前綴索引表時(shí)可以幫助確定該行數(shù)據(jù)所在邏輯數(shù)據(jù)塊的起始行號。前綴索引的大小會比數(shù)據(jù)量少 1024 倍，因此會全量緩存在內(nèi)存中，在實(shí)際查找的過程中可以有效加速查詢。

比如主鍵模型的建表語句，指定了PRIMARY KEY為spu_id,seller_id,date，當(dāng)查詢條件包含了spu_id、seller_id時(shí)能快速的定位到數(shù)據(jù)，但如果單獨(dú)按照seller_id來查詢，則無法利用到前綴索引（最左匹配原則）。所以在設(shè)計(jì)表結(jié)構(gòu)時(shí)將經(jīng)常作為查詢條件的列，選為排序列。當(dāng)排序鍵涉及多個(gè)列的時(shí)候，建議把區(qū)分度高、且經(jīng)常查詢的列放在前面。

CREATE TABLE table_c(
`si` BIGINT(20) NOT NULL,
`sel` BIGINT(20) NOT NULL,
`date` DATE NOT NULL,
//....
) ENGINE=olap
PRIMARY KEY(`si`,`sel`,`date`)
COMMENT "xxxxxx"
PARTITION BY RANGE(`date`)
(START ("2022-10-11") END ("2023-01-19") EVERY (INTERVAL 1 DAY))
DISTRIBUTED BY HASH(si) BUCKETS 8
PROPERTIES (
//....
);

• bitmap索引

如果想要提高一個(gè)非前綴索引列的查詢效率，可以為這一列創(chuàng)建 Bitmap 索引。比如列基數(shù)較低，值大量重復(fù)，例如 ENUM 類型的列，使用 Bitmap 索引能夠減少查詢的響應(yīng)時(shí)間。

舉個(gè)??，現(xiàn)在對商品信息的商品狀態(tài)和商品類型創(chuàng)建bitmap索引：

CREATE INDEX status_idx ON table_a (status) USING BITMAP COMMENT '商品狀態(tài)索引';
CREATE INDEX type_idx ON table_a (biz_type) USING BITMAP COMMENT '商品類型索引';

1. 構(gòu)建字典：StarRocks 根據(jù) 商品狀態(tài) 列的取值構(gòu)建一個(gè)字典，將 普通商品 和 定制服務(wù) 分別映射為 INT 類型的編碼值：0 和 1。
2. 生成 bitmap：StarRocks 根據(jù)字典的編碼值生成 bitmap。因?yàn)?普通商品 出現(xiàn)在了1,2,3,4,5行，所以 普通商品 的 bitmap 是 111110000；定制服務(wù) 出現(xiàn)在第 6,7,8,9行，所以 定制服務(wù) 的 bitmap 是 000001111。
3. 查詢 定制服務(wù) 的商品：先查詢字典映射，得到字典值1，再去查詢字典值1的bitmap，得出定制服務(wù)在6,7,8,9行。
4. 查詢 定制服務(wù) 且 上架 的商品：類似的道理，會將兩段bitmap值進(jìn)行位運(yùn)算，000001111 & 100100100 得出 000000100，也就是只有第7行滿足條件。

4.3   Colocate Join

！Colocation Join 功能，是將一組擁有相同 Colocation Group Schema（CGS）的 Table 組成一個(gè) Colocation Group（CG）。并保證這些 Table 對應(yīng)的數(shù)據(jù)分片會落在同一個(gè) BE 節(jié)點(diǎn)上。使得當(dāng) Colocation Group 內(nèi)的表進(jìn)行分桶列上的 Join 操作時(shí)，可以通過直接進(jìn)行本地?cái)?shù)據(jù) Join，減少數(shù)據(jù)在節(jié)點(diǎn)間的傳輸耗時(shí)。

同一 CG 內(nèi)的 Table 必須保證以下屬性相同：

• 分桶列和分桶數(shù)相同，DISTRIBUTED BY HASH(k1) BUCKETS 8相同
• 副本數(shù)相同，replication_num相同

建表時(shí)，可以在 PROPERTIES? 中指定屬性 "colocate_with" = "group_name"，表示這個(gè)表是一個(gè) Colocation Join 表，并且歸屬于一個(gè)指定的 Colocation Group。

CREATE TABLE tbl (k1 int, v1 int sum)
DISTRIBUTED BY HASH(k1) BUCKETS 8
PROPERTIES(
   "replication_num" = "3",
   "colocate_with" = "groupName"
);

使用完Colocation Join 的執(zhí)行計(jì)劃，join op后會標(biāo)注走的COLOCATE

4.4   物化視圖

！物化視圖是將預(yù)先計(jì)算好（根據(jù)定義好的 SELECT 語句）的數(shù)據(jù)集，存儲在 StarRocks 中的一個(gè)特殊的表，本質(zhì)上是張聚合模型的表。

2.5版本下物化視圖還不支持查詢改寫，由于物化視圖是預(yù)先定義聚合的數(shù)據(jù)，因此當(dāng)要查詢的數(shù)據(jù)列超過物化視圖所定義列的范圍的話，會導(dǎo)致物化視圖失效。

創(chuàng)建語句如下：

create materialized view table_view as
select
  si,
  date,
  SUM(qscn),
  //....
from
  tabel_a_detail
group by
  si,
  date

當(dāng)創(chuàng)建完物化視圖后，可以明顯的發(fā)現(xiàn)耗時(shí)變低了，再次查詢執(zhí)行計(jì)劃，rollup已經(jīng)變成了物化視圖的表名：

對近200個(gè)字段分別做聚合操作后再分頁，SQL如下：

SELECT
    si,
    `date`,
    SUM(qscn) AS qscn
    // ...省略194個(gè)聚合指標(biāo)
FROM tabel_a_detail
GROUP BY si, date
LIMIT 0,100;

從查詢耗時(shí)上來看，物化視圖能極大的提高查詢效率，在大量數(shù)據(jù)下也比較平穩(wěn)。

4.5   星型模型

StarRocks 支持選擇更靈活的星型模型來替代傳統(tǒng)建模方式的大寬表。用一個(gè)視圖來取代寬表，直接使用多表關(guān)聯(lián)來查詢。在 SSB 的標(biāo)準(zhǔn)測試集的對比中，StarRocks 的多表關(guān)聯(lián)性能相較于單表查詢并無明顯下降。

相比星型模型，寬表的缺點(diǎn)包括：

• 維度更新成本更高。寬表中，維度信息更新會反應(yīng)到整張表中，其更新的頻率直接影響查詢的效率。
• 維護(hù)成本更高。寬表的建設(shè)需要額外的開發(fā)工作、存儲空間。
• 導(dǎo)入成本更高。寬表的 Schema 字段數(shù)較多，導(dǎo)入過程中需要排序的列會增加，進(jìn)而導(dǎo)致導(dǎo)入時(shí)間變長。

5、問題與規(guī)劃

我們在上線后的使用過程中也發(fā)現(xiàn)了一些瓶頸點(diǎn)，比如高計(jì)算量 + 大數(shù)據(jù)量的查詢時(shí)間會略久（數(shù)億行數(shù)據(jù)的count(distinct case when),sum(case when)等）、主鍵模型下的某寬表數(shù)據(jù)空洞 + 列數(shù)越來越多導(dǎo)致查詢及導(dǎo)入性能受影響，基于這些瓶頸我們未來有如下規(guī)劃：

• 優(yōu)化表結(jié)構(gòu)設(shè)計(jì)?

主鍵模型的某寬表的表結(jié)構(gòu)及示例數(shù)據(jù)如下，由于不同指標(biāo)(A、B、C、...、Y)的可能情況較多(1、2、3、...、20)，就導(dǎo)致組合之下存在25 * 20=500列，且對于某一行數(shù)據(jù)的比如A指標(biāo)，可能僅有A_1、A_2列是有具體值的，而對于A_3 ~ A_20其實(shí)都是默認(rèn)值或者空值；而B指標(biāo)，卻可能是B_3和B_6列是有值的，其他列是默認(rèn)值，這便造成了表中數(shù)據(jù)的空洞化；另一方面，假如需要新增指標(biāo)的話，比如新增Z指標(biāo)，大寬表在原有基礎(chǔ)上又要新增20列(Z_1 ~ Z_20)，這對于表的維護(hù)以及查詢導(dǎo)入都會帶來壓力。

+-------+------+------+------------+-------+------+------------+-------------+------+
|p_id   |A_1   |A_2   |A_3   ...   |A_20   |B_1   |B_2   ...   |B_20   ...   |Y_20  |
+-------+------+------+------------+-------+------+------------+-------------+------+
|1      |3     |4     |0     ...   |0      |0     |0     ...   |999    ...   |0     |
+-------+------+------+------------+-------+------+------------+-------------+------+
|9987   |9     |0     |1     ...   |0      |2     |4     ...   |0      ...   |197   |
...

為此后續(xù)我們考慮兩種思路進(jìn)行表結(jié)構(gòu)的優(yōu)化，一是使用非結(jié)構(gòu)化的數(shù)據(jù)類型比如json格式來存儲相關(guān)數(shù)據(jù)，但會導(dǎo)致相關(guān)列的篩選性能下降；二是對寬表進(jìn)行拆分，但會造成行數(shù)據(jù)量的暴漲。所以這塊還是需要花費(fèi)心思設(shè)計(jì)下的，也歡迎大家有好的想法與我們交流。

• 多表異步物化視圖?

對于多表關(guān)聯(lián)的場景，我們希望后續(xù)能夠使用多表物化視圖的形式對數(shù)據(jù)進(jìn)行預(yù)聚合，從而在查詢時(shí)提高查詢響應(yīng)的速度，尤其是大數(shù)據(jù)量的查詢場景下；同時(shí)由于我們數(shù)據(jù)是每天固定時(shí)間批量導(dǎo)入，完全可以接受在數(shù)據(jù)導(dǎo)入后異步刷新物化視圖。但目前2.4版本的多表異步物化視圖尚不支持查詢改寫，2.5支持SPJG類型查詢的自動(dòng)命中物化視圖查詢改寫，3.0支持大多數(shù)查詢場景的查詢改寫。

• Query Cache?

Query Cache 可以保存查詢的中間計(jì)算結(jié)果。后續(xù)發(fā)起的語義等價(jià)的查詢，能夠復(fù)用先前緩存的結(jié)果，加速計(jì)算，從而提升高并發(fā)場景下簡單聚合查詢的 QPS 并降低平均時(shí)延。該特性自2.5版本開始支持，且初期支持有限，比如2.5版本僅支持寬表模型下的單表聚合查詢，而3.0會支持更多使用場景，包括各種 Broadcast Join、Bucket Shuffle Join 等 Join 場景。所以后續(xù)比較期待使用該特性擴(kuò)展我們查詢的QPS，提高查詢體驗(yàn)。

6、寫在最后

雖然整個(gè)過程遇到了很多問題也踩了不少的坑，但上線后查詢響應(yīng)時(shí)間以及整體運(yùn)行的穩(wěn)定性還是比較滿意的，因此后續(xù)我們也考慮接入更多的數(shù)據(jù)以及場景到starrocks中，也特別感謝DBA 團(tuán)隊(duì)和 Starrocks 官方的支持。?