MySQL 5.1参考手册 :: 18. 分区(3)
时间：2008-05-11 来源：sdccf
18.2.6. MySQL分区处理NULL值的方式
MySQL 中的分区在禁止空值（NULL）上没有进行处理，无论它是一个列值还是一个用户定义表达式的值。一般而言，在这种情况下MySQL 把NULL视为0。如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。
在本节中，我们提供了一些例子，来说明当决定一个行应该保存到哪个分区时，MySQL 是如何处理NULL值的。
如果插入一行到按照RANGE或LIST分区的表，该行用来确定分区的列值为NULL，分区将把该NULL值视为0。例如，考虑下面的两个表，表的创建和插入记录如下：
mysql> CREATE TABLE tnlist (
-> id INT,
-> name VARCHAR(5)
-> )
-> PARTITION BY LIST(id) (
-> PARTITION p1 VALUES IN (0),
-> PARTITION p2 VALUES IN (1)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE tnrange (
-> id INT,
-> name VARCHAR(5)
-> )
-> PARTITION BY RANGE(id) (
-> PARTITION p1 VALUES LESS THAN (1),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO tnlist VALUES (NULL, 'bob');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tnrange VALUES (NULL, 'jim');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tnlist;
+------+------+
| id | name |
+------+------+
| NULL | bob |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tnrange;
+------+------+
| id | name |
+------+------+
| NULL | jim |
+------+------+
1 row in set (0.00 sec)
在两个表中，id列没有声明为“NOT NULL”，这意味着它们允许Null值。可以通过删除这些分区，然后重新运行SELECT 语句，来验证这些行被保存在每个表的p1分区中：
mysql> ALTER TABLE tnlist DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE tnrange DROP PARTITION p1;
Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM tnlist;
Empty set (0.00 sec)
mysql> SELECT * FROM tnrange;
Empty set (0.00 sec)
在按HASH和KEY分区的情况下，任何产生NULL值的表达式都视同好像它的返回值为0。我们可以通过先创建一个按HASH分区的表，然后插入一个包含有适当值的记录，再检查对文件系统的作用，来验证这一点。假定有使用下面的语句在测试数据库中创建了一个表tnhash：
CREATE TABLE tnhash (
id INT,
name VARCHAR(5)
)
PARTITION BY HASH(id)
PARTITIONS 2；
假如Linux 上的MySQL 的一个RPM安装，这个语句在目录/var/lib/mysql/test下创建了两个.MYD文件，这两个文件可以在bash shell中查看，结果如下：
/var/lib/mysql/test> ls *.MYD -l
-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p0.MYD
-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD
注意：每个文件的大小为0字节。现在在表tnhash 中插入一行id列值为NULL的行，然后验证该行已经被插入：
mysql> INSERT INTO tnhash VALUES (NULL, 'sam');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tnhash;
+------+------+
| id | name |
+------+------+
| NULL | sam |
+------+------+
1 row in set (0.01 sec)
回想一下，对于任意的整数N,NULL MOD N 的值总是等于NULL。这个结果在确定正确的分区方面被认为是0。回到系统shell(仍然假定bash用于这个目的) ，通过再次列出数据文件，可以看出值被成功地插入到第一个分区（默认名称为p0）中：
var/lib/mysql/test> ls *.MYD -l
-rw-rw---- 1 mysql mysql 20 2005-11-04 18:44 tnhash_p0.MYD
-rw-rw---- 1 mysql mysql 0 2005-11-04 18:41 tnhash_p1.MYD
可以看出INSERT语句只修改了文件tnhash_p0.MYD，它在磁盘上的尺寸增加了，而没有影响其他的文件。
假定有下面的一个表：
CREATE TABLE tndate (
id INT,
dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
)；
像其他的MySQL函数一样，YEAR(NULL)返回NULL值。一个dt列值为NULL的行，其分区表达式的计算结果被视为0，该行被插入到分区p0中。
18.3. 分区管理
18.3.1. RANGE和LIST分区的管理 18.3.2. HASH和KEY分区的管理 18.3.3. 分区维护 18.3.4. 获取关于分区的信息MySQL 5.1 提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。所有这些操作都可以通过使用ALTER TABLE 命令的分区扩展来实现(关于语法的定义，请参见13.1.2节，“ALTER TABLE语法” )。也有获得分区表和分区信息的方式。在本节，我们讨论下面这些主题：
· 按RANGE或LIST分区的表的分区管理的有关信息，请参见18.3.1节，“RANGE和LIST分区的管理”。
· 关于HASH和KEY分区管理的讨论，请参见18.3.2节，“HASH和KEY分区的管理”。
· MySQL 5.1中提供的、获得关于分区表和分区信息的机制的讨论，请参见18.3.4节，“获取关于分区的信息” 。
· 关于执行分区维护操作的讨论，请参见18.3.3节，“分区维护”。
注释：在MySQL 5.1中，一个分区表的所有分区都必须有子分区同样的名字，并且一旦表已经创建，再改变子分区是不可能的。
要点：当前，从5.1系列起建立的MySQL 服务器就把“ALTER TABLE ... PARTITION BY ...”作为有效的语法，但是这个语句目前还不起作用。我们期望MySQL 5.1达到生产状态时，能够按照下面的描述实现该语句的功能。
要改变一个表的分区模式，只需要使用带有一个“partition_options”子句的ALTER TABLE 的命令。这个子句和与创建一个分区表的CREATE TABLE命令一同使用的子句有相同的语法，并且总是以关键字PARTITION BY 开头。例如，假设有一个使用下面CREATE TABLE语句建立的按照RANGE分区的表：
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(YEAR(purchased))
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
)；
现在，要把这个表按照使用id列值作为键的基础，通过KEY分区把它重新分成两个分区，可以使用下面的语句：
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；
这和先删除这个表、然后使用“CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2；”重新创建这个表具有同样的效果。
18.3.1. RANGE和LIST分区的管理
关于如何添加和删除分区的处理，RANGE和LIST分区非常相似。基于这个原因，我们在本节讨论这两种分区的管理。关于HASH和KEY分区管理的信息，请参见18.3.2节，“HASH和KEY分区的管理”。删除一个RANGE或LIST分区比增加一个分区要更加简单易懂，所以我们先讨论前者。
从一个按照RANGE或LIST分区的表中删除一个分区，可以使用带一个DROP PARTITION子句的ALTER TABLE命令来实现。这里有一个非常基本的例子，假设已经使用下面的CREATE TABLE和INSERT语句创建了一个按照RANGE分区的表，并且已经插入了10条记录：
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE(YEAR(purchased))
-> (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tr VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'CD player', '1993-11-05'),
-> (3, 'TV set', '1996-03-10'),
-> (4, 'bookcase', '1982-01-10'),
-> (5, 'exercise bike', '2004-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'popcorn maker', '2001-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '1984-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)
可以通过使用下面的命令查看那些记录已经插入到了分区p2中：
mysql> SELECT * FROM tr
-> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)
要删除名字为p2的分区，执行下面的命令：
mysql> ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)
记住下面一点非常重要：当删除了一个分区，也同时删除了该分区中所有的数据。可以通过重新运行前面的SELECT查询来验证这一点：
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)
如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATE TABLE命令。（请参见13.2.9节，“TRUNCATE语法”）。
如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE ... REORGANIZE PARTITION”语句。参见下面的内容，或者在13.1.2节，“ALTER TABLE语法” 中参考关于REORGANIZE PARTITION的信息。
如果现在执行一个SHOW CREATE TABLE命令，可以观察到表的分区结构是如何被改变的：
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)
如果插入购买日期列的值在'1995-01-01'和 '2004-12-31'之间（含）的新行到已经修改后的表中时，这些行将被保存在分区p3中。可以通过下面的方式来验证这一点：
mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 11 | pencil holder | 1995-07-12 |
| 1 | desk organiser | 2003-10-15 |
| 5 | exercise bike | 2004-05-09 |
| 7 | popcorn maker | 2001-11-22 |
+------+----------------+------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased
-> BETWEEN '1995-01-01' AND '2004-12-31';
Empty set (0.00 sec)
注意：由“ALTER TABLE ... DROP PARTITION”语句引起的、从表中删除的行数并没有被服务器报告出来，就好像通过同等的DELETE查询操作一样。
删除LIST分区使用和删除RANGE分区完全相同的“ALTER TABLE ... DROP PARTITION”语法。但是，在对其后使用这个表的影响方面，还是有重大的区别：在这个表中，再也不能插入这么一些行，这些行的列值包含在定义已经删除了的分区的值列表中 (有关示例，请参见18.2.2节，“LIST分区” ）。
要增加一个新的RANGE或LIST分区到一个前面已经分区了的表，使用“ALTER TABLE ... ADD PARTITION”语句。对于使用RANGE分区的表，可以用这个语句添加新的区间到已有分区的序列的前面或后面。例如，假设有一个包含你所在组织的全体成员数据的分区表，该表的定义如下：
CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE(YEAR(dob)) (
PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990)
);
进一步假设成员的最小年纪是16岁。随着日历接近2005年年底，你会认识到不久将要接纳1990年（以及以后年份）出生的成员。可以按照下面的方式，修改成员表来容纳出生在1990－1999年之间的成员：
ALTER TABLE ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
要点：对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，将会导致下面的一个错误：
mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));
错误1463 (HY000): 对每个分区，VALUES LESS THAN 值必须严格增长
采用一个类似的方式，可以增加新的分区到已经通过LIST分区的表。例如，假定有如下定义的一个表：
CREATE TABLE tt (
id INT,
data INT
)
PARTITION BY LIST(data) (
PARTITION p0 VALUES IN (5, 10, 15),
PARTITION p1 VALUES IN (6, 12, 18)
)；
可以通过下面的方法添加一个新的分区，用来保存拥有数据列值7，14和21的行：
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21))；
注意：不能添加这样一个新的LIST分区，该分区包含有已经包含在现有分区值列表中的任意值。如果试图这样做，将会导致错误：
mysql> ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8, 12));
错误1465 (HY000): 在LIST分区中，同一个常数的多次定义
因为带有数据列值12的任何行都已经分配给了分区p1，所以不能在表tt上再创建一个其值列表包括12的新分区。为了实现这一点，可以先删除分区p1，添加分区np，然后使用修正后的定义添加一个新的分区p1。但是，正如我们前面讨论过的，这将导致保存在分区p1中的所有数据丢失——而这往往并不是你所真正想要做的。另外一种解决方法可能是，建立一个带有新分区的表的副本，然后使用“CREATE TABLE ... SELECT ...”把数据拷贝到该新表中，然后删除旧表，重新命名新表，但是，当需要处理大量的数据时，这可能是非常耗时的。在需要高可用性的场合，这也可能是不可行的。
幸运地是，MySQL 的分区实现提供了在不丢失数据的条件下重新定义分区的方式。让我们首先看两个涉及到RANGE分区的简单例子。回想一下现在定义如下的成员表：
mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int(11) default NULL,
`fname` varchar(25) default NULL,
`lname` varchar(25) default NULL,
`dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(dob)) (
PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)
假定想要把表示出生在1960年前成员的所有行移入到一个分开的分区中。正如我们前面看到的，不能通过使用“ALTER TABLE ... ADD PARTITION”来实现这一点。但是，要实现这一点，可以使用ALTER TABLE上的另外一个与分区有关的扩展，具体实现如下：
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
)；
实际上，这个命令把分区p0分成了两个新的分区s0和s1。同时，它还根据包含在两个“PARTITION ... VALUES ...”子句中的规则，把保存在分区p0中的数据移入到两个新的分区中，所以分区s0中只包含YEAR(dob)小于1960的那些行，s1中包含那些YEAR(dob)大于或等于1960但是小于1970的行。
一个REORGANIZE PARTITION语句也可以用来合并相邻的分区。可以使用如下的语句恢复成员表到它以前的分区：
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
PARTITION p0 VALUES LESS THAN (1970)
)；
使用“REORGANIZE PARTITION”拆分或合并分区，没有数据丢失。在执行上面的语句中，MySQL 把保存在分区s0和s1中的所有数据都移到分区p0中。
“REORGANIZE PARTITION”的基本语法是：
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；
其中，tbl_name 是分区表的名称，partition_list 是通过逗号分开的、一个或多个将要被改变的现有分区的列表。partition_definitions 是一个是通过逗号分开的、新分区定义的列表，它遵循与用在“CREATE TABLE”中的partition_definitions 相同的规则 (请参见13.1.5节，“CREATE TABLE语法”)。应当注意到，在把多少个分区合并到一个分区或把一个分区拆分成多少个分区方面，没有限制。例如，可以重新组织成员表的四个分区成两个分区，具体实现如下：
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
)；
同样，对于按LIST分区的表，也可以使用REORGANIZE PARTITION。让我们回到那个问题，即增加一个新的分区到已经按照LIST分区的表tt中，但是因为该新分区有一个值已经存在于现有分区的值列表中，添加新的分区失败。我们可以通过先添加只包含非冲突值的分区，然后重新组织该新分区和现有的那个分区，以便保存在现有的那个分区中的值现在移到了新的分区中，来处理这个问题：
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
PARTITION p1 VALUES IN (6, 18),
PARTITION np VALUES in (4, 8, 12)
)；
当使用“ALTER TABLE ... REORGANIZE PARTITION”来对已经按照RANGE和LIST分区表进行重新分区时，下面是一些要记住的关键点：
· 用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则。
最重要的是，应该记住：新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于重新组织按照LIST分区的表）。
· partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。
例如，在本节中用作例子的成员表中，分区p1和p2总共覆盖了1980到1999的这些年。因此，对这两个分区的重新组织都应该覆盖相同范围的年份。
· 对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。
例如，不能使用以“ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...”开头的语句，来重新组织本节中用作例子的成员表。因为，p0覆盖了1970年以前的年份，而p2覆盖了从1990到1999（包括1990和1999）之间的年份，因而这两个分区不是相邻的分区。
· 不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。如果想在不删除和重建表的条件下实现这两个任务，可以使用“ALTER TABLE ... PARTITION BY ....”，例如：
· ALTER TABLE members
· PARTITION BY HASH(YEAR(dob))
· PARTITIONS 8；
注释：在MySQL 5.1发布前的版本中，“ALTER TABLE ... PARTITION BY ...”还没有实现。作为替代，要么使用先删除表，然后使用想要的分区重建表，或者——如果需要保留已经存储在表中的数据——可以使用“CREATE TABLE ... SELECT ...”来创建新的表，然后从旧表中把数据拷贝到新表中，再删除旧表，如有必要，最后重新命名新表。
相关阅读 更多 +
