影响mysql性能因素.docx
- 文档编号:24474980
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:35
- 大小:34.91KB
影响mysql性能因素.docx
《影响mysql性能因素.docx》由会员分享,可在线阅读,更多相关《影响mysql性能因素.docx(35页珍藏版)》请在冰豆网上搜索。
影响mysql性能因素
影响MySQLServer性能的相关因素
商业需求对性能的影响
应用系统中的每一个功能在设计初衷肯定都是出于为用户提供某种服务,或者满足用户的某种需求,但是,并不是每一个功能在最后都能很成功,甚至有些功能的推出可能在整个系统中是画蛇添足。
不仅没有为用户提高任何体验度,也没有为用户改进多少功能易用性,反而在整个系统中成为一个累赘,带来资源的浪费。
不合理需求造成资源投入产出比过低需求是否合理很多时候可能并不是很容易界定,尤其是作为技术人员来说,可能更难以确定一个需求的合理性。
即使指出,也不一定会被产品经历们认可。
那作为技术人员的我们怎么来证明一个需求是否合理呢?
第一、每次产品经理们提出新的项目(或者功能需求)的时候,应该要求他们同时给出该项目的预期收益的量化指标,以备项目上先后统计评估投入产出比率;
第二、在每次项目进行过程中,应该详细记录所有的资源投入,包括人力投入,硬件设施的投入,以及其他任何项目相关的资源投入;
第三、项目(或者功能需求)上线之后应该及时通过手机相关数据统计出项目的实际收益值,以便计算投入产出比率的时候使用;
第四、技术部门应该尽可能推动设计出一个项目(或者功能需求)的投入产出比率的计算规则。
在项目上线一段时间之后,通过项目实际收益的统计数据和项目的投入资源量,计算出整个项目的实际投入产出值,并公布给所有参与项目的部门知晓,同时存放以备后查。
有了实际的投入产出比率,我们就可以和项目立项之初产品经理们的预期投入产出比率做出比较,判定出这个项目做的是否值得。
而且当积累了较多的项目投入产出比率之后,我们可以根据历史数据分析出一个项目合理的投入产出比率应该是多少。
这样,在项目立项之初,我们就可以判定出产品经理们的预期投入产出比率是否合理,项目是否真的有进行的必要。
有了实际的投入产出比率之后,我们还可以拿出数据给老板们看,让他知道功能并不是越多越好,让他知道有些功能是应该撤下来的,即使撤下该功能可能需要投入不少资源。
实际上,一般来说,在产品开发及运营部门内部都会做上面所说的这些事情的。
但很多时候可能更多只是一种形式化的过程。
在有些比较规范的公司可能也完成了上面的大部分流程,但是要么数据不公开,要么公开给其他部门的数据存在一定的偏差,不具备真实性。
为什么会这样?
其实就一个原因,就是部门之间的利益冲突及业绩冲突问题。
产品经理们总是希望尽可能的让用户觉得自己设计的产品功能齐全,让老板觉得自己做了很多事情。
但是从来都不会去关心因为做一个功能所带来的成本投入,或者说是不会特别的关心这一点。
而且很多时候他们也并不能太理解技术方面带来的复杂度给产品本身带来的负面影响。
这里我们就拿一个看上去很简单的功能来分析一下。
需求:
一个论坛帖子总量的统计
附加要求:
实时更新在很多人看来,这个功能非常容易实现,不就是执行一条SELECTCOUNT(*)的Query就可以得到结果了么?
是的,确实只需要如此简单的一个Query就可以得到结果。
但是,如果我们采用不是MyISAM存储引擎,而是使用的Innodb的存储引擎,那么大家可以试想一下,如果存放帖子的表中已经有上千万的帖子的时候,执行这条Query语句需要多少成本?
恐怕再好的硬件设备,恐怕都不可能在10秒之内完成一次查询吧。
如果我们的访问量再大一点,还有人觉得这是一件简单的事情么?
既然这样查询不行,那我们是不是该专门为这个功能建一个表,就只有一个字段,一条记录,就存放这个统计量,每次有新的帖子产生的时候,都将这个值增加1,这样我们每次都只需要查询这个表就可以得到结果了,这个效率肯定能够满足要求了。
确实,查询效率肯定能够满足要求,可是如果我们的系统帖子产生很快,在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候,恐怕这个统计表又要成为大家的噩梦了。
要么因为并发的问题造成统计结果的不准确,要么因为锁资源争用严重造成整体性能的大幅度下降。
其实这里问题的焦点不应该是实现这个功能的技术细节,而是在于这个功能的附加要求“实时更新”上面。
当一个论坛的帖子数量很大了之后,到底有多少人会关注这个统计数据是否是实时变化的?
有多少人在乎这个数据在短时间内的不精确性?
我想恐怕不会有人会傻傻的盯着这个统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加1吧?
即使明明白白的告诉用户这个统计数据是每过多长时间段更新一次,那有怎样?
难道会有很多用户就此很不爽么?
只要去掉了这个“实时更新”的附加条件,我们就可以非常容易的实现这个功能了。
就像之前所提到的那样,通过创建一个统计表,然后通过一个定时任务每隔一定时间段去更新一次里面的统计值,这样既可以解决统计值查询的效率问题,又可以保证不影响新发贴的效率,一举两得。
实际上,在我们应用的系统中还有很多很多类似的功能点可以优化。
如某些场合的列表页面参与列表的数据量达到一个数量级之后,完全可以不用准确的显示这个列表总共有多少条信息,总共分了多少页,而只需要一个大概的估计值或者一个时间段之前的统计值。
这样就省略了我们的分页程序需要在分以前实时COUNT出满足条件的记录数。
其实,在很多应用系统中,实时和准实时,精确与基本准确,在很多地方所带来的性能消耗可能是几个性能的差别。
在系统性能优化中,应该尽量分析出那些可以不实时和不完全精确的地方,作出一些相应的调整,可能会给大家带来意想不到的巨大性能提升。
无用功能堆积使系统过度复杂影响整体性能很多时候,为系统增加某个功能可能并不需要花费太多的成本,而要想将一个已经运行了一段时间的功能从原有系统中撤下来却是非常困难的。
首先,对于开发部门,可能要重新整理很多的代码,找出可能存在与增加该功能所编写的代码有交集的其他功能点,删除没有关联的代码,修改有关联的代码;
其次,对于测试部门,由于功能的变动,必须要回归测试所有相关的功能点是否正常。
可能由于界定困难,不得不将回归范围扩展到很大,测试工作量也很大。
最后,所有与撤除下线某个功能相关的工作参与者来说,又无法带来任何实质性的收益,而恰恰相反是,带来的只可能是风险。
由于上面的这几个因素,可能很少有公司能够有很完善的项目(或者功能)下线机制,也很少有公司能做到及时将系统中某些不合适的功能下线。
所以,我们所面对的应用系统可能总是越来越复杂,越来越庞大,短期内的复杂可能并无太大问题,但是随着时间的积累,我们所面对的系统就会变得极其臃肿。
不仅维护困难,性能也会越来越差。
尤其是有些并不合理的功能,在设计之初或者是刚上线的时候由于数据量较小,带来不了多少性能损耗。
可随着时间的推移,数据库中的数据量越来越大,数据检索越来越困难,对真个系统带来的资源消耗也就越来越大。
而且,由于系统复杂度的不断增加,给后续其他功能的开发带来实现的复杂度,可能很多本来很简单的功能,因为系统的复杂而不得不增加很多的逻辑判断,造成系统应用程序的计算量不断增加,本身性能就会受到影响。
而如果这些逻辑判断还需要与数据库交互通过持久化的数据来完成的话,所带来的性能损失就更大,对整个系统的性能影响也就更大了。
系统架构及实现对性能的影响
一个WEB应用系统,自然离不开Web应用程序(WebApp)和应用程序服务器(AppServer)。
AppServer我们能控制的内容不多,大多都是使用已经久经考验的成熟产品,大家能做的也就只是通过一些简单的参数设置调整来进行调优,不做细究。
而WebApp大部分都是各自公司根据业务需求自行开发,可控性要好很多。
所以我们从Web应用程序着手分析一个应用程序架构的不同设计对整个系统性能的影响将会更合适。
商业需求告诉了我们一个系统应该有什么不应该有什么,系统架构则则决定了我们系统的
构建环境。
就像修建一栋房子一样,在清楚了这栋房子的用途之后,会先有建筑设计师来画出一章基本的造型图,然后还需要结构设计师为我们设计出结构图。
系统架构设计的过程就和结构工程好似设计结构图一样,需要为整个系统搭建出一个尽可能最优的框架,让整个系统能够有一个稳定高效的结构体系让我们实现各种商业需求。
谈到应用系统架构的设计,可能有人的心里会开始嘀咕,一个DBA有什么资格谈论人家架构师(或者程序员)所设计的架构?
其实大家完全没有必要这样去考虑,我们谈论架构只是分析各种情形下的性能消耗区别,仅仅是根据自己的专业特长来针对相应架构给出我们的建议及意见,并不是要批判架构整体的好坏,更不是为了推翻某个架构。
而且我们所考虑的架构大多数时候也只是数据层面相关的架构。
我们数据库中存放的数据都是适合在数据库中存放的吗?
对于有些开发人员来说,数据库就是一个操作最方便的万能存储中心,希望什么数据都存放在数据库中,不论是需要持久化的数据,还是临时存放的过程数据,不论是普通的纯文本格式的字符数据,还是多媒体的二进制数据,都喜欢全部塞如数据库中。
因为对于应用服务器来说,数据库很多时候都是一个集中式的存储环境,不像应用服务器那样可能有很多台;而且数据库有专门的DBA去帮忙维护,而不像应用服务器很多时候还需要开发人员去做一些维护;还有一点很关键的就是数据库的操作非常简单统一,不像文件操作或者其他类型的存储方式那么复杂。
其实我个人认为,现在的很多数据库为我们提供了太多的功能,反而让很多并不是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能,最后却全部怪罪到数据库身上。
1.二进制多媒体数据
将二进制多媒体数据存放在数据库中,一个问题是数据库空间资源耗用非常严重,另一个问题是这些数据的存储很消耗数据库主机的CPU资源。
这种数据主要包括图片,音频、视频和其他一些相关的二进制文件。
这些数据的处理本不是数据的优势,如果我们硬要将他们塞入数据库,肯定会造成数据库的处理资源消耗严重。
2.流水队列数据
我们都知道,数据库为了保证事务的安全性(支持事务的存储引擎)以及可恢复性,都是需要记录所有变更的日志信息的。
而流水队列数据的用途就决定了存放这种数据的表中的数据会不断的被INSERT,UPDATE和DELETE,而每一个操作都会生成与之对应的日志信息。
在MySQL中,如果是支持事务的存储引擎,这个日志的产生量更是要翻倍。
而如果我们通过一些成熟的第三方队列软件来实现这个Queue数据的处理功能,性能将会成倍的提升。
3.超大文本数据
对于5.0.3之前的MySQL版本,VARCHAR类型的数据最长只能存放255个字节,如果需要存储更长的文本数据到一个字段,我们就必须使用TEXT类型(最大可存放64KB)的字段,甚至是更大的LONGTEXT类型(最大4GB)。
而TEXT类型数据的处理性能要远比VARCHAR类型数据的处理性能低下很多。
从5.0.3版本开始,VARCHAR类型的最大长度被调整到64KB了,但是当实际数据小于255Bytes的时候,实际存储空间和实际的数据长度一样,可一旦长度超过255Bytes之后,所占用的存储空间就是实际数据长度的两倍。
所以,超大文本数据存放在数据库中不仅会带来性能低下的问题,还会带来空间占用的浪费问题。
是否合理的利用了应用层Cache机制?
对于Web应用,活跃数据的数据量总是不会特别的大,有些活跃数据更是很少变化。
对于这类数据,我们是否有必要每次需要的时候都到数据库中去查询呢?
如果我们能够将变化相对较少的部分活跃数据通过应用层的Cache机制Cache到内存中,对性能的提升肯定是成数量级的,而且由于是活跃数据,对系统整体的性能影响也会很大。
当然,通过Cache机制成功的案例数不胜数,但是失败的案例也同样并不少见。
如何合理的通过Cache技术让系统性能得到较大的提升也不是通过寥寥几笔就能说明的清楚,这里我仅根据以往的经验列举一下什么样的数据适合通过Cache技术来提高系统性能:
1.系统各种配置及规则数据;
由于这些配置信息变动的频率非常低,访问概率又很高,所以非常适合存使用Cache;
2.活跃用户的基本信息数据;
虽然我们经常会听到某某网站的用户量达到成百上千万,但是很少有系统的活跃用户量能够都达到这个数量级。
也很少有用户每天没事干去将自己的基本信息改来改去。
更为重要的一点是用户的基本信息在应用系统中的访问频率极其频繁。
所以用户基本信息的Cache,很容易让整个应用系统的性能出现一个质的提升。
3.活跃用户的个性化定制信息数据;
虽然用户个性化定制的数据从访问频率来看,可能并没有用户的基本信息那么的频繁,但相对于系统整体来说,也占了很大的比例,而且变更皮律一样不会太多。
从Ebay的PayPal通过MySQL的Memory存储引擎实现用户个性化定制数据的成功案例我们就能看出对这部分信息进行Cache的价值了。
虽然通过MySQL的Memory存储引擎并不像我们传统意义层面的Cache机制,但正是对Cache技术的合理利用和扩充造就了项目整体的成功。
4.准实时的统计信息数据;
所谓准实时的统计数据,实际上就是基于时间段的统计数据。
这种数据不会实时更新,也很少需要增量更新,只有当达到重新Build该统计数据的时候需要做一次全量更新操作。
虽然这种数据即使通过数据库来读取效率可能也会比较高,但是执行频率很高之后,同样会消耗不少资源。
既然数据库服务器的资源非常珍贵,我们为什么不能放在应用相关的内存Cache中呢?
5.其他一些访问频繁但变更较少的数据;
出了上面这四种数据之外,在我们面对的各种系统环境中肯定还会有各种各样的变更较少但是访问很频繁的数据。
只要合适,我们都可以将对他们的访问从数据库移到Cache中。
我们的数据层实现都是最精简的吗?
从以往的经验来看,一个合理的数据存取实现和一个拙劣的实现相比,在性能方面的差异经常会超出一个甚至几个数量级。
我们先来分析一个非常简单且经常会遇到类似情况的示例:
在我们的示例网站系统中,现在要实现每个用户查看各自相册列表(假设每个列表显示10张相片)的时候,能够在相片名称后面显示该相片的留言数量。
这个需求大家认为应该如何实现呢?
我想90%的开发开发工程师会通过如下两步来实现该需求:
1、通过“SELECTid,subject,urlFROMphotoWHEREuser_id=?
limit10”得到第一页的相片相关信息;
2、通过第1步结果集中的10个相片id循环运行十次“SELECTCOUNT(*)FROMphoto_commentWHEREphoth_id=?
”来得到每张相册的回复数量然后再瓶装展现对象
此外可能还有部分人想到了如下的方案:
1、和上面完全一样的操作步骤;
2、通过程序拼装上面得到的10个photo的id,再通过in查询“SELECTphoto_id,count(*)FROMphoto_commentWHEREphoto_idin(?
)GROUPBYphoto_id”一次得到10个photo的所有回复数量,再组装两个结果集得到展现对象。
我们来对以上两个方案做一下简单的比较:
1、从MySQL执行的SQL数量来看,第一种解决方案为11(1+10=11)条SQL语句,第二种解决方案为2条SQL语句(1+1);
2、从应用程序与数据库交互来看,第一种为11次,第二种为2次;
3、从数据库的IO操作来看,简单假设每次SQL为1个IO,第一种最少11次IO,第二种小于等于11次IO,而且只有当数据非常之离散的情况下才会需要11次;
4、从数据库处理的查询复杂度来看,第一种为两类很简单的查询,第二种有一条SQL语句有GROUPBY操作,比第一种解决方案增加了了排序分组操作;
5、从应用程序结果集处理来看,第一种11次结果集的处理,第二中2次结果集的处理,但是第二种解决方案中第二词结果处理数量是第一次的10倍;
6、从应用程序数据处理来看,第二种比第一种多了一个拼装photo_id的过程。
我们先从以上6点来做一个性能消耗的分析:
1、由于MySQL对客户端每次提交的SQL不管是相同还是不同,都需要进行完全解析,这个动作主要消耗的资源是数据库主机的CPU,那么这里第一种方案和第二种方案消耗CPU的比例是11:
2。
SQL语句的解析动作在整个SQL语句执行过程中的整体消耗的CPU比例是较多的;
2、应用程序与数据库交互所消耗的资源基本上都在网络方面,同样也是11:
2;
3、数据库IO操作资源消耗为小于或者等于1:
1;
4、第二种解决方案需要比第一种多消耗内存资源进行排序分组操作,由于数据量不大,多出的消耗在语句整体消耗中占用比例会比较小,大概不会超过20%,大家可以针对性测试;
5、结果集处理次数也为11:
2,但是第二中解决方案第二次处理数量较大,整体来说两次的性能消耗区别不大;
6、应用程序数据处理方面所多出的这个photo_id的拼装所消耗的资源是非常小的,甚至比应用程序与MySQL做一次简单的交互所消耗的资源还要少
综合上面的这6点比较,我们可以很容易得出结论,从整体资源消耗来看,第二中方案会远远优于第一种解决方案。
而在实际开发过程中,我们的程序员却很少选用。
主要原因其实有两个,一个是第二种方案在程序代码实现方面可能会比第一种方案略为复杂,尤其是在当前编程环境中面向对象思想的普及,开发工程师可能会更习惯于以对象为中心的思考方式来解决问题。
还有一个原因就是我们的程序员可能对SQL语句的使用并不是特别的熟悉,并不一定能够想到第二条SQL语句所实现的功能。
对于第一个原因,我们可能只能通过加强开发工程师的性能优化意识来让大家能够自觉纠正,而第二个原因的解决就正是需要我们出马的时候了。
SQL语句正是我们的专长,定期对开发工程师进行一些相应的数据库知
识包括SQL语句方面的优化培训,可能会给大家带来意想不到的收获的。
这里我们还仅仅只是通过一个很长见的简单示例来说明数据层架构实现的区别对整体性能的影响,
实际上可以简单的归结为过渡依赖嵌套循环的使用或者说是过渡弱化SQL语句的功能造成性能消耗过多的实例。
后面我将进一步分析一下更多的因为架构实现差异所带来的性能消耗差异。
过度依赖数据库SQL语句的功能造成数据库操作效率低下
前面的案例是开发工程师过渡弱化SQL语句的功能造成的资源浪费案例,而这里我们再来分析一个完全相反的案例:
在群组简介页面需要显示群名称和简介,每个群成员的nick_name,以及群主的个人签名信息。
需求中所需信息存放在以下四个表中:
user,user_profile,groups,user_group
我们先看看最简单的实现方法,一条SQL语句搞定所有事情:
SELECTname,description,user_type,nick_name,sign
FROMgroups,user_group,user,user_profile
WHEREgroups.id=?
ANDgroups.id=user_group.group_id
ANDuser_group.user_id=user.id
ANDuser_profile.user_id=user.id
当然我们也可以通过如下稍微复杂一点的方法分两步搞定:
首先取得所有需要展示的group的相关信息和所有群组员的nick_name信息和组员类别:
当然我们也可以通过如下稍微复杂一点的方法分两步搞定:
首先取得所有需要展示的group的相关信息和所有群组员的nick_name信息和组员类别:
SELECTname,description,user_type,nick_name
FROMgroups,user_group,user
WHEREgroups.id=?
ANDgroups.id=user_group.group_id
ANDuser_group.user_id=user.id
然后在程序中通过上面结果集中的user_type找到群主的user_id再到user_profile表中取得群主的签名信息:
SELECTsignFROMuser_profileWHEREuser_id=?
重复执行相同的SQL造成资源浪费
这个问题其实是每个人都非常清楚也完全认同的一个问题,但是在应用系统开发过程中,仍然会常有这样的现象存在。
究其原因,主要还是开发工程师思维中面向对象的概念太过深入,以及为了减少自己代码开发的逻辑和对程序接口过度依赖所造成的。
我曾经在一个性能优化项目中遇到过一个案例,某个功能页面一侧是“分组”列表,是一列“分组”的名字。
页面主要内容则是该“分组”的所有“项目”列表。
每个“项目”以名称(或者图标)显示,同时还有一个SEO相关的需求就是每个“项目”名称的链接地址中是需要有“分组”的名称的。
所以在“项目”列表的每个“项目”的展示内容中就需要得到该项目所属的组的名称。
按照开发工程师开发思路,非常容易产生取得所有“项目”结果集并映射成相应对象之后,再从对象集中获取“项目”所属组的标识字段,然后循环到“分组”表中取得需要的”组名“。
然后再将拼装成展示对象。
看到这里,我想大家应该已经知道这里存在的一个最大的问题就是多次重复执行了完全相同的SQL得到完全相同的内容。
同时还犯了前面第一个案例中所犯的错误。
或许大家看到之后会不相信有这样的案例存在,我可以非常肯定的告诉大家,事实就是这样。
同时也请大家如果有条件的话,好好Review自
己所在的系统的代码,非常有可能同样存在上面类似的情形还有部分解决方案要远优于上面的做法,那就是不循环去取了,而是通过Join一次完成,也就是解决了第一个案例所描述的性能问题。
但是又误入了类似于第二个案例所描述的陷阱中了,为实际上他只需要一次查询就可以得到所有“项目”所属的“分组”的名称(所有项目都是同一个组的)。
当然,也有部分解决方案也避免了第二个案例的问题,分为两条SQL,两步完成了这个需求。
这样在性能上面基本上也将近是数量级的提升了。
但是这就是性能最优的解决方案了么?
不是的,我们甚至可以连一次都不需要访问就获得所需要的“分组”名称。
首先,侧栏中的“分组”列表是需要有名称的,我们为什么不能直接利用到呢?
当然,可能有些系统的架构决定了侧栏和主要内容显示区来源于不同的模板(或者其他结构),那么我们也完全可以通过在进入这个功能页面的链接请求中通过参数传入我们需要的“分组”名称。
这样我们就可以完全不需要根据“项目”相关信息去数据库获取所属“分组”的信息,就可以完成相应需求了。
当然,是否需要通过请求参数来节省最后的这一次访问,可能会根据这个功能页面的PV来决定,如果访问并不是非常频繁,那么这个节省可能并不是很明显,而应用系统的复杂度却有所增加,而且程序看上去可能也会不够优雅,但是如果访问非常频繁的场景中,所节省的资源还是比较可观的。
上面还仅仅只是列举了我们平时比较常见的一些实现差异对性能所带来的影响,除了这些实现方面所带来的问题之外,应用系统的整体架构实现设计对系统性能的影响可能会更严重。
下面大概列举了一些较为常见的架构设计实现不当带来的性能问题和资源浪费情况。
1、Cache系统的不合理利用导致Cache命中率低下造成数据库访问量的增加,同时也浪费了Cache系统的硬件资源投入;
2、过度依赖面向对象思想,对系统
3、对可扩展性的过渡追求,促使系统设计的时候将对象拆得过于离散,造成系统中大量的复杂Join语句,而MySQLServer在各数据库系统中的主要优势在于处理简单逻辑的查询,这与其锁定的机制也有较大关系;
4、对数据库的过渡依赖,将大量更适合存放于文件系统中的数据存入了数据库中,造成数据库资源的浪费,影响到系统
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 影响 mysql 性能 因素