MyCat 子表

目录

前言

概念 ：比如说用户表或者订单表，通常这些表都会有一张主体表，用户表有用户id、姓名、手机号等等，而订单表也会有订单的id,金额、状态、收货人姓名、收货人手机号、收货人地址、创建时间等等，都是和订单主体相关的。
子表 ：通常就是我们说的，order_item 表里面通常都会有购买商品的id、商品的名称、商品购买人的金额，存放类似这样的信息。
场景 ：订单表是按订单的id去进行水平切分的，订单对id进行取模，0分配到第一个数据库、1分配到第二个数据库，那么现在我要插入订单明细表，那么需要按照什么方式进行水平切分呢？

子表

一、schema.xml配置

• childTable标签，定义分片子表
• name属性，子表名称
• joinKey属性，标记子表中的列， 用于与父表做关联
• parentKey标签， 标记父表中的列， 与joinKey对应
• primaryKey属性，子表主键，同table标签
• needAddLimit属性，同table标签

schema.xml

[root@localhost bin]# cat /opt/mycat/conf/schema.xml

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

        <schema name="user" checkSQLschema="true" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <table name="user" dataNode="dn246,dn247" rule="mod-long" />
                <table name="province" dataNode="dn246,dn247" type="global"/>

                <!-- 子表 start  -->
                <table name="o_order" dataNode="dn246,dn247" rule="auto-sharding-long" >
                   <!-- 
                        表名: order_item 
                        用于子表关联的id: order_id
                        父表中的主键key: id
                    -->
                   <childTable name="order_item" joinKey="order_id" parentKey="id"/>
                </table>
		<!-- 子表 end -->

                <!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
                        /> -->
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="dn246" dataHost="db246" database="user_246" />
        <dataNode name="dn247" dataHost="db247" database="user_247" />
        <!-- <dataNode name="dn3" dataHost="localhost1" database="db3" />  -->

        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
        <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->

        <dataHost name="db246" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="M1" url="192.168.8.246:3306" user="eddie" password="Abc@123456">
                        <readHost host="S1" url="192.168.8.245:3306" user="eddie" password="Abc@123456" /> 
                </writeHost>

                <!-- <writeHost host="M2" url="192.168.8.245:3306" user="eddie" password="Abc@123456"/> -->

                <!-- <writeHost host="hostS1" url="localhost:3316" user="root"
                                   password="123456" /> -->

                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>

        <dataHost name="db247" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="M1" url="192.168.8.247:3306" user="eddie"
                                   password="Abc@123456">
                        <!-- can have multi read hosts -->
                        <!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> -->
                </writeHost>
                <!-- <writeHost host="hostS1" url="192.168.8.247:3316" user="root"
                                                        password="123456" /> -->

                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>


        <!--
                <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
                <heartbeat>             </heartbeat>
                 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>
                 </dataHost>

          <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
                <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
                <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"       password="123456" > </writeHost> </dataHost>

                <dataHost name="jdbchost" maxCon="1000"         minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
                <heartbeat>select       user()</heartbeat>
                <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

                <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
                <heartbeat> </heartbeat>
                 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"         password="jifeng"></writeHost> </dataHost> -->

        <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
                dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
                url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
                </dataHost> -->
</mycat:schema>

返回 rule.xml 确认，分片键是否 id

<tableRule name="auto-sharding-long">
        <rule>
            <!-- 分片列：按照这个列分片 -->
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
</tableRule>

进入 ip:9066 reload 配置

mysql> reload @@config_all;
Query OK, 1 row affected (0.24 sec)
Reload config success

二、创建测试表

分别在 192.168.8.246 和 192.168.8.247 运行DDL语句

CREATE TABLE `o_order` (
  `id` int NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `order_status` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `order_item` (
  `id` int NOT NULL,
  `order_id` int NOT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `num` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

MyCat:8066 插入测试数据

-- o_order 插入与查询语句
INSERT INTO `user`.`o_order`(`id`, `total_amount`, `order_status`) VALUES (1, 10, 1)
SELECT * FROM `user`.`o_order` WHERE `id` = 1 AND `total_amount` = 10 AND `order_status` = 1 LIMIT 1

-- order_item 插入与查询语句：order_id需要和o_order.id一致
INSERT INTO `user`.`order_item`(`id`, `order_id`, `product_name`, `num`) VALUES (1, 1, '测试商品', 1)
SELECT * FROM `user`.`order_item` WHERE `id` = 1 AND `order_id` = 1 AND `product_name` = '测试商品' AND `num` = 1 LIMIT 1

autopartition-long.txt 规则条件 0-500M=0，所以数据也会同步到 192.168.8.246

继续在 MyCat:8066 插入测试数据 order_id = 6000000

INSERT INTO `user`.`order_item`(`id`, `order_id`, `product_name`, `num`) VALUES (2, 6000000, '测试商品2', 2)
SELECT * FROM `user`.`order_item` WHERE `id` = 2 AND `order_id` = 6000000 AND `product_name` = '测试商品2' AND `num` = 2 LIMIT 1

autopartition-long.txt 规则条件 500M-1000M=1，所以数据也会同步到 192.168.8.247