【MySQL】分库分表（Mycat、MySQL8驱动jar包下载）

欧叶冲冲冲

已于 2023-10-07 16:38:23 修改

阅读量1k

点赞数 2

分类专栏： MySQL 文章标签： mysql adb 数据库学习笔记

于 2023-08-20 22:36:15 首次发布

本文链接：https://blog.csdn.net/YG15165/article/details/132397468

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

目录

前言
一、分库分表
1. 垂直拆分
2. 水平拆分
3. 实现技术

二、Mycat概述
1. 准备环境（下载Mycat以及MySQL8驱动jar包）
2. 概念介绍

三、Mycat入门
1. 环境准备
2. 分片配置

四、Mycat配置
1. schema.xml
1.1. schema标签
1.2. dataNode标签
1.3. dataHost标签

2. rule.xml
3. server.xml
3.1. system标签
3.2. user标签

五、Mycat分片
1. 垂直拆分
2. 水平拆分
3. 分片规则（9种）

六、Mycat管理及监控
1. Mycat原理
2. Mycat管理
3. Mycat监控

总结

前言

我的编程语言学习笔记——MySQL分库分表

一、分库分表

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：

IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘lO，效率较低。请求数据太多，带宽不够，网络I0瓶颈。
CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的（分布式）
拆分策略：

1. 垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

1️⃣每个库的表结构都不一样。
2️⃣每个库的数据也不一样。
3️⃣所有库的并集是全量数据。
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

1️⃣每个表的结构都不一样。
2️⃣每个表的数据也不一样，一般通过一列（主键/外键）关联。
3️⃣所有表的并集是全量数据。

2. 水平拆分

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
1️⃣个库的表结构都一样。
2️⃣每个库的数据都不一样。
3️⃣所有库的并集是全量数据。
水平分表：以字段为依据，按照一定策略，将一今表的数据拆分到多个表中。

1️⃣每个表的表结构都一样。
2️⃣每个表的数据都不一样。
3️⃣所有表的并集是全量数据。

3. 实现技术

shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
Mycat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

二、Mycat概述

Mycat是开源的、活跃的、基于Java语言编写的My5QL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在(伪装MySQL协议)

1. 准备环境（下载Mycat以及MySQL8驱动jar包）

下载：Mycat是采用java语言开发的开源的数据库中间件，支持Wwindows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件（MySQL（下载方法详见）、JDK（下载方法详见）、Mycat）

# 210上下载Mycat1.6.7.4
wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz
# 解压
tar -zxvf Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz -C /usr/local/
# 查看解压后的mycat目录结构
cd /usr/local/mycat ; ll

在这里插入图片描述

目录结构：
bin：存放可执行文件，用于启动停止mycat
conf：存放mycat的配置文件
lib：存放mycat的项目依赖包（jar)
logs：存放mycat的日志文件

# 进入lib，删除旧版本的jar包
rm -rf mysql-connector-java-5.1.35.jar
# 下载当前MySQL版本的jar包
wget -P /root/ https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-j-8.1.0.tar.gz
tar -zxvf /root/mysql-connector-j-8.1.0.tar.gz -C /root/test/
cp /root/test/mysql-connector-j-8.1.0/mysql-connector-j-8.1.0.jar /usr/local/mycat/lib/
# 赋予权限
chmod 777 mysql-connector-j-8.1.0.jar

2. 概念介绍

介绍图：

三、Mycat入门

由于tb_order表中数据量很大，磁盘to及容量都到达了瓶颈，现在需要对tb_order表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上,具体的结构，参考下图:

1. 环境准备

在这里插入图片描述

# 检查三台服务器防火墙是否关闭(要关闭它)
systemctl status firewalld
# 分别在三台服务器上创建db01数据库
create database db01;

2. 分片配置

分片解析

修改配置文件：vim /usr/local/mycat/conf/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.150.103:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
        </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.150.104:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
        </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.150.105:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
        </dataHost>
</mycat:schema>

配置mycat的用户及用户的权限信息：vim /usr/local/mycat/conf/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">123456</property>
    <property name="schemas">DB01</property>
    <property name="readOnly">true</property>
</user>

启动Mycat：切换到MyCat的安装目录，执行如下指令，启动MyCat

# 启动
bin/mycat start
# 停止
bin/mycat stop

Mycat启动之后，占用端口号8066

分片测试：通过如下指令，就可以连接并登录MyCat

 mysql -h 192.168.150.103 -P 8066 -uroot -p123456

然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况

CREATE TABLE TB_ORDER (
  id BIGINT(20) NOT NULL,
  title VARCHAR(100) NOT NULL ,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

# 第一个节点
INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
INSERT INTO TB_ORDER(id,title) VALUES(1000000,'goods1000000');
INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');

# 五百万之后落到第二节点
INSERT INTO TB_ORDER(id,title) VALUES(5000001,'goods5000001');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');

# 一千万以上，一千五以下，第二节点，超过一千五百万报错，需增加新的节点
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');

四、Mycat配置

1. schema.xml

schema.xml作为MyCat中最重要的配置文件之一，涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置，主要包括以下三组标签：schema标签、datanode标签、datahost标签

1.1. schema标签

schema标签用于定义MyCat实例中的逻辑库，一个Mycat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。
MyCat中的逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库(use xxx)

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

schema标签中的核心属性：

name：指定自定义的逻辑库库名（区分大小写）
checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除（true自动去除，false不自动去除）

sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

子标签table：table标签定义了MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在table标签中定义。一个schema下可以有多个table。

table核心属性：

name：定义逻辑表表名，在该逻辑库下唯一
dataNode：定义逻辑表所属的数据节点，该属性需要与dataNode标签中name对应；多个dataNode用逗号分隔
rule：分片规则的名字，分片规则名字是在rule.xml中定义的
primaryKey：逻辑表对应真实表的主键
type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global

1.2. dataNode标签

dataNode标签中定义了MyCat中的数据节点，也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

核心属性：

name ：定义数据节点名称，需要与schema标签中的子标签table的dataNode对应
dataHost ：数据库实例主机名称，引用自dataHost 标签中name 属性
database ：定义分片所属数据库，也就是真实数据库

1.3. dataHost标签

dataHost标签在MyCat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分类、心跳语句。

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc">
    <heartbeat>select user()</heartbeat>

    <writeHost host="master" url="jdbc:mysql://192.168.88.135:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
</dataHost>

核心属性：

name ：唯一标识，供上层标签使用
maxCon/minCon ：最大连接数/最小连接数
balance：负载均衡策略，取值0,1,2,3
writeType ：写操作分布方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
dbDriver ：数据库驱动，支持native、jdbc

2. rule.xml

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function。

在这里插入图片描述

注意：只有涉及到分表的时候，才会使用分片规则。

3. server.xml

server.xml配置文件包含了MyCat的系统配置环境信息，主要有两个重要的标签：system、user。

3.1. system标签

system标签用于配置系统级别的属性和参数。它是全局配置，适用于整个Mycat实例。

在Mycat中，system标签用于配置系统级别的属性和参数。它是全局配置，适用于整个Mycat实例。
system标签的常见属性包括：

balance：指定负载均衡算法，默认为modlong。
dataHostRetry：指定数据节点重试次数，默认为1。
heartbeatSwitch：指定是否开启心跳检测，默认为false。
whiteListSwitch：指定是否开启白名单功能，默认为false。
randomStandbySwitch：指定是否开启随机备份节点的选择，默认为false。
defaultDatasource：指定默认的数据源。

 <system>
     <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
     <property name="useHandshakeV10">1</property>
     <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
</system>

对应的系统配置项及其含义（部分）

属性	取值	含义
charset	utf8	设置Mycat的字符集，字符集需要与MySQL的字符集保持一致
nonePasswordLogin	0,1	0为需要密码登陆、1为不需要密码登陆默认为0。设置为1则需要指定默认账户
useHandshakeV10	0,1	使用该选项主要的目的是为了能够兼容高版本的`jdbc`驱动,是否采用`HandshakeV10Packet`来与`client`进行通信,1:是,0否
useSqlStat	0,1	开启SQL实时统计,1为开启,0为关闭;开启之后,MyCat会自动统计SQL语句的执行情况;`mysql -h 127.0.0.1 -P 9066 -u root -p`查看MyCat执行的SQL,执行效率比较低的SQL,SQL的整体执行情况、读写比例等;`show@@sql;` 、`show @@sql.slow;` 、`show @@sql.sum;`
useGlobleTableCheck	0,1	是否开启全局表的一致性检测。1为开启，0为关闭
sqlExecuteTimeout	1000	SQL语句执行的超时时问单位为s，默认300s
sequnceHandlerType	0,1,2	用来指定Mycat全局序列类型,0为本地文件,1为数据库方式,2为时间戳列方式。默认使用本地文件方式,文存方式主要用于测试
sequnceHandlerPattern	正则表达式	必须带有`MYCATSEQ`或者`mycatseq`进入序列匹配流程注意`MYCATSEQ_`有空格的情况
subqueryRelationshipCheck	true,false	子查沟中存在关联查询的情况下,检查关联字段中是否有分片字段,默认false
fakeMySQLVersion	8.0.33,8.0.34	设置模拟的MySQL版本号
defaultSqlParser		Mycat1.4之后的默认SQL解析器
idleTimeout	30	指定连接的空闲时间的超时长度；如果超时间关闭资源并回收，默认30分钟
txisolation	1,2,3,4	初始化前端连接的事务隔离级别,默认为 `REPEATED_READ`，对应数字为3。`READ_UNCOMMITED=1`; `READ_COMMITTED=2`;`REPEATED_READ=3`;`SERIALIZABLE=4`
serverPort	8066	定义Mycat的使用端口，默认8066
managerPort	9066	定义Mycat的管理端口，默认9066

通过配置system标签，可以设置Mycat的全局属性和行为。这些属性可以影响Mycat的负载均衡、故障恢复、安全性等方面的功能。根据实际需求，可以对system标签进行相应的配置来满足业务需求。

3.2. user标签

在Mycat中，user标签用于配置数据库用户的访问权限和连接池设置。每个user标签定义了一个数据库用户，并指定了该用户的相关属性。

user标签的常见属性包括：

name：指定数据库用户的名称。
password：指定数据库用户的密码。
schemas：指定该用户可以访问的数据库模式（schema）。
readOnly：指定该用户是否只能进行读操作，默认为false。
defaultDatasource：指定该用户默认的数据源。
checkSQL：指定在连接池中从连接池中获取连接前执行的SQL语句。

在这里插入图片描述
其中若未配置逻辑表的权限，则按照逻辑数据库的DML权限；若配置逻辑表的权限，则按照逻辑表的DML权限。

通过配置user标签，可以实现对不同用户的访问控制、权限管理和负载均衡。Mycat会根据用户的配置，将相应的SQL请求路由到指定的数据源，并根据用户的权限限制用户的操作。

五、Mycat分片

1. 垂直拆分

场景：在业务系统中，涉及以下表结构，但是由于用户与订单每天都会产生大量的数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分，原有的数据库表如下

配置（准备shopping表）：

1️⃣schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<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.150.102:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
	</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.150.103:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </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.150.104:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
    </dataHost>

</mycat:schema>

2️⃣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">123456</property>
    <property name="schemas">SHOPPING</property>
    <property name="readOnly">true</property>
</user>

测试：

# 重启Mycat
bin/mycat stop
bin/mycat start

# 查看是否正常启动
tail -f logs/wrapper.log

# 在mycat的命令行中，通过source指令导入表结构(两个sql文件已导入到根目录下)，以及对应的数据，查看数据分布情况
source /root/shopping-table.sql
source /root/shopping-insert.sql

# 查询用户的收件人及收件人地址信息（包含省、市、区）
select ua.user_id, ua.contact, p.province, c.city, r.area, ua.address from tb_user_address ua, tb_areas_provinces p, tb_areas_city c, 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语句会报错，因为tb_areas_provinces、tb_areas_city和tb_areas_region在第三个切片中，解决方案如下：

全局表配置：对于省、市、区/县表 tb_areas_provinces、tb_areas_city、tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作

<!-- 在schema标签中添加 -->
<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn1,dn2,dn3" primaryKey="id" type="global"/>

设置完scheme.xml文件之后，需要重启mycat服务，清空原shopping表并且重新通过source指令重新导入表结构，以及对应的数据；最后再重新执行上述SQL语句

2. 水平拆分

场景：在业务系统中，有一张表（日志表），业务系统每天会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分

在这里插入图片描述

配置（创建itcast表）：

1️⃣schema.xml

<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
    <table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

2️⃣server.xml

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,ITCAST</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>

测试

# 在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况
CREATE TABLE tb_log (
	id bigint(20) NOT NULL COMMENT 'ID',
	model_name varchar(200) DEFAULT NULL COMMENT '模块名',
	model_value varchar(200) DEFAULT NULL COMMENT '模块值',
	return_value varchar(200) DEFAULT NULL COMMENT '返回值',
	return_class varchar(200) DEFAULT NULL COMMENT '返回值类型',
	operate_user varchar(20) DEFAULT NULL COMMENT '操作用户',
	operate_time varchar(20) DEFAULT NULL COMMENT '操作时间',
	param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
	operate_class varchar(200) DEFAULT NULL COMMENT '操作类',
	operate_method varchar(200) DEFAULT NULL COMMENT '操作方法',
	cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
	source int(1) DEFAULT NULL COMMENT '来源 : 1 PC , 2 Android , 3 IOS',
	PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time，source) VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);

3. 分片规则（9种）

改变好分片规则后都需要重启Mycat更新规则，并检查Mycat日志是否成功防止出错

范围：根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片

根据id大小配置：

范围分片针对于数字类型的字段，不适用于字符串类型的字段

取模：范围分片针对于数字类型的字段，不适用于字符串类型的字段

根据id取模配置：

一致性hash：相同的hash因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置

根据id配置：

# 改好规则后Mycat上有逻辑上的tb_order表，实际上还需在Mycat上创建
create table tb_order(
    id  varchar(100) not null primary key,
    money   int null,
    content varchar(200) null
);

INSERT INTO tb_order (id, money, content) VALUES ('b92fdaaf-6fc4-11ec-b831-482ae33c4a2d', 10, 'b92fdaf8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93482b6-6fc4-11ec-b831-482ae33c4a2d', 20, 'b93482d5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b937e246-6fc4-11ec-b831-482ae33c4a2d', 50, 'b937e25d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93be2dd-6fc4-11ec-b831-482ae33c4a2d', 100, 'b93be2f9-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93f2d68-6fc4-11ec-b831-482ae33c4a2d', 130, 'b93f2d7d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9451b98-6fc4-11ec-b831-482ae33c4a2d', 30, 'b9451bcc-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9488ec1-6fc4-11ec-b831-482ae33c4a2d', 560, 'b9488edb-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94be6e6-6fc4-11ec-b831-482ae33c4a2d', 10, 'b94be6ff-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94ee10d-6fc4-11ec-b831-482ae33c4a2d', 123, 'b94ee12c-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b952492a-6fc4-11ec-b831-482ae33c4a2d', 145, 'b9524945-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95553ac-6fc4-11ec-b831-482ae33c4a2d', 543, 'b95553c8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9581cdd-6fc4-11ec-b831-482ae33c4a2d', 17, 'b9581cfa-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95afc0f-6fc4-11ec-b831-482ae33c4a2d', 18, 'b95afc2a-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95daa99-6fc4-11ec-b831-482ae33c4a2d', 134, 'b95daab2-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9667e3c-6fc4-11ec-b831-482ae33c4a2d', 156, 'b9667e60-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96ab489-6fc4-11ec-b831-482ae33c4a2d', 175, 'b96ab4a5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96e2942-6fc4-11ec-b831-482ae33c4a2d', 180, 'b96e295b-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b97092ec-6fc4-11ec-b831-482ae33c4a2d', 123, 'b9709306-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b973727a-6fc4-11ec-b831-482ae33c4a2d', 230, 'b9737293-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b978840f-6fc4-11ec-b831-482ae33c4a2d', 560, 'b978843c-6fc4-11ec-b831-482ae33c4a2d');

枚举：通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份、性别、状态拆分数据等业务

根据字段status进行枚举分片：

可以自定义规则sharding-by-intfile-enumstatus，使其专门用于status字段。配置了默认节点后，异常数据（比如4）都分到默认节点中

CREATE TABLE tb_user (
  id bigint(20) NOT NULL COMMENT 'ID',
  username varchar(200) DEFAULT NULL COMMENT '姓名',
  status int(2) DEFAULT '1' COMMENT '1: 未启用, 2: 已启用, 3: 已关闭',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_user (id,username ,status) values(1,'Tom',1);
insert into tb_user (id,username ,status) values(2,'Cat',2);
insert into tb_user (id,username ,status) values(3,'Rose',3);
insert into tb_user (id,username ,status) values(4,'Coco',2);
insert into tb_user (id,username ,status) values(5,'Lily',1);
insert into tb_user (id,username ,status) values(6,'Tom',1);
insert into tb_user (id,username ,status) values(7,'Cat',2);
insert into tb_user (id,username ,status) values(8,'Rose',3);
insert into tb_user (id,username ,status) values(9,'Coco',2);
insert into tb_user (id,username ,status) values(10,'Lily',1);

应用指定：运行阶段由应用自主决定路由到哪个分片，直接根据字符子串（必须是数字）计算分片号

根据id字符字串进行分片：

CREATE TABLE tb_app (
  id varchar(10) NOT NULL COMMENT 'ID',
  name varchar(200) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_app (id,name) values('0000001','Testx00001');
insert into tb_app (id,name) values('0100001','Test100001');
insert into tb_app (id,name) values('0100002','Test200001');
insert into tb_app (id,name) values('0200001','Test300001');
insert into tb_app (id,name) values('0200002','TesT400001');

固定分片hash算法：该算法类似于十进制的求模运算，但是为二进制的操作，例如，取id的二进制低10位与1111111111进行位&运算

特点：
1️⃣如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
2️⃣可以均匀分配，也可以非均匀分配。
3️⃣分片字段必须为数字类型。
根据id数字值与一个数按位与运算：

CREATE TABLE tb_longhash (
  id int(11) NOT NULL COMMENT 'ID',
  name varchar(200) DEFAULT NULL COMMENT '名称',
  firstChar char(1)  COMMENT '首字母',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_longhash (id,name,firstChar) values(1,'七匹狼','Q');
insert into tb_longhash (id,name,firstChar) values(2,'八匹狼','B');
insert into tb_longhash (id,name,firstChar) values(3,'九匹狼','J');
insert into tb_longhash (id,name,firstChar) values(4,'十匹狼','S');
insert into tb_longhash (id,name,firstChar) values(5,'六匹狼','L');
insert into tb_longhash (id,name,firstChar) values(6,'五匹狼','W');
insert into tb_longhash (id,name,firstChar) values(7,'四匹狼','S');
insert into tb_longhash (id,name,firstChar) values(8,'三匹狼','S');
insert into tb_longhash (id,name,firstChar) values(9,'两匹狼','L');

字符串hash解析算法：截取字符串中的指定位置的子字符串，进行hash算法，算出分片

根据name进行分片：

create table tb_strhash(
	name varchar(20) primary key,
	content varchar(100)
)engine=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID());
INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID());
INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID());
INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID());
INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());

按（天）日期分片：

举个例子：

create table tb_datepart(
    id   bigint  not null comment 'ID'  primary key,
    name varchar(100) null comment '姓名',
    create_time date  null
);

# 插入不同时间的数据
insert into tb_datepart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_datepart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_datepart(id,name ,create_time) values(3,'Rose','2022-01-11');
insert into tb_datepart(id,name ,create_time) values(4,'Coco','2022-01-20');
insert into tb_datepart(id,name ,create_time) values(5,'Rose2','2022-01-21');
insert into tb_datepart(id,name ,create_time) values(6,'Coco2','2022-01-30');
insert into tb_datepart(id,name ,create_time) values(7,'Coco3','2022-01-31');

按自然月分片：使用场景为按照月份来分片，每个自然月为一个分片

举个例子：

create table tb_monthpart(
    id   bigint  not null comment 'ID'  primary key,
    name varchar(100) null comment '姓名',
    create_time date  null
);

insert into tb_monthpart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_monthpart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_monthpart(id,name ,create_time) values(3,'Rose','2022-01-31');
insert into tb_monthpart(id,name ,create_time) values(4,'Coco','2022-02-20');
insert into tb_monthpart(id,name ,create_time) values(5,'Rose2','2022-02-25');
insert into tb_monthpart(id,name ,create_time) values(6,'Coco2','2022-03-10');
insert into tb_monthpart(id,name ,create_time) values(7,'Coco3','2022-03-31');
insert into tb_monthpart(id,name ,create_time) values(8,'Coco4','2022-04-10');
insert into tb_monthpart(id,name ,create_time) values(9,'Coco5','2022-04-30');

六、Mycat管理及监控

1. Mycat原理

对insert语句进行分析
对select语句进行分析

2. Mycat管理

Mycat默认开通2个端口，可以在server.xml中进行修改。

8066数据访问端口，即进行DML和DDL操作。
9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。

# 连接登录mycat的9066端口
mysql -h192.168.150.102 -P9066 -u root -p

Mycat的管理指令

命令	含义
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统计

注意：

如果使用show @@sql查询执行的SQL语句，结果一直为Empty Set。
需要在mycat的配置文件server.xml中将下列属性值修改为1<property name="useSqlStat">1</property> 

3. Mycat监控

Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server`使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器（目前仅限于Linux系统）的CPU、内存、网络、磁盘。

Mycat-eye运行过程中需要依赖ZooKeeper，因此需要先安装ZooKeeper

安装Zookeeper、Mycat-web

# 下载Zookeeper
wget https://dlcdn.apache.org/zookeeper/zookeeper-3.9.0/apache-zookeeper-3.9.0-bin.tar.gz
# 解压并改名
tar -zxvf apache-zookeeper-3.9.0-bin.tar.gz -C /opt/module ; mv apache-zookeeper-3.9.0-bin/ zookeeper-3.9.0 
# 创建data文件夹并获取路径
cd /opt/module/zookeeper-3.9.0 ; mkdir data ;cd data ; pwd 
# 修改配置文件名称并配置（将dataDir后面路径修改为data文件夹的路径）
cd /opt/module/zookeeper-3.9.0/conf ; mv zoo_sample.cfg zoo.cfg ; ll
vim zoo.cfg
# 启动并查看是否启动成功（standalone）
cd /opt/module/zookeeper-3.9.0 ; bin/zkServer.sh start ; bin/zkServer.sh status

# 安装Mycat-web,如果下载失败就先下载到windows系统中再用sftp命令put进Linux中
cd /opt/software ; wget  https://github.com/MyCATApache/Mycat-download/blob/master/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz
# 解压
cd /opt/software ; tar -zxvf Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz -C /opt/module/
# 启动
cd /opt/module/mycat-web;sh start.sh

如果Zookeeper与Mycat-web不在同一台服务器上 , 需要在/opt/module/mycat-web/mycat-web/WEB-INF/classes/mycat.properties文件中配置Zookeeper的地址

Mycat-web目录介绍

etc ：jetty配置文件
lib ：依赖jar包
mycat-web ：mycat-web项目
readme.txt
start.jar ：启动jar
start.sh ：linux启动脚本

启动后Mycat-web网页端👉点击前往

# 首先要开启Mycat的事实统计功能（server.xml）
<property name="useSqlStat">1</property>  <!-- 1为开启实时统计、0为关闭 -->

在这里插入图片描述

总结

✍分库分表核心：将单台数据库服务器的数据分散（垂直拆分、水平拆分）存储在多台数据库中

✍MyCat是一个开源的分库分表的中间件，最主要的三个配置文件Schema.xml、 rule.xml、server.xml，分片规则有范围、取模、枚举、一致性hash、固定分片hash算法、字符串hash解析算法、按天分片、按自然月分片

✍下一节：读写分离！

欧叶冲冲冲

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
【MySQL】分库分表（Mycat、MySQL8驱动jar包下载）

我的编程语言学习笔记——MySQL分库分表Mycat是开源的、活跃的、基于Java语言编写的My5QL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在(伪装MySQL协议)✍分库分表核心：将单台数据库服务器的数据分散（垂直拆分、水平拆分）存储在多台数据库中✍MyCat是一个开源的分库分表的中间件，最主要的三个配置文件Schema.xmlrule.xmlserver.xml。
复制链接

扫一扫