MySQL range 分区表

create table t2( id int null )engine=innodb partition by range(id) ( partition p0 values less than (10), partition p1 values less than (20));

查看分区表的信息：

mysql> select * from information_schema.partitions     -> where table_schema=database() and table_name='t2'\G;

修改分区：

mysql> alter table t2 add partition ( partition p3 values less than maxvalue);

range分区主要用于日期列的分区，比如：

create table t3( 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));

删除分区信息：

alter table t3 drop partition p2008;

查看分区表的执行计划：

mysql> explain partitions select * from t3 where date>='2009-01-01' and date<'2010-01-01';

对于range分区的查询，优化器只能对year()、to_days()、to_seconds()、unix_timestamp()这类函数进行优化选择。