MyCat分库分表高级教程

喜欢ヅ旅行 2023-09-29 12:19 7阅读 0赞

## **1.课程回顾** ##

2，本章重点

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

mycat横向切分如何实现

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

## 3.具体内容 ##

### 3.1 数据切分概念 ###

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

####  ####

3.2 切分种类及优缺点

####  ####

3.2.1 的垂直（纵向）切分

概念：

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

使用场景：

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

具体实现：

![f6101de68c9e4fba949dc064428b6897.png][]优点：

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

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

• 数据维护简单。

缺点：

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

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

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

**3.2.2 水平（横向）切分**

概念：

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

使用场景：

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

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

具体实现：

![24da23c4763248b08d5c54114fd19851.png][]

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

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

水平切分常见分片规则：

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

![daf47d6439cb4c388ae07c8f51c2a25d.png][]• 按照日期，将不同月甚至日的数据分散到不同的库中；

优点：

• 拆分规则抽象好，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

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配置：![9eccc4e5f0364b879173529838b5a121.png][]

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

#### ![53345287376e45928b52a9fdf06cbbda.png][] ####

**3.3.3 schema.xml配置**

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

<table>分片表：

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

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

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

![0093b208c92945a984086452f65d0863.png][]

![a4642f25720e4f738b698a2e8d369b54.png][]

**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

配置自己分片：![23ff7b253d1142b98829ab9a173f60ae.png][]

![3bdbdbdab33c4205ad27c19f17f7a539.png][]

![d834a93417be46ee93c9f5907a0037ad.png][]

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

![ae2723cc7cf84257974fe834f2d712b4.png][]

![53488cc801d24585b4c7603f8b327e44.png][]

schema.xml配置：

![d6477ea850a34a199901a578ebbea7e8.png][]重启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');

更多分片规则参考：![1b9891d52ce7471a939259260d1df55b.png][]

####  ####

3.4 mycat 全局序列号

概念：

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

本地配置原理：

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

具体实现：

vim /usr/mycat-shard/mycat/conf/sequence\_conf.properties

![93dc7e8069cd4737982461c5b7b81108.png][]

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

![1d2c0a31cd3f40a797cd1776371db84b.png][]具体使用：

重启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

![6069c6b3d3674ab4a2c549cbecc4fed1.png][]

重启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');

[f6101de68c9e4fba949dc064428b6897.png]: https://img-blog.csdnimg.cn/f6101de68c9e4fba949dc064428b6897.png
[24da23c4763248b08d5c54114fd19851.png]: https://img-blog.csdnimg.cn/24da23c4763248b08d5c54114fd19851.png
[daf47d6439cb4c388ae07c8f51c2a25d.png]: https://img-blog.csdnimg.cn/daf47d6439cb4c388ae07c8f51c2a25d.png
[9eccc4e5f0364b879173529838b5a121.png]: https://img-blog.csdnimg.cn/9eccc4e5f0364b879173529838b5a121.png
[53345287376e45928b52a9fdf06cbbda.png]: https://img-blog.csdnimg.cn/53345287376e45928b52a9fdf06cbbda.png
[0093b208c92945a984086452f65d0863.png]: https://img-blog.csdnimg.cn/0093b208c92945a984086452f65d0863.png
[a4642f25720e4f738b698a2e8d369b54.png]: https://img-blog.csdnimg.cn/a4642f25720e4f738b698a2e8d369b54.png
[23ff7b253d1142b98829ab9a173f60ae.png]: https://img-blog.csdnimg.cn/23ff7b253d1142b98829ab9a173f60ae.png
[3bdbdbdab33c4205ad27c19f17f7a539.png]: https://img-blog.csdnimg.cn/3bdbdbdab33c4205ad27c19f17f7a539.png
[d834a93417be46ee93c9f5907a0037ad.png]: https://img-blog.csdnimg.cn/d834a93417be46ee93c9f5907a0037ad.png
[ae2723cc7cf84257974fe834f2d712b4.png]: https://img-blog.csdnimg.cn/ae2723cc7cf84257974fe834f2d712b4.png
[53488cc801d24585b4c7603f8b327e44.png]: https://img-blog.csdnimg.cn/53488cc801d24585b4c7603f8b327e44.png
[d6477ea850a34a199901a578ebbea7e8.png]: https://img-blog.csdnimg.cn/d6477ea850a34a199901a578ebbea7e8.png
[1b9891d52ce7471a939259260d1df55b.png]: https://img-blog.csdnimg.cn/1b9891d52ce7471a939259260d1df55b.png
[93dc7e8069cd4737982461c5b7b81108.png]: https://img-blog.csdnimg.cn/93dc7e8069cd4737982461c5b7b81108.png
[1d2c0a31cd3f40a797cd1776371db84b.png]: https://img-blog.csdnimg.cn/1d2c0a31cd3f40a797cd1776371db84b.png
[6069c6b3d3674ab4a2c549cbecc4fed1.png]: https://img-blog.csdnimg.cn/6069c6b3d3674ab4a2c549cbecc4fed1.png