mysql的分区
作者:bin一、分区的优点:
1、和单个磁盘来比,可以存更多数据;
2、优化查询,查询时只扫描必要的一个或者多个分区提高效率,同时执行例如count,sum,这样的函数时,可以多个分区并行执行,提高效率;
3、对于过期的,不需要保留的数据,可以直接删除分区(例如删除某个date前的数据);
二、分区的类型:
RANGE、LIST、HASH、KEY
其中前3者,要求分区键必须是int类型;
无论哪种分区类型都必须满足下面2条中的其中一条:
1、没有主键/唯一键
2、分区键是主键/唯一键
三、RANGE分区
RANGE分区利用取值范围将数据分区;
例:
mysql> create table emp( -> id int not null, -> ename varchar(30), -> store_id int not null, -> ) -> partition by range (store_id)( -> partition p0 values less than (10), -> partition p1 values less than (20), -> partition p2 values less than maxvalue -> );
解析:将0-10的store_id数据放在p0,10-20放到p1,大于20放到p3中
注意:如果分区键值为null的话,range中会将null当作最小值来处理;
mysql 5.5改进来range分区的功能,支持了非整数来分区RANGE COLUMNS;
例如:
mysql> create table emp_1( -> id int not null, -> name varchar (30), -> store_id int not null, -> `date` date not null default ('2017-09-11') -> ) -> parttion by range columns (date) ( -> partition p0 values less than ('2017-09-10'), -> partition p1 values less than ('2017-09-20'), -> partition p2 values less than (maxvalues) -> );
这样可以通过不同的日期,区分数据,优化查询时间;
删除过期的数据:
例如我要删除’2017-09-10’前的数据,只要:
alter table emp_1 drop partition p0;
四、LIST分区
LIST分区建立离散值的分区,需要指定特定值属于哪个分区,其他很多地方类似于RANGE
设置方法:
-> partition p1 values in (1,2,3),
值得注意的是,如果插入的字段值,不在LIST分区中,那么将会插入失败,因为LIST并不存在类似于maxvalues less than这样的定义方式;
五、HASH分区
主要通过分散点,确保数据被平均分布在不同的分区中;
设置方法;
partition by hash (store_id) partitions 4;
这里定义来4个分区,插入数据时会随机分配到其中一个分区中;
这样每个分区数据都相对平均,提高来查询效率;
六、KEY分区
类似于HASH分区
不同之处,创建分区时KEY分区可以不指定分区的key,KEY分区会自动选择主键/唯一键作为分区键,并且在没有主键/唯一键时,选择一个不为空的键作为分区键;
七、分区管理
创建:同上;
附加新分区:
alter table emp_1 add partition ( partition p3 values less than ('2120-09-30'));
删除分区:
alter table emp_1 drop partition p0;
拆分分区:
alter table emp_1 reorganize partition p3 into ( ->partition p2 values less than ('2017-09-30'), ->partition p3 values less than ('2117-09-30'), )
合并分区:
alter table emp_1 expenses partition p3,p4,p5 into ( ->partition p2 values less than ('2217-09-30'), )
增加分区数量(hash\key):
将分区数量附加8个
alter table emp_1 add partition partitions 8;
减少分区数量(hash\key):
减少2个分区
alter table emp_1 coalesce partition 2;