深入解析MySQL分区（Partition）功能

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节，“分区管理”。注释：这个特殊的功能由于Bug #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分区”。 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于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=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的自变数的任意列都必须是那个键的一部分。 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视为0。如果你希望回避这种做法，你应该在设计表时不允许空值；最可能的方法是，通过声明列“NOT NULL”来实现这一点。 在本节中，我们提供了一些例子，来说明当决定一个行应该保存到哪个分区时，MySQL 是如何处理NULL值的。 如果插入一行到按照RANGE或LIST分区的表，该行用来确定分区的列值为NULL，分区将把该NULL值视为0。例如，考虑下面的两个表，表的创建和插入记录如下： mysql> CREATE TABLE tnlist (