mysql分片字段类型_Mycat 分片策略

最新推荐文章于 2022-08-10 22:55:01 发布

Pickteemo

最新推荐文章于 2022-08-10 22:55:01 发布

阅读量591

点赞数

文章标签： mysql分片字段类型

本文链接：https://blog.csdn.net/weixin_33181210/article/details/114832384

版权

@(学习)[mycat, mysql]

1. 分片规则配置文件

分片规则配置文件rule.xml位于$MYCAT_HOME/conf目录，它定义了所有拆分表的规则。在使用过程中可以灵活使用不同的分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化，只需要简单的几步就可以让运维人员及数据库管理员轻松将数据拆分到不同的物理库中。该文件包含两个重要的标签，分别是Funcation和tableRule。

1.1 Funcation标签

class="io.mycat.route.function.AutoPartitionByLong">

autopartition-long.txt

name属性指定算法的名称，在该文件中唯一。

class属性对应具体的分片算法，需要指定算法的具体类。

property属性根据算法的要求指定。

1.2 tableRule标签

rang-long

name属性指定分片规则的名称，在该文件中唯一。

rule属性指定分片算法的具体内容，包含columns和algorithm两个属性。

columns属性指定对应的表中用于分片的列名。

algorithm属性对应function中指定的算法的名称。

2. 连续分片和离散分片

分片优缺点

分片连续分片离散分片

优点

扩容无需迁移数据

范围条件查询消耗资源少

并发访问能力增强

范围条件查询性能提升

缺点

存在数据热点的可能性

并发访问能力受限于单一或少量DataNode

数据扩容比较困难，涉及到数据迁移问题

数据库连接消耗比较多

特殊分片

2.1 连续分片

连续分片规则

* 自定义数字范围分片

* 按日期(天)分片

* 按单月小时分片

* 自然月分片

2.1.1 自定义数字范围分片

适用于明确知道分片字段的某个范围属于哪个分片，其字段为数字类型。

vim conf/schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

vim conf/rule.xml

age

rang-long

class="io.mycat.route.function.AutoPartitionByLong">

autopartition-long.txt

配置说明：

* columns指定分片的表列名。

* algorithm指定分片函数与function对应。

* rang-long函数中的mapFile代表规则配置文件的路径，可在$MYCAT_HOME/conf下新建目录作好规范，比如将autopartition-long.txt放在$MYCAT_HOME/conf/func中，则用func/autopartition-long.txt来引用。

* defaultNode为超过范围后的默认节点。

vim conf/autopartition-long.txt

# range start-end ,data node index

# K=1000,M=10000.

0-2M=0

2M-3M=1

3M-6M=2

测试sql：

CREATE TABLE auto_sharding_long (`age` int NOT NULL ,`db_nm` varchar(20) NULL);

INSERT INTO auto_sharding_long (age,db_nm) VALUES (20000, database());

INSERT INTO auto_sharding_long (age,db_nm) VALUES (25000, database());

INSERT INTO auto_sharding_long (age,db_nm) VALUES (35000, database());

测试结果：

[root@testA conf]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 test

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE auto_sharding_long (`age` int NOT NULL ,`db_nm` varchar(20) NULL);

Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO auto_sharding_long (age,db_nm) VALUES (20000, database());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO auto_sharding_long (age,db_nm) VALUES (25000, database());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO auto_sharding_long (age,db_nm) VALUES (35000, database());

Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_sharding_long;

+-------+-------+

| age | db_nm |

+-------+-------+

| 20000 | db0 |

| 35000 | db2 |

| 25000 | db1 |

+-------+-------+

3 rows in set (0.00 sec)

mysql>

注意

schema里的table的dataNode节点个数必须大于等于 autopartition-long.txt里的配置个数

dataNode节点从0开始

2.1.2 按日期(天)分片

根据时间类型字段，按天分片。

vim conf/schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

vim conf/rule.xml

create_time

part-by-day

class="io.mycat.route.function.PartitionByDate">

yyyy-MM-dd

2017-10-01

配置说明：

* columns标识将要分片的表字段。

* algorithm为分片函数。

* dateFormat为日期格式。

* sBeginDate为开始日期。

* sEndDate为结束日期，如果配置了这个属性，则代表数据达到了这个日期的分片后会重复从开始分片插入。

* sPartionDay为分区天数，默认从开始日期算起，每隔10天一个分区。

测试SQL：

CREATE TABLE sharding_by_day (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL);

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-01', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-10', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-11', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-21', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-31', database());

select * from sharding_by_day;

测试结果：

[root@testA conf]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 test

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE sharding_by_day (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL);

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-01', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-10', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-11', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-21', database());

INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-31', database());

select * from sharding_by_day; Query OK, 0 rows affected (0.24 sec)

mysql> INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-01', database());

Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-10', database());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-11', database());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-21', database());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sharding_by_day (create_time,db_nm) VALUES ('2017-10-31', database());

ERROR 1064 (HY000): Can't find a valid data node for specified node index :SHARDING_BY_DAY -> CREATE_TIME -> 2017-10-31 -> Index : 3

mysql> select * from sharding_by_day;

+---------------------+-------+

| create_time | db_nm |

+---------------------+-------+

| 2017-10-01 00:00:00 | db0 |

| 2017-10-10 00:00:00 | db0 |

| 2017-10-11 00:00:00 | db1 |

| 2017-10-21 00:00:00 | db2 |

+---------------------+-------+

4 rows in set (0.05 sec)

mysql>

注意

以上测试结果中有个错误，因为配置的dataNode节点数为3，而2017-10-31为2017-10-01后的第四个10天中的第一天，因此需要至少4个dataNode，节点不够就报如上错误了。

2.1.3 按单月小时分片

单月内按照小时拆分，最小粒度是小时，一天最多可以有24个分片，最少1个分片，下个月从头开始循环，每个月末需要手工清理数据。

字段为字符串类型，yyyymmddHH 10位。

vim conf/schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

vim conf/rule.xml

sharding_col

sharding-by-hour

class="io.mycat.route.function.LatestMonthPartion">

配置说明：

* columns标识将要分片的表字段，字符串类型(yyyymmddHH)，格式需要符合Java标准。

* algorithm为分片函数。

* splitOneDay为一天切分的分片数。

测试SQL：

CREATE TABLE sharding_by_hour (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL,sharding_col varchar(10) null);

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100101','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100108','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100109','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100116','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100117','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100123','2017-10-01', database());

select * from sharding_by_hour;

测试结果：

[root@testA conf]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 test

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE sharding_by_hour (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL,sharding_col varchar(10) null);

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100101','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100108','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100109','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100116','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100117','2017-10-01', database());

INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100123','2017-10-01', database());

select * from sharding_by_hour; Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100101','2017-10-01', database());

Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100108','2017-10-01', database());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100109','2017-10-01', database());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100116','2017-10-01', database());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100117','2017-10-01', database());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sharding_by_hour (sharding_col,create_time,db_nm) VALUES ('2017100123','2017-10-01', database());

Query OK, 1 row affected (0.00 sec)

mysql> select * from sharding_by_hour;

+---------------------+-------+--------------+

| create_time | db_nm | sharding_col |

+---------------------+-------+--------------+

| 2017-10-01 00:00:00 | db0 | 2017100101 |

| 2017-10-01 00:00:00 | db1 | 2017100108 |

| 2017-10-01 00:00:00 | db1 | 2017100109 |

| 2017-10-01 00:00:00 | db2 | 2017100116 |

| 2017-10-01 00:00:00 | db2 | 2017100117 |

| 2017-10-01 00:00:00 | db2 | 2017100123 |

+---------------------+-------+--------------+

6 rows in set (0.06 sec)

注意

* 分片数必须跟dataNode节点数一致

* 最大分24个分片

* 分片字段的值必须是字符串，如：2017103112.(即为2017年10月31日12点)

2.1.4 按自然月分片

使用场景为按月份列分区，每个自然月一个分片，查询条件时使用between and。

字段为时间类型。

vim conf/schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123456">

vim conf/rule.xml

create_time

sharding-by-month

class="io.mycat.route.function.PartitionByMonth">

yyyy-MM-dd

2017-10-01

2017-12-30

配置说明：

* columns标识将要分片的表字段，字符串类型，与dateFormat格式一致。

* algorithm为分片函数。

* dateFormat为日期字符串格式。

* sBeginDate为开始日期。

* sEndDate为结束日期

测试SQL：

CREATE TABLE sharding_by_month (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL);

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-10-01', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-10-30', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-11-11', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-11-21', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-12-01', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2017-12-31', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2018-01-01', database());

INSERT INTO sharding_by_month (create_time,db_nm) VALUES ('2018-01-31', database());

select * from sharding_by_month

测试结果：

[root@testA conf]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 test

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE sharding_by_month (create_time timestamp NULL ON UPDATE CURRENT_TIMESTAMP ,`db_nm` varchar(20) NULL);