MySQL运维(二)MySQL分库分表概念及实战、读取分离详解
MySQL运维(二)MySQL分库分表详解、读取分离详解
1、MySQL分库分表相关概念
1.1 分库分表概念
1.1.1 分库的原因
分库：就是一个数据库分成多个数据库，部署到不同机器。
如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。从这几方面来看：
- 磁盘存储
业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。
- 并发连接支撑
数据库连接是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。
1.1.2 分表的原因
分表：就是一个数据表分成多个表。
数据量太大，SQL的查询就会变慢。如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。
即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢。
1.2 分库分表的类别
1.2.1 垂直分库
垂直分库，将原来一个单数据库的压力分担到不同的数据库，可以很好应对高并发场景。
1.2.2 水平分库
水平分库是指，将表的数据量切分到不同的数据库服务器上，每个服务器具有相同的库和表，只是表中的数据集合不一样。它可以有效的缓解单机单库的性能瓶颈和压力。
1.2.3 垂直分表
如果一个单表包含了几十列甚至上百列，管理起来很混乱，每次都select *的话，还占用IO资源。这时候，我们可以将一些不常用的、数据较大或者长度较长的列拆分到另外一张表。
1.2.4 水平分表
如果一个表的数据量太大，可以按照某种规则（如hash取模、range），把数据切分到多张表去。
1.3 分库分表的策略
分库分表策略一般有几种，使用与不同的场景：
- range范围
- hash取模
- range+hash取模混合
1.3.1 range范围
range，即范围策略划分表。比如我们可以将表的主键，按照从0~1000万的划分为一个表，1000~2000万划分到另外一个表。如下图：
当然，有时候我们也可以按时间范围来划分，如不同年月的订单放到不同的表，它也是一种range的划分策略。
这种方案的优点：
- 这种方案有利于扩容，不需要数据迁移。假设数据量增加到5千万，我们只需要水平增加一张表就好啦，之前
0~4000万的数据，不需要迁移。
缺点：
- 这种方案会有热点问题，因为订单id是一直在增大的，也就是说最近一段时间都是汇聚在一张表里面的。比如最近一个月的订单都在
1000万~2000万之间，平时用户一般都查最近一个月的订单比较多，请求都打到order_1表啦，这就导致数据热点问题。
1.3.2 hash取模
hash取模策略：指定的路由key（一般是user_id、订单id作为key）对分表总数进行取模，把数据分散到各个表中。
这种方案的优点：
- hash取模的方式，不会存在明显的热点问题。
缺点：
- 如果一开始按照hash取模分成4个表了，未来某个时候，表数据量又到瓶颈了，需要扩容，这就比较棘手了。比如你从4张表，又扩容成
8张表，那之前id=5的数据是在（5%4=1，即t_order_1），现在应该放到（5%8=5，即t_order_5），也就是说历史数据要做迁移了。
1.3.3 range+hash取模混合
既然range存在热点数据问题，hash取模扩容迁移数据比较困难，我们可以综合两种方案一起，取之之长，弃之之短。
比较简单的做法就是，在拆分库的时候，我们可以先用range范围方案，比如订单id在04000万的区间，划分为订单库1;id在4000万8000万的数据，划分到订单库2,将来要扩容时，id在8000万~1.2亿的数据，划分到订单库3。然后订单库内，再用hash取模的策略，把不同订单划分到不同的表。
1.4 分库分表的时机
1.4.1 什么时候分表？
如果你的系统处于快速发展时期，如果每天的订单流水都新增几十万，并且，订单表的查询效率明变慢时，就需要规划分库分表了。一般B+树索引高度是2~3层最佳，如果数据量千万级别，可能高度就变4层了，数据量就会明显变慢了。一般500万数据就要考虑分表了。
1.4.2 什么时候分库
业务发展很快，还是多个服务共享一个单体数据库，数据库成为了性能瓶颈，就需要考虑分库了。比如订单、用户等，都可以抽取出来，新搞个应用（其实就是微服务思想），并且拆分数据库（订单库、用户库）。
1.5 分库分表产生的问题及分库分表中间件
1.5.1 事务问题
分库分表后，假设两个表在不同的数据库，那么本地事务已经无效啦，需要使用分布式事务了。
1.5.2 跨库关联
跨节点Join的问题：解决这一问题可以分两次查询实现
1.5.3 排序问题
跨节点的count,order by,group by以及聚合函数等问题：可以分别在各个节点上得到结果后在应用程序端进行合并。
1.5.4 分页问题
- 方案1：在个节点查到对应结果后，在代码端汇聚再分页。
- 方案2：把分页交给前端，前端传来pageSize和pageNo，在各个数据库节点都执行分页，然后汇聚总数量前端。这样缺点就是会造成空查，如果分页需要排序，也不好搞。
1.5.5 分布式ID
数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID，或者使用雪花算法生成分布式ID。
1.5.6 分库分表中间件
目前流行的分库分表中间件比较多：
- cobar
- Mycat
- Sharding-JDBC
- Atlas
- TDDL（淘宝）
- vitess
2、分库分表中间件__Mycat使用详解
Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。
开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。 具体的分库分表的策略，只需要在MyCat中配置即可。
2.1 Mycat的下载及安装
Mycat 下载地址：http://dl.mycat.org.cn/
Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件。
- MySQL
- JDK
- Mycat
| 服务器 | 安装软件 | 说明 |
|---|---|---|
| 192.168.42.104 | JDK、Mycat | MyCat中间件服务器 |
| 192.168.42.104 | MySQL(端口3301) | 分片服务器 |
| 192.168.42.104 | MySQL(端口3302) | 分片服务器 |
| 192.168.42.104 | MySQL(端口3303) | 分片服务器 |
jdk安装过程略。。。
[root@centos04 apps]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)
使用docker在同一台服务器启动3个mysql实例
可以参考之前的文章，如下：
容器虚拟化技术Docker（二）mysql主从配置案例、redis集群搭建及扩容、缩容案例详解
docker run -p 3301:3306 --name mysql-01 \
-v /mydata/mysql-01/log:/var/log/mysql \
-v /mydata/mysql-01/data:/var/lib/mysql \
-v /mydata/mysql-01/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
docker run -p 3302:3306 --name mysql-02 \
-v /mydata/mysql-02/log:/var/log/mysql \
-v /mydata/mysql-02/data:/var/lib/mysql \
-v /mydata/mysql-02/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
docker run -p 3303:3306 --name mysql-03 \
-v /mydata/mysql-03/log:/var/log/mysql \
-v /mydata/mysql-03/data:/var/lib/mysql \
-v /mydata/mysql-03/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7
[root@centos04 apps]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1a7fb3926eec mysql:5.7 "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 33060/tcp, 0.0.0.0:3303->3306/tcp, :::3303->3306/tcp mysql-03
d3fe19fdce82 mysql:5.7 "docker-entrypoint.s…" 23 seconds ago Up 22 seconds 33060/tcp, 0.0.0.0:3302->3306/tcp, :::3302->3306/tcp mysql-02
6ef5e3163244 mysql:5.7 "docker-entrypoint.s…" 4 minutes ago Up 4 minutes 33060/tcp, 0.0.0.0:3301->3306/tcp, :::3301->3306/tcp mysql-01
使用客户端工具进行连接
2.1.1 上传Mycat压缩包到服务器
Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz
2.1.2 解压MyCat的压缩包
tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /opt/apps/
[root@centos04 mycat]# ll
total 12
drwxr-xr-x. 2 root root 190 Jan 29 13:20 bin
drwxrwxrwx. 2 root root 6 Apr 15 2020 catlet
drwxrwxrwx. 4 root root 4096 Jan 29 13:20 conf
drwxr-xr-x. 2 root root 4096 Jan 29 13:20 lib
drwxrwxrwx. 2 root root 6 Aug 31 2021 logs
-rwxrwxrwx. 1 root root 227 Sep 13 2021 version.txt
# bin : 存放可执行文件，用于启动停止mycat
# conf：存放mycat的配置文件
# lib：存放mycat的项目依赖包（jar）
# logs：存放mycat的日志文件
2.1.3 mycat概念
MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。
在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。
2.2 Mycat入门（水平分表）
由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上, 具体的结构，参考下图
在上述3台mysql数据库中创建数据库 db01 。
2.2.1 配置
可以用notepad++安装NppFTP实现对linux上配置文件的更改。
-
下载地址：https://github.com/ashkulz/NppFTP/releases
选择对应版本解压，x86对应32位，x64对应64位。 -
解压后将.dll文件复制到notepad++安装目录的plugins文件夹中
-
重启Notepad++后即可使用（虚拟机连接：连接方式：SFTP 端口号：22）
也可以参考https://www.likecs.com/show-203660625.html#sc=400进行配置
schema.xml中配置
在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3302?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3303?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
</mycat:schema>
server.xm进行配置
需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">DB01</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">DB01</property>
<property name="readOnly">true</property>
</user>
上述的配置表示，定义了两个用户 root 和 user ，这两个用户都可以访问 DB01 这个逻辑库，访问密码都是123456，但是root用户访问DB01逻辑库，既可以读，又可以写，但是 user用户访问DB01逻辑库是只读的。
2.2.2 测试
启动Mycat
[root@centos04 mycat]# bin/mycat start
Starting Mycat-server...
# 关闭
[root@centos04 mycat]# bin/mycat stop
Mycat启动之后，占用端口号 8066。
启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。
[root@centos04 mycat]# tail -f logs/wrapper.log
STATUS | wrapper | 2023/01/29 17:56:51 | Launching a JVM...
INFO | jvm 1 | 2023/01/29 17:56:51 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 1 | 2023/01/29 17:56:51 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2023/01/29 17:56:51 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2023/01/29 17:56:51 |
INFO | jvm 1 | 2023/01/29 17:56:51 | SLF4J: Class path contains multiple SLF4J bindings.
INFO | jvm 1 | 2023/01/29 17:56:51 | SLF4J: Found binding in [jar:file:/opt/apps/mycat/lib/log4j-slf4j-impl-2.13.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO | jvm 1 | 2023/01/29 17:56:51 | SLF4J: Found binding in [jar:file:/opt/apps/mycat/lib/log4j-slf4j-impl-2.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO | jvm 1 | 2023/01/29 17:56:51 | SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
INFO | jvm 1 | 2023/01/29 17:56:52 | MyCAT Server startup successfully. see logs in logs/mycat.log
# 出现MyCAT Server startup successfully.
测试
# 登录mycat
mysql -h 192.168.42.104 -P 8066 -uroot -p123456
# 然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况。
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DB01 |
+----------+
1 row in set (0.01 sec)
mysql> use DB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables in DB01 |
+----------------+
| tb_order |
+----------------+
1 row in set (0.00 sec)
# 在mycat上创建表
mysql> CREATE TABLE TB_ORDER (
-> id BIGINT(20) NOT NULL,
-> title VARCHAR(100) NOT NULL ,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.06 sec)
OK!
# 此时在物理库上，出现此表
# 插入测试数据
mysql> INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
Query OK, 1 row affected (0.02 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
Query OK, 1 row affected (0.00 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
Query OK, 1 row affected (0.01 sec)
OK!
mysql>
mysql> INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
Query OK, 1 row affected (0.00 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
Query OK, 1 row affected (0.00 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
Query OK, 1 row affected (0.00 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
Query OK, 1 row affected (0.00 sec)
OK!
mysql> INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');
ERROR 1064 (HY000): can't find any valid datanode :TB_ORDER -> ID -> 15000001
经过测试，我们发现，在往 TB_ORDER 表中插入数据时：
- 如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
- 如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
- 如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
- 如果id的值超出1500w，在插入数据时，将会报错。
这是由逻辑表配置时的一个参数 rule 决定的，而这个参数配置的就是分片规则 。在rule.xml文件中找到auto-sharding-long及对应的function
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
在conf目录下找到autopartition-long.txt文件，可以看到这里面规定了分片的规则。
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
2.3 Mycat配置详解
2.3.1 schema.xml
schema标签
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
核心属性：
-
name：指定自定义的逻辑库库名
-
checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
-
sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。
核心属性：
-
name：定义逻辑表表名，在该逻辑库下唯一
-
dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；
多个dataNode逗号分隔 -
rule：分片规则的名字，
分片规则名字是在rule.xml中定义的 -
primaryKey：逻辑表对应真实表的主键
-
type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；
全局表，配置为 global
datanode标签
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />
核心属性：
-
name：定义数据节点名称
-
dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
-
database：定义分片所属数据库
datahost标签
该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
核心属性：
-
name：唯一标识，供上层标签使用
-
maxCon/minCon：最大连接数/最小连接数
-
balance：负载均衡策略，取值 0,1,2,3(后面主从读写分离细讲)
-
writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
-
dbDriver：数据库驱动，支持 native、jdbc
2.3.2 rule.xml
rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。
2.3.3 server.xml
在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性
中配置逻辑表的权限。
2.4 Mycat垂直分库
业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。
现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库
服务器，用户及省市区表拆分到一个服务器。最终结构如下：
2.4.1 配置
schema.xml
<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
<table name="tb_user" dataNode="dn3" primaryKey="id" />
<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
</schema>
<dataNode name="dn1" dataHost="dhost1" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3302?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://192.168.42.104:3303?useSSL=false" user="root" password="123456">
</writeHost>
</dataHost>
server.xml
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">SHOPPING</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">SHOPPING</property>
<property name="readOnly">true</property>
</user>
2.4.2 测试
-- 执行建表语句
source /root/shopping-table.sql
source /root/shopping-insert.sql
-- 登录mycat
-- 在MyCat的命令行中，当我们执行以下多表联查的SQL语句时，可以正常查询出数据。
select
ua.user_id, ua.contact, p.province, c.city, r.area , ua.address
from
tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r
where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id =r.areaid ;
-- 查询每一笔订单及订单的收件地址信息(包含省、市、区)
SELECT
order_id , payment ,receiver, province , city , area
FROM
tb_order_master o, tb_areas_provinces p , tb_areas_city c , tb_areas_region r
WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ;
-- 经过测试，我们看到，SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成SQL语句失败，报错
-- 对于这种情况应该如何解决呢？？？
对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。
修改schema.xml中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在所涉及到的dataNode中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。
<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn3" primaryKey="id" type="global"/>
2.5 Mycat分片规则
2.5.1 范围分片
根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。
schema.xml配置
schema.xml逻辑表配置：
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
schema.xml数据节点配置：
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="auto-sharding-long">
<rule>
<!--columns标识将要分片的表字段-->
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<!--mapFile对应的外部配置文件-->
<property name="mapFile">autopartition-long.txt</property>
<!--defaultNode枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。-->
<property name="defaultNode">0</property>
</function>
在rule.xml中配置分片规则时，关联了一个映射配置文件 autopartition-long.txt，该配置文件的配置如下：
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
含义：
-
0-500万之间的值，存储在0号数据节点(数据节点的索引从0开始) ；
-
500万-1000万之间的数据存储在1号数据节点 ；
-
1000万-1500万的数据节点存储在2号节点 ；
该分片规则，主要是针对于数字类型的字段适用。 在MyCat的入门程序中，使用的就是该分片规则。
2.5.2 取模分片
根据指定的字段值与节点数量进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。
schema.xml配置
schema.xml逻辑表配置：
<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">3</property>
</function>
分片规则属性说明如下：
2.5.3 一致性hash分片
所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的拓容问题。
schema.xml配置
schema.xml逻辑表配置：
<!-- 一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">3</property>
<property name="virtualBucketTimes">160</property>
</function>
2.5.4 枚举分片
通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。
schema.xml配置
schema.xml逻辑表配置：
<!-- 枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus"/>
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<!-- 多个枚举，可以自己增加 tableRule -->
<tableRule name="sharding-by-intfile-enumstatus">
<rule>
<columns>status</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="defaultNode">2</property>
<property name="mapFile">partition-hash-int.txt</property>
</function>
partition-hash-int.txt ，内容如下 :
1=0
2=1
3=2
2.5.5 应用指定分片
直接根据字符子串（必须是数字）计算分片号。
schema.xml配置
schema.xml逻辑表配置：
<!-- 应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-substring">
<rule>
<columns>id</columns>
<algorithm>sharding-by-substring</algorithm>
</rule>
</tableRule>
<function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
<property name="startIndex">0</property> <!-- zero-based -->
<property name="size">2</property>
<property name="partitionCount">3</property>
<property name="defaultPartition">0</property>
</function>
2.5.6 固定分片hash算法
该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与1111111111 进行位 & 运算，位与运算最小值为 0000000000，最大值为1111111111，转换为十进制，也就是位于0-1023之间。
特点：
-
如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
-
可以均匀分配，也可以非均匀分配。
-
分片字段必须为数字类型。
schema.xml配置
schema.xml逻辑表配置：
<!-- 固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-long-hash">
<rule>
<columns>id</columns>
<algorithm>sharding-by-long-hash</algorithm>
</rule>
</tableRule>
<!-- 分片总长度为1024，count与length数组长度必须一致； -->
<function name="sharding-by-long-hash" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">2,1</property>
<property name="partitionLength">256,512</property>
</function>
注意：
1) 分片长度 : 默认最大2^10 , 为 1024 ;
2) count, length的数组长度必须是一致的 ;
以上分为三个分区:0-255,256-511,512-1023
2.5.7 字符串hash解析算法分片
截取字符串中的指定位置的子字符串, 进行hash算法， 算出分片。
schema.xml配置
schema.xml逻辑表配置：
<!-- 字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-stringhash">
<rule>
<columns>name</columns>
<algorithm>sharding-by-stringhash</algorithm>
</rule>
</tableRule>
<function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
<property name="partitionLength">512</property> <!-- zero-based -->
<property name="partitionCount">2</property>
<property name="hashSlice">0:2</property>
</function>
说明
2.5.8 按天分片
按照日期及对应的时间周期来分片。
schema.xml配置
schema.xml逻辑表配置：
<!-- 按天分片 -->
<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-date">
<rule>
<columns>create_time</columns>
<algorithm>sharding-by-date</algorithm>
</rule>
</tableRule>
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2022-01-01</property>
<property name="sEndDate">2022-01-30</property>
<property name="sPartionDay">10</property>
</function>
<!--
从开始时间开始，每10天为一个分片，到达结束时间之后，会重复开始分片插入配置表的 dataNode 的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31 ，每10天一个分片，一共需要37个分片。
-->
2.5.9 自然月分片
使用场景为按照月份来分片, 每个自然月为一个分片。
schema.xml配置
schema.xml逻辑表配置：
<!-- 按自然月分片 -->
<table name="tb_monthpart" dataNode="dn4,dn5,dn6" rule="sharding-by-month" />
schema.xml数据节点配置：
<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置
rule.xml分片规则配置：
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<!--
从开始时间开始，一个月为一个分片，到达结束时间之后，会重复开始分片插入配置表的 dataNode 的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31 ，一共需要12个分片。
-->
<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2022-01-01</property>
<property name="sEndDate">2022-03-31</property>
</function>
2.6 Mycat原理及监控
2.6.1 Mycat原理
在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。
而在MyCat的使用过程中，MyCat官方也提供了一个管理监控平台MyCat-Web（MyCat-eye）。Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白。帮 Mycat分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。
Mycat默认开通2个端口，可以在server.xml中进行修改。
- 8066 数据访问端口，即进行 DML 和 DDL 操作。
- 9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态
# 连接MyCat的管理控制台
mysql -h 192.168.42.104 -p 9066 -uroot -p123456
show @@help 查看Mycat管理工具帮助文档
show @@version 查看Mycat的版本
reload @@config 重新加载Mycat的配置文件
show @@datasource 查看Mycat的数据源信息
show @@datanode 查看MyCat现有的分片节点信息
show @@threadpool 查看Mycat的线程池信息
show @@sql 查看执行的SQL
show @@sql.sum 查看执行的SQL统计
2.6.2 Mycat监控
Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
安装和监控可以参考: https://blog.51cto.com/u_15790101/5717943
3、利用Mycat实现读写分离
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
3.1 Mycat实现一主一从读写分离
主从复制的搭建，可以参考：容器虚拟化技术Docker（二）mysql主从配置案例、redis集群搭建及扩容、缩容案例详解
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。
3.1.1 schema.xml配置
schema.xml逻辑表配置：
<!-- 配置逻辑库 -->
<schema name="DB02" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>
<dataNode name="dn7" dataHost="dhost7" database="db02" />
<!--
writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。 所以我们要想实现读写分离，就得配置writeHost关联的是主库，readHost关联的是从库。
而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个非常重要的负责均衡的参数 balance,几个参数值的含义如下图
所以，在一主一从模式的读写分离中，balance配置1或3都是可以完成读写分离的。
-->
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="jdbc:mysql://192.168.42.104:3307" user="root" password="123456" >
<readHost host="slave1" url="jdbc:mysql://192.168.42.104:3308" user="root" password="123456" />
</writeHost>
</dataHost>
3.1.2 server.xml 配置
配置root用户可以访问DB02逻辑库。
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">DB02</property>
</user>
注意：
当主节点Master宕机之后，业务系统就只能够读，而不能写入数据了。
3.2 Mycat实现双主双从读写分离
一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机。
搭建过程比较麻烦，直接给出Mycat的配置。
<!--配置逻辑库-->
<schema name="DB03" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>
<!--配置数据节点-->
<dataNode name="dn7" dataHost="dhost7" database="db03" />
<!--配置节点主机-->
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306" user="root" password="1234" >
<readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306" user="root" password="1234" />
</writeHost>
<writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306" user="root" password="1234" >
<readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306" user="root" password="123456" />
</writeHost>
</dataHost>
# 相关参数解释
balance="1"
代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡 ;
writeType
0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
1 : 所有的写操作都随机地发送到配置的writeHost上 ;
switchType
-1 : 不自动切换
1 : 自动切换