第2.3章：StarRocks表设计--排序键和前缀索引

在介绍StarRocks的四种数据模型时，我们多次提到了排序键，也列出了一些排序键的注意事项，现在咱们来一起研究下到底什么是排序键。

1排序键

StarRocks为了加速查询，底层的数据是按照指定的列排序存储的，这部分用于排序的列（可以是一列或多列），就称为排序键（Sort Key）。以排序列作为条件进行数据查找，会非常的高效。

直观来看，各个模型的排序键就是建表语句中DUPLICATE KEY、AGGREGATE KEY、UNIQUE KEY或PRIMARY KEY后面指定的列。但是四种模型的排序键还是有一些区别：

明细模型：

明细模型排序键写法比较灵活，可以指定部分的维度列为排序键。比如表table02（严格来说它是没有指标列的）：

CREATE TABLE IF NOT EXISTS starrocks.table02 (

    event_time DATETIME NOT NULL COMMENT "datetime of event",

    event_type INT NOT NULL COMMENT "type of event",

    user_id INT COMMENT "id of user",

    channel INT COMMENT ""

)

DUPLICATE KEY(event_time, event_type,user_id)

DISTRIBUTED BY HASH(user_id) BUCKETS 10;

若我们使用DUPLICATE KEY()显式定义排序键，单从建表不报错的角度，可以有四种组合：

event_time

event_time, event_type

event_time, event_type, user_id

event_time, event_type, user_id, channel

可以发现，这四种组合的共同点是都是从表的首列开始，是连续的，且都和建表语句中列的顺序相同。这也正是排序键的定义要求：在建表时，用于排序的这些列需要定义在其他列之前，并且在指定排序键的时候列的顺序要和建表语句中的相同，否则建表语句会报错。

还以table02为例，若DUPLICATE KEY后的排序列写为：DUPLICATE KEY(event_type)或DUPLICATE KEY(event_type, event_time)或DUPLICATE KEY(event_time, user_id, event_type)，就都是错误的。

明细模型下，若我们省略排序键的语句，那么StarRocks通常会默认指定表结构的前三列作为排序键。

聚合模型：

聚合模型的排序键只能是把所有的维度列都写上去，或者干脆把排序键这里整个不写（默认也会使用所有的维度列）。以下面的table03为例，排序键只有一种组合，就是：AGGREGATE KEY(site_id, date, city_code, state)，要么这一句整个省略，不然如果写为AGGREGATE KEY(site_id)或者AGGREGATE KEY(site_id, date)都是会报错的。

CREATE TABLE IF NOT EXISTS starrocks.table03 (

    site_id LARGEINT NOT NULL COMMENT "id of site",

    date DATE NOT NULL COMMENT "time of event",

    city_code VARCHAR(20) COMMENT "city_code of user",

    state INT COMMENT "web state",

    pv BIGINT SUM DEFAULT "0" COMMENT "total page views"

)

DISTRIBUTED BY HASH(site_id) BUCKETS 8;

更新模型/主键模型：

更新模型和主键模型的排序键只有一种写法，就是在UNIQUE KEY()的括号中指定。以table04为例，建表时排序键语句为UNIQUE KEY(create_time, order_id)，则用于排序的列就是create_time和order_id。更新模型/主键模型的排序键必需显式指定，不能省略不写。

总结一下排序键的注意事项：

1、排序键中包含的列必须是从第一列开始，并且是连续的；

2、排序键中列的顺序是由create table语句中的列顺序决定的；

3、排序键不需要包含过多的列。如果选择了大量的列用于排序，那么排序的开销会导致数据导入的时间和资源使用增加；

4、在大多数时候，排序键中的前面几列也能很准确的定位到数据行所在的区间，更多列的排序也不会带来查询的提升；

5、排序键的选择需要结合查询场景，我们需要把经常作为查询条件的列建议放在排序键中。当排序键涉及多个列的时候，我们要将区分度高、经常查询的列建议放在前面。若一个字段只是偶尔查询，不需要将其放入排序键（当使用更新模型或主键模型时尤其注意这一点）。

2前缀索引

前面提到，StarRocks的底层数据是按照排序键排序后存储的。而前缀索引（shortkey index），就是在排序键的基础上实现的一种根据给定前缀列，有效加速查询的索引方式。

首先明确一点，前缀索引不需要我们单独手动创建或指定，在建表时其实已经默认完成了指定，它是StarRocks自带的一种加速方式。前缀索引的默认要求主要有：

1、前缀索引包含的列只能是排序键中的列；

2、前缀索引包含的列数不超过3；

3、前缀索引的字节数不超过36字节；

4、前缀索引不能包含FLOAT/DOUBLE类型的列；

5、前缀索引中VARCHAR类型列只能出现一次，并且是末尾位置（即使没有达到36个字节，如果遇到VARCHAR，也会直接截断，不再往后继续）；

6、若在建表语句中指定PROPERTIES {"short_key" = "integer"}时, 可突破上面的第2、3条限制。

StarRocks默认每1024行构成一个逻辑块，取逻辑块首行数据的前36个字节作为这行数据的前缀索引存储在shortkey index表中（这个表上层不可感知）。

举例说明，我们创建表table06：

CREATE TABLE IF NOT EXISTS starrocks.table06 (

    user_id BIGINT NOT NULL,

    age INT NOT NULL,

    message VARCHAR(100),

    max_time DATETIME,

    min_time DATETIME

) DUPLICATE KEY(user_id, age, message)

DISTRIBUTED BY HASH(user_id) BUCKETS 8;

其前缀索引就是user_id(8Byte) + age(4Bytes) + message(prefix 20 Bytes)（遇varchar截断），也就是说，每1024行会取首行的数据的这32个字节作为前缀。

当我们的查询条件可以命中前缀索引时，数据检索流程为：查找shortkey index表，获得逻辑块的起始行号，查找维度列的行号索引，获得目标列的数据块，读取数据块，然后解压解码，从数据块中找到维度列前缀对应的数据项。这样就可以极大的加快查询速度。

而如果查询无法命中前缀，为了定位到数据行的位置，需要进行二分查找，以找到指定区间。此时就需要将查询条件列都加载到内存中，会消耗较多内存空间，对比使用前缀索引时也会耗时一些（当然也不会特别慢，StarRocks还有zone_map索引等其他加速手段）。

比如在table06中，我们执行如下查询：

SELECT * FROM table06 WHERE user_id=1829239 and age=20；

在大数据量下，该查询的效率会远高于如下查询：

SELECT * FROM table06 WHERE age=20；

所以在建表时，合理的设定列的顺序，能够极大地提高查询效率。