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

「爱情、让人受尽委屈。」 2024-03-27 11:16 44阅读 0赞

### 概念介绍 ###

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

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

MERGE 是MySQL最简单的一种分表，MySQL自带的一个分表功能，Merge表并不保存数据，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

![62e4969944749b0818303972971de48a.png][]

推荐的使用方法是先有一个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][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`

![47267e8710d6ad92a4125a9763c13f94.png][]

#### 新增分表 ####

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

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。文件内容类似：

![1ff90976a8790c9a59f99b6c93a5cd77.png][]

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

[Sphinx]: https://www.runoob.com/w3cnote/sphinx-sql-search-engine.html
[MERGE]: https://dev.mysql.com/doc/refman/5.6/en/merge-storage-engine.html
[62e4969944749b0818303972971de48a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9647f6b3f8b04361885af7a7387aee9f.png
[eq_ref]: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_eq_ref
[ref]: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_ref
[47267e8710d6ad92a4125a9763c13f94.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b8ad0c4f337c430c85aca2adf5e510e8.png
[1ff90976a8790c9a59f99b6c93a5cd77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/0ea784df748546c08f91c97f2833a3f4.png