谦谦君子

2019-01-16 · 3640 次阅读

使用 Merge 存储引擎实现 MySQL 或 MariaDB 分表

原创 mysql mariadb

分表就是把 N 条记录的表，分成若干个分表，各个分表记录的总和仍为 N。分表的方法有很多，用 merge 来分表是最简单的一种方式。

Merge 表有点类似于视图。使用 Merge 存储引擎实现 MySQL 分表，这种方法比较适合那些没有事先考虑分表，随着数据的增多，已经出现了数据查询慢的情况。

这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码。所以使用 Merge 存储引擎实现 MySQL 分表可以避免改代码。

Merge 引擎下每一张表只有一个 MRG 文件。MRG 里面存放着分表的关系，以及插入数据的方式。它就像是一个外壳，或者是连接池，数据存放在分表里面。

merge 合并表的要求：

合并的表使用的必须是 MyISAM 引擎
表的结构必须一致，包括索引、字段类型、引擎和字符集

对于增删改查，直接操作总表即可，也可以单独查询分表。

Merge 表的优点

A：分离静态的和动态的数据

B：利用结构接近的的数据来优化查询

C：查询时可以访问更少的数据

D：更容易维护大数据集

E: 可以通过修改 .mrg 文件来修改 Merge 表，当然也可以用 alter 进行修改，修改后要通过 FLUSH TABLES 刷新表缓存，此法可以动态增加减少子表

merge 详解

merge 是 mysql 的一种存储引擎，它把一组 MyISAM 数据表当做一个逻辑单元。

CREATE TABLE t1    (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) engine=myisam;
CREATE TABLE t2    (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) engine=myisam;
CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

即创建表的语句制定引擎即可：engine=myisam，另外，创建分表也可以用 like，如：

CREATE TABLE t2 like t1;

对应定期分表的情况下，只要定期相应的增加一个基础表，再修改 merge 表中的 union 就行了

ALTER TABLE tbl_name UNION=(t1,t2)

如在增加一个表（需和其他基础表一样的结构）：

CREATE TABLE t3(a INT NOT NULL AUTO_INCREMENT PRIMARYKEY,message CHAR(20));
ALTER TABLE total UNION=(t1,t2,t3)

INSERT_METHOD=LAST; 表示插入的方法，INSERT_METHOD 的值可以是 FIRST（插入第一个表），LAST（最后一个表），NO（不能插入）

查询的时候，和平常一样：

select * from total where -- ...

merge 表会自动找到相应的基础表进行查询。

需要注意的是 merge 表并不维护 唯一性 检查，唯一性有各基础表完成。所以插入新的记录时候可能和其他基础表的内容重复。所以再插入去需要用代码进行唯一性检查。

不能直接删除一个分表，这样会破坏 merge 表。正确的方法是:

alter table total ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;
drop table t2

实战演练

建表

1.用户1表

CREATE TABLE `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;

2.用户2表

create table user2 like user1;

3.主表

CREATE TABLE `alluser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `sex` int(1) NOT NULL DEFAULT '0',
  KEY `id` (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`user1`,`user2`);

ENGINE = MERGE 和 ENGINE = MRG_MyISAM 是一样的意思，都是代表使用的存储引擎是 Merge。

操作

1.先在 user1 表中增加一条数据，然后再在 user2 表中增加一条数据，查看 alluser 中的数据。

insert into user1(name, sex) values ('张三', 1);
insert into user2(name, sex) values ('李四', 2);

select * from alluser;

发现是刚刚插入的数据如下：

这就出现了一个 id 重复，这就造成了当删除和修改的时候异常，解决办法是给 alluser 的 id 赋唯一值。

我们解决方法是，重新建立一张表 tb_ids(id int)，用来专门存一个 id 的，并插入一条初始数据，同时删除掉 user1 和 user2 中的数据。

create table tb_ids(id int);
insert into tb_ids values(1);
delete from user1;
delete from user2;

然后在 user1 和 user2 表中分别建立一个触发器(tr_seq 和 tr_seq2)，触发器的功能是当在 user1 或者 user2 表中增加一条记录时，取出 tb_ids 中的 id 值，赋给 user1 和 user2 的 id，然后将 tb_ids 的 id 值加 1，user1 表的触发器内容如下（user2 表的触发器修要修改触发器的名字和表名，如下红字标注）：

DELIMITER $$
   CREATE TRIGGER tr_seq
   BEFORE INSERT on user1
   FOR EACH ROW BEGIN 
      select id  into @testid from tb_ids limit 1;
      update tb_ids set id = @testid + 1;
   set new.id =  @testid;
   END$$
   DELIMITER;

2.在 user1 和 user2 表中分别增加一条数据，

insert into user1(name, sex) values('王五', 1);
insert into user2(name, sex) values('赵六', 2);

3.查询 user1 和 user2 中的数据：

4.查询总表 alluser 中的数据，发现 id 没有重复的：