本文最后更新于 66 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com

MySQL分库分表

一、介绍

1.1 问题分析

采用单数据库进行数据存储存在以下瓶颈：

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

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

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

1.2 拆分策略

1.2.1 垂直拆分

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

1.每个库的表结构都不一样；

2.每个库的数据不一样；

3.所有库的并集时全量数据。

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

1.每个表的结构都不一样；

2.每个表的数据也不一样，一般通过一列(主键/外键)关联；

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

1.2.2 水平拆分

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

1.每个库的表结构都一样；

2.每个库的数据都不一样；

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

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

1.每个表的表结构都一样；

2.每个表的数据都不一样；

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

1.3 实现技术

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

二、MyCat概述

2.1 介绍

MyCat是开源的、活跃的、基于Java编写的MySQL数据库中间件。

优势：

1.性能可靠稳定

2.技术团队强大

3.体系完善

4.社区活跃

2.2 安装MyCat

bin：存放可执行文件，用于启停mycat
conf：存放mycat的配置文件
lib：存放mycat项目依赖包

2.3 概念

MyCat中不直接存储数据，数据都存储在物理结构的数据库中，MyCat只存储逻辑结构

三、MyCat入门

3.1 入门需求

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

启动MyCat：

#第一步：进入mycat安装路径
cd /usr/local/mycat
#第二步：开启mycat
bin/mycat start
#第三步：连接mycat
mysql -h mycat服务器ip -P 8066(mycat默认端口) -u root -p 密码

四、MyCat配置

4.1 schema.xml

schema.xml作为MyCat中最重要的配置文件之一，包含了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。
位置：/usr/local/mycat/conf 下

主要包含以下三个标签：

1.schema标签

2.datanode标签

3.datahost标签

4.1.1 schema标签


    
schema标签用于定义MyCat示例中的逻辑库，一个MyCat实例中，可以有多个逻辑库，可以通过schema标签划分不同的逻辑库。

MyCat逻辑库概念等同于MySQL中database概念，需要操作某个逻辑库下表时，也需要切换逻辑库(use 库名)。
schema核心属性：

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

schema标签(table)：table标签定义MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在table标签中定义
table核心属性：

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

4.1.2 dataNote标签
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

dataNote标签定义了MyCat中的数据节点(数据分片)。一个dataNote标签就是一个独立的数据分片
dataNote核心属性：

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

4.1.3 dataHost标签
<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="hostS1" url="jdbc:mysql://服务器IP:3306?    useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="密码" />
</dataHost>

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

name：唯一标识，供上层标签使用
maxCon/minCon：最大/最小连接数
balance：负载均衡策略，取值0，1，2，3
writeType：写操作分发方式(0：写操作转发到第一个writeHost，第一个挂了切换到第二个；1：写操作随机分发到配置的writeHost)
dbDriver：数据库驱动，支持native、jdbc

4.2 rule.xml

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活使用分片算法，或者对同一个分片算法使用不同的参数，让分片过程可配置化，主要包含：tableRule和Function两类标签。  
<tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
</tableRule>

<function name="rang-long"class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
</function>
4.3 server.xml

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

点击查看：系统配置信息详情
<system>
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>
</system>
4.3.2 user标签
五、MyCat分片
5.1 垂直分库

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

分别在三台服务器中创建数据库shopping，配置schema.xml和server.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="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"/>      
</schema>     
<dataNode name="dn1" dataHost="dhost1" database="shopping" />
<dataNode name="dn2" dataHost="dhost2" database="shopping" />
<dataNode name="dn3" dataHost="dhost3" database="shopping" />
5.2 水平分表

场景：在业务系统中，有一张表(日志表)，业务系统每天都会产生大量日志数据，单台服务器存储能力有限，可以对数据库表进行拆分，每个数据库表结构相同，数据不同。

分别在三台服务器中创建数据库itcast，配置schema.xml和server.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" />
六、分片规则
6.1 范围分片

范围分片(auto-sharding-long)是根据指定的字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片，主键必须是数字。
6.2 取模分片

取模分片(mod-long)是根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定数据属于哪一个分片，主键必须是数字。
6.3 一致性hash算法

一致性哈希(sharding-by-murmur)指相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。
6.4 枚举分片

枚举分片(sharding-by-intfile)指通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则使用于按照省份、性别、状态拆分数据等业务。
标签中填写：**索引字段**
配置defaultNode：将不满足枚举规则的数据放置在默认数据库节点中，例如把status是4的数据放在第3个数据库节中，所以defaultNode值为2；
partition-hash-int.txt中：**等号左边是枚举值，等号右边是数据库节点索引值从0开始。**
6.5 应用指定算法

运行阶段由应用自主决定路由到哪个分片中，直接根据字符子串(必须是数字)计算分片。
6.6 固定hash算法

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

特点：

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


约束固定大小为1024；Count和length配置数量必须一致
2,1：指有3个数据库节点，前2个节点长度为256，第3个节点长度为512；
256,512：定义数据节点长度，总数必须为1024
6.7 字符串hash解析

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

指定开始时间begin、结束时间end按照设定的时间周期partionday存放数据。当日期时间超过指定结束日期后，会重新从第一个数据库节点开始按照指定时间周期存放。例如：2022-01-31-2022-02-09重新存放在节点0的数据库中。

注意：例如设置的分片节点数为3个(dn1,2,3)必须和分片规则1月1日-30日：30(天数)/10(周期)=3 数量一致
6.9 按自然月分片

按照月份分片，每个自然月为一个分片。
七、MyCat管理及监控
7.1 MyCat原理
7.2 MyCat管理工具

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


8066端口：数据访问端口，即进行DML和DDL操作。


9066端口：数据库管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。
mysql -h mycat服务器ip地址 -P 9066 -u root -p 密码


      









































































命令
含义




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统计



7.3 MyCat监控

监控工具：MyCat-eye
MyCat-eye(MyCat-web)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。它通过JDBC连接对MyCat、MySQL监控，监控远程服务器(仅限Linux系统)的cpu、内存、网络、磁盘。
MyCat-eye运行需要依赖zookeeper，需要先安装zookeeper。
zookeeper安装：
运行路径 /usr/local/zookeeper-3.4.6
运行zookeeper：bin/zkServer.sh start
查看运行状态：bin/zkServer.sh status --返回 Mode: standalone 运行成功
mycat-eye安装
etc         ----> jetty配置文件

lib         ----> 依赖jar包

mycat-web   ----> mycat-web项目

readme.txt

start.jar   ----> 启动jar

start.sh    ----> linux启动脚本
启动mycat-eye路径：/usr/local/mycat-web
启动mycat-eye：sh start.sh
访问mycat-eye：打开浏览器输入mycat-eye服务器地址：ip:8082/mycat