mysql mycat单库分表_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

#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的时候