MySQL服务状态查看和监控方式

运行中的MySQL状态查看可以通过以下方式进行监控和衡量不同指标：

show global status like 'Uptime';  服务器已运行的秒数。重启服务器后会置零。以下内容使用的Uptime均为此字段。

1、QPS (每秒查询量)

查询命令：
show global status like 'Queries' ; 服务器执行的语句数量。与"Questions"变量不同,该变量包括在存储过程中执行的语句。它不计算COM_PING或COM_STATISTICS命令。
show global status like 'Questions' ; 服务器执行的语句数量。这只包括由客户端发送到服务器的语句，而不包括在存储过程中执行的语句。该变量不计算COM_PING、COM_STATISTICS、COM_STMT_PREPARE、COM_STMT_CLOSE或COM_STMT_RESET命令。
公式：QPS = Questions(or Queries) / Uptime

2、TPS (每秒事务量)

查询命令：
show global status like 'Com_commit' ;已提交事务的计数。
show global status like 'Com_rollback';回滚事务的计数。
公式：TPS = (Com_commit + Com_rollback) / Uptime

3、Key Buffer 缓存命中率(用于MyISAM引擎)

查询命令：
show global status like 'key%'  
show global status like 'key_read_requests' ;从MyISAM键缓存读取键块的请求次数。
show global status like 'key_reads' ;从磁盘物理读取键块到MyISAM键缓存中的次数。如果Key_reads很大，则可能是的key_buffer_size值可能太小。
show global status like 'Key_write_requests' ;向MyISAM键缓存写入键块的请求次数。
show global status like 'Key_writes' ;将键块从MyISAM键缓存物理写入磁盘的次数。
公式：
key_buffer_read_hits = (1 - key_reads / key_read_requests) * 100%   
key_buffer_write_hits = (1 - key_writes / key_write_requests) * 100%

4、InnoDB Buffer 缓存命中率（Hit rate）

 show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。

还可以通过以下方法查询：
show status like 'Innodb_buffer_pool_read_requests';从InnoDB缓冲池中读取的总请求数
show status like 'Innodb_buffer_pool_reads';表示从磁盘读取的次数
公式：innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

5、查询缓存Query Cache 命中率（8.0以后弃用了）

查询命令：
show status like 'Qcache%'
公式：Query_cache_hits = (Qcache_hits / (Qcache_hits + Qcache_inserts )) * 100%

6、Table Cache 状态量

查询命令：
show global status like 'open_tables';表示当前打开的table总和，即所有connection打开的table总数。
show global status like 'opend_tables';打开过的表的数量总和。如果Opened_tables很大，你的table_open_cache值可能太小了。
Opened_tables/Uptime的值过大说明table_open_cache过小，导致一些table对象（即下文说的table对象）经常会刷出server层，需要的时候再创建，最终导致此计数过大。

7、Thread Cache 命中率

查询命令：
show global status like 'Threads_created';为处理连接而创建的线程数。如果Threads_created很大，可能需要增加thread_cache_size的值。可以计算缓存未命中率，公式为Threads_created/Connections。
show global status like 'Connections';对MySQL服务器的连接尝试总次数;
公式：Thread_cache_hits = (1 - Threads_created / connections) * 100%

8、锁定状态

查询命令：
show global status like 'Table_locks_waited';服务器状态变量表示请求表锁时未立即授予并需要等待的次数。该值反映了MySQL服务器中表锁争用的程度。
如果Table_locks_waited的值很高，并且您遇到性能问题，则意味着存在大量的表锁争用。为了解决这个问题，您应该首先优化查询，以最小化所需的表锁数量。这可能涉及改进查询结构、添加索引或重写效率低下的SQL语句。
如果优化查询无法足够减少争用，您有几个选择：
分割表：您可以将大型表分割为较小的表，以分散工作负载并减少表锁争用。如果存在经常需要锁定的特定列或行组，这种方法可能非常有效。
复制：实施复制可以通过允许将读密集型操作转移到副本服务器来帮助缓解表锁争用。通过将工作负载分布在多个实例上，您可以减少主服务器上对表锁的需求。
show global status like 'Table_locks_immediate'; 立即获得表锁的次数。
注意观察 Table_locks_waited/Table_locks_immediate 比值，如果较大，可能表锁造成严重阻塞；也要注意 Innodb_row_lock_waits，过高可能是间隙锁造成的

9、主从复制信息

查询命令：
SHOW MASTER STATUS;此命令将显示主服务器当前复制状态的相关信息，包括二进制日志文件名和位置。

SHOW SLAVE STATUS；此命令将显示从服务器当前复制状态的详细信息。\G选项以更易读的格式显示输出。其中：

• Seconds_Behind_Master: 这个字段表示主库和从库之间的延迟时间，以秒为单位。如果该值为0，则表示从库与主库同步完全。

• Slave_IO_Running: 这个字段表示从库的IO线程是否正在运行。IO线程负责从主库读取并写入二进制日志文件中的事件。如果该值为Yes，则表示IO线程正在正常工作；如果该值为No，则表示IO线程停止了，可能由于网络连接或权限问题导致。

• Slave_SQL_Running: 这个字段表示从库的SQL线程是否正在运行。SQL线程负责读取并执行从主库复制过来的二进制日志中的事件。如果该值为Yes，则表示SQL线程正在正常工作；如果该值为No，则表示SQL线程停止了，可能由于错误的SQL语句或其他问题导致。

10、Tmp Table 状况 (临时表状况)

查询命令：
show status like 'Created_tmp_disk_tables';服务器在执行语句期间创建的内部临时表写到磁盘的数量。
注意
由于已知限制，Created_tmp_disk_tables不计算在内存映射文件中创建的磁盘临时表。默认情况下，TempTable存储引擎的溢出机制在内存映射文件中创建内部临时表。此行为由temptable_use_mmap变量控制，默认情况下启用。
show status like 'Created_tmp_tables';服务器在执行语句期间创建的内部临时表的数量。
观察比值 Created_tmp_disk_tables/Created_tmp_tables，最好不要超过10%；如果 Created_tmp_tables 值较大，可能是排序语句过多或连接语句未优化。

11、Binlog Cache 使用状况

查询命令：
show status like 'Binlog_cache_disk_use';(二进制日志缓存磁盘使用次数）
这是使用临时二进制日志缓存的大小超过binlog_cache_size的值， 导致二进制日志事务缓存写入磁盘的数量。非事务语句数量单独在Binlog_stmt_cache_disk_use状态变量中记录。
如果 Binlog_cache_disk_use 值不为0，可能需要增加 binlog_cache_size 的大小

12、Innodb_log_waits 等待写入完成的日志数量

查询命令：
show status like 'innodb_log_waits';表示等待写入完成的日志数量。
在InnoDB中，事务的持久性主要通过将日志记录到redo日志（也称为事务日志或者重做日志）来实现。当一个事务提交时，它的日志记录会被写入到磁盘上的redo日志文件中。在某些情况下，如果系统负载过高或者IO延迟较高，可能会导致其他事务需要等待直到日志写入完成。
Innodb_log_waits变量用于监控这些等待事件的数量。如果该值较高，表示有许多事务在等待日志写入完成。这可能意味着I/O子系统存在性能问题，因此可以借助该状态变量来识别和解决潜在的性能瓶颈。

13、InnoDB存储引擎的死锁情况

使用管理员权限执行以下命令来获取InnoDB存储引擎的状态信息：
查询命令：SHOW ENGINE INNODB STATUS;
执行该命令后，会返回一个结果集。在结果集中，找到名为"InnoDB"的部分，查找名为"LATEST DETECTED DEADLOCK"的子节。这个子节提供了有关最近检测到的死锁的详细信息。

14、查看连接线程

查询命令：show processlist

• Id（连接ID）：每个客户端连接都被分配一个唯一的连接ID。
• User（用户名）：与连接关联的MySQL用户。
• Host（主机名）：连接到数据库服务器的主机名或IP地址。
• db（数据库）：当前正在使用的数据库名称。如果没有选择任何数据库，则显示为NULL。
• Command（命令）：客户端正在执行的命令类型。常见的命令类型包括SELECT、INSERT、UPDATE、DELETE等。
• Time（运行时间）：连接在当前状态下已经运行的时间（以秒为单位）。
• State（状态）：描述连接当前所处的状态。可以是"Sleep"（空闲）、"Locked"（锁定）、"Sending data"（发送数据）等等。
• Info（信息）：给出有关查询或操作的更多详细信息，如SQL语句。

15、innodb脏页比例

查询命令：
show global status like 'Innodb_buffer_pool_pages_dirty'; 脏页数
show global status like 'Innodb_buffer_pool_pages_total';Innodb缓存总页数
公式：Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total