一枝梧桐
一枝梧桐

MySQL运维之分库分表（三）

一枝梧桐 知识技能 4

取模分片

1、介绍

根据指定的字段值与节点数量进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。

2、配置

schema.xml 逻辑表配置：

<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />

schema.xml 数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 分片规则配置：

<tableRule name="mod-long">
  <rule>
  <columns>id</columns>
  <algorithm>mod-long</algorithm>
  </rule>
  </tableRule>
  
  <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
  <property name="count"> 3 </property>
  </function>

分片规则属性说明如下：

属性描述
columns标识将要分片的表字段
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
count数据节点的数量

该分片规则，主要是针对于数字类型的字段适用。在前面水平拆分的演示中，我们选择的就是取模分片。

3、测试

配置完毕后，重新启动 MyCat,然后在 MyCat 的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。

 一致性hash分片

1、介绍

所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的拓容问题。

2、配置

schema.xml 中逻辑表配置：

<!-- 一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-murmur">
  <rule>
  <columns>id</columns>
  <algorithm>murmur</algorithm>
  </rule>
  </tableRule>
  
  <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
  <property name="seed"> 0 </property><!-- 默认是0 -->
  <property name="count"> 3 </property>
  <property name="virtualBucketTimes"> 160 </property></function>

分片规则属性含义：

属性 描述

columns 标识将要分片的表字段

algorithm 指定分片函数与function的对应关系

class 指定该分片算法对应的类

seed 创建murmur_hash对象的种子，默认 0

count 要分片的数据库节点数量，必须指定，否则没法分片

virtualBucketTimes

一个实际的数据库节点被映射为这么多虚拟节点，默认是 160 倍，也

就是虚拟节点数是物理节点数的 160

倍;virtualBucketTimes*count就是虚拟结点数量 ;

weightMapFile

节点的权重，没有指定权重的节点默认是 1 。以properties文件的

格式填写，以从 0 开始到count-1的整数值也就是节点索引为key，

以节点权重值为值。所有权重值必须是正整数，否则以 1 代替

bucketMapPath

用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个

属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出

到这个文件，没有默认值，如果不指定，就不会输出任何东西

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

create table tb_order(
  id varchar( 100 ) not null primary key,
  money int null,
  content varchar( 200 ) null
  );
  
INSERT INTO tb_order (id, money, content) VALUES ('b92fdaaf-6fc4-11ec-b831-
482ae33c4a2d', 10 , 'b92fdaf8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93482b6-6fc4-11ec-b831-
482ae33c4a2d', 20 , 'b93482d5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b937e246-6fc4-11ec-b831-
482ae33c4a2d', 50 , 'b937e25d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93be2dd-6fc4-11ec-b831-
482ae33c4a2d', 100 , 'b93be2f9-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93f2d68-6fc4-11ec-b831-
482ae33c4a2d', 130 , 'b93f2d7d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9451b98-6fc4-11ec-b831-
482ae33c4a2d', 30 , 'b9451bcc-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9488ec1-6fc4-11ec-b831-
482ae33c4a2d', 560 , 'b9488edb-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94be6e6-6fc4-11ec-b831-
482ae33c4a2d', 10 , 'b94be6ff-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94ee10d-6fc4-11ec-b831-
482ae33c4a2d', 123 , 'b94ee12c-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b952492a-6fc4-11ec-b831-
482ae33c4a2d', 145 , 'b9524945-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95553ac-6fc4-11ec-b831-
482ae33c4a2d', 543 , 'b95553c8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9581cdd-6fc4-11ec-b831-
482ae33c4a2d', 17 , 'b9581cfa-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95afc0f-6fc4-11ec-b831-
482ae33c4a2d', 18 , 'b95afc2a-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95daa99-6fc4-11ec-b831-
482ae33c4a2d', 134 , 'b95daab2-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9667e3c-6fc4-11ec-b831-
482ae33c4a2d', 156 , 'b9667e60-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96ab489-6fc4-11ec-b831-
482ae33c4a2d', 175 , 'b96ab4a5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96e2942-6fc4-11ec-b831-
482ae33c4a2d', 180 , 'b96e295b-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b97092ec-6fc4-11ec-b831-
482ae33c4a2d', 123 , 'b9709306-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b973727a-6fc4-11ec-b831-
482ae33c4a2d', 230 , 'b9737293-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b978840f-6fc4-11ec-b831-
482ae33c4a2d', 560 , 'b978843c-6fc4-11ec-b831-482ae33c4a2d');
枚举分片

1、介绍

通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。

2、配置

schema.xml 中逻辑表配置：

<!-- 枚举 -->

<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-intfile">
  <rule>
  <columns>sharding_id</columns>
  <algorithm>hash-int</algorithm>
  </rule>
  </tableRule>
  
  <!-- 自己增加 tableRule -->
  <tableRule name="sharding-by-intfile-enumstatus">
  <rule>
    <columns>status</columns>
    <algorithm>hash-int</algorithm>
  </rule>
  </tableRule>
  
  <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
  <property name="defaultNode"> 2 </property>
  <property name="mapFile">partition-hash-int.txt</property>
  </function>

partition-hash-int.txt，内容如下：

1 = 0
2 = 1
3 = 2

分片规则属性含义：

属性描述
columns标识将要分片的表字段
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
mapFile对应的外部配置文件
type默认值为0; 0 表示Integer,1 表示String
defaultNode默认节点 小于 0 标识不设置默认节点 , 大于等于 0 代表设置默认节点

默认节点的所用：枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点；如果没有默认值,碰到不识别的则报错。

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

CREATE TABLE tb_user (
  id bigint( 20 ) NOT NULL COMMENT 'ID',
  username varchar( 200 ) DEFAULT NULL COMMENT '姓名',
  status int( 2 ) DEFAULT '1' COMMENT '1: 未启用, 2: 已启用, 3: 已关闭',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  insert into tb_user (id,username ,status) values( 1 ,'Tom', 1 );
  insert into tb_user (id,username ,status) values( 2 ,'Cat', 2 );
  insert into tb_user (id,username ,status) values( 3 ,'Rose', 3 );
  insert into tb_user (id,username ,status) values( 4 ,'Coco', 2 );
  insert into tb_user (id,username ,status) values( 5 ,'Lily', 1 );
  insert into tb_user (id,username ,status) values( 6 ,'Tom', 1 );
  insert into tb_user (id,username ,status) values( 7 ,'Cat', 2 );
  insert into tb_user (id,username ,status) values( 8 ,'Rose', 3 );
  insert into tb_user (id,username ,status) values( 9 ,'Coco', 2 );
  insert into tb_user (id,username ,status) values( 10 ,'Lily', 1 );
应用指定算法

1、介绍

运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。

2、配置

schema.xml 中逻辑表配置：

<!-- 应用指定算法 -->

<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-substring">
  <rule>
    <columns>id</columns>
    <algorithm>sharding-by-substring</algorithm>
  </rule>
  </tableRule>
  
  <function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
  <property name="startIndex"> 0 </property> <!-- zero-based -->
  <property name="size"> 2 </property>
  <property name="partitionCount"> 3 </property>
  <property name="defaultPartition"> 0 </property>
 </function>

分片规则属性含义：

属性描述
columns标识将要分片的表字段
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
startIndex字符子串起始索引
size字符长度
partitionCount分区(分片)数量
defaultPartition默认分片(在分片数量定义时, 字符标示的分片编号不在分片数量内时，使用默认分片)

示例说明：

id=05-100000002, 在此配置中代表根据id中从 startIndex=0，开始，截取siz=2位数字即 05，05 就是获取的分区，如果没找到对应的分片则默认分配到 defaultPartition.

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

CREATE TABLE tb_app (
  id varchar( 10 ) NOT NULL COMMENT 'ID',
  name varchar( 200 ) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  insert into tb_app (id,name) values('0000001','Testx00001');
  insert into tb_app (id,name) values('0100001','Test100001');
  insert into tb_app (id,name) values('0100002','Test200001');
  insert into tb_app (id,name) values('0200001','Test300001');
  insert into tb_app (id,name) values('0200002','TesT400001');
 固定分片hash算法

1、介绍

该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与 1111111111 进行位 & 运算，位与运算最小值为 0000000000，最大值为 1111111111，转换为十进制，也就是位于 0-1023 之间。

特点：

  • 如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。

  • 可以均匀分配，也可以非均匀分配。

  • 分片字段必须为数字类型。

2、配置

schema.xml 中逻辑表配置：

<!-- 固定分片hash算法 -->

<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-long-hash">
  <rule>
    <columns>id</columns>
    <algorithm>sharding-by-long-hash</algorithm>
  </rule>
  </tableRule>
  
  <!-- 分片总长度为 1024 ，count与length数组长度必须一致； -->
  <function name="sharding-by-long-hash" class="io.mycat.route.function.PartitionByLong">
  <property name="partitionCount">2,1</property>
  <property name="partitionLength">256,512</property>
 </function>

分片规则属性含义：

属性描述
columns标识将要分片的表字段名
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
partitionCount分片个数列表
partitionLength分片范围列表

约束：

  1. 分片长度: 默认最大2^10 , 为 1024

  2. count、length 的数组长度必须是一致的

以上分为三个分区：0-255、256-511、512-1023

示例说明：

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

CREATE TABLE tb_longhash (
  id int( 11 ) NOT NULL COMMENT 'ID',
  name varchar( 200 ) DEFAULT NULL COMMENT '名称',
  firstChar char( 1 ) COMMENT '首字母',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  insert into tb_longhash (id,name,firstChar) values( 1 ,'七匹狼','Q');
  insert into tb_longhash (id,name,firstChar) values( 2 ,'八匹狼','B');
  insert into tb_longhash (id,name,firstChar) values( 3 ,'九匹狼','J');
  insert into tb_longhash (id,name,firstChar) values( 4 ,'十匹狼','S');
  insert into tb_longhash (id,name,firstChar) values( 5 ,'六匹狼','L');
  insert into tb_longhash (id,name,firstChar) values( 6 ,'五匹狼','W');
  insert into tb_longhash (id,name,firstChar) values( 7 ,'四匹狼','S');
  insert into tb_longhash (id,name,firstChar) values( 8 ,'三匹狼','S');
  insert into tb_longhash (id,name,firstChar) values( 9 ,'两匹狼','L');
字符串hash解析算法

1、介绍

截取字符串中的指定位置的子字符串, 进行hash算法，算出分片。

2、配置

schema.xml 中逻辑表配置：

<!-- 字符串hash解析算法 -->

<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-stringhash">
  <rule>
    <columns>name</columns>
    <algorithm>sharding-by-stringhash</algorithm>
  </rule>
  </tableRule>
  
  <function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
  <property name="partitionLength"> 512 </property> <!-- zero-based -->
  <property name="partitionCount"> 2 </property>
  <property name="hashSlice">0:2</property>
  </function>

分片规则属性含义：

属性描述
olumns标识将要分片的表字段
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
partitionLengthhash求模基数 ; length*count=1024 (出于性能考虑)
partitionCount分区数
hashSlicehash运算位, 根据子字符串的hash运算; 0 代表 str.length(), -1 代表 str.length()-1 , 大于 0 只代表数字自身；可以理解为substring（start，end，start为 0 则只表示 0

示例说明：

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数据分布情况。

create table tb_strhash(
  name varchar( 20 ) primary key,
  content varchar( 100 )
  )engine=InnoDB DEFAULT CHARSET=utf8mb4;
  
  INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID());
  INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID());
  INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID());
  INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID());INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());

1、介绍

按照日期及对应的时间周期来分片。

2、配置

schema.xml 中逻辑表配置：

<!-- 按天分片 -->

<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />

schema.xml 中数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml 中分片规则配置：

<tableRule name="sharding-by-date">
  <rule>
    <columns>create_time</columns>
    <algorithm>sharding-by-date</algorithm>
  </rule>
  </tableRule>
  
  <function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
  <property name="dateFormat">yyyy-MM-dd</property>
  <property name="sBeginDate">2022-01-01</property>
  <property name="sEndDate">2022-01-30</property>
  <property name="sPartionDay"> 10 </property></function>
  
  <!--
从开始时间开始，每 10 天为一个分片，到达结束时间之后，会重复开始分片插入
配置表的 dataNode 的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31 ，每
10 天一个分片，一共需要 37 个分片。
-->

分片规则属性含义：

属性描述
columns标识将要分片的表字段
algorithm指定分片函数与function的对应关系
class指定该分片算法对应的类
dateFormat日期格式
sBeginDate开始日期
sEndDate结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入
sPartionDay分区天数，默认值 10 ，从开始日期算起，每个 10 天一个分区

3、测试

配置完毕后，重新启动 MyCat，然后在 MyCat 的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。

create table tb_datepart(
  id bigint not null comment 'ID' primary key,
  name varchar( 100 ) null comment '姓名',
  create_time date null
  );
  
  insert into tb_datepart(id,name ,create_time) values( 1 ,'Tom','2022-01-01');
  insert into tb_datepart(id,name ,create_time) values( 2 ,'Cat','2022-01-10');
  insert into tb_datepart(id,name ,create_time) values( 3 ,'Rose','2022-01-11');
  insert into tb_datepart(id,name ,create_time) values( 4 ,'Coco','2022-01-20');
  insert into tb_datepart(id,name ,create_time) values( 5 ,'Rose2','2022-01-21');
  insert into tb_datepart(id,name ,create_time) values( 6 ,'Coco2','2022-01-30');
  insert into tb_datepart(id,name ,create_time) values( 7 ,'Coco3','2022-01-31');

#推荐阅读

标签： MySQL MySQL日志 MySQL维护 MySQL教程

赏
文章来源： 一枝梧桐
版权声明：除非特别标注，否则均为本站原创文章，转载时请以链接形式注明文章出处。

上一篇MySQL运维之分库分表（二）

下一篇MySQL运维之分库分表（四）

相关文章