全部产品
云市场

聚集列

更新时间:2019-04-29 15:51:33

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

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

语法

  1. CREATE TABLE table_name (
  2. column_name data_type [NOT NULL][DEFAULT 'default'][COMMENT 'comment'][, …],
  3. primary key (column_name[, ])
  4. )
  5. PARTITION BY HASH KEY(column_name) PARTITION NUM 128
  6. TABLEGROUP table_group_name
  7. [CLUSTERED BY (column_name1,column_name2)]
  8. options (updateType='realtime')

参数

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

聚集列选择依据

  • 主要或大多数的查询条件中均包括某一列,且该查询条件具有较高的筛选率,则选择该列作为聚集列。

  • Join子句中的等值条件列(通常是一级分区列)作为聚集列。

示例

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

  1. CREATE TABLE t_fact_mail_status (
  2. mail_id varchar COMMENT '',
  3. scan_timestamp timestamp COMMENT '',
  4. biz_date bigint COMMENT '',
  5. org_code varchar COMMENT '',
  6. org_name varchar COMMENT '',
  7. dlv_person_name varchar COMMENT '',
  8. receiver_name varchar COMMENT '',
  9. receiver_phone varchar COMMENT '',
  10. receiver_addr varchar COMMENT '',
  11. product_no varchar COMMENT '',
  12. mag_no varchar COMMENT '',
  13. PRIMARY KEY (mail_id,org_code,biz_date)
  14. )
  15. PARTITION BY HASH KEY (org_code) PARTITION NUM 128
  16. SUBPARTITION BY LIST KEY (biz_date)
  17. SUBPARTITION OPTIONS (available_partition_num = 30)
  18. CLUSTERED BY (org_code)
  19. TABLEGROUP ads
  20. OPTIONS (UPDATETYPE='realtime')
  21. COMMENT '';

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

  1. select mail_id,biz_date,org_code,org_name
  2. from t_fact_mail_status
  3. where org_code='203202'and biz_date=20171221;

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