MySQL运维篇(三)分库分表

发布时间:2024年01月17日

一、介绍

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
在这里插入图片描述

文章来源:https://blog.csdn.net/yirenyuan/article/details/135617646
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。