分类： Mysql/postgreSQL
2008-05-11 22:33:15
MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
注释：在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。
不像按照RANGE定义分区的情形，LIST分区不必声明任何特定的顺序。关于LIST分区更详细的语法信息，请参考13.1.5节，“CREATE TABLE语法” 。
对于下面给出的例子，我们假定将要被分区的表的基本定义是通过下面的“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)；
（这和18.2.1节，“RANGE分区” 中的例子中使用的是同一个表）。
假定有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))；
这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。
要点：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“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”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。 关于这方面的讨论，请参考18.2.5节，“子分区”。
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；
如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 例外： 对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。（参见第17章：MySQL簇）。
如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。
“expr”还可以是一个返回一个整数的SQL表达式。例如，也许你想基于雇用雇员的年份来进行分区。这可以通过下面的语句来实现：
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(YEAR(hired))PARTITIONS 4；
“expr”还可以是MySQL 中有效的任何函数或其他表达式，只要它们返回一个既非常数、也非随机数的整数。（换句话说，它既是变化的但又是确定的）。但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。
最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。
例如，“date_col” 是一个DATE（日期）类型的列，那么表达式TO_DAYS(date_col)就可以说是随列“date_col”值的变化而发生直接的变化，因为列“date_col”值的每个变化，表达式的值也将发生与之一致的变化。而表达式YEAR(date_col)的变化就没有表达式TO_DAYS(date_col)那么直接，因为不是列“date_col”每次可能的改变都能使表达式YEAR(date_col)发生同等的改变。即便如此，表达式YEAR(date_col)也还是一个用于 哈希函数的、好的候选表达式，因为它随列date_col的一部分发生直接变化，并且列date_col的变化不可能引起表达式YEAR(date_col)不成比例的变化。
作为对照，假定有一个类型为整型（INT）的、列名为“int_col”的列。现在考虑表达式“POW(5-int_col,3) + 6”。这对于哈希函数就是一个不好的选择，因为“int_col”值的变化并不能保证表达式产生成比例的变化。列 “int_col”的值发生一个给定数目的变化，可能会引起表达式的值产生一个很大不同的变化。例如，把列“int_col”的值从5变为6，表达式的值将产生“－1”的改变，但是把列“int_col”的值从6变为7时，表达式的值将产生“－7”的变化。
换句话说，如果列值与表达式值之比的曲线图越接近由等式“y=nx（其中n为非零的常数）描绘出的直线，则该表达式越适合于 哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。
理论上讲，对于涉及到多列的表达式，“修剪（pruning）”也是可能的，但是要确定哪些适于 哈希是非常困难和耗时的。基于这个原因，实际上不推荐使用涉及到多列的哈希表达式。
当使用了“PARTITION BY HASH”时，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N ，其中“N = MOD(expr, num)”。例如，假定表t1 定义如下，它有4个分区：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4；
如果插入一个col3列值为'2005-09-15'的记录到表t1中，那么保存该条记录的分区确定如下：
MOD(YEAR('2005-09-01'),4)= MOD(2005,4)= 1
MySQL 5.1 还支持一个被称为“linear hashing（线性哈希功能）”的变量，它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。关于这种算法的描述，请参见18.2.3.1节，“LINEAR HASH分区” 。
每当插入或更新一条记录，用户函数都要计算一次。当删除记录时，用户函数也可能要进行计算，这取决于所处的环境。
注释：如果将要分区的表有一个唯一的键，那么用来作为HASH用户函数的自变数或者主键的column_list的自变数的任意列都必须是那个键的一部分。
MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下面所示：
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 LINEAR HASH(YEAR(hired))PARTITIONS 4；
假设一个表达式expr, 当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到：
1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下面的公式得到：
2. V = POWER(2, CEILING(LOG(2, num)))
（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。
3. 设置 N = F(column_list) & (V - 1).
4. 当 N >= num:
· 设置 V = CEIL(V / 2)
· 设置 N = N & (V - 1)
例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6;
现在假设要插入两行记录到表t1中，其中一条记录col3列值为'2003-04-14'，另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下：
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7 = 3 (3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下：
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7 = 6 (6 >= 4 为真（TRUE）: 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
= 6 & 3 = 2 (2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。
“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。
通过线性KEY分割一个表也是可能的。下面是一个简单的例子：
CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY (col1)PARTITIONS 3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。关于该算法及其蕴涵式的描述请参考 18.2.3.1节，“LINEAR HASH分区” 。
子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE )；
表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，这些分区的头2个只保存“purchased”列中值小于1990的那些记录。
在MySQL 5.1中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning）。
为了对个别的子分区指定选项，使用SUBPARTITION 子句来明确定义子分区也是可能的。例如，创建在前面例子中给出的同一个表的、一个更加详细的方式如下：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );
几点要注意的语法项：
· 每个分区必须有相同数量的子分区。
· 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。换句话说，下面的语句将执行失败：
· CREATE TABLE ts (id INT, purchased DATE)
· PARTITION BY RANGE(YEAR(purchased))
· SUBPARTITION BY HASH(TO_DAYS(purchased))
· (
· PARTITION p0 VALUES LESS THAN (1990)
· (
· SUBPARTITION s0,
· SUBPARTITION s1
· ),
· PARTITION p1 VALUES LESS THAN (2000),
· PARTITION p2 VALUES LESS THAN MAXVALUE
· (
· SUBPARTITION s2,
· SUBPARTITION s3
· )
· )；
即便这个语句包含了一个SUBPARTITIONS 2子句，但是它仍然会执行失败。
· 每个SUBPARTITION 子句必须包括 (至少)子分区的一个名字。否则，你可能要对该子分区设置任何你所需要的选项，或者允许该子分区对那些选项采用其默认的设置。
· 在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一。例如，下面的CREATE TABLE 语句是有效的：
· CREATE TABLE ts (id INT, purchased DATE)
· PARTITION BY RANGE(YEAR(purchased))
· SUBPARTITION BY HASH(TO_DAYS(purchased))
· (
· PARTITION p0 VALUES LESS THAN (1990)
· (
· SUBPARTITION s0,
· SUBPARTITION s1
· ),
· PARTITION p1 VALUES LESS THAN (2000)
· (
· SUBPARTITION s0,
· SUBPARTITION s1
· ),
· PARTITION p2 VALUES LESS THAN MAXVALUE
· (
· SUBPARTITION s0,
· SUBPARTITION s1
· )
· )；
子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0， /disk1， /disk2等。现在考虑下面的例子：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s0 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s0 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ) )；
在这个例子中，每个RANGE分区的数据和索引都使用一个单独的磁盘。还可能有许多其他的变化；下面是另外一个可能的例子：
CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(TO_DAYS(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0a DATA DIRECTORY = '/disk0' INDEX DIRECTORY = '/disk1', SUBPARTITION s0b DATA DIRECTORY = '/disk2' INDEX DIRECTORY = '/disk3' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s1a DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s1b DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2a, SUBPARTITION s2b ) )；
在这个例子中，存储的分配如下：
· 购买日期在1990年前的记录占了大量的存储空间，所以把它分为了四个部分进行存储，组成p0分区的两个子分区（s0a 和s0b）的数据和索引都分别用一个单独的磁盘进行存储。换句话说：
o 子分区s0a 的数据保存在磁盘/disk0中。
o 子分区s0a 的索引保存在磁盘/disk1中。
o 子分区s0b 的数据保存在磁盘/disk2中。
o 子分区s0b 的索引保存在磁盘/disk3中。
· 保存购买日期从1990年到1999年间的记录（分区p1）不需要保存购买日期在1990年之前的记录那么大的存储空间。这些记录分在2个磁盘（/disk4和/disk5）上保存，而不是4个磁盘：
o 属于分区p1的第一个子分区（s1a）的数据和索引保存在磁盘/disk4上 — 其中数据保存在路径/disk4/data下，索引保存在/disk4/idx下。
o 属于分区p1的第二个子分区（s1b）的数据和索引保存在磁盘/disk5上 — 其中数据保存在路径/disk5/data下，索引保存在/disk5/idx下。
· 保存购买日期从2000年到现在的记录（分区p2）不需要前面两个RANGE分区那么大的空间。当前，在默认的位置能够足够保存所有这些记录。
将来，如果从2000年开始后十年购买的数量已经达到了默认的位置不能够提供足够的保存空间时，相应的记录（行）可以通过使用“ALTER TABLE ... REORGANIZE PARTITION”语句移动到其他的位置。关于如何实现的说明，请参见18.3节，“分区管理” 。