MySQL运维篇（三）分库分表

一、介绍

1. 问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下 性能瓶颈：
（1）IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘 IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
（2）CPU瓶颈：排序、分组、连接查询、聚合统计等 SQL 会耗费大量的 CPU 资源，请求数太多，CPU 出现瓶颈。

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

2. 拆分策略

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

3. 垂直拆分

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

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
特点：
1️⃣每个表的结构都不一样。
2️⃣每个表的数据也不一样，一般通过一列（主键/外键）关联。
3️⃣所有表的并集是全量数据。

4. 水平拆分

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

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

5. 实现技术

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

二、MyCat概述

1. 介绍

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

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

2. 下载

下载地址：http://www.mycat.org.cn/
在这里插入图片描述

3. 安装

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

4. 目录介绍

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

5. 概念介绍

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

三、MyCat 入门

1. 需求

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

2. 环境准备

准备3台服务器：
192.168.200.210：MyCat中间件服务器，同时也是第一个分片服务器。
192.168.200.213：第二个分片服务器。
192.168.200.214：第三个分片服务器。

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

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

3.2 server.xml

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

<user name="root" defaultAccount="true">
	<property name="password">123456</property>
	<property name="schemas">DB01</property>
	
	<!-- 表级 DML 权限设置 -->
	<!--
	<privileges check="true">
	<schema name="DB01" dml="0110" >
	<table name="TB_ORDER" dml="1110"></table>
	</schema>
	</privileges>
	-->
</user>

<user name="user">
	<property name="password">123456</property>
	<property name="schemas">DB01</property>
	<property name="readOnly">true</property>
</user>

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

4. 测试

4.1 启动

配置完毕后，先启动涉及到的 3 台分片服务器，然后启动 MyCat 服务器。切换到 Mycat 的安装目录，执行如下指令，启动 Mycat：

#启动
bin/mycat start

#停止
bin/mycat stop

Mycat 启动之后，占用端口号 8066。
启动完毕之后，可以查看 logs 目录下的启动日志，查看 Mycat 是否启动完成。

4.2 测试

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

mysql -h 192.168.200.210 -P 8066 -uroot -p123456

我们看到我们是通过 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(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 配置

1. schema.xml

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

主要包含以下三组标签：
1️⃣schema 标签
2️⃣datanode 标签
3️⃣datahost 标签

1.1 schema 标签

（1）schema 定义逻辑库

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

核心属性：
1️⃣name：指定自定义的逻辑库库名
2️⃣checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
3️⃣sqlMaxLimit：如果未指定 limit 进行查询，列表查询模式查询多少条记录

（2） schema 中的 table 定义逻辑表

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

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

1.2 datanode 标签

在这里插入图片描述

核心属性：
1️⃣name：定义数据节点名称
2️⃣dataHost：数据库实例主机名称，引用自 dataHost 标签中 name 属性
3️⃣database：定义分片所属数据库

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

2. rule.xml

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

3. server.xml

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

（1）system 标签

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

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

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

五、MyCat 分片

1. 垂直拆分

1.1 场景

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

现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：

1.2 准备

准备三台服务器，IP地址如图所示：
并且在 192.168.200.210，192.168.200.213, 192.168.200.214 上面创建数据库
shopping。

1.3 配置

（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.200.210: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.200.213: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.200.214:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
</dataHost>

（2） server.xml

<user name="root" defaultAccount="true">
	<property name="password">123456</property>
	<property name="schemas">SHOPPING</property>
	
	<!-- 表级 DML 权限设置 -->
	<!--
	<privileges check="true">
	<schema name="DB01" dml="0110" >
	<table name="TB_ORDER" dml="1110"></table>
	</schema>
	</privileges>
	-->
	
</user>

<user name="user">
	<property name="password">123456</property>
	<property name="schemas">SHOPPING</property>
	<property name="readOnly">true</property>
</user>

1.4 全局表

对于省、市、区/县表 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="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"/>

在这里插入图片描述

配置完毕后，重新启动 MyCat。
（1）删除原来每一个数据库服务器中的所有表结构
（2）通过 source 指令，导入表及数据

source /root/shopping-table.sql

source /root/shopping-insert.sql

（3）检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表
（4）然后再次执行上面的多表联查的 SQL 语句

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;

（5）当在 MyCat 中更新全局表的时候，我们可以看到，所有分片节点中的数据都发生了变化，每个节点的全局表数据时刻保持一致。

2. 水平拆分

2.1 场景

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

2.2 准备

准备三台服务器，IP地址如图所示：
并且，在三台数据库服务器中分表创建一个数据库itcast。

2.3 配置

（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="true">
    <schema name="DB01" dml="0110" >
    <table name="TB_ORDER" dml="1110"></table>
    </schema>
    </privileges>
    -->
</user>

3. 分片规则

3.1 范围分片

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

（2）配置

分片规则配置属性含义：

3.2 取模分片

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

（2）配置

分片规则配置属性含义：

3.3 一致性hash分片

（1）介绍
所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的拓容问题。

（2）配置

分片规则配置属性含义：

3.4 枚举分片

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

（2）配置

分片规则配置属性含义：

3.5 应用指定算法

（1）介绍
运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。

（2）配置

分片规则配置属性含义：

六、MyCat 管理及监控

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 等。为优化 SQL 提供依据。

2. MyCat 管理

Mycat 默认开通 2 个端口，可以在 server.xml 中进行修改。
（1）8066 数据访问端口，即进行 DML 和 DDL 操作。
（2）9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理 mycat 的整个集群状态。

连接MyCat的管理控制台：

mysql -h 192.168.200.210 -p 9066 -uroot -p123456

在这里插入图片描述

3. MyCat-eye

3.1 介绍

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

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

3.2 安装

（1）zookeeper 安装
（2）Mycat-web 安装

3.3 访问

http://192.168.200.210:8082/mycat

MySQL运维篇（三）分库分表

一、介绍

1. 问题分析

2. 拆分策略

3. 垂直拆分

4. 水平拆分

5. 实现技术

二、MyCat概述

1. 介绍

2. 下载

3. 安装

4. 目录介绍

5. 概念介绍

三、MyCat 入门

1. 需求

2. 环境准备

3. 配置

3.1 schema.xml

3.2 server.xml

4. 测试

4.1 启动

4.2 测试

四、MyCat 配置

1. schema.xml

1.1 schema 标签

1.2 datanode 标签

1.3 datahost 标签

2. rule.xml

3. server.xml

五、MyCat 分片

1. 垂直拆分

1.1 场景

1.2 准备

1.3 配置

1.4 全局表

2. 水平拆分

2.1 场景

2.2 准备

2.3 配置

3. 分片规则

3.1 范围分片

3.2 取模分片

3.3 一致性hash分片

3.4 枚举分片

3.5 应用指定算法

六、MyCat 管理及监控

1. MyCat 原理

2. MyCat 管理

3. MyCat-eye

3.1 介绍

3.2 安装

3.3 访问

相关文章