MySQL分区表

一、概念

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。分区表对应用上层是透明的。需要注意的是：分区的索引是按照子表定义的，没有全局索引的概念。

二、分区表的优缺点

2.1 优点

1、大表数据分区，查询时优化器根据分区定义，无须扫描所有分区，只查找需要数据的分区
2、批量删除整个分区十分方便

2.2 缺点

1、一个表最多只能有1024个分区
2、在分区表上，用于分区表达式里的每一个字段都必须是唯一性索引的一部分(包括主键)
3、分区表中无法使用外键
4、分区键不可以为NULL
5、分区列和索引列不匹配导致无法进行分区过滤

三、分区表的语法

3.1 创建分区表

mysql> CREATE TABLE IF NOT EXISTS `user` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY RANGE (id) (  
 ->     PARTITION p0 VALUES LESS THAN (3),  
 ->     PARTITION p1 VALUES LESS THAN (6),  
 ->     PARTITION p2 VALUES LESS THAN (9),  
 ->     PARTITION p3 VALUES LESS THAN (12),  
 ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
 -> );  

3.2 对现有表进行分区

alter table aa partition by RANGE(id)  
 -> (PARTITION p1 VALUES less than (1),  
 -> PARTITION p2 VALUES less than (5),  
 -> PARTITION p3 VALUES less than MAXVALUE);

3.3 删除分区

mysql> alter table aa drop partition p2; 

四、索引方式：
性能依次降低

4.1 主键分区
主键分区即字段是主键同时也是分区字段，性能最好

4.2 部分主键+分区索引
使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引

4.3 分区索引
没有主键，只有分区字段且分区字段建索引

4.4 分区+分区字段没有索引
只建了分区，但是分区字段没有建索引