MySQL性能影响因素

本章将带你完成查询和schema的调优。数据库就是用于执行查询的，提高查询速度是调优的最终目
标。数据库的性能取决于许多因素，主要是查询、schema、配置项和硬件。

1. 商业需求的影响
   不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析一下。
   需求：一个论坛帖子总量的统计，附加要求：实时更新。
   从功能上来看非常容易实现，执行一条 SELECT COUNT(*) from 表名的 Query 就可以得到结果。但是
   如果存放帖子的表中已经有上千万的帖子的时候，执行这条 Query语句需要多少成本？恐怕再好的硬件
   设备，恐怕都不可能在 10 秒之内完成一次查询。
   既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，就存放这
   个统计量，每次有新的帖子产生的时候，都将这个值增加 1，这样我们每次都只需要查询这个表就可以
   得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够满足要求，可是如果帖子产生很
   快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候，恐怕这个统计表又要成为大家的噩
   梦了。要么因为并发的问题造成统计结果的不准确，要么因为锁资源争用严重造成整体性能的大幅度下
   降。
   其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实时更新”上
   面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否是实时变化的？有多少
   人在乎这个数据在短时间内的不精确性？恐怕不会有人会盯着这个统计数字并追究当自己发了一个帖子
   然后回头刷新页面发现这个统计数字没有加1吧？所以只要去掉了这个“实时更新”的附加条件，就可以非
   常容易的实现这个功能了。就像之前所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔
   一定时间段去更新一次里面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发
   贴的效率，一举两得。

2. 系统架构及实现的影响
   所有数据都是适合在数据库中存放的吗？数据库为我们提供了太多的功能，反而让很多并不是太了解数
   据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，最后却全部怪罪到数据
   库身上。实际上，以下几类数据都是不适合在数据库中存放的：
   二进制多媒体数据
   这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。将二进制多媒体数据存放在数据库
   中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的CPU
   资源。这些数据的处理本不是数据库的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处
   理资源消耗严重。
   超大文本数据
   对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需要存储更长
   的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放 64KB）的字段，甚至是更大的
   LONGTEXT 类型（最大 4GB）。而 TEXT 类型数据的处理性能要远比VARCHAR类型数据的处理性能低
   下很多。从 5.0.3 版本开始，VARCHAR 类型的最大长度被调整到 64KB
   了，所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问题。
   是否合理利用了应用层cache机制？
   云计算-legolas制作
   对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，
   我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据
   通过应用层的 Cache 机制 Cache 到内存中，对性能的提升肯定是成数量级的，而且由于是活跃数据，
   对系统整体的性能影响也会很大。

3. 查询语句对性能的影响
   SQL 语句的优劣是对性能有影响的，每个SQL语句在优化之前和优化之后的性能差异也是各不相同。在
   数据库管理软件中，最大的性能瓶颈就是在于磁盘 IO，也就是数据的存取操作上面。而对于同一份数
   据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量可能会有天壤之别，所消
   耗的资源也自然是区别很大。功能完全相同的两条SQL的在性能方面的差异。
   我们在执行 sql 语句时可以用 explain 来查看执行计划；
   也可以打开profiling功能，来查看SQL语句执行的整个过程中各项资源的使用情况：
   mysql> set profiling=1;
   mysql> show variables like ‘%profiling%’;
   ±-----------------------±------+
   | Variable_name | Value |
   ±-----------------------±------+
   | have_profiling | YES |
   | profiling | ON |
   | profiling_history_size | 15 |
   ±-----------------------±------+
   3 rows in set (0.00 sec)
   mysql> select * from employees;
   mysql> show profiles;
   ±---------±-----------±----------------------------------+
   | Query_ID | Duration | Query |
   ±---------±-----------±----------------------------------+
   | 1 | 0.49405350 | select * from employees |
   | 2 | 0.05112575 | show variables like ‘%profiling%’ |
   | 3 | 0.00111100 | show variables like ‘%profiling%’ |
   | 4 | 0.50444975 | select * from employees |
   ±---------±-----------±----------------------------------+
   4 rows in set, 1 warning (0.00 sec)
   mysql> show profile CPU,BLOCK IO for query 1;
   ±-------------------------------±---------±---------±-----------±----------
   —±--------------+
   | Status | Duration | CPU_user | CPU_system |
   Block_ops_in | Block_ops_out |
   ±-------------------------------±---------±---------±-----------±----------
   —±--------------+
   | starting | 0.000095 | 0.000014 | 0.000059 |
   0 | 0 |
   | Executing hook on transaction | 0.000006 | 0.000001 | 0.000004 |
   0 | 0 |
   | starting | 0.000010 | 0.000002 | 0.000009 |
   0 | 0 |
   | checking permissions | 0.000008 | 0.000001 | 0.000006 |
   0 | 0 |

4. 数据库schema设计对性能的影响

5. 硬件选择对性能的影响
   首先，数据库主机是存取数据的地方，所以数据库主机的IO性能肯定是需要最优先考虑的一个因素，这
   一点不管是什么类型的数据库应用都是适用的。在主机中决定IO性能部件主要由磁盘和内存所决定，当
   然也包括各种与 IO 相关的板卡。
   其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的
   计算量自然也就比较多，所以数据库主机的 CPU 处理能力也不能忽视。
   最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器都要多，所
   以数据库主机的网络设备的性能也可能会成为系统的瓶颈。
   优化总结
   数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。简
   单来说，可以通过下面的话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优
   化，逻辑实现精简化，硬件设施理性化。