1、背景

貨品活動運營在圈選所需貨品清單時，需要操作自助取數、智能運營系統、數據報表等多個系統工具才能完成。需要一個以供給側盤貨為核心需求的盤貨工具，實現運營各場景盤貨及貨品分析訴求，提升運營效率。但是實現起來由以下幾個難點。

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

業內比較流行的OLAP數據庫主要有ClickHouse和StarRocks。ClickHouse使用成本較高，非標準SQL協議，對JOIN支持不好，對靈活的業務開發并不友好。StarRocks支持標準的SQL協議，且對JOIN支持較好，MPP+向量化的查詢引擎，性能也得到保障。并且在與其他數據庫的性能測試對比中，StarRocks表現也十分亮眼。

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

2、模型選擇

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

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

3、數據寫入

設計完表結構后，我們考慮如何進行數據的導入。StarRocks支持多種數據源的導入，下圖展示了在各種數據源場景下，應該選擇哪一種導入方式。

！盤貨的指標數據我們選擇通過DataWorks（DataX）方式進行導入。而在導入的過程中經常會碰到以下兩個問題。

3.1   列數量不匹配

默認設置下，數據會被轉化為字符串，以 CSV 格式通過 Stream Load 導入至 StarRocks。字符串以 \t? 作為列分隔符，\n 作為行分隔符。

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

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

• 錯誤現象

• 解決示例

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

3.2   版本號超出限制

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

• 錯誤現象

• 解決示例

4、性能優化

4.1   執行計劃

• Query Plan

通過以下命令查看 Query Plan。

# 查看SQL執行計劃
EXPLAIN sql_statement;
# 查看SQL包含列統計信息的執行計劃
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;

執行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

核心指標主要有以下幾個：

• Query Profile

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

set is_report_success = true;

4.2   索引

• 前綴索引

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

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

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索引

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

舉個??，現在對商品信息的商品狀態和商品類型創建bitmap索引：

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

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

4.3   Colocate Join

！Colocation Join 功能，是將一組擁有相同 Colocation Group Schema（CGS）的 Table 組成一個 Colocation Group（CG）。并保證這些 Table 對應的數據分片會落在同一個 BE 節點上。使得當 Colocation Group 內的表進行分桶列上的 Join 操作時，可以通過直接進行本地數據 Join，減少數據在節點間的傳輸耗時。

同一 CG 內的 Table 必須保證以下屬性相同：

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

建表時，可以在 PROPERTIES? 中指定屬性 "colocate_with" = "group_name"，表示這個表是一個 Colocation Join 表，并且歸屬于一個指定的 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 的執行計劃，join op后會標注走的COLOCATE

4.4   物化視圖

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

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

創建語句如下：

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

當創建完物化視圖后，可以明顯的發現耗時變低了，再次查詢執行計劃，rollup已經變成了物化視圖的表名：

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

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

從查詢耗時上來看，物化視圖能極大的提高查詢效率，在大量數據下也比較平穩。

4.5   星型模型

StarRocks 支持選擇更靈活的星型模型來替代傳統建模方式的大寬表。用一個視圖來取代寬表，直接使用多表關聯來查詢。在 SSB 的標準測試集的對比中，StarRocks 的多表關聯性能相較于單表查詢并無明顯下降。

相比星型模型，寬表的缺點包括：

• 維度更新成本更高。寬表中，維度信息更新會反應到整張表中，其更新的頻率直接影響查詢的效率。
• 維護成本更高。寬表的建設需要額外的開發工作、存儲空間。
• 導入成本更高。寬表的 Schema 字段數較多，導入過程中需要排序的列會增加，進而導致導入時間變長。

5、問題與規劃

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

• 優化表結構設計?

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

+-------+------+------+------------+-------+------+------------+-------------+------+
|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   |
...

為此后續我們考慮兩種思路進行表結構的優化，一是使用非結構化的數據類型比如json格式來存儲相關數據，但會導致相關列的篩選性能下降；二是對寬表進行拆分，但會造成行數據量的暴漲。所以這塊還是需要花費心思設計下的，也歡迎大家有好的想法與我們交流。

• 多表異步物化視圖?

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

• Query Cache?

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

6、寫在最后

雖然整個過程遇到了很多問題也踩了不少的坑，但上線后查詢響應時間以及整體運行的穩定性還是比較滿意的，因此后續我們也考慮接入更多的數據以及場景到starrocks中，也特別感謝DBA 團隊和 Starrocks 官方的支持。?