16. 分库分表

3.1 介绍

3.1.1 问题分析

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

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

为了解决上述问题，我们需要对数据库进行分库分表处理。分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

3.1.2 拆分策略

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

3.1.3 垂直拆分

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

每个库的表结构都不一样。
每个库的数据也不一样。
所有库的并集是全量数据。

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

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

3.1.4 水平拆分

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

每个库的表结构都一样。
每个库的数据都不一样。
所有库的并集是全量数据。

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

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

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

3.1.5 实现技术

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

3.2 Mycat概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用 mycat，对于开发人员来说根本感觉不到mycat的存在。开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。具体的分库分表的策略，只需要在MyCat中配置即可。优势：

性能可靠稳定
强大的技术团队
体系完善
社区活跃

3.2.1 安装

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

3.2.2 概念介绍

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

3.3 Mycat入门

3.3.1 需求

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

3.3.2 环境准备

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

3.3.3 配置

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.10.101:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="980918" />
	</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.10.107:3316?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="980918" />
	</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.10.108:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="980918" />
	</dataHost>
 

</mycat:schema>

server.xml 需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：

<user name="root" defaultAccount="true">
	<property name="password">980918</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">980918</property>
	<property name="schemas">DB01</property>
	<property name="readOnly">true</property>
</user>

上述的配置表示，定义了两个用户 root 和 user ，这两个用户都可以访问 DB01 这个逻辑库，访问密码都是123456，但是root用户访问DB01逻辑库，既可以读，又可以写，但是 user用户访问 DB01逻辑库是只读的。

3.3.4 测试

配置完毕后，先启动涉及到的3台分片服务器，然后启动MyCat服务器。切换到Mycat的安装目录，执行如下指令，启动Mycat：启动：bin/mycat start 停止：bin/mycat stop Mycat启动之后，占用端口号 8066。启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。连接Mycat mysql -h 192.168.200.210 -P 8066 -uroot -p123456 我们看到我们是通过MySQL的指令来连接的MyCat，因为MyCat在底层实际上是模拟了MySQL的协议。然后就可以在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(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 表中插入数据时：

如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
如果id的值超出1500w，在插入数据时，将会报错。

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

3.4 Mycat配置

3.4.1 schema.xml

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

schema标签
datanode标签
datahost标签

schema标签

1	`select * from DB01.TB_ORDER;`

datanode标签

datahost标签

3.4.2 rule.xml

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

3.4.3 server.xml

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

system标签

server系统配置信息含义.xlsx

user标签

配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下：在测试权限操作时，我们只需要将 privileges 标签的注释放开。在 privileges 下的schema 标签中配置的dml属性配置的是逻辑库的权限。在privileges的schema下的table标签的dml属性中配置逻辑表的权限。

3.5 Mycat分片

垂直拆分

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

配置

3.5.1 范围分片

按照特性的范围区间（比如时间区间、ID 区间）来分配数据，比如将 id 为 1~299999 的记录分到第一个库， 300000~599999 的分到第二个库。范围分片适合需要经常进行范围查找的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。

3.5.2 取模分片

根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。指定节点数

3.5.3 哈希分片

求指定 key（比如 id）的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。

3.5.4 一致性哈希分片

一致性哈希分片算法利用哈希环的思想，对数据进行哈希映射，并通过顺时针寻找最近的服务节点来进行数据的存储。

3.5.5 枚举分片

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

3.5.6 应用指定规则

3.5.7 固定分片hash算法

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

特点：

如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
可以均匀分配，也可以非均匀分配。
分片字段必须为数字类型。

3.5.8 字符串hash解析算法

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

3.5.9 按天分片

3.5.10 自然月

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

3.6 Mycat管理及监控

3.6.1 Mycat原理

3.6.2 Mycat管理

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

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

1	`mysql -h 192.168.200.210 -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统计

3.6.3 监控

mycat-eye

zookeeper

后端开发 > MySQL

#MySQL

16. 分库分表

http://binbo-zappy.github.io/2024/12/06/MySQL/16-分库分表/

作者

Binbo

发布于

2024年12月6日

许可协议

17. 读写分离上一篇

15. 主从复制下一篇