第三步：学习数据库表设计要点

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`)
PROPERTIES ();

建表完成后，您可以通过如下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';