425      微信

DRDS DDL语句__开发手册_分布式关系型数据库 DRDS-阿里云

DRDS 建表DDL

本文档将指导您通过SQL语句的方式在DRDS上创建应用所需的某个需要水平切分的逻辑表。

步骤0

您首先需要成功申请了DRDS实例。 申请链接 https://buy.aliyun.com/drds?spm=5176.2020520107.0.0.UepMfR&loading#/loading

步骤1 切分方案

接下来您需要确定分库分表的方案，比如这个逻辑表按照哪个列进行切分，数据分布到多少个库上，每个库上有几个物理表，以及数据将按照什么规则进行切分(是哈希还是日期的信息，一般对于name和id用hash，对日期用日期函数)。

注意： 因为DRDS支持最高到5.6版本的MySQL，如果客户选用的是5.5版本的RDS，则有些新的5.6的建表选项可能会不支持。

步骤2 建分库

根据切分方案您需要登陆DRDS console进行建库操作，操作步骤请参考DRDS控制台使用https://docs.aliyun.com/?spm=5176.2020520107.103.5.xuvE85&pos=1#/pub/drds/quick-start/console_use 。 (目前还不支持DDL直接进行建分库操作)

步骤3 建表

接着您可以在多个地方使用DDL SQL语句建表:

1) 使用MySQL兼容客户端建立了与DRDS的连接后手工输入执行

2) 在程序中通过连接MySQL调用DDL SQL的方式执行

3) 从DRDS控制台的登录数据库按钮登录到DMS

4) 在DRDS控制台执行新建表/分布式DDL

其中第4种方式与其他3种方式有区别，第4种方式主要是在DRDS控制台的执行DDL，这种方式只能建单表或者分库的表，而不能建分库分表，也就是说如果在DRDS控制台建表要么建单表，要么建只分库的表。由于控制台执行建表语句有一个步骤设置分库键，所以控制台的建表语句不支持下面将要介绍到的DBPARTITION BY，TBPARTITION BY，TBPARTITIONS，BROADCAST等语句。

您在手工建表的时候需要指定几个关键参数:

1) DBPARTITION BY hash(partition_key): 指定分库键和分库算法

2) TBPARTITION BY(可选): 默认与DBPARTITION相同 指定数据与物理表使用什么方式进行映射(指定分表键)

3) TBPARTITIONS(可选): 每个库上的物理表数目(默认为1)，如您无需分表，则无需指定该字段

4) BROADCAST(与DBPARTITION BY互斥): 指定建广播表

MySQL 5.6语法请参考https://dev.mysql.com/doc/refman/5.6/en/create-table.html (DRDS在此基础上针对分库分表功能语法有所扩充)。

举例，现在假设已经建好的分库数为8：

a) 如果您希望建一个单库单表，不做任何切分：

CREATE TABLE single_tbl
    (id int, name varchar(30), primary key(id));

b) 如果您希望建一个表，只分库但不分表，且切分方式为根据id列哈希(会根据id列的值进行hash运算后的结果将表中数据分布在多个子库中)

CREATE TABLE multi_db_single_tbl
    (id int, name varchar(30), primary key(id)) dbpartition by hash(id);

c) 如果您希望建一个分表，每个分库含有3张物理表，切分方式都为按照id列进行哈希(会根据id列的值进行hash运算后的结果将表中数据分布在多个子库中，每个子库中的数据再根据运算结果分布在3个物理表中)

CREATE TABLE multi_db_multi_tbl
    (id int, name varchar(30), primary key(id))
    dbpartition by hash(id) tbpartition by hash(id) tbpartitions 3;

d) 如果您希望建一个分表，每个库含有3张物理表，切分方式为库按照id列进行哈希，物理表按照id2列进行哈希(会根据id列的值进行hash运算后的结果将表中数据分布在多个子库中，每个子库中的数据再根据id2列值的hash运算结果分布在3个物理表中)

CREATE TABLE multi_db_multi_tbl
    (id int auto_increment, id2 int, name varchar(30), primary key(id))
    dbpartition by hash(id) tbpartition by hash(id2) tbpartitions 3;

除了可以使用哈希来做映射策略，还可以使用日期类策略MM/DD/WEEK/MMDD来进行映射，但只限于对物理表的映射时可用，这种方式大部分用于将不同的日期的数据进行物理表集中，如业务员操作日志。

e) 公司A的日志系统记录营业员进行的所有操作，按照营业员id以及按照一周七天来切分

CREATE TABLE user_log
    (userId int, name varchar(30), operation varchar(30), actionDate DATE)
    dbpartition by hash(userId) tbpartition by WEEK(actionDate) tbpartitions 7;

f) 如果按照一年12个月进行日志记录，上面的例子可以改为

CREATE TABLE user_log
    (userId int, name varchar(30), operation varchar(30), actionDate DATE)
    dbpartition by hash(userId) tbpartition by MM(actionDate) tbpartitions 12;

g) 如果按照一个月31天进行日志记录，上面的例子可以改为

CREATE TABLE user_log
    (userId int, name varchar(30), operation varchar(30), actionDate DATE)
    dbpartition by hash(userId) tbpartition by DD(actionDate) tbpartitions 31;

h) 如果按照一年365天进行日志记录，上面的例子可以改为，但这样将在每个分库中创建365个物理表(好多表啊~~~)

CREATE TABLE user_log
    (userId int, name varchar(30), operation varchar(30), actionDate DATE)
    dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 365;

i) 上面的例子里创建的物理表太多了，如果您希望数据可以在每个分库中只哈希到10个物理表

CREATE TABLE user_log
    (userId int, name varchar(30), operation varchar(30), actionDate DATE)
    dbpartition by hash(userId) tbpartition by MMDD(actionDate) tbpartitions 10;

j) 也可以同时指定其他的MySQL建表属性，如

CREATE TABLE multi_db_multi_tbl
    (id int, name varchar(30), primary key(id))
    ENGINE=InnoDB DEFAULT CHARSET=utf8
    dbpartition by hash(id) tbpartition by hash(id) tbpartitions 3;

k) 建广播表

CREATE TABLE brd_tbl
    (id int, name varchar(30), primary key(id))
    ENGINE=InnoDB DEFAULT CHARSET=utf8
    BROADCAST;

l) 使用主键当分库键

CREATE TABLE prmkey_tbl
    (id int, name varchar(30), primary key(id))
    dbpartition by hash();

m) 使用主键当分库分表键

CREATE TABLE prmkey_multi_tbl
    (id int, name varchar(30), primary key(id))
    dbpartition by hash() tbpartition by hash() tbpartitions 3;

步骤4 使用中对表结构的修改

如果在日常DML中发现原始创建的逻辑表需要修改，比如增加某些列，修改数据定义，也可以使用DDL语句的方式执行。语句可以参考MySQL的语法https://dev.mysql.com/doc/refman/5.6/en/alter-table.html 。

注意： 因为在建表的时候指定了切分列，在修改表的操作中不允许修改切分的列

a) 客户可以添加一个新的列来记录操作人员的身份证信息

ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);

b) 增加索引

ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);

c) 删除索引

ALTER TABLE user_log
    DROP INDEX idcard_idx;

d) 修改字段

ALTER TABLE user_log
    MODIFY COLUMN idcard varchar(40);

步骤5 需要删除表时

删除切分的表和一般的表没有任何区别，系统会自动处理相关物理表的删除操作，语法请参考https://dev.mysql.com/doc/refman/5.6/en/drop-table.html 。

客户删除原始创建逻辑表

DROP TABLE user_log;

DDL语法说明

DRDS兼容MySQL的DDL表操作语法，并添加了drds_partition_options的分库分表关键字如下

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [drds_partition_options]
    [partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [drds_partition_options]
    [partition_options]
    select_statement
drds_partition_options:
    DBPARTITION BY
        HASH([column])
        [TBPARTITION BY
            { HASH(column)
            | {MM|DD|WEEK|MMDD}(column)}
          [TBPARTITIONS num]
        ]
ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Tips: DBPARTITION BY 不支持按照时间分库

SHOW TOPOLOGY FROM multi_db_multi_tbl;
+------+-----------------+-----------------------+
| ID   | GROUP_NAME      | TABLE_NAME            |
+------+-----------------+-----------------------+
|    0 | corona_qatest_0 | multi_db_multi_tbl_00 |
|    1 | corona_qatest_0 | multi_db_multi_tbl_01 |
|    2 | corona_qatest_0 | multi_db_multi_tbl_02 |
|    3 | corona_qatest_1 | multi_db_multi_tbl_03 |
|    4 | corona_qatest_1 | multi_db_multi_tbl_04 |
|    5 | corona_qatest_1 | multi_db_multi_tbl_05 |
|    6 | corona_qatest_2 | multi_db_multi_tbl_06 |
|    7 | corona_qatest_2 | multi_db_multi_tbl_07 |
|    8 | corona_qatest_2 | multi_db_multi_tbl_08 |
|    9 | corona_qatest_3 | multi_db_multi_tbl_09 |
|   10 | corona_qatest_3 | multi_db_multi_tbl_10 |
|   11 | corona_qatest_3 | multi_db_multi_tbl_11 |
+------+-----------------+-----------------------+
12 rows in set (0.21 sec)

CREATE TABLE IF NOT EXISTS table1
    (id int, name varchar(30), primary key(id))
    dbpartition by hash(id);
DROP TABLE IF EXISTS table1;