mycat水平分表 ER表 全局表

• 准备与说明

场景

有4张表，客户表（customer ）、订单表（orders ）、订单详情表（orders_detail）、订单状态字典表 （dict_order_type ），将订单表数据保存到两个数据库中

拆分方案及定义

1. mycat逻辑库：TESTDB103

2. mysql物理库：两个节点，库名均为orders103

   1. 订单表数据分片保存到两个节点

   2. 订单详情表数据保存到与之关联的订单表数据相同的节点（订单详情表有字段order_id，关联orders表的id，ER表）

   3. 订单字典表全量数据分别保存的两张表中（全局表）

   4. 客户表保存到其中一个节点即可（可以参考mycat垂直分库：https://blog.csdn.net/shuair/article/details/118772636）

准备：提前搭建好mycat，可以参考文章：https://blog.csdn.net/shuair/article/details/118546292

• 修改server.xml

添加逻辑库TESTDB103

<user name="mycat" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">TESTDB,TESTDB101,TESTDB102,TESTDB103</property>
    <property name="defaultSchema">TESTDB</property>
</user>

• 修改schema.xml

配置schema、dataNode、dataHost节点

警告：所有的schema写在一起、所有的dataNode写在一起、所有的dataHost写在一起

• ER表

orders表和orders_detail表关联紧密，可以设置成ER表关系

orders表数据保存到两个节点，根据E-R关系的数据分片策略，配置orders_detail为orders子表，并配置好关联关系，可以解决关联查询（join）问题

• 全局表

dict_order_type表设置为全局表（type="global"），解决关联查询问题

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 
        <!-- 省略其它schema -->
        <schema name="TESTDB103" checkSQLschema="false" sqlMaxLimit="100">
            	<!-- orders-mod-rule为分片规则，在rule.xml文件中配置 -->
            	<table name="orders" dataNode="dn10301,dn10302" rule="orders-mod-rule">
                        <!-- orders_detail表的order_id字段与orders表的id字段关联，orders表分片字段也要是id，否则插入orders_detail数据会异常（不知原因，待解决） -->
			<childTable name="orders_detail" joinKey="order_id" parentKey="id"/>
		</table>
		<table name="dict_order_type" dataNode="dn10301,dn10302" type="global"/>
                <table name="customer" dataNode="dn10302"/>
        </schema>
 
        <!-- 省略其它dataNode -->
        <dataNode name="dn10301" dataHost="host2" database="orders103" />
        <dataNode name="dn10302" dataHost="host3" database="orders103" />
 
        <!-- 省略其它dataHost -->
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://ip:3306" user="root" password="密码"></writeHost>
        </dataHost>
        <dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://ip:3306" user="root" password="密码"></writeHost>
        </dataHost>
</mycat:schema>

• 修改rule.xml

配置orders表的分片规则，新增如下配置

警告：所有的tableRule写在一起、所有的function写在一起

<!-- 省略其它tableRule -->
<!-- 配置一个分表规则 -->
<tableRule name="orders-mod-rule">
	<rule>
        <!-- 分片字段 -->
    	<columns>id</columns>
        <!-- 分片算法 -->
		<algorithm>orders-mod-long</algorithm>
    </rule>
</tableRule>

<!-- 省略其它function -->
<!-- 定义分片算法，后面有写好的算法mod-long，复制一份，调整数据库节点数即可 -->
<function name="orders-mod-long" class="io.mycat.route.function.PartitionByMod">
    <property name="count">2</property>
</function>

重启mycat

• 创建数据库

在两个节点的mysql分别创建数据库orders103

create database orders103;

• 创建数据表

直接在mysql数据库创建表

节点1：创建3张表，订单表、订单详情表、订单状态字典表

DROP TABLE IF EXISTS orders;
CREATE TABLE orders ( id INT auto_increment, order_type INT, customer_id INT, amount DECIMAL ( 10, 2 ), PRIMARY KEY ( id ) );
 
DROP TABLE IF EXISTS orders_detail;
CREATE TABLE orders_detail ( id INT auto_increment, detail VARCHAR ( 2000 ), order_id INT, PRIMARY KEY ( id ) );
 
DROP TABLE IF EXISTS dict_order_type;
CREATE TABLE dict_order_type ( id INT auto_increment, order_type VARCHAR ( 200 ), PRIMARY KEY ( id ) );

节点2：创建4张表，上面3张表+客户表

DROP TABLE IF EXISTS customer;
CREATE TABLE customer ( id INT auto_increment, `name` VARCHAR ( 200 ), PRIMARY KEY ( id ) );

• 添加测试数据

在mycat窗口添加测试数据

INSERT INTO `customer` ( `id`, `name` ) VALUES ( 1, 'zhang3' );
INSERT INTO `customer` ( `id`, `name` ) VALUES ( 2, 'li4' );
INSERT INTO `customer` ( `id`, `name` ) VALUES ( 3, 'wang5' );
INSERT INTO `customer` ( `id`, `name` ) VALUES ( 4, 'zhao6' );
INSERT INTO `customer` ( `id`, `name` ) VALUES ( 5, 'zhangsan' );
INSERT INTO `customer` ( `id`, `name` ) VALUES ( 6, 'lisi' );
 
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 1, 'common' );
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 2, 'common2' );
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 3, 'common3' );
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 4, 'common4' );
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 5, 'common5' );
INSERT INTO `dict_order_type` ( `id`, `order_type` ) VALUES ( 6, 'common6' );
 
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 1, 1, 1, 10.89 );
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 2, 2, 2, 20.89 );
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 3, 3, 3, 30.89 );
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 4, 4, 4, 40.89 );
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 5, 5, 5, 50.89 );
INSERT INTO `orders` ( `id`, `order_type`, `customer_id`, `amount` ) VALUES ( 6, 6, 6, 60.89 );
 
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 1, 'one order', 1 );
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 2, 'one order2', 2 );
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 3, 'one order3', 3 );
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 4, 'one order4', 4 );
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 5, 'one order5', 5 );
INSERT INTO `orders_detail` ( `id`, `detail`, `order_id` ) VALUES ( 6, 'one order6', 6 );

此时查看数据库，会发现：customer表数据都在节点2，dict_order_type分别在2个节点有全量数据，orders表和orders_detail表数据会分别分散到2个节点上，并且有关联关系的在同一个节点上。

此时mycat水平分表就配置完成了