第6章 影响 MySQL Server 性能的相关因素

前言：
　　大部分人都一致认为一个数据库应用系统（这里的数据库应用系统概指所有使用数据库的系统）的
性能瓶颈最容易出现在数据的操作方面，而数据库应用系统的大部分数据操作都是通过数据库管理软件
所提供的相关接口来完成的。所以数据库管理软件也就很自然的成为了数据库应用系统的性能瓶颈所
在，这是当前业界比较普遍的一个看法。但我们的应用系统的性能瓶颈真的完全是因为数据库管理软件
和数据库主机自身造成的吗？我们将通过本章的内容来进行一个较为深入的分析，让大家了解到一个数
据库应用系统的性能到底与哪些地方有关，让大家寻找出各自应用系统的出现性能问题的根本原因，而
尽可能清楚的知道该如何去优化自己的应用系统。
　　考虑到本书的数据库对象是MySQL，而MySQL 最多的使用场景是WEB 应用，那么我们就以一个WEB 应
用系统为例，逐个分析其系统构成，结合笔者在大型互联网公司从事DBA 工作多年的经验总结，分析出
数据库应用系统中各个环境对性能的影响。

6.1 商业需求对性能的影响

应用系统中的每一个功能在设计初衷肯定都是出于为用户提供某种服务，或者满足用户的某种需
求，但是，并不是每一个功能在最后都能很成功，甚至有些功能的推出可能在整个系统中是画蛇添足。
不仅没有为用户提高任何体验度，也没有为用户改进多少功能易用性，反而在整个系统中成为一个累
赘，带来资源的浪费。
　　不合理需求造成资源投入产出比过低
　　需求是否合理很多时候可能并不是很容易界定，尤其是作为技术人员来说，可能更难以确定一个需
求的合理性。即使指出，也不一定会被产品经历们认可。那作为技术人员的我们怎么来证明一个需求是
否合理呢？

第一、每次产品经理们提出新的项目（或者功能需求）的时候，应该要求他们同时给出该项目的预
期收益的量化指标，以备项目上先后统计评估投入产出比率；
第二、在每次项目进行过程中，应该详细记录所有的资源投入，包括人力投入，硬件设施的投入，
以及其他任何项目相关的资源投入；
第三、项目（或者功能需求）上线之后应该及时通过手机相关数据统计出项目的实际收益值，以便
计算投入产出比率的时候使用；
第四、技术部门应该尽可能推动设计出一个项目（或者功能需求）的投入产出比率的计算规则。在
项目上线一段时间之后，通过项目实际收益的统计数据和项目的投入资源量，计算出整个项目的实际投
入产出值，并公布给所有参与项目的部门知晓，同时存放以备后查。
　　有了实际的投入产出比率，我们就可以和项目立项之初产品经理们的预期投入产出比率做出比较，
判定出这个项目做的是否值得。而且当积累了较多的项目投入产出比率之后，我们可以根据历史数据分
析出一个项目合理的投入产出比率应该是多少。这样，在项目立项之初，我们就可以判定出产品经理们
的预期投入产出比率是否合理，项目是否真的有进行的必要。
　　有了实际的投入产出比率之后，我们还可以拿出数据给老板们看，让他知道功能并不是越多越好，

让他知道有些功能是应该撤下来的，即使撤下该功能可能需要投入不少资源。

实际上，一般来说，在产品开发及运营部门内部都会做上面所说的这些事情的。但很多时候可能更
多只是一种形式化的过程。在有些比较规范的公司可能也完成了上面的大部分流程，但是要么数据不公
开，要么公开给其他部门的数据存在一定的偏差，不具备真实性。
　　为什么会这样？其实就一个原因，就是部门之间的利益冲突及业绩冲突问题。产品经理们总是希望
尽可能的让用户觉得自己设计的产品功能齐全，让老板觉得自己做了很多事情。但是从来都不会去关心
因为做一个功能所带来的成本投入，或者说是不会特别的关心这一点。而且很多时候他们也并不能太理
解技术方面带来的复杂度给产品本身带来的负面影响。
　　这里我们就拿一个看上去很简单的功能来分析一下。
需求：一个论坛帖子总量的统计

　　其实，在很多应用系统中，实时和准实时，精确与基本准确，在很多地方所带来的性能消耗可能是
几个性能的差别。在系统性能优化中，应该尽量分析出那些可以不实时和不完全精确的地方，作出一些
相应的调整，可能会给大家带来意想不到的巨大性能提升。
　　无用功能堆积使系统过度复杂影响整体性能

很多时候，为系统增加某个功能可能并不需要花费太多的成本，而要想将一个已经运行了一段时间
的功能从原有系统中撤下来却是非常困难的。
　　首先，对于开发部门，可能要重新整理很多的代码，找出可能存在与增加该功能所编写的代码有交
集的其他功能点，删除没有关联的代码，修改有关联的代码；
　　其次，对于测试部门，由于功能的变动，必须要回归测试所有相关的功能点是否正常。可能由于界
定困难，不得不将回归范围扩展到很大，测试工作量也很大。
　　最后，所有与撤除下线某个功能相关的工作参与者来说，又无法带来任何实质性的收益，而恰恰相
反是，带来的只可能是风险。

　　由于上面的这几个因素，可能很少有公司能够有很完善的项目（或者功能）下线机制，也很少有公
司能做到及时将系统中某些不合适的功能下线。所以，我们所面对的应用系统可能总是越来越复杂，越
来越庞大，短期内的复杂可能并无太大问题，但是随着时间的积累，我们所面对的系统就会变得极其臃
肿。不仅维护困难，性能也会越来越差。尤其是有些并不合理的功能，在设计之初或者是刚上线的时候
由于数据量较小，带来不了多少性能损耗。可随着时间的推移，数据库中的数据量越来越大，数据检索
越来越困难，对真个系统带来的资源消耗也就越来越大。
　　而且，由于系统复杂度的不断增加，给后续其他功能的开发带来实现的复杂度，可能很多本来很简
单的功能，因为系统的复杂而不得不增加很多的逻辑判断，造成系统应用程序的计算量不断增加，本身
性能就会受到影响。而如果这些逻辑判断还需要与数据库交互通过持久化的数据来完成的话，所带来的
性能损失就更大，对整个系统的性能影响也就更大了。

6.2 系统架构及实现对性能的影响

　　上一节中商业需求告诉了我们一个系统应该有什么不应该有什么，系统架构则则决定了我们系统的
构建环境。就像修建一栋房子一样，在清楚了这栋房子的用途之后，会先有建筑设计师来画出一章基本
的造型图，然后还需要结构设计师为我们设计出结构图。系统架构设计的过程就和结构工程好似设计结
构图一样，需要为整个系统搭建出一个尽可能最优的框架，让整个系统能够有一个稳定高效的结构体系
让我们实现各种商业需求。

　　实际上，以下几类数据都是不适合在数据库中存放的：
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 了，但是当实际数据小于255
Bytes 的时候，实际存储空间和实际的数据长度一样，可一旦长度超过255 Bytes 之后，所占用的存
储空间就是实际数据长度的两倍。
　　所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问
题。

是否合理的利用了应用层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、通过“SELECT id,subject,url FROM photo WHERE user_id = ? limit 10” 得到第一页的相片
相关信息；
2、通过第1 步结果集中的10 个相片id 循环运行十次“SELECT COUNT(*) FROM photo_comment
WHERE photh_id = ?” 来得到每张相册的回复数量然后再瓶装展现对象。

　　此外可能还有部分人想到了如下的方案：
1、和上面完全一样的操作步骤；
2、通过程序拼装上面得到的10 个photo 的id，再通过in 查询“SELECT photo_id,count(*) FROM
photo_comment WHERE photo_id in (?) GROUP BY photo_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 语句有GROUP
BY 操作，比第一种解决方案增加了了排序分组操作；
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 语句搞定所有事情：

SELECT name,description,user_type,nick_name,sign
FROM groups,user_group,user ,user_profile
WHERE groups.id = ?
AND groups.id = user_group.group_id
AND user_group.user_id = user.id
AND user_profile.user_id = user.id
当然我们也可以通过如下稍微复杂一点的方法分两步搞定：
首先取得所有需要展示的group 的相关信息和所有群组员的nick_name 信息和组员类别：
SELECT name,description,user_type,nick_name
FROM groups,user_group,user
WHERE groups.id = ?
AND groups.id = user_group.group_id
AND user_group.user_id = user.id
然后在程序中通过上面结果集中的user_type 找到群主的user_id 再到user_profile 表中取得群主
的签名信息：

SELECT sign FROM user_profile WHERE user_id = ?

　　大家应该能够看出两者的区别吧，两种解决方案最大的区别在于交互次数和SQL 复杂度。而带来的
实际影响是第一种解决方案对user_profile 表有不必要的访问（非群主的profile 信息），造成IO 访问
的直接增加在20%左右。而大家都知道，IO 操作在数据库应用系统中是非常昂贵的资源。尤其是当这个
功能的PV 较大的时候，第一种方案造成的IO 损失是相当大的。

 

　　重复执行相同的SQL 造成资源浪费
这个问题其实是每个人都非常清楚也完全认同的一个问题，但是在应用系统开发过程中，仍然会常
有这样的现象存在。究其原因，主要还是开发工程师思维中面向对象的概念太过深入，以及为了减少自
己代码开发的逻辑和对程序接口过度依赖所造成的。

　　我曾经在一个性能优化项目中遇到过一个案例，某个功能页面一侧是“分组”列表，是一列“分
组”的名字。页面主要内容则是该“分组”的所有“项目”列表。每个“项目”以名称（或者图标）显
示，同时还有一个SEO 相关的需求就是每个“项目”名称的链接地址中是需要有“分组”的名称的。所
以在“项目”列表的每个“项目”的展示内容中就需要得到该项目所属的组的名称。按照开发工程师开
发思路，非常容易产生取得所有“项目”结果集并映射成相应对象之后，再从对象集中获取“项目”所
属组的标识字段，然后循环到“分组”表中取得需要的”组名“。然后再将拼装成展示对象。

　　看到这里，我想大家应该已经知道这里存在的一个最大的问题就是多次重复执行了完全相同的SQL
得到完全相同的内容。同时还犯了前面第一个案例中所犯的错误。或许大家看到之后会不相信有这样的
案例存在，我可以非常肯定的告诉大家，事实就是这样。同时也请大家如果有条件的话，好好Review 自
己所在的系统的代码，非常有可能同样存在上面类似的情形。

　　还有部分解决方案要远优于上面的做法，那就是不循环去取了，而是通过Join 一次完成，也就是解
决了第一个案例所描述的性能问题。但是又误入了类似于第二个案例所描述的陷阱中了，因为实际上他
只需要一次查询就可以得到所有“项目”所属的“分组”的名称（所有项目都是同一个组的）。

　　当然，也有部分解决方案也避免了第二个案例的问题，分为两条SQL，两步完成了这个需求。这样在
性能上面基本上也将近是数量级的提升了。

　　但是这就是性能最优的解决方案了么？不是的，我们甚至可以连一次都不需要访问就获得所需要的
“分组”名称。首先，侧栏中的“分组”列表是需要有名称的，我们为什么不能直接利用到呢？

　　当然，可能有些系统的架构决定了侧栏和主要内容显示区来源于不同的模板（或者其他结构），那
么我们也完全可以通过在进入这个功能页面的链接请求中通过参数传入我们需要的“分组”名称。这样
我们就可以完全不需要根据“项目”相关信息去数据库获取所属“分组”的信息，就可以完成相应需求
了。当然，是否需要通过请求参数来节省最后的这一次访问，可能会根据这个功能页面的PV 来决定，如
果访问并不是非常频繁，那么这个节省可能并不是很明显，而应用系统的复杂度却有所增加，而且程序
看上去可能也会不够优雅，但是如果访问非常频繁的场景中，所节省的资源还是比较可观的。
　　上面还仅仅只是列举了我们平时比较常见的一些实现差异对性能所带来的影响，除了这些实现方面
所带来的问题之外，应用系统的整体架构实现设计对系统性能的影响可能会更严重。下面大概列举了一
些较为常见的架构设计实现不当带来的性能问题和资源浪费情况。

1、Cache 系统的不合理利用导致Cache 命中率低下造成数据库访问量的增加，同时也浪费了Cache
系统的硬件资源投入；
2、过度依赖面向对象思想，对系统
3、对可扩展性的过渡追求，促使系统设计的时候将对象拆得过于离散，造成系统中大量的复杂Join
语句，而MySQL Server 在各数据库系统中的主要优势在于处理简单逻辑的查询，这与其锁定的机制也有
较大关系；
4、对数据库的过渡依赖，将大量更适合存放于文件系统中的数据存入了数据库中，造成数据库资源
的浪费，影响到系统的整体性能，如各种日志信息；
5、过度理想化系统的用户体验，使大量非核心业务消耗过多的资源，如大量不需要实时更新的数据
做了实时统计计算。

　　以上仅仅是一些比较常见的症结，在各种不同的应用环境中肯定还会有很多不同的性能问题，可能
需要大家通过仔细的数据分析和对系统的充分了解才能找到，但是一旦找到症结所在，通过相应的优化
措施，所带来的收益也是相当可观的。