开发篇-MySQL分区（一）

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数十个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

MySQL分区的优点主要包括以下4个方面：

和单个磁盘或者文件系统分区相比，可以存储更多数据。

优化查询：在Where子句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及SUM()和COUNT()这类聚合函数的查询的时候，可以容易的在每个分区上并行处理，最终结果只需要汇总所有分区得到的结果。

对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来容易的删除数据。

跨多个磁盘来分散数据查询，获得更大的查询吞吐量。

注意：在MySQL5.1版本中分区的实现仍然是pre-alpha版本，该版本中分区特性不是特别适用于生产环境，部分本章中描述的一些功能在MySQL5.1版本中没有实现和提供支持，部分在新发布的MySQL5.5中已经实现，本章基于MySQL5.1版本对分区特性进行描述，如果没有特别标注，描述的特性都适用于MySQL5.1版本，MySQL5.1版本不支持的功能或者在MySQL5.5新增加支持的特性将会特别标注，请读者注意。

6.1分区概述

分区有利于管理非常大的表，它采用了“分而治之”的逻辑，分区引入了分区键（partition key）的概念，分区键用于根据某个区间值（或者范围值）、特定值列表、或者HASH函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象。

可以通过使用SHOW VARIABLES命令来确定当前的MySQL是否支持分区，例如：

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

Variable_name Value

+-----------------------+-------+

have_partition_engine YES

+-----------------------+-------+

1 row in set (0.00 sec)

在如上列出的SHOW VARIABLES 命令所产生的输出中，如果没有看到变量have_partition_engine的值为YES，那么MySQL的版本就不支持分区。

注意：使用命令SHOW ENGINES的输出中，不会显示任何有关分区支持的信息，必须使用SHOW VARIABLES 来判断当前版本是否支持分区。使用命令 SHOW PLUGINS 的输出中，能够检查到当前版本是否安装了分区插件。

MySQL支持使用大部分存储引擎（比如MyISAM、INNODB、MEMORY等存储引擎）创建分区表；MySQL不支持使用MERGE或CSV存储引擎来创建分区表，更多的内容可以参考MySQL最新的官方文档。在MySQL 5.1版本中，同一个分区表的所有分区必须使用同一个存储引擎；即同一个表上，不能对一个分区使用MyISAM引擎，对另一个分区使用InnoDB；但是，可以在同一个MySQL服务器中，甚至同一个数据库中，对于不同的分区表使用不同的存储引擎。

和非分区表设置存储引擎一样，分区表设置存储引擎，只能用[STORAGE]ENGINE子句。[STORAGE]ENGINE子句必须列在CREATE TABLE语句中的其他任何分区选项之前。例如，下面的例子给出了创建一个使用InnoDB引擎有6个HASH分区的表：

mysql> CREATE TABLE emp (empid INT, salary DECIMAL(7,2), birth_date DATE)

-> ENGINE=INNODB

-> PARTITION BY HASH( MONTH(birth_date) )

-> PARTITIONS 6;

Query OK, 0 rows affected (0.11 sec)

注意： MySQL的分区适用于一个表的所有数据和索引：不能只对表数据分区而不对索引分区；反过来也是一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL的分区表上创建的索引一定是本地LOCAL索引。

6.2 分区类型

本节主要讨论在MySQL5.1中可用的分区类型，包括：

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

LIST 分区：类似RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区。

HASH 分区：基于给定的分区个数，把数据分配到不同的分区。

KEY 分区：类似于HASH分区

MySQL5.1版本中，RANGE分区、LIST分区、HASH分区都要求分区键必须是INT类型，或者通过表达式返回INT类型，也就说MySQL5.1仅仅支持整数分区，唯一的例外就是分区类型为KEY分区的时候，可以使用其他类型的列（BLOBor TEXT列类型除外）作为分区键。

注意：在MySQL5.5或以上版本中，已经支持非整数的RANGE和LIST分区了，在后面6.2.3 Columns分区章节有详细的例子说明。

无论是哪种MySQL分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包含分区键，也就是说不能使用主键/唯一键字段之外的其他字段分区，例如 emp表的主键为id字段，在尝试通过 store_id 字段分区的时候，MySQL会提示返回失败：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL,

-> PRIMARY KEY (id)

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

去掉主键约束后，创建表就会成功：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

分区的名字基本上遵循MySQL标识符的原则。说到命名，顺便介绍一下MySQL命名中的大小写敏感：在MySQL中，数据库和表对应于数据目录中的目录和文件。所以，操作系统的大小写敏感性决定数据库和表命名的大小写敏感性。这就意味着数据库和表名在Windows中是大小写不敏感的，而在大多数的Unix或Linux系统中是大小写敏感的。但是需要注意的是，分区的名字是不区分大小写的。例如，下面的CREATE TABLE 语句将会产生错误：

mysql> CREATE TABLE t2 (val INT)

-> PARTITION BY LIST(val)(

-> PARTITION mypart VALUES IN (1,3,5),

-> PARTITION MyPart VALUES IN (2,4,6)

-> );

ERROR 1517 (HY000): Duplicate partition name mypart

这是因为MySQL认为分区名字 mypart 和 MyPart 没有区别。

6.2.1Range 分区

按照RANGE分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用VALUES LESS THAN 操作符进行分区定义。

例如雇员表emp 中按商店ID store_id 进行RANGE分区：

mysql> CREATE TABLE emp (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (store_id) (

-> PARTITION p0 VALUES LESS THAN (10),

-> PARTITION p1 VALUES LESS THAN (20),

-> PARTITION p2 VALUES LESS THAN (30)

-> );

Query OK, 0 rows affected (0.05 sec)

按照这种分区方案，在商店1到9工作的雇员相对应的所有行被保存在分区P0中，商店10到19的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；类似JAVA或者C中的“switch case”语句。

这个时候，如果增加了商店ID大于等于30的行，会出现错误，因为没有规则包含了商店ID大于等于30的行，服务器不知道该把记录保存在那里。

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

ERROR 1526 (HY000): Table has no partition for value 50

可以通过设置分区的时候VALUES LESS THAN MAXVALUE子句，该子句提供给所有大于明确指定的最高值的值，MAXVALUE 表示最大的可能的整数值。例如，增加p3分区存储所有商店ID大于等于30的行之后再执行插入语句就没有问题：

mysql> alter table emp add partition (partition p3 VALUES LESS THAN MAXVALUE);

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into emp(id, ename, hired, job, store_id) values ('7934', 'MILLER', '1982-01-23', 'CLERK', 50);

Query OK, 1 row affected (0.04 sec)

MySQL支持在VALUES LESS THAN 子句中使用表达式，比如以日期作为RANGE分区的分区列：

mysql> CREATE TABLE emp_date (

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE (YEAR(separated)) (

-> PARTITION p0 VALUES LESS THAN (1995),

-> PARTITION p1 VALUES LESS THAN (2000),

-> PARTITION p2 VALUES LESS THAN (2005)

-> );

Query OK, 0 rows affected (0.08 sec)

注意： 在RANGE分区中，分区键如果是NULL值会被当做一个最小值来处理，在后续的6.2.7 MySQL分区处理NULL值的方式 小节中有详细的说明。

MySQL 5.1 支持整数列分区，那么对于想在日期或者字符串列上进行分区，就得使用函数进行转换。但是要是查询如果不用函数转换，那么就无法利用RANGE分区特性来提高查询性能。

MySQL 5.5 改进了RANGE分区功能，提供了RANGE COLUMNS分区支持非整数分区，创建日期分区就不需要通过函数进行转换，例如：

mysql> CREATE TABLE emp_date(

-> id INT NOT NULL,

-> ename VARCHAR(30),

-> hired DATE NOT NULL DEFAULT '1970-01-01',

-> separated DATE NOT NULL DEFAULT '9999-12-31',

-> job VARCHAR(30) NOT NULL,

-> store_id INT NOT NULL

-> )

-> PARTITION BY RANGE COLUMNS (separated) (

-> PARTITION p0 VALUES LESS THAN ('1996-01-01'),

-> PARTITION p1 VALUES LESS THAN ('2001-01-01'),

-> PARTITION p2 VALUES LESS THAN ('2006-01-01')

-> );

Query OK, 0 rows affected (0.07 sec)

注意： MySQL 5.1分区日期处理上支持的函数只有两个YEAR() 和 TO_DAYS()。MySQL 5.5 分区日期处理上增加支持函数TO_SECONDS()，把日期转换成秒钟，从能够比按天分区更细化的分区。

RANGE 特别适用于以下情况：

当需要删除过期的数据时，只需要简单的ALTER TABLE emp DROP PARTITION p0 来删除p0分区中数据。对于上百万的记录的表来说，删除分区要比运行一个DELETE语句有效的多。

经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含有符合该WHERE子句的任何记录。例如，检索商店ID大于等于25的记录数，MySQL只需要扫描p2分区即可：

mysql> explain partitions select count(1) from emp where store_id >= 25\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: emp

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5

Extra: Using where

1 row in set (0.00 sec)

6.2.2List 分区

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST分区在很多方面类似于RANGE分区，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。

LIST分区通过使用PARTITION BY LIST(expr) 子句来实现，expr是某列值或一个基于某列值返回一个整数值的表达式，然后通过VALUES IN(value_list) 的方式来定义分区，其中value_list是一个逗号分隔的整数列表。和RANGE分区不同，LIST分区不必声明任何特定的顺序，例如：

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category INT,

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST(category) (

-> PARTITION p0 VALUES IN (3, 5),

-> PARTITION p1 VALUES IN (1, 10),

-> PARTITION p2 VALUES IN (4, 9),

-> PARTITION p3 VALUES IN (2),

-> PARTITION p4 VALUES IN (6)

-> );

Query OK, 0 rows affected (0.09 sec)

注意： MySQL 5.1 中，LIST分区只能匹配整数列表。category 只能是INT类型，所以需要额外的转换表来记录类别编号和类别的名称。

如果试图插入的列值（或者分区表达式的返回值）不包含分区值列表中时，那么INSERT操作会失败并报错。要重点注意，LIST分区不存在类似VALUES LESS THAN MAXVALUE 这样包含其他值在内的定义方式。将要匹配的任何值都必须在值列表中找得到。

MySQL 5.5 中支持非整数分区，创建LIST分区就不需要额外的转换表：

mysql> CREATE TABLE expenses (

-> expense_date DATE NOT NULL,

-> category VARCHAR(30),

-> amount DECIMAL (10,3)

-> )PARTITION BY LIST COLUMNS (category) (

-> PARTITION p0 VALUES IN ( 'lodging', 'food'),

-> PARTITION p1 VALUES IN ( 'flights', 'ground transportation'),

-> PARTITION p2 VALUES IN ( 'leisure', 'customer entertainment'),

-> PARTITION p3 VALUES IN ( 'communications'),

-> PARTITION p4 VALUES IN ( 'fees')

-> );

Query OK, 0 rows affected (0.07 sec)