MySQL 高级(6) MyCat

MyCat 是一款数据库中间件，具有以下作用

• 读写分离
  普通的主从复制中，Java 程序需要配置多个数据源。如果想要做到读写分离的话，则进行增删改操作时，在写服务器 (主数据库) 上进行，而查操作则在读服务器 (从数据库) 上进行。并且如果要增加数据库，则需要修改 Java 的配置文件中的数据源，但是修改配置文件后是需要重启的，这种要求对于一些高可用程序来说显然是不合理的，因此就引入了 MyCat 数据库中间件，MyCat 是对数据库的高层抽象，Java 程序只需要配置一个 MyCat 数据源，就可以对 MyCat 底层配置的各种数据库进行 crud 操作，并且根据 SQL 可以自行选择主从数据库

• 数据分片：水平拆分 ( 分表 )，垂直拆分 ( 分库 )，水平+垂直拆分 ( 分库分表 )

• 多数据源整合：可以连接多种数据库

MyCat 的原理是对 SQL 进行拦截分析，然后将 SQL 发往对应的数据库进行执行后返回

相关概念

• schema：逻辑库，与MySQL中的Database（数据库）对应，一个逻辑库中定义了所包括的Table。
• table：表，即物理数据库中存储的某一张表，与传统数据库不同，这里的表需要声明其所存储的逻辑数据节点
• dataNode： MyCAT 的逻辑数据节点，是存放table的具体物理节点，也称之为分片节点，通过 DataSource 来关联到后端某个具体数据库上，一般来说，为了高可用性，每个DataNode 都设置两个 DataSource，一主一从，当主节点宕机，系统自动切换到从节点。
• dataHost：定义某个物理库的访问地址，用于捆绑到dataNode上。

安装 MyCat

首先官网下载 MyCat 1.6 的 Linux 版本，然后传输到 Linux 上，然后解压

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

解压完成后进入 mycat 目录中

在 MyCat 中，有三个配置文件很重要

• schema.xml：定义逻辑库，表，分片节点等内容
• rule.xml：定义分片规则
• server.xml：定义用户以及系统相关变量，如端口，用户名密码等

Docker 安装版 ( 赞美开源精神！)，首先克隆 GitHub 上的项目

git clone https://github.com/dekuan/docker.mycat.git

编译

cd docker.mycat
docker-compose build

docker.mycat 目录下为

其中配置文件映射到 config/mycat 目录下，修改教程和加压缩版一样

修改 schema.xml

修改 schema.xml，该配置文件在 conf 目录下 ( 使用 docker 安装的则在 config/mycat 目录下，不再赘述 )

1. 删除 中的内容

1. 然后在 中添加属性 dataNode，指定数据节点名

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

</schema>

1. 修改 中的属性 database，以指定数据库 ( 用于主从复制的数据库 )。一个数据节点对应一个数据库

1. 修改 的属性 name，和 中的 dataHost 属性对应上

1. 修改 即写服务器 ( 主数据库 ) 的 url 属性为写服务器的 IP，以及 user 和 password 属性为主数据库的用户名和密码
   修改 即读服务器 ( 从数据库 ) 的 url 属性为读服务器的 IP，以及 user 和 password 属性为从数据库的用户名和密码

2. 删除其他的多余配置

修改 server.xml

修改 server.xml，该配置文件在 conf 目录下

在 server.xml 先配置用户信息

标签中的 name 属性为用户名， 标签中的 name 为 password 时，代表密码，用于连接 mycat

特别注意！！！！name 为 schemas 的 property，其值应该和 schema.xml 中的 <schema> 的 name 属性值对应

这里我修改用户名为 mycat，密码则不修改

验证数据库访问情况

两个服务器应该可以互相访问对方的数据库，即能够进行主从复制，查看从机状态即可

如果无法连接创建用户即可

启动

这里我就使用 docker 版本的启动了

注意观察启动日志，如果显示了某一台主机没有权限，就检查该主机的 ip 是否有对应有用户及权限，如果没有则使用命令

GRANT all
ON *.* TO 'root'@'IP' IDENTIFIED BY 'root';

访问 MyCat

后台管理窗口

mysql -uroot -proot -P9066 -h mycat IP

数据窗口

mysql -uroot -proot -P8066 -h mycat IP

想要使用可视化操作数据，则使用 GUI 连接时选择 8066 端口即可

读写分离

tips：读写分离的必要条件是主从复制

测试是否读写分离

为了测试是否完成了读写分离，需要先让主从复制的两个数据库有不同的数据

1. 在主从数据库中都插入下面这张表

   create table rAndw(
    name varchar(20)
   );
   

1. 在两个创建好的表中插入这样一条数据

   insert into rAndw values (@@hostname);
   

1. 在 mycat 中查询

   select * from rAndw;
   

发现查询的是主数据库中对应的主机名，所以读写分离还没有开启

开启读写分离

在 schema.xml 配置文件中 可以配置 balance 属性，该属性决定了负载均衡类型

• balance = “0”：不开启读写分离机制，所有读操作都发送到当前可用的写主机上
• balance = “1”：全部的读主机和写主机参与 select 语句的负载均衡
• balance = “2”：所有读操作随机的在读主机和写主机中分发
• balance = “3”：所有读请求随机分发到读主机上执行，写主机不承担读压力

常用的为 1 和 3，当 balance=”1” 时，可以进行双主双从的互为主备设置

这里为了测试，将其设置为 3

然后重启容器，再次进行查询

select * from rAndw;

发现和容器 ID 一致，读写分离开启

分库分表

分库

分库指的是通过 MyCat 将不同的表实际的创建到不同主机的数据库中

分库的前提：两个或多个干净的库，并且是通过操作 MyCat 来完成的分库

分库的原则：可以在分库中被分出去的表，不能与原库中的表有 join 关联，因为表之间不能跨库作业

假设：一个电商数据库中有以下几张表，用户表有 20w 条数据，订单表有 600w 条数据，订单详细表有 600 条数据，订单状态表有 20 条数据

Q：此时该数据库可以进行怎样的分库，可以将哪些表分出去？

A：可以将用户表分出去，用户表只起到了验证登录的作用，登陆后将用户 ID 存储在 session 中，就可以将 session 中的用户 ID 用来与订单表等做条件进行查询，而不需要放在同一个数据库中。同理，与客户有关的所有表都可以拆分到另一个库中

实操

1. 在 schema.xml 中， 下添加一个

name 代表表名，dataNode 表示将这张表放到哪个节点中

<table name="customer" dataNode="dn2" > </table>

1. 然后新建一个 dataNode，对应到 dn2 上

   <dataNode name="dn2" dataHost="host2" database="anti_sm" />
   

tips：由于我只开了两个数据库服务器，因此要将原来用来做读写分离的主机 (dn1 ) 选择的数据库变得与 dn2 相同，且不能进行主从复制

1. 因此在这时，就不存在从机了，因为两个数据库都需要进行写操作。删掉 中的 ，并修改 balance=0，不开启读写分离

2. 复制上一步中修改了的 ，然后

   • 修改 name 属性为 host2

   • 修改 中的 host 主机名，不能和第一个 dataHost 中的 主机名重复

   • 修改 url 为原来作为从机的数据库服务器的 IP

3. 新建在 中指定的 database ( 两台主机上都要创建 )

   create database anti_sm;
   

1. 重启 MyCat

2. 然后在 MyCat 中创建表，这样才会将表分到对应的库中
   原理是根据

测试

在 MyCat 中新建表

create table customer(
    id int auto_increment PRIMARY KEY,
    name VARCHAR(200)
);

查看 dn1 对应主机的 anti_sm 数据库中

没有表

查看 dn2 对应主机的 anti_sm 数据库中

有表

再新建几张表

create table orders(
    id int auto_increment primary key,
    order_type int,
    customer_id int,
    amount DECIMAL(10, 2)
);

create table orders_datail(
    id int auto_increment primary key,
    detail varchar(2000),
    order_id int
);

create table  dict_order_type(
    id int auto_increment primary key,
    order_type varchar(200)
);

可以发现 dn1 对应的主机的数据库中有，而 dn2 中没有这几张表

分库搭建成功

tips：使用 GUI 形式的数据库管理工具，连接 MyCat 后可能会出现展示不全的兼容性问题

分表

根据上面创建的表，假设 orders 表中有 600w 条数据，对 orders 进行拆分，根据 customer_id 进行拆分，对节点数取模来实现平均拆分

实操

1. 修改 schema.xml 中的 ，新增一个

<table name="orders" dataNode="dn1,dn2" rule="mod_rule"> </table>

rule 是后面需要配置的

1. 修改 rule.xml，新增 ，name 与上面配置的 rule 属性值一样
   ：分片规则的依据字段 ( 或者叫做参数 )
   ：分片算法

   <tableRule name="mode_rule">
                <rule>
                        <columns>customer_id</columns>
                        <algorithm>mode-long</algorithm>
                </rule>
        </tableRule>
   

这里使用的分片算法 mod_long 是 MyCat 内置的
但是需要对其进行修改，将 的值修改为 2，因为我们只有两个节点
然后保存退出

1. 由于在进行分库时，有一个主机上的数据库只有 customer 表而没有 orders 表，这时对 orders 的分表操作并不会成功，因此先要在没有 orders 的主机数据库上创建 orders 表。即保证
   dn1 和 dn2 上都有要被拆分的表

2. 在 MyCat 中，对 orders 表进行数据插入测试
   注意，使用 MyCat 插入数据时，必须写全字段名，不然当分片算法依据的是字段时就会报错

   insert into orders(id, order_type, customer_id, amount) values(1, 101, 100, 100100);
   insert into orders(id, order_type, customer_id, amount) values(2, 101, 100, 100300);
   insert into orders(id, order_type, customer_id, amount) values(3, 101, 101, 120100);
   insert into orders(id, order_type, customer_id, amount) values(4, 101, 101, 103000);
   insert into orders(id, order_type, customer_id, amount) values(5, 102, 101, 100400);
   insert into orders(id, order_type, customer_id, amount) values(6, 102, 100, 100020);
   

然后在 MyCat 中进行数据查询

select * from orders;

可以发现查询出来的数据格式和往常并不一样，这是因为进行了分表
查看 dn2，发现只有这三条数据
dn1 则只有另外三条

跨库 join

可是，在四张表中，订单详情表与订单表的联系是十分紧密的，将订单表进行拆分后，是否还能与订单详情表进行连表查询呢？

首先，在 MyCat 中对 orders_detail 表插入数据

insert into orders_datail(id, detail, order_id) values(1, "detail1", 1);
insert into orders_datail(id, detail, order_id) values(2, "detail2", 2);
insert into orders_datail(id, detail, order_id) values(3, "detail3", 3);
insert into orders_datail(id, detail, order_id) values(4, "detail4", 4);
insert into orders_datail(id, detail, order_id) values(5, "detail5", 5);
insert into orders_datail(id, detail, order_id) values(6, "detail6", 6);

然后对 orders 表和 orders_detail 表进行关联查询

select *
from orders o inner join orders_detail od
on o.id = od.order_id;

ER 表

MyCat 的核心是拦截转发，将这条 SQL 拦截后，将其转发到了有 orders 这张表的数据库中，但是 dn2 节点的数据库中是没有 orders_detail 这张表的，因此报错了

MyCat 无法对没有分表的表和分表后的表之间进行跨库作业，这时就需要对 orders_detail 这张表也进行拆分

但是 orders_detail 如何拆分呢？可以根据订单详情表中和 orders 表的关联字段，即 order_id 进行拆分，这种拆分方式称为 ER 表

这种拆分方式的配置如下

1. 修改 schema.xml，在订单表对应的

<childTable name="orders_datail" primaryKey="id" joinKey="order_id" parentKey="id" />

joinKey：子表对父表的关联字段
parentKey：父表对子表的关联字段

1. 在没有 orders_detail 这张表的数据库中创建这张表

   create table orders_datail(
    id int auto_increment primary key,
    detail varchar(2000),
    order_id int
   );
   

1. 重启 MyCat，再次插入数据后查询

   insert into orders_datail(id, detail, order_id) values(1, "detail1", 1);
   insert into orders_datail(id, detail, order_id) values(2, "detail2", 2);
   insert into orders_datail(id, detail, order_id) values(3, "detail3", 3);
   insert into orders_datail(id, detail, order_id) values(4, "detail4", 4);
   insert into orders_datail(id, detail, order_id) values(5, "detail5", 5);
   insert into orders_datail(id, detail, order_id) values(6, "detail6", 6);
   

可以看见查询成功

全局表

最后来看下 dict_order_type 这张表，这张表的数据量虽然很少，但同时又是必须使用的，即使将 orders 表拆分了，但是由于不知道哪些数据会到哪个数据库，因此 dict_order_type 是不能进行拆分而每个数据库都需要一份， 这种表又称为全局表

全局表会直接复制给每个数据库一份，所有写操作也会同步给多个库，所以全局表一般不能是大数据表或者更新频繁的表

一般全局表为字典表或是系统表

配置方法如下

1. schema.xml 文件的 下新增一个

<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>

1. 在没有 dict_order_type 的主机的数据库中创建全局表

   create table  dict_order_type(
    id int auto_increment primary key,
    order_type varchar(200)
   );
   

1. 重启 MyCat，在 MyCat 中插入数据

   insert into dict_order_type(id, order_type) values(101, "type1");
   insert into dict_order_type(id, order_type) values(102, "type2");
   

1. 打开两个主机中的数据库 anti_sm 发现都有 dict_order_type 这张表，并且数据一致

全局序列

在上面插入数据时，我们是通过指定 id 的方式插入的，这河里吗？显然 id 是需要自动生成的，但是我们同时又不能使用自增，因为自增会导致分在不同数据库中的逻辑上在同一张表中的数据的 id 会重复

比如：上面分表案例中，dn1 和 dn2 的 orders 表分片规则是通过 customer_id mod 2 分的，如果在插入时不指定 id 的话，则插入 customer_id 为 100 和 插入 customer_id 为 101 这两条数据时，他们在各自的数据节点中都是 id 为 1 的记录，在 MyCat 中通过 id 查询时就会返回两条记录，这显然不河里

所以为了 id 不重复，id 应该是全局唯一的，MyCat 提供了三种生成唯一 id 的方式

本地文件

MyCat 建立一个本地文件，在拦截 insertSQL 的同时为其设置 id，并记录，保持 id 一直是能是末尾上的

这种方式的缺点是抗风险能力差，如果 MyCat 的服务器挂掉了，即使有备用机，本地文件依旧无法读取，不知道再插入时 id 应该是何数

数据库

利用数据库中的一个表来进行计数累加，但并不是每次生成序列都会读写数据库，这样效率很低

MyCat 会预加载一部分序列号段到 MyCat 内存中，给插入语句进行使用。如果内存中的序列号段用完了，MyCat 会再向数据库要一次

Q：如果 MyCat 崩溃，那内存中的序列还存在吗？

A：崩溃后序列不存在， MyCat 重启后会向数据库申请新的号段，原有号段会弃用，从而避免了主键重复

创建序列脚本

1. 在 windows 的 anti_sm ( 存储分表的 ) 数据库中创建下表

   CREATE TABLE mycat_sequence(
    NAME VARCHAR(50) NOT NULL PRIMARY KEY,
    current_value INT NOT NULL,
    increment INT NOT NULL DEFAULT 100
   ) ENGINE=INNODB;
   

1. 创建函数，返回当前的序列值

   DROP FUNCTION IF EXISTS mycat_seq_currval;
   DELIMITER $$
   CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64)
   DETERMINISTIC
   BEGIN
   DECLARE retval VARCHAR(64);
   SET retval="-999999999,null";
   SELECT CONCAT(CAST(current_value AS CHAR), "," ,CAST(increment AS CHAR)) INTO retval 
   FROM MYCAT_SEQUENCE WHERE `name`=seq_name;
   RETURN retval;
   END $$
   

1. 创建函数，设置序列值

   DROP FUNCTION IF EXISTS mycat_seq_setval;
   DELIMITER $$
   CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) 
   DETERMINISTIC
   BEGIN
   UPDATE MYCAT_SEQUENCE
   SET current_value = value
   WHERE name = seq_name;
   RETURN mycat_seq_currval(seq_name);
   END $$
   

1. 创建函数，获取下一个序列的值

   DROP FUNCTION IF EXISTS mycat_seq_nextval;
   DELIMITER $$
   CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) 
   DETERMINISTIC
   BEGIN
   UPDATE MYCAT_SEQUENCE
   SET current_value = current_value + increment WHERE name = seq_name;
   RETURN mycat_seq_currval(seq_name);
   END $$
   

1. 增加要用的序列

   insert into mycat_sequence(name, current_value, increment) values("orders", 400000, 100);
   

400000 代表起始值，100 代表每次分配序列的长度。即插入 的起始值是 400100，而后每插入一条数据就 +1。如果 MyCat 重启，则下次插入就从 400200 开始，丢弃固定长度的序列

1. 修改 MyCat 配置文件：sequence_db_conf.properties
   指定全局序列的相关配置在哪个节点上 ( 使用 orders 属性指定 )，eg：

   ORDERS=dn1
   

1. 配置 server.xml，显式声明使用数据库方式来配置全局序列。找到 name 为 sequenceHandlerType 的 ，将其值修改为 1
   
   sequenceHandlerType 的值有四种， 对应了全局序列的四种配置方式

   • 本地文件 0
   • 数据库 1
   • 时间戳 2
   • 自生成 3
2. 重启 MyCat，通过插入一条数据，这时插入语句这么写

   INSERT INTO orders(id, amount, customer_id, order_type)
   VALUES
   (NEXT VALUE FOR mycatseq_orders, 1000, 101, 102);
   

运行报错
但是在下方信息栏中确实插入成功了

这是因为 GUI 工具与 MyCat 的兼容性不太好
插入完成后进行查询

SELECT * FROM orders;

可以发现成功插入

时间戳

拦截插入语句时，将时间戳设置为其 id，缺点是时间戳有 18 位，太长了

自生成

根据业务逻辑来组合，或者在后端生成 UUID，雪花算法生成等后端方式实现 ( 比较常用，使用 Dao 层框架 MybatisPlus 等更容易实现 )