MySQL分区之RANGE分区(二)
signed 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