第十章：第30节 MySQL进阶篇——variables和status部分详解

慢查询、事务的隔离、查询缓存 已讲过了，我们再来简单举几个在优化MySQL时会用到的系统变量和状态变量：

1）连接数

mysql如果出现”mysql: error 1040: too many connections”的情况，一种是访问量确实很高，mysql服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是mysql配 置文件中max_connections值过小：

show variables like 'max_connections';

楠神本地mysql服务器最大连接数是512，意思就是最大允许512个客户端同时连接MySQL服务器（前提是我本地计算机能撑得住）。在实际项目中，假如项目平台流量很高，请求MySQL的次数也会变得很高，一台服务器撑不住了，我们需要多增加专门的主机做MySQL服务器。大家可能听过了，针对大流量，MySQL的解决方案是读写分离（后面我们再去介绍）。

我们也可以查看这个状态值（status）获取服务器曾响应过的最大连接数：

show global status like 'max_used_connections';

可通过下面公式来查看MySQL的连接数设置的是否合理：

10% < max_used_connections / max_connections * 100% < 85%

如果发现比例在10%以下，mysql服务器连接数上限设置的过高了；如果比例超过了85%，那连接数设置低了，需要加大连接数，或者采取其他优化措施。

2）key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb 

show variables like 'key_buffer_size';

show global status like 'key_read%';

一共有120个索引读取请求，有23个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%

假如Key_read_requests 和 key_buffer_size数很接近了，索引未命中缓存概率很高的话，需要适当加大key_buffer_size 

show global status like 'key_blocks_u%';

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数 
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% （理想值 ≈ 80%） 

3）临时表

show global status like 'created_tmp%';

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数： 
Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值<= 25%）

show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');

小于max_heap_table_size值的临时表才能全部放内存，超过的就会用到硬盘临时表。

4）open table 的情况 

show global status like 'open%tables%'; 

Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_open_cache值 

show variables like 'table_open_cache';

Open_tables / Opened_tables * 100% =69% 理想值 （>= 85%） 
Open_tables / table_open_cache * 100% = 100% 理想值 (<= 95%) 

5)线程使用情况

show global status like 'Thread%';

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置： 

show variables like 'thread_cache_size'; 

thread_cache_size
每建立一个连接，都需要一个线程来与之匹配。
thread_cache_size：用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

show global 里关于线程缓存有如下几个状态变量，这里作简单解释一下。
Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。
最好将thread_cache_size设置成与threads_connected一样。不过很少有情况将threads_cache_size设置成比200还大的数。

6）排序使用情况

show global status like 'sort%';

show variables like 'Sort_buffer_size';

Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度，见 How fast can you sort data with MySQL?（引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html） 

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处，参见：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is- read_rnd_buffer_size/ 

7）文件打开数(open_files) 

show global status like 'open_files'; 

show variables like 'open_files_limit'; 

比较合适的设置：Open_files / open_files_limit * 100% <= 75％ 

8）表锁情况 

show global status like 'table_locks%';

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数

9）表扫描情况 

show global status like 'handler_read%'; 

如上所示，mysql中关于read的计数器，有7个。他们的数值对于系统的状况的了解，对于系统的调优都十分重要。

首先7个计数器，我们应该分为两部分：
1）对索引读的计数器：前面的5个都是对索引读情况的计数器，
     Handler_read_first：是指读索引的第一项（的次数）；
     Handler_read_key：是指读索引的某一项（的次数）；
     Handler_read_next：是指读索引的下一项（的次数）；
     Handler_read_last：是指读索引的最后第一项（的次数）；
     Handler_read_prev：是指读索引的前一项（的次数）；

5者应该有四种组合：
1. Handler_read_first 和 Handler_read_next 组合应该是索引覆盖扫描
2. Handler_read_key 基于索引取值
3. Handler_read_key 和 Handler_read_next 组合应该是索引范围扫描
4. Handler_read_last 和 Handler_read_prev 组合应该是索引范围扫描（orde by desc）

2）对数据文件的计数器：后面的2个都是对数据文件读情况的计数器，这里很重要的一点要理解：索引项之间都是有顺序的，所以才有first, last, next, prev等等，所以前面的5个都是对索引读情况的计数器，而后面的2个是对数据文件的读情况的计数器。

很显然的一点：
后面的2个 Handler_read_rnd 和 Handler_read_rnd_next 是越低越好，如果很高，应该进行索引相关的调优。而Handler_read_key的数值肯定是越高越好，越高代表使用索引读很高。其他的计数器，要具体情况具体分析。

10）max_allowed_packet

一个查询语句包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。该值太小则会在处理大包时产生错误。如果使用大的BLOB列，必须增加该值。

楠神对这个参数印象还是比较深刻的，就是在导数据的时候会出错。为什么出错？主要是在insert时，有些字段的数据超过了max_allowed_packet设置的值，插不进去数据就报错了。

11）timeout

mysql有好几种timeout的情况，我们先用sql来查一下timeout的情况 show global variables like “%timeout%”;

connect_timeout 连接超时 mysql连接共有6次握手，3次TCP协议这个跟connect_timeout参数没有关系，另外3次跟connect_timeout参数有关系，该参数主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。

delayed_insert_timeout 这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间

interactive_timeout 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。参数默认值：28800秒（8小时）

lock_wait_timeout 锁等待超时时间

net_read_timeout / net_write_timeout 这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数

slave_net_timeout 解释：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了

wait_timeout 服务器关闭非交互连接之前等待活动的秒数

更详细推荐看《mysql的timeout》

《PHP实际应用中mysql出现“锁等待”实际问题分析研究》