TIPS
本文基于MySQL 8.0，理论支持MySQL 5.0及更高版本。
本文来自于课程：《Java架构师体系课：跟随千万级项目从0到100全过程高效成长》
本文整理了常用的MySQL诊断命令，可以帮助我们了解数据库的运行情况。
SHOW PROCESSLIST
作用：
SHOW [FULL] PROCESSLIST用于查看当前正在运行的线程。如果执行此命令的用户拥有 PROCESS 权限，则可看到所有线程；否则只能看到自己的线程（即与当前登录用户关联的线程）。如果不使用FULL关键字，只在Info字段中展示前100个字符。
当遇到“too many connections”错误信息时，想要了解发生了什么，SHOW PROCESSLIST就非常有用。MySQL保留了一个额外的连接，用于让拥有 CONNECTION_ADMIN （或已废弃的 SUPER ）权限的账户使用，从而确保管理员始终能够连接并检查系统。
可使用 KILL 语句杀死线程。
语法：
SHOW [FULL] PROCESSLIST
示例：
mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec)
由结果可知，结果包含如下几列：
-
Id：连接的唯一标识，是CONNECTION_ID()函数的返回。
-
User：发出该语句的MySQL用户。
- system_user表示服务器产生的非客户端线程，用于处理内部任务。这可能是用来在从库复制或延迟行处理器的IO/SQL线程。对于system_user，Host字段将会为空
- unauthenticated user是指与客户端连接，但尚未完成客户端用户身份认证的线程。
- event_scheduler是指事件调度器的监控线程。（有关事件调度器可详见 “Using the Event Scheduler” ）
TIPS
User字段的值是system_user和 SYSTEM_USER 权限不是一回事，前者指内部线程，后者用来区分系统账户和普通账户的类别，不要搞混了。
-
Host：发出该语句的客户端的主机名（当User是system_user时，Host为空）。TCP/IP链接的主机名以 host_name:client_port 格式上报，以便更轻松地了解哪个客户端在干什么。
-
db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则值为NULL
-
Command：当前线程正在执行的命令。有关线程命令的描述，可详见 “Examining Thread Information” 。
-
Time：线程处于当前状态的时间（单位秒）。对于从库的SQL线程，该字段的值表示上次复制事件的时间和从库机器的实际时间之间经过了多少秒。详见 “Replication Implementation Details”
-
State：指示线程正在执行的操作、事件或状态。大多数State对应于非常快速的操作。如果线程在给定状态下很久，则需要排查下。
-
Info：当前线程正在执行的语句，如果未执行任何语句则值为NULL。该语句可能是发送到服务器的那条语句，也可能是内部的语句（如果某个语句执行了其他语句）。例如一条CALL语句执行了一条正在执行SELECT语句的存储过程，则Info字段会展示SELECT语句。
Command取值：
TIPS
-
Binlog Dump：主库上的线程，用于将binlog内容发送到从库
-
Change user：线程正在执行更改用户操作
-
Close stmt：线程正在关闭一个prepared statement
-
Connect：一个复制从库已连接到其主库
-
Connect Out：一个复制从库正在连接到其主库
-
Create DB：线程正在执行create-database操作
-
Daemon：服务器内部线程，而非为客户端连接提供服务的线程
-
Debug：该线程正在生成调试信息
-
Delayed insert：该线程是延迟插入处理程序
-
Drop DB：线程正在执行drop-database操作。
-
Error：你懂的
-
Execute：线程正在执行一个prepared statement
-
Fetch：正在从Prepared Statement 中获取执行结果
-
Field List：该线程正在获取表的字段信息
-
Init DB：线程正在选择默认数据库。
-
Kill：该线程正在杀死另一个线程
-
Long Data：正在从prepared statement中检索long data
-
Ping：线程正在处理server-ping请求。
-
Prepare：该线程正在准备一个prepared statement
-
Processlist：该线程正在生成服务器线程相关信息
-
Query：线程正在执行一条语句
-
Quit：线程正在终止
-
Refresh：该线程是刷新表，日志或缓存；或者正在重置状态变量或在复制服务器信息。
-
Register Slave：该线程正在注册一个从库
-
Reset stmt：线程正在重置prepared statement
-
Set option：线程正在设置或重置client statement-execution选项
-
Shutdown：线程正在关闭服务器
-
Sleep：线程正在等待客户端向其发送statement
-
Statistics：该线程正在生成服务器状态信息
-
Table Dump：线程正在将表内容发送到从属服务器。
-
Time：Unused
State取值：
State的取值非常多，有一两百个，这里就不展开了，读者可直接前往官方文档查询。详见：
等价操作：
下面两个命令作用等价：
SHOW FULL PROCESSLIST
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
事实上，SHOW PROCESSLIST的结果就是从INFORMATION_SCHEMA.PROCESSLIST表中获取的。
实用SQL：
分享几个操作 INFORMATION_SCHEMA.PROCESSLIST 表的实用SQL。
-- 按照客户端IP分组，看哪个客户端的连接数最多
select client_ip, count(client_ip) as client_num
from (select substring_index(host, ':', 1) as client_ip
from `information_schema`.processlist) as connect_info
group by client_ip
order by client_num desc;
-- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select *
from `information_schema`.processlist
where Command != 'Sleep'
order by Time desc;
-- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';')
from `information_schema`.processlist
where Command != 'Sleep'
and Time > 300
order by Time desc;
参考文档：
SHOW STATUS
作用：查看服务器相关信息。返回结果解读详见：Server Status Variables
语法：
SHOW [GLOBAL | SESSION] STATUS
[LIKE 'pattern' | WHERE expr]
示例：
SHOW STATUS;
SHOW GLOBAL STATUS like '%Slow%'
参考文档：
SHOW VARIABLES
作用：查看MySQL的变量，内容解读详见： Server System Variables
语法：
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]
示例：
SHOW VARIABLES;
参考文档：
SHOW TABLE STATUS
作用：查看表以及视图的状态
语法：
SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
示例：
SHOW TABLE STATUS from employees;
参考文档：
SHOW INDEX
作用：查看索引相关信息
语法：
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
示例：
SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;
参考文档：
SHOW ENGINE
作用：展示有关存储引擎的相关信息。
语法：
SHOW ENGINE engine_name {STATUS | MUTEX}
示例：
-- 有关innodb的内容解读详见：https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
参考文档：
SHOW MASTER STATUS
作用：展示有关master binlog文件的相关信息
语法：
SHOW MASTER STATUS
示例：
SHOW MASTER STATUS
参考文档：
SHOW SLAVE STATUS
作用：展示slave线程的相关信息
语法：
SHOW SLAVE STATUS [FOR CHANNEL channel]
示例：
SHOW SLAVE STATUS
参考文档：
SHOW PROCEDURE
作用：返回存储过程相关信息
语法：
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]
示例：
SHOW PROCEDURE STATUS LIKE 'sp1'
参考文档：
SHOW FUNCTION STATUS
作用：查看函数相关信息
语法：
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]
示例：
SHOW FUNCTION STATUS
参考文档：
SHOW TRIGGERS
作用：查看触发器相关信息
语法：
SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
示例：
SHOW TRIGGERS LIKE 'acc%'
参考文档：
SHOW WARNINGS
作用：展示error、warning、note级别的诊断信息
语法：
SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS
示例：
mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4));
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t1 VALUES(10,'mysql'), (NULL,'test'), (300,'xyz');
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1265
Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************
Level: Warning
Code: 1048
Message: Column 'a' cannot be null
*************************** 3. row ***************************
Level: Warning
Code: 1264
Message: Out of range value for column 'a' at row 3
3 rows in set (0.00 sec)
参考文档：
SHOW ERRORS
作用：展示error级别的诊断信息，和show warnings类似。
语法：
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS
示例：
SHOW COUNT(*) ERRORS;
SELECT @@error_count;
参考文档：
SHOW BINARY LOGS
作用：列出服务器上的所有binary log
语法：
SHOW BINARY LOGS
SHOW MASTER LOGS
示例：
SHOW BINARY LOGS;
SHOW BINLOG EVENTS
作用：查看binary log中的事件
语法：
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
示例：
SHOW BINLOG EVENTS
SHOW RELAYLOG EVENTS
作用：查看复制从库的relay log事件相关信息
语法：
SHOW RELAYLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
[channel_option]
channel_option:
FOR CHANNEL channel
示例：
SHOW RELAYLOG EVENTS
参考文档：
参考文档
本文只列出了MySQL常用的诊断命令，还有一些其他的，详见 SHOW Statements
学习更多：
慕课网为大家准备了一门按演进思路进行讲授的课程：《Java架构师体系课：跟随千万级项目从0到100全过程高效成长》 希望能够帮助大家在演进的过程跟随千万级项目从0到100全过程高效成长。