MySQL表分区专题二
[整理:[email protected] 整理时间: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；
