Mycat 基础

最新推荐文章于 2023-04-20 16:25:10 发布

钱多多_qdd

最新推荐文章于 2023-04-20 16:25:10 发布

阅读量583

点赞数

文章标签： java 数据库

本文链接：https://blog.csdn.net/moneywenxue/article/details/120735722

版权

一、什么情况下推荐分库分表

大家可能在网上看过一些数据库的规范，比如阿里巴巴 Java 开发手册，它推荐的理
论分表界线是 500 万。

【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

二、分库分表的类型和特点

分库分表的类型：

垂直切分：基于表或字段划分，表结构不同。我们有单库的分表，也有多库的分库。
水平切分：基于数据划分，表结构相同，数据不同，也有同库的水平切分和多库的切分。

2.1 垂直分表

垂直分表有两种，一种是单库的，一种是多库的。字段太多了，就要拆表，表太多
了，就要拆库。

2.1.1 单库垂直分表

单库分表，比如：商户信息表，拆分成基本信息表，联系方式表，结算信息表，附件表等等。

2.1.2 多库垂直分表

多库垂直分表就是把原来存储在一个库的不同的表，拆分到不同的数据库。

比如：消费金融核心系统数据库，有很多客户相关的表，这些客户相关的表，全部单独存放到客户的数据库里面。合同，放款，风控相关的业务表也是一样的。

当我们对原来的一张表做了分库的处理，如果某些业务系统的数据还是有一个非常快的增长速度，比如说还款数据库的还款历史表，数据量达到了几个亿，这个时候硬件限制导致的性能问题还是会出现，所以从这个角度来说垂直切分并没有从根本上解决单库单表数据量过大的问题。在这个时候，我们还需要对我们的数据做一个水平的切分。这个时候，一个应用需要多个数据库。

2.2 水平切分

水平切分就是按照数据的维度分布不同的表中，可以是单库的，也可以是多库的。

2.2.1 单库水平分表

两个案例：
银行的交易流水表，所有进出的交易都需要登记这张表，因为绝大部分时候客户都是查询当天的交易和一个月以内的交易数据，所以我们根据使用频率把这张表拆分成三张表：

当天表 channel_transaction：只存储当天的数据。
当月表 channel_transaction_month：我们在夜间运行一个定时任务，前一天的数据，全部迁移到当月表。用的是 insert into select，然后 delete。
历史表 channel_transaction_history：同样是通过定时任务，把登记时间超过30 天的数据，迁移到 history 历史表（历史表的数据非常大，我们按照月度，每个月建立分区）。

费用明细表：
消费金融公司跟线下商户合作，给客户办理了贷款以后，消费金融公司要给商户返费用，或者叫提成，每天都会产生很多的费用的数据。为了方便管理，我们每个月建立一张费用表，例如 fee_detail_202501……fee_detail_202512。
但是注意，跟分区一样，在一个数据库分表的方式虽然可以一定程度解决单表查询性能的问题，但是并不能解决单机存储瓶颈的问题，因为所有的表占用的是相同的磁盘存储空间。

2.2.2 多库水平分表

另一种是多库的水平分表。比如客户表，我们拆分到多个库存储，表结构是完全一样的。

一般我们说的分库分表都是跨库的分表。

但分库分表也会带来哪些问题呢，也就是我们说的分库分表之后带来的复杂性。

2.3 分库分表带来的问题

跨库关联查询
分布式事务
排序、翻页、函数计算
全局主键

2.3.1 跨库关联查询

比如查询在合同信息的时候要关联客户数据，由于是合同数据和客户数据是在不同
的数据库，那么我们肯定不能直接使用join 的这种方式去做关联查询。

我们有几种主要的解决方案：

字段冗余：
比如合同库的合同表只保存了客户 ID，如果要查询客户姓名，比如关联客户库的客
户表，这时候我们可以直接把客户姓名存到合同表，通过这种方式避免跨库关联查询的
问题（这个叫做反范式的设计）。
数据同步：
比如商户系统要查询产品系统的产品表，我们干脆在商户系统创建一
张产品表，通过 ETL、MQ 或者 canal 定时同步产品数据。
全局表（广播表）：有一些基础信息表，比如行名行号表、行政区划表，被很多
业务系统用到，如果我们放在核心系统，每个系统都要调接口去查询，这个时候我们可
以在所有的数据库都存储相同的基础数据，各个系统自己维护，保持同步。

上面的思路都是通过合理的业务设计避免跨库关联查询，实际上在我们的系统中，
尽量不要用跨库关联查询。如果最后无法避免跨库关联的情况，那我们就只能用最后一
种办法。

4.系统层组装
在不同的数据库节点，各自利用查询条件，把符合条件数据的数据查询出来，然后
在内存中重新组装，返回给客户端

2.3.2 分布式事务

如果是在一个数据库里面，我们可以用本地事务来控制，但是在不同的数据库里面
就不行了。这里必须要出现一个协调者的角色，让大家统一行动，而且要分成多个阶段，
一般是先确定都能成功才成功，只要有一个人不能成功，就要全部失败。

核心思想其实是在预先提交能够成功的情况下，尽量缩短同时提交的时间差，来提
升成功的概率。

2.3.3 排序、翻页、函数计算问题

跨节点多库进行查询时，会出现 limit 分页，order by 排序的问题。比如有两个节点，
节点 1 存的是奇数 id=1,3,5,7,9……；节点 2 存的是偶数 id=2,4,6,8,10……
如果查询语句是查出第一页的 10 条数据：

select * from user_info order by id limit 0,10

需要在两个节点上各取出 10 条（为什么都要查 10 条？假设 10 条都在第 2 个节点
上？），然后合并数据，重新排序，节点多的话就更麻烦了。
max、min、sum、count 之类的函数在进行计算的时候，也需要先在每个分片上执
行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终将结果返回。

2.3.4 全局主键避重问题

MySQL 的数据库里面字段有一个自增的属性，Oracle 也有 Sequence 序列。如果
是一个数据库，那么可以保证 ID 是不重复的，但是水平分表以后，每个表都按照自己的
规律自增，不同的表之间肯定会出现 ID 重复的问题
1）UUID（Universally Unique Identifier 通用唯一识别码）

UUID 标准形式包含 32 个 16 进制数字，分为 5 段，形式为 8-4-4-4-12 的 36 个字符，例如：c4e7956c-03e7-472c-8909-d733803e79a9。
UUID 是主键是最简单的方案，本地生成，性能高，没有网络耗时。但缺点也很明显，
由于 UUID 非常长，会占用大量的存储空间；另外，作为主键建立索引和基于索引进行查询时都会存在性能问题，在 InnoDB 中，UUID 的无序性会引起数据位置频繁变动，导致分页。

2）数据库
把序号维护在数据库的一张表中。这张表记录了全局主键的类型、位数、起始值，
当前值。当其他应用需要获得全局 ID 时，先 for update 锁行，取到值+1 后并且更新后
返回。并发性比较差。

3）Redis
基于 Redis 的 INT 自增的特性，使用批量的方式降低数据库的写压力，每次获取一段区间的 ID 号段，用完之后再去数据库获取，可以大大减轻数据库的压力。

4）雪花算法 Snowflake（64bit）

核心思想：
a）使用 41bit 作为毫秒数，可以使用 69 年；
b）10bit 作为机器的 ID（5bit 是数据中心，5bit 的机器 ID），支持 1024 个节点；
c）12bit 作为毫秒内的流水号（每个节点在每毫秒可以产生 4096 个 ID）；
d）最后还有一个符号位，永远是 0。

代码：snowflake.SnowFlakeTest
优点：毫秒数在高位，生成的 ID 整体上按时间趋势递增；不依赖第三方系统，稳定性和效率较高，理论上 QPS 约为 409.6w/s(1000*2^12)，并且整个分布式系统内不会产生 ID 碰撞；可根据自身业务灵活分配 bit 位。
不足就在于：强依赖机器时钟，如果时钟回拨，则可能导致生成 ID 重复。

以上是分库分库会带来的一些问题和解决方案。

当然还有一个非常关键的问题。
原来你的应用系统只需要连接到一个数据库，配置一个数据源，现在要配置多个。
配置就配置吧，问题就来了：
这个问题我们把它叫做：多数据源的问题，或者动态数据源的问题。
我们可以从查询的整个流程来分析一下，哪些环节是我们可以下手的。

2.4 多数据源/动态数据源的解决方案

在 SSM 的项目里面，查询一般要经过这些流程：
DAO——Mapper（ORM）——JDBC——代理——数据库服务

多数据源问题就不介绍了，相信读者已经在项目中使用过了。

三、mycat概念与配置

3.1 Mycat 介绍与核心概念

3.1.1 基本介绍

历史：从阿里 cobar 升级而来，由开源组织维护（兼职）。

定位：运行在应用和数据库之间，可以当做一个 MySQL 服务器使用（不论是在工具还是在代码或者命令行中都可以直接连接）。实现对 MySQL 数据库的分库分表，也可以通过 JDBC 支持其他的数据库。

Mycat 的关键特性（官网首页 http://www.mycat.org.cn）

可以当做一个 MySQL 数据库来使用
支持 MySQL 之外的数据库，通过 JDBC 实现
解决了我们提到的所有问题，多表 join、分布式事务、全局序列号、翻页排序
支持 ZK 配置，带监控 mycat-web（已经停止维护）
2.0 已经发布；文档许久没有更新

3.1.2 核心概念

概念	含义
主机	物理主机，一台服务器，一个数据库服务，一个 3306 端口
物理数据库	真实的数据库，例如 146、 150、 151 的 gpcat 数据库
物理表	真实的表，例如 146、 150、 151 的 gpcat 数据库的 order_info 表
分片	将原来单个数据库的数据切分后分散存储在不同的数据库节点
分片节点	分片以后数据存储的节点
分片键	分片依据的字段，例如 order_info 表以 id 为依据分片,id 就是分片键，通常是主键
分片算法	分片的规则，例如随机、取模、范围、哈希、枚举以及各种组合算法

逻辑表	相对于物理表，是分片表聚合后的结果，对于客户端来说跟真实的表没有区别
逻辑数据库	相对于物理数据库，是数据节点聚合后的结果，例如 imall

一个逻辑表可以映射到多个节点的一张表（分片表），也可以映射到一个节点的表
（非分片表），也可以映射到一个节点的多张表（单库分表）。

下载、解压 Mycat（有 Windows 版本，可以在本地数据库测试）
http://dl.mycat.org.cn/

linux：

wget http://dl.mycat.org.cn/1.6.7.3/20190927161129/Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
tar -xzvf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz

Mycat 解压以后有 5 个目录：

目录	作用
bin	启动目录
catlet	空目录
conf	配置目录
lib	jar 包依赖
logs	日志目录

3.2 Mycat 配置详解

主要的配置文件 server.xml、schema.xml、rule.xml 和具体的分片配置文件。

坑非常多，配置错误会导致无法启动，这个时候要看 wrapper 日志！
文件一定要注意备份，不知道什么时候就跑不起来了……

3.2.1 server.xml

包含系统配置信息。

system 标签：例如字符集、线程数、心跳、分布式事务开关等等。
user 标签：配置登录用户和权限

下面我贴一下我测试的server.xml（删除多余注释信息）：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
		<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
		<!-- 0 文件 1数据库 2 本地时间戳 3 ZK -->
		<property name="sequnceHandlerType">0</property>
		  <!--  <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-->
		<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="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">1</property>
		<!--
			单位为m
		-->
		<property name="memoryPageSize">1m</property>
		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>
		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">true</property>
	</system>
	<!-- 全局SQL防火墙设置 -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->

	<user name="root">
		<property name="password">root</property>
		<property name="schemas">imall,gupao</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

</mycat:server>

下面表示逻辑库：name为root，密码为root。逻辑库包含：imall和gupao两个库

<user name="root">
		<property name="password">root</property>
		<property name="schemas">imall,gupao</property>
	</user>

3.2.2 schema.xml

schema 在 MySQL 里面跟数据库是等价的。

schema.xml 包括逻辑库、表、分片规则、分片节点和数据源，可以定义多个 schema。

这里面有三个主要的标签（table、dataNode、dataHost）：

<table/>：

表名和库名最好都用小写
定义了逻辑表，以及逻辑表分布的节点和分片规则：

<schema name="imall" checkSQLschema="false" sqlMaxLimit="100">
    <table name="customer" primaryKey="id" dataNode="122-imall,123-imall,124-imall"
    rule="auto-sharding-long" />
    <table name="order_info" dataNode="122-imall,123-imall,124-imall" rule="mod-long-order" >
        <childTable name="order_detail" primaryKey="id" joinKey="order_id"               parentKey="order_id"/>
    </table>
</schema>

<schema name="gupao" checkSQLschema="false" sqlMaxLimit="100">
    <table name="student" primaryKey="sid" dataNode="122-gupao,123-gupao,124-gupao"
    rule="mod-long" />
</schema>

配置	作用
checkSQLsche ma	在查询 SQL 中去掉逻辑库名

sqlMaxLimit	自动加上 limit 控制数据的返回
primaryKey	指定该逻辑表对应真实表的主键。 MyCat 会缓存主键（通过 primaryKey 属性配置）与具体 dataNode 的信息。当分片规则（rule）使用非主键进行分片时，那么在使用主键进行查询时， MyCat 就会通过缓存先确定记录在哪个 dataNode 上，然后再在该 dataNode 上执行查询。如果没有缓存/缓存并没有命中的话，还是会发送语句给所有的 dataNode。
dataNode	数据分片的节点
autoIncrement	自增长（全局序列）， true 代表主键使用自增长策略
type	全局表： global。其他：不配置

<dataNode/>：数据节点与物理数据库的对应关系：

<dataNode name="122-imall" dataHost="host122" database="imall" />
<dataNode name="123-imall" dataHost="host123" database="imall" />
<dataNode name="124-imall" dataHost="host124" database="imall" />

<dataNode name="122-gupao" dataHost="host122" database="gupao" />
<dataNode name="123-gupao" dataHost="host123" database="gupao" />
<dataNode name="124-gupao" dataHost="host124" database="gupao" />

<dataHost/>：

配置物理主机的信息，readhost 是从属于 writehost 的。

<dataHost name="host122" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.44.122:3306" user="root" password="123456">
</writeHost>
</dataHost>

<dataHost name="host123" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.44.123:3306" user="root" password="123456">
    <!-- <readHost host="hostS1"></readHost> -->
    </writeHost>
</dataHost>

<dataHost name="host124" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.44.124:3306" user="root" password="123456">
    </writeHost>
</dataHost

balance——负载的配置，决定 select 语句的负载：

值	作用
0	不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
1	所有读操作都随机发送到当前的 writeHost 对应的 readHost 和备用的 writeHost
2	所有的读操作都随机发送到所有的 writeHost,readHost 上
3	所有的读操作都只发送到 writeHost 的 readHost 上

writeType——读写分离的配置，决定 update、delete、insert 语句的负载：

值	作用
0	所有写操作都发送到可用的 writeHost 上（默认第一个，第一个挂了以后发到第二个）
1	所有写操作都随机的发送到 writeHost

switchType：主从切换配置

值	作用
-1	表示不自动切换
1	默认值，表示自动切换
2	基于 MySQL 主从同步的状态决定是否切换,心跳语句为 show slave status
3	基于 MySQL galera cluster 的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。

贴一下我的schema.xml 测试配置：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="imall" checkSQLschema="false" sqlMaxLimit="100">
		<table name="customer" primaryKey="id" dataNode="122-imall,123-imall,124-imall" rule="auto-sharding-long" />
		<table name="order_info" dataNode="122-imall,123-imall,124-imall" rule="mod-long-order" >
    			<childTable name="order_detail" primaryKey="id" joinKey="order_id" parentKey="order_id"/>
		</table>
		<table name="mycat_sequence" dataNode="122-imall" autoIncrement="true" primaryKey="id"></table>
	</schema>
	
	<schema name="gupao" checkSQLschema="false" sqlMaxLimit="100">
		<table name="student" primaryKey="sid" dataNode="122-gupao,123-gupao,124-gupao" rule="mod-long" />
		<table name="noshard" primaryKey="id" autoIncrement="true" dataNode="122-gupao" />
		<table name="dict" primaryKey="id" type="global" dataNode="122-gupao,123-gupao,124-gupao" />
		<table name="fee" primaryKey="id" subTables="fee2025$1-3" dataNode="122-gupao" rule="sharding-by-month" />
	</schema>

	<dataNode name="122-imall" dataHost="host122" database="imall_1" />
	<dataNode name="123-imall" dataHost="host123" database="imall_2" />
	<dataNode name="124-imall" dataHost="host124" database="imall_3" />

	<dataNode name="122-gupao" dataHost="host122" database="gupao_1" />
	<dataNode name="123-gupao" dataHost="host123" database="gupao_2" />
	<dataNode name="124-gupao" dataHost="host124" database="gupao_3" />
	
	<dataHost name="host122" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
				   password="root">
		</writeHost>
	</dataHost>

	<dataHost name="host123" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
							   password="root">
		<!-- <readHost host="hostS1"></readHost>  -->
		</writeHost>
	</dataHost>

	<dataHost name="host124" maxCon="1000" minCon="10" balance="0"
					  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
						   password="root">
		</writeHost>
	</dataHost>
</mycat:schema>

3.2.3 rule.xml

定义了分片规则和算法

分片规则：

<tableRule name="rang-long-cust">
    <rule>
        <columns>id</columns>
        <algorithm>func-rang-long-cust</algorithm>
    </rule>
</tableRule>

分片算法：

<function name="func-rang-long-cust" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">rang-long-cust.txt</property>
</function>

分片配置：rang-long-cust.txt

10001-20000=1
0-10000=0
20001-100000=2

以上是最重要的三个配置文件。

3.2.4 ZK 配置

略

3.2.5 启动停止

检查环境变量配置：

conf/wrapper.conf

wrapper.java.command=/usr/local/soft/java/jdk1.8.0_40/bin/java

进入 mycat/bin 目录（注意要先启动物理数据库）：
如果直接用解压包，需要对 bin 目录下所有执行文件 chmod777。

操作	命令
启动	./mycat start
停止	./mycat stop
重启	./mycat restart
查看状态	./mycat status
前台运行	./mycat console

Windows：用 startup_nowrap.bat 启动

连接：

mysql -uroot -p123456 -h 192.168.44.122 -P8066 imall

3.3 Mycat 分片验证

gupao库：

在三个gupao库（gupao_1、gupao_2、gupao_3）导入sql：

-- 在所有数据库节点上创建数据库gupao，创建两张表
-- 全局表
CREATE TABLE `dict` (
  `id` int(11) DEFAULT NULL,
  `param_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `param_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 取模分片表
CREATE TABLE `student` (
  `sid` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `qq` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在第一个gupao库（gupao_1）创建非分片表：

-- 在第一个数据库节点（122）gupao数据库创建非分片表
CREATE TABLE `noshard` (
  `id` bigint(30) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

库内分表——在第一个数据库节点gupao数据库创建单库分片表：

-- 库内分表
-- 在第一个数据库节点（122）gupao数据库创建单库分片表
CREATE TABLE `fee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `fee20251`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20252`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);
CREATE TABLE `fee20253`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) 
);

imall库：

在三个imall库（imall_1、imall_2、imall_3）导入sql：

-- 在所有数据库节点上创建数据库imall，创建3张表
-- 范围分片表
CREATE TABLE `customer` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ER分片表
CREATE TABLE `order_info` (
  `order_id` int(11) NOT NULL COMMENT '订单ID',
  `uid` int(11) DEFAULT NULL COMMENT '用户ID',
  `nums` int(11) DEFAULT NULL COMMENT '商品数量',
  `state` int(2) DEFAULT NULL COMMENT '订单状态',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ER分片表
CREATE TABLE `order_detail` (
  `order_id` int(11) NOT NULL COMMENT '订单号',
  `id` int(11) NOT NULL COMMENT '订单详情',
  `goods_id` int(11) DEFAULT NULL COMMENT '货品ID',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `is_pay` int(2) DEFAULT NULL COMMENT '支付状态',
  `is_ship` int(2) DEFAULT NULL COMMENT '是否发货',
  `status` int(2) DEFAULT NULL COMMENT '订单详情状态',
  PRIMARY KEY (`order_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

导完上述语句后，我的物理数据库如下图（仅展示了gupao_1、imall_1）：

我是在windows上测试的，我在改好配置后，启动bin/startup_nowrap.bat

修改server.xml、schema.xml、rule.xml 等配置，这些配置我在前面已经贴过了，请参考前面的再理理头绪。

启动后访问该服务器8066端口，如图：

以下的测试，如没有特殊说明，都是8066端口的逻辑库gupao和imall中执行的。

3.3.1 范围分片

特点：容易出现冷热数据

imall 库 customer 表：范围分片。

1.先看看schema.xml设计到customer表的配置：

2.再来看一下rule.xml关于auto-sharding-long的配置：

3.下面来看看autopartition-long.txt：

4.执行插入sql：

-- 测试范围分片（在mycat连接中imall数据库中执行）
INSERT INTO `customer` (`id`, `name`) VALUES (6666, '赵先生');
INSERT INTO `customer` (`id`, `name`) VALUES (7777, '钱先生');
INSERT INTO `customer` (`id`, `name`) VALUES (16666, '孙先生');
INSERT INTO `customer` (`id`, `name`) VALUES (17777, '李先生');
INSERT INTO `customer` (`id`, `name`) VALUES (26666, '周先生');
INSERT INTO `customer` (`id`, `name`) VALUES (27777, '吴先生');

5.查看入库情况：

先看8066逻辑库：

再看3个物理库：

3.3.2 取模分片表

特点：分布均匀，但是迁移工作量比较大

gupao 数据库，student 表：取模分片

1.先看看schema.xml设计到student表的配置：

2.再来看一下rule.xml关于mod-long的配置：

表示以3取模分配

3.执行插入sql：

-- 测试取模分片（在mycat连接中gupao数据库中执行）
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (1, '李大彪', '166669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (4, '菜狗子', '655556666');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (2, '等候那場雪', '466669999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (5, '猫老公', '265286999');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (3, 'tj-大白', '368828888');
INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (6, '大郎', '516895555');

4.查看入库情况：

先看8066逻辑库：

再看3个物理库：

3.3.3 取模分片（ER 表）

我们有些表的数据是存在逻辑的主外键关系的，比如订单表 order_info，存的是汇总的商品数，商品金额；订单明细表 order_detail，是每个商品的价格，个数等等。或者、叫做从属关系，父表和子表的关系。

order_info：

order_detail：

他们之间会经常有关联查询的操作，如果父表的数据和子表的数据分别存储在不同的数据库，跨库关联查询也比较麻烦。所以我们能不能把父表和数据和从属于父表的数据落到一个节点上呢？

比如 order_id=1001 的数据在 node1，它所有的明细数据也放到 node1；order_id=1002 的数据在 node2，它所有的明细数据都放到 node2，这样在关联查询的时候依然是在一个数据库。

注意，这个也是一种避免跨库关联的重要手段。

1.先看看schema.xml设计到order_info表的配置：

2.再来看一下rule.xml关于mod-long-order的配置：

数据库节点数量是3，order_id对3取模，落点在节点：0，1，2

3.执行插入sql：

imall 库，order_info：

--测试ER分片（在mycat连接中imall数据库中执行）
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (1, 1000001, 1, 2, '2025-09-23 14:35:37', '2025-09-23 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (2, 1000002, 1, 2, '2025-09-24 14:35:37', '2025-09-24 14:35:37');
INSERT INTO `order_info` (`order_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (3, 1000003, 3, 1, '2025-09-25 11:35:49', '2025-09-25 11:35:49');

imall 库，order_detail：

--测试ER分片（在mycat连接中imall数据库中执行）
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (3, 20180001, 85114752, 19.99, 1, 1, 1);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180002, 25411251, 1280.00, 1, 1, 0);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180003, 62145412, 288.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180004, 21456985, 399.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180005, 21457452, 1680.00, 1, 1, 2);
INSERT INTO `order_detail` (`order_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180006, 65214789, 9999.00, 1, 1, 3);

4.查看入库情况：

imall_1：

imall_2：

imall_3：

3.3.4 全局表

gupao 数据库，dict 表：全局表
1.先看看schema.xml设计到dict表的配置：

全局，添加一条三个节点都添加。

2.执行插入sql：

INSERT INTO `noshard` (`id`, `name`) VALUES (1, '这是一条没有分片的数据');

3.查看入库情况：

图片就不贴了，三个节点dict表，都添加了这条数据。

3.3.5 非分片表

gupao 数据库，noshard 表：

1.先看看schema.xml设计到noshard表的配置：

noshar表是只在第一个节点。

2.执行插入sql：

INSERT INTO `noshard` (`id`, `name`) VALUES (1, '这是一条没有分片的数据');

3.查看入库情况：

3.3.6 库内分表

注意，逻辑表也要创建，即使没有数据，否则会报错。
需要在 122-gupao 清空所有数据（bug，mycat 无法 truncate）

1.先看看schema.xml设计到fee表的配置：

2.再来看一下rule.xml关于mod-long的配置：

3.执行插入sql：

-- 测试语句（在mycat连接中gupao数据库中执行）
INSERT INTO `fee` (`id`, `create_time`) VALUES (1, '2025-1-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (2, '2025-2-1 14:46:19');
INSERT INTO `fee` (`id`, `create_time`) VALUES (3, '2025-3-1 14:46:19');

4.查看入库情况：

3.4 Mycat 全局 ID

Mycat 全局序列实现方式主要有 4 种：本地文件方式、数据库方式、本地时间戳算
法、ZK。也可以自定义业务序列。
注意获取的前缀都是：MYCATSEQ_

参考资料：
https://blog.51cto.com/mynode/1910570

3.4.1 文件方式

配置文件 server.xml，修改以后需要重启 mycat 服务。

sequnceHandlerType 值：

0 -文件
1- 数据库
2- 本地时间戳
3 -ZK

<property name="sequnceHandlerType">0</property>

文件方式，配置 conf/sequence_conf.properties：

CUSTOMER.HISIDS=
CUSTOMER.MINID=10000001
CUSTOMER.MAXID=20000000
CUSTOMER.CURID=10000001

语法：

select next value for MYCATSEQ_CUSTOMER;

在插入语句中使用：

INSERT INTO `noshard` (`id`, `name`) VALUES (8, next value for MYCATSEQ_CUSTOMER);

优点：本地加载，读取速度较快。
缺点：当 Mycat 重新发布后，配置文件中的 sequence 需要替换（还原）。Mycat不能做集群部署。

3.4.2 数据库方式

这种方式需要一个表和 3 个存储过程。

<property name="sequnceHandlerType">1</property>

配置： sequence_db_conf.properties
把这张表创建在第一个节点（122）上，所以是 122-imall：：

GLOBAL=122-imall
COMPANY=122-imall
CUSTOMER=122-imall
ORDERS=122-imall

在第一个数据库节点（122）imall 数据库上创建 MYCAT_SEQUENCE 表：

DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 1,
remark varchar(100),
PRIMARY KEY(name)) ENGINE=InnoDB;

注：可以在 schema.xml 配置文件中配置这张表，供外部访问。

<table name="mycat_sequence" dataNode="122-imall" autoIncrement="true" primaryKey="id"></table>

122-imall 数据库创建存储过程——获取当前 sequence 的值：

DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS
varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM
MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval ;
END
;;
DELIMITER ;

122-imall 数据库创建存储过程，获取下一个 sequence：

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS
varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;

122-imall 数据库创建存储过程，设置 sequence：

DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value
INTEGER) RETURNS varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;咕泡出品， 必属精品 www.gupaoedu.com
38
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER;

122-imall 数据库插入记录:

INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('GLOBAL', 1, 100,'');
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('ORDERS', 1, 100,'
订单表使用');

测试：

select next value for MYCATSEQ_ORDERS

上面的测试语句是网上教程自带的，执行报错：

下面这是我自己执行的，也报错：

 insert into t_user(id,name,age)values(next value for MYCATSEQ_GLOBAL,'波波烤鸭5',23)

3.4.3 本地时间戳方式

ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) ，长度为 18位

<property name="sequnceHandlerType">2</property>

配置文件 sequence_time_conf.properties:

#sequence depend on TIME
WORKID=01
DATAACENTERID=01

验证：

select next value for MYCATSEQ_GLOBAL

3.4.4 ZK 方式（不演示）

修改 conf/myid.properties

设置 loadZk=true（启动时会从 ZK 加载配置，一定要注意备份配置文件，并且先用
bin/init_zk_data.sh,把配置文件写入到 ZK）

<property name="sequnceHandlerType">3</property>

配置文件：sequence_distributed_conf.properties：

# 代表使用 zk
INSTANCEID=ZK
# 与 myid.properties 中的 CLUSTERID 设置的值相同
CLUSTERID=010

复制配置文件：

cd /usr/local/soft/mycat/conf
cp *.txt *.xml *.properties zkconf/
chown -R zkconf/

cd /usr/local/soft/mycat/bin
./init_zk_data.sh

验证：

select next value for MYCATSEQ_GLOBAL

3.4.5 使用

每次获取都要 next value 吗？可以自动自增吗？

在 schema.xml 的 table 标签上配置 autoIncrement="true"，不需要获取和指定序列的情况下，就可以使用全局 ID 了。

例如非分片表：

INSERT INTO `noshard` (`name`) VALUES ( '自动获取自增值');

四、mycat监控与日志查看

4.1 监控

4.1.1 命令行监控

连接到管理端口 9066（注意不是 8066），注意必须要带 IP

mysql -uroot -h127.0.0.1 -p123456 -P9066

全部命令：

mysql>show @@help;

命令	作用
show @@server;	查看服务器状态，包括占用内存等
show @@database;	查看数据库
show @@datanode;	查看数据节点

show @@datasource;	查看数据源
show @@connection;	该命令用于获取 Mycat 的前端连接状态，即应用与 mycat 的连接
show @@backend;	查看后端连接状态
show @@cache;	查看缓存使用情况 SQLRouteCache： sql 路由缓存。 TableID2DataNodeCache ：缓存表主键与分片对应关系。 ER_SQL2PARENTID ：缓存 ER 分片中子表与父表关系
reload @@config;	重新加载基本配置（热加载），使用这个命令时 mycat 服务不可用
show @@sysparam;	查询 mycat 服务的系统参数
show @@sql.high;	执行频率高的 SQL
show @@sql.slow;	慢 SQL 设置慢 SQL 的命令： reload @@sqlslow=5 ;
show @@syslog limit=50;	查看最近 50 条系统日志

4.1.2 命令行监控 mycatweb 监控

https://github.com/MyCATApache/Mycat-download/tree/master/mycat-web-1.0

Mycat-eye 是 mycat 提供的一个监控工具，它依赖于 ZK。
本地必须要运行一个 ZK，必须先启动 ZK。
参考： https://gper.club/articles/7e7e7f7ff7g59gc3g64

下载 mycat-web

cd /usr/local/soft
wget http://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz

启动 mycat-web

cd mycat-web
nohup ./start.sh &

停止：kill start.jar 相关的进程

访问端口 8082
http://192.168.44.122:8082/mycat/

mycat server.xml 配置：

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

重启 mycat 服务生效。

4.2 日志

log4j 的 level 配置要改成 debug

4.2.1 wrapper.log 日志

wrapper 日志：mycat 启动，停止，添加为服务等都会记录到此日志文件，如果系
统环境配置错误或缺少配置时，导致 Mycat 无法启动，可以通过查看 wrapper.log 定位
具体错误原因。

4.2.2 mycat.log 日志

mycat.log 为 mycat 主要日志文件，记录了启动时分配的相关 buffer 信息，数据源
连接信息，连接池，动态类加载信息等等。
在 conf/log4j2.xml 文件中进行相关配置，如保留个数，大小，字符集，日志文件大
小等。

钱多多_qdd

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

Mycat 基础

一、什么情况下推荐分库分表

二、分库分表的类型和特点

2.1 垂直分表

2.1.1 单库垂直分表

2.1.2 多库垂直分表

2.2 水平切分

2.2.1 单库水平分表

2.2.2 多库水平分表

2.3 分库分表带来的问题

2.3.1 跨库关联查询

2.3.2 分布式事务

2.3.3 排序、 翻页、 函数计算问题

2.3.4 全局主键避重问题

2.4 多数据源/动态数据源的解决方案

三、mycat概念与配置

3.1 Mycat 介绍与核心概念

3.1.1 基本介绍

3.1.2 核心概念

3.2 Mycat 配置详解

3.2.1 server.xml

3.2.2 schema.xml

3.2.3 rule.xml

3.2.4 ZK 配置

3.2.5 启动停止

3.3 Mycat 分片验证

3.3.1 范围分片

3.3.2 取模分片表

3.3.3 取模分片（ER 表）

3.3.4 全局表

3.3.5 非分片表

3.3.6 库内分表

3.4 Mycat 全局 ID

3.4.1 文件方式

3.4.2 数据库方式

3.4.3 本地时间戳方式

3.4.4 ZK 方式（ 不演示）

3.4.5 使用

四、mycat监控与日志查看

4.1 监控

4.1.1 命令行监控

4.1.2 命令行监控 mycatweb 监控

4.2 日志

4.2.1 wrapper.log 日志

4.2.2 mycat.log 日志

2.3.3 排序、翻页、函数计算问题

3.4.4 ZK 方式（不演示）