MySQL分表查询之Merge存储引擎实现

概念介绍

MySQL 分表之后怎么进行联合查询？用有表数量限制的 union all，还是汇总到一张表再查询，亦或用Sphinx（ 高性能SQL全文检索引擎 ）？

在这篇文章里，介绍使用 Merge [mɜːrdʒ] 存储引擎实现 MySQL [maɪ es kju: el]分表查询。

MERGE 是MySQL最简单的一种分表，MySQL自带的一个分表功能，Merge表并不保存数据，Merge表和分表是对应映射关系。

MERGE 存储引擎把一组 MyISAM 数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。

分区表(Partition)是 MySQL 5.1 的新特性，而合并表 MERGE 已经有很长的历史了，合并表和分区表的概念比较相似，合并表是将许多个 MyISAM 表合并成一个续表，类似于使用 UNION 语句将多个表合并，合并表不是真的创造一张表，它就像是一个用户放置相似表的容器。而分区表则通过一些特殊的语句，创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表。

操作实例

建表

我们先依次建立 3 张结构相同，自增 ID 不同的数据表，假设每张表最多100万 ID。

CREATE TABLE `xushanxiang`.`users1` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `uname` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARSET=utf8 COLLATE utf8_general_ci;
alter table users1 auto_increment=1000000;
INSERT INTO `users1` (`id`, `uname`) VALUES (NULL, '用户一');

CREATE TABLE `xushanxiang`.`users2` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `uname` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARSET=utf8 COLLATE utf8_general_ci;
alter table users2 auto_increment=2000000;
INSERT INTO `users2` (`id`, `uname`) VALUES (NULL, '用户二');

CREATE TABLE `xushanxiang`.`users3` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `uname` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARSET=utf8 COLLATE utf8_general_ci;
alter table users3 auto_increment=3000000;
INSERT INTO `users3` (`id`, `uname`) VALUES (NULL, '用户三');

 

再使用 UNION 语句创建表 users1 和 users2 的合并表 users，命令如下：

CREATE TABLE users ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `uname` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = MERGE CHARSET=utf8 COLLATE utf8_general_ci UNION=(users1,users2) INSERT_METHOD=LAST

推荐的使用方法是先有一个MERGE表，里面只包含一张表，当一个这个表的的大小增长到一定程度（比如100w）时，创建另一张空表，将其挂入MERGE表，然后继续插入记录。

说明

之后，我们在合并表users里面查询(select)、修改(update)、删除(delete)记录，都会对实际的分表产生对应的操作。

insert_method=last 的含义是，如果向表users中插入(新增insert)一条记录，那么就将这条记录插入到合并表的最后一个表里面。

ENGINE=MERGE 指明使用 MERGE 引擎，可能见到过ENGINE=MRG_MyISAM的例子，它们是一回事。

UNION=(users1, users2) 指明了 MERGE 表中挂接了些哪表，可以通过 alter table 的方式修改UNION的值，以实现增删MERGE表子表的功能。

注意

• MySQL合并表的实现对性能有一定的影响，合并表看上去是一张表，事实上是逐个打开各个子表，这样的情况下，可能会因为缓存过多而导致超过MySQL缓存的最大设置。
• 某些功能在表格MyISAM中不可用。MERGE例如，您不能在表上创建FULLTEXT索引。
• 如果MERGE表是非临时的，则所有基础MyISAM表都必须是非临时的。如果MERGE表是临时表，则MyISAM表可以是临时表和非临时表的任意组合。
• MERGE表比表使用更多的文件描述符MyISAM。如果 10 个客户端使用MERGE映射到 10 个表的表，则服务器使用 (10 × 10) + 10 个文件描述符。（10 个客户端中的每一个都有 10 个数据文件描述符，以及在客户端之间共享的 10 个索引文件描述符。）索引读取速度较慢。当您读取索引时，MERGE存储引擎需要对所有基础表发出读取，以检查哪一个与给定索引值最匹配。要读取下一个索引值，MERGE存储引擎需要搜索读取缓冲区以找到下一个值。只有当一个索引缓冲区用完时，存储引擎才需要读取下一个索引块。这使得MERGE索引在搜索 eq_ref 时要慢得多，但在搜索 ref 时不会慢得多。
• 最大行数问题：The maximum number of rows in a MERGE table is 2的64次方 (~1.844E+19; the same as for a MyISAM table). It is not possible to merge multiple MyISAM tables into a single MERGE table that would have more than this number of rows.
• 创建合并表的CREATE语句不会检查子表是否兼容，如果创建了一个有效的合并表之后对某个表进行了修改，那么合并表也会发生错误。
• merge表并不维护 “唯一性”检查，唯一性有各基础表完成。所以插入新的记录时候可能和其他基础表的内容重复。所以再插入去需要用代码进行唯一性检查。
• 若数据主键已存在则无法插入。MERGE表只对建表之后的操作负责。
• 若MREGE后存在重复主键，按主键查询，顺序查询，只出现一条查询记录即停止。
• 合并表可以重命名，例如执行：rename table users to members;
• 删除MERGE表不会对子表产生影响。

出错

如果打开新建的 users 表是报错如下：

#1168-Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

请检查下面的一些可能问题：

1. 查看是不是有一些表不是 MYISAM 引擎的表，因为 MERGE 引擎只适用于 MYISAM 表。而分区则没有限制。
2. 查看是不是在 union 的表中含有不存在的表。
3. 查看是不是 MERGE 的时候引用了不在同一个库的表，并且该表没有指定数据库名字。
4. 比较各个表的结构（索引、引擎、列、字符集等）是否一致。

查询

因为数据量小，我们直接执行下面的语句：

SELECT * FROM `users`

新增分表

新增分表的结构必须和前面的其它分表一样。

CREATE TABLE `xushanxiang`.`users3` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `uname` VARCHAR(10) NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM CHARSET=utf8 COLLATE utf8_general_ci;

alter table users3 auto_increment=3000000;

INSERT INTO `users3` (`id`, `uname`) VALUES (NULL, '用户三');

再把新增的分表加入到merge表users中：

ALTER TABLE users UNION=(users1, users2, users3);

删除分表

直接删除一个子表，MERGE表会被破坏，正确方式是先用 alter table 方式先将子表从MERGE表中去除，再删除子表。

例如：删除分表users1：

alter table users ENGINE=MRG_MyISAM UNION=(users2,users3) INSERT_METHOD=LAST;

DROP TABLE users1;

误删子表怎么办？

误删子表时，如何恢复MERGE表？误删子表时，MERGE表上将无法进行任何操作。

方法1，drop MERGE表，重建。重建时注意在UNION部分去掉误删的子表。

方法2，建立MERGE表时，会在数据库目录下生成一个.MRG文件，比如设表名为users，则文件名为users.MRG。文件内容类似：

可以直接修改此文件，去掉误删表的表名。然后执行 flush tables 即可修复MERGE表。