MySQL RANGE分区的创建和使用（附带实例）

MySQL 5.1 及高版本支持分区表（Partitioned Table），分区表的使用大大提升了 MySQL 执行效率。

表分区是将一个大表根据特定条件分割成若干小表，每个分区包含一部分数据。这种方法特别适用于处理大数据量的场景，例如记录数超过 600万的用户表，可以根据入库日期或所在地进行分区。

分区表是一种特殊的表结构，通过一些特殊的语句创建独立的存储空间，实际上每个分区都是有索引的独立表。这种设计使得分区看上去像一张单独的表，但在物理存储上却进行了优化。分区表的主要目的是通过物理数据库设计技术，减少特定查询操作的响应时间，同时对应用来说分区是完全透明的。

MySQL 的分区主要有两种形式：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）：

水平分区是根据表的行进行分割，这种形式的分区一定是将表的某个属性作为分割的条件。例如，将某张表里面的日期为 2011 年的数据和日期为 2012 年的数据分割开，就可以采用这种分区形式。
垂直分区是通过对表的垂直划分来减少目标表的宽度，是某些特定的列被划分到特定的分区。

下面介绍 MySQL RANGE 分区表的常用操作案例。RANGE 分区使用 values less than 操作符来进行定义，把连续且不相互重叠的字段分配给分区。

【实例】创建 RANGE 分区，命令如下：

mysql> create table emp(
      empno varchar(20) not null,
      empname varchar(20),
      deptno int,
      birthdate date,
      salary int
      )
      partition by range(salary)
      (
      partition p1 values less than(1000),
      partition p2 values less than(2000),
      partition p3 values less than(3000)
      );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp values(1000,'kobe',12,'1888-08-08',1500);
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp values(1000,'kobe',12,'1888-08-08',3500);
ERROR 1526 (HY000): Table has no partition for value 3500

此时，按照工资级别（字段 salary）进行表分区，partition by range 的语法类似于 switch...case 的语法：

如果 salary 小于 1000，数据存储在 p1 分区；
如果 salary 小于 2000，数据存储在 p2 分区；
如果 salary 小于 3000，数据存储在 p3 分区。

上面插入的第二条数据的工资级别（字段 salary）为 3500，此时没有分区来存储该范围的数据，所以发生了错误。为了解决这种问题，加入“partition p4 values less than maxvalue”语句即可，命令如下：

mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)

mysql> create table emp(
      empno varchar(20) not null,
      empname varchar(20),
      deptno int,
      birthdate date,
      salary int
      )
      partition by range(salary)
      (
      partition p1 values less than(1000),
      partition p2 values less than(2000),
      partition p3 values less than(3000),
      partition p4 values less than maxvalue
      );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp values(1000,'kobe',12,'1888-08-08',1000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(1000,'durant',12,'1888-08-08',3500);
Query OK, 1 row affected (0.00 sec)

maxvalue 表示可能的最大整数值。值得注意的是，values less than 子句中可以只使用一个表达式，不过表达式结果不能为 NULL。下面按照日期进行分区，命令如下：

mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)
mysql> create table emp(
      empno varchar(20) not null,
      empname varchar(20),
      deptno int,
      birthdate date,
      salary int
      )
      partition by range(year(birthdate))(
        partition p0 values less than(1980),
        partition p1 values less than(1990),
        partition p2 values less than(2000),
        partition p3 values less than maxvalue
      );
Query OK, 0 rows affected (0.01 sec)

该方案中，1980 年以前出生的员工的信息存储在 p0 分区中，1990 年以前出生的员工的信息存储在 p1 分区中，2000 年以前出生的员工的信息存储在p2分区中，2000年以后出生的员工的信息存储在 p3 分区中。

RANGE 分区是很有用的，常常用于以下几种情况：
1) 当要删除某个时间段的数据时，只需删除分区即可。例如，要删除 1980 年以前出生的员工的所有信息，此时会执行“alter table emp drop partition p0”要比执行“delete from emp where year(birthdate)<=1980”高效得多。

2) 如果使用包含日期或者时间的列，可以考虑使用 RANGE 分区。

3) 经常运行直接依赖于分割表的列的查询，比如，执行“select count(*) from emp where year(birthdate) = 1999 group by empno”，此时 MySQL 数据库可以很迅速地确定只有分区 p2 需要扫描，因为其他分区不符合查询条件。

MySQL RANGE分区的创建和使用（附带实例）

相关文章