MySQL分区技术是用来减轻海量数据带来的负担,解决数据库性能下降问题的一种方式,其他的方式还有建立索引,大表拆小表等等。
MySQL分区按照分区的参考方式来分有RANGE分区、LIST分区、HASH分区、KEY分区。本文对这几种分区方式进行了详细的介绍,并且给出了简单的示例,文章简洁明了,对于想要初步了解MySQL分区技术的同学来说是很不错的参考材料。
三、案例分析
这个案例是针对有个员工、部门、部门经理、头衔和销售记录的模拟数据,其ER图如下所示,数据量大概有4百万左右。数据下载URL:https://launchpad.net/test-db
图11,案例分析
通过如上可知,对于同样的数据按照分区和不分区的技术分别存储,从而便于如下的查询性能分析和对比。对于salaries表,它采用RANGE分区,定义如下:
图12,案例分析
1,单表查询
从销售记录中找到1999年整年的销售记录有多少条,这个很简单,查询语句如下:
select count(*) from salaries s where s.from_date between"1999-01-01" and "1999-12-31" ;
那么对于分区前后的查询性能却有很大的差别:
图13,分区前后查询性能对比
通过如上可知,利用分区之后它只需扫描p16分区,访问的记录明显减少,所以性能自然有较大的提升:
图14,无采用分区技术和采用分区技术性能对比
2,单表查询的badcase
若现在有如下查询:
select count(*) from salaries s where year(s.from_date)=1999;
那么它是否能够利用到分区技术呢,答案是否定的。为什么呢,因为分区中的key是s.from_date,而不是year(s.from_date),mysql并不能很智能地判断year是1999的,那么它就是分为p16分区,这个可以通过如下的查询计划可以证实:
图15,未优化前的单表查询
也就是其实它访问了所有的分区,所以并没有很好地利用分区功能,将SQL改写如下:
select count(*) from salaries s where year between '1999-01-01' and'1999-12-31' ;
则查询计划如下:
图16,改进后的单表查询
可知,书写正确的SQL可以完全表现出两种相差特别大的性能。
3,连接查询
同样地,对于连接查询,在有没有分区的条件下,将有性能3倍左右的差距。对于更大的数据量,可能会有更大的性能差距。SQL如下:
select count(*) from salaries s left join employees e ons.emp_no=e.emp_no where s.from_date between '1999-01-01' and '1999-12-31' ;
图17,无采用分区和采用分区的性能对比
4,删除查询
为了删除1998年的销售数据,那么在有分区情况下可以不利用delete查询快速地完成垃圾数据的清理。
图18,删除查询性能对比
可知,对于有分区的情况下,只需要将某个分区删除掉即可,时间仅为0.05s,相对应原来的2.82s,这个提升是非常高的。当然,利用分区功能删除之后的数据文件信息如下:
图19,利用分区功能删除后的文件信息
那么接下来如果接着插入1998年的数据,数据是否丢失了呢?还是会写不进去?答案也都是否定,它会将数据写入p16分区中。有兴趣的读者可以自己收到试试。
四、总结和不足
分区的好处有很多:
1,与单个磁盘或文件系统分区相比,可以存储更多的数据;
2,对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据;
3,一些查询可以得到极大的优化,如where语句数据可以只保存在一个或多个分区内;
4,涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理;
5,通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
在设计分区过程中,需要考虑的因素有很多,如:
1,分区的列;
2,分区使用的函数,特别是非Integer类型的列;
3,服务器性能;
4,内存大小。
根据分区技术,有一些技巧:
1,若索引的大小> RAM,考虑选用分区,不采用索引;
2,尽量不采用Primary Key做分区的key;
3,当CPU性能高的时候,考虑使用Archive存储引擎;
4,对于大量的历史数据,考虑使用Archive+PARTITION。
总之,
1,MySQL分区技术是一种逻辑的水平分表技术;
2,它只访问需要访问的分区,从而提高性能;
3,支持range, hash, key, list和复合分区方法;
4,支持MySQL服务器所支持的任何存储引擎;
5,除了Key分区方法,Partition的key 必须是整数(或者能转化成整数)。