Mysql关于分库、分表、分区的具体介绍

àì夳堔傛蜴生んèń 2022-05-13 18:28 155阅读 0赞

# 1、分表 #

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

分表几种策略方式

## 1.1、mysql集群 ##

事实它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

## 1.2、自定义规则分表 ##

1 Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。  
2 Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。  
3 Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。  
4 List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。  
5 composite（复合模式） –以上模式的组合使用。

## 1.3、利用merge存储引擎来实现分表 ##

Merge表有点类似于视图。使用Merge存储引擎实现MySQL分表，这种方法比较适合那些没有事先考虑分表，随着数据的增多，已经出现了数据查询慢的情况。

这个时候如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码。所以使用Merge存储引擎实现MySQL分表可以避免改代码。

Merge引擎下每一张表只有一个MRG文件。MRG里面存放着分表的关系，以及插入数据的方式。它就像是一个外壳，或者是连接池，数据存放在分表里面。

merge合并表的要求：

*  合并的表使用的必须是MyISAM引擎
 *  表的结构必须一致，包括索引、字段类型、引擎和字符集

对于增删改查，直接操作总表即可。

实现方式：

1.用户1表

CREATE TABLE `user1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      `sex` int(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2.用户2表

create table user2 like user1;

3.主表

CREATE TABLE `alluser` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      `sex` int(1) NOT NULL DEFAULT '0',
      KEY `id` (`id`)
    ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`user1`,`user2`);

ps：

1) ENGINE = MERGE 和 ENGINE = MRG\_MyISAM是一样的意思，都是代表使用的存储引擎是 Merge。

2) INSERT\_METHOD，表示插入方式，取值可以是：0 和 1，0代表不允许插入，1代表可以插入；

3) FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表。

1. 先在user1表中增加一条数据，然后再在user2表中增加一条数据，查看 alluser中的数据。

insert into user1(name,sex) values ('张三',1);
    
    insert into user2(name,sex) values ('李四',2);

select \* from alluser;  发现是刚刚插入的数据如下：

![1072167-20170327174445998-1386816792.png][]

这就出现了一个id重复，这就造成了当删除和修改的时候异常，解决办法是给 alluser的id赋唯一值。

我们解决方法是，重新建立一张表tb\_ids(id int)，用来专门存一个id的，并插入一条初始数据，同时删除掉user1和user2中的数据。

create table tb_ids(id int);
    insert into tb_ids values(1);
    delete from user1;
    delete from user2;

然后在user1和user2表中分别建立一个触发器(tr\_seq和tr\_seq2)，触发器的功能是 当在user1或者user2表中增加一条记录时，取出tb\_ids中的id值，赋给user1和user2的id，然后将tb\_ids的id值加1，

user1表的触发器内容如下（user2表的触发器修要修改 触发器的名字 和 表名，如下红字标注）：

DELIMITER $$
       CREATE TRIGGER tr_seq
       BEFORE INSERT on user1
       FOR EACH ROW BEGIN 
          select id  into @testid from tb_ids limit 1;
          update tb_ids set id = @testid + 1;
       set new.id =  @testid;
       END$$
       DELIMITER;

2.在user1和user2表中分别增加一条数据，

insert into user1(name,sex) values('王五',1);
    
    　　  insert into user2(name,sex) values('赵六',2);

3.查询user1和user2中的数据：

![1072167-20170329232101373-612030119.png][]           ![1072167-20170329232203170-2119848737.png][]

4.查询总表alluser中的数据，发现id没有重复的：

![1072167-20170329232243670-1155100370.png][]

参考链接：[https://www.cnblogs.com/xbq8080/p/6628034.html][https_www.cnblogs.com_xbq8080_p_6628034.html]

# 2、分区 #

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

# 3、分表和分区的区别与联系 #

1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。  
2.分表和分区不矛盾，可以相互配合的，对于那些***大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式***（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试试），访问量不大，但是***表数据很多的表，我们可以采取分区的方式***等。  
3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。  
4.表分区相对于分表，操作方便，不需要创建子表。  
我们知道对于大型的互联网应用，数据库单表的数据量可能达到千万甚至上亿级别，同时面临这高并发的压力。***Master-Slave结构只能对数据库的读能力进行扩展，写操作还是集中在Master中***，Master并不能无限制的挂接Slave库，如果需要对数据库的吞吐能力进行进一步的扩展，可以考虑采用分库分表的策略。  
5.***数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题***

[1072167-20170327174445998-1386816792.png]: /images/20220504/134d901512cc41fb99faaeae5747c8ae.png
[1072167-20170329232101373-612030119.png]: https://images2015.cnblogs.com/blog/1072167/201703/1072167-20170329232101373-612030119.png
[1072167-20170329232203170-2119848737.png]: /images/20220504/b59047c6e842434888c06735efdefa83.png
[1072167-20170329232243670-1155100370.png]: /images/20220504/596937a23078425c84c32f86f61210e0.png
[https_www.cnblogs.com_xbq8080_p_6628034.html]: https://www.cnblogs.com/xbq8080/p/6628034.html