MySQL分区和分表的区别与联系

一、分区和分表简介

  • 分表
    就是将一张大表分成N个小表。

  • 分区
    将一张大表的数据分成N个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上。

二、分区和分表的区别

1. 实现方式

1.1 分表

MySQL的分表是真正的分表,将一张大表分成若干个小表之后,每个小表都是完整的一张表,都对应三个文件(以MyISAM存储引擎为例):

  • .MYD文件:数据文件;
  • .MYI文件:索引文件;
  • .FRM文件:表结构文件。

如下图所示:

MyISAM分表文件

上述的分表使用MERGE存储引擎(MyISAM存储引擎的分表变种)。alluser是总表,它下面有三个分表,user1user2user3。这三个子表都是独立的表,读取数据的时候,我们可以通过总表来取数据。

由上图可知,三个子表的.MYD(数据)和.MYI(索引)文件并没有存储在数据库指定的数据目录(Data Directory)中,而是存储在各自指定的目录中,然后在数据目录中创建相应的软链接指向它们。实际上,MySQL支持将分表的相关文件分散存储在不同的磁盘和分区中,对于拥有多个磁盘的数据库服务器来说,无疑能够充分利用多个磁盘的I/O性能。

由上图还可知,总表是没有.MYD.MYI文件的,也就是说,总表并不会存储任何数据和索引,这些都存储在各个分表的相关文件之中。总表的.MRG文件存储的内容如下图所示:

MRG文件内容

显而易见,alluser.MRG文件存储的是分表的表名,以及插入数据的方式。此处,可以把总表理解成一个外壳,或者是连接池。关于MyISAM存储引擎的分表机制,请参考:
使用MERGE存储引擎实现MySQL的分表机制

1.2 分区

将一张大表分区之后,它还是一张表,不会变成多张表,但是它存放数据的区块变多了。如下图所示:

MyISAM分区文件

由上图可知,分区文件的文件名格式为:

  1. 索引文件名称:
  2. 表名#P#分区名.MYI
  3. 数据文件名称:
  4. 表名#P#分区名.MYD

示例中的big_table表被分为四个分区,分别是part0、part1、part2和part3。这四个分区的.MYD(数据)和.MYI(索引)文件也可以分散存储在不同的目录、分区或磁盘中,然后在数据库指定的数据目录(Data Directory)中创建软链接指向这些文件。对于拥有多个磁盘的数据库服务器来说,无疑能够充分利用多个磁盘的I/O性能。

除了上述四个分区的相应文件之外,数据库还会创建一个.par文件。这个文件类似于分表中的.MRG文件,用于存储这张表的分区信息,其内容如下所示:

PAR文件内容

关于MyISAM存储引擎的分区机制,请参考:
MySQL分区理解和实践

2. 数据处理

2.1 分表

分表之后,数据都是存放在分表里的,总表只是一个外壳,存取数据发生在一个一个的分表里面。看下面的例子:

  1. select * from alluser where id='12';

表面上看,是对表alluser进行操作的,实际并不是的,而是对alluser里面的分表进行了操作。

2.2 分区

分区不存在分表的概念,分区只不过把存放数据的文件分割成了许多小块,分区后的表仍然是一张表,数据处理还是由自己来完成。

3. 性能提升

3.1 分表

分表之后,单表的并发能力提高了,磁盘I/O性能也提高了。并发能力为什么会提高呢?因为查询一次所花的时间变短了,如果出现高并发的话,总表可以根据不同的查询,将并发压力分散到不同的小表里面。磁盘I/O性能为什么会提高呢?因为本来一个非常大的.MYD(数据)文件现在也分散到各个小表的.MYD(数据)文件中去了。

3.2 分区

MySQL提出了分区的概念,就是想要突破磁盘I/O瓶颈,想要提高磁盘的读写能力,借此提高MySQL的性能。在这一点上,分区和分表的测重点不同:分表的重点是存取数据时,如何提高MySQL的并发能力;分区则偏重于如何突破磁盘的读写能力,从而达到提高MySQL性能的目的。

4. 实现难度

4.1 分表

分表的方法有很多,用MERGE存储引擎来分表,是最简单的一种方式。这种方式和分区难易度差不多,并且对应用代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

4.2 分区

分区实现是比较简单的,建立分区表和建立普通的表没有什么区别,并且对于应用代码来说是透明的。

三、分区和分表的关系

  • 都能够提高MySQL的性能,分表能够改善高并发的性能,分区能够充分利用磁盘的I/O吞吐率。

  • 分表和分区并不矛盾,而是可以相互配合的。对于那些访问量比较大,并且数据量比较多的表,可以采取分表和分区结合的方式(如果MERGE存储引擎的分表方式不能和分区配合的话,也可以使用其他的分表方式)。对于访问量不大,但是数据量比较多的表,可以只采取分区的方式。