第36期：MySQL 原生水平拆表

引言

上一章节我们探讨过数据垂直拆分，今天来继续讨论数据拆分的话题：水平拆分！

正文

一般在关系数据库中，水平拆分具体对应两个方面：

第一是水平拆表

水平拆表是基于一张表的某个字段，以一定的拆分方法将单表拆分为多张表的拆分策略。拆分完后需要把原来对单张表的操作转换为对多张子表的操作。一般来讲，和垂直拆分类似，需要一张全局路由表。定义好路由表后，即可简化对拆分表的操作。比如涉及到数据拆分后的数据同步，查询语句下发到拆分子表等都可以直接操作路由表。

如下图所示，表 A 按照 ID 拆分，奇数表为表 A1 ，偶数表为表 A2 ：

第二是水平分区

MySQL 原生水平拆表

MERGE 表的优点很多，罗列如下：

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

那接下来，用几个简单例子来了解下 MERGE 表的使用方法。

表m1 - m10，10张子表，分别按照 id 从小到大来存放，每张表10000条记录，表结构如下：

(debian-ytt1:3500)|(ytt)>show create table m1\G*************************** 1. row ***************************       Table: m1Create 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_ci1 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_globalCreate 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)

用 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: 0  Duplicates: 0  Warnings: 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 表的优点，缺点也有很多：

1. MERGE 表的子表是 MYISAM 引擎，并且不能基于 MERGE 表建立全文索引。

2. MERGE 表使用更多的文件描述符。

3. 对 MERGE 表索引的读取会更慢。MERGE 表会扫描底下的每张表索引来看看哪个合适。

   针对第三个缺点，比如之前的查询，来对比下两条 SQL 的执行计划：

   (debian-ytt1:3500)|(ytt)>explain format=tree  select * 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 execution      1 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 表查询效率高。

1. 对 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 表的应用场景仅仅局限于以下：

1. 日志表，并且预先拆分完毕。比如按照日期，按照用户 ID 等。

2. 不常更新的表，可以对表进行压缩。
3. 数据可靠性要求不高的表。比如新闻资讯类等。

总结