1.查看Mysql版本是否支持分區
SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
如果VALUE 為YES 則支持分區，
2.測試那種存儲引擎支持分區
INOODB引擎 www.2cto.com
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引擎 www.2cto.com
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 www.2cto.com
同一個分區表中的所有分區必須使用同一個存儲引擎，並且存儲引擎要和主表的保持一致。
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)); www.2cto.com
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) www.2cto.com
使用年函數也可以分區。
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: ALL
possible_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)); www.2cto.com
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)); www.2cto.com
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 可以重新組織分區。
作者 sss0213
