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 | 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年數據所在的分區即可 [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) 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<='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)