这几天学习了一下分区，不经意间想起了三国演义开篇词：“合久必分，分久必合”，看来这句话也适合数据库，英文手册和中文手册对比，更新了很多东西，以后尽量看英文的了，下面是学习笔记：
理论部分：
1，基本概念：
分区可以设置任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。
2，注意事项：
对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。
分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。
可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。
无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。
3，分区类型：
| RANGE分区 |
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN 操作符来进行定义。 |
| lIST分区 |
类似于RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 |
| HASH分区 |
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 |
| KEY分区 |
类似于HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 |
测试实例：
1，range测试实例：
说明：employees表保存有20家音像店的职员记录，这20家音像店的编号从1到20，按照这种分区方案，在商店1到5工作的职员相对应的所有行被保存在分区P0中，商店6到10的职员保存在P1中，依次类推，大于或等于16的职员都保存在P3中。PARTITION BY RANGE 语法要求每个分区都是按顺序进行定义，从最低到最高，AXVALUE 表示最大的可能的整数值，在将来的某个时候，当商店数已经增长到25,30 或更多，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
mysql> 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
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into employees values (1,'xiaoii','xiaowu01','1971-01-01','1972-01-01',1,5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (2,'xiaokk','xiaowu01','1972-01-01','1973-01-01',2,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (3,'xiaoxx','xiaowu01','1974-01-01','1975-01-01',3,7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (4,'xiaoyy','xiaowu01','1976-01-01','1977-01-01',4,11);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (5,'xiaocc','xiaowu01','1978-01-01','1979-01-01',5,12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (6,'xiaoaa','xiaowu01','1980-01-01','1981-01-01',6,16);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (7,'xiaott','xiaowu01','1982-01-01','1983-01-01',7,17);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employees values (8,'xiaogg','xiaowu01','1984-01-01','1985-01-01',8,20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from employees;
+----+--------+----------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+--------+----------+------------+------------+----------+----------+
| 1 | xiaoii | xiaowu01 | 1971-01-01 | 1972-01-01 | 1 | 5 |
| 2 | xiaokk | xiaowu01 | 1972-01-01 | 1973-01-01 | 2 | 6 |
| 3 | xiaoxx | xiaowu01 | 1974-01-01 | 1975-01-01 | 3 | 7 |
| 4 | xiaoyy | xiaowu01 | 1976-01-01 | 1977-01-01 | 4 | 11 |
| 5 | xiaocc | xiaowu01 | 1978-01-01 | 1979-01-01 | 5 | 12 |
| 6 | xiaoaa | xiaowu01 | 1980-01-01 | 1981-01-01 | 6 | 16 |
| 7 | xiaott | xiaowu01 | 1982-01-01 | 1983-01-01 | 7 | 17 |
| 8 | xiaogg | xiaowu01 | 1984-01-01 | 1985-01-01 | 8 | 20 |
+----+--------+----------+------------+------------+----------+----------+
8 rows in set (0.00 sec)
mysql> explain partitions select * from employees;
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employees | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 8 | |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employees where store_id=5;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employees where store_id=11;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | p2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employees where store_id=16;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | p3 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
2，LIST测试实例：
假定有20个音像店，店面分布在4个地区，如下表所示：
| 地区
|
商店ID 号
|
| 北区 |
3, 5, 6, 9, 17 |
| 东区 |
1, 2, 10, 11, 19, 20 |
| 西区 |
4, 12, 13, 14, 18 |
| 中心区 |
7, 8, 15, 16 |
mysql> CREATE TABLE employeeslist (
-> 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)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into employeeslist values (1,'xiaoii','xiaowu01','1971-01-01','1972-01-01',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employeeslist values (2,'xiaokk','xiaowu01','1972-01-01','1973-01-01',2,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employeeslist values (3,'xiaoxx','xiaowu01','1974-01-01','1975-01-01',3,4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from employeeslist;
+----+--------+----------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+--------+----------+------------+------------+----------+----------+
| 2 | xiaokk | xiaowu01 | 1972-01-01 | 1973-01-01 | 2 | 3 |
| 1 | xiaoii | xiaowu01 | 1971-01-01 | 1972-01-01 | 1 | 1 |
| 3 | xiaoxx | xiaowu01 | 1974-01-01 | 1975-01-01 | 3 | 4 |
+----+--------+----------+------------+------------+----------+----------+
3 rows in set (0.00 sec)
mysql> explain partitions select * from employeeslist where store_id=1;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeslist | pEast | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employeeslist where store_id=3;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeslist | pNorth | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employeeslist where store_id=4;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeslist | pWest | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
3，HASH测试实例：
mysql> CREATE TABLE employeeshash (
-> 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;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into employeeshash values (1,'xiaoii','xiaowu01','1971-01-01','1972-01-01',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employeeshash values (2,'xiaokk','xiaowu01','1972-01-01','1973-01-01',2,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employeeshash values (3,'xiaoxx','xiaowu01','1974-01-01','1975-01-01',3,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employeeshash values (4,'xiaoxx','xiaowu01','1974-01-01','1975-01-01',3,9);
Query OK, 1 row affected (0.00 sec)
mysql> select * from employeeshash;
+----+--------+----------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+--------+----------+------------+------------+----------+----------+
| 1 | xiaoii | xiaowu01 | 1971-01-01 | 1972-01-01 | 1 | 1 |
| 4 | xiaoxx | xiaowu01 | 1974-01-01 | 1975-01-01 | 3 | 9 |
| 3 | xiaoxx | xiaowu01 | 1974-01-01 | 1975-01-01 | 3 | 6 |
| 2 | xiaokk | xiaowu01 | 1972-01-01 | 1973-01-01 | 2 | 3 |
+----+--------+----------+------------+------------+----------+----------+
4 rows in set (0.00 sec)
mysql> explain partitions select * from employeeshash;
+----+-------------+---------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | employeeshash | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+---------------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employeeshash where store_id=1;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeshash | p1 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employeeshash where store_id=6;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeshash | p2 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from employeeshash where store_id=3;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employeeshash | p3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
4，子分区测试实例：
在MySQL 5.1中，可以对RANGE或LIST分区了的表再进行子分区。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（composite partitioning），子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：
mysql> 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
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc ts;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| purchased | date | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，这些分区的头2个只保存“purchased”列中值小于1990的那些记录。
分区管理：
MySQL 5.1 可以通过使用ALTER TABLE 命令对分区进行 添加、删除、重新定义、合并或拆分。
1，RANGE和LIST分区的管理：
| ALTER TABLE chlotte DROP PARITION p1
|
删除chlotte表中名称为p1的分区及p1分区中的所有数据，但NDBCLUSTER存储引擎不支持此语法。 |
| TRUNCATE TABLE chlotte; |
只删除分区中的数据，保留表的定义和表的分区模式。 |
| ALTER TABLE ... REORGANIZE PARTITION |
改变表的分区而又不丢失数据。 |
| ALTER TABLE ... ADD PARTITION |
增加一个新的RANGE或LIST分区到一个前面已经分区了的表，对于RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。 |
| ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)； |
其中，tbl_name 是分区表的名称，partition_list 是通过逗号分开的、一个或多个将要被改变的现有分区的列，partition_definitions 是一个是通过逗号分开的、新分区定义的列表，使用“REORGANIZE PARTITION”拆分或合并分区，没有数据丢失。 |
2，HASH和KEY分区的管理：
| ALTER TABLE chlotte COALESCE PARTITION 6； |
把chlotte表的分区数量由7个减少到6个 |
| ALTER TABLE chlotte COALESCE PARTITION8; |
把chlotte表的分区数量从6个增加到8个 |
分区维护：
Table maintenance of partitioned tables can be accomplished using the statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE, which are supported for partitioned tables as of MySQL 5.1.27.
| ALTER TABLE t1 REBUILD PARTITION p0, p1; |
重建分区: 这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。 |
| ALTER TABLE t1 OPTIMIZE PARTITION p0, p1; |
优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行作了许多修改，用来收回没有使用的空间，并整理分区数据文件的碎片。 |
| ALTER TABLE t1 ANALYZE PARTITION p3; |
分析分区：读取并保存分区的键分布。 |
| ALTER TABLE t1 REPAIR PARTITION p0,p1; |
修复分区： 修复被破坏的分区。 |
| ALTER TABLE trb3 CHECK PARTITION p1; |
检查分区： 可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。 |
分区的限制：
1，从mysql 5.1.12 开始不支持： 存储过程，存储函数，用户自定义函数，用户变量。
2，分区表不支持外键。
