Mysql分区

分区概述:

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

分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。

实际上，表的不同部分在不同的位置被存储为单独的表。

用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。

函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区

1. mysql> show variables like '%partition%'\G
   
2. *************************** 1. row ***************************
   
3. Variable_name: have_partitioning
   
4.         Value: YES
   
5. 1 row in set (0.00 sec)

对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。

在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。

但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。

要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE 选项，这如同为非分区表配置存储引擎一样。

但是，必须记住[STORAGE] ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：

1. mysql> CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
   
2.     -> ENGINE=INNODB
   
3.     -> PARTITION BY HASH(MONTH(tr_date))
   
4.     -> PARTITIONS 6;
   
5. Query OK, 0 rows affected (0.29 sec)

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

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

可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。

分区的一些优点包括：

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

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

分区类型:

• RANGE分区
• LIST分区
• HASH分区
• KEY分区
• 子分区

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

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

分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。

RANGE分区:

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT NOT NULL,
   
8.     -> store_id INT NOT NULL
   
9.     -> );
   
10. Query OK, 0 rows affected (0.08 sec)

根据你的需要，这个表可以有多种方式来按照区间进行分区。一种方式是使用store_id 列。例如，你可能决定通过添加一个PARTITION BY RANGE子句把这个表分割成4个区间，如下所示: 

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT NOT NULL,
   
8.     -> store_id INT NOT NULL
   
9.     -> )
   
10.     -> PARTITION BY RANGE (store_id) (
    
11.     -> PARTITION p0 VALUES LESS THAN (6),
    
12.     -> PARTITION p1 VALUES LESS THAN (11),
    
13.     -> PARTITION p2 VALUES LESS THAN (16),
    
14.     -> PARTITION p3 VALUES LESS THAN (21)
    
15.     -> );
    
16. Query OK, 0 rows affected (0.08 sec)

按照这种分区方案，

在商店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子句，该子句提供给所有大于明确指定的最高值的值：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT NOT NULL,
   
8.     -> store_id INT NOT NULL
   
9.     -> )
   
10.     -> PARTITION BY RANGE (store_id) (
    
11.     -> PARTITION p0 VALUES LESS THAN (6),
    
12.     -> PARTITION p1 VALUES LESS THAN (11),
    
13.     -> PARTITION p2 VALUES LESS THAN (16),
    
14.     -> PARTITION p3 VALUES LESS THAN MAXVALUE
    
15.     -> );
    
16. Query OK, 0 rows affected (0.09 sec)

MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区

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

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT NOT NULL,
   
8.     -> store_id INT NOT NULL
   
9.     -> )
   
10.     -> PARTITION BY RANGE (job_code) (
    
11.     -> PARTITION p0 VALUES LESS THAN (100),
    
12.     -> PARTITION p1 VALUES LESS THAN (1000),
    
13.     -> PARTITION p2 VALUES LESS THAN (10000)
    
14.     -> )
    
15.     -> ;
    
16. Query OK, 0 rows affected (0.06 sec)

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

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

除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT,
   
8.     -> store_id INT
   
9.     -> )
   
10.     -> PARTITION BY RANGE (YEAR(separated)) (
    
11.     -> PARTITION p0 VALUES LESS THAN (1991),
    
12.     -> PARTITION p1 VALUES LESS THAN (1996),
    
13.     -> PARTITION p2 VALUES LESS THAN (2001),
    
14.     -> PARTITION p3 VALUES LESS THAN MAXVALUE
    
15.     -> );
    
16. Query OK, 0 rows affected (0.23 sec)

在这个方案中，在1991年前雇佣的所有雇员的记录保存在分区p0中，1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中， 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中，2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用：

当需要删除“旧的”数据时。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用

1. ALTER TABLE employees DROP PARTITION p0;

来删除所有在1991年前就已经停止工作的雇员相对应的所有行

对于有大量行的表，这比运行一个如

1. DELETE FROM employees WHERE YEAR(separated) <= 1990;

这样的一个DELETE查询要有效得多。

想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。

LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

注释：在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。

假定有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”语句：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT,
   
8.     -> store_id INT
   
9.     -> )
   
10.     -> PARTITION BY LIST(store_id) (
    
11.     -> PARTITION pNorth VALUES IN (3,5,6,9,17),
    
12.     -> PARTITION pEast VALUES IN (1,2,10,11,19,20),
    
13.     -> PARTITION pWest VALUES IN (4,12,13,14,18),
    
14.     -> PARTITION pCentral VALUES IN (7,8,15,16)
    
15.     -> );
    
16. Query OK, 0 rows affected (0.53 sec)

这使得在表中增加或删除指定地区的雇员记录变得容易起来。

例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询

1. mysql> ALTER TABLE employees DROP PARTITION pWest;
   
2. Query OK, 0 rows affected (0.20 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0

来进行删除，它与具有同样作用的DELETE （删除）查询

1. DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)

比起来，要有效得多。 

要点：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

1. 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分区结合起来生成复合的子分区也是可能的

HASH分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONSnum”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。

例如，下面的语句创建了一个使用基于“store_id”列进行 哈希处理的表，该表被分成了4个分区：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT,
   
8.     -> store_id INT
   
9.     -> )
   
10.     -> PARTITION BY HASH(store_id)
    
11.     -> PARTITIONS 4;
    
12. Query OK, 0 rows affected (0.28 sec)

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 例外： 对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。

如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。

“expr”还可以是一个返回一个整数的SQL表达式。例如，也许你想基于雇用雇员的年份来进行分区。这可以通过下面的语句来实现：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT,
   
8.     -> store_id INT
   
9.     -> )
   
10.     -> PARTITION BY HASH(YEAR(hired))
    
11.     -> PARTITIONS 4;
    
12. Query OK, 0 rows affected (0.10 sec)

“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个分区：

1. mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
   
2.     -> PARTITION BY HASH( YEAR(col3) )
   
3.     -> PARTITIONS 4;
   
4. Query OK, 0 rows affected (0.06 sec)

如果插入一个col3列值为'2005-09-15'的记录到表t1中，那么保存该条记录的分区确定如下：

MOD(YEAR('2005-09-01'),4) = MOD(2005,4) =  1

MySQL 5.1 还支持一个被称为“linear hashing（线性哈希功能）”的变量，它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。

每当插入或更新一条记录，用户函数都要计算一次。当删除记录时，用户函数也可能要进行计算，这取决于所处的环境。

注释：如果将要分区的表有一个唯一的键，那么用来作为HASH用户函数的自变数或者主键的column_list的自变数的任意列都必须是那个键的一部分。

LINEAR HASH分区

MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规 哈希使用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字，如下面所示：

1. mysql> CREATE TABLE employees (
   
2.     -> id INT NOT NULL,
   
3.     -> fname VARCHAR(30),
   
4.     -> lname VARCHAR(30),
   
5.     -> hired DATE NOT NULL DEFAULT '1970-01-01',
   
6.     -> separated DATE NOT NULL DEFAULT '9999-12-31',
   
7.     -> job_code INT,
   
8.     -> store_id INT
   
9.     -> )
   
10.     -> PARTITION BY LINEAR HASH(YEAR(hired))
    
11.     -> PARTITIONS 4;
    
12. Query OK, 0 rows affected (0.02 sec)

假设一个表达式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个分区，是通过下面的语句创建的：

1. mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
   
2.     -> PARTITION BY LINEAR HASH( YEAR(col3) )
   
3.     -> PARTITIONS 6;
   
4. Query OK, 0 rows affected (0.05 s

现在假设要插入两行记录到表t1中，其中一条记录col3列值为'2003-04-14'，另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下：

1. V = POWER(2, CEILING(LOG(2,7))) = 8
2. N = YEAR('2003-04-14') & (8 - 1)
3. = 2003 & 7
4. = 3
5. (3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中)

第二条记录将要保存到的分区序号计算如下：

1. V = 8
2. N = YEAR('1998-10-19') & (8-1)
3. = 1998 & 7
4. = 6
5. (6 >= 4 为真（TRUE）: 还需要附加的步骤)
6. N = 6 & CEILING(5 / 2)
7. = 6 & 3
8. = 2
9. (2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

KEY分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

通过线性KEY分割一个表也是可能的。下面是一个简单的例子：

1. mysql> CREATE TABLE tk (
   
2.     -> col1 INT NOT NULL,
   
3.     -> col2 CHAR(5),
   
4.     -> col3 DATE
   
5.     -> ) 
   
6.     -> PARTITION BY LINEAR KEY (col1)
   
7.     -> PARTITIONS 3;
   
8. Query OK, 0 rows affected (0.04 sec)

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

子分区

子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：

1. mysql> CREATE TABLE ts (id INT, purchased DATE)
   
2.     -> PARTITION BY RANGE(YEAR(purchased))
   
3.     -> SUBPARTITION BY HASH(TO_DAYS(purchased))
   
4.     -> SUBPARTITIONS 2
   
5.     -> (
   
6.     -> PARTITION p0 VALUES LESS THAN (1990),
   
7.     -> PARTITION p1 VALUES LESS THAN (2000),
   
8.     -> PARTITION p2 VALUES LESS THAN MAXVALUE
   
9.     -> );
   
10. Query OK, 0 rows affected (0.08 sec)

表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 子句来明确定义子分区也是可能的。例如，创建在前面例子中给出的同一个表的、一个更加详细的方式如下： 

1. mysql> CREATE TABLE ts (id INT, purchased DATE)
   
2.     -> PARTITION BY RANGE(YEAR(purchased))
   
3.     -> SUBPARTITION BY HASH(TO_DAYS(purchased))
   
4.     -> (
   
5.     -> PARTITION p0 VALUES LESS THAN (1990)
   
6.     -> (
   
7.     -> SUBPARTITION s0,
   
8.     -> SUBPARTITION s1
   
9.     -> ),
   
10.     -> PARTITION p1 VALUES LESS THAN (2000)
    
11.     -> (
    
12.     -> SUBPARTITION s2,
    
13.     -> SUBPARTITION s3
    
14.     -> ),
    
15.     -> PARTITION p2 VALUES LESS THAN MAXVALUE
    
16.     -> (
    
17.     -> SUBPARTITION s4,
    
18.     -> SUBPARTITION s5
    
19.     -> )
    
20.     -> );
    
21. Query OK, 0 rows affected (0.05 sec)

下面贴一些分区sql语句

二. 创建分区

分区目前有4个类型 RANGE, LIST, HASH, KEY,  这篇博客要讲的是RANGE类型. 手册里如下描述.

1	RANGE : 基于属于一个给定连续区间的列值，把多行分配给分区。

注意:RANGE的值只能从小到大,而且,标识列必须包含在主键里.

创建range分区有2种方式,1种是创建表时就创建分区,另1种是创建表后再创建分区.

1.创建表时就创建分区

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE members (     id INT,      fname VARCHAR(25),     entry_time DATETIME,     PRIMARY KEY (`id`,`entry_time`) ) PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

2.创建表后再创建分区

1 2 3 4 5 6

ALTER TABLE members PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

分区后, Mysql 将会 members 分成 4个表, 将 '2013-06-30 23:59:59' 以前注册的都放在p0里, 将 '2013-12-31 23:59:59'以前注册的都放在p1里,以此类推,

2014年7月份和以后的都放在p3里.

三. 测试分区

使用 explain partitions 测试查询语句是否精确到单个分区.

1 2 3 4 5 6 7 8

explain partitions select count(*) from members where entry_time = '2013-03-01 23:59:59';                           +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ | id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | Extra                    | +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ |  1 | SIMPLE      | school | p0         | index | PRIMARY       | id      | 8       | NULL |    2 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)

四. 获取分区信息

1	mysql> SHOW CREATE TABLE members\G

显示如下

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE members (     id INT,     fname VARCHAR(25),     entry_time DATETIME ) PARTITION BY RANGE(entry_time) (     PARTITION p0 VALUES LESS THAN ('2013-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN ('2013-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN ('2014-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

五. 修改分区(拆分,合并)

拆分

上边 member 的分区,只分到了2014年6月份.现在我们将p3分区重拆分为多个分区.

1 2 3 4 5 6 7

ALTER TABLE members REORGANIZE PARTITION p3 INTO (     PARTITION p4 VALUES LESS THAN ('2014-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p5 VALUES LESS THAN ('2015-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p6 VALUES LESS THAN ('2015-12-31 23:59:59') ENGINE = InnoDB,     PARTITION p7 VALUES LESS THAN ('2016-06-30 23:59:59') ENGINE = InnoDB,     PARTITION p8 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

合并

再次声明:RANGE的值只能从小到大,而且,标识列必须包含在主键里.

现在将p0,p1,p2合并到m1里.

1 2 3

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2 INTO (     PARTITION m1 VALUES LESS THAN ('2014-06-31 23:59:59') ENGINE = InnoDB );

六. 重新分区

语法与创建分区一样, 直接 alter by 就可以了.Mysql会将旧分区逻辑删除掉.

比如更改分区 标志列.

1 2 3 4 5 6

ALTER TABLE members PARTITION BY RANGE(id) (     PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,     PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,     PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,     PARTITION p3 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB );

还可以更改为其他分区类型 如 LIST,HASH等. 举一个HASH的例子, 将id平均分布到5分区表.

1	ALTER TABLE members PARTITION BY HASH (id) PARTITIONs 5;

七. 删除分区以及分区数据

如非必要,尽量不要用,使用 拆分合并更好一点.

1	ALTER TABLE tr DROP PARTITION p1;

删除分区,不删除数据

alert table tb_user remove partitioning