MySQL分库分布表¹

1 介绍

1.1. 问题分析

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

1.2. 拆分策略

维度 \ 力度分库分表
垂直拆分垂直分库垂直分表
水平拆分水平分库水平分表

维度 \ 力度	分库	分表
垂直拆分	垂直分库	垂直分表
水平拆分	水平分库	水平分表

垂直拆分
1. 垂直分库：
  以表为依据，根据业务将不同表拆分到不同库中。
  - 特点：
    1. 每个库的表结构都不一样。
    2. 每个库的数据也不一样。
    3. 所有库的并集是全数据量。
2. 垂直分表：
  以字段为依据，根据字段属性将不同字段拆分到不同表中。
  - 特点：
    1. 每个表的结构都不一样。
    2. 每个表的数据也不一样，一般通过一列（主键/外键）关联。
    3. 所有表的并集是全量数据。
水平拆分
1. 水平分库：
  以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
  - 特点：
    1. 每个库的表结构都一样。
    2. 每个库的数据都不一样。
    3. 所有库的并集是全量数据。
2. 水平分表：
  以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
  - 特点：
    1. 每个表的表结构都一样。
    2. 每个表的数据都不一样。
    3. 所有表的并集是全量数据。

1.3. 实现技术

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

2 Mycat概述

2.1. 介绍

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

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

2.2. 下载和安装

下载
官网
下载地址
安装
mycat是采用java语言开发的开源的数据库中间件，支持windows和 Linux运行环境。
1. 准备条件
  - MySQL
  - jdk
  - mycat
  - 服务器安装软件说明
    192.168.88.1 jdk、mycat mycat中间件服务器
    192.168.88.2 mysql 分片服务器
    192.168.88.3 mysql 分片服务器
    192.168.88.4 mysql 分片服务器
2. 安装mycat
  1 2 cd /opt tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz
目录结构
1 cd /opt/mycat
bin：存放可执行文件，用于启停mycat
catlet：多表联查涉及
conf：存放mycat配置文件
lib：存放mycat的项目依赖包（jar）
logs：存放mycat的日志文件

服务器	安装软件	说明
192.168.88.1	jdk、mycat	mycat中间件服务器
192.168.88.2	mysql	分片服务器
192.168.88.3	mysql	分片服务器
192.168.88.4	mysql	分片服务器

2.4. Mycat核心概念介绍

3 Mycat入门

3.1. 需求

由于tb_order 表中的数据量很大，磁盘IO及容量都到达了瓶颈，闲需要对 tb_order 表进行数据分片，分为三个数据节点，每个节点位于不同的服务器上。

3.2. 环境准备

在三台MySQL服务器上均创建名为db01的database。

分片配置schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- schema：逻辑库 -->
    <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
        <!-- table：逻辑表 dataNode：分片节点 rule：分片规则 -->
        <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/>
    </schema>
    <!-- dataNode：分片节点 dataHost：节点主机 database：数据库的database-->
    <dataNode name="dn1" dataHost="dhost1" database="db01"/>
    <dataNode name="dn2" dataHost="dhost2" database="db01"/>
    <dataNode name="dn3" dataHost="dhost3" database="db01"/>
    <!-- dataHost：节点主机 dbDriver：驱动（native、jdbc）-->
    <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：节点名称 url：连接地址 user：用户名 password：密码-->
        <writeHost host="master"
                   url="jdbc:mysql://127.0.0.1:3311?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncodingutf8"
                   user="root"
                   password="wk77585211314"/>
    </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://127.0.0.1:3312?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncodingutf8"
                   user="root"
                   password="wk77585211314"/>
    </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://127.0.0.1:3313?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncodingutf8"
                   user="root"
                   password="wk77585211314"/>
    </dataHost>
</mycat:schema>

分片配置server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	……
    <user name="root" defaultAccount="true">
        <property name="password">wk77585211314</property>
        <property name="schemas">DB01</property>
    </user>
   
    <user name="user">
        <property name="password">wk77585211314</property>
        <property name="schemas">DB01</property>
        <property name="readOnly">true</property>
    </user>
   
</mycat:server>

3.3. 启动Mycat

命令

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

Mycat默认端口号 8066

查看日志

  
tail -f logs/wrapper.log 
   
## ------------------------
INFO   | jvm 2    | 2023/02/18 19:52:35 | MyCAT Server startup successfully. see logs in logs/mycat.log

分片测试

  
mysql -uroot -p123456 -h 192.168.88.1 -P 8066

由于xml配置文件配置了数据库及表，所以登录后会看到相应的表和数据库

在mycat中创建表，并往表中插入数据

# 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 (5000001,'goods5000001');

id = 1,2,3 分布于192.168.1.2（MySQL sever 1）； id = 5000001 分布于192.168.1.3（MySQL sever 2），出现这样的原因是配置了不同的分片规则

分片规则分析

schema.xml的rule

  
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
    <!-- table：逻辑表 dataNode：分片节点 rule：分片规则 -->
    <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/>
</schema>

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

autopartition-long.txt

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

4 Mycat配置

4.1. `schema.xml`

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

主要包含以下三组标签：

schema标签：用于定义MyCat实例中的逻辑库，一个MyCat实例中，可以有多个逻辑库，可以通过schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database 概念，需要操作某个逻辑库下的表时，也需要切换逻辑库（use xxx）。
1 2 3 4 5 <schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"> <table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/> </schema> <schema ……> </schema>
核心属性：
- name：指定自定义的逻辑库库名。
- checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除。
  fase：必须 use 数据库名; ，执行后，才能操作增删改查。
  true：可直接用 数据库名.表名 来操作增删改查。
- sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录。
  SQL语句未指定limit ，最大查出多少数据
- table标签：定义了MyCat中逻辑库schema下的逻辑表，所有需要拆分的表都需要在 table 标签中定义。
  核心属性：
  1. name：定义逻辑表表名，在逻辑库下唯一。
  2. dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中的name对应；多个dataNode逗号分隔。
  3. rule：分片规则的名字，分片规则名字是在rule.xml中定义的。
  4. primaryKey：逻辑表对应真实表的主键。
  5. type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global。
datanode标签：定义了MyCat中的数据节点，也就是我们通常说的数据分。一个dataNode标签就是一个独立的数据分片。
1 2 3 <dataNode name="dn1" dataHost="dhost1" database="db01"/> <dataNode name="dn2" dataHost="dhost2" database="db01"/> <dataNode name="dn3" dataHost="dhost3" database="db01"/>
核心属性：
- name：定义数据节点名称。
- dataHost：数据库实例主机名称，引用自dataHost标签中name属性。
- database：定义分片所属的数据库。

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

  
<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://127.0.0.1:3312?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncodingutf8" user="root" password="123456"/>
</dataHost>

核心属性：

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

4.2. `rule.xml`

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

  
<!-- tableRule：分片规则 name：名称 -->
<tableRule name="rule1">
	<rule>
   		<!-- columns：字段 -->
		<columns>id</columns>
   		<!-- algorithm：对应function的name -->        
		<algorithm>func1</algorithm>
	</rule>
</tableRule>
<tableRule name="xxx">……
<!-- function：方法 name：名称 class：对应java类 -->    
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <!-- <property name="seed">0</property> -->
    <!-- property：对应属性/属性的外部文件 -->
	<property name="mapFile">autopartition-long.txt</property>
</function>
<function >……

4.3. `server.xml`

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

system标签：对应系统配置项及其含义。

  
<system>
	<property name="nonePasswordLogin">0</property>
	<!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="useHandshakeV10">1</property>
	<!-- 握手协议版本 -->
	<property name="useSqlStat">0</property>
	<!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>
	<!-- 1为开启全加班一致性检测、0为关闭 -->
	<property name="sqlExecuteTimeout">300</property>
	<!-- SQL 执行超时 单位:秒-->
	<property name="sequnceHandlerType">2</property>
	<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>-->
	<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
	<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
	<property name="subqueryRelationshipCheck">false</property>
	<!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<!--  <property name="useCompression">1</property>-->
	<!--1为开启mysql压缩协议-->
	<!--  <property name="fakeMySQLVersion">5.6.20</property>-->
	<!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 	<property name="processors">1</property> 	<property name="processorExecutor">32</property> 	 -->
	<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
	<property name="processorBufferPoolType">0</property>
	<!--默认是65535 64K 用于sql解析时最大文本长度 -->
	<!--<property name="maxStringLiteralLength">65535</property>-->
	<!--<property name="sequnceHandlerType">0</property>-->
	<!--<property name="backSocketNoDelay">1</property>-->
	<!--<property name="frontSocketNoDelay">1</property>-->
	<!--<property name="processorExecutor">16</property>-->
	<!--
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
			<property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
	<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
	<property name="handleDistributedTransactions">0</property>
	<!--			off heap for merge/order/group/limit      1开启   0关闭		-->
	<property name="useOffHeapForMerge">0</property>
	<!--			单位为m		-->
	<property name="memoryPageSize">64k</property>
	<!--			单位为k		-->
	<property name="spillsFileBufferSize">1k</property>
	<property name="useStreamOutput">0</property>
	<!--			单位为m		-->
	<property name="systemReserveMemorySize">384m</property>
	<!--是否采用zookeeper协调切换  -->
	<property name="useZKSwitch">false</property>
	<!-- XA Recovery Log日志路径 -->
	<!--<property name="XARecoveryLogBaseDir">./</property>-->
	<!-- XA Recovery Log日志名称 -->
	<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
	<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
	<property name="strictTxIsolation">false</property>
	<property name="useZKSwitch">true</property>
</system>

user 标签:

5 Mycat分片

5.1. 垂直拆分

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

测试

在MyCat 命令行中，通过 source 命令导入表结构，以及对应的数据，查看数据分布情况。

source shopping-table.sql
source 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;

`ERROR 1064 (HY000): invalid route in sql, multi tables found but datanode has no intersection sql`

全局表配置

对应省、市、区/县表，是属于数据字典表，在多个业务模块中都有可能会遇到，可以将其设置为全局表，利于业务操作。
全局表在执行DML语句时，所有数据库节点都会操作，保持数据一致。

配置：

  
<!-- dataNode="dn11,dn12,dn13" type="global" -->
<schema>
    <table name="tb_areas_provinces" dataNode="dn11,dn12,dn13" type="global" primaryKey="id"/>
<table name="tb_areas_city" dataNode="dn11,dn12,dn13" type="global" primaryKey="id"/>
    <table name="tb_areas_region" dataNode="dn11,dn12,dn13" type="global" primaryKey="id"/>
</schema>

5.2. 水平拆分

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

配置

schema.xml

  
<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
        <table name="tb_log" dataNode="dn111,dn112,dn113" primaryKey="id" rule="mod-long" />
</schema>
   
<dataNode name="dn111" dataHost="dhost1" database="itcast"/>
<dataNode name="dn112" dataHost="dhost2" database="itcast"/>
<dataNode name="dn113" dataHost="dhost3" database="itcast"/>

server.xml

  
<user name="root" defaultAccount="true">
		<property name="password">wk77585211314</property>
		<property name="schemas">DB01,SHOPPING,ITCAST</property>
</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;

5.3. MyCat 分片

分片规则-范围
根据指定字段及其配置的范围与数据节点的对应情况，来决定该属于哪个分片。
配置：
分片规则 - 取模
根据指定的字段与节点数量来进行求模运算，根据运算结果，来决定该数据属于哪一个分片。
配置：
分片规则 - 一致性hash
相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。
配置：
分片规则 - 枚举分片
通过在配置文件中配置可能的枚举值，指定数据分布到不同的数据节点上，本规则适用按照省份、性别、状态拆分数据等业务。
配置：
分片规则 - 应用指定
运行阶段由应用自主决定路由到哪个分片，直接根据字符子串（必须是数字）计算分片号。
配置：
分片规则 - 固定分片hash算法(数字)
该算法类似于十进制的求模运算，但是为二进制的操作，例如，取id的二进制低10位与 1111111111进行位 & 运算。
特点：
- 如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
- 可以均匀分配，也可以非均匀分配。
- 分片字段必须为数字类型。
配置：
分片规则-字符串hash解析
截取字符串中的指定位置的子字符串，进行hash算法，算出分片。
配置：
分片规则-按（天）日期分片
配置：
分片规则 - 自然月
使用场景为按月份来分片，每个自然月为一个分片。
配置：

6 Mycat管理及监控

6.1. MyCat原理

原理
- insert 时根据status字段路由到匹配的数据库节点。
- select 未指定路由的字段的值时，则所有数据库节点都会执行SQL，并将结果集返回给MyCat进行合并等处理。
- select 指定路由字段的值时，会根据路由规则进行路由到指定的数据库节点执行SQL，并将结果集返回给MyCat进行合并等处理。

6.2. MyCat管理

命令行管理

MyCat默认开通两个端口 8066 和 9066 ，可以在server.xml中修改。

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

## 登陆MyCat 管理控制
mysql -uroot -p123456 -P9066 -h 192.168.88.1

管理命令：

命令	含义
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统计

MyCat-eye（MyCat-web）
- 介绍
  MyCat-web（MyCat-eye）是对MyCat-server提供监控服务，功能不局限于MyCat-server使用。它通过JDBC连接对MyCat、MySQL监控，监控远程服务器（目前仅限于Linux系统）的CPU、内存、网络、磁盘。
  MyCat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
- 安装
  - Zookeeper安装
  - MyCat-web安装
- 访问
  1 GE http://192.168.88.1:8082/mycat
- MyCat-web配置
  - 开启MyCat的实时统计功能（server.xml）
    1 <property name="useSqlStat">1</property> 
  - 在MyCat界面配置服务
- MyCat-web功能

参考黑马程序员 ↩

MySQL分库分布表

MySQL分库分布表1