阿里云分布式关系型数据库DRDS DDL语法

DRDS DDLSQL语法创建拆分表

DRDS的自动分库分表是采用拆分表来实现的,即将原先的一个大表拆分为分布在多个数据库中的分表,每个分表只存储一部分数据,从而提高了对表增删改查的性能。

虽然DRDS将一个大表拆分成了多个小表,但这对用户是透明的,用户还是可以使用原先的表名访问所有分表中的数据,DRDS会根据SQL语句中拆分字段的值来判断将SQL语句发往哪个分表中执行。

因此,在DRDS中定义一个拆分表跟传统的非拆分表相比,主要有两个关键的地方需要定义:

  • 拆分字段:使用哪个字段对表中的数据进行拆分。
  • 拆分函数:使用什么算法对表中的数据进行拆分。

语法

CREATETABLEtable_name(create_definition,...)[DRDSpartitionoptions]

[DRDSPartitionOptiosn]语法

dbpartitionbyINT_MOD([column_name])|STRING_HASH([column_name])|YYYYMM([column_name])START([start_date])PERIOD[num]|YYYY([column_name])START([start_date])PERIOD[num]

注意事项

  • [DRDSPartitionOptiosn]部分的语法必须在放在最后
  • 表的主键必须是拆分字段
  • 对于按时间和日期拆分的表,每个分表只存储一个时间段的数据,不会循环使用。当分表用尽后,用户需要手动对分表进行扩展。例如,用户的某个DRDS数据库有16个分库,其中表tab1按年月拆分,每个月一个分表,因此总共可以存储16个月的数据。如果用户需要存储更多时间段的数据,需要手动增加分库数目,并对表进行扩展。

拆分函数

目前DRDS支持以下的拆分函数,函数名不区分大小写

  • INT_MOD():对整型字段进行拆分,支持int,smallint,bigint,tinyint,mediumint
  • STRING_HASH():对字符字段进行拆分,支持char,varchar
  • YYYYMM():对时间,日期字段进行拆分,按月拆分,支持timestamp,date,datetime
  • YYYY():时间,日期字段进行拆分,按年拆分,支持timestamp,date,datetime

关键字:(不区分大小写)

  • START:按时间拆分时,数据的起始时间,格式为‘YYYY’或者‘YYYY-MM’,其他格式将不被接受,例如start('2018')或start('2018-05')
  • PERIOD:按时间拆分时,每多少时间周期的数据放入到一个分表中,例如每3个月,或每2年的数据放入一个分表中

示例

  • 按整型字段拆分
  • createtableddl_demo1(idint,namevarchar(10)default‘’,deptvarchar(10)notnull,primarykey(id))ENGINE=InnoDBDEFAULTCHARSET=utf8dbpartitionbyint_mod(id);

  • 按字符字段拆分
  • createtableddl_demo2(idint,namevarchar(10)default‘’deptvarchar(10)notnull,primarykey(name))ENGINE=InnoDBDEFAULTCHARSET=utf8dbpartitionbystring_hash(name);

  • 使用YYYYMM函数,数据的起始时间为2019年5月,每3个月的数据放入一个分表中
  • createtableddl_demo3(order_idint,order_datedatetime)ENGINE=InnoDBDEFAULTCHARSET=utf8dbpartitionbyYYYYMM(order_date)start('2019-05')period3;

  • 使用YYYY函数,数据的起始时间为2000年,每2年的数据放入一个分表中
  • createtableddl_demo4(order_idint,order_datedatetime)ENGINE=InnoDBDEFAULTCHARSET=utf8dbpartitionbyYYYY(order_date)start('2000')period2;删除拆分表

删除表的语法为标准SQL

droptabletable_name1,table_name2,table_name3,......

例如:

droptableddl_demo1,ddl_demo2,ddl_demo3,ddl_demo4;扩展拆分表

备注:只支持按时间或日期进行拆分的表

对于按日期时间进行拆分的表,每个分表只存储一个时间段的数据,不会循环使用。当分表用尽后,用户需要手动对分表进行扩展。扩展分表有两个步骤:

  • 通过控制台添加分库
  • 连接到DRDS数据库中,执行SQL,扩展相应的表

例如,用户的某个DRDS数据库有24个分库,其中表tab1按年月拆分,每个月一个分表,因此总共可以存储24个月的数据。如果用户需要存储更多时间段的数据,需在控制台上,添加新的分库,并通过SQL扩展分表。

1.在所有新增的分库中扩展分表

在所有新增的分库中扩展分表,推荐使用这种方式

altertabletablenameaddpartitionsonalldbpartitions;

例如

altertabledemo_timetbaddpartitionsonalldbpartitions;

2.在指定的分库中扩展分表

如果只想在特定的分库上扩展分表,可以使用下面的SQL:

altertabletablenameaddpartitionsonsubdbname1,subdbname1,subdbname1,.......

subdbname:是DRDS在RDSMySQL上的分库名,可以在控制台阿里云数据库详情中【库管理】页面中查看当前数据库的所有分库名

例如在分库db1_drds_593c_17,db1_drds_593c_18,db1_drds_593c_19,db1_drds_593c_20中扩展分库

altertabledemo_timetbaddpartitionsondb1_drds_593c_17,db1_drds_593c_18,db1_drds_593c_19,db1_drds_593c_20;删除拆分表的分区

备注:只支持按时间或日期进行拆分的表

对于按时间或日期进行拆分的表,可以通过直接删除某个分库上的分表进行历史数据的高效批量清理,预发如下:

altertabletablenamedropdbpartitionsubdbname;

subdbname:是DRDS在RDSMySQL上的分库名,可以在控制台阿里云数据库详情中【库管理】页面中查看当前数据库的所有分库名

例如表timetb在分库db1_drds_593c_17的存储的历史数据是24个月以前的,不再需要,可以通过下面的SQL直接清除。

altertabletimetbdropdbpartitiondb1_drds_593c_17;