当mysql数据表量特别大的时候,仅仅依靠添加索引以及优化sql语句已经起不了明显的作用,表里超过百万条数据,但是又够不上分布式处理的级别,不需要分表处理的时候,这时候就涉及到mysql的一个功能叫做分区。注意:mysql5.6.2以上版本才能完整支持分区,以下的版本会有问题!
一、那什么是分区呢,简单的说就是:
1、将数据分段划分在多个位置存放,可以是同一块磁盘,也可以是不同的机器。
2、分区后,表面还是一张表,但是数据散列到多个位置
3、读写时候还是表名不变,数据库自动去组织分区的数据
下面举例说明,先建立一张新闻表news,包含下面几个字段、
id int //主键
news_title varchar(50) //新闻标题
news_content text //新闻内容
news_addtime timestamp //文章添加时间
然后插入一些测试数据
mysql中的分区有range,list,hash,key四种方式,这里介绍range方法,其他的可以融汇贯通,所谓range就是范围划分,譬如我们按照ID划分,对已有表进行分区语句语法如下:
ALTER TABLE 表名 PARTITION BY(字段名)
(
PARTITON 分区名 VALUES LESS THAN (字段值)
);
用news表举例
ALTER table news PARTITION by range(id)(
PARTITION news_a values less than (4),
PARTITION news_b values less than (7),
PARTITION news_c values less than (MAXVALUE)
);
上面语句的意思就是把news表分了三个区,第一个分区名称是news_a 区间是id<4的数据,第二个分区名称是news_b 区间是id<7的数据,第三个分区名称news_c,区间是剩下的小于最大id的数据
分区之后,会在存放表数据的文件夹多出分区个数的文件
select * from news partition(news_a); //从分区a中查询数据
新添加的数据会出现在最后一个分区
合并分区
alter table reorganize partition 分区名1,分区名2 into(
partition 分区名 values less than (xx)
);
删除分区
alter table news remove partitioning;
二、子分区
在建立了主分区之后,MYSQL还可以对每个主分区再进行分区,就是子分区,下面用一个例子进行说明:
例子:按年份,分类,搜索新闻
首页在news表中新添加一个分类字段
classid int //新闻分类
不用分区的sql语句应该是:select * from news where classid=@classid and year(addtime)=@year order by click desc;
下面开始分区处理
1、先按照classid划分主分区,用list方法
alter table news partition by list(classid)(
partition class_a values in(1),
partition class_b values in(2),
partition class_c values in(3),
);
此时执行语句会报错,原因是设置分区的字段必须要设置联合主键,所以我们把classid设置联合主键
2、在上一步的基础上根据年份划分子分区,先把addtime也设置成联合索引,具体语句如下:
alter table news partition by list(classid) subpartition by hash(year(addtime)) subpartiton 3(
partition class_a values in(1),
partition class_b values in(2),
partition class_c values in(3),
);
其中那个数字3指的是每个主分区下子分区的数量,比如有3个年份,这里就是数字3,有2个年份,就是数字2,这个数字要根据实际情况来定
3、分区划分好了,比如我们要取出主分区a下面的2014年份子分区下的数据,sql语言如下:
set @num = mod(2014,3);
select * from news partition(class_asp@num) order by click;