range分区(实用篇)

上一篇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