MyCat分库分表高级教程

1.课程回顾

2，本章重点

数据库拆分方式种类和优缺点

mycat横向切分如何实现

分表分库后，表中主键如何生成

3.具体内容

3.1 数据切分概念

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。

3.2 切分种类及优缺点

3.2.1 的垂直（纵向）切分

概念：

一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分

使用场景：

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小， 业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。（尤其是微服务项目，更适合按照不同微服务的功能，然后把数据库表也分开）

具体实现：

优点：

 

• 拆分后业务清晰，拆分规则明确；

• 系统之间整合或扩展容易；（微服务更适合敏捷开发，每个服务都使用自己的一套表）

• 数据维护简单。

缺点：

• 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度；

• 受每种业务不同的限制存在单库（单表数据库很大）性能瓶颈，不易数据扩展跟性能提高；

• 事务处理复杂（分布式事务，单应用事务无法解决问题）

3.2.2 水平（横向）切分

概念：

根据 表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数 据的水平（横向）切分

使用场景：

水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中， 对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。（mycat 主要实现的就是这个功能）

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加优化从库（读负载均衡）、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

具体实现：

 

user1,user2,user3并不是指三个库中表的名称，表名称是一样的 user1用户表的第一部分数据 user2用户表的第二部分数据。。。

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，

水平切分常见分片规则：

• 按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中；

• 按照日期，将不同月甚至日的数据分散到不同的库中； 

 

优点：

• 拆分规则抽象好，join 操作基本可以数据库做（mycat 可以使用join）；

• 不存在单库大数据(数据量大的表都被拆分)，提升高并发的性能瓶颈；

• 应用端改造较少(数据拆分后，业务项目中mybatis语句不用做任何更改）；

• 提高了系统的稳定性跟负载能力。

缺点：

• 拆分规则难以抽象（实际开发应用过程，可能表存的数据列复杂）；

• 分片事务一致性难以解决（一次插入10条数据，有5条向db1中添加，有另外5条向db2中添加）；

• 数据多次扩展难度跟维护量极大；

• 跨库 join 性能较差（跨网络。。。）。 

3.3 mycat实现分表分库

3.3.1 准备工作

需求：假如用户数据量非常大，想切分用户表数据

正常方式，需要再克隆5台独立mysql服务器，都安装mysql数据，再克隆一台mycat服务器，进行分表分库配置。

本节课，为了节省时间，使用mycat做读写分离的所有服务器。

首先要把mysql数据库服务器都启动起来，然后去除主从关系（一旦服务器重启，主从关系依然存在）。

查看mysqld服务是否启动：

service mysqld status

登录（allsession中操作）：

mysql -uroot -ptiger

查看从服务状态：

show slave status \G;

关闭主从关系：

stop slave; 注意，再次启动启动服务器，主从关系依然启动，可能再次去除

创建5个数据库：

db_sharding_test1,db_sharding_test2,db_sharding_test3,db_sharding_test4,db_sharding_test5(同时在mysql4上) 因为关闭主从关系，所以创建库时其他服务器上没有

在5个库中分别创建表：

create table tb_user(

   user_id  int  primary key comment '主键不自增',

   user_name  varchar(20) comment '用户名',

   real_name  varchar(30) comment '真实姓名',

   salary   double  comment '薪酬' ,

   hiredate  date  comment '雇佣时间'

);

在5个库中都要执行

3.3.2 server.xml配置

如果修改前面mycat读写分离的配置，非常麻烦，所以再解压一份mycat，不再配置环境变量，直接在新解压的mycat中配置分表分库。

启动mycat服务器，并使用xshell进行连接。

在/usr下创建mycat-shard目录：

mkdir  /usr/mycat-shard

解压mycat压缩包到该目录：

tar -xzvf /root/software/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/mycat-shard/

修改server.xml配置：

 

vim /usr/mycat-shard/mycat/conf/server.xml

 

3.3.3 schema.xml配置

vim /usr/mycat-shard/mycat/conf/schema.xml

<table>分片表：

分片表，是指那些原有的很大数据的表，需要切分到多个数据库的表，这样，每个分片都有一部分数据，所 有分片构成了完整的数据。

<table rule=""> rule 属性 配置分片规则：

前面讲了数据切分，一个大表被分成若干个分片表，就需要一定的规则，这样按照某种业务规则把数据分到 某个分片的规则就是分片规则，数据切分选择合适的分片规则非常重要，将极大的避免后续数据处理的难度

 

 

3.3.4 分片规则配置 rule.xml id取模方式分片

rule.xml 里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法， 或者对表使用相同的算法但具体的参数不同。这个文件里面主要有 tableRule 和 function 这两个标签。在具体使 用过程中可以按照需求添加 tableRule 和 function。

tableRule：

<tableRule name="r1"><rule><columns>分片列</columns><algorithm>算法规则</algorithm></tableRule> 这个标签定义表分片规则。

name 属性指定唯一的名字，用于标识不同的表规则。 内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。

columns 内指定要拆分的列名字。

algorithm 使用 function 标签中的 name 属性。连接表规则和具体路由算法

function:

 <function name="xxx"

                class="XXX">

                <property name="paramName">paramValue</property>

        </function>

name 指定算法的名字。

class 制定路由算法具体的类名字。

property 为具体算法需要用到的一些属性。

vim /usr/mycat-shard/mycat/conf/rule.xml 

配置自己分片：

 

 

 

3.3.5 id取模方式测试

启动mycat : 注意是 /usr/mycat-shard/mycat

 /usr/mycat-shard/mycat/bin/mycat start

测试添加：11%5 =1   12 %5 =2   13%5=3  14%5 =4   15%5=0 1005%5=0 1006%5=1 .......

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(11,'zhangsan1','张三1',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(12,'zhangsan1','张三2',21000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(13,'zhangsan1','张三3',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(14,'zhangsan1','张三4',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(15,'zhangsan1','张三5',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(16,'zhangsan1','张三7',30000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(17,'zhangsan1','张三7',20000,'2022-04-04');





insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1005,'zhangsan1','张三5',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1006,'zhangsan1','张三7',30000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1007,'zhangsan1','张三7',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1008,'zhangsan1','张三5',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1009,'zhangsan1','张三7',30000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(1010,'zhangsan1','张三7',20000,'2022-04-04');



-- mycat 会自动合并数据集

select * from tb_user;

-- 支持排序

select * from tb_user order by user_id;

3.3.6 分片规则配置role.xml 自然月方式分片

vim /usr/mycat-shard/mycat/conf/rule.xml

 

 schema.xml配置：

重启mycat:

 

  /usr/mycat-shard/mycat/bin/mycat stop

  /usr/mycat-shard/mycat/bin/mycat start

3.3.7 自然月方式测试

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(113,'zhangsan1','张三3',20000,'2022-01-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(114,'zhangsan1','张三4',20000,'2022-01-14');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(115,'zhangsan1','张三5',20000,'2022-01-24');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(116,'zhangsan1','张三7',30000,'2022-02-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(117,'zhangsan1','张三7',20000,'2022-02-14');





insert tb_user(user_id,user_name,real_name,salary,hiredate) values(105,'zhangsan1','张三5',20000,'2022-03-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(106,'zhangsan1','张三7',30000,'2022-03-24');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(107,'zhangsan1','张三7',20000,'2022-03-14');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(108,'zhangsan1','张三5',20000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(109,'zhangsan1','张三7',30000,'2022-04-04');



insert tb_user(user_id,user_name,real_name,salary,hiredate) values(110,'zhangsan1','张三7',20000,'2022-05-04');

一定是按照自然月分片（从2022-01-01开始，最多到5月，因为就5个库）：

//错误Can't find a valid data node for specified node index :TB_USER -> HIREDATE -> 2022-07-04 -> Index : 5

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(110,'zhangsan1','张三7',20000,'2022-06-04');

//错误Can't find a valid data node for specified node index :TB_USER -> HIREDATE -> 2022-07-04 -> Index : 6

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(110,'zhangsan1','张三7',20000,'2022-07-04');

更多分片规则参考：

 

3.4 mycat 全局序列号

概念：

在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，MyCat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式。

本地配置原理：

此方式 MyCAT 将 sequence 配置到文件中，当使用到 sequence 中的配置后，MyCAT 会更下 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。

具体实现：

vim /usr/mycat-shard/mycat/conf/sequence_conf.properties 

 

vim /usr/mycat-shard/mycat/conf/server.xml

具体使用：

 

重启mycat服务

/usr/mycat-shard/mycat/bin/mycat restart

多次执行下面语句：

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(next value for MYCATSEQ_GLOBAL,'zhangsan1','张三7',20000,'2022-01-04');

切换分片规则：

 vim /usr/mycat-shard/mycat/conf/schema.xml 

 

重启mycat服务

/usr/mycat-shard/mycat/bin/mycat restart

多次执行下面语句：

insert tb_user(user_id,user_name,real_name,salary,hiredate) values(next value for MYCATSEQ_GLOBAL,'zhangsan1','张三7',20000,'2022-01-04');