MySQL运维篇之分库分表_学习

MySQL运维篇之分库分表

创始人

2024-05-29 18:09:02

03、分库分表

3.1、介绍

3.1.1、问题分析

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

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

3.1.2、分库分表

3.1.2.1、简介

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

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

2、拆分策略

在这里插入图片描述

3.1.2.2、垂直拆分

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

在这里插入图片描述

特点：

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

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

在这里插入图片描述

特点：

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

3.1.2.3、水平拆分

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

在这里插入图片描述

特点：

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

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

在这里插入图片描述

特点：

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

3.1.2.4、实现技术

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

3.2、Mycat概述

1、介绍

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

2、优势：

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

3、下载

下载地址：http://dl.mycat.org.cn/

可以选择下载版本1.6.7.3或者1.6.7.4

4、安装

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

MySQL
JDK
MyCat

服务器	安装软件	说明
192.168.200.210	JDK、MyCat	MyCat中间件服务器
192.168.200.210	MySQL	分片服务器
192.168.200.213	MySQL	分片服务器
192.168.200.214	MySQL	分片服务器

5、目录结构

在这里插入图片描述

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

6、概念介绍

在这里插入图片描述

3.3、Mycat入门

1、需求

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

在这里插入图片描述

每个表的结构相同，数据不同，为水平拆分。

2、环境准备

① 确保三台服务器的防火墙关闭

# 查看防火墙状态
systemctl status firewalld# 关闭防火墙
systemctl stop firewalld     #关闭防火墙
systemctl disable firewalld  #停止防火墙开机自启

② 在三台服务器上创建数据库db01

③ 分片配置（schema.xml）



select user()select user()select user()

 
④ 分片配置（server.xml） 
配置mycat的用户及用户的权限信息： 
123456DB01
123456DB01true

 
3、启动服务 
切换到MyCat的安装目录，执行如下指令，启动MyCat： 
# 启动
bin/mycat start# 停止
bin/mycat stop
 
Mycat启动之后，占用端口号8066。 
4、分片测试 
通过如下指令，就可以连接并登录MyCat。 
 mysql -h 192.168.88.135 -P 8066 -uroot -p
 
然后就可以在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');
 
 
 经过测试，我们发现，在往 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标签 
 
3.4.1.1、schema标签 
1、schema标签用于定义MyCat实例中的逻辑库，一个Mycat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的逻辑库。 
MyCat中的逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库(use xxx)。 
2、核心属性：
 name ：指定自定义的逻辑库库名（区分大小写）
 
 checkSQLschema ：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
 
  若为false，必须需要先切换到指定数据库下，才可以进行查询其中的表。
 
 
 sqlMaxLimit ：如果未指定limit进行查询，列表查询模式查询多少条记录
 
3、子标签：table
table标签定义了MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在table标签中定义。
一个schema下可以有多个table。
核心属性：
name ：定义逻辑表表名，在该逻辑库下唯一
dataNode ：定义逻辑表所属的数据节点，该属性需要与dataNode 标签中name对应；多个dataNode 逗号分隔。
rule ：分片规则的名字，分片规则名字是在rule.xml中定义的
primaryKey ：逻辑表对应真实表的主键
type ：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global
3.4.1.2、dataNode标签
1、dataNode 标签中定义了MyCat中的数据节点，也就是我们通常说的数据分片。一个dataNode 标签就是一个独立的数据分片。



2、核心属性：
name ：定义数据节点名称，需要与schema标签中的子标签table的dataNode对应
dataHost ：数据库实例主机名称，引用自dataHost 标签中name 属性
database ：定义分片所属数据库，也就是真实数据库
3.4.1.3、dataHost标签
1、dataHost标签在MyCat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分类、心跳语句。
select user()

2、核心属性：
name ：唯一标识，供上层标签使用
maxCon/minCon ：最大连接数/最小连接数
balance ：负载均衡策略，取值0,1,2,3
writeType ：写操作分布方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
dbDriver ：数据库驱动，支持native、jdbc
3.4.2、rule.xml
rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function。
 注意：
 只有涉及到分表的时候，才会使用分片规则。 
3.4.3、server.xml
server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
1、system标签
对应的系统配置项及其含义
 0 10  

2、user标签
 若未配置逻辑表的权限，则按照逻辑数据库的DML权限；若配置逻辑表的权限，则按照逻辑表的DML权限。 
3.5、Mycat切片
3.5.1、垂直分库
1、场景
在业务系统中，涉及以下表结构，但是由于用户与订单每天都会产生大量的数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分，原有的数据库表如下。
2、准备三台服务器；分别在三台MySQL中创建数据库shopping。
3、配置
① schema.xml




 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
select user()select user()select user()
② server.xml
123456SHOPPING
123456SHOPPINGtrue

4、测试
在mycat的命令行中，通过source指令导入表结构，以及对应的数据，查看数据分布情况。
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在第三个切片中。
 解决方案：全局表配置
 对于省、市、区/县表 tb_areas_provinces、tb_areas_city、tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作。
 
 
 
 
 
 
 
 
 
 
 
 
 


设置完scheme.xml文件之后，需要先停止mycat服务，再启动mycat服务；并且重新通过source指令导入表结构，以及对应的数据；最后再重新执行上述SQL语句。
此外，当通过mycat更新tb_areas_provinces表中的数据时，会同时更新三个切片中的tb_areas_provinces表中的数据。
3.5.2、水平分表
1、场景
在业务系统中，有一张表（日志表），业务系统每天会产生大量的日志数据，单台服务器的数据存储及处理能力是有限的，可以对数据库表进行拆分。
2、准备三台服务器；分别在三台MySQL中创建数据库itcast。
3、配置
① schema.xml

   
 
 
 
 
 
 
 
 



② server.xml
123456SHOPPING,ITCAST

4、测试
在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.5.3、Mycat分片规则
3.6、Mycat管理及监控
3.6.1、Mycat原理
1、对insert语句进行分析
Mycat Server先解析SQL语句——insert into tb_user(id,name,status) values(1,'it01',1)；
进行分片分析，根据插入数据的status字段和分片规则进行分片;
将SQL语句路由到分片1；当前不涉及读写分离分析；
在分片1中执行当前的SQL语句。
2、对select语句进行分析
Mycat Server先解析SQL语句——select * from tb_user；
进行分片分析，判断查询条件，由于没有status字段的查询条件，则需要路由到三个分片；
在三个分片中执行SQL语句，并将查询的结果返回给Mycat；
在Mycat对结果进行合并，由于不涉及聚合、排序等，则不需要进行后续处理；
将最终结果返回给客户端。
Mycat Server先解析SQL语句——select * from tb_user；
进行分片分析，判断查询条件，根据查询条件status in(1,3)确定分片1和3，且需要路由到这两个分片；
在分片1和3中执行SQL语句，并将查询的结果返回给Mycat；
在Mycat对结果进行合并，根据order by id对结果进行排序；
将最终结果返回给客户端。
3.6.2、Mycat管理工具
1、Mycat默认开通2个端口，可以在server.xml中进行修改。
8066数据访问端口，即进行DML和DDL操作。
9066数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。
连接登录mycat的9066端口
mysql -h192.168.88.135 -P9066 -uroot -p
2、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。
 1  
 
 
3.6.2、Mycat监控 
1、介绍 
Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器（目前仅限于Linux系统）的CPU、内存、网络、磁盘。 
Mycat-eye运行过程中需要依赖ZooKeeper，因此需要先安装ZooKeeper。 
2、安装 
 ZooKeeper安装
 A. 上传安装包 zookeeper-3.4.6.tar.gz
 B. 解压
 tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/
 C. 创建数据存放目录
 cd /usr/local/zookeeper-3.4.6/
mkdir data
 D. 修改配置文件名称并配置
 cd config
mv zoo_sample.cfg zoo.cfg
 E. 在zoo.cfg中配置数据存放目录
 dataDir=/usr/local/zookeeper-3.4.6/data
 F.启动Zookeeper
 bin/zkServer.sh start
bin/zkServer.sh status
 
 MyCat-web安装
 A. 上传安装包 Mycat-web.tar.gz 
 B. 解压
 tar -zxvf Mycat-web.tar.gz -C /usr/local/
 C. 目录介绍
 etc ----> jetty配置文件
 lib ----> 依赖jar包
 mycat-web ----> mycat-web项目
 readme.txt
 start.jar ----> 启动jar
 start.sh ----> linux启动脚本
 D. 启动
 sh start.sh
 E.查看端口号8082
 netstat -ntpl |grep 8082
  备注:
 如果Zookeeper与Mycat-web不在同一台服务器上 , 需要设置Zookeeper的地址 ;
 在/usr/local/mycat-web/mycat-web/WEB-INF/classes/mycat.properties文件中配置 :
 
 
  
3、访问 
http://192.168.88.135:8082/mycat 
 
4、配置Mycat 
开启Mycat的事实统计功能（server.xml） 
1  
 
在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统计


            
            
                
               
            
            
                
                    上一篇：hutool XML反序列化漏洞(CVE-2023-24162)

                
                
                    下一篇：mybatis面试题 一                
            
            
            
                
                相关内容


    

    
    
        
        
            
            
                
                
                    
                    
                        
                        热门资讯
                        
                    
                    
                
                
            
            
            
            

                                
                
                    
                    
                    
                    
                    
                        为非遗注入时尚力量
                        转自：贵州日报 权若青凯里“绣里淘”非遗集市，草木染的清香在空气中浮动，苗语叫卖声如古老歌谣在耳畔回...
                    
                    
                
                                
                
                    
                    
                    
                    
                    
                        贵州理工学院4门课程获批国家级...
                        转自：贵州日报 本报讯（记者 王雨）记者从贵州理工学院获悉，近日教育部发布《教育部关于公布第三批国家...
                    
                    
                
                                
                
                    
                    
                    
                    
                    
                        “贵州造”给煤矿安全装上“顺风...
                        转自：贵州日报贵州省能源智能开发与高效利用实验室。 张凌 摄 贵州日报天眼新闻记者 张凌在贵州深邃复...
                    
                    
                
                                
                
                    
                    
                    
                    
                    
                        一块钢板如何变为风力发电机组的...
                        转自：贵州日报贵州保龙设备制造有限公司风电塔筒生产车间内，工人对塔筒焊接口进行打磨。 贵州日报天眼新...
                    
                    
                
                                
                
                    
                    
                    
                    
                    
                        全国政协副主席王光谦率队到我省...
                        转自：贵州日报 本报讯（记者 鲁毅）1月8日至11日，全国政协副主席、民盟中央常务副主席王光谦率队到...





    
    
        
        
            2026 ©
            浩江知识网汇飞网发门网比特空间星闪网太阳生活网能源上品网东方游戏网八零商务网太和茶叶网浩江娱乐网        
        
        
        
                        
                知识
            
                        
                常识
            
                        
                学习
            
                        
                热点
            
                        
                文学