一、 水平分表
1、选择水平分表
- 订单表orders表已经有600万数据超过MySQL单表数据瓶颈，需要进行水平分表
- 鉴于订单表特性，无论是按照id、时间进行分表均不合适，应该保证每个人的订单在同一张表里。所以应该按照客户id（customer_id）进行分表，具体做法：按照客户id取模平均分配到两张表里。
2、配置文件修改
- 修改配置文件schema.xml
<?xml version=”1.0″?>
<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>
<mycat:schema xmlns:mycat=” http://io.mycat/”>
<schema name=”TESTDB” checkSQLschema=”false” sqlMaxLimit=”100″ dataNode=”dn1″>
<table name=”customer” dataNode=”dn2″ ></table>
<table name=”orders” dataNode=”dn1,dn2″ rule=”mod_rule” ></table>
</schema>
<dataNode name=”dn1″ dataHost=”host1″ database=”atguigu_mc” />
<dataNode name=”dn2″ dataHost=”host2″ database=”atguigu_sm” />
<dataHost name=”host1″ maxCon=”1000″ minCon=”10″ balance=”2″
writeType=”0″ dbType=”mysql” dbDriver=”native” switchType=”1″ slaveThreshold=”100″>
<heartbeat>select user()</heartbeat>
<writeHost host=”hostm1″ url=”192.168.67.1:3306″ user=”root”
password=”123123″>
</writeHost>
</dataHost>
<dataHost name=”host2″ 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.67.1:3306″ user=”root”
password=”123123″>
</writeHost>
</dataHost>
- 修改配置文件rule.xml
<tableRule name=”mod_rule”>
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
……
<function name=”mod-long” class=”io.mycat.route.function.PartitionByMod”>
<!– how many data nodes –>
<property name=”count”>2</property>
</function>
Java培训Mycat水平分表
3、重启Mycat
先在另一个库里创建订单表，之后重启Mycat，让新配置生效
Java培训Mycat水平分表
4、 验证
- 连接Mycat向订单表插入数据
注：表名后必须加上相关字段，告知Mycat，哪个字段是customer_id
INSERT INTO orders(id,order_type,customer_id,amount) values(1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
- 执行成功后，分别去两个数据库查看，两个客户的订单已分到两个表里。
