MyCAT2分库分表

编号	分表字段	效果
1	id(主键、或创建时间)	查询订单注重时效。历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均
2	customer_id(客户id)	根据客户id去分，两个节点访问平均，一个客户所有的订单都在同一个节点，这样查询就快

3.分库分表的环境准备

我们可以按第6点的方式，搭建MySQL两组主从复制模型。

1.准备四个数据库

2.分别使用Docker启动

名称	ip	port
dw0	192.168.106.133	3312
dr0	192.168.106.133	3313
dw1	192.168.106.133	3314
dr1	192.168.106.133	3315

docker run --name dw0 -p 3312:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.28 --lower_case_table_names=1

docker run --name dr0 -p 3313:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.28 --lower_case_table_names=1

docker run --name dw1 -p 3314:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.28 --lower_case_table_names=1

docker run --name dr1 -p 3315:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.28 --lower_case_table_names=1

启动完成 docker ps 查看：

1.进入dw0配置

将容器里面的配置文件复制出来，主要修改服务器的配置。在root目录下创建一个mysqlms的目录存放从docker容器里面复制过来的配置文件。

1）进入目录：

cd /mysqlms

docker cp dw0:/etc/mysql/conf.d/docker.cnf dw0.cnf

docker cp dr0:/etc/mysql/conf.d/docker.cnf dr0.cnf

2）修改dw0.cnf：

server-id=1

log-bin=master.bin

3）修改dr0.cnf：

server-id=2

4）配置文件修改后，复制到容器里面：

docker cp dw0.cnf dw0:/etc/mysql/conf.d/docker.cnf

docker cp dr0.cnf dr0:/etc/mysql/conf.d/docker.cnf

5）重启MySQL（dw0,dr0）：

docker restart dw0 dr0

6）进入dw0主机里面执行相关配置：

docker exec -it dw0 bash

mysql -uroot -p123456

7）创建用户

create user 'rep'@'%' identified by '123456';

8）给该用户授予权限：

grant replication slave on *.* to 'rep'@'%';

9）刷新权限：

flush privileges;

至此，dw0里面已经创建了一个用户：rep 123456 拥有所有库，所有表replication slave

10）查看状态后面备用：

show master status;

2.进入dr0配置

1）进入容器并登录：

docker exec -it dr0 bash

mysql -u root -p123456

2）执行主从配置：

change master to master_host="192.168.175.109",master_port=3312,master_user="rep",master_password="123456",master_log_file="master.000001",master_log_pos=854;

master_log_file：该文件具体叫什么名称，需要从主机里面去看看上面【查看状态】

3）启动主从（在dr0里面执行）：

start slave ;

4）查询主从的状态（dr0）：

show slave status \G;

5）成功的标志：

3.进入dw1配置

将容器里面的配置文件复制出来，主要修改服务器的配置。在root目录下创建一个mysqlms的目录存放从docker容器里面复制过来的配置文件。

1）进入目录：

cd /mysqlms

docker cp dw1:/etc/mysql/conf.d/docker.cnf dw1.cnf

docker cp dr1:/etc/mysql/conf.d/docker.cnf dr1.cnf

2）修改dw1.cnf

server-id=1

log-bin=master.bin

3）修改dr1.cnf

server-id=2

4）配置文件修改后，复制到容器里面：

docker cp dw1.cnf dw1:/etc/mysql/conf.d/docker.cnf

docker cp dr1.cnf dr1:/etc/mysql/conf.d/docker.cnf

5）重启MySQL（dw1,dr1）：

docker restart dw1 dr1

6）进入dw1主机里面执行相关配置

docker exec -it dw1 bash

mysql -uroot -p123456

7）创建用户：

create user 'rep'@'%' identified by '123456';

8）给该用户授予权限：

grant replication slave on *.* to 'rep'@'%';

9）刷新权限：

flush privileges;

至此：dw1里面已经创建了一个用户：rep 123456 拥有所有库，所有表replication slave

10）查看状态后面备用：

show master status;

4.进入dr1配置

1）进入容器并登录：

docker exec -it dr1 bash

mysql -u root -p123456

2）执行主从配置：

change master to master_host="192.168.175.109",master_port=3314,master_user="rep",master_password="123456",master_log_file="master.000001",master_log_pos=854,get_master_public_key=1;

master_log_file：该文件具体叫什么名称，需要从主机里面去看看上面【查看状态】

3）启动主从：（在dr1里面执行）

start slave ;

4）查询主从的状态（dr1）

show slave status \G;

5）成功的标志：

5.使用Navicat连接测试

6.配置MyCAT数据源

通过上面的配置，我们准备了两组主从，分别为：

dw0 dr0
dw1 dr1

接下来，我们要在MyCAT里面配置这四个数据源。

添加dw0数据源

/*+ mycat:createDataSource{

"name":"dw0",

"password":"123456", "url":"jdbc:mysql://127.0.0.1:3312?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user":"root",} */;

添加dr0数据源

/*+ mycat:createDataSource{

"name":"dr0",

"password":"123456", "url":"jdbc:mysql://127.0.0.1:3313?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user":"root",} */;

添加dw1数据源

/*+ mycat:createDataSource{

"name":"dw1",

"password":"123456", "url":"jdbc:mysql://127.0.0.1:3314?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user":"root",} */;

添加dr1数据源

/*+ mycat:createDataSource{

"name":"dr1",

"password":"123456", "url":"jdbc:mysql://127.0.0.1:3315?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user":"root",} */;

执行之后我们在MyCAT里面看到如下数据源的配置文件。

7.配置MyCAT集群配置

注意：自动分片默认要求集群名字以c为前缀，数字为后缀：

c0就是分片表第一个节点；
c1就是第二个节点。

一般情况下我们使用默认的就可以了。

/*! mycat:createCluster{

"name":"c0",

"masters":[

"dw0"

"replicas":[

"dr0"

]

} */;

/*! mycat:createCluster{

"name":"c1",

"masters":[

"dw1"

"replicas":[

"dr1"

]

} */;

创建完成之后查看MyCAT配置文件里面内容如下：

{

"clusterType":"MASTER_SLAVE",

"heartbeat":{

"heartbeatTimeout":1000,

"maxRetryCount":3,

"minSwitchTimeInterval":300,

"showLog":false,

"slaveThreshold":0.0

"masters":[

"dw0"

"maxCon":2000,

"name":"c0",

"readBalanceType":"BALANCE_ALL",

"replicas":[

"dr0"

"switchType":"SWITCH"

}

8.全局表(广播表)配置

全局表：所有分片库中都有全量数据的表。分库分表的环境准备好之后，接下来我们在MyCAT里面执行相关的命令，就可以帮我们创建全局表。

1.创建数据库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2.创建表

use db1;

CREATE TABLE `sys_dict` (

`id` bigint NOT NULL AUTO_INCREMENT,

`dict_type` int ,

`dict_name` varchar(100) DEFAULT NULL,

`dict_value` int ,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 BROADCAST;

上面的SQL中有一个BROADCAST 这个就是全局表的标识。

3.查看结果

MyCAT的配置文件中：

后端数据库中发现所有的表全部出现了。

4.添加数据查看结果

INSERT INTO sys_dict(dict_type,dict_name,dict_value) VALUES(1,"男",1);

INSERT INTO sys_dict(dict_type,dict_name,dict_value) VALUES(1,"女",0);

所有的库中都有的数据

5.查询数据查看结果

select * from sys_dict

发现数据并没有重复。

9.分片表配置【重点】

关键字：dbpartition、tbpartitition、tbpartitions、dbpartitions。以上的运行成功必须是c0、c1的数据源配置没有问题才行。

1.创建表

CREATE TABLE orders(

ID BIGINT NOT NULL AUTO_INCREMENT,

ORDER_TYPE INT,

CUSTOMER_ID INT,

AMOUNT DECIMAL(10,2),

PRIMARY KEY(ID)

) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

dbpartition BY mod_hash(CUSTOMER_ID) tbpartition By mod_hash(CUSTOMER_ID)

tbpartitions 1 dbpartitions 2

dbpartition BY mod_hash(CUSTOMER_ID)： 指定数据库的分片算法及使用哪一条数据进行分片HASH

tbpartition BY mod_hash(CUSTOMER_ID) ：指定表的分片算法及使用哪一条数据进行分片HASH

tbpartitions 1 表的分片数量

dbpartitions 2 数据库的分片数量

2.查看MyCAT生成的配置

3.添加数据

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(1,101,100,100101);

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(2,101,100,100101);

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(3,101,100,100101);

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(4,102,101,101102);

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(5,102,101,101102);

INSERT INTO ORDERS(ID,ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(6,102,101,101102);

4.查询后台物理库

从图中我们可以看到数据库生成了。打开数据库后，里面的表也生成了，里面的数据也分开了，并不在一个表里。

5.MyCAT中查询

从上图中，我们发现查询的结果也帮我们合并了。

10.ER表配置

说明：在1.6的版本中，我们ER表的配置有关系的数据必须存放在相同的库中，但是在2.0中不用了，MyCAT2自动帮我们优化了。上面我们创建了一张订单表，接下来我们创建一张订单详情表。

1.创建表

CREATE TABLE orders_detail(

ID BIGINT AUTO_INCREMENT,

detail VARCHAR(2000),

order_id BIGINT,

PRIMARY KEY(ID)

) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

dbpartition BY mod_hash(order_id) tbpartition By mod_hash(order_id)

tbpartitions 1 dbpartitions 2

dbpartition BY mod_hash(order_id) ：指定数据库的分片算法及使用哪一条数据进行分片HASH

tbpartition BY mod_hash(order_id) ：指定表的分片算法及使用哪一条数据进行分片HASH

tbpartitions 1 表的分片数量

dbpartitions 2 数据库的分片数量

2.查看MyCAT生成的配置

从上图中我们可以出已经放到分片表里面了。

3.添加数据

INSERT INTO orders_detail VALUES(1,"详情1",1);

INSERT INTO orders_detail VALUES(2,"详情2",2);

INSERT INTO orders_detail VALUES(3,"详情3",3);

INSERT INTO orders_detail VALUES(4,"详情4",4);

INSERT INTO orders_detail VALUES(5,"详情5",5);

INSERT INTO orders_detail VALUES(6,"详情6",6);

4.查询后台物理库

从上图可知，dw0里面只存了三条。

发现dw1里面也存放了三条。

5.MyCAT中关联查询

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

6.疑问

从物理库中我们看到一个库里面的详情数据和定义数据不配套，那是为什么呢？在1.6的版本里面是不允许的。

接下来我们说明下原因：

1）MyCAT2在涉及两个表的JOIN分片字段等价关系的时候可以完成JOIN的下推。

2）MyCAT2无需要指定ER表，是自动识别的。

3）查询配置的表是否具有ER关系使用如下注释：

/*+ mycat:showErGroup{}*/

结果如下：

上面的group_id 表示相同的组，该组中的表具有相同的存储分布(在MyCAT2中它是这么规定的)。

11.分片算法简介

1.取模哈希分片 MOD_HASH

1）如果分片值是字符串则先对字符串进行Hash转换为数值类型

2）分库键和分表键是同键

3）分表下标=分片值%(分库数量*分表数量)

4）分库下标=分表下标/分表数量

5）分库键和分表键是不同键

6）分表下标= 分片值%分表数量

7）分库下标= 分片值%分库数量

create table travelrecord (

....

) ENGINE=InnoDB DEFAULT CHARSET=utf8

dbpartition by MOD_HASH (id) dbpartitions 6

tbpartition by MOD_HASH (id) tbpartitions 6;

2.范围哈希分片 RANGE_HASH

1）RANGE_HASH(字段1, 字段2, 截取开始下标)

2）仅支持数值类型，字符串类型

3）当时字符串类型时候，第三个参数生效

4）计算时候优先选择第一个字段,找不到选择第二个字段

5）如果是字符串则根据下标截取其后部分字符串，然后该字符串hash成数值

6）根据数值按分片数取余

7）要求截取下标不能少于实际值的长度

8）两个字段的数值类型要求一致

create table travelrecord(

...

)ENGINE=InnoDB DEFAULT CHARSET=utf8

dbpartition by RANGE_HASH(id,user_id,3) dbpartitions 3

tbpartition by RANGE_HASH(id,user_id,3) tbpartitions 3;

3.字符串哈希分片 UNI_HASH

1）如果分片值是字符串则先对字符串进行hash转换为数值类型

2）分库键和分表键是同键

3）分库下标=分片值%分库数量

4）分表下标=(分片值%分库数量)*分表数量+(分片值/分库数量)%分表数量

5）分库键和分表键是不同键

6）分表下标= 分片值%分表数量

7）分库下标=分片值%分库数量

create table travelrecord (

....

) ENGINE=InnoDB DEFAULT CHARSET=utf8

dbpartition by UNI_HASH (id) dbpartitions 6

tbpartition by UNI_HASH (id) tbpartitions 6;

4.日期哈希分片 YYYYDD

1）仅用于分库

2）DD是一年之中的天数

3）(YYYY*366+DD)%分库数

create table travelrecord (

....

) ENGINE=InnoDB DEFAULT CHARSET=utf8

dbpartition by YYYYDD(xxx) dbpartitions 8

tbpartition by xxx(xxx) tbpartitions 12;

12.【掌握】MyCAT2全局ID的生成方式

1.为什么要全局ID

在复杂的分布式系统中，需要对大量的数据和消息进行唯一标识。如在阿里，淘宝，支付，等系统中，数据日渐增长，对数据分库分表后需要有一个唯一ID来标识一条数据或消息；还有如美团和饿了吗的骑手ID 商家ID 优惠券ID等，从以上可以得出，一个能够生成全局唯一ID的系统是非常必要的。

在MyCAT2中，自动默认使用雪花片法生成全局序列号。

如果不需要MyCAT默认全局序列，可以通过配置关闭自动加全局序列；建表语句方式关闭全局序列。

如果不需要使用MyCAT的自增序列，而使用MySQL本身的自增主键的功能。需要在配置中更改对应的建表SQL。不设置auto_increment关键字，这样 MyCAT就不认为这个表有自增主键的功能。就不会使用MyCAT全局序列号，这样，对应的插入SQL在MySQL中去处理，由MySQL的自增主键功能补全。

雪花算法：引入了时间戳的ID保持自增的分布式ID生成算法。

2.雪花算法原理说明

Twitter开源分布式生成ID算法。

1）优点：基本解决了所有问题

2）缺点：每个节点时间可能不同，生成ID是整体趋势递增的

Snowflake的结构如下(每部分用-分开):

图中的信息如下：

1）第一位未使用，因为二进制中最高位是符号位同，1表示负数，0表示正数。ID不可能为负数。

2）时间戳：41位，最后为2的41次方，大概69.73年，10==2+8

3）然后是5位datacenterId和5位workerId(10位的长度最多支持部署1024个节点） 也可以是3位datacenterId和7位workerId

4）最后12位是毫秒内的计数（12位的计数顺序号支持每个节点每毫秒产生4096个ID序号）每秒生成409.6万个【可怕】

一共加起来刚好64位，为一个Long型。(转换成字符串长度为18)

我们可以使用在线进制转换进制转化工具测试。

优点：

1）毫秒数在高位，自增序列在低位，整个ID都是趋势递增的。

2）不依赖数据库等第三方系统，以服务的方式部署，稳定性更高，生成ID的性能也是非常高的。

3）可以根据自身业务特性分配bit位，非常灵活。

缺点：强依赖机器时钟，如果机器上时钟回拨，会导致发号重复或者服务会处于不可用状态。

结论：适用于大规模分布式架构。

3.使用默认的雪花算法验证

如果不需要使用MyCAT的自增序列，而使用MySQL本身的自增主键的功能，需要在配置中更改对应的建表SQL,不设置AUTO_INCREMENT关键字，这样MyCAT就不认为这个表有自增主键的功能，就不会使用MyCAT的全局序列号。对应的插入SQL在MySQL处理，由MySQL的自增主键功能补全自增值。

1.向订单表里面添加数据

INSERT INTO ORDERS(ORDER_TYPE,CUSTOMER_ID,AMOUNT) VALUES(103,102,102102);