bitscn.com
1.查看mysql版本是否支持分区  
 show variables like '%partition%';  
+-------------------+-------+
| variable_name     | value |
+-------------------+-------+
| have_partitioning | yes   |
+-------------------+-------+
如果value 为yes 则支持分区，
 2.测试那种存储引擎支持分区   
inoodb引擎 mysql> create table engine1(id int) engine=innodb partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec) mrg_myisam引擎 mysql> create table engine2(id int) engine=mrg_myisam partition by range(id)(partition po values less than(10)); error 1572 (hy000): engine cannot be used in partitioned tables blackhole引擎 mysql> create table engine3(id int) engine=blackhole partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec) csv引擎 mysql> create table engine4(id int) engine=csv partition by range(id)(partition po values less than(10)); error 1572 (hy000): engine cannot be used in partitioned tables memory引擎 mysql> create table engine5(id int) engine=memory partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec) federated引擎 mysql> create table engine6(id int) engine=federated partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec) archive引擎 mysql> create table engine7(id int) engine=archive partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec) myisam 引擎 mysql> create table engine8(id int) engine=myisam partition by range(id)(partition po values less than(10)); query ok, 0 rows affected (0.01 sec)
3.mysql分区表，分区引擎测试
表分区的存储引擎相同
mysql> create table pengine1(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=myisam); query ok, 0 rows affected (0.05 sec)
表分区的存储引擎不同
mysql> create table pengine2(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=innodb); error 1497 (hy000): the mix of handlers in the partitions is not allowed in this version of mysql
同一个分区表中的所有分区必须使用同一个存储引擎，并且存储引擎要和主表的保持一致。
4.分区类型
range:基于一个连续区间的列值，把多行分配给分区；
list：列值匹配一个离散集合； hash:基于用户定义的表达式的返回值选择分区，表达式对要插入表中的列值进行计算。这个函数可以包含sql中有效的，产生非负整数值的任何表达式。
key：类似于hash分区，区别在于key 分区的表达式可以是一列或多列，且mysql提供自身的hash函数。
5.range分区maxvalue值 及加分区测试；
创建表 prange，最后分区一个分区值是maxvalue
mysql> create table prange(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than maxvalue);query ok, 0 rows affected (0.06 sec)
加分区
mysql> alter table prange add partition (partition p3 values less than (20));error 1481 (hy000): maxvalue can only be used in last partition definition
在分区p0前面加个分区
mysql> alter table prange add partition (partition p3 values less than (1));error 1481 (hy000): maxvalue can only be used in last partition definition
说明有maxvalue值后，直接加分区是不可行的；
创建表prange1，无maxvalue值
mysql> create table prange1(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than (30)); query ok, 0 rows affected (0.08 sec)
从最大值后加个分区
mysql> alter table prange1 add partition (partition p3 values less than (40));query ok, 0 rows affected (0.02 sec)records: 0 duplicates: 0 warnings: 0
从分区的最小值前加个分区
mysql> alter table prange1 add partition (partition p43 values less than (1));error 1493 (hy000): values less than value must be strictly increasing for each partition
由此可见，range 的分区方式在加分区的时候，只能从最大值后面加，而最大值前面不可以添加；
6. 用时间做分区测试
create table ptime2(id int,createdate datetime) engine=myisam partition by range (to_days(createdate)) (partition po values less than (20100801),partition p1 values less than (20100901)); query ok, 0 rows affected (0.01 sec) mysql> create table ptime3(id int,createdate datetime) engine=myisam partition by range (createdate) (partition po values less than (20100801),partition p1 values less than (20100901)); error 1491 (hy000): the partition function returns the wrong type
直接使用时间列不可以，range分区函数返回的列需要是整型。
mysql> create table ptime6(id int,createdate datetime) engine=myisam partition by range (year(createdate)) (partition po values less than (2010),partition p1 values less than (2011)); query ok, 0 rows affected (0.01 sec)
使用年函数也可以分区。
7.mysql可用的分区函数
day() dayofmonth() dayofweek() dayofyear() datediff() extract() hour() microsecond() minute() mod() month() quarter() second() time_to_sec() to_days() weekday() year() yearweek() 等
当然，还有floor（），ceiling() 等，前提是使用这两个分区函数的分区健必须是整型。
要小心使用其中的一些函数，避免犯逻辑性的错误，引起全表扫描。
比如：
create table ptime11(id int,createdate datetime) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31)); mysql> insert into ptime11 values (1,'2010-06-17'); mysql> explain partitions select count(1) from ptime11 where createdate>'2010-08-17'/g; *************************** 1. row *************************** id: 1 select_type: simple table: ptime11 partitions: po,p1 type: allpossible_keys: null key: null key_len: null ref: null rows: 5 extra: using where 1 row in set (0.00 sec)
8.主键及约束测试
分区健不包含在主键内
mysql> create table pprimary(id int,createdate datetime,primary key(id)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31)); error 1503 (hy000): a primary key must include all columns in the table's partitioning function
分区健包含在主键内
mysql> create table pprimary1(id int,createdate datetime,primary key(id,createdate)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));query ok, 0 rows affected (0.05 sec)
说明分区健必须包含在主键里面。
mysql> create table pprimary2(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(uid)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));error 1503 (hy000): a unique index must include all columns in the table's partitioning function
说明在表上建约束索引会有问题，必须把约束索引列包含在分区健内。
mysql> create table pprimary3(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(createdate)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));query ok, 0 rows affected (0.00 sec)
虽然在表上可以加约束索引，但是只有包含在分区健内，这种情况在实际应用过程中会遇到问题，这个问题点在以后的mysql 版本中也许会改进。
9.子分区测试
只有range和list分区才能有子分区，每个分区的子分区数量必须相同，
mysql> create table pprimary7(id int,createdate datetime,uid char(10),primary key(id,createdate)) engine=myisam partition by range(to_days(createdate)) subpartition by hash(to_days(createdate))(partition p0 values less than (20100801) ( subpartition so,subpartition s1) ,partition p1 values less than (20100901) (subpartition s0,subpartition s1)); error 1517 (hy000): duplicate partition name s1
提示了重复的分区名称错误，这和mysql5.1帮助文档中的说明有出入，不知道是不是这个问题在某个小版本中修改过。
10.mysql分区健null值测试；
mysql将null值视为0.自动插入最小的分区中。
11.mysql分区管理测试
mysql> alter table pprimary4 truncate partition p1;error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'truncate partition p1' at line 1
5.1版本中还不支持这个语法，5.5中已经支持，很好的一个命令；
alter table reorganize 可以重新组织分区。