在分析型数据库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语句只需要扫描这几个数据块即可。