2

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

 1 year ago
source link: https://xushanxiang.com/mysql-subtable-merge.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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

作者: xusx 分类: MySQL 发布时间: 2022-06-27 18:45 浏览：7

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

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

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

分区表是 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，命令如下：

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> users <span class="token punctuation">(</span> <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> UNSIGNED <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token punctuation">,</span> <span class="token punctuation">`</span>uname<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MERGE</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8 <span class="token keyword">COLLATE</span> utf8_general_ci <span class="token keyword">UNION</span><span class="token operator">=</span><span class="token punctuation">(</span>users1<span class="token punctuation">,</span>users2<span class="token punctuation">)</span> INSERT_METHOD<span class="token operator">=</span><span class="token keyword">LAST</span>
  • MySQL合并表的实现对性能有一定的影响，合并表看上去是一张表，事实上是逐个打开各个子表，这样的情况下，可能会因为缓存过多而导致超过MySQL缓存的最大设置。
  • 创建合并表的CREATE语句不会检查子表是否兼容，如果创建了一个有效的合并表之后对某个表进行了修改，那么合并表也会发生错误。

如果打开新建的 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`
MySQL-MERGE-1.png

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK