影响MySQL性能的主要因素和五大配置参数

　　我们今天主要和大家分享的是对MySQL性能影响关系紧密的五大配置参数和影响MySQL性能的主要因素，以下就是文章的具体内容描述，希望会给你带来一些帮助。

　　以下的文章主要是对MySQL性能影响关系紧密的五大配置参数的介绍，我前几天在相关网站看见对MySQL性能影响关系紧密的五大配置参数的资料，觉得挺好，就拿出来供大家分享，望你能有所收获。

　　(一)连接

　　连接通常来自Web服务器，下面列出了一些与连接有关的参数，以及该如何设置它们。

　　1、max_connections

　　这是Web服务器允许的最大连接数，记住每个连接都要使用会话内存(关于会话内存，文章后面有涉及)。

　　2、max_packet_allowed

　　最大数据包大小，通常等于你需要在一个大块中返回的最大数据集的大小，如果你在使用远程mysqldump，那它的值需要更大。

　　3、aborted_connects

　　检查系统状态的计数器，确定其没有增长，如果数量增长说明客户端连接时遇到了错误。

　　4、thread_cache_size

　　入站连接会在MySQL中创建一个新的线程，因为MySQL中打开和关闭连接都很廉价，速度也快，它就没有象其它数据库，如Oracle那么多持续连接了，但线程预先创建并不会节约时间，这就是为什么要MySQL线程缓存的原因了。

　　如果在增长请密切注意创建的线程，让你的线程缓存更大，对于2550或100的thread_cache_size，内存占用也不多。

　　(二)查询缓存

　　(三)临时表

　　内存速度是相当快的，因此我们希望所有的排序操作都在内存中进行，我们可以通过调整查询让结果集更小以实现内存排序，或将变量设置得更大。

　　tmp_table_size

　　max_heap_table_size

　　无论何时在MySQL中创建临时表，它都会使用这两个变量的最小值作为临界值，除了在磁盘上构建临时表外，还会创建许多会话，这些会话会抢占有限制的资源，因此最好是调整查询而不是将这些参数设置得更高，同时，需要注意的是有BLOB或TEXT字段类型的表将直接写入磁盘。

　　(四)会话内存

　　MySQL中每个会话都有其自己的内存，这个内存就是分配给SQL查询的内存，因此你想让它变得尽可能大以满足需要。但你不得不平衡同一时间数据库内一致性会话的数量。这里显得有点黑色艺术的是MySQL是按需分配缓存的，因此，你不能只添加它们并乘以会话的数量，这样估算下来比MySQL典型的使用要大得多。

　　最佳做法是启动MySQL，连接所有会话，然后继续关注顶级会话的VIRT列，mysqld行的数目通常保持相对稳定，这就是实际的内存总用量，减去所有的静态MySQL内存区域，就得到了实际的所有会话内存，然后除以会话的数量就得到平均值。

　　1、read_buffer_size

　　缓存连续扫描的块，这个缓存是跨存储引擎的，不只是MyISAM表。

　　2、sort_buffer_size

　　执行排序缓存区的大小，最好将其设置为1M-2M，然后在会话中设置，为一个特定的查询设置更高的值。

　　3、join_buffer_size

　　执行联合查询分配的缓存区大小，将其设置为1M-2M大小，然后在每个会话中再单独按需设置。

　　4、read_rnd_buffer_size

　　用于排序和orderby操作，最好将其设置为1M，然后在会话中可以将其作为一个会话变量设置为更大的值。

　　(五)慢查询日志

　　慢速查询日志是MySQL很有用的一个特性。

　　1、log_slow_queries

　　MySQL参数中log_slow_queries参数在my.cnf文件中设置它，将其设置为on，默认情况下，MySQL会将文件放到数据目录，文件以“主机名-slow.log”的形式命名，但你在设置这个选项的时候也可以为其指定一个名字。

　　2、long_query_time

　　默认值是10秒，你可以动态设置它，值从1到将其设置为on，如果数据库启动了，默认情况下，日志将关闭。截至5.1.21和安装了Google补丁的版本，这个选项可以以微秒设置，这是一个了不起的功能，因为一旦你消除了所有查询时间超过1秒的查询，说明调整非常成功，这样可以帮助你在问题变大之前消除问题SQL。

　　3、log_queries_not_using_indexes

　　开启这个选项是个不错的主意，它真实地记录了返回所有行的查询。

　　小结

　　我们介绍了MySQL参数的五大类设置，平时我们一般都很少碰它们，在进行MySQL性能调优和故障诊断时这些参数还是非常有用的。

　　MySQL中的缓存查询包括两个解析查询计划，以及返回的数据集，如果基础表数据或结构有变化，将会使查询缓存中的项目无效。

　　1、query_cache_min_res_unit

#p#分页标题#e#

　　MySQL参数中query_cache_min_res_unit查询缓存中的块是以这个大小进行分配的，使用下面的公式计算查询缓存的平均大小，根据计算结果设置这个变量，MySQL就会更有效地使用查询缓存，缓存更多的查询，减少内存的浪费。

　　2、query_cache_size

　　这个参数设置查询缓存的总大小。

　　3、query_cache_limit

　　这个参数告诉MySQL丢掉大于这个大小的查询，一般大型查询还是比较少见的，如运行一个批处理执行一个大型报表的统计，因此那些大型结果集不应该填满查询缓存。

      看完了以上关于影响MySQL性能的五大配置参数，下面来看一下影响MySQL性能的主要因素：

　　1、业务需求对MySQL性能的影响

　　应用系统中的每一个功能在设计初衷肯定都是出于为用户提供某种服务，或者满足用户的某种需求，但是，并不是每一个功能在最后都能很成功，甚至有些功能的推出可能在整个系统中是画蛇添足。不仅没有为用户提高任何体验度，也没有为用户改进多少功能易用性，反而在整个系统中成为一个累赘，带来资源的浪费。

　　这里我们就拿一个看上去很简单的功能来分析一下。

　　需求：一个论坛帖子总量的统计

　　附加要求：实时更新

　　在很多人看来，这个功能非常容易实现，不就是执行一条SELECTCOUNT(*)的Query就可以得到结果了么？是的，确实只需要如此简单的一个Query就可以得到结果。但是，如果我们采用不是MyISAM存储引擎，而是使用的Innodb的存储引擎，那么大家可以试想一下，如果存放帖子的表中已经有上千万的帖子的时候，执行这条Query语句需要多少成本？恐怕再好的硬件设备，恐怕都不可能在10秒之内完成一次查询吧。如果我们的访问量再大一点，还有人觉得这是一件简单的事情么？

　　既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加1，这样我们每次都只需要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够满足要求，可是如果我们的系统帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候，恐怕这个统计表又要成为大家的噩梦了。要么因为并发的问题造成统计结果的不准确，要么因为锁资源争用严重造成整体性能的大幅度下降。

　　其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实时更新”上面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否是实时变化的？有多少人在乎这个数据在短时间内的不精确性？我想恐怕不会有人会傻傻的盯着这个统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加1吧？即使明明白白的告诉用户这个统计数据是每过多长时间段更新一次，那有怎样？难道会有很多用户就此很不爽么？

　　只要去掉了这个“实时更新”的附加条件，我们就可以非常容易的实现这个功能了。就像之前所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一举两得。

　　实际上，在我们应用的系统中还有很多很多类似的功能点可以优化。如某些场合的列表页面参与列表的数据量达到一个数量级之后，完全可以不用准确的显示这个列表总共有多少条信息，总共分了多少页，而只需要一个大概的估计值或者一个时间段之前的统计值。这样就省略了我们的分页程序需要在分以前实时COUNT出满足条件的记录数。

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

　　无用功能堆积使系统过度复杂影响整体性能很多时候，为系统增加某个功能可能并不需要花费太多的成本，而要想将一个已经运行了一段时间的功能从原有系统中撤下来却是非常困难的。

#p#分页标题#e#

　　首先，对于开发部门，可能要重新整理很多的代码，找出可能存在与增加该功能所编写的代码有交集的其他功能点，删除没有关联的代码，修改有关联的代码；其次，对于测试部门，由于功能的变动，必须要回归测试所有相关的功能点是否正常。可能由于界定困难，不得不将回归范围扩展到很大，测试工作量也很大。

　　最后，所有与撤除下线某个功能相关的工作参与者来说，又无法带来任何实质性的收益，而恰恰相反是，带来的只可能是风险。

　　由于上面的这几个因素，可能很少有公司能够有很完善的项目（或者功能）下线机制，也很少有公司能做到及时将系统中某些不合适的功能下线。所以，我们所面对的应用系统可能总是越来越复杂，越来越庞大，短期内的复杂可能并无太大问题，但是随着时间的积累，我们所面对的系统就会变得极其臃肿。不仅维护困难，性能也会越来越差。尤其是有些并不合理的功能，在设计之初或者是刚上线的时候由于数据量较小，带来不了多少性能损耗。可随着时间的推移，数据库中的数据量越来越大，数据检索越来越困难，对真个系统带来的资源消耗也就越来越大。

　　而且，由于系统复杂度的不断增加，给后续其他功能的开发带来实现的复杂度，可能很多本来很简单的功能，因为系统的复杂而不得不增加很多的逻辑判断，造成系统应用程序的计算量不断增加，本身性能就会受到影响。而如果这些逻辑判断还需要与数据库交互通过持久化的数据来完成的话，所带来的性能损失就更大，对整个系统的性能影响也就更大了。

　　2、系统架构及实时对性能的影响

　　一个WEB应用系统，自然离不开Web应用程序（WebApp）和应用程序服务器（AppServer）。AppServer我们能控制的内容不多，大多都是使用已经久经考验的成熟产品，大家能做的也就只是通过一些简单的参数设置调整来进行调优，不做细究。而WebApp大部分都是各自公司根据业务需求自行开发，可控性要好很多。所以我们从Web应用程序着手分析一个应用程序架构的不同设计对整个系统性能影响将会更合适。我们所考虑的架构大多数时候是数据层面相关的架构。

　　我们数据库中存放的数据都是适合在数据库中存放的吗？实际上，以下几类数据都是不适合在数据库中存放的：

　　②二进制多媒体数据

　　②流水队列数据

　　③超大文本数据

　　是否合理的利用了应用层Cache机制？

　　对于Web应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据通过应用层的Cache机制Cache到内存中，对性能的提升肯定是成数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

　　当然，通过Cache机制成功的案例数不胜数，但是失败的案例也同样并不少见。如何合理的通过Cache技术让系统性能得到较大的提升也不是通过寥寥几笔就能说明的清楚，这里我仅根据以往的经验列举一下什么样的数据适合通过Cache技术来提高系统性能：

　　①系统各种配置及规则数据；

　　②活跃用户的基本信息数据；

　　③活跃用户的个性化定制数据；

　　④准实时的统计信息数据；

　　⑤其他一些访问频率高但变更较少的数据。

　　数据层实现都是最精简的吗？

　　从以往的经验来看，一个合理的数据存取实现和一个拙劣的实现相比，在性能方面的差异经常会超出一个甚至几个数量级。

　　上面还仅仅只是列举了我们平时比较常见的一些实现差异对性能所带来的影响，除了这些实现方面所带来的问题之外，应用系统的整体架构实现设计对系统性能的影响可能会更严重。下面大概列举了一些较为常见的架构设计实现不当带来的性能问题和资源浪费情况：

　　Cache系统的不合理利用导致Cache命中率低下造成数据库访问量的增加，同时也浪费了Cache系统的硬件资源投入；

　　过度依赖面向对象思想，对系统数据资源的严重浪费；

　　对可扩展性的过渡追求，促使系统设计的时候将对象拆得过于离散，造成系统中大量的复杂Join语句，而MySQLServer在各数据库系统中的主要优势在于处理简单逻辑的查询，这与其锁定的机制也有较大关系；

　　对数据库的过渡依赖，将大量更适合存放于文件系统中的数据存入了数据库中，造成数据库资源的浪费，影响到系统的整体性能，如各种日志信息；

　　过度理想化系统的用户体验，使大量非核心业务消耗过多的资源，如大量不需要实时更新的数据做了实时统计计算。

      小编结语：

      更多内容尽在课课家教育！