Merge引擎将多个使用MyISAM引擎且表结构相同的数据表合并为一个表,使得查询速度更快,同时也方便了维护数据。
优点:
- 只需查询总表即可,无需分别查询各个子表;
- 查询速度比查询一张大表快;
- 按照相应规则进行分表,维护数据变得更加方便;
- 多个子表映射到一个总表的速度极快。
缺点:
- 总表必须是Merge引擎,子表必须是MyISAM引擎,且表结构必须相同(即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同),适合频繁查询的业务;
- MERGE表不能使用某些MyISAM特性。例如:子表可创建全文索引,MERGE表查询不能使用全文索引;
- 一张MyISAM引擎的表对应3个文件(.frm描述表结构 .MYD保存表数据 .MYI存储表索引),1000张表就有3000个文件,检索慢;
- 若使用ALTER TABLE语句修改MERGE表,那么会立即丢失总表和子表的映射关系,并且会将所有子表的数据拷贝至修改后的新表;
- 子表之间不能保证唯一键约束,只能保证单个子表内部的唯一性约束;
- 子表不支持分区(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的数据,这种情况是不允许的。因此有两种做法:
- 建立中间表用于存储总表目前最大的id值,每次插入数据时先从中间表中取出id值,id=id+1,更新中间表,再插入数据,此方法可结合缓存来实现,速度更快。
- 在每张子表中建立触发器,触发事件为插入数据之前:
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引擎分表至此已经做完了。