概念介绍
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 aMyISAM
table). It is not possible to merge multipleMyISAM
tables into a singleMERGE
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
请检查下面的一些可能问题:
- 查看是不是有一些表不是 MYISAM 引擎的表,因为 MERGE 引擎只适用于 MYISAM 表。而分区则没有限制。
- 查看是不是在 union 的表中含有不存在的表。
- 查看是不是 MERGE 的时候引用了不在同一个库的表,并且该表没有指定数据库名字。
- 比较各个表的结构(索引、引擎、列、字符集等)是否一致。
查询
因为数据量小,我们直接执行下面的语句:
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表。
相关文章: