mysql通过Merge引擎进行分表 - TIMO-一个有趣的学习家园

一、使用场景

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

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

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

　　merge合并表的要求：

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

　　对于增删改查，直接操作总表即可。

注意

分表的id不能是自增（auto increment）的；
分表必须使用MyISAM存储引擎；
每个分表的表结构必须相同；
MySQL必须具有存储分表数据文件和索引文件的目录的读写权限；
必须启用MySQL的符号链接支持功能。
总表的表结构必须与各个分表相同；
总表必须使用MRG_MyISAM存储引擎；
总表不会创建任何数据文件和索引文件；
MRG文件存储总表需要映射的子表的表名；
总表本身不存储任何数据和索引；
INSERT_METHOD需要设置为NO，或者不配置；
总表的id不能是自增（auto increment）的。

MERGE分表的优点

MERGE分表可以解决下面的问题：

适用于存储日志数据。例如，可以将不同月份的数据存入不同的表，然后使用myisampack工具压缩数据，最后通过一张MERGE表来查询这些数据。

可以获得更快的速度。可以根据某种指标，将一张只读的大表分割成若干张小表，然后将这些小表分别放在不同的磁盘上存储。当需要读取数据时，MERGE表可以将这些小表的数据组织起来，就好像使用先前的大表一样，但是速度会快很多。

可以提高搜索效率。可以根据某种指标将一张只读的大数据表分割为若干个小表，然后根据不同的查询维度，可以得到若干种小表的组合，然后再为这些组合分别创建不同的MERGE表。例如，有一张只读的大数据表T，分割为T1、T2、T3、T4，共4张小表，有两种查询维度A和B，A可以得到小表组合T1、T2和T3，B可以得到小表组合T2、T3和T4，分别为A和B创建两个MERGE表，也就是M1和M2，这两个MERGE表分别关联的小表是存在交叠的。

可以更加有效的修复表。修复单个的小表要比修复大数据表更加容易。

多个子表映射至一个总表的速度极快。因为MERGE表本身不会存储和维护任何索引，索引都是由各个关联的子表存储和维护的，所以创建和重新映射MERGE表的速度非常快。

不受操作系统的文件大小限制。单个表会受到文件大小的限制，但是拆分成多个表，则可以无限扩容。

MERGE表还可以用来给单个表创建别名，并且几乎不会影响性能。

MERGE分表的问题

总表（MERGE表）必须使用MRG_MyISAM存储引擎，子表必须使用MyISAM存储引擎，不可避免会受到MyISAM存储引擎的限制。

MERGE表不能使用某些MyISAM特性。例如，虽然可以为子表创建全文索引，但是却不能使用全文索引，通过MERGE表查询数据。

MERGE表会使用更多的文件描述符。如果有10个客户端使用1张MERGE表，那么就需要消耗(10×10)+10个文件描述符（其中，10个客户端分别有10个数据文件描述符，并且会共享使用10个索引文件描述符）。

若使用ALTER TABLE语句修改总表的存储引擎，那么会立即丢失总表和子表的映射关系，并且会将所有子表的数据拷贝至修改后的新表。

总表和子表的主键都不能使用自动增长（auto increment）。

子表之间不能保证唯一键约束，只能保证单个子表内部的唯一性约束。

由于不能保证唯一键约束，导致REPLACE语句的行为会不可预期，INSERT ... ON DUPLICATE KEY UPDATE语句也有类似问题。因此，只能使用路由策略，对子表使用这些语句，而不能对总表使用。

子表不支持分区（Partition）。

当正在使用总表时，不能对任何子表执行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE语句，否则会导致不可预期的结果。

总表和子表的表结构必须完全一致。

总表可以映射的所有子表的总行数上限为 264 行。

不支持INSERT DELAYED语句。

二、建表

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`);

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

　　2) INSERT_METHOD，表示插入方式，取值可以是：0 和 1，0代表不允许插入，1代表可以插入；

　　3) FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表。

三、操作

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表的触发器要修改触发器的名字(tr_seq) 和表名(user1)，如下）：

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没有重复的：

--end--