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;