MySQL分区表详解（附带实例）

从 MySQL 5.1 版本开始支持数据表分区，通俗地讲，表分区是将一张大表根据条件分割成若干小表。例如，某用户表的记录超过了 600 万条，那么就可以根据入库日期或者所在地将表分区。

分区表通过一些特殊的语句创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表。分区看上去像一个单独的表。

数据库分区是一种物理数据库设计技术，分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲说分区完全是透明的。

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

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

下面介绍 MySQL 各种分区表常用的操作案例。

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 分区很有用，常常使用在以下几种情况：

• 如果要删除某个时间段的数据时，只需要删除分区即可。例如，要删除 1980 年以前出生员工的所有信息，此时执行“alter table emp drop partition p0”的效率要比执行“delete from emp where year(birthdate)<=1980”高效得多。
• 如果使用包含日期或者时间的列，可以考虑用到 RANGE 分区。
• 经常运行直接依赖于分割表的列的查询。比如，当执行某个查询，如“select count（*） from emp where year(birthdate) = 1999 group by empno”，此时 MySQL 数据库可以很迅速地确定只有分区 p2 需要扫描，这是因为查询条件对于其他分区不符合。

MySQL LIST分区

LIST 分区类似 RANGE 分区，它们的区别主要在于，LIST 分区中每个分区的定义和选择是基于某列的值从属于一个集合，而 RANGE 分区是从属于一个连续区间值的集合。

下面通过示例演示创建 RANGE 分区，命令如下：

mysql> create table employees(
     empname varchar(20),
     deptno int,
     birthdate date not null,
     salary int
     )
     partition by list(deptno)
     (
     partition p1 values in (10,20),
     partition p2 values in (30),
     partition p3 values in (40)
     );
Query OK, 0 rows affected (0.01 sec)

以上示例以部门编号划分分区，10 号部门和 20 号部门的员工信息存储在 p1 分区，30 号部门的员工信息存储在 p2 分区，40 号部门的员工信息存储在 p3 分区。

同 RANG 分区一样，如果插入数据的部门编号不在分区值列表中时，那么 INSERT 插入操作将失败并报错。

MySQL HASH分区

HASH 分区是基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。

HASH 分区主要用来确保数据在预先确定数目的分区中平均分布。在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在 HASH 分区中，MySQL 自动完成这些工作，用户所要做的只是为将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

看下面的例子：

mysql> create table htable(
     id int,
     name varchar(20),
     birthdate date not null,
     salary int
     )
     partition by hash(year(birthdate))
     partitions 4;
Query OK, 0 rows affected (0.00 sec)

当使用了“partition by hash”时，MySQL 将基于用户函数结果的模数，来确定使用哪个编号的分区。将要保存记录的分区编号为 N=MOD(表达式, num)。如果表 htable 中插入一条 birthdate 为“2010-09-23”的记录，可以通过如下方法计算该记录的分区：

mod(year(’2010-09-23’),4)=mode(2010,4) =2

此时，该条记录的数据将会存储在分区编号为 2 的分区空间。

MySQL线性HASH分区

线性 HASH 分区和 HASH 分区的区别在于，线性哈希功能使用的一个线性的 2 的幂运算法则，而 HASH 分区使用的是哈希函数的模数。

先看下面的例子：

mysql> create table htable2(
     id int not null,
     name varchar(20),
     hired date not null default '1999-09-09',
     deptno int
     )
     partition by linear hash(year(hired))
     partitions 4;
Query OK, 0 rows affected (0.03 sec)

如果表 htable2 中插入一条 hired 为“2010-09-23”的记录，记录将要保存到的分区是 num 个分区中的分区 N。下列步骤通过哈希函数计算 N。

1) 找到下一个大于 num 的 2 的幂，把这个值称作 V，可以通过下面的公式得到：

V=POWR(2,CEILING(LOG(2,num)))

假设 num 的值是 13，那么 LOG(2,13) 就是 3.70043。CEILING(3.70043) 就是 4，则 V=POWER(2,4)，即等于 16。

2) 计算 N=F(column_list) & (V – 1) 此时当 N>=num 时，V=CEIL(V/2)，此时 N=N & (V-1)。

下面示例通过线性哈希分区算法来计算分区N的值。线性哈希分区表 t1 通过下面的语句创建：

mysql> create table th1(
     col1 int,
     col2 char(5),
     col3 date
     )
     partition by linear hash( year(col3) )
     partitions 6;
Query OK, 0 rows affected (0.59 sec)

现在假设要插入两条记录到表 th1 中，其中一条记录 col3 列的值为“2003-04-14”，另一条记录 cols 列值为“1998-10-19”。第一条记录要保存到的分区计算过程如下：

记录将要保存到 num 分区中的分区 N。假设 num 是 7 个分区，表 t1 使用线性 HASH 分区且有 4 个分区。

V = POWR(2,CEILING(LOG(2,num)))
V = POWR(2,CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
  = 2003 & 7
  = 3

N 的值是 3，很显然 3>=4 不成立，所以附件条件不执行，第一条记录的信息将存储在 3 号分区中。

第二条记录将要保存的分区序号计算如下：

V = POWR(2,CEILING(LOG(2,num)))
V = POWR(2,CEILING(LOG(2,7))) = 8
  = YEAR('1998-10-19') & (8 - 1)
  = 1998 & 7
  = 6

N 的值是 6，很显然 6>=4 成立，所以附件条件会执行。

V = CEIL(6/2) = 3
N = N & (V-1)
  = 6 & 2
  = 2

此时发现 2 >= 4 不成立，记录将被保存到 #2 分区中。

线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000G）数据的表。它的缺点在于，与使用常规 HASH 分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。

MySQL KEY分区

类似于 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数，这些函数是基于与 PASSWORD() 一样的运算法则。

看下面的例子：

mysql> create table keytable(
     id int,
     name varchar(20) not null,
     deptno int,
     birthdate date not null,
     salary int
     )
     partition by key(birthdate)
     partitions 4;
Query OK, 0 rows affected (0.11 sec)

在 KEY 分区中使用关键字 LINEAR 和在 HASH 分区中使用具有同样的作用，分区的编号是通过 2 的幂算法得到，而不是通过模数算法。

MySQL复合分区

复合分区是分区表中每个分区的再次分割，子分区既可以使用 HASH 分区，也可以使用 KEY 分区。这也被称为子分区。

复合分区需要注意以下问题：

• 如果一个分区中创建了复合分区，则其他分区也要有复合分区。
• 如果创建了复合分区，则每个分区中的复合分区数必有相同。
• 同一分区内的复合分区，名字不相同，不同分区内的复合分区名字可以相同。

下面通过示例演示不同的复合分区的创建方法。

1) RANGE-HASH复合分区

创建 RANGE-HASH 复合分区的命令如下：

mysql> create table rhtable(
     no varchar(20) not null,
     name varchar(20),
     deptno int,
     birthdate date not null,
     salary int
     )
     partition by range(salary)
     subpartition by hash(year(birthdate))
     subpartitions 3
     (
     partition p1 values less than (2000),
     partition p2 values less than maxvalue
     );
Query OK, 0 rows affected (0.23 sec)

2) RANGE-KEY复合分区

创建 RANGE-KEY 复合分区的命令如下：

mysql> create table rktable(
     no varchar(20) not null,
     name varchar(20),
     deptno int,
     birth date not null,
     salary int
     )
     partition by range(salary)
     subpartition by key(birth)
     subpartitions 3
     (
     partition p1 values less than (2000),
     partition p2 values less than maxvalue
     );
Query OK, 0 rows affected (0.07 sec)

3) LIST-HASH复合分区

创建 LIST-HASH 复合分区的命令如下：

mysql> create table lhtable(
     no varchar(20) not null,
     name varchar(20),
     deptno int,
     birth date not null,
     salary int
     )
     partition by list(deptno)
     subpartition by hash( year(birth) )
     subpartitions 3
     (
     partition p1 values in (10),
     partition p2 values in (20)
     );
Query OK, 0 rows affected (0.08 sec)

4) LIST-KEY复合分区

创建 LIST-KEY 复合分区的命令如下：

mysql> create table lktable(
     no varchar(20) not null,
     name varchar(20),
     deptno int,
     birthdate date not null,
     salary int
     )
     partition by list(deptno)
     subpartition by key(birthdate)
     subpartitions 3
     (
     partition p1 values in (10),
     partition p2 values in (20)
     );
Query OK, 0 rows affected (0.09 sec)