mysql的partition_mysql的partition操作

Mysql 技术内幕

1. 创建partition的表

mysql> create table log_partition (

-> dt datetime not null,

-> info varchar(100) not null,

-> index ( dt)

-> )

-> partition by range(year(dt))(

-> partition p0 values less than ( 2005),

-> partition p1 values less than (2006),

-> partition p2 values less than ( 2007),

-> partition p3 values less than ( 2008),

-> partition p4 values less than maxvalue

-> );

Query OK, 0 rows affected (0.19 sec)

mysql> show create table log_partition

| log_partition | CREATE TABLE `log_partition` (

`dt` datetime NOT NULL,

`info` varchar(100) NOT NULL,

KEY `dt` (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (year(dt))

(PARTITION p0 VALUES LESS THAN (2005) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2006) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (2007) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2008) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

而且每partition会占一个文件：

3，使用explain来查看一个查询是在哪个partition上执行的。

其他链接可以参见：

http://blog.csdn.net/ls3648098/article/details/9353623

http://en.oreilly.com/mysql2010/public/schedule/detail/12431

http://www.cnblogs.com/hustcat/archive/2009/12/24/1631674.html

创建partition表时，如果用了非支持函数，会出现“This partition function is not allowed”的错误提示