MySQL5.1 引入表分区功能,使得MySQL在处理大表的能力上得到增强。使用过表分区功能的朋友应该知道,MySQL5.1中使用表分区的时候,对字段是有要求的,那就是必须是整数型,或者可以将其他类型的字段通过函数转换成整数型才可以。
/* with MySQL 5.1 ivan @ MySQL实验室(mysqlab.net/blog/) */ CREATE TABLE mysqlab_net ( ivan DATE ) PARTITION BY RANGE (TO_DAYS (ivan ) ) ( PARTITION p01 VALUES LESS THAN (TO_DAYS ( ’2007-08-08′ ) ), PARTITION p02 VALUES LESS THAN (TO_DAYS ( ’2008-08-08′ ) ), PARTITION p03 VALUES LESS THAN (TO_DAYS ( ’2009-08-08′ ) ), PARTITION p04 VALUES LESS THAN (MAXVALUE ) );
SHOW CREATE TABLE mysqlab_net\G
*************************** 1. row ***************************TABLE: mysqlab_netCREATE TABLE: CREATE TABLE `mysqlab_net` (`ivan` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1/*!50100 PARTITION BY RANGE (TO_DAYS(ivan))(PARTITION p01 VALUES LESS THAN (733261) ENGINE = InnoDB,PARTITION p02 VALUES LESS THAN (733627) ENGINE = InnoDB,PARTITION p03 VALUES LESS THAN (733992) ENGINE = InnoDB,PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */怎么样?读取的时候谁知道那个数字是多少?(不过也可以通过自定义函数实现还原)
中加入了columns关键字,使得可读性好多了。看例子 /* with MySQL 5.5 ivan @ MySQL实验室(mysqlab.net/blog/) */ CREATE TABLE `mysqlab.net` ( ivan DATE ) PARTITION BY RANGE COLUMNS (ivan ) ( PARTITION p01 VALUES LESS THAN ( ’2007-08-08′ ), PARTITION p02 VALUES LESS THAN ( ’2008-08-08′ ), PARTITION p03 VALUES LESS THAN ( ’2009-08-08′ ), PARTITION p04 VALUES LESS THAN (MAXVALUE );
SHOW CREATE TABLE `mysqlab.net`\G
*************************** 1. row ***************************TABLE: mysqlab.netCREATE TABLE: CREATE TABLE `mysqlab.net` (`ivan` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1/*!50500 PARTITION BY RANGE COLUMNS(ivan)(PARTITION p01 VALUES LESS THAN (’2007-08-08′) ENGINE = InnoDB,PARTITION p02 VALUES LESS THAN (’2008-08-08′) ENGINE = InnoDB,PARTITION p03 VALUES LESS THAN (’2009-08-08′) ENGINE = InnoDB,PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */另外表分区(partition) columns关键字还支持多字段,比如 partition by range columns(a,b);将支持清空指定的分区TRUNCATE PARTITION。有望在明年(2010)夏季GA。另外支持的功能在上的使用,让人非常期待!
Reference:
Note:在使用表分区的时候,并不是分区越多越好,要根据情况而定,因为会出现意想不到的。