ApsaraDB for SelectDB：第三步：學習資料庫表設計要點

Unique模型

在Unique模型中，Key列值相同時，多行資料的Value列會按寫入的先後順序進行覆蓋。Unique模型提供了兩種實現方式：讀時合并（MOR，merge-on-read）和寫時合并（MOW，merge-on-write）。

由於寫時合并技術已非常成熟且穩定，並且能夠提供優異的查詢效能，因此建議您優先採用寫時合并的實現方式。此處僅簡要介紹Unique模型的寫時合并。有關讀時合并的詳細資料，請參見讀時合并（MOR）。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "訂單id",
    `order_time` DATETIME NOT NULL COMMENT "訂單時間",
    `customer_id` LARGEINT NOT NULL COMMENT "使用者id",
    `total_amount` DOUBLE COMMENT "訂單總金額",
    `status` VARCHAR(20) COMMENT "訂單狀態",
    `payment_method` VARCHAR(20) COMMENT "支付方式",
    `shipping_method` VARCHAR(20) COMMENT "運輸方式",
    `customer_city` VARCHAR(20) COMMENT "使用者所在城市",
    `customer_address` VARCHAR(500) COMMENT "使用者地址"
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Duplicate模型

在Duplicate模型中，Key列值相同時，多行資料同時儲存在系統中，沒有預彙總、主鍵唯一性限制式等特點。

例如，您希望記錄並分析業務系統產生的日誌資料，且期望資料按照日誌時間、日誌類型、錯誤碼進行排序儲存，您可以選擇此模型。具體建立log表的語句如下，其表示log表模型為Duplicate模型，且資料會按照log_time、log_type和error_code進行排序。

CREATE TABLE IF NOT EXISTS log
(
    `log_time` DATETIME NOT NULL COMMENT "日誌時間",
    `log_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(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`) ()
DISTRIBUTED BY HASH(`log_type`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Aggregate模型

CREATE TABLE IF NOT EXISTS user_behavior
(
    `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`)
PARTITION BY RANGE(`date`) ()
DISTRIBUTED BY HASH(`user_id`)
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

Range分區

Range分區是指按照分區欄位範圍，對資料進行劃分管理，是最常用的分區方式。典型的使用情境是按照時間進行資料分區，方便對海量的時間序資料進行管理、查詢最佳化等。

分區分桶的最終目的是合理地劃分資料，分區規則設定合理性的主要標準是：

• 在分區及分桶規則下，每個分桶（Tablet）的資料量在1~10 GB的範圍內；

• 根據您管理資料的粒度確定分區粒度（例如，在日誌情境下，您通常需要按天淘汰歷史資料，此時選擇以天為分區粒度比較合適）。

在日誌情境下，經常按照時間範圍過濾查詢資料，且需要按照時間淘汰歷史分區，您可以將log_time欄位指定為分區列，並採取按天分區的方式，樣本如下。

CREATE TABLE IF NOT EXISTS log
(
 `log_time` DATETIME NOT NULL COMMENT "日誌時間",
 `log_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(`log_time`, `log_type`, `error_code`)
PARTITION BY RANGE(`log_time`)
(
 PARTITION `p20240201` VALUES [("2024-02-01"), ("2024-02-02")),
 PARTITION `p20240202` VALUES [("2024-02-02"), ("2024-02-03")),
 PARTITION `p20240203` VALUES [("2024-02-03"), ("2024-02-04"))
)
DISTRIBUTED BY HASH(`log_type`);

建表完成後，您可以通過如下SQL查看錶的分區資訊。

SHOW partitions FROM log;

p20240201: [("2024-02-01"), ("2024-02-02"))
p20240202: [("2024-02-02"), ("2024-02-03"))
p20240203: [("2024-02-03"), ("2024-02-04"))

當您使用以下語句查詢資料時，就會命中分區p20240202: [("2024-02-02"), ("2024-02-03"))，系統不會掃描剩餘兩個分區的資料，從而提高了查詢資料的速度。

SELECT * FROM orders WHERE order_time = '2024-02-02';

List分區

List分區是按照分區欄位的枚舉值，對資料進行劃分管理。當對採用List分區的表進行查詢時，可結合過濾條件快速進行分區裁剪，提升查詢效能。

您可以根據操作業務資料時常用的欄位來選擇List分區列。需要注意的是，各個分區之間的資料量要均勻，避免嚴重的資料扭曲。

例如，在電商情境中，訂單資料量通常非常龐大，且某些情境經常需要根據訂單使用者所屬城市來查詢分析此類資料。因此，為了更方便地管理和查詢資料，可以將customer_city欄位指定為分區列。假如資料量按地區的分布如下：

• 北京、上海和中國香港預計有6GB。

• 紐約、舊金山預計有5GB。

• 東京預計有5GB。

此時您可以按照以下樣本進行分區。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "訂單id",
    `order_time` DATETIME NOT NULL COMMENT "訂單時間",
    `customer_city` VARCHAR(20) COMMENT "使用者所在城市",
    `customer_id` LARGEINT NOT NULL COMMENT "使用者id",
    `total_amount` DOUBLE COMMENT "訂單總金額",
    `status` VARCHAR(20) COMMENT "訂單狀態",
    `payment_method` VARCHAR(20) COMMENT "支付方式",
    `shipping_method` VARCHAR(20) COMMENT "運輸方式",
    `customer_address` VARCHAR(500) COMMENT "使用者地址"
)
UNIQUE KEY(`order_id`, `order_time`, `customer_city`)
PARTITION BY LIST(`customer_city`)
(
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true"
);

建表完成後，您可以通過如下SQL查看錶的分區資訊，該表會自動產生以下3個分區。

SHOW partitions FROM orders;

p_cn: ("Beijing", "Shanghai", "Hong Kong")
p_usa: ("New York", "San Francisco")
p_jp: ("Tokyo")

當您使用以下語句查詢資料時，就會命中分區p_jp: ("Tokyo")，系統不會掃描剩餘兩個分區的資料，從而提高了查詢資料的速度。

SELECT * FROM orders WHERE customer_city = 'Tokyo';

倒排索引

SelectDB支援倒排索引，可用於滿足文字欄位的全文檢索索引、以及普通欄位的等值或範圍查詢，能夠快速從大量資料中篩選出滿足條件的資料。此處僅簡紹如何建立倒排索引，更多資訊，請參見倒排索引。

建表時建立索引

在電商情境中，根據使用者ID、使用者地址關鍵詞查詢訂單資訊是高頻操作，此時可以在customer_id和customer_address欄位上建立倒排索引來提高查詢速度。具體建表語句如下。

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` LARGEINT NOT NULL COMMENT "訂單id",
    `order_time` DATETIME NOT NULL COMMENT "訂單時間",
    `customer_id` LARGEINT NOT NULL COMMENT "使用者id",
    `total_amount` DOUBLE COMMENT "訂單總金額",
    `status` VARCHAR(20) COMMENT "訂單狀態",
    `payment_method` VARCHAR(20) COMMENT "支付方式",
    `shipping_method` VARCHAR(20) COMMENT "運輸方式",
    `customer_city` VARCHAR(20) COMMENT "使用者所在城市",
    `customer_address` VARCHAR(500) COMMENT "使用者地址",
    INDEX idx_customer_id (`customer_id`) USING INVERTED,
    INDEX idx_customer_address (`customer_address`) USING INVERTED PROPERTIES("parser" = "chinese")
)
UNIQUE KEY(`order_id`, `order_time`)
PARTITION BY RANGE(`order_time`) ()
DISTRIBUTED BY HASH(`order_id`)
PROPERTIES (
    "enable_unique_key_merge_on_write" = "true",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-7",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.create_history_partition" = "true",
    "dynamic_partition.buckets" = "16"
);

已有表建立索引

在電商情境中，根據使用者ID查詢訂單資訊資料是一個高頻操作，但如果建表時沒有為customer_id欄位建立倒排索引，可以使用以下語句為其增加索引。

ALTER TABLE orders ADD INDEX idx_customer_id (`customer_id`) USING INVERTED;

首碼索引

首碼索引是在底層資料按照Key列排序的基礎上，選擇首碼的一個或多個Key列構建的索引，其本質是基於資料排序特性進行二分尋找。首碼索引屬於內建索引，建表後SelectDB自動建立。

首碼索引沒有專門的文法去定義，系統會根據建表的Key欄欄位定義順序，選取前36個位元組所能覆蓋的欄位作為首碼索引，但當遇到VARCHAR類型時，首碼索引會直接截斷，後面的Key列不再加入首碼索引。

建表時欄位定義的順序尤為重要，它決定了哪些欄位會被用作首碼索引，強烈建議您參考如下原則確定Key列順序：

• 高頻用於過濾條件的、高基數的Key列放在其他欄位之前。如Duplicate模型章節的日誌情境中，日誌時間log_time放在錯誤碼error_code之前。

• 等值過濾條件的Key列放在區間過濾條件的Key列之前。如倒排索引章節的電商情境中，時間order_time通常按照區間過濾，放在訂單號order_id之後。

• 普通類型欄位放在VARCHAR類型欄位之前。如INT類型的Key列放在VARCHAR類的KEY列之前。

使用樣本

在倒排索引章節的電商情境中，訂單資訊表的首碼索引為order_id+order_time，當查詢條件是首碼索引的首碼時（即查詢條件包含order_id或同時包含order_id和order_time），可以極大的加快查詢速度。如以下兩個樣本，樣本一的查詢速度會遠高於樣本二的查詢速度。

樣本一

SELECT * FROM orders WHERE order_id = 1829239 and order_time = '2024-02-01';

樣本二

SELECT * FROM orders WHERE order_time = '2024-02-01';