详细介绍mysql中的分区

概述

 之前，看到分区，我捏个去，好高大上哟。昨天终于知道了分区是个啥玩意，也不过如此，今天总结一下，好记性不如烂笔头嘛。
MySQL从5.1开始支持分区功能。分区一句话就是：把一张表按照某种规则（range/list/hash/key等）分成多个区域（页/文件）保存。对mysql应用开发来说，分区与不分区是没区别的（即对应用是透明的）。如同突围战中的“化整为零”。MySQL支持大部分的存储引擎（如：MyISAM、InnoDB、Memory等）创建分区，不支持MERGE和CSV来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。做一个引例：

#创建一个5个hash分区的myisam表
CREATE TABLE `test`.`partition_t1`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY HASH(MONTH(birth_date))
PARTITIONS 5;

引例结果

分区作用

• 可以存储更多的数据（系统单个文件最大限制）

• 优化查询，在where子句中，如果包含分区条件，只需要扫描一个或部分分区来提高查询效率。在涉及sum()这类函数时候， 可以在分区上并行处理，最后汇总结果。

• 对于过期或不需要的数据，可以删除相关分区来快速删除数据。

• 跨多个磁盘来分散数据查询，单表的并发能力提高了，磁盘I/O性能也提高了。

分区类型

分为4种：

• range分区：基于一个给定的连续区间范围，把数据分配到不同的分区中。

• list分区：类似range分区，区别在于list是基于枚举出的值列表分区，range是根据范围来分区的。

• hash分区：基于给定的分区个数，把数据分配到不同分区（取模/线性）

• key分区：类似于hash分区。

MySQL5.1中range,list,hash分区要求分区键必须是int。MySQL5.5及以上，支持非整型的range和list分区，即：range columns 和 list columns。
注意：无论哪种分区，要么分区表上没有主键/唯一键，要么分区键必须有一个是主键/唯一键。

1.range分区

range分区是利用取值范围（区间）划分分区，区间要连续并且不能互相重叠，使用values less than操作符进行分区定义。

例一：

CREATE TABLE `test`.`partition_t2`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE(id)(
   PARTITION t21 VALUES LESS THAN (10),
   PARTITION t22 VALUES LESS THAN (20),
   PARTITION t23 VALUES LESS THAN MAXVALUE
);

上例中定义了一个包含3个分区（t21,t22,t23）的range分区表，这个有点类似与高级语言中的switch语句。解释如下：当id<10的时候，在t21分区；当20>id>=10的时候，在t22分区；当id>=20时候，在t23分区。

例二：

CREATE TABLE `test`.`partition_t3`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY RANGE COLUMNS(birth_date)(
   PARTITION t31 VALUES LESS THAN ('1996-01-01'),
   PARTITION t32 VALUES LESS THAN ('2006-01-01'),
   PARTITION t33 VALUES LESS THAN ('2038-01-01')
);

MySQL5.5改进range分区，提供range columns分区支持非整数分区。

2.list分区

list分区创建离散的值列表（类似mysql中的enum类型数据）来划分分区，使用values in操作符来分区。list分区不必要声明任何特定的顺序的。list有很多方面类似于range。

CREATE TABLE `test`.`partition_t4`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LIST(id)(
   PARTITION t41 VALUES IN (1,2),
   PARTITION t42 VALUES IN (3,6),
   PARTITION t43 VALUES IN (5,4),
   PARTITION t44 VALUES IN (7,8)
);

上面的例子是，当id为1或2，在t41分区；当id为3或6，在t42分区，以此类推...

3.hash分区

hash分区主要用来分散热点读取，确保数据在预定确定个数分区中尽可能的平均分布。一个表执行hash分区，mysql会对分区键应用一个散列函数，以此确定数据应该放在n个分区中的哪一个分区。hash分区支持两种散列函数（分区方式）：取模算法（默认hash分区方式）和线性的2的幂的运算法则（liner hash 分区）。

常规hash分区

#顶部引例就是常规hash分区

• mysql不推荐使用涉及多列的hash表达式。

• 常规hash在分区管理上带来的代价太大了，不适合灵活变动的分区的需求。参见：一致性哈希算法

• 因为常规hash分区在管理上的问题，所有mysql引入线性hash分区。

  线性hash分区

  CREATE TABLE `test`.`partition_t5`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
  ) ENGINE=MYISAM
  PARTITION BY LINEAR HASH(id)
  PARTITIONS 5;

  上例中，创建一个5个分区的线性hash分区。

• 线性hash分区优点：在分区维护上，mysql能够处理更加迅速；

• 线性hash分区缺点：分区各个分区之间数据分布不太均衡。

4.key分区

• hash分区允许用户自定义的表达式，而key分区不允许使用用户自定义的表达式。

• hash分区只支持整数分区，key分区支持除了blob或text类型之外的其他数据类型分区。

• 与hash分区不同，创建key分区表的时候，可以不指定分区键，默认会选择使用主键/唯一键作为分区键，没有主键/唯一键,必须指定分区键。

CREATE TABLE `test`.`partition_t6`(  
  `id` INT UNSIGNED NOT NULL,
  `username` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `birth_date` DATE NOT NULL
) ENGINE=MYISAM
PARTITION BY LINEAR KEY(email)
PARTITIONS 5;

columns 与子分区

1.columns分区

columns 包括range columns与list columns 支持非整型的分区键。columns分区支持多列分区。

CREATE TABLE `test`.`partition_t7`(  
  `a` INT UNSIGNED NOT NULL,
  `b` INT UNSIGNED NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,10),
    PARTITION p2 VALUES LESS THAN (10,20),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

判断依据：（a<10） or ((1=10) and (10<10))。

2.子分区

子分区是分区表中对每一个分区的再次分割，又被称为复合分区。MySQL从MySQL5.1开始支持对通过range和list的表再进行子分区，子分区即可以hash分区，也可以使用key分区。子分区适合保存非常大量的数据记录。

CREATE TABLE partition_t8(id INT,purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(TO_DAYS(purchased))
SUBPARTITIONS 2(
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

mysql子分区

分区管理

MySQL5.1提供添加、删除、重定义、合并、拆分分区命令。

1.range或list分区

#删除分区
alter table partition_t8 drop partition p2;
#添加一个分区
alter table partition_t8 add partition(
    partition p4 values less than (2030)
    )
#重定义一个分区
alter table partition_t8 reorganize partition p3 into(
    partition p2 values less than (2005),
    partition p3 values less than (2015)
);

• 只能从range分区列表最大端增加分区。

• 增加list分区，不能添加一个包含现有分区值列表中的任意值分区，也就是说对一个固定的分区键值，必须指定并且只能指定一个唯一的分区。

• 重新定义range分区，只能够重新定义相邻的分区，同时重新定义的分区区间必须和原分区区间覆盖相同的区间。

2.hash或key分区

#减少分区数,(如将分区数减少到2)
alter table partition_t8 coalesce partition 2;
#增加分区数(如：为分区数增加了8)
alter table partiton_t8 add partition partitions 8;

• coalesce不能用来增加分区数量。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

以上就是详细介绍mysql中的分区的详细内容，更多请关注其它相关文章！