序言
日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕,可能就死在那了。分表的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。这里主要讲使用MERGE存储引擎来实现分表。
正文
个人是小白,所以在前人的经验上,把自己踩过的坑填上。在这里博主用的是MySQL5.5,有一张city表,举例把它分成两张表。
首先创建两张子表和city表具有同样的表结构
#查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | MUL | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.12 sec)
#创建子表
mysql>CREATE TABLE IF NOT EXISTS `city1` (
-> `ID` int(11) NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL,
-> `CountryCode` char(3) NOT NULL,
-> `District` char(20) NOT NULL,
-> `Population` int(11) NOT NULL,
-> PRIMARY KEY (`ID`) )
-> ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
mysql>CREATE TABLE IF NOT EXISTS `city2` (
-> `ID` int(11) NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL,
-> `CountryCode` char(3) NOT NULL,
-> `District` char(20) NOT NULL,
-> `Population` int(11) NOT NULL,
-> PRIMARY KEY (`ID`) )
-> ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
现在来创建主表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。
#创建主表
mysql>CREATE TABLE IF NOT EXISTS `allcity` (
-> `ID` int(11) NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL,
-> `CountryCode` char(3) NOT NULL,
-> `District` char(20) NOT NULL,
-> `Population` int(11) NOT NULL,
-> PRIMARY KEY (`ID`) )
-> ENGINE=MERGE UNION=(city1,city2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;
创建主表的时候有个INSERT_METHOD,指明插入方式,取值可以是:0 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。
现在我需要把现有的city表分为分拆到city1和city2表中。
#把2000条数据插入city1表
mysql>INSERT INTO city1(city1.ID,city1.Name,city1.CountryCode,city1.District,city1.Population) SELECT city.ID,city.Name,city.CountryCode,city.District,city.Population FROM city where city.ID <= 2000;
#把剩下的数据插入city2表
mysql>INSERT INTO city2(city2.ID,city2.Name,city2.CountryCode,city2.District,city2.Population) SELECT city.ID,city.Name,city.CountryCode,city.District,city.Population FROM city where city.ID >= 2000;
这样我就成功的将一张city表,分成了二个表,这个时候有一个问题,代码中的sql语句怎么办,以前是一张表,现在变成二张表了,代码改动很大,这样带来了很大的工作量,有没有好的办法解决这一点呢?办法是把以前的city表备份一下,然后删除掉,上面的操作中我建立了一个allcity表,只把这个allcity表的表名改成city就行了。