E-MapReduce：阿里雲StarRocks業務使用最佳實務

通用限制

• 僅支援UTF-8編碼。

• VARCHAR類型的最大長度為1048576。

• 資料目錄名、資料庫名、表名、視圖名、使用者名稱和角色名稱大小寫敏感，而列名和分區名大小寫不敏感。

主鍵表限制

• 在建表語句中，主鍵列必須在其他列之前定義。

• 主鍵必須包含分區列和分桶列。

• 主鍵列支援的資料類型為數值（整型、布爾）、日期和字串。

• 預設情況下，單條主索引值編碼後最大長度為128位元組。

• 建表後，主鍵不可修改。

• 主鍵列的值不可更新，避免破壞資料一致性。

建表文法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...][, index_definition1[, index_definition2, ...]])[ENGINE = [olap|mysql|elasticsearch|hive|iceberg|hudi|jdbc]][key_desc][COMMENT "table comment"][partition_desc][distribution_desc][rollup_index][ORDER BY (column_definition1,...)][PROPERTIES ("key"="value", ...)][BROKER PROPERTIES ("key"="value", ...)]

常用語句

• 使用Hash分桶與列存彙總

  CREATE TABLE example_db.table_hash (k1 TINYINT,k2 DECIMAL(10, 2) DEFAULT "10.5",v1 CHAR(10) REPLACE,v2 INT SUM
  )ENGINE = olap
  AGGREGATE KEY (k1, k2)COMMENT "my first StarRocks table"DISTRIBUTED BY HASH(k1);

• 建立分區表

  CREATE TABLE example_db.table_range (k1 DATE,k2 INT,k3 SMALLINT,v1 VARCHAR(2048),v2 DATETIME DEFAULT "2014-02-04 15:36:00")ENGINE = olap
  DUPLICATE KEY (k1, k2, k3)PARTITION BY RANGE (k1) (PARTITION p1 VALUES LESS THAN ("2014-01-01"),PARTITION p2 VALUES LESS THAN ("2014-06-01"),PARTITION p3 VALUES LESS THAN ("2014-12-01"))DISTRIBUTED BY HASH(k2);

• 建立動態分區表

  CREATE TABLE example_db.dynamic_partition (    k1 DATE,    k2 INT,    k3 SMALLINT,    v1 VARCHAR(2048),    v2 DATETIME DEFAULT "2014-02-04 15:36:00")ENGINE = olap
  DUPLICATE KEY (k1, k2, k3)PARTITION BY RANGE (k1) (    PARTITION p1 VALUES LESS THAN ("2014-01-01"),    PARTITION p2 VALUES LESS THAN ("2014-06-01"),    PARTITION p3 VALUES LESS THAN ("2014-12-01"))DISTRIBUTED BY HASH(k2)PROPERTIES (    "storage_medium" = "SSD",    "dynamic_partition.time_unit" = "DAY",    "dynamic_partition.start" = "-3",    "dynamic_partition.end" = "3",    "dynamic_partition.prefix" = "p");

• 通過選擇建立表

  CREATE TABLE employee_new
  PRIMARY KEY(order_id)AS SELECT order_list.order_id,SUM(goods.price) AS total
  FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
  GROUP BY order_id;

• 查看錶結構

  SHOW CREATE TABLE [db_name.]table_name;

• 建立和刪除索引

  CREATE INDEX index3 ON sales_records (item_id) USING BITMAP COMMENT '';
  DROP INDEX index3 ON sales_records;

• 清空表或分區

  TRUNCATE TABLE example_db.tbl;
  TRUNCATE TABLE tbl PARTITION (p1, p2);

欄位類型選擇

• 正確選擇時間類型和數字類型的列，以減少計算的開銷。例如，時間類型的資料“2024-01-01 00:00:00”不應使用VARCHAR儲存，這樣無法有效利用StarRocks內部的 ZoneMap 索引，從而影響過濾速度。

• 對於需要精確結果的情況，建議使用 DECIMAL 類型，而非 FLOAT 或 DOUBLE 類型。

分區選擇

• 值變化不頻繁的時間列常用於 WHERE 過濾，建議使用該列建立分區，可以結合日期（一級分區）和業務分類（二級分區，例如地區、訂單類型等枚舉類）。

• 對於有資料淘汰需求的情境，可以選擇使用動態分區。

• 如果資料更新呈現明顯的冷熱特徵，建議強制建立分區。例如，最近一周的資料可以按天分區。

• 單個分區的資料量建議不超過 100GB。

• 資料量超過 50GB 或 5,000,000 行（5KW）的表建議建立分區。

• 按需建立分區，避免過多分區，以免分區中繼資料佔用過多 FE 的記憶體。

• 當前支援的分區類型包括：時間類型（定界分割、運算式分區）、字串（列表分區）、數字（定界分割、列表分區）。

• 預設情況下，最大支援 1024 個分區，可以通過參數進行調整，但通常情況下無需調整。

分桶選擇

• 在生產環境中強制使用3個副本（僅適用於存算一體架構下）。

• 分桶個數的判斷：

  • 當表或分區資料量超過50 GB時，每個桶預估為1 GB。

  • 當表或分區資料量小於50 GB時，每個桶預估為500 MB。

  • 根據以上策略估算出的分桶數如果小於 BE 節點數，則最終分桶數以 BE 節點數為準。例如，如果有 6 個 BE 節點且根據 500MB 每個桶預估得出的分桶數為 1，最終分桶數取 6。

  • 單個桶的預估大小應在 500MB 至 1GB 之間，而未經處理資料的大小通常在 5GB 至 10GB（匯入 StarRocks 後，壓縮比約為 7:1 至 10:1）。

  • 對於非分區表，避免使用動態分桶，應根據實際資料量評估分桶個數。

  • 如果分區表各分區的資料差異很大，建議不要使用動態分桶策略。

• 分桶裁剪與資料扭曲的選擇

  • 如果分桶列在 WHERE 子句中頻繁使用且重複度較低（例如使用者識別碼、事務 ID 等），則可將該列用作分桶列。

  • 如果查詢條件中包含 city_id 和 site_id，而 city_id 的取值只有幾十，僅使用 city_id 作為分桶列可能導致某些桶的資料量過大，從而產生資料扭曲。此時可考慮將 city_id 和 site_id 聯合作為分桶欄位，但這樣做的缺點是，如果查詢條件中僅包含 city_id，則無法利用分桶裁剪。

  • 如果沒有合適的欄位打散資料，可以考慮使用隨機分桶，但此方式無法利用分桶裁剪的優勢。

  • 對於有兩個或多個超過 100,000 行的表進行 JOIN 操作，建議使用 Colocate Join，詳情請參見Colocate Join。

索引選擇

Bitmap索引

• 適合基數在 10,000 至 100,000 之間的列。

• Bitmap索引最佳化等值查詢（=）、範圍查詢（例如>、>=、<、<=）和IS NULL查詢，但不適用於不等於查詢（!=）和LIKE查詢。

• 不支援FLOAT、DOUBLE、BOOLEAN和DECIMAL類型的列建立Bitmap索引。

• 對於基數低於 255 的列（如城市、性別），不需要建立 Bitmap 索引，因為 StarRocks 會自動針對這些情況建立低基數字典以加速查詢。

• 在明細模型和主鍵模型中，可以為所有列建立 Bitmap 索引。在彙總模型和更新模型中，僅支援為Key建立 Bitmap 索引。

Bloom Filter索引

• Bloom Filter 索引可以快速判斷表的資料檔案中是否可能包含要查詢的資料。如果確定不包含，查詢可以直接跳過該檔案，從而減少掃描的資料量。

• 適合基數在 100,000 以上且列的重複度很低的列。

• 適用於IN和等值（=）過濾條件的查詢。

• 不支援為 TINYINT、FLOAT、DOUBLE 和 DECIMAL 類型的列建立 Bloom Filter 索引。

• 在主鍵模型和明細模型中，所有列均可建立Bloom Filter索引；彙總模型和更新模型中，僅維度列（Key列）支援建立。

資料生命週期

• 建議使用TRUNCATE刪除資料，而非DELETE。

• 完整的UPDATE文法僅適用於3.0版本及以上的主鍵模型；高並發的 UPDATE 操作受到限制，建議每次更新操作之間間隔至少一分鐘。

• 如果必須使用 DELETE 刪除資料，務必帶上 WHERE 條件，並且禁止並發執行 DELETE。例如，刪除 id=1, 2, 3, ..., 1000 時，建議DELETE FROM tbl1 WHERE id IN (1, 2, 3, ..., 1000)的方式，而非分開執行 1000 條 DELETE 語句。

• DROP 操作：

  • DROP 操作預設將資料移入 FE 資源回收筒，預設保留 86,400 秒（1 天），此期間可恢複以避免誤操作。該時間受參數 catalog_trash_expire_second 控制。

  • 超過 1 天后，資料將進入 BE 的 trash 目錄，預設保留 259,200 秒（3 天）。在 2.5.17、3.0.9 和 3.1.6 之後，預設值已改為 86,400 秒（1 天），受參數 trash_file_expire_time_sec 控制。

  • 如果在 DROP 後希望儘快釋放磁碟空間，可以考慮調小 FE 和 BE 的 trash 保留時間。

基本文法

詳情請參見SELECT。

常用SQL

• 簡單彙總分析

  SELECT tiny_column, SUM(short_column)FROM small_table 
  GROUP BY tiny_column 
  HAVING SUM(short_column) = 1;

• 使用GROUPING SETS

  SELECT a, b, SUM(c)FROM tab1 
  GROUP BY GROUPING SETS ((a, b), (a), (b), ());

• 使用LIMIT進行分頁

  SELECT varchar_column 
  FROM big_table 
  ORDER BY varchar_column 
  LIMIT 1 OFFSET 0;

SQL查詢注意事項

• 避免使用SELECT *，建議指定需要查詢的列，例如SELECT col0, col1 FROM tb1;。

• 避免全表掃描，建議增加過濾條件，例如SELECT col0, col1 FROM tb1 WHERE id = 123;。

• 避免巨量資料量下載，如果要使用，強制使用分頁，例如SELECT col0, col1, col2, ..., col50 FROM tb ORDER BY id LIMIT 0, 50000;。

• 分頁操作需加上ORDER BY以確保結果有序。

• 雖然StarRocks已進行隱式轉換以達到最佳效能，但建議使用類型一致的欄位進行JOIN，避免使用FLOAT或DOUBLE類型的JOIN以導致結果不準確。

• 關聯欄位不得使用函數或運算式。

  SELECT *
  FROM table1 
  JOIN table2 
  ON DATE_FORMAT(tb1.col1, '%Y-%m-%d') = DATE_FORMAT(tb2.col1, '%Y-%m-%d');

• 在處理兩個或多個超過KW行的表JOIN時，推薦使用Colocate Join。

• 避免笛卡爾積。

• 避免使用一些不必要的函數或者運算式。

  • 移除不必要的函數

    -- Q1 bad case
    SELECT l_tax 
    FROM lineitem 
    WHERE CAST(l_shipdate AS VARCHAR) > SUBSTR('1990-01-02 12:30:31', 1, 10);
    
    -- Q2 good case
    SELECT l_tax 
    FROM lineitem 
    WHERE l_shipdate > '1990-01-02';
    

  • 避免過度使用函數處理運算式

    -- Q1 bad case
    SELECT COUNT(1) 
    FROM lineitem 
    WHERE l_shipdate >= REGEXP_EXTRACT("TIME:1996-01-02 20:00:00", "(\\d{4}-\\d{2}-\\d{2})", 1);
    
    
    -- Q2 good case
    SELECT COUNT(1) 
    FROM lineitem 
    WHERE l_shipdate >= "1996-01-02";
    

  • 查詢多個表需要指定串連條件

    -- bad case
    SELECT * FROM table1, table2;
    
    -- good case
    SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column1;

  • 正確使用子查詢

    -- bad case
    SELECT *FROM table1 WHERE column1 IN (SELECT column2 FROM table2);
    
    -- good case
    SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2 WHERE table1.column3 = table2.column3);
    

  • 使用AND條件代替OR條件

    -- bad case
    SELECT *
    FROM table1
    JOIN table2
    WHERE (table1.column1 = table2.column1 OR table1.column2 = table2.column2);
    
    
    -- good case
    SELECT *
    FROM table1
    JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2;