MYSQL利用merge存储引擎来实现分表

  1. 云栖社区>
  2. 博客>
  3. 正文

MYSQL利用merge存储引擎来实现分表

幸运码发放 2018-09-08 06:42:35 浏览1098
展开阅读全文

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

加入测试数据:

INSERT INTO user1(NAME,sex) VALUES('huangbaokang',0)
INSERT INTO user2(NAME,sex) VALUES('zhanglulu',0)
  • 1
  • 2

创建总表:

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

采用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
  • 1
  • 2
  • 3

这样我就成功的将一张user表,分成了二个表,这个时候有一个问题,代码中的sql语句怎么办,以前是一张表,现在变成二张表了,代码改动很大,这样给程序员带来了很大的工作量,有没有好的办法解决这一点呢?办法是把以前的user表备份一下,然后删除掉,上面的操作中我建立了一个t_user表,只把这个t_user表的表名改成user就行了。但是,不是所有的mysql操作都能用的。

如: 
如果你使用 alter table 来把 merge 表变为其它表类型,到底层表的映射就被丢失了。取而代之的,来自底层 myisam 表的行被复制到已更换的表中,该表随后被指定新类型。

更新t_user表,看会不会影响其他表数据。执行如下:

UPDATE t_user SET sex=1
  • 1

再次查询,发现是可以修改存储在其他表的数据。 
这里写图片描述

原文地址https://blog.csdn.net/huangbaokang/article/details/82494578

网友评论

作者关闭了评论
幸运码发放
+ 关注