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)一条记录,那么就将这条记录插入到合并表的最后一个表里面。

insert_method 的值可以是 FIRST(插入第一个表),LAST(最后一个表),NO(不能插入) 。

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 264 (~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;

# 或者: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  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表。

相关文章:

MySQL根据表前缀批量修改、删除表 (xushanxiang.com)


已发布

分类

来自

标签: