使用MySQL中Merge引擎分表

    Merge引擎将多个使用MyISAM引擎且表结构相同的数据表合并为一个表，使得查询速度更快，同时也方便了维护数据。

优点：

1. 只需查询总表即可，无需分别查询各个子表；
2. 查询速度比查询一张大表快；
3. 按照相应规则进行分表，维护数据变得更加方便；
4. 多个子表映射到一个总表的速度极快。

缺点：

1. 总表必须是Merge引擎，子表必须是MyISAM引擎，且表结构必须相同（即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同），适合频繁查询的业务；
2. MERGE表不能使用某些MyISAM特性。例如：子表可创建全文索引，MERGE表查询不能使用全文索引；
3. 一张MyISAM引擎的表对应3个文件（.frm描述表结构    .MYD保存表数据    .MYI存储表索引），1000张表就有3000个文件，检索慢；
4. 若使用ALTER TABLE语句修改MERGE表，那么会立即丢失总表和子表的映射关系，并且会将所有子表的数据拷贝至修改后的新表；
5. 子表之间不能保证唯一键约束，只能保证单个子表内部的唯一性约束；
6. 子表不支持分区（Partition）。

    接下来，我们开始实现merge引擎分表：

DROP TABLE IF EXISTS `log_1`;
DROP TABLE IF EXISTS `log_2`;
DROP TABLE IF EXISTS `log_3`;
DROP TABLE IF EXISTS `log_merge`;

CREATE TABLE `log_1`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '日志类型：1',
  `name` varchar(32) NOT NULL COMMENT '操作行为',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `i2`(`log_type`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '日志记录子表1' ROW_FORMAT = Dynamic;

CREATE TABLE `log_2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_type` tinyint(1) NOT NULL DEFAULT 2 COMMENT '日志类型：2',
  `name` varchar(32) NOT NULL COMMENT '操作行为',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `i2`(`log_type`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '日志记录子表2' ROW_FORMAT = Dynamic;

CREATE TABLE `log_3`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_type` tinyint(1) NOT NULL DEFAULT 3 COMMENT '日志类型：3',
  `name` varchar(32) NOT NULL COMMENT '操作行为',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `i2`(`log_type`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '日志记录子表3' ROW_FORMAT = Dynamic;

CREATE TABLE `log_merge`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_type` tinyint(1) NOT NULL COMMENT '日志类型：1-3',
  `name` varchar(32) NOT NULL COMMENT '操作行为',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `i2`(`log_type`) USING BTREE
) ENGINE = MRG_MYISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '日志记录总表' ROW_FORMAT = Dynamic UNION = (`log_1`,`log_2`,`log_3`) INSERT_METHOD=NO;

    仔细观察总表的sql语句，其中INSERT_METHOD我写的是NO，即不允许插入，因为总表我打算仅仅用于查询，而子表用来插入数据。INSERT_METHOD还有FIRST和LAST两个选项，即在第一个表或者在最后一个表中插入数据，但在实际业务中此类情况非常少。

    然后，我们再来分别往三张子表插入数据：

INSERT INTO `log_1`(`id`,`name`) VALUES (1, '操作行为1');
INSERT INTO `log_2`(`id`,`name`) VALUES (2, '操作行为2');
INSERT INTO `log_3`(`id`,`name`) VALUES (3, '操作行为3');

    出现问题了，子表中主键值会重复，默认取第一张表的数据。如下图：

    总表中出现了两条id=2的数据，这种情况是不允许的。因此有两种做法：

1. 建立中间表用于存储总表目前最大的id值，每次插入数据时先从中间表中取出id值，id=id+1，更新中间表，再插入数据，此方法可结合缓存来实现，速度更快。
2. 在每张子表中建立触发器，触发事件为插入数据之前：

DELIMITER $$
CREATE TRIGGER tr_log_1
BEFORE INSERT on log_1
FOR EACH ROW BEGIN 
   select id into @newid from log_merge order by id desc limit 1; ## 取log_merge表中最大的id值，@newid是临时变量
   set new.id =  @newid + 1;  ## 将最开始取到的id+1赋值给要插入的记录id
END$$
DELIMITER;

DELIMITER $$
CREATE TRIGGER tr_log_2
BEFORE INSERT on log_2
FOR EACH ROW BEGIN 
   select id into @newid from log_merge order by id desc limit 1; ## 取log_merge表中最大的id值，@newid是临时变量
   set new.id =  @newid + 1;  ## 将最开始取到的id+1赋值给要插入的记录id
END$$
DELIMITER;

DELIMITER $$
CREATE TRIGGER tr_log_3
BEFORE INSERT on log_3
FOR EACH ROW BEGIN 
   select id into @newid from log_merge order by id desc limit 1; ## 取log_merge表中最大的id值，@newid是临时变量
   set new.id =  @newid + 1;  ## 将最开始取到的id+1赋值给要插入的记录id
END$$
DELIMITER;

    好了，让我们再插入数据试试吧：

    Merge引擎分表至此已经做完了。