作者:老王
MySQL从5.1.3开始支持Partition,你可以使用如下命令来确认你的版本是否支持Partition:
mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
MySQL支持RANGE,LIST,HASH,KEY分区类型,其中以RANGE最为常用:
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
created DATETIME,
PRIMARY KEY(id, created)
) ENGINE=INNODB PARTITION BY RANGE (TO_DAYS(created)) (
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))
)
即便创建完分区,也可以在后期管理,比如说添加一个新的分区:
ALTER TABLE foo ADD PARTITION (
PARTITION foo_3 VALUES LESS THAN (TO_DAYS('2011-01-01'))