#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
#vi /usr/local/mycat/conf/rule.xml
id
mod-long
3
在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的时候