Day24-MySQL优化策略

一、查询缓存

         高性能MySQL这本书用了20几页去描述查询缓存这个知识点，照比其他知识点多了几倍~，书中作者却不建议我们开启MySQL自带的缓存功能。

         我觉得听大牛的没有错，并且下文MySQL官方也给出了性能会下降的警告。

         但是小编也有个人见解，在架构不复杂，机器配置不高也没有缓存服务器做支撑，并且以查为主，基本没改动的情况下，并做好严格做好开启和关闭之后的性能差异测试，然后在多方衡量之后在决定是否要开启。

         注意：查询中包含任何用户自定义的函数，存储函数，用户变量，临时表，mysql库中的系统表，或者包含列级别的权限的表都不会被缓存!

查询缓存（QueryCache）保存查询返回的完整结果。当查询命中该缓存，MySQL会立即返回结果，跳过解析、优化和执行阶段。

官方在特定环境测试结果（官方文档中有详细说明）：

1.如果对某表进行简单查询，但每次查询条件都不一样时，打开查询缓存会导致性能下降13%。

2.如对一个只有一行数据的表进行查询，则可以提升238%。

所以查询缓存特别适用于更新频率非常低、查询频率非常高的场景。

启用MySQL查询缓存可以极大地减低数据库服务器的CPU使用率和结果返回的时间。

查看查询缓存情况：

mysql> show variables like '%query_cache%';
（query_cache_type 为 ON 表示已经开启）
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 20971520 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

如果不是ON，修改配置文件以开启查询缓存：

> vi /etc/my.cnf

[mysqld]中添加：

query_cache_size = 20M

query_cache_type = ON

重启mysql服务：

> service mysql restart

查看缓存使用情况：

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 83       |
| Qcache_free_memory      | 19811040 |
| Qcache_hits             | 3108196  |
| Qcache_inserts          | 757254   |
| Qcache_lowmem_prunes    | 20720    |
| Qcache_not_cached       | 47219    |
| Qcache_queries_in_cache | 47       |
| Qcache_total_blocks     | 276      |
+-------------------------+----------+

    其中各个参数的意义如下：

- Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

- Qcache_free_memory：缓存中的空闲内存。

- Qcache_hits：每次查询在缓存中命中时就增大

- Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

- Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)

- Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

- Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。

- Qcache_total_blocks：缓存中块的数量。

对于某些不想使用缓存的语句，可以这样使用：

select SQL_NO_CACHE count(*) from users where email = 'hello';

优化提示：

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据 查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小;查询缓存利用率在80%以上而且 Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

引用大牛一句话：

如果Qcache_lowmem_prunes 值比较大，表示查询缓存区大小设置太小，需要增大。

如果Qcache_free_blocks 较多，表示内存碎片较多，需要清理，flush query cache

根据我看的 《High Performance MySQL》中所述，关于query_cache_min_res_unit大小的调优

，书中给出了一个计算公式，可以供调优设置参考：

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

二、如何分析和配置查询缓存

三、查询缓存优化

使用多个较小的表，而不是用一个大表，对查询缓存有帮助。

批量写入时，只需要做一次缓存失败，所以相比单条写入效率更好。

可以通过SQL_CACHE 和 SQL_NO_CACHE来控制某个SELECT 语句是否需要进行缓存。还可以通过修改会话级别的变量query_cache_type 来控制查询缓存。

禁用写密集应用的查询缓存。

严格做好相关测试，对比打开和关闭查询缓存的性能差异。

四、查询缓存总结

完全相同的查询在重复执行的时候，查询缓存可以立即返回结果，而无须在数据库中重新执行一次。根据我们的经验，在高并发压力环境中查询缓存会导致性能的下降，甚至僵死。如果你一定要使用查询缓存，那么不要设置太大内存，而且只有在明确收益的同时才使用。那该如何判断是否应该使用查询缓存了？建议使用Percona Server，观察更细致的日志，并做一些简单的计算。还可以查看缓存命中率或者命中和写入比率。查询缓存是一个非常方便的缓存，对应用程序完全透明，无须任何额外的编码，但是，如果希望有

更高的缓存效率，我们建议是使用memcached、redis，来做缓存服务器。

五、服务器优化设置

注意事项：打算长期使用的设置都应该配置到My.cnf 文件中，而不是用命令行特别指定。否则偶然的启动时候，忘了再次设置，就会有很大的风险。

对My.cnf 进行微调整*（一次改变一个或两个变量），然后进行迭代式的基准测试！

1、对InnoDB的缓冲池进行基准测试，有助于我们制定“内存曲线”真正的展示我们需要多少内存。以此有根据的进行MySQL服务器的内存扩展，避免浪费!

2、要对高可用的配置进行调整，查看崩溃到恢复需要多长时间。可以反复设置一个备库，故意让她崩溃。来取到恢复时间，进而调整系统配置参数，来提升故障恢复速度。

3、分析慢查询日志，确定哪些问题导致的慢查询，然后通过调整，和开发规范，来避免经常出现慢查询操作。

     用pt-query-digest 来分析TCP流量，在服务器完全打开慢查询日志的时候，使用pt-log-player 重放所有慢查询，然后用pt-query-digest来分析输出报告。

     这个可以观察在不同硬件，软件，和服务配置下，查询语句的运行情况！

4、在初期就应该规划好配置和长期优化的策略作为首要核心要点，而不要找新手不断的进行调优和进行长久的迭代式基准测试，这会浪费大量精力，时间。

     大量的时间应该花在检查备份，监控执行计划explan等的事情上，才会更有意义。

5、避免使用互联网上受欢迎的调优脚本，这样做风险很大，一定要做严密的评估和测试的基础上进行修改调优脚本，别家公司的以及通用的优化计划脚本，并不一定适合自己。

6、my.cnf 文件的可配置性太强了，导致新手很容易把MySQL玩坏了。高手就能把MySQL玩的可以支撑起亿级的请求。 数据库也不是AI,它是死的，除非你能写个类似AI的脚本，根据复杂的业务，和当前服务器硬件的配置，自动生成My.cnf 最佳配置，现在我还没有这么nb的编程水平，后期可以尝试写一个。跟Myeclipse 自动生成DAO一样的基础上，进行灵动生成，再NB的脚本，进行人为手工调整。

7，因为它开源，需要对硬件，业务的评估，来定制My.cnf 这个文件。刚安装好的MySQL生成的配置文件，是最low的，是为了满足所有机器启动的最小配置原则，跑项目那是不行的~

     所以，安装好MySQL之后，一定要花一些时间认真的配置My.cnf 这个数据库的全局配置文件。

小结：如果使用的是InnoDB最重要的两个参数：

InnoDB_buffer_pool_size 和 InnoDB_log_file_size

推荐一个在线配置工具：http://tools.percona.com  可以得到非常好的建议！

六、硬件优化设置

略，对硬件不是很了解，也不想做多的介绍了。

一般不了解的只能花钱买最好的硬件，用最大的内存、用最好的网络

假设千兆网络不行换万兆网络的架设。

直接花钱提高IO,CPU,网络的架构设计和硬件。

强烈推荐：

MySQL管理之缓存机制：

http://yijiu.blog.51cto.com/433846/1392483

myql优化，启动MySQL缓存机制，实现命中率100%

http://blog.csdn.net/shachao888/article/details/52411258

打赏

微信扫一扫，打赏作者吧～