当初我要这么学-MySQL-Server-性能优化，现在哪至于这样！-CSDN博客

本文链接：https://blog.csdn.net/2401_84411433/article/details/138045266

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

Fine tuning of the installation directories:
–bindir=DIR user executables [EPREFIX/bin]
… …
Program names:
–program-prefix=PREFIX prepend PREFIX to installed program names
… …
System types:
–build=BUILD configure for building on BUILD [guessed]
… …
Optional Features:
–disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no)
… …
Optional Packages:
–with-charset=CHARSET
… …
–without-innodb Do not include the InnoDB table handler
… …
Some influential environment variables:
CC C compiler command
… …
CCASFLAGS assembler compiler flags (defaults to CFLAGS)
… …

上面的输出内容中很多都已经省略了，大家完全可以通过自行测试得到更为丰富的内容输出。下面针对几个比较重要的编译参数做一个简单的介绍：

“—prefix”：设定安装路径，默认为“/usr/local”；
“—datadir”：设定 MySQL 数据文件存放路径；
“—with-charset”：设定系统的默认字符集；
“—with-collation”：系统默认的校验规则；
“—with-extra-charsets”：出了默认字符集之外需要编译安装的字符集；
“—with-unix-socket-path”：设定 socket 文件地址；
“—with-tcp-port”：指定特定监听端口，默认为 3306；
“—with-mysqld-user”：指定运行 mysqld 的 os 用户，默认为 mysql；
“—without-query-cache”：禁用 Query Cache 功能；
“—without-innodb”：禁用 Innodb 存储引擎；
“--with-partition”：在 5.1 版本中开启 partition 支持特性；
“--enable-thread-safe-client”：以线程方式编译客户端；
“—with-pthread”：强制使用 pthread 线程库编译；
“—with-named-thread-libs”：指定使用某个特定的线程库编译；
“—without-debug”：使用非 debug 模式；
“—with-mysqld-ldflags”：mysqld 的额外 link 参数；
“—with-client-ldflags”：client 的额外 link 参数；

以上这些参数是在源码安装中比较常用的一些编译参数，其中前面几个编译参数主要是为了方便我们在安装的时候可以定制自己的系统，让系统更适合我们自己应用环境的相关规范，做到环境统一，并按照实际需求生成相应的二进制代码。而后面的一些参数主要是用来优化编译结果的。

我想大家应该都能理解一般来说，一个系统功能越复杂，其性能一般都会越差。所以，在我们安装编译 MySQL 的时候应该尽量只选用我们需要的组件，仅安装我们需要的存储引擎，仅编译我们需要的字符集，让我们的系统能够尽可能的简单，因为这样的 MySQL 也会给我们带来尽可能高的性能。

此外，对于一些特定的软件环境上，可能会有多种线程库的选择的，如果你对各个线程库较为了解，完全可以通过编译参数设定让MySQL使用最合适的线程库，让MySQL 在我们特定的环境中发挥他最优化的一面。

源码包的编译参数中默认会以 Debug 模式生成二进制代码，而 Debug 模式给 MySQL 带来的性能损失是比较大的，所以当我们编译准备安装的产品代码的时候，一定不要忘记使用“—without-debug”参数禁用 Debug 模式。

而“—with-mysqld-ldflags”和“—with-client-ldflags”两个编译参数如果设置为“-all-static”的话，可以告诉编译器以静态方式编译来使编译结果代码得到最高的性能。使用静态编译和动态方式编译的代码相比，性能差距可能会达到 5%到 10%之多。

就我个人来说最常使用的编译配置参数如下，各位可以参照自行增删相关内容：

./configure–prefix=/usr/local/mysql
–without-debug
–without-bench
–enable-thread-safe-client
–enable-assembler
–enable-profiling
–with-mysqld-ldflags=-all-static
–with-client-ldflags=-all-static
–with-charset=latin1
–with-extra-charset=utf8,gbk
–with-innodb
–with-csv-storage-engine
–with-federated-storage-engine
–with-mysqld-user=mysql
–without-embedded-server
–with-server-suffix=-community
–with-unix-socket-path=/usr/local/mysql/sock/mysql.sock

二、MySQL 日志设置优化

在安装完 MySQL 之后，肯定是需要对 MySQL 的各种参数选项进行一些优化调整的。虽然 MySQL 系统的伸缩性很强，既可以在有很充足的硬件资源环境下高效的运行，也可以在极少资源环境下很好的运行，但不管怎样，尽可能充足的硬件资源对 MySQL 的性能提升总是有帮助的。在这一节我们主要分析一下 MySQL 的日志（主要是 Binlog）对系统性能的影响，并根据日志的相关特性得出相应的优化思路。

①日志产生的性能影响

MySQL 的日志包括错误日志（Error Log），更新日志（Update Log），二进制日志（Binlog），查询日志（Query Log），慢查询日志（Slow Query Log）等。当然，更新日志是老版本的 MySQL 才有的，目前已经被二进制日志替代。

在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减少 IO 损耗提高系统性能的目的。但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是 MySQL很多存储引擎进行增量备份的基础，也是 MySQL 实现复制的基本条件。有时候为了进一步的性能优化，定位执行较慢的 SQL 语句，很多系统也会打开慢查询日志来记录执行时间超过特定数值（由我们自行设置）的 SQL 语句。

一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将 MySQL 中执行的每一条 Query 都记录到日志中，会该系统带来比较大的 IO 负担，而带来的实际效益却并不是非常大。一般只有在开发测试环境中，为了定位某些功能具体使用了哪些 SQL 语句的时候，才会在短时间段内打开该日志来做相应的分析。所以，在MySQL系统中，会对性能产生影响的MySQL日志（不包括各存储引擎自己的日志）主要就是 Binlog 了。

②Binlog 相关参数及优化策略

我们首先看看Binlog的相关参数，通过执行如下命令可以获得关于Binlog的相关参数。当然，其中也显示出了“ innodb_locks_unsafe_for_binlog”这个 Innodb 存储引擎特有的与 Binlog 相关的参数：

mysql> show variables like ‘%binlog%’;
±-------------------------------±-----------+
| Variable_name | Value |
±-------------------------------±-----------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| sync_binlog | 0 |
±-------------------------------±-----------+

“binlog_cache_size"：在事务过程中容纳二进制日志 SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client都可以分配设置大小的 binlog cache 空间。如果读者朋友的系统中经常会出现多语句事务的华，可以尝试增加该值的大小，以获得更有的性能。当然，我们可以通过 MySQL 的以下两个状态变量来判断当前的 binlog_cache_size 的状况：Binlog_cache_use 和 Binlog_cache_disk_use。

“max_binlog_cache_size”：和"binlog_cache_size"相对应，但是所代表的是 binlog 能够使用的最大 cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出 “ Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage”的错误。

“max_binlog_size”：Binlog 日志最大值，一般来说设置为 512M 或者 1G，但不能超过 1G。该大小并不能非常严格控制Binlog大小，尤其是当到达 Binlog 比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有 SQL 都记录进入当前日志，直到该事务结束。这一点和 Oracle 的 Redo 日志有点不一样，因为 Oracle 的 Redo 日志所记录的是数据文件的物理位置的变化，而且里面同时记录了 Redo 和 Undo相关的信息，所以同一个事务是否在一个日志中对 Oracle 来说并不关键。而 MySQL 在 Binlog 中所记录的是数据库逻辑变化信息，MySQL 称之为 Event，实际上就是带来数据库变化的 DML 之类的 Query 语句。

“sync_binlog”：这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁盘。
sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

在 MySQL 中系统默认的设置是 sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统 Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多。

大家都知道，MySQL 的复制（Replication），实际上就是通过将 Master 端的 Binlog 通过利用 IO 线程通过网络复制到 Slave 端，然后再通过 SQL 线程解析 Binlog 中的日志再应用到数据库中来实现的。所以，Binlog 量的大小对 IO 线程以及 Msater 和 Slave 端之间的网络都会产生直接的影响。

MySQL 中 Binlog 的产生量是没办法改变的，只要我们的 Query 改变了数据库中的数据，那么就必须将该 Query 所对应的 Event 记录到 Binlog 中。那我们是不是就没有办法优化复制了呢？当然不是，在 MySQL 复制环境中，实际上是有 8 个参数可以让我们控制需要复制或者需要忽略而不进行复制的 DB 或者 Table 的，分别为：

Binlog_Do_DB：设定哪些数据库（Schema）需要记录 Binlog；
Binlog_Ignore_DB：设定哪些数据库（Schema）不要记录 Binlog；
Replicate_Do_DB：设定需要复制的数据库（Schema），多个 DB 用逗号（“,”）分隔；
Replicate_Ignore_DB：设定可以忽略的数据库（Schema）；
Replicate_Do_Table：设定需要复制的 Table；
Replicate_Ignore_Table：设定可以忽略的 Table；
Replicate_Wild_Do_Table：功能同 Replicate_Do_Table，但可以带通配符来进行设置；
Replicate_Wild_Ignore_Table：功能同 Replicate_Ignore_Table，可带通配符设置；

通过上面这八个参数，我们就可以非常方便按照实际需求，控制从 Master 端到 Slave 端的 Binlog量尽可能的少，从而减小 Master 端到 Slave 端的网络流量，减少 IO 线程的 IO 量，还能减少 SQL 线程的解析与应用 SQL 的数量，最终达到改善 Slave 上的数据延时问题。

实际上，上面这八个参数中的前面两个是设置在 Master 端的，而后面六个参数则是设置在 Slave 端的。虽然前面两个参数和后面六个参数在功能上并没有非常直接的关系，但是对于优化 MySQL 的 Replication 来说都可以启到相似的功能。当然也有一定的区别，其主要区别如下：

如果在 Master 端设置前面两个参数，不仅仅会让 Master 端的 Binlog 记录所带来的 IO 量减少，还会让 Master 端的 IO 线程就可以减少 Binlog 的读取量，传递给 Slave 端的 IO 线程的 Binlog 量自然就会较少。这样做的好处是可以减少网络 IO，减少 Slave 端 IO 线程的 IO 量，减少 Slave 端的 SQL 线程的工作量，从而最大幅度的优化复制性能。当然，在 Master 端设置也存在一定的弊端，因为 MySQL 的判断是否需要复制某个 Event 不是根据产生该 Event 的 Query 所更改的数据所在的 DB，而是根据执行 Query 时刻所在的默认 Schema，也就是我们登录时候指定的 DB 或者运行“USE DATABASE”中所指定的 DB。只有当前默认 DB 和配置中所设定的 DB 完全吻合的时候 IO 线程才会将该 Event 读取给 Slave 的 IO 线程。所以如果在系统中出现在默认 DB 和设定需要复制的 DB 不一样的情况下改变了需要复制的 DB 中某个 Table 的数据的时候，该 Event 是不会被复制到 Slave 中去的，这样就会造成 Slave 端的数据和 Master 的数据不一致的情况出现。同样，如果在默认 Schema 下更改了不需要复制的 Schema 中的数据，则会被复制到 Slave 端，当 Slave 端并没有该 Schema 的时候，则会造成复制出错而停止；
而如果是在 Slave 端设置后面的六个参数，在性能优化方面可能比在 Master 端要稍微逊色一点，因为不管是需要还是不需要复制的 Event 都被会被 IO 线程读取到 Slave 端，这样不仅仅增加了网络 IO 量，也给 Slave 端的 IO 线程增加了 Relay Log 的写入量。但是仍然可以减少 Slave 的 SQL 线程在 Slave 端的日志应用量。虽然性能方面稍有逊色，但是在 Slave 端设置复制过滤机制，可以保证不会出现因为默认 Schema 的问题而造成 Slave 和 Master 数据不一致或者复制出错的问题。

③Slow Query Log 相关参数及使用建议

再来看看 Slow Query Log 的相关参数配置。有些时候，我们为了定位系统中效率比较地下的 Query 语句，则需要打开慢查询日志，也就是 Slow Query Log。我们可以如下查看系统慢查询日志的相关设置：

“ log_slow_queries ” 参数显示了系统是否已经打开 Slow Query Log 功能，而 “long_query_time”参数则告诉我们当前系统设置的 Slow Query 记录执行时间超过多长的 Query。在 MySQL AB 发行的 MySQL 版本中 Slow Query Log 可以设置的最短慢查询时间为 1 秒，这在有些时候可能没办法完全满足我们的要求，如果希望能够进一步缩短慢查询的时间限制，可以使用 Percona 提供的 microslow-patch（件成为 msl Patch）来突破该限制。msl patch 不仅仅能将慢查询时间减小到毫秒级别，同时还能通过一些特定的规则来过滤记录的 SQL，如仅记录涉及到某个表的 Slow Query 等等附加功能。考虑到篇幅问题，这里就不介绍 msl patch 给我们带来的更为详细的功能和使用，大家请参考官方介绍（ http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/）

打开 Slow Query Log 功能对系统性能的整体影响没有 Binlog 那么大，毕竟 Slow Query Log 的数据量比较小，带来的 IO 损耗也就较小，但是，系统需要计算每一条 Query 的执行时间，所以消耗总是会有一些的，主要是CPU方面的消耗。如果大家的系统在CPU 资源足够丰富的时候，可以不必在乎这一点点损耗，毕竟他可能会给我们带来更大性能优化的收获。但如果我们的 CPU 资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开 Slow Query Log 功能来定位可能存在的慢查询。

MySQL 的其他日志由于使用很少（Query Log）或者性能影响很少，我们就不在此过多分析了，至于各个存储引擎相关的日志，我们留在下一篇“常用存储引擎优化”部分再做相应的分析。

三、Query Cache 优化

谈到 Query Cache，恐怕使用过 MySQL 的大部分人都会或多或少有一些了解，因为在很多人看来他可以帮助我们将数据库的性能产生一个“质”的提升。但真的是这样吗？这一节我们就将如何合理的使用 MySQL 的 Query Cache 进行一些相应的分析并得出部分优化建议。

①Query Cache 真的是“尚方宝剑”吗？

MySQL 的 Query Cache 实现原理实际上并不是特别的复杂，简单的来说就是将客户端请求的 Query语句（当然仅限于 SELECT 类型的 Query）通过一定的 hash 算法进行一个计算而得到一个 hash 值，存放在一个 hash 桶中。同时将该 Query 的结果集（Result Set）也存放在一个内存 Cache 中的。存放 Query hash 值的链表中的每一个 hash 值所在的节点中同时还存放了该 Query 所对应的 Result Set 的 Cache 所在的内存地址，以及该 Query 所涉及到的所有 Table 的标识等其他一些相关信息。系统接受到任何一个 SELECT 类型的 Query 的时候，首先计算出其 hash 值，然后通过该 hash 值到 Query Cache 中去匹配，如果找到了完全相同的 Query，则直接将之前所 Cache 的 Result Set 返回给客户端而完全不需要进行后面的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后，也会通知 Query Cache，需要将所有与该 Table 有关的 Query 的 Cache 全部失效，并释放出之前占用的内存地址，以便后面其他的 Query 能够使用。

从上面的实现原理来看，Query Cache 确实是以比较简单的实现带来巨大性能收益的功能。但是很多人可能都忽略了使用 QueryCache 之后所带来的负面影响：

Query 语句的 hash 运算以及 hash 查找资源消耗。 当我们使用 Query Cache 之后，每条 SELECT 类型的 Query 在到达 MySQL 之后，都需要进行一个 hash 运算然后查找是否存在该 Query 的 Cache，虽然这个 hash 运算的算法可能已经非常高效了，hash 查找的过程也已经足够的优化了，对于一条 Query 来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千条 Query 的时候，我们就不能对产生的 CPU 的消耗完全忽视了。
Query Cache 的失效问题。 如果我们的表变更比较频繁，则会造成 Query Cache 的失效率非常高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我们每次缓存到Query Cache中的Cache数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同 Query 进来之后无法使用到之前的 Cache。
Query Cache 中缓存的是 Result Set ，而不是数据页，也就是说，存在同一条记录被 Cache 多次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定 Query Cache 的大小啊。是的，我们确实可以限定 Query Cache 的大小，但是这样，Query Cache 就很容易造成因为内存不足而被换出，造成命中率的下降。

对于 Query Cache 的上面三个负面影响，如果单独拿出每一个影响来说都不会造成对整个系统多大的问题，并不会让大家对使用 Query Cache 产生太多顾虑。但是，当综合这三个负面影响一起考虑的话，恐怕 Query Cache 在很多人心目中就不再是以前的那把“尚方宝剑”了。

②适度使用 Query Cache

虽然 Query Cache 的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我们完全不用因为 Query Cache 的上面三个负面影响就完全失去对 Query Cache 的信心。只要我们理解了 Query Cache 的实现原理，那么我们就完全可以通过一定的手段在使用 Query Cache 的时候扬长避短，重点发挥其优势，并有效的避开其劣势。

首先，我们需要根据 Query Cache 失效机制来判断哪些表适合使用 Query 哪些表不适合。由于 Query Cache 的失效主要是因为 Query 所依赖的 Table 的数据发生了变化，造成 Query 的 Result Set 可能已经有所改变而造成相关的 Query Cache 全部失效，那么我们就应该避免在查询变化频繁的 Table 的 Query 上使用，而应该在那些查询变化频率较小的 Table 的 Query 上面使用。MySQL 中针对 Query Cache 有两个专用的 SQL Hint（提示）：SQL_NO_CACHE 和 SQL_CACHE，分别代表强制不使用 Query Cache 和强制使用 Query Cache。我们完全可以利用这两个 SQL Hint，让 MySQL 知道我们希望哪些 SQL 使用 Query Cache 而哪些 SQL 就不要使用了。这样不仅可以让变化频繁 Table 的 Query 浪费 Query Cache 的内存，同时还可以减少 Query Cache 的检测量。

其次，对于那些变化非常小，大部分时候都是静态的数据，我们可以添加 SQL_CACHE 的 SQL Hint，强制 MySQL 使用 Query Cache，从而提高该表的查询性能。

最后，有些 SQL 的 Result Set 很大，如果使用 Query Cache 很容易造成 Cache 内存的不足，或者将之前一些老的 Cache 冲刷出去。对于这一类 Query 我们有两种方法可以解决，一是使用 SQL_NO_CACHE 参数来强制他不使用 Query Cache 而每次都直接从实际数据中去查找，另一种方法是通过设定“query_cache_limit”参数值来控制 Query Cache 中所 Cache 的最大 Result Set ，系统默认为 1M（1048576）。当某个 Query 的 Result Set 大于“query_cache_limit”所设定的值的时候，Query Cache 是不会 Cache 这个 Query 的。

③Query Cache 的相关系统参数变量和状态变量

我们首先看看 Query Cache 的系统变量，可以通过执行如下命令获得 MySQL 中 Query Cache 相关的系统参数变量：

mysql> show variables like ‘%query_cache%’;
±-----------------------------±----------+
| Variable_name | Value |
±-----------------------------±----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
±-----------------------------±----------+

“have_query_cache”：该 MySQL 是否支持 Query Cache；
“query_cache_limit”：Query Cache 存放的单条 Query 最大 Result Set ，默认 1M；
“query_cache_min_res_unit”：Query Cache 每个 Result Set 存放的最小内存大小，默认4k；
“query_cache_size”：系统中用于 Query Cache 内存的大小；
“query_cache_type”：系统是否打开了 Query Cache 功能；
“query_cache_wlock_invalidate”：针对于 MyISAM 存储引擎，设置当有 WRITE LOCK 在某个 Table 上面的时候，读请求是要等待 WRITE LOCK 释放资源之后再查询还是允许直接从 Query Cache 中读取结果，默认为 FALSE（可以直接从 Query Cache 中取得结果）。

以上参数的设置主要是“query_cache_limit”和“query_cache_min_res_unit”两个参数的设置需要做一些针对于应用的相关调整。如果我们需要 Cache 的 Result Set 一般都很小（小于 4k）的话，可以适当将 “ query_cache_min_res_unit ” 参数再调小一些，避免造成内存的浪费，“query_cache_limit”参数则不用调整。而如果我们需要Cache 的 Result Set 大部分都大于4k 的话，则最好将“query_cache_min_res_unit”调整到和 Result Set 大小差不多，“query_cache_limit”的参数也应大于 Result Set 的大小。当然，可能有些时候我们比较难准确的估算 Result Set 的大小，那么当 Result Set 较大的时候，我们也并不是非得将“query_cache_min_res_unit”设置的和每个Result Set 差不多大，是每个结果集的一半或者四分之一大小都可以，要想非常完美的完全不浪费任何内存确实也是不可能做到的。

如果我们要了解Query Cache的使用情况，则可以通过Query Cache 相关的状态变量来获取，如通过如下命令：

mysql> show status like ‘Qcache%’;
±------------------------±-----------+
| Variable_name | Value |
±------------------------±-----------+
| Qcache_free_blocks | 7499 |
| Qcache_free_memory | 190662000 |
| Qcache_hits | 1888430018 |
| Qcache_inserts | 1014096388 |
| Qcache_lowmem_prunes | 106071885 |
| Qcache_not_cached | 7951123988 |
| Qcache_queries_in_cache | 19315 |
| Qcache_total_blocks | 47870 |
±------------------------±-----------+

“Qcache_free_blocks”：Query Cache 中目前还有多少剩余的 blocks。如果该值显示较大，则说明 Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理（）。
“Qcache_free_memory”：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的 Query Cache 内存大小是否足够，是需要增加还是过多了；
“Qcache_hits”：多少次命中。通过这个参数我们可以查看到 Query Cache 的基本效果；
“Qcache_inserts”：多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出 Query Cache 的命中率了：
Query Cache 命中率 = Qcache_hits / ( Qcache_hits + Qcache_inserts )；
“Qcache_lowmem_prunes”：多少条 Query 因为内存不足而被清除出 Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出
“Qcache_not_cached”：因为 query_cache_type 的设置或者不能被 cache 的 Query 的数量；
“Qcache_queries_in_cache”：当前 Query Cache 中 cache 的 Query 数量；
“Qcache_total_blocks”：当前 Query Cache 中的 block 数量；

④Query Cache 的限制

Query Cache 由于存放的都是逻辑结构的 Result Set，而不是物理的数据页，所以在性能提升的同时，也会受到一些特定的限制。

5.1.17 之前的版本不能 Cache 帮定变量的 Query，但是从 5.1.17 版本开始，Query Cache 已经开始支持帮定变量的 Query 了；
所有子查询中的外部查询 SQL 不能被 Cache；
在 Procedure，Function 以及 Trigger 中的 Query 不能被 Cache；
包含其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache。

鉴于上面的这些限制，在使用 Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入 Query Cache，仅仅让某些 Query 的查询结果被 Cache。

四、MySQL Server 其他常用优化

除了安装，日志，Query Cache 之外，可能影响 MySQL Server 整体性能的设置其他很多方面，如网络连接，线程管理，Table 管理等。这一节我们将分析除了前面几节内容之外的可能影响 MySQL Server 性能的其他可优化的部分。

①网络连接与连接线程

虽然 MySQL 的连接方式不仅仅只有通过网络方式，还可以通过命名管道的方式，但是不论是何种方式连接 MySQL，在 MySQL 中都是通过线程的方式管理所有客户端请求的连接。每一个客户端连接都会有一个与之对应的生成一个连接线程。我们先看一下与网络连接的性能配置项及对性能的影响。

max_conecctions：整个 MySQL 允许的最大连接数；
这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量大于 max_conecctions 的情况下，由于 MySQL 的设置限制，那么应用中必然会产生连接请求的等待，从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数设置的尽可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值
max_user_connections：每个用户允许的最大连接数；
上面的参数是限制了整个 MySQL 的连接数，而 max_user_connections 则是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和 max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来说，完全没有做太多的限制，可以尽量放开一些。
net_buffer_length：网络包传输中，传输消息之前的 net buffer 初始化大小；
这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大小，所以造成的影响主要是当我们的每次消息都很大的时候 MySQL 总是需要多次申请扩展该缓冲区大小。系统默认大小为 16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到8KB。
max_allowed_packet：在网络传输中，一次传消息输量的最大值；
这个参数与 net_buffer_length 相对应，只不过是 net buffer 的最大值。当我们的消息传输量大于 net_buffer_length 的设置时，MySQL 会自动增大 net buffer 的大小，直到缓冲区大小达到 max_allowed_packet 所设置的值。系统默认值为 1MB，最大值是 1GB，必须设定为 1024 的倍数，单位为字节。
back_log：在 MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的所有请求将存放在一个等待队列中，这个队列就是 MySQL 的连接请求队列。当我们的系统存在瞬时的大量连接请求的时候，则应该注意 back_log 参数的设置。系统默认值为 50，最大可以设置为 65535。当我们增大 back_log 的设置的时候，同时还需要主义 OS 级别对网络监听队列的限制，因为如果 OS 的网络监听设置小于 MySQL 的 back_log 设置的时候，我们加大“back_log”设置是没有意义的。

上面介绍了网络连接交互相关的主要优化设置，下面我们再来看看与每一个客户端连接想对应的连接线程。

在 MySQL 中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个 Thread Cache 池，将空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL 首先会检查 Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。在 MySQL 中与连接线程相关的系统参数及状态变量说明如下：

thread_cache_size：Thread Cache 池中应该存放的连接线程数。

当系统最初启动的时候，并不会马上就创建 thread_cache_size 所设置数目的连接线程存放在 Thread Cache 池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当存放的连接线程达到 thread_cache_size 值之后，MySQL 就不会再续保存用完的连接线程了。

如果我们的应用程序使用的短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让 MySQL 新建和销毁相应的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了 Thread Cache 之后，由于连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用完之后销毁，所以可以节省下大量的系统资源。所以在短连接的应用系统中， thread_cache_size 的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求数。

而如果我们使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，但也并不是完全没有价值。因为应用程序即使是使用了长连接，也很难保证他们所管理的所有连接都能处于很稳定的状态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应用服务器数量较大的系统中，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作将会更多。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大有帮助的。只不过在长连接的环境中我们不需要将 thread_cache_size 参数设置太大，一般来说可能 50 到 100 之间应该就可以了。

thread_stack：每个连接线程被创建的时候，MySQL 给他分配的内存大小。

当 MySQL 创建一个新的连接线程的时候，是需要给他分配一定大小的内存堆栈空间，以便存放客户端的请求Query以及自身的各种状态和处理信息。不过一般来说如果不是对MySQL 的连接线程处理机制十分熟悉的话，不应该轻易调整该参数的大小，使用系统的默认值（192KB）基本上可以所有的普通应用环境。如果该值设置太小，会影响 MySQL 连接线程能够处理客户端请求的Query 内容的大小，以及用户创建的 Procedures 和 Functions 等。