前两天,发现数据库中有张表已经到达了百万级别,因此需要做分表的工作,网上查阅了下,最常见的做法就是:利用mysql的merge引擎做分表;使用中间件做分表。
这次主要介绍使用中间件Mycat来做单库分表(目前业务还未到达多库分表的需要),这里不再重复Mycat的安装与配置了,网上有很多教程。
实现单库分表需要配置3个文件:server.xml、rule.xml、schema.xml。
- server.xml:这个文件中主要配置了mycat的用户名和密码以及该用户的权限。
- rule.xml:这个文件定义了我们对表进行拆分所涉及到的规则定义。
- schema.xml:这个文件中配置的是逻辑库、逻辑表、数据节点、数据主机以及实际物理库的地址,还有部分读写规则的配置。
下面我们来修改这3个配置文件(做好备份工作):
server.xml:增加test逻辑库的用户名及密码与权限,schemas是逻辑库,要和schema.xml里的schema name对应。
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">test</property>
<property name="readOnly">true</property>
</user>
rule.xml:自定义分片规则,根据id来分片,分为2张表,取2的模。
<tableRule name="test-mod-long">
<rule>
<columns>id</columns>
<algorithm>test-mod-long</algorithm>
</rule>
</tableRule>
<function name="test-mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">2</property>
</function>
schema.xml:配置test逻辑库,test逻辑表以及数据节点。这里我们做了主从和读写分离。
<schema name="test" checkSQLschema="false" sqlMaxLimit="100">
<table name="test" primaryKey="id" autoIncrement="true" subTables="test_$1-2" rule="test-mod-long" dataNode="testNode1">
</table>
</schema>
<dataNode name="testNode1" dataHost="ReiterHost" database="test" />
<dataHost name="ReiterHost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="masterReiter" url="x.x.x.x:3306" user="root" password="123456">
<readHost host="slaveFan" url="x.x.x.x:3306" user="root" password="123456" />
</writeHost>
<writeHost host="masterFan" url="x.x.x.x:3306" user="root" password="123456" />
</dataHost>
至此,3个文件已经修改完毕了,现在我们来创建数据库和数据表。(创建test数据库,test_1和test_2数据表)
CREATE DATABASE `test`;
USE `test`;
DROP TABLE IF EXISTS `test_1`;
CREATE TABLE `test_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS `test_2`;
CREATE TABLE `test_2` LIKE `test_1`;
接下来,我们登录mycat查询一下。发现查询的结果是从test_1和test_2两张表中汇总而来,而且还自带了LIMIT 100。(注意:test表不存在于mysql中,只是在schema.xml配置中的一张逻辑表)
EXPLAIN SELECT * FROM `TEST`;
然后,我们插入下数据。
再分别查询两张分表的数据。
至此,单库分表已经完成。但是mysql的主键自增已经不起作用了,对于这种情况,我采用的做法是利用中间表存储目前主键的id值。每次往TEST表插入数据时,先从中间表取出id值,加1,作为TEST的id值,再插入数据。当然,结合缓存来存取这个主键的id值也可以,当找不到缓存时再从数据库中取出id值。(此做法主要从业务角度出发,不适合高并发的业务。)