上一篇blog对range分区做了个大概简介，接下来这篇对实际使用中的一些小细节进行分析总结。主要是新增和删除分区以及数据写入(null值的处理已在前面提到http://blog.sina.com.cn/s/blog_4d398f210100wrb8.html)
==========================================================
普通表：
root@qw_test 01:49:44>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
1）改造分区表
root@qw_test 03:34:19>alter table tmp_qw_test partition by range(to_days(gmt_create));
ERROR 1492 (HY000): For RANGE partitions each partition must be defined
—直接改造分区表不指定分区则报错
方法：在改造分区表的同时至少指定一个分区
root@qw_test 03:36:12>alter table tmp_qw_test partition by range(to_days(gmt_create))
-> (partition p_tmp_qw_test_201107 VALUES LESS THAN (734684) ENGINE = InnoDB);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
—因为分区键不是主键的其中一个组成部分而报错
方法：修改主键，使分区键成为主键的一部分
root@qw_test 03:37:37>alter table tmp_qw_test drop primary key,
-> add primary key(id,gmt_create);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 03:40:03>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
修改主键后改造分区成功
root@qw_test 03:40:11>alter table tmp_qw_test partition by range(to_days(gmt_create))
-> (partition p_tmp_qw_test_201107 VALUES LESS THAN (734684) ENGINE = InnoDB);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 03:41:13>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
2)在已有分区表上增加分区
通过to_days计算好数字(这里已两个月一个分区为例)
root@qw_test 03:41:18>select to_days(‘2011-09-01′);
+———————–+
| to_days(‘2011-09-01′) |
+———————–+
| 734746 |
+———————–+
1 row in set (0.00 sec)
root@qw_test 03:45:19> alter table tmp_qw_test add partition( PARTITION p_tmp_qw_test_201109 VALUES LESS THAN (734746) ENGINE = InnoDB);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 03:45:46>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
3)删除分区
root@qw_test 04:00:37> alter table tmp_qw_test drop partition p_tmp_qw_test_201107;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 04:00:51>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
删除最大的分区后，开始不能新增的小分区能新增成功
root@qw_test 04:04:28> alter table tmp_qw_test add partition( PARTITION p_tmp_qw_test_201112 VALUES LESS THAN (734837) ENGINE = InnoDB);
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
(当前最大分区为：p_tmp_qw_test_201201，分区是按顺序定义，只能从最低到最高)
—删除最大分区后，小的分区新增成功
root@qw_test 04:04:55> alter table tmp_qw_test drop partition p_tmp_qw_test_201201;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 04:05:05> alter table tmp_qw_test add partition( PARTITION p_tmp_qw_test_201112 VALUES LESS THAN (734837) ENGINE = InnoDB);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 04:05:12>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
删除最后一个分区时提示失败，提示使用drop table
root@qw_test 04:11:57>alter table tmp_qw_test drop PARTITION p_tmp_qw_test_201109;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead
与前一篇介绍range分区中提到的分区有点有关，单个分区对应一个数据文件；删除分区即删除了相应的数据
注：分区的新增、删除每次只能是一个
root@qw_test 03:52:37> alter table tmp_qw_test add partition( PARTITION p_tmp_qw_test_201111 VALUES LESS THAN (734807) ENGINE = InnoDB),
-> add partition( PARTITION p_tmp_qw_test_201201 VALUES LESS THAN (734868) ENGINE = InnoDB);
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 ‘
add partition( PARTITION p_tmp_qw_test_201201 VALUES LESS THAN (734868) ENGINE’ at line 1
root@qw_test 04:05:17> alter table tmp_qw_test drop PARTITION p_tmp_qw_test_201111,drop partition p_tmp_qw_test_201112;
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 ‘drop partition p_tmp_qw_test_201112′ at line 1
4)查看分区表信息
root@qw_test 04:15:56> select * from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=’qw_test’ and TABLE_NAME like ‘tmp_qw_test%’\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: qw_test
TABLE_NAME: tmp_qw_test
PARTITION_NAME: p_tmp_qw_test_201109
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: to_days(gmt_create)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 734746
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
1 row in set (0.00 sec)
5)range分区对于不在分区范围内的数据写入时会报错，不能写入
root@qw_test 04:18:34>insert into tmp_qw_test values(1,1234,’asd’,2,’2011-10-01′,’2011-10-01′,’asd’,’asd’,1,1);
ERROR 1526 (HY000): Table has no partition for value 734776
—对于未指定对最大值进行处理的分区需要及时添加相应的分区！否则需要指定对最大值的处理
root@qw_test 04:19:43> alter table tmp_qw_test add partition( PARTITION p_tmp_qw_test_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@qw_test 04:21:50>show create table tmp_qw_test\G
*************************** 1. row ***************************
Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`nick` varchar(32) NOT NULL,
`status` tinyint(4) NOT NULL,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`char_1` varchar(32) DEFAULT NULL,
`char_2` varchar(32) DEFAULT NULL,
`num_1` bigint(20) DEFAULT NULL,
`num_2` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`,`gmt_create`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
另一种方式是定期新增分区，可以是人肉的。。也可以是存储过程定期调用，估计大家都选后者，人肉越多引入误操作问题的可能性就越大。呵呵，下一篇会介绍一个简单的定期删除和新增分区的存储过程
下面是一些其他分区的其他操作(有兴趣的可以再研究研究)
COALESCE PARTITION
REORGANIZE PARTITION
ANALYZE PARTITION
CHECK PARTITION
OPTIMIZE PARTITION
REBUILD PARTITION
REPAIR PARTITION
