MySQL-分库分表

目录

介绍

问题分析

拆分策略

垂直拆分

垂直分库

垂直分表

水平拆分

水平分库

水平分表

实现技术

MyCat

介绍

目录

结构

入门

配置

schema.xml

schema标签

datanode标签

datahost标签

rule.xml

server.xml

system标签

user标签

分片

垂直拆分

水平拆分

分片规则

范围分片auto-sharding-long

取模分片mod-long

一致性hash分片sharding-by-murmur

枚举分片

应用指定算法sharding-by-substring

固定分片hash算法

字符串hash解析算法

按天分片算法

自然月分片

MyCat管理与监控

MyCat原理

MyCat管理

MyCat-eye

---

介绍

问题分析

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

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

为了解决上述问题，需要对数据库进行分库分表处理

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

拆分策略

分库分表的形式主要是两种：垂直拆分和水平拆分，拆分的粒度又分为分库和分表：

垂直拆分

垂直分库

以表为依据，根据业务将不同表拆分到不同库中

特点：

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

垂直分表

以字段为依据，根据字段属性将不同字段拆分到不同表中

特点：

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

水平拆分

水平分库

以行（记录）为依据，按照一定策略，将一个库的数据拆分到多个库中

特点：

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

水平分表

以行（记录）为依据，按照一定策略，将一个表的数据拆分到多个表中

特点：

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

实现技术

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

MyCat

介绍

MyCat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件，可以像使用MySQL一样来使用MyCat

优势：

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

目录

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

结构

在MyCat的整体结构中，分为：逻辑结构和物理结构

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

入门

tb_order表中数据量很大，对其进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上

准备三台服务器，并分别创建数据库db01：

• 192.168.200.210：MyCat中间件服务器，同时也是第一个分片服务器
• 192.168.200.213：第二个分片服务器
• 192.168.200.214：第三个分片服务器

修改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>

修改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>

启动：

先启动3台分片服务器，然后启动MyCat服务器，切换到MyCat的安装目录，启动MyCat

#启动
bin/mycat start
#停止
bin/mycat stop

MyCat启动后，占用端口号8066

连接MyCat：

MyCat在底层模拟了MySQL的协议

mysql -h 192.168.200.210 -P 8066 -uroot -p123456

在MyCat中创建表，并插入数据：

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');

在插入数据时：

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

数据落在哪一个分片服务器是由逻辑表配置时的一个参数rule决定的，这个参数配置的就是分片规则

配置

schema.xml

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

主要包含三组标签：

• schema标签
• datanode标签
• datahost标签

schema标签

1.schema定义逻辑库

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

核心属性：

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

2.schema中的table定义逻辑表

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

核心属性：

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

datanode标签

核心属性：

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

datahost标签

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

核心属性：

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

rule.xml

rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化

主要包含两类标签：tableRule、Function

server.xml

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

system标签

主要配置MyCat中的系统配置信息

属性	取值	含义
charset	utf8	设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致
nonePasswordLogin	0,1	0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户
useHandshakeV10	0,1	使用该选项主要的目的是为了能够兼容高版本的jdbc驱动, 是否采用HandshakeV10Packet来与client进行通信, 1:是, 0:否
useSqlStat	0,1	开启SQL实时统计, 1 为开启 , 0 为关闭 ; 开启之后, MyCat会自动统计SQL语句的执行情况 ; mysql -h 127.0.0.1 -P 9066 -u root -p 查看MyCat执行的SQL, 执行效率比较低的SQL , SQL的整体执行情况、读写比例等 ; show @@sql ; show @@sql.slow ; show @@sql.sum ;
useGlobleTableCheck	0,1	是否开启全局表的一致性检测。1为开启 ，0为关闭 。
sqlExecuteTimeout	1000	SQL语句执行的超时时间 , 单位为 s ;
sequnceHandlerType	0,1,2	用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试
sequnceHandlerPattern	正则表达式	必须带有MYCATSEQ或者 mycatseq进入序列匹配流程 注意MYCATSEQ_有空格的情况
subqueryRelationshipCheck	true,false	子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false
useCompression	0,1	开启mysql压缩协议 , 0 : 关闭, 1 : 开启
fakeMySQLVersion	5.5,5.6	设置模拟的MySQL版本号
defaultSqlParser		由于MyCat的最初版本使用了FoundationDB的SQL解析器, 在MyCat1.3后增加了Druid解析器, 所以要设置defaultSqlParser属性来指定默认的解析器; 解析器有两个 : druidparser 和 fdbparser, 在MyCat1.4之后,默认是druidparser, fdbparser已经废除了
processors	1,2....	指定系统可用的线程数量, 默认值为CPU核心 x 每个核心运行线程数量; processors 会影响processorBufferPool, processorBufferLocalPercent, processorExecutor属性, 所有, 在性能调优时, 可以适当地修改processors值
processorBufferChunk		指定每次分配Socket Direct Buffer默认值为4096字节, 也会影响BufferPool长度, 如果一次性获取字节过多而导致buffer不够用, 则会出现警告, 可以调大该值
processorExecutor		指定NIOProcessor上共享 businessExecutor固定线程池的大小; MyCat把异步任务交给 businessExecutor线程池中, 在新版本的MyCat中这个连接池使用频次不高, 可以适当地把该值调小
packetHeaderSize		指定MySQL协议中的报文头长度, 默认4个字节
maxPacketSize		指定MySQL协议可以携带的数据最大大小, 默认值为16M
idleTimeout	30	指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟
txIsolation	1,2,3,4	初始化前端连接的事务隔离级别,默认为 REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3; SERIALIZABLE=4;
sqlExecuteTimeout	300	执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒;
serverPort	8066	定义MyCat的使用端口, 默认8066
managerPort	9066	定义MyCat的管理端口, 默认9066

user标签

配置MyCat中的用户、访问密码以及用户针对逻辑库、逻辑表的权限信息

分片

垂直拆分

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

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

准备三台服务器，并创建数据库shopping：

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>

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>

上传测试SQL脚本到服务器的/root目录：

执行指令导入测试数据， 重新启动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_city c , tb_areas_provinces p ,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，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作

修改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

删除原来每一个数据库服务器中的所有表结构

通过source指令导入表及数据

检查每一个数据库服务器中的表及数据分布，三个节点中都有这三张全局表

再次执行SQL语句

当在MyCat中更新全局表时，所有分片中的数据都改变，每个节点的全局表数据时刻保持一致

水平拆分

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

每台服务器上创建数据库itcast

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" />

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>

重启MyCat，创建表，插入数据：

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);

分片规则

范围分片auto-sharding-long

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

schema.xml逻辑表配置：

<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

schema.xml数据节点配置：

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

rule.xml分片规则配置：

<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>
    <property name="defaultNode">0</property>
</function>

分片规则配置属性含义：

在rule.xml中配置分片规则时，关联了一个映射配置文件autopartition-long.txt：

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

含义：0-500万之间的值，存储在0号数据节点(数据节点的索引从0开始) ；500万-1000万之间的数据存储在1号数据节点 ；1000万-1500万的数据节点存储在2号节点 

该分片规则主要针对于数字类型的字段使用

取模分片mod-long

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

schema.xml逻辑表配置：

<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="mod-long">
    <rule>
        <columns>id</columns>
        <algorithm>mod-long</algorithm>    
    </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">    
    <property name="count">3</property>
</function>

分片规则配置属性含义：

 该分片规则主要针对于数字类型的字段使用

一致性hash分片sharding-by-murmur

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

schema.xml逻辑表配置：

<!-- 一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-murmur">
    <rule>
        <columns>id</columns>
        <algorithm>murmur</algorithm>    
    </rule>
</tableRule>

<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
    <property name="seed">0</property><!-- 默认是0 -->
    <property name="count">3</property>
    <property name="virtualBucketTimes">160</property>
</function>

分片规则配置属性含义：

重启，创建表插入数据：

create table tb_order(
    id  varchar(100) not null primary key,    money   int null,
    content varchar(200) null
);

INSERT INTO tb_order (id, money, content) VALUES ('b92fdaaf-6fc4-11ec-b831-482ae33c4a2d', 10, 'b92fdaf8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93482b6-6fc4-11ec-b831-482ae33c4a2d', 20, 'b93482d5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b937e246-6fc4-11ec-b831-482ae33c4a2d', 50, 'b937e25d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93be2dd-6fc4-11ec-b831-482ae33c4a2d', 100, 'b93be2f9-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b93f2d68-6fc4-11ec-b831-482ae33c4a2d', 130, 'b93f2d7d-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9451b98-6fc4-11ec-b831-482ae33c4a2d', 30, 'b9451bcc-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9488ec1-6fc4-11ec-b831-482ae33c4a2d', 560, 'b9488edb-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94be6e6-6fc4-11ec-b831-482ae33c4a2d', 10, 'b94be6ff-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b94ee10d-6fc4-11ec-b831-482ae33c4a2d', 123, 'b94ee12c-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b952492a-6fc4-11ec-b831-482ae33c4a2d', 145, 'b9524945-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95553ac-6fc4-11ec-b831-482ae33c4a2d', 543, 'b95553c8-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9581cdd-6fc4-11ec-b831-482ae33c4a2d', 17, 'b9581cfa-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95afc0f-6fc4-11ec-b831-482ae33c4a2d', 18, 'b95afc2a-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b95daa99-6fc4-11ec-b831-482ae33c4a2d', 134, 'b95daab2-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b9667e3c-6fc4-11ec-b831-482ae33c4a2d', 156, 'b9667e60-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96ab489-6fc4-11ec-b831-482ae33c4a2d', 175, 'b96ab4a5-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b96e2942-6fc4-11ec-b831-482ae33c4a2d', 180, 'b96e295b-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b97092ec-6fc4-11ec-b831-482ae33c4a2d', 123, 'b9709306-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b973727a-6fc4-11ec-b831-482ae33c4a2d', 230, 'b9737293-6fc4-11ec-b831-482ae33c4a2d');
INSERT INTO tb_order (id, money, content) VALUES ('b978840f-6fc4-11ec-b831-482ae33c4a2d', 560, 'b978843c-6fc4-11ec-b831-482ae33c4a2d');

枚举分片

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

schema.xml逻辑表配置：

<!-- 枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus"/>

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-intfile">
   <rule>
       <columns>sharding_id</columns>
       <algorithm>hash-int</algorithm>
   </rule>
</tableRule>


<!-- 自己增加 tableRule -->
<tableRule name="sharding-by-intfile-enumstatus">
   <rule>
       <columns>status</columns>
       <algorithm>hash-int</algorithm>
   </rule>
</tableRule>


<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
     <property name="defaultNode">2</property> //默认节点为第三个
     <property name="mapFile">partition-hash-int.txt</property>
</function>

partition-hash-int.txt：状态为1对应第一个节点（节点下标从0开始），为2对应第二个节点，为3对应第三个节点

1=0
2=1
3=2

分片规则配置属性含义：

重启，创建表插入数据：

CREATE TABLE tb_user (
 id bigint(20) NOT NULL COMMENT 'ID',
 username varchar(200) DEFAULT NULL COMMENT '姓名',
 status int(2) DEFAULT '1' COMMENT '1: 未启用, 2: 已启用, 3: 已关闭',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_user (id,username ,status) values(1,'Tom',1);
insert into tb_user (id,username ,status) values(2,'Cat',2);
insert into tb_user (id,username ,status) values(3,'Rose',3);
insert into tb_user (id,username ,status) values(4,'Coco',2);
insert into tb_user (id,username ,status) values(5,'Lily',1);
insert into tb_user (id,username ,status) values(6,'Tom',1);
insert into tb_user (id,username ,status) values(7,'Cat',2);
insert into tb_user (id,username ,status) values(8,'Rose',3);
insert into tb_user (id,username ,status) values(9,'Coco',2);
insert into tb_user (id,username ,status) values(10,'Lily',1);

应用指定算法sharding-by-substring

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

 schema.xml逻辑表配置：

!-- 应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-substring">
   <rule>
       <columns>id</columns>
       <algorithm>sharding-by-substring</algorithm>
   </rule>
</tableRule>

<function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
     <property name="startIndex">0</property> <!-- zero-based -->
     <property name="size">2</property> //截取长度
     <property name="partitionCount">3</property>
     <property name="defaultPartition">0</property>
</function>

分片规则配置属性含义：

示例说明id=05-100000002 , 在此配置中代表根据id中从startIndex=0开始，截取siz=2位数字即05，05就是获取的分区，如果没找到对应的分片则默认分配到defaultPartition

重启，创建表插入数据：

CREATE TABLE tb_app (
 id varchar(10) NOT NULL COMMENT 'ID',
 name varchar(200) DEFAULT NULL COMMENT '名称',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_app (id,name) values('0000001','Testx00001');
insert into tb_app (id,name) values('0100001','Test100001');
insert into tb_app (id,name) values('0100002','Test200001');
insert into tb_app (id,name) values('0200001','Test300001');
insert into tb_app (id,name) values('0200002','TesT400001');

固定分片hash算法

该算法类似于十进制的求模运算，但是二进制的操作，例如，取id的二进制后10位与1111111111进行位&运算，位&运算最小值为0000000000，最大值为1111111111，转换为十进制，也就是位于0-1023之间

特点：

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

 schema.xml逻辑表配置：

<!-- 固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-long-hash">
   <rule>
       <columns>id</columns>
       <algorithm>sharding-by-long-hash</algorithm>
   </rule>
</tableRule>

<!-- 分片总长度为1024，count与length数组长度必须一致； -->
<function name="sharding-by-long-hash" class="io.mycat.route.function.PartitionByLong">
     <property name="partitionCount">2,1</property>
     <property name="partitionLength">256,512</property>
</function>

约束：

1. 分片长度：默认最大1024
2. count、length的长度必须一致

以上分为三个分区：0-255,256-511,512-1023

分片规则配置属性含义：

 示例说明：

重启，创建表插入数据：

CREATE TABLE tb_longhash (
 id int(11) NOT NULL COMMENT 'ID',
 name varchar(200) DEFAULT NULL COMMENT '名称',
 firstChar char(1) COMMENT '首字母',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_longhash (id,name,firstChar) values(1,'七匹狼','Q');
insert into tb_longhash (id,name,firstChar) values(2,'八匹狼','B');
insert into tb_longhash (id,name,firstChar) values(3,'九匹狼','J');
insert into tb_longhash (id,name,firstChar) values(4,'十匹狼','S');
insert into tb_longhash (id,name,firstChar) values(5,'六匹狼','L');
insert into tb_longhash (id,name,firstChar) values(6,'五匹狼','W');
insert into tb_longhash (id,name,firstChar) values(7,'四匹狼','S');
insert into tb_longhash (id,name,firstChar) values(8,'三匹狼','S');
insert into tb_longhash (id,name,firstChar) values(9,'两匹狼','L');

字符串hash解析算法

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

 schema.xml逻辑表配置：

<!-- 字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-stringhash">
   <rule>
       <columns>name</columns>
       <algorithm>sharding-by-stringhash</algorithm>
   </rule>
</tableRule>

<function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
     <property name="partitionLength">512</property> <!-- zero-based -->
     <property name="partitionCount">2</property>
     <property name="hashSlice">0:2</property>
</function>

分片规则配置属性含义：

示例说明：

重启，创建表插入数据：

create table tb_strhash(
 name varchar(20) primary key,
 content varchar(100)
)engine=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID());
INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID());
INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID());
INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID());
INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());

按天分片算法

按照日期及对应的时间周期来分片

 schema.xml逻辑表配置：

<!-- 按天分片 -->
<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-date">
   <rule>
       <columns>create_time</columns>
       <algorithm>sharding-by-date</algorithm>
   </rule>
</tableRule>

<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
     <property name="dateFormat">yyyy-MM-dd</property>
     <property name="sBeginDate">2022-01-01</property>
     <property name="sEndDate">2022-01-30</property>
     <property name="sPartionDay">10</property>
</function>
<!--
 从开始时间开始，每10天为一个分片，到达结束时间之后，会重复开始分片插入
 配置表的 dataNode 的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31 ，每
10天一个分片，一共需要37个分片。
 -->

分片规则配置属性含义：

重启，创建表插入数据：

create table tb_datepart(
 id bigint not null comment 'ID' primary key,
 name varchar(100) null comment '姓名',
 create_time date null
);

insert into tb_datepart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_datepart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_datepart(id,name ,create_time) values(3,'Rose','2022-01-11');
insert into tb_datepart(id,name ,create_time) values(4,'Coco','2022-01-20');
insert into tb_datepart(id,name ,create_time) values(5,'Rose2','2022-01-21');
insert into tb_datepart(id,name ,create_time) values(6,'Coco2','2022-01-30');
insert into tb_datepart(id,name ,create_time) values(7,'Coco3','2022-01-31');

自然月分片

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

 schema.xml逻辑表配置：

<!-- 按自然月分片 -->
<table name="tb_monthpart" dataNode="dn4,dn5,dn6" rule="sharding-by-month" />

schema.xml数据节点配置：

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

rule.xml分片规则配置：

<tableRule name="sharding-by-month">
   <rule>
       <columns>create_time</columns>
       <algorithm>partbymonth</algorithm>
   </rule>
</tableRule>

<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
     <property name="dateFormat">yyyy-MM-dd</property>
     <property name="sBeginDate">2022-01-01</property>
     <property name="sEndDate">2022-01-30</property>
</function>
<!-- 
 从开始时间开始，一个月为一个分片，到达结束时间之后，会重复开始分片插入
 配置表的 dataNode 的分片，必须和分片规则数量一致，例如 2022-01-01 到 2022-12-31 ，一
共需要12个分片。
-->

分片规则配置属性含义：

重启，创建表插入数据：

create table tb_monthpart(
 id bigint not null comment 'ID' primary key,
 name varchar(100) null comment '姓名',
 create_time date null
);

insert into tb_monthpart(id,name ,create_time) values(1,'Tom','2022-01-01');
insert into tb_monthpart(id,name ,create_time) values(2,'Cat','2022-01-10');
insert into tb_monthpart(id,name ,create_time) values(3,'Rose','2022-01-31');
insert into tb_monthpart(id,name ,create_time) values(4,'Coco','2022-02-20');
insert into tb_monthpart(id,name ,create_time) values(5,'Rose2','2022-02-25');
insert into tb_monthpart(id,name ,create_time) values(6,'Coco2','2022-03-10');
insert into tb_monthpart(id,name ,create_time) values(7,'Coco3','2022-03-31');
insert into tb_monthpart(id,name ,create_time) values(8,'Coco4','2022-04-10');
insert into tb_monthpart(id,name ,create_time) values(9,'Coco5','2022-04-30');

MyCat管理与监控

MyCat原理

在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到哪几个（或哪一个）节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端

MyCat官方提供了一个管理监控平台MyCat-Web（MyCat-eye）， MyCat-Web是MyCat可视化运维的管理和监控平台，弥补了MyCat在监控上的空白，帮MyCat分担统计任务和配置管理任务，MyCat-Web还引入了Zookeeper作为配置中心，可以管理多个节点。MyCat-Web主要管理和监控MyCat的流量、连接、活动线程和内存等，具备IP白名单、邮件告警等模块，还可以统计SQL并分析慢SQL和高频SQL等，为优化SQL提供依据

MyCat管理

MyCat默认开通两个端口，可以在server.xml中修改：

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

连接MyCat的管理控制台：

mysql -h 192.168.200.210 -p 9066 -uroot -p123456

MyCat-eye

MyCat-eye（MyCat-web）是对mycat-server提供监控服务，功能不局限于对mycat-server使用，它通过JDBC连接对MyCat、MySQL监控，监控远程服务器（目前仅限于Linux系统）的cpu、内存、网络、磁盘

MyCat-eye运行过程中需要依赖zookeeper

安装MyCat-eye和zookeeper

访问：

配置，开启MyCat的实时统计功能（server.xml）：

<property name="useSqlStat">1</property> <!-- 1为开启实时统计、0为关闭 -->

在MyCat监控界面配置服务地址：

通过MyCat执行一系列的增删改查的测试，过一段时间后打开，查看MyCat-eye监控到的数据信息

性能监控：

物理节点：

SQL统计：

SQL表分析：

SQL监控：

高频SQL：