MySQL分区之RANGE分区_MySQL

bitsCN.com

MySQL分区之RANGE分区

 

环境：    

[sql] 

mysql> select version()/G;  

*************************** 1. row ***************************  

version(): 5.5.28  

 

         ㈠ 主要应用场景

         

         RANGE分区主要用于日期列的分区

         例如销售类的表，可以根据年份来分区存储销售记录

         如下是对sales表进行分区

[sql] 

mysql> create table sales(money int unsigned not null,  

    -> date datetime  

    -> )engine=innodb  

    -> partition by range (year(date)) (  

    -> partition p2008 values less than (2009),  

    -> partition p2009 values less than (2010),  

    -> partition p2010 values less than (2011)  

    -> );  

Query OK, 0 rows affected (0.06 sec)  

  

mysql> insert into sales SELECT 100,'2008-01-01';  

Query OK, 1 row affected (0.02 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales SELECT 100,'2008-02-01';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales SELECT 200,'2008-01-02';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales SELECT 100,'2008-03-01';  

Query OK, 1 row affected (0.01 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales SELECT 100,'2009-03-01';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales SELECT 200,'2010-03-01';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> select * from sales;  

+-------+---------------------+  

| money | date                |  

+-------+---------------------+  

|   100 | 2008-01-01 00:00:00 |  

|   100 | 2008-02-01 00:00:00 |  

|   200 | 2008-01-02 00:00:00 |  

|   100 | 2008-03-01 00:00:00 |  

|   100 | 2009-03-01 00:00:00 |  

|   200 | 2010-03-01 00:00:00 |  

+-------+---------------------+  

6 rows in set (0.00 sec)  

 

          ① 便于对sales表管理，如果要删除2008年的数据，我们就不需要执行：

             delete from sales where date>= '2008-01-01' and date

             而只需删除2008年数据所在的分区即可

[sql] 

mysql> alter table sales drop partition p2008;  

Query OK, 0 rows affected (0.10 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

  

mysql> select * from sales;  

+-------+---------------------+  

| money | date                |  

+-------+---------------------+  

|   100 | 2009-03-01 00:00:00 |  

|   200 | 2010-03-01 00:00:00 |  

+-------+---------------------+  

2 rows in set (0.00 sec)  

 

          ② 另一个好处是加快某些查询操作，例如，我们只需要查询2009年整年的销售额

[sql] 

mysql> explain partitions  

    -> select * from sales  

    -> where date>='2009-01-01' and date

*************************** 1. row ***************************  

           id: 1  

  select_type: SIMPLE  

        table: sales  

   partitions: p2009  

         type: ALL  

possible_keys: NULL  

          key: NULL  

      key_len: NULL  

          ref: NULL  

         rows: 4  

        Extra: Using where  

1 row in set (0.00 sec)  

 

          SQL优化器会进行分区修剪，即只搜索p2009

          也请注意分区的边界，如date

          

          ㈡ 常见相关问题

          

          ① 插入了一个不在分区中定义的值

[sql] 

mysql> insert into sales select 200,'2012-12-3';  

ERROR 1526 (HY000): Table has no partition for value 2012  

mysql> show create table sales /G;  

*************************** 1. row ***************************  

       Table: sales  

Create Table: CREATE TABLE `sales` (  

  `money` int(10) unsigned NOT NULL,  

  `date` datetime DEFAULT NULL  

) ENGINE=InnoDB DEFAULT CHARSET=latin1  

/*!50100 PARTITION BY RANGE (year(date))  

(PARTITION p2009 VALUES LESS THAN (2010) ENGINE = InnoDB,  

 PARTITION p2010 VALUES LESS THAN (2011) ENGINE = InnoDB) */  

1 row in set (0.00 sec)  

  

ERROR:   

No query specified  

  

mysql> alter table sales add partition(  

    -> partition p2012 values less than maxvalue);  

Query OK, 0 rows affected (0.04 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

  

mysql> insert into sales select 200,'2012-12-3';  

Query OK, 1 row affected (0.01 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> select * from sales where date='2012-12-3';  

+-------+---------------------+  

| money | date                |  

+-------+---------------------+  

|   200 | 2012-12-03 00:00:00 |  

+-------+---------------------+  

1 row in set (0.00 sec)  

 

          ② 对RANGE分区的查询，优化器只能对year(),to_days(),to_seconds()和unix_timestamp()这类函数进行优化选择

[sql] 

mysql> create table t (date datetime)  

    -> engine=innodb  

    -> partition by range (year(date)*100+month(date)) (  

    -> partition p201201 values less than (201202),  

    -> partition p201202 values less than (201203),  

    -> partition p201203 values less than (201204)  

    -> );  

Query OK, 0 rows affected (0.02 sec)  

  

mysql> insert into t select '2012-01-01';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-01-06';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-02-06';  

Query OK, 1 row affected (0.01 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-01-06';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-03-06';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-02-01';  

Query OK, 1 row affected (0.01 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> select * from t;  

+---------------------+  

| date                |  

+---------------------+  

| 2012-01-01 00:00:00 |  

| 2012-01-06 00:00:00 |  

| 2012-01-06 00:00:00 |  

| 2012-02-06 00:00:00 |  

| 2012-02-01 00:00:00 |  

| 2012-03-06 00:00:00 |  

+---------------------+  

6 rows in set (0.00 sec)  

  

mysql> explain partitions  

    -> select * from t  

    -> where date>='2012-01-01' and date

*************************** 1. row ***************************  

           id: 1  

  select_type: SIMPLE  

        table: t  

   partitions: p201201,p201202,p201203  

         type: ALL  

possible_keys: NULL  

          key: NULL  

      key_len: NULL  

          ref: NULL  

         rows: 6  

        Extra: Using where  

1 row in set (0.00 sec)  

  

ERROR:   

No query specified  

  

mysql> drop table t;  

Query OK, 0 rows affected (0.01 sec)  

mysql> create table t (date datetime)  

    -> engine=innodb  

    -> partition by range (to_days(date)) (  

    -> partition p201201 values less than (to_days('2012-02-01')),  

    -> partition p201201 values less than (to_days('2012-03-01')),  

    -> partition p201201 values less than (to_days('2012-04-01'))  

    -> );  

mysql> insert into t select '2012-01-02';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-01-03';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-01-08';  

Query OK, 1 row affected (0.01 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-02-08';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> insert into t select '2012-03-08';  

Query OK, 1 row affected (0.00 sec)  

Records: 1  Duplicates: 0  Warnings: 0  

  

mysql> select * from t;  

+---------------------+  

| date                |  

+---------------------+  

| 2012-01-02 00:00:00 |  

| 2012-01-03 00:00:00 |  

| 2012-01-08 00:00:00 |  

| 2012-02-08 00:00:00 |  

| 2012-03-08 00:00:00 |  

+---------------------+  

5 rows in set (0.00 sec)  

  

mysql> explain partitions  

    -> select * from t  

    -> where date>='2012-01-01' and date

*************************** 1. row ***************************  

           id: 1  

  select_type: SIMPLE  

        table: t  

   partitions: p1  

         type: ALL  

possible_keys: NULL  

          key: NULL  

      key_len: NULL  

          ref: NULL  

         rows: 3  

        Extra: Using where  

1 row in set (0.00 sec)  

 

bitsCN.com