深⼊解析MySQL分区（Partition）功能
参考  blog.csdn/tjcyjd/article/details/11194489
第18章：分区
⽬录
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⼆进制码是使⽤分区⽀持建⽴的，那么激活它不需要任何其他的东西 (例如，在myf ⽂件中，不需要特殊的条⽬)。可以通过使⽤SHOW VARIABLES命令来确定MySQL是否⽀持分区，例如：
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
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)(
mysql group by order by
-> 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分区的采⽤上⾯的⽅案，下⾯的查询将失败：