聚集列语法、参数、选择依据和示例-阿里云帮助中心

在分析型数据库MySQL版中，数据存储支持按一列或多列进行排序（先按第一列排序，第一列相同情况下使用第二列排序），以保证该列中值相同或相近的数据保存在磁盘同一位置，这样的列我们称之为聚集列。

当以聚集列为查询条件时，由于查询结果保存在磁盘同一位置，可以减少I/O（Input/Output）次数。分析型数据库MySQL版中主聚集列只有一列，因此需要选择最合适的列作为主聚集列。

语法

CREATE TABLE table_name (
    column_name data_type [NOT NULL][DEFAULT 'default'][COMMENT 'comment'][, …],
    primary key (column_name[, … ])
)
PARTITION BY HASH KEY(column_name) PARTITION NUM 128
TABLEGROUP table_group_name
[CLUSTERED BY (column_name1,column_name2)]
options (updateType='realtime')

参数

绝大多数参数和普通表中介绍一样，CLUSTERED BY用于指定聚集列，您可以把一列或者多列指定为聚集列。

聚集列选择依据

主要或大多数的查询条件中均包括某一列，且该查询条件具有较高的筛选率，则选择该列作为聚集列。
Join子句中的等值条件列（通常是一级分区列）作为聚集列。

示例

现有一张数据表，其一级分区键和聚集列均为org_code，示例如下。

CREATE TABLE t_fact_mail_status (
mail_id varchar COMMENT '',
scan_timestamp timestamp COMMENT '',
biz_date bigint COMMENT '',
org_code varchar COMMENT '',
org_name varchar COMMENT '',
dlv_person_name varchar COMMENT '',
receiver_name varchar COMMENT '',
receiver_phone varchar COMMENT '',
receiver_addr varchar COMMENT '',
product_no varchar COMMENT '',
mag_no varchar COMMENT '',
PRIMARY KEY (mail_id,org_code,biz_date)
)
PARTITION BY HASH KEY (org_code) PARTITION NUM 128
SUBPARTITION BY LIST KEY (biz_date)
SUBPARTITION OPTIONS (available_partition_num = 30)
CLUSTERED BY (org_code)
TABLEGROUP ads
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

设置CLUSTERED BY(org_code)后，org_code记录会按一级分区规则分布在某一个计算节点CN上，并尽可能的存储在同一个数据块上。当以聚集列为查询条件时，相比未设置聚集列的查询，如下SQL语句的访问I/O将减少数百倍。

select mail_id,biz_date,org_code,org_name
from t_fact_mail_status 
where org_code='203202'and biz_date=20171221;

假设共有10万个不同的org_code，每个org_code每天大约有100条~5000条记录。如果org_code=203202的记录为1000条，则按以上方案设置一级分区和聚集列后，这1000条记录会存储在连续的几个数据块上。当您通过以上SQL语句查询数据时，SQL语句只需要扫描这几个数据块即可。