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) */ |

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”的错误提示