MySQL 表分区专题二
[ 整理 :hkebao@126.com 整理时间 :2010-1-21 ]
MySQL 5.1 的二进制版本目前还不可用；但是，可以从 BitKeeper 知识库中获得源码。要激活分区，需要使用 --with- 分区选项编译服务器。
一、 分区概述
分区 又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统 分配单个表的多个部分。
实际上，表的不同部分在不同的位置被存储为
单独的表。（相当于是一个分表）
用户所选择的、实现数据分割的规则被称为分区函数
这在 MySQL 中它可以是模数，或者是简单的匹配
一个连续的数值区间或数值列表，或者是一个内部 HASH 函数，或一个线性 HASH 函 数。函数根据
用户指定的分区类型 来选择，把用户提供的表达式的值作为参数。该表达式可以是
一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函
数。这个表达式的值传递给分
区函数 ，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号 。
这个函数不能是常数，也不能是任意数。它不能包含任何查 询，但是实际上
可以使用 MySQL 中任何可用的 SQL 表达式，只要该表达式返回一个小于 MAXVALUE （最大可能的正整数）的正数值。
当二进制码变成可用时（也就是说，5.1 -max 二进制码将通过--with-partition 建立），分区支持就将包含在MySQL 5.1 的-max 版本中。如果MySQL 二进制码是使用分区
支持建立的，那么激活它不需要任何其他的东西 ( 例如，在my.cnf
文件中，不需要特殊的条目) 。可以通过使用SHOW
VARIABLES 命令来确定MySQL 是否支持分区，例如：
mysql> SHOW VARIABLES LIKE '%partition%';
（即只需要在编译二进制的时候指定好此选项
就行）
对于创建了分区的表，可以使用你的 MySQL 服务器所支持的任何存储引擎； MySQL
分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在 MySQL 5.1 版中，同一个分区表的所
有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM ，而对另一个使用 InnoDB 。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。
（同一个表不同分区其引擎必须要一样，同一
个数据库不同的分区表引擎可以不一样）
下面的例子给出了怎样创建一个通过HASH 分成6 个
分区、使用InnoDB 存储引擎的表：
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH(MONTH(tr_date)) PARTITIONS 6; （注释：每个 PARTITION
子句可以包含一个
[STORAGE] ENGINE 选项，但是在 MySQL
5.1 版本中，这没有作用）。
注释 ：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分
区，反之亦然，同时也不能只对表的一部分进行分区。
可以通过使用用来创建分区表的 CREATE
TABLE 语句的 PARTITION 子句的 DATA
DIRECTORY （数据路径）和 INDEX DIRECTORY （索引路径）选项 ，为每个分区的数据和索引指定特定的路径。
分区的一些优点包括：
· 与单个磁盘或文件系统分区相比，可以存储更多的数据。
对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分
区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
一些查询可以得到极大的优化，这主要是借助
于满足一个给定 WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分
区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
通过跨多个磁盘来分散数据查询，来获得更大
的查询吞吐量。
可用的分区类型。这些类型包括：
· RANGE 分区 18.2.1节，“RANGE 分
区”
· LIST 分
区 RANGE 分区，区别在于LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，“ LIST 分区”
· HASH 分区 ：基于用户定义的表达式的返
回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中
有效的、产生非负整数值的任何表达式。参见18.2.3节，“HASH 分
区”
· KEY 分
区 HASH 分区，区别在于KEY 分区只支持计算一列或多列，且MySQL 服务器
提供其自身的哈希函数。必须有一列或多列包含整数值。参见18.2.4节，“KEY 分
区”
无论使用何种类型的分区，分区总是在创建时
就自动的顺序编号，且从 0 开始记录，记住这一点非常重要。
当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4 个
分区，那么这些分区就编号为0, 1, 2, 和3 。
对于RANGE 和LIST 分区类型，确认每
个分区编号都定义了一个分区，很有必要。对HASH 分区，使用的用户函数必须返回一个大于0 的整数值。对于KEY 分区，这个问题通过MySQL 服务器内部使用的
哈希函数自动进行处理。
分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识
符。但是应当注意，分区的名字是不区分大小写的。
1、
RANGE 分区
每个分区包含那些分区表达式的值位于一个给
定的连续区间内的行。这些区间要连续且不能相互重叠，使用 VALUES LESS THAN 操作符来进行定义。
在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20 家音像店的职员记录，这20 家音像店的编号从1 到20 。
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL ) ；根据你的需要，这个表可以有多种方式来按照区间进行分区。一种方式是使用store_id 列。
例如，你可能决定通过添加一个PARTITION BY RANGE 子句把这个表分割成4 个区间，如下所示:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ) ；按照这种分区方案，在商店1 到5 工作
的雇员相对应的所有行被保存在分区P0 中，商店6 到10 的雇员保存在P1 中，依次类推。注意，每个分区都
是按顺序进行定义，从最低到最高。这是PARTITION
BY RANGE 语法的要求；在这点上，它类似于C 或Java 中的“switch ... case” 语句。
对于包含数据(72, 'Michael', 'Widenius', '1998-06-25 ',
NULL, 13) 的一个新行，可以很容易地确定它将插入到p2 分区中，但是如果增
加了一个编号为第21 的商店，将会发生什么呢？在这种方案下，由于没有规则把store_id 大于20 的商店包含在内，服务器将不知
道把该行保存在何处，将会导致错误。 要避免这种错误，可以通过在CREATE TABLE 语句中
使用一个“catchall ”
VALUES LESS THAN 子句，该子句提供给所有大于明确指定的最高值的值：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE ) ；MAXVALUE 表示最大的可能的整数值。现在，store_id
列值大于或等于16 （定义了的最高值）的所有行都将保存在分区p3 中。在将来的某个时候，当商店数已经增长到25, 30, 或
更多 ，可以使用ALTER TABLE 语句为商店21-25,
26-30, 等等增加新的分区
（按照什么来划分分区的）
以下的处理方式就比较好用！
除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的
表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated) 的值 （直
接使用函数进行分表处理）。实现这种分区模式的CREATE TABLE 语句的一个例子如下所
示：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT, store_id INT ) PARTITION BY RANGE (YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE ) ；在这个方案中，在1991 年前雇佣的所有雇员的记录保存在分区p0 中，1991 年到1995 年期间雇佣的所有雇员的记录保存在分区p1 中， 1996 年到2000 年期间雇佣的所有雇员的记录保
存在分区p2 中，2000 年后雇佣的所有工
人的信息保存在p3 中。
RANGE 分区在如下场合特别有用：
· 当需要删除“ 旧
的” 数据时。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用
“ALTER TABLE employees
DROP
PARTITION p0 ；” 来删除所有在1991 年前就已经停止工作的雇员相对应的所有行。
（直接将一个分区给干掉）
对于有大量行的表，这比运行一个如 “DELETE FROM employees WHERE YEAR(separated) <= 1990 ； ” 这样的一个 DELETE 查询要有效得多。
想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
· 经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) =
2000 GROUP
BY store_id ；” 这样的查询时，MySQL 可
以很迅速地确定只有分区p2 需要扫描，这是因为余下的分区不可能包含有符合该WHERE 子句的任何记录。注释 ：这种优化还没有在MySQL
5.1 源程序中启用，但是，有关工作正在进行中。
（依据条件直接定位 如果说条件不是我们的分区条件呢？）
2、
LIST 分区
MySQL 中的 LIST 分区在很多方面类似于 RANGE 分区。和按照 RANGE 分区一样，每个分区必须明确定义。它们的主要区别在于， LIST 分区中每个分区的定义和选择是基于某
列的值从属于一个值列表集中的一个值，而 RANGE 分区是从属于一个连续区间值 的
集合。 LIST 分
区通过使用 “PARTITION
BY LIST(expr )” 来实现，其中 “expr” 是某列值或一
个基于某个列值、并返回一个整数值的表达式，然后通过 “VALUES IN (value_list )” 的方式来定义每个分区，其中 “value_list ” 是一个通过逗号分隔的整数列表。
对于下面给出的例子，我们假定将要被分区的表的基本定义是通过下面的“CREATE TABLE” 语
句提供的：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT, store_id INT ) ；假定有20 个
音像店，分布在4 个有经销权的地区，如下表所示：
地区
商店ID 号
北区
3, 5, 6, 9, 17
东区
1, 2, 10, 11, 19,
20
西区
4, 12, 13, 14, 18
中心区
7, 8, 15, 16
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割
表，可以使用下面的“CREATE TABLE” 语句：
CREATE TABLE
employees (
id INT NOT
NULL,
fname
VARCHAR(30),
lname
VARCHAR(30),
hired DATE
NOT NULL DEFAULT '1970-01-01 ',
separated
DATE NOT NULL DEFAULT '9999-12-31 ',
job_code INT,
store_id INT
)
PARTITION BY
LIST(store_id)
PARTITION
pNorth VALUES IN
(3,5,6,9,17),
PARTITION
pEast VALUES IN
(1,2,10,11,19,20),
PARTITION
pWest VALUES IN
(4,12,13,14,18),
PARTITION
pCentral VALUES IN
(7,8,15,16)
) ；
（即一个值是位于一个集合 LIST 内的）
要点 “INSERT” 查询将失败并报错。例如，假定LIST 分区的采用上面的方案，下面的查询将失败：
INSERT INTO employees VALUES (224, 'Linus', 'Torvalds', '2002-05-01 ', '2004-10-12 ', 42, 21); 这是因为“store_id” 列值21 不
能在用于定义分区pNorth, pEast, pWest, 或pCentral 的值列表中找到。要重点注意的是，LIST 分
区没有类似如“VALUES LESS THAN MAXVALUE” 这样的包含其他值在内的定
义。将要匹配的任何值都必须在值列表中找到。
3、
HASH 分区
HASH 分区主要用来确保数据在预先确定数目的分区中平均分布。在 RANGE 和 LIST 分区中，必须明确指定一个给定的列值
或列值集合应该保存在哪个分区中；而在 HASH 分区中， MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式， 以及指定被分区的表将要被分割成的分区数量。
要使用 HASH 分区来分割一个表 ，要在 CREATE TABLE 语句上添加一个 “PARTITION BY HASH (expr )” 子句，其中 “expr ” 是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL 整型的一列的名字。此外，你很可
能需要在后面再添加一个 “PARTITIONS
num ” 子句，其中 num 是一个非负的整数，它表示表将要被分割成分区的数量。
下面的语句创建了一个使用基于 “store_id” 列进行
哈希处理的表，该表被分成了 4 个分区：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01 ', separated DATE NOT NULL DEFAULT '9999-12-31 ', job_code INT, store_id INT ) PARTITION BY HASH(store_id) PARTITIONS 4 ； 