通过 show status 优化数据库性能

1、使用show status查看MySQL服务器状态信息

MySQL客户端连接成功后，通过 show [session|global] 命令可以查询服务器的状态信息，也可以在操作系统上使用 mysql admin extended -status命令获取这些信息。可以通过查询表的方式来查询状态变量的值，MySQL 5.6查询 INFORMATION_SCHEMA.GLOBAL_STATUS 和INFORMATION_SCHEMA.SESSION_STATUS；MySQL 5.7查询 performance_schema.global_status 和 performance_schema.session_status。

命令	含义
show status like 'uptime';	查询当前MySQL本次启动后的运行统计时间（单位：秒）。
show status like 'com_select';	com_select表示本次MySQL启动后执行的SELECT语句的次数，1次查询只累加1，执行错误的SELECT 语句，该值也会加1。com_insert、com_update和com_delete分别表示insert、update、delete语句的执行次数。需要注意的是，这4个参数对于所有存储引擎的表操作都会进行累加，而参数Innodb_rows_deleted、Innodb_rows_inserted、Innodb_rows_read和Innodb_rows_updated的值只针对InnoDB存储引擎。
show status like 'Thread_%';	MySQL服务器的线程信息。threads_cached表示线程缓存内的线程的数量，threads_connected表示当前打开的连接的数量，threads_created表示创建用来处理连接的线程数。如果Threads_created较大，那么可能要增加thread_cache_size值。threads_running表示激活的（非睡眠状态）线程数。
show status like 'connections';	查看试图连接到MySQL（不管是否连接成功）的连接数
show status like 'table_locks_immediate';	查看立即获得的表的锁的次数。
show status like 'table_locks_waited';	查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，那么应首先优化查询，然后拆分表或使用复制。
show status like 'slow_launch_threads';	查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_queries';	慢查询的次数，即查看查询时间超过long_query_time秒的查询的个数。，如果慢查询很多，那么可以通过慢查询日志或者show processlist检查慢查询语句。
Max_used_connections	如果显示的链接数过大，留意当前服务器的并发数，单台服务器是不是已经不堪重负了。一般的，连接数应该为最大链接数的85%左右。

状态名	作用域	详细解释
Aborted_clients	Global	由于客户端没有正确关闭连接导致客户端终止而中断的连接数
Aborted_connects	Global	试图连接到MySQL服务器而失败的连接数
Binlog_cache_disk_use	Global	使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
Binlog_cache_use	Global	使用临时二进制日志缓存的事务数量
Bytes_received	Both	从所有客户端接收到的字节数。
Bytes_sent	Both	发送给所有客户端的字节数。
com*		各种数据库操作的数量
Compression	Session	客户端与服务器之间只否启用压缩协议
Connections	Global	试图连接到(不管是否成功)MySQL服务器的连接数
Created_tmp_disk_tables	Both	服务器执行语句时在硬盘上自动创建的临时表的数量
Created_tmp_files	Global	mysqld已经创建的临时文件的数量
Created_tmp_tables	Both	服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘
Delayed_errors	Global	用INSERT DELAYED写的出现错误的行数(可能为duplicate key)。
Delayed_insert_threads	Global	使用的INSERT DELAYED处理器线程数。
Delayed_writes	Global	写入的INSERT DELAYED行数
Flush_commands	Global	执行的FLUSH语句数。
Handler_commit	Both	内部提交语句数
Handler_delete	Both	行从表中删除的次数。
Handler_discover	Both	MySQL服务器可以问NDB CLUSTER存储引擎是否知道某一名字的表。这被称作发现。Handler_discover说明通过该方法发现的次数。
Handler_prepare	Both	A counter for the prepare phase of two-phase commit operations.
Handler_read_first	Both	索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。
Handler_read_key	Both	根据键读一行的请求数。如果较高，说明查询和表的索引正确。
Handler_read_next	Both	按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev	Both	按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd	Both	根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。
Handler_read_rnd_next	Both	在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
Handler_rollback	Both	内部ROLLBACK语句的数量。
Handler_savepoint	Both	在一个存储引擎放置一个保存点的请求数量。
Handler_savepoint_rollback	Both	在一个存储引擎的要求回滚到一个保存点数目。
Handler_update	Both	在表内更新一行的请求数。
Handler_write	Both	在表内插入一行的请求数。
Innodb_buffer_pool_pages_data	Global	包含数据的页数(脏或干净)。
Innodb_buffer_pool_pages_dirty	Global	当前的脏页数。
Innodb_buffer_pool_pages_flushed	Global	要求清空的缓冲池页数
Innodb_buffer_pool_pages_free	Global	空页数。
Innodb_buffer_pool_pages_latched	Global	在InnoDB缓冲池中锁定的页数。这是当前正读或写或由于其它原因不能清空或删除的页数。
Innodb_buffer_pool_pages_misc	Global	忙的页数，因为它们已经被分配优先用作管理，例如行锁定或适用的哈希索引。该值还可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。
Innodb_buffer_pool_pages_total	Global	缓冲池总大小（页数）。
Innodb_buffer_pool_read_ahead_rnd	Global	InnoDB初始化的“随机”read-aheads数。当查询以随机顺序扫描表的一大部分时发生。
Innodb_buffer_pool_read_ahead_seq	Global	InnoDB初始化的顺序read-aheads数。当InnoDB执行顺序全表扫描时发生。
Innodb_buffer_pool_read_requests	Global	InnoDB已经完成的逻辑读请求数。
Innodb_buffer_pool_reads	Global	不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
Innodb_buffer_pool_wait_free	Global	一般情况，通过后台向InnoDB缓冲池写。但是，如果需要读或创建页，并且没有干净的页可用，则它还需要先等待页面清空。该计数器对等待实例进行记数。如果已经适当设置缓冲池大小，该值应小。
Innodb_buffer_pool_write_requests	Global	向InnoDB缓冲池的写数量。
Innodb_data_fsyncs	Global	fsync()操作数。
Innodb_data_pending_fsyncs	Global	当前挂起的fsync()操作数。
Innodb_data_pending_reads	Global	当前挂起的读数。
Innodb_data_pending_writes	Global	当前挂起的写数。
Innodb_data_read	Global	至此已经读取的数据数量（字节）。
Innodb_data_reads	Global	数据读总数量。
Innodb_data_writes	Global	数据写总数量。
Innodb_data_written	Global	至此已经写入的数据量（字节）。
Innodb_dblwr_pages_written	Global	已经执行的双写操作数量
Innodb_dblwr_writes	Global	双写操作已经写好的页数
Innodb_log_waits	Global	我们必须等待的时间，因为日志缓冲区太小，我们在继续前必须先等待对它清空
Innodb_log_write_requests	Global	日志写请求数。
Innodb_log_writes	Global	向日志文件的物理写数量。
Innodb_os_log_fsyncs	Global	向日志文件完成的fsync()写数量。
Innodb_os_log_pending_fsyncs	Global	挂起的日志文件fsync()操作数量。
Innodb_os_log_pending_writes	Global	挂起的日志文件写操作
Innodb_os_log_written	Global	写入日志文件的字节数。
Innodb_page_size	Global	编译的InnoDB页大小(默认16KB)。许多值用页来记数；页的大小很容易转换为字节。
Innodb_pages_created	Global	创建的页数。
Innodb_pages_read	Global	读取的页数。
Innodb_pages_written	Global	写入的页数。
Innodb_row_lock_current_waits	Global	当前等待的待锁定的行数。
Innodb_row_lock_time	Global	行锁定花费的总时间，单位毫秒。
Innodb_row_lock_time_avg	Global	行锁定的平均时间，单位毫秒。
Innodb_row_lock_time_max	Global	行锁定的最长时间，单位毫秒。
Innodb_row_lock_waits	Global	一行锁定必须等待的时间数。
Innodb_rows_deleted	Global	从InnoDB表删除的行数。
Innodb_rows_inserted	Global	插入到InnoDB表的行数。
Innodb_rows_read	Global	从InnoDB表读取的行数。
Innodb_rows_updated	Global	InnoDB表内更新的行数。
Key_blocks_not_flushed	Global	键缓存内已经更改但还没有清空到硬盘上的键的数据块数量。
Key_blocks_unused	Global	键缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
Key_blocks_used	Global	键缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块。
Key_read_requests	Global	从缓存读键的数据块的请求数。
Key_reads	Global	从硬盘读取键的数据块的次数。如果Key_reads较大，则Key_buffer_size值可能太小。可以用Key_reads/Key_read_requests计算缓存损失率。
Key_write_requests	Global	将键的数据块写入缓存的请求数。
Key_writes	Global	向硬盘写入将键的数据块的物理写操作的次数。
Last_query_cost	Session	用查询优化器计算的最后编译的查询的总成本。用于对比同一查询的不同查询方案的成本。默认值0表示还没有编译查询。 默认值是0。Last_query_cost具有会话范围。
Max_used_connections	Global	服务器启动后已经同时使用的连接的最大数量。
ndb*		ndb集群相关
Not_flushed_delayed_rows	Global	等待写入INSERT DELAY队列的行数。
Open_files	Global	打开的文件的数目。
Open_streams	Global	打开的流的数量(主要用于记录)。
Open_table_definitions	Global	缓存的.frm文件数量
Open_tables	Both	当前打开的表的数量。
Opened_files	Global	文件打开的数量。不包括诸如套接字或管道其他类型的文件。 也不包括存储引擎用来做自己的内部功能的文件。
Opened_table_definitions	Both	已经缓存的.frm文件数量
Opened_tables	Both	已经打开的表的数量。如果Opened_tables较大，table_cache 值可能太小。
Prepared_stmt_count	Global	当前的预处理语句的数量。 (最大数为系统变量: max_prepared_stmt_count)
Qcache_free_blocks	Global	查询缓存内自由内存块的数量。
Qcache_free_memory	Global	用于查询缓存的自由内存的数量。
Qcache_hits	Global	查询缓存被访问的次数。
Qcache_inserts	Global	加入到缓存的查询数量。
Qcache_lowmem_prunes	Global	由于内存较少从缓存删除的查询数量。
Qcache_not_cached	Global	非缓存查询数(不可缓存，或由于query_cache_type设定值未缓存)。
Qcache_queries_in_cache	Global	登记到缓存内的查询的数量。
Qcache_total_blocks	Global	查询缓存内的总块数。
Queries	Both	服务器执行的请求个数，包含存储过程中的请求。
Questions	Both	已经发送给服务器的查询的个数。
Rpl_status	Global	失败安全复制状态(还未使用)。
Select_full_join	Both	没有使用索引的联接的数量。如果该值不为0,你应仔细检查表的索引
Select_full_range_join	Both	在引用的表中使用范围搜索的联接的数量。
Select_range	Both	在第一个表中使用范围的联接的数量。一般情况不是关键问题，即使该值相当大。
Select_range_check	Both	在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为0，你应仔细检查表的索引。
Select_scan	Both	对第一个表进行完全扫描的联接的数量。
Slave_heartbeat_period	Global	复制的心跳间隔
Slave_open_temp_tables	Global	从服务器打开的临时表数量
Slave_received_heartbeats	Global	从服务器心跳数
Slave_retried_transactions	Global	本次启动以来从服务器复制线程重试次数
Slave_running	Global	如果该服务器是连接到主服务器的从服务器，则该值为ON。
Slow_launch_threads	Both	创建时间超过slow_launch_time秒的线程数。
Slow_queries	Both	查询时间超过long_query_time秒的查询的个数。
Sort_merge_passes	Both	排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。
Sort_range	Both	在范围内执行的排序的数量。
Sort_rows	Both	已经排序的行数。
Sort_scan	Both	通过扫描表完成的排序的数量。
ssl＊		ssl连接相关
Table_locks_immediate	Global	立即获得的表的锁的次数。
Table_locks_waited	Global	不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
Threads_cached	Global	线程缓存内的线程的数量。
Threads_connected	Global	当前打开的连接的数量。
Threads_created	Global	创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。
Threads_running	Global	激活的（非睡眠状态）线程数。
Uptime	Global	服务器已经运行的时间（以秒为单位）。
Uptime_since_flush_status	Global	最近一次使用FLUSH STATUS 的时间（以秒为单位）。

2、指令说明

SHOW [统计范围] STATUS [LIKE '状态项名称']

说明：统计范围关键字分为 GLOBAL 和 SESSION (或LOCAL) 两种。不写默认为 session，也就是只统计当前连接的状态信息。

 

（1）查看当前MySQL本次启动后的运行统计时间

mysql> show status like 'uptime';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Uptime        | 15785694 |
+---------------+----------+

（2）查看当前MySQL会话执行的SELECT语句的次数

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+

（3）查看MySQL服务器的线程信息

mysql> show status like 'Thread_%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Threadpool_idle_threads | 56     |
| Threadpool_threads      | 57     |
| Threads_cached          | 0      |
| Threads_connected       | 24     |
| Threads_created         | 785464 |
| Threads_running         | 1      |
+-------------------------+--------+

（4）查看从硬盘读取键的数据块的次数

mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 10485760 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show global status like 'key_read%';
+-------------------+-------------+
| Variable_name     | Value       |
+-------------------+-------------+
| Key_read_requests | 18791647083 |
| Key_reads         | 18343825    |
+-------------------+-------------+
2 rows in set (0.00 sec)

key_buffer_size 是对 myisam 引擎影响很大的一个参数（目前mysql不应该再使用myisam引擎了，除了迫不得已的情况）。

上面命令可以得出一共有 18791647083 个索引请求，其中 18343825 个请求在内存中没有找到索引，而在硬盘中读取索引。

说明：一般myisam的索引是存储在内存当中的，当索引长度大于key_buffer_size的时候，myisam无法从内存中获取索引，这时应该调高key_buffer_size的值。

（5）慢查询统计

mysql> show variables like '%slow%'; 
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name             | Value                                                                                                        |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_admin_statements | ON                                                                                                           |
| log_slow_filter           | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit       | 1                                                                                                            |
| log_slow_slave_statements | ON                                                                                                           |
| log_slow_verbosity        |                                                                                                              |
| slow_launch_time          | 2                                                                                                            |
| slow_query_log            | ON                                                                                                           |
| slow_query_log_file       | /var/lib/mysql_3306/slow.log                                                                                 |
+---------------------------+--------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 17529 |
+---------------------+-------+

配置中打开了（slow_query_log）记录慢查询（最好是打开，方便优化），超过2秒（slow_launch_time）即为慢查询，一共有17529条（slow_queries）慢查询 

（6）排序使用情况

mysql> show global status like 'sort%';  
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 0        |
| Sort_range        | 0        |
| Sort_rows         | 47961650 |
| Sort_scan         | 8220     |
+-------------------+----------+

mysql> show variables like '%Sort_buffer_size%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| myisam_sort_buffer_size | 67108864 |
| sort_buffer_size        | 20971520 |
+-------------------------+----------+

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 并不一定能提高速度。

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处。

 

除此之外，还有以下常用指令

--查看insert语句的执行数
show [global] status like 'com_insert';
--查看update语句的执行数
show [global] status like 'com_update';
--查看delete语句的执行数
show [global] status like 'com_delete';
--查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';
--查看线程缓存内的线程的数量。
show status like 'threads_cached';
--查看当前打开的连接的数量。
show status like 'threads_connected';
--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';
--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';
--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';
--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';
--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';
--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';
--查看当前慢查询(Slow_queries)的数量，如果慢查询很多，可以通过慢查询日志或者show processlist检查慢查询语句。
show status like '%slow%'; 
--查看使用的最大连接数。如果显示的连接数过大，留意当前服务器的并发数，单台服务器是不是已经不堪重负了。一般连接数应该为最大连接数的85%左右
show status like '%max_used_connections%';
show variables like '%max_connections%';

 

3、重点关注的性能状态

（1）key buffer 命中率

key buffer 命中率代表了myisam类型表的索引cache命中率，命中率的大小直接影响myisam类型表的读写性能。key buffer 命中率实际上包括读命中率和写命中率两种，mysql中并没有直接给出这两个命中率的值，但是可以通过如下方式计算：

key_buffer_read_hits=(1-key_reads/key_read_requests) * 100%

key_buffer_write_hits=(1-key_writes/key_write_requests)*100%

• Key_read_requests，表示索引读请求次数。
• Key_reads，表示请求在内存中没有找到直接从硬盘读取索引。
• Key_blocks_unused，表示未使用的缓存簇(blocks)数。
• Key_blocks_used，表示曾经用到的最大的blocks数。

获取所需要状态的变量值：

mysql> show status like 'key%';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_blocks_not_flushed | 0           |
| Key_blocks_unused      | 0           |
| Key_blocks_used        | 8279        |
| Key_blocks_warm        | 8279        |
| Key_read_requests      | 18792216953 |
| Key_reads              | 18343826    |
| Key_write_requests     | 5311061792  |
| Key_writes             | 1132741629  |
+------------------------+-------------+

key_buffer_read_hits=(1-18343826/18792216953) * 100%

key_buffer_write_hits=(1-1132741629/5311061792) * 100%

命中率过低，说明myisam类型表的读写存在问题。

（2）innodb buffer 命中率

这里 innodb buffer 所指的是innodb_buffer_pool，也就是用来缓存innodb类型表和索引的内在空间。类似key buffer，同样可以根据mysql提供的相应的状态信息计算其命中率：

innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%;

获取所需状态变量值：

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd     | 0            |
| Innodb_buffer_pool_read_ahead         | 316145818    |
| Innodb_buffer_pool_read_ahead_evicted | 1196903      |
| Innodb_buffer_pool_read_requests      | 239804713832 |
| Innodb_buffer_pool_reads              | 1155486264   |
+---------------------------------------+--------------+

命中率过低，说明innodb类型表的读写存在问题。

（3）query cache命中率

query cache 是mysql的查询cache，my.cnf配置文件若开启 querey cache，则可以对查询过的语句结果进行cache。对于一些用户数不高或一次性统计平台建议关闭查询缓存。若开启query cache，则对query cache 命中率进行监控也是需要的，它可以告诉我们是数据库是否在正确使用query cache。

query cache命中率计算如下：

query_cache_hits =(Qcache_hits/(Qcache_hits+Qcache_inserts))* 100%;

• 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：缓存中块的数量。 

获取变量值：

mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

• query_cache_limit：超过此大小的查询将不缓存 
• query_cache_min_res_unit：缓存块的最小大小 
• query_cache_size：查询缓存大小 
• query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询 
• query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。 

说明：

①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% 

（4）table_cache 命中率

table_cache 指定表调整缓存的大小，当mysql访问某个表时，若表缓存空间还有空间，则将该表就被打开并将数据放入其中，下次访问此表时可以更快的访问表的内容。通过查峰值时间的状态值open_tables 和 opened_tables可以决定是否需要增加table_cache值。

需要注意的是，如果 table_cache 设置太高，可能会造成文件描述符不足，从而造成性能不稳定或是连接失败。

table_cache的当前状态量可以帮助我们判断系统参数table_open_cache的设置是否合理。如果状态量open_tables与opened_tables之间的比率过低，则代表table cache设置过小，网上有人认为这值的比率设置在80%~85%最好。

• Open_tables，表示打开表的数量
• Opened_tables，表示打开过的表数量

获取变量：

mysql>  show status like 'open%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Open_files               | 448     |
| Open_streams             | 0       |
| Open_table_definitions   | 827     |
| Open_tables              | 1860    |
| Opened_files             | 7327691 |
| Opened_plugin_libraries  | 0       |
| Opened_table_definitions | 0       |
| Opened_tables            | 0       |
| Opened_views             | 0       |
+--------------------------+---------+

Open_tables / Opened_tables * 100%  理想值 >= 85%
Open_tables / table_cache * 100%  理想值  <= 95%

（5）thread cache命中率

在mysql中，为了尽可能提高客户端连接的过程，实现 了一个thread cache池，将空闲的连接线程存放在其中，而不是请求完成后销毁，当有新的连接请求的时候，mysql首先检查thread cache是否存储空闲的连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的线程。

thread cache命中率能直接反应出系统参数thread_cache_size设置是否合理。一个合理的read_cache_size参数能够节约大量创建新连接时所需要消耗的资源。

thread cache命中率计算方式如下：

thread_cache_hits = (1- threads_created/threads_connections) * 100 %;

获取所需要状态变量值：

mysql> show status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |
| Threads_connected | 103   |
| Threads_created   | 27675 |
| Threads_running   | 3     |
+-------------------+-------+

正常来说，thread cache命中率在90%以上才算合理。

（6）tmp table相关状况分析

tmp table 主要用于监控mysql使用临时表的量是否过多，是否有临时表过大而不得不从内存中换出到磁盘文件中，临时表使用状态 信息获取如下：

mysql> show status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 9     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Created_tmp_disk_tables为临时表过大无法在内存中完成，而不得不使用磁盘的次数。若create_tmp_tables非常大，则表示系统排序操作过多，或者可能是连接方式不是很优化。而如果是create_tmp_disk_table/create_tmp_tables比率过高，如超过10%，则需要考虑tmp_table_size参数是否需

要调整大些。建议tmp_table_size与max_heap_table_size需要设置成一样大。

mysql> show variables like '%table_size%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+

（7）binlog cache

若打开binlog日志功能，则需要考虑binlog cache问题。

binlog不是一有数据就写到binlog中，而是先写入到binlog cache中，再写入到binlog中。Binlog_cache_disk_use为binlog使用硬盘使用量， Binlog_cache_use 为binlog已使用的量。若 Binlog_cache_disk_use大于0，则说明binlog_cache不够用。

mysql> show status like 'binlog_cache%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Binlog_cache_disk_use | 0      |
| Binlog_cache_use      | 735059 |
+-----------------------+--------+

（8）innodb_log_waits

innodb_log_waits状态变量直接反应innodb log buffer 空间不足造成等待的次数。innodb_log_waits直接反应系统的写入性能，当值 达到每秒1次时，就需要增加innodb_log_buffer_size的值，适当的增加不会造成内存不足的问题。

mysql> show status like 'innodb_log_waits';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+

（9）复制延时量

复制延时量：复制延时量将直接影响了Slave数据库处于不一致状态的时间长短。如果我们是通过Slave来提供读服务，就不得不重视这个延时量。我们可以通过在Slave节点上执行“SHOW SLAVE STATUS”命令，取Seconds_Behind_Master项的值来了解Slave当前的延时量（单位：秒）。当

然，该值的准确性依赖于复制是否处于正常状态。每个环境下的Slave所允许的延时长短与具体环境有关，所以复制延时多长时间是合理的，只能由读者朋友根据各自实际的应用环境来判断。

（10）锁状态

mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。

mysql> show status like '%lock%';
+------------------------------------------+------------+
| Variable_name                            | Value      |
+------------------------------------------+------------+
| Aria_pagecache_blocks_not_flushed        | 0          |
| Aria_pagecache_blocks_unused             | 15706      |
| Aria_pagecache_blocks_used               | 547        |
| Binlog_group_commit_trigger_lock_wait    | 0          |
| Com_lock_tables                          | 0          |
| Com_unlock_tables                        | 0          |
| Handler_external_lock                    | 0          |
| Innodb_row_lock_current_waits            | 2          |
| Innodb_row_lock_time                     | 4530820    |
| Innodb_row_lock_time_avg                 | 3          |
| Innodb_row_lock_time_max                 | 11911      |
| Innodb_row_lock_waits                    | 1161899    |
| Key_blocks_not_flushed                   | 0          |
| Key_blocks_unused                        | 0          |
| Key_blocks_used                          | 830        |
| Key_blocks_warm                          | 830        |
| Performance_schema_locker_lost           | 0          |
| Performance_schema_rwlock_classes_lost   | 0          |
| Performance_schema_rwlock_instances_lost | 0          |
| Qcache_free_blocks                       | 0          |
| Qcache_total_blocks                      | 0          |
| Table_locks_immediate                    | 6315060596 |
| Table_locks_waited                       | 25998163   |
+------------------------------------------+------------+

如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决。