Apache doris 排序键及ShortKey Index

2021-09-07 | 阅读：次

1.排序列的原理

Apache Doris中为加速查询，在内部组织并存储数据时，会把表中数据按照指定的列进行排序，这部分用于排序的列（可以是一个或多个列），可以称之为Sort Key。明细模型中Sort Key就是指定的用于排序的列（即 DUPLICATE KEY 指定的列），聚合模型中Sort Key列就是用于聚合的列（即 AGGREGATE KEY 指定的列），唯一主键模型中Sort Key就是指定的满足唯一性约束的列（即 UNIQUE KEY 指定的列）。下图中的建表语句中Sort Key都为 (user_id, date, city, age, sex)。

CREATE TABLE user_access_dup
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
     user_name VARCHAR(32) DEFAULT '',
    `last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT DEFAULT "99999" COMMENT "用户最小停留时间"
)
DUPLICATE KEY(user_id, date, city, age, sex)
DISTRIBUTED BY HASH(city) BUCKETS 10;

CREATE TABLE user_access_agg
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
     user_name VARCHAR(32) DEFAULT '',
    `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(city) BUCKETS 10;

CREATE TABLE user_access_unique
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
     user_name VARCHAR(32) DEFAULT '',
    `last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT EFAULT "99999" COMMENT "用户最小停留时间"
)
UNIQUE KEY(user_id, date, city, age, sex)
DISTRIBUTED BY HASH(city) BUCKETS 10;

各表数据都依照user_id, date, city, age, sex这四列排序。这里有两点需要注意：

排序列的定义必须出现在建表语句中其他列的定义之前。的建表语句为例，三个表的排序列可以是user_id, date, city, age, sex，或者user_id, date, city, age, sex,user_name，但不能是user_id, date, city, ,user_name，或者user_id, date, city, age, sex。
排序列的顺序是由create table语句中的列顺序决定的。DUPLICATE/UNIQUE/AGGREGATE KEY中顺序需要和create table语句保持一致。以user_access_dup表为例，也就是说下面的建表语句会报错。

-- 错误的建表语句
CREATE TABLE user_access_dup
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
     user_name VARCHAR(32) DEFAULT '',
    `last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT EFAULT "99999" COMMENT "用户最小停留时间"
)
DUPLICATE KEY(date,user_id,city,age,sex)
DISTRIBUTED BY HASH(city) BUCKETS 10;

-- 正确的建表语句
CREATE TABLE user_access_dup
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
     user_name VARCHAR(32) DEFAULT '',
    `last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT EFAULT "99999" COMMENT "用户最小停留时间"
)
DUPLICATE KEY(user_id, date, city, age, sex)
DISTRIBUTED BY HASH(city) BUCKETS 10;

DUPLICATE KEY列顺序与CREATE TABLE中不一致

再来看一下排序列在查询中的效果，图1中排序列的效果可分三种情况进行描述：

用户查询时如果条件包含上述两列，则可以大幅地降低扫描数据行，如：
```
select sum(cost) from user_access_dup where user_id = 123 and city = 2 ;
```
如果查询只包含site_id一列，也能定位到只包含user_id 的数据行，如：
```
select sum(cost) from user_access_dup where user_id = 123;
```
如果查询只包含city一列，那么需要扫描所有的数据行，排序的效果相当于大打折扣，如：
```
select sum(cost) from user_access_dup  where city = 2;
```

在第一个case中，为了定位到数据行的位置，需进行二分查找，以找到指定区间。假设数据行非常多，直接对user_id, date, city, age, sex进行二分查找，需要把两列数据都加载到内存中，这会消耗大量内存空间。为优化这个细节，Doris在Sort Key的基础上引入稀疏的shortkey index，Sort Index的内容会比数据量少1024倍，因此会全量缓存在内存中，实际查找的过程中可以有效加速查询。当Sort Key列数非常多时，会占用大量内存, 为了避免这种情况, 对shortkey index索引项做了限制:

shortkey 的列只能是排序键的前缀;
shortkey 列数不超过3;
字节数不超过36字节;
不包含FLOAT/DOUBLE类型的列;
VARCHAR类型列只能出现一次, 并且是末尾位置;
当shortkey index的末尾列为CHAR或者VARCHAR类型时, shortkey的长度会超过36字节;

2. 如何选择排序列

从上面的介绍可以看出，如果用户在查询user_access_dup表时只选择city做查询条件，排序列相当于失去了功效。因此排序列的选择是和查询模式息息相关的，经常作为查询条件的列建议放在Sort Key中。

当Sort Key涉及多个列的时候，谁先谁后也有讲究，区分度高、经常查询的列建议放在前面。在user_access_dup表中，city的取值个数是固定的（城市数目是固定的），而site_id的取值个数要比city大得多，而且还在不断变多，因此user_id区分度就比city要高不少。

还是以user_access_dup表为例：

如果用户需要经常按user_id + date + city + age + sex的组合进行查询，那么把user_id 放在Sort Key第一列就是更加有效的一种方式。
如果用户需要经常用 city进行查询，偶尔按照 user_id + date + city + age + sex 组合查询，那么把 city 放在 Sort Key 的第一列就更为合适。
当然有一种极端情况，就是按user_id + date 组合查询、以及city + date 查询的比例不相上下。那么这个时候，可以创建一个city + date 为组合 key 的 RollUp 表，RollUp表会为city，date 再建一个Sort Index。