第36期（MySQL 原生水平拆表） mysql

文章图片

引言上一章节我们探讨过数据垂直拆分，今天我们来继续讨论数据拆分：水平拆分！
水平拆分和垂直拆分有些不一样，垂直拆分最小单元是字段，与业务有很强的关联性，具体业务对应具体的拆分数据；而水平拆分最小单元是数据行，与具体业务关系不大，业务关联可以是拆分后的单张表数据，也可以是拆分前的全局数据。简单来说，水平拆分对应用透明，应用逻辑在数据拆分后不需要大动。
正文一般在关系数据库中，水平拆分具体对应两个方面：第一是水平拆表。水平拆表是基于一张表的某个字段，以一定的拆分方法拆分为多张表的数据拆分，拆分完后需要把原来对单张表的操作转换为对多张子表的操作。一般来讲，和垂直拆分类似，需要一张全局路由表。定义好路由表后，即可简化对拆分表的操作。比如涉及到数据拆分后的数据同步，查询语句下发到拆分子表等都可以直接操作路由表。如下图所示，表 A 按照 ID 拆分，奇数表为表 A1 ，偶数表为表 A2 ：

文章图片

第二是水平分区。 【第36期（MySQL 原生水平拆表）】水平分区和水平拆表类似，都是基于一个字段对表数据进行拆分。不同的是水平分区是数据库内置功能，相比水平分表来讲，操作上会更加简单。不过最大的缺点是拆分后的数据不能跨实例，这也是现在大部分中间件或者是 NEW SQL 不直接用水平分区的原因。但是如果仅仅考虑在单机上进行数据拆分，应该首选水平分区，具体原因我们后续章节会讲，今天主要是来回顾下 MySQL 的内置水平拆表方案。
MySQL 原生水平拆表提到 MySQL 的原生水平拆表，能想到的就是 MERGE 表！MERGE 表是针对传统 MYISAM 表做水平汇总的功能表。一提到 MYISAM ，可能好多人觉得过时了，没必要继续了！不过我这儿要讲的是，虽然 MERGE 过时了，但是 MERGE 提供给我们未来进行水平拆分的方法是非常值得学习的，这也就是为什么这篇要对 MERGE 做一个示例说明的原因。了解如何使用 MERGE 表对我们后面要讲的 INNODB 表拆分有很大的借鉴意义。
MERGE 表的优点很多，罗列如下：

数据易于管理。比如一张很大的日志表，存放10年的数据，按照月份拆分成120张表，采用 MERGE 表做汇聚，不需要对120张子表分别检索，只需要检索 MERGE 表即可。
降低单块磁盘 IO 使用率。比如可以把日志表不同月份的数据分散到不同的磁盘来避免单一磁盘 IO 使用率过高的问题。
查询简单。比如日志表的查询模式比较固定，查询当前年份数据的请求非常频繁，历史数据偶尔查询，就可以拆分成两张表，一张当前表，一张历史表，再用 MERGE 表来做这两张表的统一入口，查询 MERGE 表会自动路由到当前表。
MERGE 表零维护。MERGE 表只存放所包含的子表元数据，所以不需要维护，创建销毁非常快速。
MERGE 表管理的子表非常灵活。每张子表不需要局限在单个数据库中，可以灵活的分布在不同的数据库里。

那接下来，用几个简单例子来了解下 MERGE 表的使用方法。表m1 - m10，10张子表，分别按照 id 从小到大来存放，每张表10000条记录，表结构如下：

(debian-ytt1:3500)|(ytt)>show create table m1\G *************************** 1. row *************************** Table: m1 Create Table: CREATE TABLE `m1` ( `id` int NOT NULL, `r1` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_r1` (`r1`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select count(*) from m1; +----------+ | count(*) | +----------+ |10000 | +----------+ 1 row in set (0.00 sec) ...

对应的 MERGE 表结构如下：

(debian-ytt1:3500)|(ytt)>show create table m_global\G *************************** 1. row *************************** Table: m_global Create Table: CREATE TABLE `m_global` ( `id` int NOT NULL, `r1` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_r1` (`r1`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT_METHOD=LAST UNION=(`m1`,`m2`,`m3`,`m4`,`m5`,`m6`,`m7`,`m8`,`m9`,`m10`) 1 row in set (0.00 sec)

表定义中包含的子表用 union 来定义，Insert_method 表示插入时往最后一张表插入。
用 merge 表来查询这10张表记录总数:

(debian-ytt1:3500)|(ytt)>select count(*) from m_global; +----------+ | count(*) | +----------+ |100000 | +----------+ 1 row in set (0.00 sec)

简化查询：
比如要在m1,m2,m3这三张表分别查询 id 为1，10001，20001的记录，需要每张表查询出来后再 UNION

(debian-ytt1:3500)|(ytt)>select * from m1 where id = 1 -> union all -> select * from m2 where id = 10001 -> union all -> select * from m3 where id = 20001; +-------+------+ | id| r1| +-------+------+ |1 |1 | | 10001 |1 | | 20001 |1 | +-------+------+ 3 rows in set (0.00 sec)

使用 merge 表来简化查询，只需查询一次即可。

(debian-ytt1:3500)|(ytt)>select * from m_global where id in (1,10001,20001); +-------+------+ | id| r1| +-------+------+ |1 |1 | | 10001 |1 | | 20001 |1 | +-------+------+ 3 rows in set (0.00 sec)

MERGE 表最大的问题是插入记录：MERGE 表属性 insert_method 有三个选项，NO/FIRST/LAST。
表m_global设置的是LAST，也就是说插入新的记录会往最后一张表里插入。比如插入一条记录（1000001，100），会往子表m10里插入。

(debian-ytt1:3500)|(ytt)>insert into m_global values (1000001,100); Query OK, 1 row affected (0.00 sec)(debian-ytt1:3500)|(ytt)>select * from m_global where id = 1000001; +---------+------+ | id| r1| +---------+------+ | 1000001 |100 | +---------+------+ 1 row in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select * from m10 where id = 1000001; +---------+------+ | id| r1| +---------+------+ | 1000001 |100 | +---------+------+ 1 row in set (0.00 sec)

同样，如果设置 insert_method=first ，则只会往第一张表插入。这会造成数据分布非常不均匀，后期需要对数据增大的表再次手动拆分。所以 MERGE 表提供了第三个选项：insert_method=no 。设置后，不允许对 MERGE 表写入，只允许读取。

(debian-ytt1:3500)|(ytt)>alter table m_global insert_method=no; Query OK, 0 rows affected (0.03 sec) Records: 0Duplicates: 0Warnings: 0(debian-ytt1:3500)|(ytt)>insert into m_global values (1000002,100); ERROR 1036 (HY000): Table 'm_global' is read only (debian-ytt1:3500)|(ytt)>

设置 MERGE 表为只读后，子表数据的分布就得靠非 MySQL 原生方法来保证。
上面我只列了 MERGE 表的优点，缺点也有很多：

MERGE 表的子表是 MYISAM 引擎，并且不能基于 MERGE 表建立全文索引。
MERGE 表使用更多的文件描述符。
对 MERGE 表索引的读取会更慢。MERGE 表会扫描底下的每张表索引来看看哪个合适。
针对第三个缺点，比如之前的查询，来对比下两条 SQL 的执行计划：

(debian-ytt1:3500)|(ytt)>explain format=treeselect * from m1 where id = 2 union all select * from m2 where id = 10002 union all select * from m3 where id = 20002\G *************************** 1. row *************************** EXPLAIN: -> Append -> Stream results -> Rows fetched before execution -> Stream results -> Rows fetched before execution -> Stream results -> Rows fetched before execution1 row in set (0.00 sec)(debian-ytt1:3500)|(ytt)>explain format=tree select * from m_global where id in (2,10002,20002)\G *************************** 1. row *************************** EXPLAIN: -> Filter: (m_global.id in (2,10002,20002))(cost=2.11 rows=3) -> Index range scan on m_global using PRIMARY(cost=2.11 rows=3)1 row in set (0.00 sec)

结果很明显，对三张子表的 UNION 查询效率比 MERGE 表查询效率高。

对 MERGE 表的删除不会删除底下的子表。MERGE 表就是一张路由表，删除路由表不会对底下子表有影响。

(debian-ytt1:3500)|(ytt)>drop table m_global; Query OK, 0 rows affected (0.01 sec)(debian-ytt1:3500)|(ytt)>show tables like 'm%'; +--------------------+ | Tables_in_ytt (m%) | +--------------------+ | m1| | m10| | m2| | m3| | m4| | m5| | m6| | m7| | m8| | m9| +--------------------+ 10 rows in set (0.00 sec)

所以 MERGE 表的应用场景仅仅局限于以下：