关于mysql:第36期MySQL-原生水平拆表

53次阅读

共计 4949 个字符，预计需要花费 13 分钟才能阅读完成。

引言

上一章节咱们探讨过数据垂直拆分，明天咱们来持续探讨数据拆分：程度拆分！

程度拆分和垂直拆分有些不一样，垂直拆分最小单元是字段，与业务有很强的关联性，具体业务对应具体的拆分数据；而程度拆分最小单元是数据行，与具体业务关系不大，业务关联能够是拆分后的单张表数据，也能够是拆分前的全局数据。简略来说，程度拆分对利用通明，应用逻辑在数据拆分后不须要大动。

注释

个别在关系数据库中，程度拆分具体对应两个方面：

第一是程度拆表。
程度拆表是基于一张表的某个字段，以肯定的拆分办法拆分为多张表的数据拆分，拆分完后须要把原来对单张表的操作转换为对多张子表的操作。一般来讲，和垂直拆分相似，须要一张全局路由表。定义好路由表后，即可简化对拆分表的操作。比方波及到数据拆分后的数据同步，查问语句下发到拆分子表等都能够间接操作路由表。

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

第二是程度分区。

程度分区和程度拆表相似，都是基于一个字段对表数据进行拆分。不同的是程度分区是数据库内置性能，相比程度分表来讲，操作上会更加简略。不过最大的毛病是拆分后的数据不能跨实例，这也是当初大部分中间件或者是 NEW SQL 不间接用程度分区的起因。然而如果仅仅思考在单机上进行数据拆分，应该首选程度分区，具体起因咱们后续章节会讲，明天次要是来回顾下 MySQL 的内置程度拆表计划。

MySQL 原生程度拆表

提到 MySQL 的原生程度拆表，能想到的就是 MERGE 表！MERGE 表是针对传统 MYISAM 表做程度汇总的功能表。一提到 MYISAM，可能好多人感觉过期了，没必要持续了！不过我这儿要讲的是，尽管 MERGE 过期了，然而 MERGE 提供给咱们将来进行程度拆分的办法是十分值得学习的，这也就是为什么这篇要对 MERGE 做一个示例阐明的起因。理解如何应用 MERGE 表对咱们前面要讲的 INNODB 表拆分有很大的借鉴意义。

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: 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: 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. 数据可靠性要求不高的表。比方新闻资讯类等。

总结

这篇咱们探讨了 MySQL 的原生程度拆表，尽管 MERGE 表曾经过期，但相熟 MERGE 表的应用能够拓展咱们后续的程度拆表思路。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
mysql
发表至： mysql
2021-10-14
 0
关于mysql:Cant-Connect-to-MySQL-Server-on-IP-Address-10061-错误的解决方案
关于mysql:深入理解MysqlMysql的锁机制
关于mysql:MySQL如何存储Emoji表情UTF8和UTF8MB4字符编码有何区别
关于mysql:MySQL连接数管理