前言

这篇文章主要通过针对 MySQL Server（mysqld）相关实现机制的分析，得到一些相应的优化建议。主要涉及 MySQL 的安装以及相关参数设置的优化，但不包括 mysqld 之外的比如存储引擎相关的参数优化，存储引擎的相关参数设置建议将主要在我的下一篇文章 “常用存储引擎的优化” 中进行说明。

一、MySQL 安装优化

选择合适的发行版本

①二进制发行版（包括 RPM 等包装好的特定二进制版本）

由于 MySQL 开源的特性，不仅仅 MySQL AB 提供了多个平台上面的多种二进制发行版本可以供大家选择，还有不少第三方公司（或者个人）也给我们提供了不少选择。

使用 MySQL AB 提供的二进制发行版本我们可以得到哪些好处？

• 通过非常简单的安装方式快速完成 MySQL 的部署；
• 安装版本是经过比较完善的功能和性能测试的编译版本；
• 所使用的编译参数更具通用性的，且比较稳定；
• 如果购买了 MySQL 的服务，将能最大程度的得到 MySQL 的技术支持；

第三方提供的 MySQL 发行版本大多是在 MySQL AB 官方提供的源代码方面做了或多或少的针对性改动，然后再编译而成。这些改动有些是在某些功能上面的改进，也有些是在某写操作的性能方面的改进。还有些由各OS 厂商所提供的发行版本，则可能是在有些代码方面针对自己的OS 做了一些相应的底层调用的调整，以使 MySQL 与自己的 OS 能够更完美的结合。当然，也有一些第三方发行版本并没有动过 MySQL 一行代码，仅仅只是在编译参数方面做了一些相关的调整，而让 MySQL 在某些特定场景下表现更优秀。

这样一说，听起来好像第三方发行的 MySQL 二进制版本要比 MySQL AB 官方提供的二进制发行版有更大的吸引力，那么我们是否就应该选用第三方提供的二进制发行版呢？先别着急，我们还需要进一步分析一下第三方发行版本可能存在哪些问题。

首先，由于第三方发行版本对 MySQL 所做的改动，很多都是为了应对发行者自己所处的特定场景而做出来的。所以，第三方发行版本并不一定适合其他所有使用者所处的环境。

其次，由于第三方发行版本的发行者并一定都是一个足够让人信任的公司（或者个人），在其生成自己的发行版本之前，是否有做过足够全面的功能和性能测试我们不得而知，在我们使用的时候是否会出现 MySQL AB 官方的发行版本中并不存在的 bug？

最后，如果我们购买了 MySQL 的相关服务，而又使用了第三方的发行版本，当我们的系统出现问题的时候，恐怕 MySQL 的支持工程师的支持工作会大打折扣，甚至可能会拒绝提供支持。

如果大家可以完全抛开以上这些可能存在隐患的顾虑，完全可以尝试使用非 MySQL AB 官方提供的二进制版本，而选用可能具有更多特性或者更高性能的发行版本了。

之前我也对网络上各种第三方二进制分发版本做过一些测试和比较，也发现了一些比较不错的版本，如 Percona 在整合了一些比较优秀的 Patch 之后的发行版本整体质量都还不错，使用者也比较多。当然，Percona 不仅仅分发二进制版本，同时也分发整合了一些优秀 Patch 的源码包。对于希望使 Percona 提供的一些 Patch 的朋友，同时又希望能够自行编译以进一步优化和定制 MySQL 的朋友，也可以下载 Percona 提供的源码包。

对于二进制分发版本的安装，对于安装本身来说，我们基本上没有太多可以优化的地方，唯一可以做的就是当我们决定了选择第三方分发版本之后，可以根据自身环境和应用特点来选择适合我们环境的优化发行版本来安装。

②源码安装

与二进制发行版本相比，如果我们选择了通过源代码进行安装，那么在安装过程中我们能够对 MySQL 所做的调整将会更多更灵活一些。因为通过源代码编译我们可以：

• 针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码；
• 根据不同的软件平台环境调整相关的编译参数；
• 针对我们特定应用场景选择需要什么组件不需要什么组件；
• 根据我们的所需要存储的数据内容选择只安装我们需要的字符集；
• 同一台主机上面可以安装多个 MySQL；
• 等等其他一些可以根据特定应用场景所作的各种调整。

在源码安装给我们带来更大灵活性的同时，同样也给我们带来了可能引入的隐患：

• 对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定；
• 对自己的应用环境把握失误而使用的优化参数可能反而使系统性能更差；
• 还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过程更为复杂，所花费的时间更长；

通过源码安装的最大特点就是可以让我们自行调整编译参数，最大程度的定制安装结果。下面我将自己在通过源码编译安装中的一些优化心得做一个简单的介绍，希望能够对大家有所帮助。

在通过源码安装的时候，最关键的一步就是配置编译参数，也就是执行通过 configure 命令所设定的各种编译选项。我们可以在 MySQL 源码所在的文件夹下面通过执行执行“./configure —help”得到可以设置的所有编译参数选项，如下：

`configure' configures this package to adapt to many kinds of systems.
Usage: ./configure [OPTION]... [VAR=VALUE]...
... ...
Installation directories:
--prefix=PREFIX install architecture-independent files in PREFIX
... ...
For better control, use the options below.
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。我们可以如下查看系统慢查询日志的相关设置：

mysql> show variables like 'log_slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries |   ON  |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'long_query%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time |   1   |
+-----------------+-------+
1 row in set (0.01 sec)
复制代码

“ 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 给我们带来的更为详细的功能和使用，大家请参考官方介 绍 （ www.mysqlperformanceblog.com/2008/04/20/…

打开 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 等。

上面介绍的这些都是我们可以怎样配置网络连接交互以及连接线程的性能相关参数，下面我们再看看该怎样检验上面所做的设置是否合理，是否有需要调整的地方。我们可以通过在系统中执行如下的几个命令来获得相关的状态信息来帮助大家检验设置的合理性：

我们现看看连接线程相关的系统变量的设置值：

mysql> show variables like 'thread%';
+-------------------+--------+
| Variable_name     |  Value |
+-------------------+--------+
| thread_cache_size |   64   | 
| thread_stack 	    | 196608 |
+-------------------+--------+
复制代码

再来看一下系统被连接的次数以及当前系统中连接线程的状态值：

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   |  127  |
+---------------+-------+

mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name  	 | Value |
+------------------------+-------+
| Delayed_insert_threads |   0   | 
| Slow_launch_threads 	 |   0   | 
| Threads_cached 	 |   4   |
| Threads_connected 	 |   7   | 
| Threads_created 	 |   11  | 
| Threads_running 	 |   1   |
+------------------------+-------+
复制代码

通过上面的命令，我们可以看出，系统设置了Thread Cache 池最多将缓存32 个连接线程，每个连接线程创建之初，系统分配192KB的内存堆栈空给他。系统启动到现在共接收到客户端的连接127 次，共创建了 11 个连接线程，但前有 7 个连接线程处于和客户端连接的状态，而 7 个连接状态的线程中只有一个是 active 状态，也就是说只有一个正在处理客户端提交的俄请求。而在 Thread Cache 池中当共 Cache 了 4 个连接线程。

通过系统设置和当前状态的分析，我们可以发现，thread_cache_size 的设置已经足够了，甚至还远大于系统的需要。所以我们可以适当减少 thread_cache_size 的设置，比如设置为 8 或者 16。根据 Connections 和 Threads_created 这两个系统状态值，我们还可以计算出系统新建连接连接的 Thread Cache 命中率，也就是通过 Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如下：

Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
复制代码

我们可以通过上面的这个运算公式计算一下上面环境中的 Thread Cache 命中率：Thread_Cache_Hit = (127 - 12) / 127 * 100% = 90.55%

一般来说，当系统稳定运行一段时间之后，我们的 Thread Cache 命中率应该保持在 90%左右甚至更高的比率才算正常。可以看出上面环境中的 Thread Cache 命中比率基本还算是正常的。

②Table Cache 相关的优化

我们先来看一下 MySQL 打开表的相关机制。由于多线程的实现机制，为了尽可能的提高性能，在 MySQL 中每个线程都是独立的打开自己需要的表的文件描述符，而不是通过共享已经打开的表的文件描述符的机制来实现。当然，针对于不同的存储引擎可能有不同的处理方式。如 MyISAM 表，每一个客户端线程打开任何一个 MyISAM 表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程共享同一个索引文件的描述符。对于 Innodb 的存储引擎，如果我们使用的是共享表空间来存储数据，那么我们需要打开的文件描述符就比较少，而如果我们使用的是独享表空间方式来存储数据，则同样，由于存储表数据的数据文件较多，则同样会打开很多的表文件描述符。除了数据库的实际表或者索引打开以外，临时文件同样也需要使用文件描述符，同样会占用系统中 open_files_limit 的设置限额。

为了解决打开表文件描述符太过频繁的问题，MySQL 在系统中实现了一个 Table Cache 的机制，和前面介绍的 Thread Cache 机制有点类似，主要就是 Cache 打开的所有表文件的描述符，当有新的请求的时候不需要再重新打开，使用结束的时候也不用立即关闭。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。我们先看一看 Table Cache 相关的系统参数及状态变量。

在 MySQL 中我们通过 table_cache（从 MySQL5.1.3 开始改为 table_open_cache），来设置系统中为我们 Cache 的打开表文件描述符的数量。通过 MySQL 官方手册中的介绍，我们设置 table_cache 大小的时候应该通过 max_connections 参数计算得来，公式如下：

table_cache = max_connections * N；
复制代码

其中 N 代表单个 Query 语句中所包含的最多 Table 的数量。但是我个人理解这样的计算其实并不是太准确，分析如下：

首先，max_connections 是系统同时可以接受的最大连接数，但是这些连接并不一定都是 active 状态的，也就是说可能里面有不少连接都是处于 Sleep 状态。而处于 Sleep 状态的连接是不可能打开任何 Table 的。

其次，这个 N 为执行 Query 中包含最多的 Table 的 Query 所包含的 Table 的个数也并不是太合适，因为我们不能忽略索引文件的打开。虽然索引文件在各个连接线程之间是可以共享打开的连接描述符的，但总还是需要的。而且，如果我 Query 中的每个表的访问都是通过现通过索引定位检索的，甚至可能还是通过多个索引，那么该 Query 的执行所需要打开的文件描述符就更多了，可能是 N 的两倍甚至三倍。

最后，这个计算的公式只能计算出我们同一时刻需要打开的描述符的最大数量，而 table_cache 的设置也不一定非得根据这个极限值来设定，因为 table_cache 所设定的只是Cache 打开的描述符的数量的大小，而不是最多能够打开的量的大小。

当然，上面的这些只是我个人的理解，也可能并不是太严谨，各位读者朋友如果觉得有其他的理解完全可以提出来大家再探讨。

我们可以通过如下方式查看 table_cache 的设置和当前系统中的使用状况：

mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   |  512  |
+---------------+-------+

mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables 	|   6   |
+---------------+-------+
复制代码

上面的结果显示系统设置的 table_cache 为 512 个，也就是说在该 MySQL 中，Table Cache 中可以 Cache 512 个打开文件的描述符；当前系统中打开的描述符仅仅则只有 6 个。

那么Table Cache池中Cache的描述符在什么情况下会被关闭呢？一般来说主要有以下集中情况会出现被 Cache 的描述符被关闭：

• Table Cache的Cache池满了，而某个连接线程需要打开某个不在Table Cache中的表时，MySQL 会通过一定的算法关闭某些没有在使用中的描述符；
• 当我们执行 Flush Table 等命令的时候，MySQL 会关闭当前 Table Cache 中 Cache 的所有文件描述符；
• 当 Table Cache 中 Cache 的量超过 table_cache 参数设置的值的时候；

③Sort Buffer，Join Buffer 和 Read Buffer

在 MySQL 中，之前介绍的多种 Cache 之外，还有在 Query 执行过程中的两种 Buffer 会对数据库的整体性能产生影响。

mysql> show variables like '%buffer%';
+-------------------------------+----------+
| Variable_name 		|   Value  |
+-------------------------------+----------+
... ...
| join_buffer_size		|  4190208 |
... ...
| sort_buffer_size 		|  2097144 |
+-------------------------------+----------+
复制代码

• join_buffer_size：当我们的 Join 是 ALL，index，rang 或者 index_merge 的时候使用的 Buffer；

实际上这种 Join 被称为 Full Join。实际上参与 Join 的每一个表都需要一个 Join Buffer，所以在 Join 出现的时候，至少是两个。Join Buffer 的设置在 MySQL 5.1.23 版本之前最大为 4GB，但是从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系统默认是 128KB。

• sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；

Sort Buffer 同样是针对单个 Thread 的，所以当多个 Thread 同时进行排序的时候，系统中就会出现多个 Sort Buffer。一般我们可以通过增大 Sort Buffer 的大小来提高 ORDER BY 或者是 GROUP BY 的处理性能。系统默认大小为 2MB，最大限制和 Join Buffer 一样，在 MySQL 5.1.23 版本之前最大为 4GB，从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4GB 的限制。

如果应用系统中很少有Join 语句出现，则可以不用太在乎 join_buffer_size 参数的大小设置，但是如果 Join 语句不是很少的话，个人建议可以适当增大 join_buffer_size 的设置到 1MB 左右，如果内存充足甚至可以设置为 2MB。对于 sort_buffer_size 参数来说，一般设置为 2MB 到 4MB 之间可以满足大多数应用的需求。当然，如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以继续增大 sort_buffer_size 的设置。在这两个 Buffer 设置的时候，最需要注意的就是不要忘记是每个 Thread 都会创建自己独立的 Buffer，而不是整个系统共享的 Buffer，不要因为设置过大而造成系统内存不足。

总结

通过参数设置来进行性能优化所能够带来的性能提升可能并不会如很多人想象的那样产生质的飞跃，除非是之前的设置存在严重的不合理情况。我们不能将性能调优完全依托在通过 DBA 在数据库上线后的参数调整之上，而应该在系统设计和开发阶段就尽可能减少性能问题。当然，也不能否认参数调整在某些场景下对系统性能的影响比较大，但毕竟只是少数的特殊情况。

喜欢的朋友可以点点关注哦，下一篇文章更新《常用存储引擎优化》！