Mysql 分库分表和 MyCat 入门

快乐的小三菊

已于 2024-06-19 11:08:32 修改

阅读量632

点赞数

分类专栏： mysql 专栏文章标签： mysql

于 2018-07-03 20:28:25 首次发布

本文链接：https://blog.youkuaiyun.com/xhf852963/article/details/80903961

版权

mysql 专栏专栏收录该内容

21 篇文章

订阅专栏

一、简介

1.1 问题分析

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

1、IO 瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘 IO，效率较低。请求数据太多，带宽不够，网络 IO 瓶颈。

2、cpu 瓶颈：排序、分组、连接查询、聚合统计等 sql 会耗费大量的 cpu 资源，请求数太多，cpu 出现瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

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

1.2 拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

1.3 垂直拆分

1.3.1 垂直分库

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

特点：

1、每个库的表结构都不一样。

2、每个库的数据也不一样。

3、所有库的并集是全量数据。

1.3.2 垂直分表

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

1、每个表的结构都不一样。

2、每个表的数据也不一样，一般通过一列（主键/外键）关联。

3、所有表的并集是全量数据。

1.4 水平拆分

1.4.1 水平分库

水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。

特点：

1、每个库的表结构都一样。

2、每个库的数据都不一样。

3、所有库的并集是全量数据。

1.4.2 水平分表

水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。

特点：

1、每个表的表结构都一样。

2、每个表的数据都不一样。

3、所有表的并集是全量数据。

1.5 实现技术

如果想要实现分库分表，可以有两种方式，如下：

1、shardingJDBC：基于 AOP 原理，在应用程序中对本地执行的 sql 进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持 java 语言，性能较高。

2、MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

本次课程，我们选择了是 MyCat 数据库中间件，通过 MyCat 中间件来完成分库分表操作。

1.6 总结

在业务系统中，为了缓解磁盘 IO 及 CPU 的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

二、MyCat 概述

2.1 简介

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

开发人员只需要连接 MyCat 即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在 MyCat 中配置即可。

优势：

1、性能可靠稳定

2、强大的技术团队

3、体系完善

4、社区活跃

2.2 下载

mycat 安装包的下载地址在这，由于官网现在不再更新维护了，只能从这里进行下载了，提取码为 gyaz。

需要用到的 jdk 的安装包地址在这，提取码为 uxne。

需要用到的高版本的 jdbc 的 jar 包地址在这，提取码为 g4q5。

2.3 安装

Mycat 是采用 java 语言开发的开源的数据库中间件，支持 Windows 和 Linux 运行环境，下面介绍 MyCat 的 Linux 中的环境搭建。我们需要在准备好的服务器中安装如下软件。

服务器	安装软件	说明
192.168.229.158	JDK、Mycat	MyCat 中间件服务器
192.168.229.158	MySQL	分片服务器
192.168.229.162	MySQL	分片服务器
192.168.229.163	MySQL	分片服务器

2.3.1 jdk 安装

1、把下载好的安装包拷贝到 linux 服务器上，如下图：

2、解压安装包到指定目录下

# 解压到指定目录下
tar -zxvf jdk-8u171-linux-x64.tar.gz -C /usr/local

3、配置环境变量，使用修改 /etc/profile 文件，在文件末尾加入如下配置

JAVA_HOME=/usr/local/jdk1.8.0_171
PATH=$JAVA_HOME/bin:$PATH

4、重新加载 profile 文件

source /etc/profile

5、检查安装是否成功

java -version

2.3.2 mycat 安装

1、把下载好的安装包拷贝到 linux 服务器上，如下图：

2、解压安装包到指定目录下

tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /usr/local/

3、将高版本 jar 复制过来替换低版本 jar 包，执行下面的命令

cd /usr/local/mycat/lib

# 删除低版本 jar 包
rm -rf mysql-connector-java-5.1.35.jar

# 切换到根目录下
cd /

# 添加高版本 jar 包
mv mysql-connector-java-8.0.22.jar /usr/local/mycat/lib/

cd /usr/local/mycat/lib

# 赋权
chmod 777 mysql-connector-java-8.0.22.jar

2.4 目录介绍

进入到 mycat 的安装目录下，可以发现 4 个文件夹，如下所示：

1、bin：存放可执行文件，用于启动停止 mycat

2、conf：存放 mycat 的配置文件

3、lib：存放 mycat 的项目依赖包（jar）

4、logs：存放 mycat 的日志文件

2.5 概念介绍

在 MyCat 的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。

在 MyCat 的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。

三、MyCat 入门

3.1 需求

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

3.2 环境准备

准备 3 台服务器：

192.168.229.158：MyCat 中间件服务器，同时也是第一个分片服务器。

192.168.229.162：第二个分片服务器。

192.168.229.163：第三个分片服务器。

并且在上述 3 台数据库中创建数据库 db01 。

3.3 配置

3.3.1 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.229.158:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</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.229.162:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</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.229.163:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</dataHost>
</mycat:schema>

3.3.2 server.xml

需要在 server.xml 中配置用户名、密码，以及用户的访问权限信息，具体的配置如下，下面只是部分的配置信息，其他的配置信息不需要修改

	<user name="root" defaultAccount="true">
		<property name="password">1234</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 这个逻辑库，访问密码都是 1234，但是 root 用户访问 DB01 逻辑库，既可以读，又可以写，但是 user 用户访问 DB01 逻辑库是只读的。

3.4 测试

3.4.1 启动

配置完毕后，先把 3 台服务器的防火墙关闭掉，然后再启动 MyCat 服务器。切换到 Mycat 的安装目录，执行如下指令，启动 Mycat：

# 启动
bin/mycat start

# 停止
bin/mycat stop

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

启动完毕之后，可以查看 logs 目录下的启动日志，查看 Mycat 是否启动完成。

3.4.2 测试

1、连接 MyCat，通过如下指令，就可以连接并登陆 MyCat。

mysql -h 192.168.229.158 -P 8066 -uroot -p1234

可以看到我们是通过 MySQL 的指令来连接的 MyCat，因为 MyCat 在底层实际上是模拟了 MySQL 的协议。

2、在 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(5000000,'goods5000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');

经过测试，我们发现，在往 TB_ORDER 表中插入数据时：

1、如果 id 的值在 1-500w 之间，数据将会存储在第一个分片数据库中。

2、如果 id 的值在 500w-1000w 之间，数据将会存储在第二个分片数据库中。

3、如果 id 的值在 1000w-1500w 之间，数据将会存储在第三个分片数据库中。

4、如果 id 的值超出 1500w，在插入数据时，将会报错。

为什么会出现这种现象，数据到底落在哪一个分片服务器到底是如何决定的呢？这是由逻辑表配置时的一个参数 rule 决定的，而这个参数配置的就是分片规则，关于分片规则的配置，在后面的课程中会详细讲解。

四、MyCat 配置

4.1 schema.xml

schema.xml 作为 MyCat 中最重要的配置文件之一 , 涵盖了 MyCat 的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含了三组标签：schema 标签、datanode 标签、datahost 标签。

4.1.1 schema 标签

4.1.1.1 schema 定义逻辑库

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

核心属性：

1、name：指定自定义的逻辑库库名

2、checkSQLschema：在 SQL 语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除。

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

4.1.1.2 schema 中的 table 定义逻辑表

table 标签定义了 MyCat 中逻辑库 schema 下的逻辑表 , 所有需要拆分的表都需要在 table 标签中定义。

核心属性：

1、name：定义逻辑表表名，在该逻辑库下唯一。

2、dataNode：定义逻辑表所属的 dataNode，该属性需要与 dataNode 标签中 name 对应；多个 dataNode 逗号分隔。

3、rule：分片规则的名字，分片规则名字是在 rule.xml 中定义的。

4、primaryKey：逻辑表对应真实表的主键。

5、type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global。

4.1.2 datanode 标签

核心属性：

1、name：定义数据节点名称。

2、dataHost：数据库实例主机名称，引用自 dataHost 标签中 name 属性。

3、database：定义分片所属数据库。

4.1.3 datahost 标签

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

核心属性：

1、name：唯一标识，供上层标签使用。

2、maxCon/minCon：最大连接数/最小连接数

3、balance：负载均衡策略，取值 0,1,2,3

4、writeType：写操作分发方式（0：写操作转发到第一个 writeHost，第一个挂了，切换到第二个。1：写操作随机分发到配置的 writeHost）

5、dbDriver：数据库驱动，支持 native、jdbc

4.2 rule.xml

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

rule.xml 的部分内容如下所示：

autopartition-long.txt 的内容如下所示：

4.3 server.xml

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

4.3.1 system 标签

主要配置 MyCat 中的系统配置信息，对应的系统配置项及其含义，如下：

属性	取值	含义
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 ;
sequnceHandlerType	0,1,2	用来指定 Mycat 全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试
sequnceHandlerPattern	正则表达式	必须带有 MYCATSEQ 或者 mycatseq 进入序列匹配流程注意MYCATSEQ_有空格的情况
subqueryRelationshipCheck	true,false	子查询中存在关联查询的情况下，检查关联字段中是否有分片字段 .默认 false
useCompression	0,1	开启 mysql 压缩协议 , 0 : 关闭, 1 : 开启
fakeMySQLVersion	5.5,5.6	设置模拟的 MySQL 版本
defaultSqlParser		由于 MyCat 的最初版本使用了 FoundationDB 的SQL 解析器, 在MyCat1.3 后增加了 Druid 解析器, 所以要设置 defaultSqlParser 属性来指定默认的解析器; 解析器有两个 : druidparser 和 fdbparser, 在 MyCat1.4之后,默认是 druidparser, fdbparser已经废除了
processors	1,2....	指定系统可用的线程数量, 默认值为 CPU 核心 x 每个核心运行线程数量; processors 会影响 processorBufferPool, processorBufferLocalPercent, processorExecutor 属性, 所有, 在性能调优时, 可以适当地修改 processors值
processorBufferChunk		指定每次分配 Socket Direct Buffer 默认值为4096字节, 也会影响BufferPool 长度, 如果一次性获取字节过多而导致 buffer 不够用, 则会出现警告, 可以调大该值
processorExecutor		指定 NIOProcessor 上共享 businessExecutor 固定线程池的大小; MyCat 把异步任务交给 businessExecutor 线程池中, 在新版本的MyCat 中这个连接池使用频次不高, 可以适当地把该值调小
packetHeaderSize		指定 MySQL 协议中的报文头长度, 默认4个字节
maxPacketSize		指定 MySQL 协议可以携带的数据最大大小, 默认值为 16M
idleTimeout	30	指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟
txIsolation	1,2,3,4	初始化前端连接的事务隔离级别,默认为 REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3; SERIALIZABLE=4;
sqlExecuteTimeout	300	执行 SQL 的超时时间, 如果 SQL 语句执行超时, 将关闭连接; 默认300秒
serverPort	8066	定义 MyCat 的使用端口, 默认8066
managerPort	9066	定义 MyCat 的管理端口, 默认9066

4.3.2 user 标签

配置 MyCat 中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下（1：有权限；0：没有权限）：

在测试权限操作时，我们只需要将 privileges 标签的注释放开。在 privileges 下的 schema 标签中配置的 dml 属性配置的是逻辑库的权限。在 privileges 的 schema 下的 table 标签的 dml 属性中配置逻辑表的权限。