环境：

mysql> select version()\G;
*************************** 1. row ***************************
version(): 5.5.28

㈠主要应用场景

RANGE分区主要用于日期列的分区
例如销售类的表，可以根据年份来分区存储销售记录
如下是对sales表进行分区

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<\'2009-01-01\'
而只需删除2008年数据所在的分区即可

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年整年的销售额

mysql> explain partitions
    -> select * from sales
    -> where date>=\'2009-01-01\' and date<=\'2009-12-31\'\G;
*************************** 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<\'2010-01-01\',那么优化器会连带搜索p2010分区

㈡常见相关问题

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

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()这类函数进行优化选择

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<=\'2012-01-31\'\G;
*************************** 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<=\'2012-01-31\'\G;
*************************** 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)

ITPub博客

MySQL分区之RANGE分区

最新文章