MySQL性能调优与架构设计——第10章 MySQL数据库Schema设计的性能优化

第10章 MySQL Server性能优化

前言：

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

10.1 MySQL 安装优化

选择合适的发行版本

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

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

使用MySQL AB提供的二进制发行版本我们可以得到哪些好处？
a) 通过非常简单的安装方式快速完成MySQL的部署；
b) 安装版本是经过比较完善的功能和性能测试的编译版本；
c) 所使用的编译参数更具通用性的，且比较稳定；
d) 如果购买了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提供的源码包。

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

2. 源码安装

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

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

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

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

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

在通过源码安装的时候，最关键的一步就是配置编译参数，也就是执行通过 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

10.2 MySQL 日志设置优化

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

日志产生的性能影响

由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源，所以对于日志的在之前介绍 MySQL物理架构的章节中，我们已经了解到了 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%';

“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）或者性能影响很少，我们就不在此过多分析了，至于各个存储引擎相关的日志，我们留在后面“常用存储引擎优化”部分再做相应的分析。

10.3 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之后所带来的负面影响：

a) Query语句的hash运算以及hash查找资源消耗。当我们使用Query Cache之后，每条SELECT 类型的Query在到达MySQL之后，都需要进行一个 hash运算然后查找是否存在该 Query的 Cache，虽然这个hash运算的算法可能已经非常高效了，hash查找的过程也已经足够的优化了，对于一条Query来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千条Query的时候，我们就不能对产生的CPU的消耗完全忽视了。

b) Query Cache的失效问题。如果我们的表变更比较频繁，则会造成Query Cache的失效率非常高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我们每次缓存到Query Cache中的Cache数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同Query进来之后无法使用到之前的Cache。

c) 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%';

● “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，而不是物理的数据页，所以在性能提升的同时，也会受到一些特定的限制。

a) 5.1.17之前的版本不能Cache 帮定变量的Query，但是从5.1.17版本开始，Query Cache已经开始支持绑定变量的Query了；

b) 所有子查询中的外部查询SQL不能被Cache；

c) 在Procedure，Function以及Trigger中的Query不能被Cache；

d) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。
鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入 Query Cache，仅仅让某些 Query 的查询结果被 Cache。

10.4 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 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池最多将缓存64个连接线程，每个连接线程创建之初，系统分配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的描述符被关闭：

a) Table Cache的Cache池满了，而某个连接线程需要打开某个不在Table Cache中的表时，MySQL 会通过一定的算法关闭某些没有在使用中的描述符；

b) 当我们执行Flush Table等命令的时候，MySQL会关闭当前Table Cache中Cache的所有文件描述符；

c) 当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，不要因为设置过大而造成系统内存不足。

10.5 小结

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

摘自：《MySQL性能调优与架构设计》简朝阳

转载请注明出处：

作者：JesseLZJ
出处：http://jesselzj.cnblogs.com

原标题：MySQL性能调优与架构设计——第10章 MySQL数据库Schema设计的性能优化

关键词：MYSQL