MYSQL之性能优化

一、不适合在数据库中存放的：

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 语句方面的优化培训，可能会给大家带来意想不到的收获的。