mysql分区表的好处,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 分区+分区字段没有索引

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