视频学习地址：17-尚硅谷-垂直分库_哔哩哔哩_bilibili
笔记参考地址：MySQL 分库分表 | xustudyxu's Blog (frxcat.fun)
一、MyCat 配置
schema.xml文件
schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。
主要包含以下三组标签：
schema标签datanode标签datahost标签
schema 定义逻辑库
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"><table name="tb_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"></table></schema>
schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
核心属性：
name：指定自定义的逻辑库库名checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
2. schema 中的table定义逻辑表
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100"><table name="tb_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"></table></schema>
table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义
核心属性：
name：定义逻辑表表名，在该逻辑库下唯一dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔rule：分片规则的名字，分片规则名字是在rule.xml中定义的primaryKey：逻辑表对应真实表的主键type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global
3. datanode标签
<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：定义分片所属数据库
4. datahost标签
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="master" url="jdbc:mysql://192.168.247.100:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="Hzf1997=Hzf"></writeHost><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></dataHost><dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="master" url="jdbc:mysql://192.168.247.101:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"password="Hzf1997=Hzf"></writeHost><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></dataHost><dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="master" url="jdbc:mysql://192.168.247.102:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"password="Hzf1997=Hzf"></writeHost><!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> --></dataHost>
该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性：
name：唯一标识，供上层标签使用maxCon/minCon：最大连接数/最小连接数balance：负载均衡策略，取值 0,1,2,3writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）dbDriver：数据库驱动，支持 native、jdbc
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>
注意： rule中的 algorithm 定义的名称，即为 funciton中name 的名称，意义对应。而 function中的 property引用，则是autopartition-long.txt文件
# range start-end ,data node index# K=1000,M=10000.0-500M=0500M-1000M=11000M-1500M=2
server.xml
server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
system标签
<system><property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户--><property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误--><property name="useHandshakeV10">1</property><property name="removeGraveAccent">1</property><property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --><property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --><property name="sqlExecuteTimeout">300</property> <!-- SQL 执行超时 单位:秒--><property name="sequenceHandlerType">1</property><!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property><property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</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 | type 2 NettyBufferPool --><property name="processorBufferPoolType">0</property><!--默认是65535 64K 用于sql解析时最大文本长度 --><!--<property name="maxStringLiteralLength">65535</property>--><!--<property name="sequenceHandlerType">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> --><property name="handleDistributedTransactions">0</property><!--off heap for merge/order/group/limit1开启 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><!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行--><property name="parallExecute">0</property></system>
主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，如下：
2. user标签
配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下：
在测试权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性中配置逻辑表的权限。
MyCat 分片
垂直拆分
场景
在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。
现在考虑将其进行垂直分库操作，将商品相关的表拆分到一个数据库服务器，订单表拆分的一个数据库服务器，用户及省市区表拆分到一个服务器。最终结构如下：
schema.xml配置
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"><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.247.100:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456"/></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.247.101:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456"/></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.247.102:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456"/></dataHost></mycat:schema>
本质就是：为不同的数据库表分配到不同的数据库中。
service.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>
上面配置完后，查询还是会出问题的。因为MyCat在执行该SQL语句时，需要往具体的数据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成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"/>
水平拆分
场景
在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。
配置
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" />
mod-long：取模，id%(节点总数),结果为0落在第一个节点。结果为1落在第二个节点。结果为2落在第三个节点。
tb_log表最终落在3个节点中，分别是 dn4、dn5、dn6 ，而具体的数据分别存储在 dhost1、dhost2、dhost3的itcast数据库中。
2. server.xml
配置root用户既可以访问 SHOPPING 逻辑库，又可以访问ITCAST逻辑库。
<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，然后在mycat的命令行中，执行如下SQL创建表、并插入数据，查看数据分布情况。执行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','-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','-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','-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','-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','-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','-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);
mysql> use ITCASTReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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;Query OK, 0 rows affected (0.08 sec)OK!
注意：这里MyCat创建的表、字段都是大写的，但是linux中，Mysql8默认区分大小写，并且在初始化的时候配置的，不能通过配置文件修改，所以，这里我手动修改了数据库表名为小写！
mysql> use itcastReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> mysql> 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','-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1);Query OK, 1 row affected (0.08 sec)OK!mysql> mysql> 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','-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1);thod, cost_time，source)VALUES('4','user','update','success','java.lang.String','10001','-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, Query OK, 1 row affected (0.02 sec)OK!mysql> 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','-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2);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','-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCQuery OK, 1 row affected (0.01 sec)OK!mysql> 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','-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3);Query OK, 1 row affected (0.03 sec)OK!mysql> 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','-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);Query OK, 1 row affected (0.01 sec)OK!
结果：
