Mysql运行状态查询命令

常见的mysql查询命令如下

查看MySQL运行情况

SHOW STATUS;

SHOW GLOBAL STATUS;

名称	释义
Aborted_clients	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects	尝试已经失败的MySQL服务器的连接的次数。
Connections	试图连接MySQL服务器的次数。
Created_tmp_tables	当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads	正在使用的延迟插入处理器线程的数量。
Delayed_writes	用INSERT DELAYED写入的行数。
Delayed_errors	用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands	执行FLUSH命令的次数。
Handler_delete	请求从一张表中删除行的次数。
Handler_read_first	请求读入表中第一行的次数。
Handler_read_key	请求数字基于键读行。
Handler_read_next	请求读入基于一个键的一行的次数。
Handler_read_rnd	请求读入基于一个固定位置的一行的次数。
Handler_update	请求更新表中一行的次数。
Handler_write	请求向表中插入一行的次数。
Key_blocks_used	用于关键字缓存的块的数量。
Key_read_requests	请求从缓存读入一个键值的次数。
Key_reads	从磁盘物理读入一个键值的次数。
Key_write_requests	请求将一个关键字块写入缓存次数。
Key_writes	将一个键值块物理写入磁盘的次数。
Max_used_connections	同时使用的连接的最大数目。
Not_flushed_key_blocks	在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows	在INSERT DELAY队列中等待写入的行的数量。
Open_tables	打开表的数量。
Open_files	打开文件的数量。
Open_streams	打开流的数量(主要用于日志记载）
Opened_tables	已经打开的表的数量。
Questions	发往服务器的查询的数量。
Slow_queries	要花超过long_query_time时间的查询数量。
Threads_connected	当前打开的连接的数量。
Threads_running	不在睡眠的线程数量。
Uptim	服务器工作了多少秒。

查看当前正在进行的进程

SHOW PROCESSLIST; 默认显示前100条

SHOW FULL PROCESSLIST; 显示所有

名称	释义
id	一个标识，你要kill 一个语句的时候很有用。
user	显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host	显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。
db	显示这个进程目前连接的是哪个数据库。
command	显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
time	此这个状态持续的时间，单位是秒。
state	显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
info	显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

state释义

名称	释义
Checking table	正在检查数据表（这是自动的）。
Closing tables	正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out	复制从服务器正在连接主服务器。
Copying to tmp table on disk	由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table	正在创建临时表以存放部分查询结果。
deleting from main table	服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables	服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables	正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed	发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked	被其他查询锁住了。
Sending data	正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。
Sorting for group	正在为 GROUP BY 做排序。
Sorting for order	正在为 ORDER BY 做排序。
Opening tables	这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates	正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table	获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting	修复指令正在排序以创建索引。
Repair with keycache	修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update	正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping	正在等待客户端发送新请求.
System lock	正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 –skip-external-locking参数来禁止外部系统锁。
Upgrading lock INSERT DELAYED	正在尝试取得一个锁表以插入新记录。
Updating	正在搜索匹配的记录，并且修改它们。
User Lock	正在等待 GET_LOCK()。
Waiting for tables	该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。

查看MySQL的配置参数

SHOW VARIABLES;

SHOW VARIABLES LIKE "%CASE%"; 查看大小写配置

查看INNODB数据库引擎运行状态&&死锁日志

SHOW ENGINE INNODB STATUS;

在MySQL 终端执行 show engine innodb status 命令查看最后一次死锁日志，主要关注日志中的 LATEST DETECTED DEADLOCK 部分


------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-xx-xx 21:09:05 7f9b22008700


*** (1) TRANSACTION:
TRANSACTION 4219870943, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 10 lock struct(s), heap size 2936, 3 row lock(s)
MySQL thread id 301903552, OS thread handle 0x7f9b21a7b700, query id 5373393954 10.101.22.135 root updating
update store
set update_time = now(), store = store-1
where sku_code='5468754' and ws_code = 'NO_001' and (store-1) >= 0 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3331 page no 16 n bits 904 index `idx_wscode` of table `store` trx id 4219870943 lock_mode X locks rec but not gap waiting
Record lock, heap no 415 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 5730303735; asc NO_001;;
1: len 8; hex 00000000000025a7; asc % ;;

*** (2) TRANSACTION:
TRANSACTION 4219870941, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1
mysql tables in use 3, locked 3
9 lock struct(s), heap size 2936, 4 row lock(s)
MySQL thread id 301939956, OS thread handle 0x7f9b22008700, query id 5373393941 10.101.22.135 root updating
update store
set update_time = now(), store = store-1
where sku_code='5655620' and ws_code = 'NO_001' and (store-1) >= 0 

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3331 page no 16 n bits 904 index `idx_wscode` of table `store` trx id 4219870941 lock_mode X locks rec but not gap
Record lock, heap no 415 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 5730303735; asc NO_001;;
1: len 8; hex 00000000000025a7; asc % ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3331 page no 7 n bits 328 index `PRIMARY` of table `store` trx id 4219870941 lock_mode X locks rec but not gap waiting
Record lock, heap no 72 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex 00000000000025a7; asc % ;;
1: len 6; hex 0000fb85fdf7; asc ;;
2: len 7; hex 1a00001d3b21d4; asc ;! ;;
3: len 7; hex 35343638373534; asc 5468754;;
4: len 5; hex 5730303735; asc NO_001;;
5: len 8; hex 8000000000018690; asc ;;
6: len 5; hex 99a76b2b97; asc k+ ;;
7: len 5; hex 99a7e35244; asc RD;;
8: len 1; hex 01; asc ;;

记一次Mysql cup飙高简单排查

现象

测试反馈测试环境接口报错。

测试环境近期非常稳定，没有新代码发布。猜测大概率环境问题导致。

服务器日志

登录服务器，查看相关日志，发现大量mysql返回数据超时。

测试环境不存在什么调用量，怀疑网络问题，或者mysql服务器出问题。
网络能ping通，于是登录mysql服务器查看。

mysql服务器

登录mysql服务器，返现mysql的CUP使用率彪到300%多。

使用SHOW FULL PROCESSLIST 命令查看。
发现有一个mysql线程一直处于 Sending data状态中。

这个sql是别的业务系统进行查询。

找到相应的sql，通过explain命令进行分析，发现sql没有走索引，一直在扫全表，且表的数据量非常大，导致CPU飙高。

反馈给相关业务同学，进行sql优化和索引添加，问题得到解决。

Chenaa's Notes

mysql09_Mysql运行状态查询命令&记一次排查实践