3分钟教你学会利用Mysql merge引擎进行分表

100人浏览 2025-01-03 10:17:30

准备

Mysql版本：Mysql 5.7.20 -log

3张表test,test1,test2：

在test表中插入100w条数据，此处利用存储过程进行演示：

创建存储过程：
create procedure ptest()
 begin
 declare pid int;
 set pid=1000000;
 while pid>0
 do insert into test(name,age) values(concat("tony",pid),pid);
 set pid=pid-1;
 end while;
 end;
调用存储过程：
call ptest();

分表的方式

其实，项目中，我们可以经常看到这些技术，例如大部分论坛，聊天软件等，都会采用数据库的分表技术，今天我们就来谈一谈数据库的分表技术及其实现方式。

1.水平分表，也有江湖人称横向分表（按数据行分）

就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行Hash和取模后拆分。

水平分表示例

某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高。同时，这也会带来一些复杂的技术问题和挑战（例如：跨分片的复杂查询，跨分片事务等）

2.垂直分表（按照数据库字段分）

垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中

垂直分表示例

在字段很多的情况下，拆分开确实更便于开发和维护。某种意义上也能避免“跨页”的问题（MySQL、MSSQL底层都是通过“数据页”来存储的，“跨页”问题可能会造成额外的性能开销，这里不展开，感兴趣的朋友可以自行查阅相关资料进行研究）。单表字段数最好不要超过40个。

但是垂直分表最好在系统设计阶段就设计好，后期维护拆分成分是很大的

由于篇幅限制，今天就演示以下水平分表的一种方式，即利用Mysql自带merge引擎进行水平分表

1.已知test表中有100w调数据，接下来把test表中的数据按照id拆分到test1(id<=50w)和test2(id>50w)中

2.创建user表

你会发现user表中包含了test1,和test2中的所有数据，既实现了基于merge引擎的对test表的水平分表。

3.此时无论你从test1,或者test2中随便删改查数据，user表都能映射出对应结果

4.同样，在

user表中的任何操作也能够直接体现在test1,test2中，此时提一下创建user表时，结尾处INSERT_METHOD,表示插入的字表，LAST即第二张表，即test2.同样还有FIRST,数据会插入到test1中。

注意：
1.一个 merge 表不能在整个表上维持 unique 约束。当你执行一个 insert，数据进入第一个或者最后一个 myisam 表（取决于 insert_method 选项的值）。mysql 确保唯一键值在那个 myisam 表里保持唯一，但不是跨集合里所有的表。
2.当你创建一个 merge 表之时，没有检查去确保底层表的存在以及有相同的机构。当 merge 表被使用之时，mysql 检查每个被映射的表的记录长度是否相等，但这并不十分可靠。如果你从不相似的 myisam 表创建一个 merge 表，你非常有可能撞见奇怪的问题。