mysql中的分区概念

admin

December 11, 2009 - 15 minutes read - 3143 words

18.1. MySQL中的分区概述 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的方式 18.3. 分区管理 18.3.1. RANGE和LIST分区的管理 18.3.2. HASH和KEY分区的管理 18.3.3. 分区维护 18.3.4. 获取关于分区的信息

本章讨论MySQL 5.1.中实现的分区。关于分区和分区概念的介绍可以在18.1节，“MySQL中的分区概述”中找到。MySQL 5.1 支持哪几种类型的分区，在18.2节，“分区类型” 中讨论。关于子分区在18.2.5节，“子分区” 中讨论。现有分区表中分区的增加、删除和修改的方法在18.3节，“分区管理” 中介绍。和分区表一同使用的表维护命令在18.3.3节，“分区维护” 中介绍。

请注意：MySQL 5.1中的分区实现仍然很新（pre-alpha品质），此时还不是可生产的（not production-ready）。同样，许多也适用于本章：在这里描述的一些功能还没有实际上实现(分区维护和重新分区命令），其他的可能还没有完全如所描述的那样实现(例如, 用于分区的数据目录（DATA DIRECTORY）和索引目录（INDEX DIRECTORY）选项受到Bug #13520) 不利的影响). 我们已经设法在本章中标出这些差异。在提出缺陷报告前，我们鼓励参考下面的一些资源：

MySQL 分区论坛这是一个为对MySQL分区技术感兴趣或用MySQL分区技术做试验提供的官方讨论论坛。来自MySQL 的开发者和其他的人，会在上面发表和更新有关的材料。它由分区开发和文献团队的成员负责监控。
分区缺陷报告已经归档在缺陷系统中的、所有分区缺陷的一个列表，而无论这些缺陷的年限、严重性或当前的状态如何。根据许多规则可以对这些缺陷进行筛选，或者可以从MySQL缺陷系统主页开始，然后查找你特别感兴趣的缺陷。
Mikael Ronström’s Blog MySQL分区体系结构和领先的开发者Mikael Ronström 经常在这里贴关于他研究MySQL 分区和MySQL簇的文章。
PlanetMySQL 一个MySQL 新闻网站，它以汇集MySQL相关的网誌为特点，那些使用我的MySQL的人应该对此有兴趣。我们鼓励查看那些研究MySQL分区的人的网誌链接，或者把你自己的网誌加到这些新闻报道中。

MySQL 5.1的二进制版本目前还不可用；但是，可以从BitKeeper知识库中获得源码。要激活分区，需要使用–with-分区选项编译服务器。关于建立MySQL 的更多信息，请参见2.8节，“使用源码分发版安装MySQL”。如果在编译一个激活分区的MySQL 5.1创建中碰到问题，可以在MySQL分区论坛中查找解决办法，如果在论坛中已经贴出的文章中没有找到问题的解决办法，可以在上面寻找帮助。

18.1. MySQL中的分区概述

本节提供了关于MySQL 5.1.分区在概念上的概述。

SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径(关于如何配置的解释，请参见 7.6.1节，“使用符号链接”)。

分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为 分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。分区函数的例子可以在本章后面关于分区类型的讨论中找到 (请参见 18.2节，“分区类型” )，也可在 13.1.5节，“CREATE TABLE语法” 的分区语法描述中找到。

当二进制码变成可用时（也就是说，5.1 -max二进制码将通过–with-partition建立），分区支持就将包含在MySQL 5.1的-max版本中。如果MySQL二进制码是使用分区支持建立的，那么激活它不需要任何其他的东西 (例如，在my.cnf文件中，不需要特殊的条目)。可以通过使用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命令来做出这个判断）。

对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。

要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE选项，这如同为非分区表配置存储引擎一样。但是，必须记住[STORAGE] ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过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版本中，这没有作用）。

创建分区的临时表也是可能的；但是，这种表的生命周期只有当前MySQL 的会话的时间那么长。对于非分区的临时表，这也是一样的。

注释：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。

可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。关于这些选项的更多信息，请参见 18.3节，“分区管理”。注释：这个特殊的功能由于Bu g #13250的原因，目前还不能实用。在第一个5.1二进制版本投入使用时，我们应该已经把这个问题解决了。

分区的一些优点包括：

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

· 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

·一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

·涉及到例如SUM()和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

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

要经常检查本页和本章，因为它将随MySQL 5.1后续的分区进展而更新。

18.2. 分区类型

18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分区处理NULL值的方式

本节讨论在MySQL 5.1中可用的分区类型。这些类型包括：

·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分区”。

无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的哈希函数自动进行处理。

分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。例如，下面的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)

    -> );

错误1488 (HY000): 表的所有分区必须有唯一的名字。

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

注释：在下面的章节中，我们没有必要提供可以用来创建每种分区类型语法的所有可能形式，这些信息可以在 13.1.5节，“CREATE TABLE语法” 中找到。

18.2.1. RANGE分区

按照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,等等增加新的分区（关于如何实现的详细信息参见 18.3节，“分区管理” )。

在几乎一样的结构中，你还可以基于雇员的工作代码来分割表，也就是说，基于job_code列值的连续区间。例如——假定2位数字的工作代码用来表示普通（店内的）工人，三个数字代码表示办公室和支持人员，四个数字代码表示管理层，你可以使用下面的语句创建该分区表：

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 (job_code) (

    PARTITION p0 VALUES LESS THAN (100),

    PARTITION p1 VALUES LESS THAN (1000),

    PARTITION p2 VALUES LESS THAN (10000)

)；

在这个例子中, 店内工人相关的所有行将保存在分区p0中，办公室和支持人员相关的所有行保存在分区p1中，管理层相关的所有行保存在分区p2中。

在VALUES LESS THAN子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分；因此，表达式的值不能为NULL。由于这个原因，雇员表的hired, separated, job_code,和store_id列已经被定义为非空（NOT NULL）。

除了可以根据商店编号分割表数据外，你还可以使用一个基于两个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年前就已经停止工作的雇员相对应的所有行。(更多信息请参见 13.1.2节，“ALTER TABLE语法” 和 18.3节，“分区管理”）。对于有大量行的表，这比运行一个如“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源程序中启用，但是，有关工作正在进行中。

18.2.2. LIST分区

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节，“子分区”。

18.2.3. HASH分区

18.2.3.1. LINEAR 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；

如果没有包括一个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= n x（其中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_的自变数的任意列都必须是那个键的一部分。

18.2.3.1. LINEAR HASH分区

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分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

18.2.4. KEY分区

按照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分区”。

18.2.5. 子分区

子分区是分区表中每个分区的再次分割。例如，考虑下面的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节，“分区管理”。

18.2.6. MySQL分区处理NULL值的方式

MySQL 中的分区在禁止空值（NULL）上没有进行处理，无论它是一个列值还是一个用户定义表达式的值。一般而言，在这种情况下MySQL 把NULL视为。如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。

在本节中，我们提供了一些例子，来说明当决定一个行应该保存到哪个分区时，MySQL 是如何处理NULL值的。

如果插入一行到按照RANGE或LIST分区的表，该行用来确定分区的列值为NULL，分区将把该NULL值视为。例如，考虑下面的两个表，表的创建和插入记录如下：

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值的表达式都视同好像它的返回值为。我们可以通过先创建一个按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

注意：每个文件的大小为字节。现在在表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。这个结果在确定正确的分区方面被认为是。回到系统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的行，其分区表达式的计算结果被视为，该行被插入到分区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 trG

*************************** 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 membersG

*************************** 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 …”来创建新的表，然后从旧表中把数据拷贝到新表中，再删除旧表，如有必要，最后重新命名新表。

18.3.2. HASH和KEY分区的管理

在改变分区设置方面，按照HASH分区或KEY分区的表彼此非常相似，但是它们又与按照RANGE或LIST分区的表在很多方面有差别。所以，本节只讨论按照HASH或KEY分区表的修改。关于添加和删除按照RANGE或LIST进行分区的表的分区的讨论，参见 18.3.1节，“RANGE和LIST分区的管理”。

不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTER TABLE … COALESCE PARTITION”命令来合并HASH或KEY分区。例如，假定有一个包含顾客信息数据的表，它被分成了12个分区。该顾客表的定义如下：

CREATE TABLE clients (

    id INT,

    fname VARCHAR(30),

    lname VARCHAR(30),

    signed DATE

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12；

要减少分区的数量从12到6，执行下面的ALTER TABLE命令：

mysql> ALTER TABLE clients COALESCE PARTITION 6；

Query OK, 0 rows affected (0.02 sec)

对于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分区的表， COALESCE能起到同样的作用。下面是一个类似于前面例子的另外一个例子，它们的区别只是在于表是按照LINEAR KEY进行分区：

mysql> CREATE TABLE clients_lk (

    ->     id INT,

    ->     fname VARCHAR(30),

    ->     lname VARCHAR(30),

    ->     signed DATE

    -> )

    -> PARTITION BY LINEAR KEY(signed)

    -> PARTITIONS 12；

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 6；

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

COALESCE不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;

错误1478 (HY000): 不能移动所有分区，使用DROP TABLE代替

要增加顾客表的分区数量从12到18，使用“ALTER TABLE … ADD PARTITION”,具体如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；

注释：“ALTER TABLE … REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。

18.3.3. 分区维护

注释：实际上，本节讨论的命令还没有在MySQL 5.1中实现，在这里提出的目的，是为了在5.1版投产前的开发周期期间，引出来自用户测试该软件的反馈意见。(换句话说，就是“请不要反馈这样的缺陷，说这些命令不起作用”）。随着MySQL5.1版开发的继续，这些信息很有可能发生变化。随着分区功能的实现和提高，我们将更新本节的内容。

MySQL 5.1中可以执行许多分区维护的任务。对于分区表，MySQL不支持命令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE。作为替代，可以使用ALTER TABLE 的许多扩展来在一个或多个分区上直接地执行这些操作，如下面列出的那样：

·重建分区: 这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。

示例：

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

·优化分区： 如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

示例：

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

在一个给定的分区表上使用“OPTIMIZE PARTITION”等同于在那个分区上运行CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。

·分析分区： 读取并保存分区的键分布。

示例：

ALTER TABLE t1 ANALYZE PARTITION (p3)；

·修补分区：修补被破坏的分区。

示例：

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

·检查分区： 可以使用几乎与对非分区表使用CHECK TABLE相同的方式检查分区。

示例：

ALTER TABLE trb3 CHECK PARTITION (p1)；

这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE … REPAIR PARTITION”来修补该分区。

还可以使用 mysqlcheck 或 myisamchk 应用程序，在对表进行分区时所产生的、单独的MYI文件上进行操作，来完成这些任务。请参见 8.7节，“mysqlcheck：表维护和维修程序”。（在pre-alpha编码中，这个功能已经可以使用）。

18.3.4. 获取关于分区的信息

本节讨论获取关于现有分区的信息。这个功能仍然处于计划阶段，所以现阶段在这里描述的，实际上是我们想要在MySQL 5.1中实现的一个概观。

如在本章中别处讨论的一样，在SHOW CREATE TABLE的输出中包含了用于创建分区表的PARTITION BY子句。例如：

mysql> SHOW CREATE TABLE trb3G

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

       Table: trb3

Create Table: CREATE TABLE `trb3` (

  `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 p2 VALUES LESS THAN (2000) ENGINE = MyISAM,

  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM

1 row in set (0.00 sec)

注释： 当前，对于按HASH或KEY分区的表，PARTITIONS子句并不显示。 ( Bug #14327)

SHOW TABLE STATUS用于分区表，它的输出与用于非分区表的输出相同，除了引擎（Engine）列总是包含‘PARTITION’值。(关于这个命令的更多信息，参见 13.5.4.18节，“SHOW TABLE STATUS语法”）。要获取单个分区的状态信息，我们计划实现一个SHOW PARTITION STATUS命令（请参见下面)。

计划用于分区表的、两个附加的SHOW命令是：

·SHOW PARTITIONS

这个命令预期其功能类似于SHOW TABLES和SHOW DATABASES，除了该命令将列出的是分区而不是表或数据库。这个命令的输出可能包含单个称为Partitions_in_ _tbl_name_的列，其中_tbl_name_是分区表的名字。对于SHOW TABLES命令而言，如果一旦选择了一个数据库，随后该数据库将作为SHOW TABLES命令的默认数据库。但是由于SHOW PARTITIONS命令不可能用这样的方式来“选择”一个表，它很可能需要使用FROM子句，以便MySQL知道要显示的是哪个表的分区信息。

·SHOW PARTITION STATUS

这个命令将提供关于一个或多个分区的详细状态信息。它的输出很可能包含有与SHOW TABLE STATUS 的输出相同或类似的列，此外，还包括显示用于分区的数据和索引路径的附加列。这个命令可能支持LIKE和FROM子句，这样使得通过名字获得关于一个给定分区的信息，或者获得关于属于指定表或数据库的分区的信息，成为可能。

扩展INFORMATION_SCHEMA 数据库的计划也在进行中，以便提供关于分区表和分区的信息。这个计划当前还处一个在非常早的阶段；随着补充的信息变得可用，以及任何新的、与分区有关的INFORMATION_SCHEMA扩展得以实现，我们将更新手册相关部分的内容。

来源： http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

Mysql