MYSQL利用merge存储引擎来实现分表
创建user1和user2两个分表
建表语句如下:只是表名不一样,其他字段信息及主键一致。
CREATE TABLE IF NOT EXISTS user1(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS user2(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
加入测试数据:
INSERT INTO user1(NAME,sex) VALUES('huangbaokang',0)
INSERT INTO user2(NAME,sex) VALUES('zhanglulu',0)
创建总表:
CREATE TABLE IF NOT EXISTS t_user (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) DEFAULT NULL,
sex INT(1) NOT NULL DEFAULT '0',
INDEX(id)
) ENGINE = MRG_MYISAM UNION =(user1,user2) INSERT_METHOD LAST CHARSET UTF8;
采用merge类型,insert_method为last
查询结果如下:
往t_user表中插入一条数据:
INSERT INTO t_user(NAME,sex) VALUES('猪八戒',1);
- 1
插入到了user2表中,因为INSERT_METHOD为last,最后插入的是user2表。
业务分表实现
当一个项目数据库表设计的时候没有考虑到分表时,时间久而久之,表的数据量会非常巨大,如某某平台注册信息表。
假如我有一张用户表user,有50W条数据,现在要拆成二张表user1和user2,每张表25W条数据,
INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
这样我就成功的将一张user表,分成了二个表,这个时候有一个问题,代码中的sql语句怎么办,以前是一张表,现在变成二张表了,代码改动很大,这样给程序员带来了很大的工作量,有没有好的办法解决这一点呢?办法是把以前的user表备份一下,然后删除掉,上面的操作中我建立了一个t_user
表,只把这个t_user
表的表名改成user就行了。但是,不是所有的mysql操作都能用的。
如:
如果你使用 alter table 来把 merge 表变为其它表类型,到底层表的映射就被丢失了。取而代之的,来自底层 myisam 表的行被复制到已更换的表中,该表随后被指定新类型。
更新t_user表,看会不会影响其他表数据。执行如下:
UPDATE t_user SET sex=1
再次查询,发现是可以修改存储在其他表的数据。
QQ:1542385235 (PHP、Java、安卓苹果app制作修改、页面切图、各类模板修改、仿站,数据库修复、WAP制作修改 。我们团队是专门做网站开发的,都是有3年以上工作经验。需要后台系统开发,网页页面制作,app制作,ui设计的请加我qq联系。非诚勿扰!!)
本人qq群也有许多的技术文档,希望可以为你提供一些帮助(非技术的勿加!)。
QQ群: 281442983 (点击链接加入群:http://jq.qq.com/?_wv=1027&k=29LoD19)