MySQL数据库：分区Partition

╰半夏微凉° 2022-04-02 06:20 314阅读 0赞

### 一、分区： ###

分区就是将表的数据按照特定规则存放在不同的区域，也就是将表的数据文件分割成多个小块，在查询数据的时候，只要知道数据数据存储在哪些区域，然后直接在对应的区域进行查询，不需要对表数据进行全部的查询，提高查询的性能。同时，如果表数据特别大，一个磁盘磁盘放不下时，我们也可以将数据分配到不同的磁盘去，解决存储瓶颈的问题，利用多个磁盘，也能够提高磁盘的IO效率，提高数据库的性能。常见的分区类型有：Range分区、List分区、Hash分区、Key分区：

>  *  （1）Range分区：按照连续的区间范围进行分区
>  *  （2）List分区：按照给定的集合中的值进行选择分区。
>  *  （3）Hash分区：基于用户定义的表达式的返回值进行分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
>  *  （4）Key分区：类似于按照HASH分区，区别在于Key分区只支持计算一列或多列，且key分区的哈希函数是由 MySQL 服务器提供。

**1、表分区的优点：**

（1）可伸缩性：

>  *  将分区分在不同磁盘，可以解决单磁盘容量瓶颈问题，存储更多的数据，也能解决单磁盘的IO瓶颈问题。

（2）提升数据库的性能：

>  *  减少数据库检索时需要遍历的数据量，在查询时只需要在数据对应的分区进行查询。
>  *  避免Innodb的单个索引的互斥访问限制
>  *  对于聚合函数，例如sum()和count()，可以在每个分区进行并行处理，最终只需要统计所有分区得到的结果

（3）方便对数据进行运维管理：

>  *  方便管理，对于失去保存意义的数据，通过删除对应的分区，达到快速删除的作用。比如删除某一时间的历史数据，直接执行truncate，或者直接drop整个分区，这比detele删除效率更高；
>  *  在某些场景下，单个分区表的备份很恢复会更有效率。

**2、表分区的缺陷：**

（1）分区字段必须放主键或者唯一索引中；

（2）每个表最大分区数为1024；

**3、业务场景举例：**

（1）项目需要动态新建、删除分区。比如新闻表，按照时月份进行分区，同时为了防止新闻表过大，只保留最近6个月的分区，同时预建后面3个月的分区，这个删除、预建分区的过程就是分区表的动态管理。

（2）历史数据或不常访问的数据占很大部分，最新或热点数据占的比例不是很大，这时也可以进行表分区。

**4、MySQL分区类型：**

根据所使用的不同分区规则，可以分成几大分区类型：

<table> 
 <tbody> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">序号</td> 
   <td style="text-align:center;vertical-align:middle;">分区类型</td> 
   <td style="text-align:center;vertical-align:middle;">说明</td> 
   <td style="text-align:center;vertical-align:middle;">使用频率</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">1</td> 
   <td style="text-align:center;vertical-align:middle;">RANGE 分区</td> 
   <td style="text-align:center;vertical-align:middle;"> <p style="text-indent:33px;">按照连续的区间范围进行分区</p> </td> 
   <td style="text-align:center;vertical-align:middle;">较多</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">2</td> 
   <td style="text-align:center;vertical-align:middle;">LIST 分区</td> 
   <td style="text-align:center;vertical-align:middle;"> <p style="text-indent:33px;">按照给定的集合中的值进行选择分区</p> </td> 
   <td style="text-align:center;vertical-align:middle;">一般</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">3</td> 
   <td style="text-align:center;vertical-align:middle;">HASH 分区</td> 
   <td style="text-align:center;vertical-align:middle;"> <p style="text-indent:33px;">基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。</p> </td> 
   <td style="text-align:center;vertical-align:middle;">较多</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;vertical-align:middle;">4</td> 
   <td style="text-align:center;vertical-align:middle;">KEY 分区</td> 
   <td style="text-align:justify;vertical-align:middle;"> <p style="text-indent:33px;">类似于按照HASH分区，除了区别在于KEY分区只支持计算一列或多列，且KEY分区的哈希函数是由MySQL 服务器提供。</p> </td> 
   <td style="text-align:center;vertical-align:middle;">一般</td> 
  </tr> 
 </tbody> 
</table>

**（1）range分区：**

每个分区的值位于一个给定的连续区间内之内。

> PARTITION BY RANGE (id) (  
> 
>          PARTITION p0 VALUES LESS THAN (3),  
> 
>          PARTITION p1 VALUES LESS THAN (6),  
> 
>          PARTITION p2 VALUES LESS THAN (9),  
> 
>          PARTITION p3 VALUES LESS THAN (12),  
> 
>          PARTITION p4 VALUES LESS THAN MAXVALUE  
> 
> );

**（2）List分区：**

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

> PARTITION BY LIST (province\_id) (  
> 
>          PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
> 
>          PARTITION p1 VALUES IN (9,10,11,12,16,21),  
> 
>          PARTITION p2 VALUES IN (13,14,15,19),  
> 
>          PARTITION p3 VALUES IN (17,18,20,22,23,24)  
> 
>  );

** （3）Hash分区：**

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。   
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。

> create table foo\_hash  
> (empno varchar(20) not null ,  
> empname varchar(20),  
> deptno int,  
> birthdate date not null,  
> salary int  
> )  
> partition by hash(year(birthdate))  
> partitions 4;

以上创建了4个分区。

**（4）Key分区：**

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

> create table foo\_key
> 
> (empno varchar(20) not null ,
> 
> empname varchar(20),
> 
> deptno int,
> 
> birthdate date not null,
> 
> salary int
> 
> )
> 
> partition by key(birthdate)
> 
> partitions 4;

**（5）复合分区：**

子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

> PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
> 
>      PARTITION p0 VALUES LESS THAN (3000000)
> 
>      DATA DIRECTORY = '/data0/data'
> 
>      INDEX DIRECTORY = '/data1/idx',
> 
>      PARTITION p1 VALUES LESS THAN (6000000)
> 
>      DATA DIRECTORY = '/data2/data'
> 
>      INDEX DIRECTORY = '/data3/idx'
> 
> );

以上例子，对 RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

## 二、常见分区操作： ##

![这里写图片描述][20161021204232167]

[20161021204232167]: /images/20220402/b9b76964929c43cf8607ebc30cce61bc.png