Mycat使用之MySQL单库分表及均分数据

#wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

#tar zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
#vi /usr/local/mycat/conf/schema.xml

<table name="orderlog" dataNode="dn1" primaryKey="id" subTables="orderlog$1-3" rule="mod-long" />

<dataNode name="dn1" dataHost="localhost1" database="db1" />

#vi /usr/local/mycat/conf/rule.xml

<tableRule name="mod-long">
<rule>
<columns>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">3</property>
</function>

在db1数据库上建立3个表
CREATE TABLE `orderlog1` (
`id` int(10) NOT NULL,
`order_id` int(10) unsigned NOT NULL,
`create_time` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orderlog2` (
`id` int(10) NOT NULL,
`order_id` int(10) unsigned NOT NULL,
`create_time` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orderlog3` (
`id` int(10) NOT NULL,
`order_id` int(10) unsigned NOT NULL,
`create_time` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#/usr/local/mycat/bin/mycat stop
#/usr/local/mycat/bin/mycat start

mysql> explain insert into orderlog(id,order_id,create_time) VALUES(1,5200,86784523);
+-----------+------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------+
| dn1 | INSERT INTO orderlog2 (id, order_id, create_time) VALUES (1, 5200, 86784523) |
+-----------+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain insert into orderlog(id,order_id,create_time) VALUES(2,5200,86784523);
+-----------+------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------+
| dn1 | INSERT INTO orderlog3 (id, order_id, create_time) VALUES (2, 5200, 86784523) |
+-----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain insert into orderlog(id,order_id,create_time) VALUES(3,5200,86784523);
+-----------+------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------+
| dn1 | INSERT INTO orderlog1 (id, order_id, create_time) VALUES (3, 5200, 86784523) |
+-----------+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

查询：

mysql> explain select * from orderlog;
+-----------+-----------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------+
| dn1 | SELECT * FROM orderlog1 LIMIT 100 |
| dn1 | SELECT * FROM orderlog2 LIMIT 100 |
| dn1 | SELECT * FROM orderlog3 LIMIT 100 |
+-----------+-----------------------------------+
3 rows in set (0.09 sec)

实现方式：切分规则根据配置中输入的数值n。此种分片规则将数据分成n份（通常dn节点也为n），从而将数据均匀的分布于各节点上。
优点：这种策略可以很好的分散数据库写的压力。比较适合于单点查询的情景
缺点：不方便扩展；出现了范围查询，就需要MyCAT去合并结果，当数据量偏高的时候，这种跨库查询+合并结果消耗的时间有可能会增加很多，尤其是还出现了order by的时候