10. 【掌握】MyCAT2分库分表

10.1 分库分表的原理

一个数据库由很多表构造成，每个表对应不同的业务，垂直切分是指按业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。

10.1.1 垂直切分

基于表或字段划分，表结构不同。我们有单库的分表，也有多库的分表，如下图所示

注意：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里面

换句话说：就是我们很多库合在一起组成一个完整的数据库系统（这个不同到集群哦）。

10.1.2 水平切分

基于数据划分，表结构相同，数据不同，也有同库的水平切分和多库的切分。

当我们的客户表数量已经到达数千万甚至上亿的时候，单表的存储容量和查询效率都会出现问题，我们需要进一步对单张表的数据进行水平切分。水平切分的每个数据库的表结构都是一样的，只是存储的数据不一样，比如每个库存储 1000 万的数据。

# 客户表  20W数据
CREATE TABLE customer(  ID INT AUTO_INCREMENT,  NAME VARCHAR(30),  PRIMARY KEY(ID)) 
# 订单表  600W数据
CREATE TABLE orders(  ID BIGINT AUTO_INCREMENT,  ORDER_TYPE INT,  CUSTOMER_ID INT,  AMOUNT DECIMAL(10,2),  PRIMARY KEY(ID))
# 订单详情表  800W数据
CREATE TABLE orders_detail( ID BIGINT AUTO_INCREMENT, detail VARCHAR(2000), order_id BIGINT, PRIMARY KEY(ID))
# 字典表  100条数据
CREATE TABLE dict_order_type( ID INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(ID ))

10.2 如何分表

10.2.1 选择要拆分的表

MySQL单表存储数据条数是有瓶颈的，单表达到1000W条数据就达到了瓶颈，会严重影响查询效率，所以我们需要进行水平拆分进行优化。例如：我们订单表和详情表里面的数据达到600W行数据，需要进行优化，那么我们就要把订单表的数据进行拆分存储了。

10.2.2 选择分表的定段

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

10.3 分库分表的环境准备

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

10.3.1 准备四个数据库

名称	ip	port
dw0	192.168.8.105	3306
dr0	192.168.8.106	3306
dw1	192.168.8.107	3306
dr1	192.168.8.108	3306

10.3.2 配置MyCAT数据源

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

1）主服务器dw0 从服务器dr0

2）主服务器dw1 从服务器dr1

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

-- 添加dw0数据源
/*+ mycat:createDataSource
{ "name":"dw0",
  "password":"root123",
  "url":"jdbc:mysql://192.168.8.105:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8", 
  "user":"root",}
*/

-- 添加dr0数据源
/*+ mycat:createDataSource
{ "name":"dr0",
  "password":"root123",
  "url":"jdbc:mysql://192.168.8.106:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8", 
  "user":"root",}
*/

-- 添加dw1数据源
/*+ mycat:createDataSource
{ "name":"dw1",
  "password":"root123",
  "url":"jdbc:mysql://192.168.8.107:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/

-- 添加dr1数据源
/*+ mycat:createDataSource
{ "name":"dr1",
  "password":"root123",
  "url":"jdbc:mysql://192.168.8.108:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/

-- 查看数据源
/*+ mycat:showDataSources{} */

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

10.3.3 配置MyCAT集群配置

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

1）c0就是分片表第一个节点；

2）c1就是第二个节点。

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

/*! mycat:createCluster{ "name":"c0", "masters":[  "dw0" ], "replicas":[  "dr0" ]} */

/*! mycat:createCluster{ "name":"c1", "masters":[  "dw1" ], "replicas":[  "dr1" ]} */

-- 查看集群
/*+ mycat:showClusters{} */

创建完成之后查看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"
}

10.4 全局表(广播表)配置

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

10.4.1 创建数据库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

10.4.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 这个就是全局表的标识。

10.4.3 查看结果

MyCAT的配置文件中：

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

10.4.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);

# 所有的库中都有的数据

10.4.5 查询数据查看结果

select * from sys_dict;

发现数据并没有重复。

10.5 分片表配置【重点】

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

10.5.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)： 指定数据库的分片算法及使用哪一条数据进行分片
# HASHtbpartition BY mod_hash(CUSTOMER_ID) ：指定表的分片算法及使用哪一条数据进行分片
# HASHtbpartitions 1  表的分片数量
# dbpartitions 2  数据库的分片数量

10.5.2 查看MyCAT生成的配置

10.5.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);

10.5.4 查询后台物理库

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

10.5.5 MyCAT中查询

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

10.6 ER表配置

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

10.6.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) ：指定数据库的分片算法及使用哪一条数据进行分片HASHtbpartition
#  BY mod_hash(order_id) ：指定表的分片算法及使用哪一条数据进行分片
#  HASHtbpartitions 1  表的分片数量
#  dbpartitions 2     数据库的分片数量

10.6.2 查看MyCAT生成的配置

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

10.6.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);

10.6.4 查询后台物理库

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

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

10.6.5 MyCAT中关联查询

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

10.6.6 疑问

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

接下来我们说明下原因：

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

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

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

/*+ mycat:showErGroup{}*/

结果如下：

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

10.7 分片算法简介

10.7.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;

10.7.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;

10.7.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 6tbpartition by UNI_HASH (id) tbpartitions 6;

10.7.4 日期哈希分片 YYYYDD

1）仅用于分库

2）DD是一年之中的天数

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

create table travelrecord ( ....) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by YYYYDD(xxx) dbpartitions 8tbpartition by xxx(xxx) tbpartitions 12;

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

11.1 为什么要全局ID

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

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

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

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

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

11.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)

我们可以使用https://tool.oschina.net/hexconvert/ 进制转化工具测试。

优点：

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

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

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

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

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

11.3 使用默认的雪花算法验证

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

11.3.1 向订单表里面添加数据

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

上面的SQL里面没有使用添加ID，那么后台是怎么处理的呢？查看后台数据发现使用的雪花算法

12. 【了解】MyCAT2工具的使用

就是一个用来连接MyCAT修改MyCAT配置的工具。

12.1 下载

# 官方下载（失效）
# http://dl.mycat.org.cn/2.0/ui/

# 局域网下载
http://192.168.123.200/Software/assistant-1.22-release-jar-with-dependencies-2022-5-26.jar

12.2 运行

前提，安装的电脑必须安装JDK1.8及以上的版本

12.3 创建连接并操作

文件-新连接：

连接成功之后我们可以看到我们之前配置的所有MyCAT的配置：