Hi！我是小小，一个双鱼座的佛系程序猿，今日的blog将会写关于MyCat最后一点学习内容，读写分离，与MyCat实际应用案例和一个小小的例子。
MyCat 读写分离
MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制。数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置Mycat实现的读写分离和自动切换机制，需要mysql的主从复制机制配合。
MyCat 配置
Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM" url="192.168.25.134:3306" user="root" password="root">
<readHost host="hostS" url="192.168.25.166:3306" user="root" password="root" />
</writeHost>
</dataHost>
(1) 设置 balance="1"与writeType="0"
Balance参数设置：
balance=“0”, 所有读操作都发送到当前可用的writeHost上。
balance=“1”，所有读操作都随机的发送到readHost。3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置：
writeType=“0”, 所有写操作都发送到可用的writeHost上。
writeType=“1”，所有写操作都随机的发送到readHost。3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。
“readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”
(2) 设置 switchType="2" 与slaveThreshold="100"
switchType 目前有三种选择：-1：表示不自动切换1：默认值，自动切换2：基于MySQL主从同步的状态决定是否切换Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性：switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running","Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。
项目案例
案例图
schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="db_store" checkSQLschema="false" sqlMaxLimit="100">
<table name="tstore" dataNode="dn_store" primaryKey="ID" />
</schema>
<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node
joinKey ：外键
parentKey ：指向外键的主键（tuser表）
-->
<table name="tcode" primaryKey="ID" type="global"
dataNode="dn_user1,dn_user2" />
<table name="tuser" primaryKey="ID" dataNode="dn_user$1-2"
rule="mod_userId_long">
<childTable name="tuaddress" primaryKey="ID" joinKey="uid"
parentKey="id" />
</table>
</schema>
<dataNode name="dn_store" dataHost="storeHost" database="db_store" />
<dataNode name="dn_user1" dataHost="userHost1" database="db_user" />
<dataNode name="dn_user2" dataHost="userHost2" database="db_user" />
<!--Node Server1 balance=1 M/S -->
<dataHost name="storeHost" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native"
switchType="2" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.24.128:3306" user="root"
password="root">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.24.129:3306"
user="root" password="root" />
</writeHost>
</dataHost>
<!--Node Server2 -->
<dataHost name="userHost1" 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="user_host1" url="192.168.24.128:3306" user="root"
password="root" >
</writeHost>
</dataHost>
<!--Node Server3 -->
<dataHost name="userHost2" 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="user_host2" url="192.168.24.129:3306"
user="root"
password="root">
</writeHost>
</dataHost>
</mycat:schema>
rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - -
http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod_userId_long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<tableRule name="mod_userId_long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则
没法分片 -->
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点
被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，
没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索
引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的
murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 --
>
</function>
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则
没法分片 -->
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod"
class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash"
class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - -
http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!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为关闭 -->
<property name="sequnceHandlerType">2</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">123456</property>
<property name="schemas">db_store,db_user</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false"> IUSD （1：可操作0：不可操作）
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
</mycat:server>
步骤：
1、先做好主从复制 2、在主库建立数据库db_user,db_store 3、从库同步db_user,db_store 4、按照上面的配置文件配置mycat,连接mycat 5、在db_store中建表tstore、并插入数据6、在db_user中建表tcode、tuser、tuaddress并插入数据
实例
用户购物下单实践
需求
需求：把订单信息分片，商家要查询出售的订单，用户要查询自己的订单。表设计：用户、商家订单、订单明细、用户订单分片规则：“用户表”---user_id 取模，“商家订单”----seller_user_id 取模“订单明细”----“商家订单”ER 分片，“买家订单”----buyer_user_id 取模
业务表
CREATE TABLE tb_user (
login_name VARCHAR (32) comment '登录名',
user_id BIGINT comment '用户标识',
TYPE INT comment '用户类型 1 商家，2买家',
passwd VARCHAR (128) comment '密码',
primary key (user_id)
) ;
alter table tb_user comment '用户表';
CREATE TABLE tb_seller_order (
seller_user_id BIGINT comment '商家标识',
buyer_user_id BIGINT comment '用户标识',
order_id BIGINT comment '订单标识',
price BIGINT comment '价格',
STATUS INT comment '状态',
primary key (order_id)
);
alter table tb_seller_order comment '商家订单表';
CREATE TABLE tb_order_detail(
seller_user_id BIGINT comment '商家标识',
order_detail_id BIGINT comment '订单明细标识',
order_id BIGINT comment '订单标识',
goods_id BIGINT comment '标识',
goods_name VARCHAR(32) comment '商品名称',
cnt INT comment '数量',
unit_price INT comment '单价',
primary key (order_detail_id)
);
alter table tb_order_detail comment '订单明细';
CREATE TABLE tb_buyer_order (
buyer_user_id BIGINT comment '用户标识',
seller_user_id BIGINT comment '商家标识',
order_id BIGINT comment '订单标识',
price BIGINT comment '价格',
STATUS INT comment '状态',
primary key (order_id)
);
alter table tb_buyer_order comment '买家订单';
服务器分配
MySql
主 192.168.10.135
主 192.168.10.136
主 192.168.10.137
mycat：
192.168.10.137
表主键生成策略
使用mycat全局序列生成（mycat 全局序列号：http://blog.csdn.net/convict_eva/article/details/5191
添加MyCat全局序列号
USER_ID_SQUE=dn_master
ORDER_ID_SQUE=dn_master
DETAIL_ID_SQUE=dn_master
在dn_master结点执行：
insert into MYCAT_SEQUENCE(NAME,current_value,increment)
values('USER_ID_SQUE',1,100),('ORDER_ID_SQUE',1,100),('DETAIL_ID_SQUE',1,100);
重启mycat测试：
SELECT NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE;
SELECT NEXT VALUE FOR MYCATSEQ_ORDER_ID_SQUE;
SELECT NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE;
rule.xml配置
配置function 标签
找到function 标签name="mod-long" 的function 配置，修改总结点数为3（和使用的mysql 结点数一致）。
<function name="mod-long"
class="org.opencloudb.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
配置tableRule标签
注意事项：name 要全局唯一rule.xml 配置添加如下配置
<!-- user分片规则 -->
<tableRule name="tb_user_mod-long">
<rule>
<!-- 分片使用的字段 -->
<columns>user_id</columns>
<!-- 分片使用的方法，对应function 名称 -->
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!-- 卖家订单分片规则 -->
<tableRule name="seller_order_mod-long">
<rule>
<columns>seller_user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<!-- 买家订单分片规则 -->
<tableRule name="buyer_order_mod-long">
<rule>
<columns>buyer_user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
schema.xml 配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="tb_a" dataNode="dn_master" />
<!-- 全局表，使用type属性指定，多个结点要都执行建表语句。所有结点的数据一致。-->
<table name="tb_global_test"
dataNode="dn_master,dn_master2,dn_master3" type="global" />
<!-- 配置表所在的分片结点，指定主键和分片规则。指定主键是为了使用主键查询时mycat什么
缓存主键对应的dn，提高查询效率。-->
<table name="tb_user" rule="tb_user_mod-long"
primaryKey="user_id" dataNode="dn_master,dn_master2,dn_master3" />
<table name="tb_seller_order" rule="seller_order_mod-long"
primaryKey="order_id" dataNode="dn_master,dn_master2,dn_master3">
<!-- 配置ER 分片，子表的存储依赖于主表，并且物理上紧邻存放。-->
<childTable name="tb_order_detail"
primaryKey="order_detail_id" joinKey="order_id"
parentKey="order_id" />
</table>
<table name="tb_buyer_order" rule="buyer_order_mod-long"
primaryKey="order_id" dataNode="dn_master,dn_master2,dn_master3" />
</schema>
<!-- 配置数据结点 -->
<dataNode name="dn_master" dataHost="master"
database="test_db" />
<dataNode name="dn_master2" dataHost="master2"
database="test_db" />
<dataNode name="dn_master3" dataHost="master3"
database="test_db" />
<!-- ddata node host 配置 -->
<dataHost name="master" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<!-- 主从心跳语句配置 -->
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.109.128:3306"
user="root" password="Abc123!@#">
<!-- 从库 -->
<readHost host="hostS2" url="192.168.109.130:3306"
user="root" password="Abc123!@#" />
</writeHost>
</dataHost>
<dataHost name="master2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.109.131:3306"
user="root" password="Abc123!@#"></writeHost>
</dataHost>
<dataHost name="master3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.109.132:3306"
user="root" password="Abc123!@#"></writeHost>
</dataHost>
</mycat:schema>
重启mycat，使用SQLyog连接到mycat，并执行建表语句。
测试
插入user
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-4',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-5',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-6',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-7',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-8',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-9',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-10',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-11',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-12',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-13',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-14',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-15',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-16',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-17',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-18',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-19',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-20',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-21',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-22',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('name-23',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
查看插入的数据是否按照id取模分片。
下单测试
上面创建的是商家用户，下面创建买家用户。
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`)
VALUES ('buyer-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
所有的用户如下：mysql> select * from tb_user order by login_name; +------------+---------+------+----------+ | login_name | user_id | TYPE | passwd | +------------+---------+------+----------+ | buyer-1 |
248 | 2 | passwd-A | | buyer-2 | 249 | 2 | passwd-A | | buyer-3 | 250 | 2 | passwdA | | name-1 | 225 | 1 | passwd-A | | name-10 | 234 | 1 | passwd-A | | name-11 | 235 | 1 | passwd-A | | name-12 | 236 | 1 | passwd-A | | name-13 | 237 | 1 |passwd-A | | name-14 | 238 | 1 | passwd-A | | name-15 | 239 | 1 | passwd-A | |name-16 | 240 | 1 | passwd-A | | name-17 | 241 | 1 | passwd-A | | name-18 |
242 | 1 | passwd-A | | name-19 | 243 | 1 | passwd-A | | name-2 | 226 | 1 |passwd-A | | name-20 | 244 | 1 | passwd-A | | name-21 | 245 | 1 | passwd-A | |name-22 | 246 | 1 | passwd-A | | name-23 | 247 | 1 | passwd-A | | name-3 |
227 | 1 | passwd-A | | name-4 | 228 | 1 | passwd-A | | name-5 | 229 | 1 |passwd-A | | name-6 | 230 | 1 | passwd-A | | name-7 | 231 | 1 | passwd-A | |name-8 | 232 | 1 | passwd-A | | name-9 | 233 | 1 | passwd-A | +------------+---------+------+----------+
下单
SELECT NEXT VALUE FOR MYCATSEQ_ORDER_ID_SQUE;
INSERT INTO
`tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`)
VALUES (225,248,201,1222);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un
it_price`)
VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name',1,1220);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un
it_price`)
VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name2',1,2);
说明：商家225 在109.128 上，tb_seller_order表根据seller_user_id 取模分片，所有此订单数据存储在与user id为225 的商家同一分片 tb_order_detail 表使用的是与tb_seller_order ER 分片，使用order_id 关联，所以tb_order_detail 存储的分片与相同的order_id 的tb_seller_order 的数据在同一分片。再测试一条数据：user_id为238 的用户存储在109.131 分片上。
INSERT INTO
`tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`)
VALUES (238,248,203,1222);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un
it_price`)
VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name',1,1220);
INSERT INTO `tb_order_detail`
(`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un
it_price`)
VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name2',1,2);
测试结果：109.128数据为：mysql> select * from tb_user; +------------+---------+------+----------+ |login_name | user_id | TYPE | passwd | +------------+---------+------+----------+ | name-1 | 225 |
1 | passwd-A | | name-4 | 228 | 1 | passwd-A | | name-7 | 231 | 1 | passwd-A | |name-10 | 234 | 1 | passwd-A | | name-13 | 237 | 1 | passwd-A | | name-16 |
240 | 1 | passwd-A | | name-19 | 243 | 1 | passwd-A | | name-22 | 246 | 1 |passwd-A | | buyer-2 | 249 | 2 | passwd-A | +------------+---------+------+----------+ 9 rows in set(0.00 sec)mysql> select * from tb_seller_order; +----------------+---------------+----------+-------+--------+ |seller_user_id | buyer_user_id | order_id | price | STATUS | +----------------+---------------+----------+-------+--------+ | 225 | 248 | 201 | 1222 | NULL | +----------------+---------------+----------+-------+--------+ 1 row in set (0.00 sec)mysql> select * from tb_order_detail; +----------------+-----------------+----------+----------+-------------+------+------------+ | seller_user_id | order_detail_id | order_id | goods_id | goods_name | cnt | unit_price| +----------------+-----------------+----------+----------+-------------+------+------------+ | 225 | 201 |
201 | 11 | goods_name | 1 | 1220 | | 225 | 202 | 201 | 11 |goods_name2 | 1 | 2 | +----------------+-----------------+----------+----------+-------------+------+------------
2 rows in set (0.00 sec)109.131数据为：mysql> select * from tb_user; +------------+---------+------+----------+ | login_name |user_id | TYPE | passwd | +------------+---------+------+----------+ | name-2 | 226 | 1 | passwd-A| | name-5 | 229 | 1 | passwd-A | | name-8 | 232 | 1 | passwd-A | | name-11 |
235 | 1 | passwd-A | | name-14 | 238 | 1 | passwd-A | | name-17 | 241 | 1 |passwd-A | | name-20 | 244 | 1 | passwd-A | | name-23 | 247 | 1 | passwd-A | |buyer-3 | 250 | 2 | passwd-A | +------------+---------+------+----------+ 9 rows in set (0.00 sec)mysql> select * from tb_seller_order; +----------------+---------------+----------+-------+--------+ |seller_user_id | buyer_user_id | order_id | price | STATUS | +----------------+---------------+----------+-------+--------+ | 238 | 248 | 203 | 1222 | NULL | +----------------+---------------+----------+-------+--------+ 1 row in set (0.00 sec)mysql> select * from tb_order_detail; +----------------+-----------------+----------+----------+-------------+------+------------+ | seller_user_id | order_detail_id | order_id | goods_id | goods_name | cnt | unit_price| +----------------+-----------------+----------+----------+-------------+------+------------+ | 238 | 203 |
203 | 11 | goods_name | 1 | 1220 | | 238 | 204 | 203 | 11 |goods_name2 | 1 | 2 | +----------------+-----------------+----------+----------+-------------+------+------------2 rows in set (0.00 sec)
关于作者
我是小小，双鱼座的佛系程序猿，来自于二线城市，生活在一线城市，我是小小，我们下期再见。
小明菜市场
推荐阅读
● 教程 | MySql都会了，确定不学习一下MyCat分片？
给我个好看再走好吗？
1495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
