1.建表即分片
计算节点默认是需要将表信息配置好之后才能创建表的。但实际使用过程中,用户在接触HotDB Server之初对计算节点不了解,且对分布式没有概念的情况下,可有一种简单的方式从MySQL平缓过渡到HotDB Server,即:建表时根据逻辑库默认关联节点分片的功能。
功能说明: 配置逻辑库时增加默认节点,动态加载后,登录计算节点可以直接建表,无需再配置表信息分片规则,若逻辑库关联一个节点则创建垂直分片表,若逻辑库关联多个节点则创建AUTO_CRC32类型的水平分片表。
使用前提:逻辑库设置默认节点
若后期对逻辑库默认节点进行更改,对更改之前创建的表无影响,只对后续新增的表生效
2.登录计算节点选择“test001”逻辑库,创建表,创建成功:
假设“test001”默认节点只有一个,则“test01”为垂直分片表;假设“test002”默认节点有多个,则“test02”为水平分片表
mysql> use TEST001;
Database changed
mysql> create table test01(id not nullauto_increment primary key,a char(8),b decimal(4,2),c int);
mysql> use TEST002;
Database changed
mysql> create table test02(id not nullauto_increment primary key,a char(8),b decimal(4,2),c int);
自动创建的水平分片表,分片字段选取顺序:主键字段 -> 唯一键字段 ->第一个整型字段(BIGINT、INT、MEDIUMINT、SMALLINT、TINYINT) - >整型字段取完之后,取字符串类型字段(CHAR、VARCHAR ) 以上类型全部取完还没有合适的时候,默认随机选择一个字段作为分片字段。
注意: 此功能仅推荐在初次接触HotDBServer的时候使用,正式交付以及上线不推荐,需要根据实际业务场景做分片。
全局表
全局表的定义请参考《分布式事务数据库HotDB Server【名词解释】功能使用手册》,使用如下语法在服务端创建全局表:
CREATE TABLE [IF NOT EXISTS] tbl_name SHARDBY global on datanode 'datanodeid'(.....
Server端直接创建全局表时, 必须在shardby 后面加global,且在on datanode之前,顺序不可调换。
mysql> CREATE TABLE tb_quan shard by global(idint not null auto_increment primary key,a int(10),b decimanl(5,2),cdecimal(5,2),d date,e time(6),f timestamp(6) DEFAULT CURRENT_TIMESTAMP(6),gdatetime(6) DEFAULT CURRENT_TIMESTAMP(6),h year,I char(20) null,j varchar(30),kblob,l text, m enum('','null','1','2','3'),n set('','null','1','2','3'));
Query OK, 0 rows affected (0.07 sec)
语法规则里的global是创建全局表的标志,'datanodeid'为节点ID,可以逗号间隔,且支持区间形式指定,如:'1,3,4,5-10,12-40',使用该语法创建分片规则的全局表,该表的节点应该包括逻辑库下所有节点。
假设创建全局表的逻辑库里不存在表,需要指定节点:
mysql> CREATE TABLE tb2_quan shard byglobal(id int not null auto_increment primary key,a int(10),b decimanl(5,2),cdecimal(5,2),d date,e time(6),f timestamp(6) DEFAULT CURRENT_TIMESTAMP(6),gdatetime(6) DEFAULT CURRENT_TIMESTAMP(6),h year,i char(20) null,j varchar(30),kblob,l text, m enum('','null','1','2','3'),n set('','null','1','2','3'));
ERROR 10090 (HY000): This table has to specify adatanodes.
假设创建全局表的逻辑库下有表,可直接不指定节点,否则指定的节点必须覆盖逻辑库下的所有节点:
mysql> CREATE TABLE tb1_quan shard by globalon datanodes'9,11'(id int not null auto_increment primary key,a int(10),bdecimanl(5,2),c decimal(5,2),d date,e time(6),f timestamp(6) DEFAULTCURRENT_TIMESTAMP(6),g datetime(6) DEFAULT CURRENT_TIMESTAMP(6),h year,ichar(20) null,j varchar(30),k blob,l text, m enum('','null','1','2','3'),nset('','null','1','2','3'));
ERROR 10090 (HY000): The specified datanodes mustcover all datanodes of the logical database.
使用已有分片规则建表相关命令
此小节介绍的命令可以登录到计算节点管理端口执行。
show hotdb datanodes – 显示当前可用的节点:
此命令用于查看配置库中hotdb_datanodes表,语法:
mysql> show hotdb datanodes [LIKE 'pattern' |WHERE expr];
参数 | 说明 | 类型 |
---|---|---|
pattern | 可选,模糊查询表达式,匹配datanode_name字段 | STRING |
expr | 可选,where条件表达式 | STRING |
结果包含字段及其说明:
列表 | 说明 | 值类型/范围 |
---|---|---|
datanode_id | 节点ID | INTEGER |
datanode_name | 节点名称 | STRING |
datanode_type | 0:主备;1:MGR | INTEGER |
例如:
mysql> show hotdb datanodes;
+------------------+-----------------------+----------------------+
| datanode_id | datanode_name | datanode_type |
+------------------+-----------------------+----------------------+
| 1 | dn_01 | 0 |
| 2 | dn_02 | 0 |
| 4 | dn_04 | 0 |
| 101 | dn_101 | 0 |
| 127 | dn_03 | 0 |
| 186 | dn_199 | 0 |
| 203 | dn_19 | 0 |
+------------------+-----------------------+----------------------+
又如:
mysql> show hotdb datanodes like 'dn_0%';
+-----------------+------------------------+----------------------+
| datanode_id | datanode_name | datanode_type |
+-----------------+------------------------+----------------------+
| 1 | dn_01 | 0 |
| 2 | dn_02 | 0 |
| 4 | dn_04 | 0 |
| 127 | dn_03 | 0 |
+-----------------+-------------------------+---------------------+
show hotdb functions – 显示当前可用的分片函数:
此命令用于查看配置库中hotdb_function表,语法:
mysql> show hotdb functions;
命令包含参数及其说明:
参数 | 说明 | 类型 |
---|---|---|
pattern | 可选,模糊查询表达式,匹配function_name字段 | STRING |
expr | 可选,where条件表达式 | STRING |
结果包含字段及其说明:
列明 | 说明 | 值类型/范围 |
---|---|---|
function_id | 分片函数ID | INTEGER |
function_name | 分片函数名称 | STRING |
function_type | 分片类型 | STRING |
auto_generated | 是否为HotDB自动生成的配置(1:自动生成,其他:非自动生成) | INTEGER |
例如:
mysql> show hotdb functions;
+----------------+------------------------+---------------------+-----------------------+
| function_id | function_name | function_type | auto_generated |
+----------------+------------------------+---------------------+-----------------------+
…省略更多…
| 40 | AUTO_CRC32_8 |AUTO_CRC32 | 0 |
| 41 | th_fun_range | RANGE | 0 |
| 42 | AUTO_MOD_5 |AUTO_MOD | 0 |
| 43 | 43_RANGE | RANGE | 0 |
| 44 | AUTO_CRC32_15|AUTO_CRC32 | 0 |
| 45 | AUTO_CRC32_5 |AUTO_CRC32 | 0 |
| 46 | yds_RANGE | RANGE | 0 |
| 47 | AUTO_CRC32_11 |AUTO_CRC32 | 0 |
+----------------+--------------------------+---------------------+---------------------+
又如:
mysql> show hotdb functions like '%range%';
+----------------+---------------------+--------------------+----------------------+
| function_id | function_name | function_type |auto_generated |
+----------------+---------------------+---------------------+---------------------+
| 41 | th_fun_range | RANGE | 0 |
| 43 | 43_RANGE | RANGE | 0 |
| 46 | yds_RANGE | RANGE | 0 |
+----------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> show hotdb functions wherefunction_name like '%range%';
+----------------+---------------------+--------------------+----------------------+
| function_id | function_name | function_type |auto_generated |
+----------------+---------------------+--------------------+----------------------+
| 41 | th_fun_range | RANGE | 0 |
| 43 | 43_RANGE | RANGE | 0 |
| 46 | yds_RANGE | RANGE | 0 |
+----------------+---------------------+--------------------+----------------------+
3 rows in set (0.00 sec)
show hotdb function infos - 显示当前可用的分片函数信息:
此命令用于查看配置库中hotdb_function_info表,语法:
mysql> show hotdb function infos [WHERE expr];
命令包含参数及其说明:
参数 | 说明 | 类型 |
---|---|---|
expr | 可选,where条件表达式 | STRING |
结果包含字段及其说明:
参数 | 说明 | 值类型/范围function_id |
---|---|---|
function_id | 分片函数ID | INTEGER |
column_value | 分片字段的值 | STRING |
datanode_id | 数据节点id | INTEGER |
例如:
mysql> show hotdb function infos;
+----------------+--------------------+------------------+
| function_id | column_value | datanode_id |
+----------------+--------------------+------------------+
| 2 | 4 | 0 |
| 3 | 1 | 0 |
| 4 | 2 | 0 |
| 31 | '' | 4 |
| 31 | 1 | 1 |
| 31 | 2 | 2 |
| 31 | null | 127 |
| 33 | 0:1 | 1 |
| 33 | 10:10 | 191 |
| 33 | 11:11 | 186 |
| 33 | 12 | 0 |
| 33 | 2:3 | 2 |
…省略更多…
又如:
mysql> show hotdb function infos wherefunction_id=38;
+----------------+--------------------+------------------+
| function_id | column_value | datanode_id |
+----------------+--------------------+------------------+
| 38 | 10:12 | 1 |
| 38 | 1:2 | 1 |
| 38 | 20 | 0 |
| 38 | 4:8 | 1 |
+----------------+-------------------+-------------------+
4 rows in set (0.00 sec)
4. show hotdb rules –显示当前可用的分片规则:
此命令用于查看配置库中hotdb_rule 表,语法:
mysql> show hotdb rules [LIKE 'pattern' |WHERE expr];
命令包含参数及其说明:
参数 | 说明 | 类型 |
---|---|---|
pattern | 可选,模糊查询表达式,匹配rule_name字段 | STRING |
expr | 可选,where条件表达式 | STRING |
结果包含字段及其说明:
列明 | 说明 | 值类型/范围 |
---|---|---|
rule_id | 分片规则ID | INTEGER |
rule_name | 分片规则名称 | STRING |
rule_column | 分片字段名称 | STRING |
function_id | 分片类型ID | INTEGER |
auto_generated | 是否为HotDB自动生成的配置(1:自动生成,其他:非自动生成) | INTEGER |
例如:
mysql> show hotdb rules;
+---------+----------------------------------------------------------------------------+---------------+-----------------+----------------------+
| rule_id | rule_name |rule_column| function_id | auto_generated |
+---------+----------------------------------------------------------------------------+---------------+-----------------+----------------------+
| 21 |AUTO_GENERATE_3_JOIN_A_JWY | ID | 1 | 1 |
| 22 |hotdb-cloud_0374c02e-58a7-4263-9b80-9c5b46fb42af | id | 5 | 0 |
| 25 |hotdb-cloud_f3979d19-93cb-4925-8dee-e4fbf8803c7c | id | 5 | 0 |
| 32 |hotdb-cloud_6ccd2f69-cf53-4e81-ab3d-61345134fb7a | id | 5 | 0 |
| 33 |hotdb-cloud_b5bc16e6-3481-40ed-83ff-e81d488e47a5 | ID | 4 | 0 |
…省略更多…
又如:
mysql> show hotdb rules like '%auto%';
+---------+----------------------------------------------------+------------------+-----------------+----------------------+
| rule_id | rule_name | rule_column | function_id |auto_generated |
+---------+----------------------------------------------------+-------------------+----------------+----------------------+
| 21 |AUTO_GENERATE_3_JOIN_A_JWY | ID | 1 | 1 |
| 50 |AUTO_GENERATE_9_FT_ADDR | ID | 26 | 1 |
| 64 |AUTO_GENERATE_23_S03 | A | 1 | 1 |
| 65 |AUTO_GENERATE_23_S04 | B | 1 | 1 |
…省略更多…
mysql> show hotdb rules where rule_name like'%auto%';
+---------+----------------------------------------------------+------------------+-----------------+----------------------+
| rule_id | rule_name | rule_column | function_id |auto_generated |
+---------+----------------------------------------------------+------------------+-----------------+----------------------+
| 21 |AUTO_GENERATE_3_JOIN_A_JWY | ID | 1 | 1 |
| 50 |AUTO_GENERATE_9_FT_ADDR | ID | 26 | 1 |
| 64 |AUTO_GENERATE_23_S03 | A | 1 | 1 |
| 65 |AUTO_GENERATE_23_S04 | B | 1 | 1 |
…省略更多…
功能说明: 根据管理平台已添加好的分片规则,在计算节点服务端利用特殊语句直接建表,无需再配置表分片信息。利用已有分片规则所建的表,删除表后,管理平台相关表配置信息会同步被删除。
使用前提:已存在分片规则,添加分片规则请参考《分布式事务数据库HotDB Server【管理平台】功能使用手册》。
1).利用服务端口命令:
查看分片规则的functionid| functionname| functiontype| ruleid | rulename等信息,根据相关字段信息创建表。
mysql> show hotdb functions;
+---------------+----------------------------------------+--------------------+-----------------------+
| function_id | function_name | function_type | auto_generated |
+---------------+-----------------------------------------+--------------------+----------------------+
| 1 | AUTO_GENERATE_CRC32 | AUTO_CRC32 | 1 |
| 2 | AUTO_CRC32_4 |AUTO_CRC32 | 0 |
| 3 | AUTO_CRC32_1 |AUTO_CRC32 | 0 |
| 26 | AUTO_GENERATE_MOD |AUTO_MOD | 1 |
| 31 | 29__MATCH1 | MATCH | 0 |
| 33 | 32_SIMPLE_MOD | SIMPLE_MOD | 0 |
| 36 | 36_SIMPLE_MOD |SIMPLE_MOD | 0 |
| 37 | 37_CRC32_MOD |CRC32_MOD | 0 |
+---------------+-----------------------------------------+-----------------------+--------------------+
8 rows in set (0.01 sec)
mysql> show hotdb rules;
+---------+----------------------------------------------------------------------------+---------------+----------------+-----------------------+
| rule_id | rule_name |rule_column | function_id | auto_generated |
+---------+----------------------------------------------------------------------------+----------------+----------------+----------------------+
| 4 |hotdb-cloud_555f9d00-27c3-4eaa-860c-309312672908 | id | 3 | 0 |
| 12 |hotdb-cloud_8b7d9b8d-f711-476c-aa33-a4e2af184ab5 | adnid | 2 | 0 |
| 13 |hotdb-cloud_7f8fff18-6016-47f1-ab0a-1912f5b75523 | adnid | 2 | 0 |
| 21 |AUTO_GENERATE_3_JOIN_A_JWY | ID | 1 | 1 |
| 50 |AUTO_GENERATE_9_FT_ADDR | ID | 26 | 1 |
| 64 |AUTO_GENERATE_23_S03 | A | 1 | 1 |
+---------+----------------------------------------------------------------------------+---------------+------------------+---------------------+
6 rows in set (0.01 sec)
2.1.水平分片表
水平分片表的定义请参考《分布式事务数据库HotDB Server【名词解释】功能使用手册》,使用如下语法直接引用分片函数创建水平分片表:
CREATE TABLE [IF NOT EXISTS] tbl_name SHARDBY {functionid | functionname} 'functionid | functionname' USING COLUMN'shardcolumnname' (.....
CREATE TABLE [IF NOT EXISTS] tbl_name SHARDBY {functiontype} 'functiontype' USING COLUMN 'shardcolumnname' on datanode'datanodeid'(.....
登录服务端,切换逻辑库,输入建表语句,执行成功。 管理平台会显示该表为已定义状态:
mysql> use fun_zy
Database changed
mysql> CREATE TABLE match1_tb shard byfunctionname 'test_match1' using column 'aname' (id INT UNSIGNED NOT NULLAUTO_INCREMENT PRIMARY KEY, adnid INT DEFAULT NULL, aname VARCHAR(32) DEFAULT'', adept VARCHAR(40), adate datetime DEFAULT NULL)ENGINE =INNODB;
Query OK, 0 rows affected (0.09 sec)
对于此语法规则建表,需要注意以下几点:
mysql> show hotdb datanodes;
+------------------+-------------------------+------------------------+
| datanode_id | datanode_name | datanode_type |
+------------------+-------------------------+-------------------------+
| 9 | dn_01 | 0 |
| 11 | dn_02 | 0 |
| 13 | dn_03 | 0 |
| 15 | dn_04 | 0 |
| 19 | dn_failover | 0 |
| 20 | dn_rmb_01 | 0 |
+------------------+-------------------------+-----------------------+
6 rows in set (0.00 sec)
functiontype 只支持 auto_crc32/auto_mod,若使用了其他类型会提示:ERROR:The fucntiontype can only beauto_crc32/auto_mod.
mysql> create table ft_match shard byfunctiontype 'match' using column 'id' on datanode '11,13'(id int(10) primarykey, a char(20) not null);
ERROR 10070 (HY000): The functiontype can only byauto_crc32/auto_mode.
使用functionid |functionname建表时,当指定的function信息关联的function_type 是auto_crc32/auto_mod 时,需要指定on datanode 'datanodes' ,否则会提示:The functionmust be specified datanodes。 如果是其他类型,则无需指定。
mysql> create table mod_ft shard by functionid'15' using column 'id'(id int(10) primary key, a char(20) not null);
ERROR 10090 (HY000): The function must bespecified datanodes.
mysql> create table testsa shard by functionid'3' using column 'id'(id int,a int);
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> CREATE TABLE match_tb shard byfunctionname 'test_match1' using column 'ananme' on datanode '1,2'(id INTUNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, adnid INT DEFAULT NULL, anameVARCHAR(32) DEFAULT '', adept VARCHAR(40), adate datetime DEFAULT NULL)ENGINE=INNODB;
ERROR 10090 (HY000): This rule doesn't need tospecify a datanodes;
表结构类似的表可以使用相同的分片规则,使用如下语法可直接引用分片规则创建水平分片表:
CREATE TABLE [IF NOT EXISTS] tbl_name SHARDBY {ruleid | rulename} 'ruleid\\rulename' [on datanode 'datanodes'] (......
登录计算节点服务端口使用命令,show hotdb rules;和show hotdbfunctions;可以看到与之分片函数关联的分片规则:
mysql> show hotdb rules;
+---------+----------------------------------------------------+-----------------+----------------+------------------------+
| rule_id | rule_name |rule_column | function_id | auto_generated |
+---------+----------------------------------------------------+------------------+----------------+-----------------------+
| 17 | AUTO_GENERATE_3_ROUTE1_TB | A | 1 | 1 |
+---------+----------------------------------------------------+------------------+----------------+-----------------------+
21 rows in set (0.01 sec)
mysql> show hotdb functions;
+----------------+--------------------+-------------------+-----------------------+
| function_id | function_name| function_type| auto_generated |
+----------------+--------------------+-------------------+-----------------------+
| 1 | test_route1 | ROUTE | 1 |
+----------------+--------------------+-------------------+-----------------------+
13 rows in set (0.01 sec)
用户可用ruleid/rulename来直接创建表:
mysql> CREATE TABLE rt_table shard by ruleid'17'(id int not null auto_increment primary key,a int(10),b decimanl(5,2),cdecimal(5,2),d date,e time(6),f timestamp(6) DEFAULT CURRENT_TIMESTAMP(6),gdatetime(6) DEFAULT CURRENT_TIMESTAMP(6),h year,I char(20) charcter set utf8,jvarchar(30) character set utf8mb4,k char(20) character set gbk,l text characterset latin1, m enum('','null','1','2','3'),n set('','null','1','2','3'));
Query OK, 0 rows affected (0.07 sec)
对于此语法规则建表,需要注意以下几点:
当指定的rule关联的function_type是auto_crc32/auto_mod 时,需要指定on datanode 'datanodes';如果是其他类型,则无需指定datanode。
mysql> show hotdb rules;
+----------+--------------------------------------------------------+------------------+----------------+-----------------------+
| rule_id | rule_name |rule_column | function_id | auto_generated |
+----------+--------------------------------------------------------+-----------------+-----------------+-----------------------+
| 17 | AUTO_GENERATE_3_ROUTE1_TB | A | 1 | 1 |
rows in set (0.01 sec)
mysql> show hotdb functions;
+----------------+---------------------+-------------------+------------------------+
| function_id | function_name | function_type| auto_generated |
+----------------+---------------------+-------------------+------------------------+
| 1 | test_route1 | ROUTE | 1 |
+----------------+---------------------+-------------------+-------------------------+
13 rows in set (0.01 sec)
mysql> CREATE TABLE route2_rptb1 shard byrulename 'AUTO_GENERATE_3_ROUTE1_TB' on datanode '9,11,13'(id int not nullauto_increment primary key,a int(10),b decimanl(5,2),c decimal(5,2),d date,etime(6),f timestamp(6) DEFAULT CURRENT_TIMESTAMP(6),g datetime(6) DEFAULTCURRENT_TIMESTAMP(6),h year,i char(20) charcter set utf8,j varchar(30)character set utf8mb4,k char(20) character set gbk,l text character set latin1,m enum('','null','1','2','3'),n set('','null','1','2','3'));
ERROR 10090 (HY000): This rule doesn't need tospecify a datanodes;
当指定的rule关联的function_type是auto_crc32/auto_mod 时, 指定的datanode 个数与参数不符时,则会提示:ERROR:The totalnumber of datanodes must be XXX(XXX为实际ruleid 关联的 function info的 column_value值):
mysql> CREATE TABLE auto_c shard by ruleid'63' on datanode '9'(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, adnidINT DEFAULT NULL, aname VARCHAR(32) DEFAULT '',adept VARCHAR(40), adatedatetime DEFAULT NULL)ENGINE=INNODB;
ERROR 10090 (HY000): The total number ofdatanodes must be 2
mysql> CREATE TABLE auto_c shard by ruleid'63' on datanode '9,15'(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,adnid INT DEFAULT NULL, aname VARCHAR(32) DEFAULT '',adept VARCHAR(40), adatedatetime DEFAULT NULL)ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)
2.2.垂直分片表
垂直分片表的定义请参考《分布式事务数据库HotDB Server【名词解释】功能使用手册》,使用如下语法规则在服务端创建垂直分片表:
CREATE TABLE [IF NOT EXISTS] tbl_name SHARDBY vertical on datanode 'datanodeid'(.....
Server端直接创建垂直分片表时, 必须在shard by 后面加vertical,且在on datanode之前,顺序不可调换。
mysql> CREATE TABLE tb_vertical shard byvertical on datanode'9'( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,adnid INT DEFAULT NULL, aname VARCAHR(32) DEFAULT '', adept VARCHAR(40), adateDATETIME DEFAULT NULL)ENGINE=INNODB;
Query OK, 0 rows affected(0.07 sec)
语法规则里的vertical是创建垂直分片表的标志,且datanodeid 仅能指定一个,若超过,会有提示:ERROR:Can onlyspecify one datanode.
未指定datanode:
mysql> CREATE TABLE tb1_vertical shard byvertical( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, adnid INTDEFAULT NULL, aname VARCAHR(32) DEFAULT '', adept VARCHAR(40), adate DATETIMEDEFAULT NULL)ENGINE=INNODB;
ERROR 10090 (HY000): This table has to specify adatanodes.
指定多个节点:
mysql> CREATE TABLE tb1_vertical shard byvertical on datanode'9,11,13'( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARYKEY, adnid INT DEFAULT NULL, aname VARCAHR(32) DEFAULT '', adept VARCHAR(40),adate DATETIME DEFAULT NULL)ENGINE=INNODB;
ERROR 10090 (HY000): Can only specify onedatanodes.
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论0 条评论