转载

【MySQL】通过show status 优化数据库性能

官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-status.html

点击(此处)折叠或打开

mysql> ? show status
Name: 'SHOW STATUS'
Description:
Syntax:
SHOW [GLOBAL | SESSION] STATUS
[LIKE 'pattern' | WHERE expr]
*Note*:
As of MySQL 5.7.6, the value of the show_compatibility_56 system
variable affects the information available from and privileges required
for the statement described here. For details, see the description of
that variable in
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html.
SHOW STATUS provides server status information (see
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html).
This statement does not require any privilege. It requires only the
ability to connect to the server.
Status variable information is also available from these sources:
o Performance Schema tables. See
http://dev.mysql.com/doc/refman/5.7/en/performance-schema-status-vari
able-tables.html.
o The GLOBAL_STATUS and SESSION_STATUS tables. See
http://dev.mysql.com/doc/refman/5.7/en/status-table.html.
o The mysqladmin extended-status command. See
http://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html.
For SHOW STATUS, a LIKE clause, if present, indicates which variable
names to match. A WHERE clause can be given to select rows using more
general conditions, as discussed in
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.
SHOW STATUS accepts an optional GLOBAL or SESSION variable scope
modifier:
o With a GLOBAL modifier, the statement displays the global status
values. A global status variable may represent status for some aspect
of the server itself (for example, Aborted_connects), or the
aggregated status over all connections to MySQL (for example,
Bytes_received and Bytes_sent). If a variable has no global value,
the session value is displayed.
o With a SESSION modifier, the statement displays the status variable
values for the current connection. If a variable has no session
value, the global value is displayed. LOCAL is a synonym for SESSION.
o If no modifier is present, the default is SESSION.
The scope for each status variable is listed at
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html.
Each invocation of the SHOW STATUS statement uses an internal temporary
table and increments the global Created_tmp_tables value.
With a LIKE clause, the statement displays only rows for those
variables with names that match the pattern:
mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
+--------------------+----------+
URL: http://dev.mysql.com/doc/refman/5.7/en/show-status.html

MySQL客户端连接成功后，通过show [session|global]命令可以查询服务器的状态信息，也可以在操作系统上使用mysqladmin 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 的时间（以秒为单位）。

MySQL优化：使用show status查看MySQL服务器状态信息

在LAMP架构的网站开发过程中，有些时候我们需要了解MySQL的服务器状态信息，譬如当前MySQL启动后的运行时间，当前MySQL的客户端会话连接数，当前MySQL服务器执行的慢查询数，当前MySQL执行了多少SELECT语句、执行了多少UPDATE/DELETE/INSERT语句等统计信息，从而便于我们根据当前MySQL服务器的运行状态进行对应的调整或优化工作。

在MySQL中，我们可以使用SHOW STATUS指令语句来查看MySQL服务器的状态信息。下面，我们以DOS命令窗口的形式连接MySQL，并执行show status;指令，我们将看到如下显示信息：

【MySQL】通过show status 优化数据库性能执行show status指令显示的部分结果

当我们执行show status语句时，MySQL将会列出多达300多条的状态信息记录，其中包括了供我们查看了解的各种信息。不过，如果直接使用show status指令得到300多条记录，会让我们看得眼花缭乱，因此我们希望能够「按需查看」一部分状态信息。这个时候，我们可以在show status语句后加上对应的like子句。例如，我们想要查看当前MySQL启动后的运行时间，我们可以执行如下语句：

--查询当前MySQL本次启动后的运行统计时间 show status like 'uptime';

此时，我们就可以看到如下结果：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 5667  |
+---------------+-------+
1 row in set (0.00 sec)

同样的，如果我们要本次MySQL启动后执行的SELECT语句的次数，我们可以执行如下语句：

show status like 'com_select';

对应输出结果如下：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

此外，与WHERE子句中的LIKE关键字类似，show status后的LIKE关键字也可以使用'_' 或'%'等通配符来进行模糊匹配。例如我们可以执行如下语句来查看MySQL服务器的线程信息：

show status like 'Thread_%';

对应输出结果如下：

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

值得注意的是，在上述show status like 'com_select'指令的执行示例中，显示的SELECT语句统计信息仅仅表示当前会话连接执行的SELECT语句数量。因为，show status指令的完整语法如下：

SHOW [统计范围] STATUS [LIKE '状态项名称'] --统计范围关键字分为GLOBAL和SESSION(或LOCAL)两种。

在show status的完整语法中，"[]"中的部分是可选的，如果我们的show status语句中不包含统计范围关键字，则默认统计范围为SESSION，也就是只统计当前连接的状态信息。如果我们需要查询自当前MySQL启动后所有连接执行的SELECT语句总数，我们可以执行如下语句：

show global status like 'com_select';

以上即是show status的详细用法。由于show status的状态统计项较多，我们就不再一一解释每个统计项的具体含义，在这里，我们仅列出部分常用的状态信息查看语句：

--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';
--查看select语句的执行数
show [global] status like 'com_select';
--查看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';
--查看当前打开的连接的数量。
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';

show processlist可以检查mysql当前sql语句的执行情况，而show status就可以检查mysql当前的状态

命令：show status（PS：可以通过like来过滤一些不必要的信息）

这个命令返回的信息相当之多，一共返回了291行信息（不用版本可能会有所差异哈），我选择了几个比较重点的来进行分析。

1.慢查询

			[plain] view plain copy
			
			mysql> show status like '%slow%';   
		
			+---------------------+-------+   
		
			| Variable_name       | Value |   
		
			+---------------------+-------+   
		
			| Slow_launch_threads | 0     |   
		
			| Slow_queries        | 0     |   
		
			+---------------------+-------+

Slow_queries显示了当前慢查询的数量，如果慢查询很多，可以通过慢查询日志或者show processlist检查慢查询语句。

2.链接数

		[plain] view plain copy
		
		mysql> show status like '%max_used_connections%';   
	
		+----------------------+-------+   
	
		| Variable_name        | Value |   
	
		+----------------------+-------+   
	
		| Max_used_connections | 4     |   
	
		+----------------------+-------+

如果显示的链接数过大，留意当前服务器的并发数，单台服务器是不是已经不堪重负了。一般的，连接数应该为最大链接数的85%左右。

3.key_read

			[plain] view plain copy
			
			mysql> show variables like 'key_buffer_size';    
		
			+-----------------+---------+   
		
			| Variable_name   | Value   |   
		
			+-----------------+---------+   
		
			| key_buffer_size | 8384512 |   
		
			+-----------------+---------+   
		
			1 row in set (0.00 sec)   
		
			mysql> show global status like 'key_read%';           
		
			+-------------------+-------+   
		
			| Variable_name     | Value |   
		
			+-------------------+-------+   
		
			| Key_read_requests | 436   |   
		
			| Key_reads         | 6     |   
		
			+-------------------+-------+   
		
			2 rows in set (0.00 sec)

key_buffer_size是对myisam引擎影响很大的一个参数（目前mysql不应该再使用myisam引擎了，除了迫不得已的情况）。上面命令可以得出一共有436个索引请求，其中6个请求在内存中没有找到索引，而在硬盘中读取索引。

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

通过show status 优化数据库性能

		mysql数据库的性能状态监控点非常多，其中很多量都是不能忽视的必须监控的量，且90%以上的内容 可以在连接上mysql后执行show status 或是 show veriables的输出值 获得，需要注意的是以上的命令获得的状态值实际上是累计值，所以如果 要计算时段内的变化 量还需要稍加处理，下面看下几项需要重点关注的性能状态： 
	
		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% 
	
		获取所需要状态的变量值： 
	
		show status like 'key%' 
	
			mysql> show status like 'key%'; 
		
			+------------------------+-------+ 
		
			| Variable_name          | Value | 
		
			+------------------------+-------+ 
		
			| Key_blocks_not_flushed | 0     | 
		
			| Key_blocks_unused      | 13396 | 
		
			| Key_blocks_used        | 19    | 
		
			| Key_read_requests      | 71    | 
		
			| Key_reads              | 19    | 
		
			| Key_write_requests     | 1     | 
		
			| Key_writes             | 1     | 
		
			+------------------------+-------+ 
		
			7 rows in set (0.00 sec) 
		
		命中率过低，说明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         | 0        | 
		
			| Innodb_buffer_pool_read_ahead_evicted | 0        | 
		
			| Innodb_buffer_pool_read_requests      | 27269024 | 
		
			| Innodb_buffer_pool_reads              | 827      | 
		
			+---------------------------------------+----------+ 
		
		命中率过低，说明innodb类型表的读写存在问题。 
	
		3. query cache命中率 
	
		query cache 是mysql的查询cache，在my.cnf配置文件若打开，则可以对查询过的语句结果进行cache。对于一些用户数不高或一次性统计平台建议关闭查询缓存。若开启query cache，则对query cache 命中率进行监控也是需要的，它可以告诉我们是数据库是否在正确使用query cache。query cache命中率计算如下： 
	
		query_cache_hits =(Qcache_hits/(Qcache_hits+Qcache_inserts))* 100%; 
	
		获取变量值： 
	
			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     | 
		
			+-------------------------+-------+ 
		
			8 rows in set (0.00 sec) 
		
		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%最好。 
	
		获取变量： 
	
		mysql> show status like 'open%'; 
	
		+--------------------------+---------+ 
	
		| Variable_name            | Value   | 
	
		+--------------------------+---------+ 
	
		| Open_files               | 6       | 
	
		| Open_streams             | 0       | 
	
		| Open_table_definitions   | 87      | 
	
		| Open_tables              | 26      | 
	
		| Opened_files             | 1554504 | 
	
		| Opened_table_definitions | 0       | 
	
		| Opened_tables            | 0       | 
	
		+--------------------------+---------+ 
	
		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/connections) * 100 %; 
	
		获取所需要状态变量值： 
	
			mysql> show status like 'thread%'; 
		
			+-------------------+-------+ 
		
			| Variable_name     | Value | 
		
			+-------------------+-------+ 
		
			| Threads_cached    | 0     | 
		
			| Threads_connected | 1     | 
		
			| Threads_created   | 23581 | 
		
			| Threads_running   | 1     | 
		
			+-------------------+-------+ 
		
		正常来说，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       | 65    | 
		
			| Created_tmp_tables      | 0     | 
		
			+-------------------------+-------+ 
		
		Created_tmp_disk_tables为临时表过大无法在内存中完成，而不得不使用磁盘的次数。若create_tmp_tables非常大，则可甬系统排序操作过多，或者可能是连接方式不是很优化。而如果是create_tmp_dis_table/create_tmp_tables比率过高，如超过10%，则需要考虑tmp_table_size参数是否需要调整大些。建议tmp_table_size与max_heap_table_size需要设置成一样大。 
	
		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 | 58    | 
		
			| Binlog_cache_use      | 39785 | 
		
			+-----------------------+-------+ 
		
		8. innodb_log_waits 
	
		innodb_log_waits状态变量直接反应innodb log buffer 空间不足造成等待的次数。innodb_log_waits直接反应系统的写入性能，当值 达到每秒1次时，就需要增加innodb_log_buffer_size的值，适当的增加不会造成内存不足的问题。 
	
		9. 复制延时量 
	
		复制延时量：复制延时量将直接影响了Slave数据库处于不一致状态的时间长短。如果我们是通过Slave来提供读服务，就不得不重视这个延时量。我们可以通过在Slave节点上执行“SHOWSLAVESTATUS”命令，取Seconds_Behind_Master项的值来了解Slave当前的延时量（单位：秒）。当然，该值的准确性依赖于复制是否处于正常状态。每个环境下的Slave所允许的延时长短与具体环境有关，所以复制延时多长时间是合理的，只能由读者朋友根据各自实际的应用环境来判断。 
	
			mysql> show slave status/G 
		
			  Seconds_Behind_Master: NULL 
		
		10. 锁状态 
	
		mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。 
	
			mysql> show status like '%lock%'; 
		
			+------------------------------------------+---------+ 
		
			| Variable_name                            | Value   | 
		
			+------------------------------------------+---------+ 
		
			| Com_lock_tables                          | 0       | 
		
			| Com_unlock_tables                        | 0       | 
		
			| Innodb_row_lock_current_waits            | 0       | 
		
			| Innodb_row_lock_time                     | 0       | 
		
			| Innodb_row_lock_time_avg                 | 0       | 
		
			| Innodb_row_lock_time_max                 | 0       | 
		
			| Innodb_row_lock_waits                    | 0       | 
		
			| Key_blocks_not_flushed                   | 0       | 
		
			| Key_blocks_unused                        | 13396   | 
		
			| Key_blocks_used                          | 19      | 
		
			| 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                    | 1570736 | 
		
			| Table_locks_waited                       | 7294    | 
		
			+------------------------------------------+---------+ 
		
		如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决。

通过show status 来优化MySQL数据库

1, 查看MySQL服务器配置信息

		
			mysql> show variables;

2, 查看MySQL服务器运行的各种状态值

	
		mysql> show global status;

3, 慢查询

		
			mysql> show variables like '%slow%';   
		
			+------------------+-------+   
		
			| Variable_name    | Value |   
		
			+------------------+-------+   
		
			| log_slow_queries | OFF   |   
		
			| slow_launch_time | 2     |   
		
			+------------------+-------+   
		
			mysql> show global status like '%slow%';   
		
			+---------------------+-------+   
		
			| Variable_name       | Value |   
		
			+---------------------+-------+   
		
			| Slow_launch_threads | 0     |   
		
			| Slow_queries        | 279   |   
		
			+---------------------+-------+

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

4, 连接数

	
		mysql> show variables like 'max_connections';   
	
		+-----------------+-------+   
	
		| Variable_name   | Value |   
	
		+-----------------+-------+   
	
		| max_connections | 500   |   
	
		+-----------------+-------+   
	
		mysql> show global status like 'max_used_connections';   
	
		+----------------------+-------+   
	
		| Variable_name        | Value |   
	
		+----------------------+-------+   
	
		| Max_used_connections | 498   |   
	
		+----------------------+-------+

设置的最大连接数是500，而响应的连接数是498

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

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

		
			mysql> show variables like 'key_buffer_size';   
		
			+-----------------+----------+   
		
			| Variable_name   | Value    |   
		
			+-----------------+----------+   
		
			| key_buffer_size | 67108864 |   
		
			+-----------------+----------+   
		
			mysql> show global status like 'key_read%';   
		
			+-------------------+----------+   
		
			| Variable_name     | Value    |   
		
			+-------------------+----------+   
		
			| Key_read_requests | 25629497 |   
		
			| Key_reads         | 66071    |   
		
			+-------------------+----------+

一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27%
需要适当加大key_buffer_size

	
		mysql> show global status like 'key_blocks_u%';   
	
		+-------------------+-------+   
	
		| Variable_name     | Value |   
	
		+-------------------+-------+   
	
		| Key_blocks_unused | 10285 |   
	
		| Key_blocks_used   | 47705 |   
	
		+-------------------+-------+

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

6，临时表

		
			mysql> show global status like 'created_tmp%';   
		
			+-------------------------+---------+   
		
			| Variable_name           | Value   |   
		
			+-------------------------+---------+   
		
			| Created_tmp_disk_tables | 4184337 |   
		
			| Created_tmp_files       | 4124    |   
		
			| Created_tmp_tables      | 4215028 |   
		
			+-------------------------+---------+

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

	
		mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');   
	
		+---------------------+-----------+   
	
		| Variable_name       | Value     |   
	
		+---------------------+-----------+   
	
		| max_heap_table_size | 134217728 |   
	
		| tmp_table_size      | 134217728 |   
	
		+---------------------+-----------+

需要增加tmp_table_size

7,open table 的情况

		
			mysql> show global status like 'open%tables%';   
		
			+---------------+-------+   
		
			| Variable_name | Value |   
		
			+---------------+-------+   
		
			| Open_tables   | 1024  |   
		
			| Opened_tables | 1465  |   
		
			+---------------+-------+

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

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

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

8, 进程使用情况

		
			mysql> show global status like 'Thread%';   
		
			+-------------------+-------+   
		
			| Variable_name     | Value |   
		
			+-------------------+-------+   
		
			| Threads_cached    | 31    |   
		
			| Threads_connected | 239   |   
		
			| Threads_created   | 2914  |   
		
			| Threads_running   | 4     |   
		
			+-------------------+-------+

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

	
		mysql> show variables like 'thread_cache_size';   
	
		+-------------------+-------+   
	
		| Variable_name     | Value |   
	
		+-------------------+-------+   
	
		| thread_cache_size | 32    |   
	
		+-------------------+-------+

9, 查询缓存(query cache)

		
			mysql> show global status like 'qcache%';   
		
			+-------------------------+----------+   
		
			| Variable_name           | Value    |   
		
			+-------------------------+----------+   
		
			| Qcache_free_blocks      | 2226     |   
		
			| Qcache_free_memory      | 10794944 |   
		
			| Qcache_hits             | 5385458  |   
		
			| Qcache_inserts          | 1806301  |   
		
			| Qcache_lowmem_prunes    | 433101   |   
		
			| Qcache_not_cached       | 4429464  |   
		
			| Qcache_queries_in_cache | 7168     |   
		
			| Qcache_total_blocks     | 16820    |   
		
			+-------------------------+----------+

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

我们再查询一下服务器关于query_cache的配置：

	
		mysql> show variables like 'query_cache%';   
	
		+------------------------------+----------+   
	
		| Variable_name                | Value    |   
	
		+------------------------------+----------+   
	
		| query_cache_limit            | 33554432 |   
	
		| query_cache_min_res_unit     | 4096     |   
	
		| query_cache_size             | 33554432 |   
	
		| 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%

示例服务器查询缓存碎片率＝ 20.46％，查询缓存利用率＝ 62.26％，查询缓存命中率＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

10,排序使用情况

		
			mysql> show global status like 'sort%';   
		
			+-------------------+----------+   
		
			| Variable_name     | Value    |   
		
			+-------------------+----------+   
		
			| Sort_merge_passes | 2136     |   
		
			| Sort_range        | 81888    |   
		
			| Sort_rows         | 35918141 |   
		
			| Sort_scan         | 55269    |   
		
			+-------------------+----------+

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/

11.文件打开数(open_files)

	
		mysql> show global status like 'open_files';   
	
		+---------------+-------+   
	
		| Variable_name | Value |   
	
		+---------------+-------+   
	
		| Open_files    | 821   |   
	
		+---------------+-------+   
	
		mysql> show variables like 'open_files_limit';   
	
		+------------------+-------+   
	
		| Variable_name    | Value |   
	
		+------------------+-------+   
	
		| open_files_limit | 65535 |   
	
		+------------------+-------+

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

正常

12。表锁情况

		
			mysql> show global status like 'table_locks%';   
		
			+-----------------------+---------+   
		
			| Variable_name         | Value   |   
		
			+-----------------------+---------+   
		
			| Table_locks_immediate | 4257944 |   
		
			| Table_locks_waited    | 25182   |   
		
			+-----------------------+---------+

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些.

13. 表扫描情况

	
		mysql> show global status like 'handler_read%';   
	
		+-----------------------+-----------+   
	
		| Variable_name         | Value     |   
	
		+-----------------------+-----------+   
	
		| Handler_read_first    | 108763    |   
	
		| Handler_read_key      | 92813521  |   
	
		| Handler_read_next     | 486650793 |   
	
		| Handler_read_prev     | 688726    |   
	
		| Handler_read_rnd      | 9321362   |   
	
		| Handler_read_rnd_next | 153086384 |   
	
		+-----------------------+-----------+

各字段解释参见http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，调出服务器完成的查询请求次数：

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

计算表扫描率：

表扫描率＝ Handler_read_rnd_next / Com_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................