MySQL数据库：分区Partition_mysql partition

一、分区：

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

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

（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 类型。

 

二、常见分区操作：