MySQL分区之RANGE分区
环境： www.2cto.com
[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 | 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年数据所在的分区即可
[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<='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分区
㈡ 常见相关问题
① 插入了一个不在分区中定义的值
[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) un