ApsaraDB for SelectDB：資料模型

基本概念

在雲資料庫 SelectDB 版中，資料以表（Table）的形式進行邏輯上的描述。 一張表包括行（Row）和列（Column），Row即您資料表中的一行資料，Column用於描述一行資料中不同的欄位。

Column可以分為兩大類：Key和Value。從業務角度看，Key和Value可以分別對應維度列和指標列。在SelectDB建表語句的列中，關鍵字DUPLICATE KEY、AGGREGATE KEY和UNIQUE KEY指定的列即是Key列，其他列是Value列。

上述關鍵字對應SelectDB中的3種資料模型，本文將對這些資料模型進行詳細介紹：

• Aggregate模型

• Unique模型

• Duplicate模型

Aggregate模型

對於寫入的資料，SelectDB會根據不同資料模型，根據建表所選不同模型的Key列中，資料相同的行（Row）進行不同的處理。對Aggregate模型而言，指定的所有Key列資料相同的行，多行資料會進行合并，Value列按照建表時欄位定義中設定的AggregationType進行預彙總，最終只保留一行資料。

這意味著Aggregate模型可以通過預彙總，極大地降低彙總查詢時所需掃描的資料量和查詢的計算量，非常適合報表類統計分析情境。該模型對count(*)查詢不友好，因為固定了Value列上的彙總方式，在進行其他類型的彙總查詢時，需要考慮語義正確性。

通過以下樣本來闡述什麼是彙總模型，以及如何正確地使用Aggregate彙總模型。

CREATE TABLE IF NOT EXISTS test.example_tbl1
(
    `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 16;

INSERT INTO example_db.example_tbl_agg1 VALUES
(10000,"2017-10-01","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","廣州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

CREATE TABLE IF NOT EXISTS test.example_tbl2
(
    `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 16;

INSERT INTO test.example_tbl2 VALUES
(10000,"2017-10-01","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","廣州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

INSERT INTO test.example_tbl2 VALUES
(10004,"2017-10-03","深圳",35,0,"2017-10-03 11:22:00",44,19,19),
(10005,"2017-10-03","長沙",29,1,"2017-10-03 18:11:02",3,1,1);

CREATE TABLE IF NOT EXISTS test.example_tbl3
(
    `user_id` LARGEINT NOT NULL COMMENT "使用者id",
    `date` DATE NOT NULL COMMENT "資料寫入日期時間",
    `timestamp` DATETIME 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`, `timestamp`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

INSERT INTO test.example_tbl3 VALUES
(10000,"2017-10-01","2017-10-01 08:00:05","北京",20,0,"2017-10-01 06:00:00",20,10,10),
(10000,"2017-10-01","2017-10-01 09:00:05","北京",20,0,"2017-10-01 07:00:00",15,2,2),
(10001,"2017-10-01","2017-10-01 18:12:10","北京",30,1,"2017-10-01 17:05:45",2,22,22),
(10002,"2017-10-02","2017-10-02 13:10:00","上海",20,1,"2017-10-02 12:59:12",200,5,5),
(10003,"2017-10-02","2017-10-02 13:15:00","廣州",32,0,"2017-10-02 11:20:00",30,11,11),
(10004,"2017-10-01","2017-10-01 12:12:48","深圳",35,0,"2017-10-01 10:00:15",100,3,3),
(10004,"2017-10-03","2017-10-03 12:38:20","深圳",35,0,"2017-10-03 10:20:22",11,6,6);

Unique模型

在某些多維分析情境下，更關注的是如何保證Key的唯一性，即如何獲得主鍵唯一性限制式。因此，雲資料庫 SelectDB 版引入了Unique表引擎。在早期版本中，該模型本質上是彙總模型的一個特例，也是一種簡化的表結構表示方式，由於彙總模型的實現方式是讀時合并MOR（Merge on Read)，因此在一些彙總查詢上效能不佳。在3.0版本SelectDB引入了Unique模型新的實現方式，寫時合并MOW（Merge on Write），通過在寫入時做一些額外的工作，實現最優的查詢效能。

對Unique模型而言，指定的所有Key列資料相同時多行資料會進行覆蓋，僅保留最新匯入的行，提供類似關係型資料庫中的唯一性限制式。

Unique模型針對需要唯一性限制式的情境，提供了主鍵唯一性限制式，可用於滿足訂單等關係型資料分析情境。對於彙總查詢有較高效能需求的情境，推薦使用新版本引入的寫時合并實現，但是該表引擎無法利用 ROLLUP等預彙總帶來的查詢優勢。

我們將通過以下樣本來闡述兩種不同的實現方式。

寫時合并（MOW）

Unqiue表引擎的寫時合并實現，與彙總表引擎就是完全不同的兩種模型，查詢效能更接近於上文的Duplicate模型，在有主鍵約束需求的情境上相比彙總模型有較大的查詢效能優勢，尤其是在彙總查詢以及需要用索引過濾大量資料的查詢中。

寫時合并在3.0版本中，作為一個新的特性，預設關閉，您可以通過如下屬性property來開啟。

"enable_unique_key_merge_on_write" = "true"

資料表example_tbl6的結構定義表。

建立表example_tbl6（省略建表語句中的Partition和Distribution資訊），修改表property屬性。樣本如下。

CREATE TABLE IF NOT EXISTS test.example_tbl6
(
    `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 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

這種建表語句所建立的表結構與彙總表引擎完全不同。

在開啟了寫時合并選項的Unique表上，資料在匯入階段就會去將被覆蓋和被更新的資料進行標記刪除，同時將新的資料寫入新的檔案。在查詢的時候，所有被標記刪除的資料都會在檔案層級被過濾掉，讀取出來的資料就都是最新的資料。這消除掉了讀時合并中的資料彙總過程，並且能夠在很多情況下支援多種謂詞的下推。因此在許多情境都能帶來比較大的效能提升，尤其是在有彙總查詢的情況下。

讀時合并（MOR）

資料表example_tbl4的結構定義表。

這是一個典型的使用者基礎資訊表。這類資料沒有彙總需求，只需保證主鍵唯一性。（這裡的主鍵為user_id+username）。

建立表example_tbl4（省略建表語句中的Partition和Distribution資訊），樣本如下。

CREATE TABLE IF NOT EXISTS test.example_tbl4
(
    `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 16;

資料表example_tbl5的結構定義表。

建立表example_tbl5（省略建表語句中的Partition和Distribution資訊），樣本如下。

CREATE TABLE IF NOT EXISTS test.example_tbl5
(
    `user_id` LARGEINT NOT NULL COMMENT "使用者id",
    `username` VARCHAR(50) NOT NULL COMMENT "使用者暱稱",
    `city` VARCHAR(20) REPLACE COMMENT "使用者所在城市",
    `age` SMALLINT REPLACE COMMENT "使用者年齡",
    `sex` TINYINT REPLACE COMMENT "使用者性別",
    `phone` LARGEINT REPLACE COMMENT "使用者電話",
    `address` VARCHAR(500) REPLACE COMMENT "使用者地址",
    `register_time` DATETIME REPLACE COMMENT "使用者註冊時間"
)
AGGREGATE KEY(`user_id`, `username`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

上述表example_tbl4的表結構，完全等同於使用彙總表引擎example_tbl5的表結構。

即Unique引擎的讀時合并實現完全可以用彙總模型中的REPLACE方式替代。其內部的實現方式和資料存放區方式也完全一樣。

Duplicate模型

在某些多維分析情境下，資料既沒有主鍵，也沒有彙總需求。因此，引入Duplicate資料模型來滿足這類需求。

對Duplicate模型而言，指定的所有Key列資料相同時多行資料同時儲存在系統中，互不影響，沒有預彙總、唯一性限制式。

Duplicate模型適合任意維度Ad-hoc查詢，可用於滿足日誌等詳細資料分析情境。該表引擎無法利用預彙總帶來的效能提升、唯一性限制式帶來的自動更新便利性。

我們將通過以下樣本來闡述Duplicate模型的實現方式。

資料表example_tbl7的結構定義表。

建立表example_tbl7（省略建表語句中的Partition和Distribution資訊）。樣本如下。

CREATE TABLE IF NOT EXISTS test.example_tbl7
(
    `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 16;

這種表模型區別於Aggregate和Unique模型，資料完全按照匯入檔案中的資料進行儲存，不會有任何彙總。即使兩行資料完全相同，也都會保留。 而在建表語句中指定的DUPLICATE KEY，只是用來指明底層資料按照那些列進行排序。在DUPLICATE KEY的選擇上，建議適當的選擇前2-4列即可。

資料模型比較

Aggregate表引擎的局限性

在彙總表引擎中，模型對外展現的是最終彙總後的資料。也就是說，任何還未彙總的資料（比如說兩個不同匯入批次的資料），必須通過某種方式保證對外展示的一致性。通過以下樣本來進行說明。

表example_tbl8結構如下。

向表example_tbl8中分兩個批次寫入如下資料。

• 第一次大量匯入的資料如下。

• 第二次大量匯入的資料如下。

可以看到，使用者ID為10001的資料在兩個批次中匯入，在SelectDB內部的合并任務未執行完畢前，底層儲存會包含上述5條未經處理資料。但是為了保證使用者只能查詢到如下最終彙總後的資料，我們會在查詢引擎中自動加入了彙總運算元，來保證資料對外的一致性。使用者查詢結果如下。

例如，執行如下SQL，得到的結果是5，而不是1。

SELECT MIN(cost) FROM example_tbl8;

同時，這種一致性保證，在某些查詢中，會極大的降低查詢效率。我們以最基本的count(*)查詢為例。

SELECT COUNT(*) FROM example_tbl8;

在其他資料庫中，這類查詢都會很快的返回結果。因為，可以通過如“匯入時對行進行計數，儲存count的統計資訊”，或者在查詢時“僅掃描某一列資料，獲得count值”的方式，只需很小的開銷，即可獲得查詢結果。但是在彙總表引擎中，這種查詢的開銷非常大。

以上述的資料為例。

執行SQLselect count(*) from example_tbl8;的結果應該為4。但如果只掃描user_id這一列，且加上查詢時彙總，最終得到的結果是3（10001, 10002, 10003）。而不加查詢時彙總，則得到的結果是5（兩批次一共5行資料）。可見這兩個結果都是不對的。

因為當彙總列非常多時，count()查詢需要掃描大量的資料。為了得到正確的結果，我們必須同時讀取user_id和date這兩列的資料，再加上查詢時彙總，才能返回4這個正確的結果。即在count()查詢中，必須掃描所有的AGGREGATE KEY列（這裡就是user_id和date），並且彙總後，才能得到正確的結果。

因此，當業務上有頻繁的count(*)查詢時，我們建議通過增加一個值恒為1、彙總類型為SUM的列來類比count(*)。例如在example_tbl8的表結構中添加列count，修改後結構定義表如下。

增加一個count列，並且匯入資料中，該列值恒為1。則select count(*) from table;的結果等價於select sum(count) from table;。而後者的查詢效率將遠高於前者。不過這種方式也有使用限制，就是需要您自行保證，不會重複匯入AGGREGATE KEY列都相同的行。否則，select sum(count) from table;只能表述原始匯入的行數，而不是select count(*) from table;的語義。

另一種方式，就是將如上的count列的彙總類型改為REPLACE，且依然值恒為1。那麼select sum(count) from table;和select count(*) from table;的結果將是一致的。並且這種方式，沒有匯入重複行的限制。

Unique表引擎的寫時合并實現

Unique引擎的寫時合并實現沒有彙總引擎的局限性，還是以上方的資料為例，寫時合并為每次匯入的rowset增加了對應的delete bitmap，來標記哪些資料被覆蓋。第一批資料匯入後狀態如下。

• 第一次大量匯入的結果資料如下。

當第二批資料匯入完成後，第一批次匯入的資料中重複的行就會被標記為已刪除，此時兩批資料狀態如下。

• 第一批次匯入的資料被標記後結果如下。

• 第二批次匯入的資料結果如下。

在查詢時，所有在delete bitmap中被標記刪除的資料都不會讀出來，因此也無需進行做任何資料彙總，上述資料中有效行數為4行，查詢出的結果也應該是4行，也就可以採取開銷最小的方式來擷取結果，即前面提到的“僅掃描某一列資料，獲得count值”的方式。

在測試環境中，count(*)查詢在Unique引擎的寫時合并實現上的效能，相比彙總模型有10倍以上的提升。

Duplicate表引擎的局限性

Duplicate 模型沒有彙總引擎的這個局限性。因為該引擎不涉及彙總語意，在做count(*)查詢時，任意選擇一列查詢，即可得到語意正確的結果。

Key列

Duplicate、Aggregate、Unique模型在使用過程中，都會在建表時指定Key列，然而實際上有所區別。

對於Duplicate模型，表的Key列，可以認為只是 “排序列”，並非起到唯一標識的作用。

對於Aggregate、Unique模型，在這種彙總類型的表裡，Key列兼顧 “排序列” 和 “唯一識別欄位”，是真正意義上的“Key列”。

資料模型的選擇建議

因為資料模型在建表時就已經確定，且無法修改。所以，選擇一個合適的資料模型非常重要。

1. Aggregate模型可以通過預彙總，極大地降低彙總查詢時所需掃描的資料量和查詢的計算量，從而大幅提升查詢效能。適合有固定查詢模式或需要彙總分析的報表類情境。該模型對count(*)查詢不友好；同時因為固定了Value列的彙總方式，在進行其他類型的彙總查詢時，需要考慮語義正確性。

2. Unique模型針對需要唯一性限制式的情境，可以保證主鍵唯一性。適合訂單、交易等關係型資料的分析情境。該模型無法利用ROLLUP等預彙總帶來的查詢優勢。當您對於彙總查詢有較高效能需求，推薦使用自1.2版本加入的寫時合并實現。

3. Duplicate模型雖然同樣無法利用預彙總的特性，但是不受彙總模型的約束，可以充分發揮列存的優勢（唯讀取相關列，而不需要讀取所有Key列）。適合針對日誌資料、詳細資料等進行任意維度Ad-hoc查詢。

4. 如果有部分列更新的需求，請查閱文檔部分列更新擷取相關使用建議。