开发者社区> 技术小美> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL 5.1 分区技术初探(二)

简介:
+关注继续查看

MySQL分区技术是用来减轻海量数据带来的负担,解决数据库性能下降问题的一种方式,其他的方式还有建立索引,大表拆小表等等。

  MySQL分区按照分区的参考方式来分有RANGE分区、LIST分区、HASH分区、KEY分区。本文对这几种分区方式进行了详细的介绍,并且给出了简单的示例,文章简洁明了,对于想要初步了解MySQL分区技术的同学来说是很不错的参考材料。


三、案例分析

  这个案例是针对有个员工、部门、部门经理、头衔和销售记录的模拟数据,其ER图如下所示,数据量大概有4百万左右。数据下载URLhttps://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;

  那么它是否能够利用到分区技术呢,答案是否定的。为什么呢,因为分区中的keys.from_date,而不是year(s.from_date)mysql并不能很智能地判断year1999的,那么它就是分为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

  总之,

  1MySQL分区技术是一种逻辑的水平分表技术;

  2,它只访问需要访问的分区,从而提高性能;

  3,支持range, hash, key, list和复合分区方法;

  4,支持MySQL服务器所支持的任何存储引擎;

  5,除了Key分区方法,Partitionkey 必须是整数(或者能转化成整数)

 

 
















本文转自百度技术51CTO博客,原文链接:http://blog.51cto.com/baidutech/748627,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
WGCLOUD的通信协议是什么
WGCLOUD监控系统的agent和server通信协议,使用的是简单灵活的http协议
27 0
英特尔持续推动NAND技术创新,引领数据中心QLC SSD普及浪潮
2021中国闪存市场峰会(CFMS)9月14号在深圳成功举行,今年主题为 “存储标准 - 存储生态”。这场高标准市场化运作的峰会,得到众多产业链核心厂商的积极响应和大力支持,英特尔、三星、美光、铠侠、长江存储等企业悉数到场。英特尔NPSG亚太区销售总监倪锦峰在会上发表了“英特尔推动NAND技术创新,释放存储潜力”的主题演讲,承诺英特尔会继续加强对NAND技术的投入,并且通过今年新发布的D5-P5316新一代QLC SSD,推动QLC技术在数据中心的普及。
74 0
国密SSL协议之C语言编程
OpenSSL支持标准的SSL协议,但并不支持国密SSL协议。本文描述了C语言使用国密版OpenSSL开发一个简单的客户端程序,连接国密Web网站,发送HTTP请求,并接收HTTP应答。
564 0
动态无线接入网络 | 《5G移动无线通信技术》之九
本节主要讲述了动态无线接入网络。动态无线接入(DyRAN)以动态的方式集成了所有元素,成为多无线接入技术环境。
1139 0
《通信技术导论(原书第5版)》——1.8 协议和分层
本节书摘来自华章出版社《通信技术导论(原书第5版)》一书中的第1章,第1.8节,作者:[美]安娜贝尔Z.多德(Annabel Z. Dodd)著 唐艳华 张选涛 译 赵志 审校,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1150 0
【MySQL】MySQL 5.7 初探
写在前面   MySQL 5.7版本于2015年10月份左右 GA,至今已经半年多了,但自己一直没有时间来follow MySQL 5.7 新的特性,作为MySQL DBA 实在汗颜,以后会花时间来研究5.7 版本的特性并针对部分优化功能做出压力测试。
1041 0
+关注
6819
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载