MySQL使用SHOW PROCESSLIST 详解

1、SHOW PROCESSLIST 显示进程列表

SHOW [FULL] PROCESSLIST 用于查看当前MySQL服务器上的所有运行中的进程列表信息。这个命令可以帮助我们了解哪些查询正在执行，它们的状态是什么，以及它们已经执行了多长时间。
示例：

mysql> SHOW PROCESSLIST;
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Query   | 0    | init  | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
1 row in set (0.00 sec)

其中每行显示的信息包括：
Id: 线程的唯一标识符。
User: 执行该命令的用户名。
Host: 用户的主机名。通常是客户端IP地址、端口号或者都有。
db: 当前线程正在工作的数据库。如果没有选定数据库，则为NULL。
Command: 线程正在执行的命令类型。例如：Sleep，Query，Locked等。
Time: 命令开始执行以来的秒数。对于'Sleep'命令，这是线程进入睡眠状态的时间。
State: 显示线程的状态信息。这对找出性能问题特别有用。
Info: 显示线程正在执行的查询。若没有查询在执行则该列为NULL。
如果没有 FULL 关键字， SHOW PROCESSLIST 则仅显示 Info 字段中每个语句的前 100 个字符。

2、线程Command有以下值：

• Binlog Dump：这是用于将二进制日志内容发送到副本的复制源上的线程。
• Change user：线程正在执行更改用户操作。
• Close stmt：线程正在关闭预备语句。
• Connect：被连接到源的复制接收器线程以及复制工作器线程使用。
• Connect Out：副本正在连接到其源。
• Create DB：线程正在执行创建数据库操作。
• Daemon：此线程对服务器内部而言，不是为客户端连接提供服务的线程。
• Debug：线程正在生成调试信息。
• Delayed insert：线程是一个延迟插入处理器。
• Drop DB：线程正在执行删除数据库操作。
• Error：Execute：线程正在执行预备语句。
• Fetch：线程正在获取执行预备语句的结果。
• Field List：线程正在检索表列的信息。
• Init DB：线程正在选择默认数据库。
• Kill：线程正在杀死另一个线程。
• Long Data：线程正在检索执行预备语句结果中的长数据。
• Ping：线程正在处理服务器ping请求。
• Prepare：线程正在准备预备语句。
• Processlist：线程正在生成关于服务器线程的信息。
• Query：用户客户端在执行查询时使用，由单线程复制应用程序线程使用，以及由复制协调器线程使用。
• Quit：线程正在终止。
• Refresh：线程正在刷新表、日志或缓存，或重置状态变量或复制服务器信息。
• Register Slave：线程正在注册副本服务器。
• Reset stmt：线程正在重置预备语句。
• Set option：线程正在设置或重置客户端语句执行选项。
• Shutdown：线程正在关闭服务器。
• Sleep：线程正在等待客户端发送新的语句给它。
• Statistics：线程正在生成服务器状态信息。
• Time：未使用。

3、线程状态State分类有以下值

以下列表描述了与通用查询处理相关的线程State值，并且不涉及更专门的活动，如复制。其中许多对于在服务器中查找错误非常有用。

• After create：当线程创建一个表（包括内部临时表）时，会出现这种情况，在创建表的函数结束时。即使由于某些错误无法创建表，也会使用此状态。
• altering table：服务器正在执行就地ALTER TABLE。
• Analyzing：线程正在计算MyISAM表键分布（例如，用于ANALYZE TABLE）。
• checking permissions：线程正在检查服务器是否具有执行语句所需的权限。
• Checking table：线程正在执行表检查操作。
• cleaning up：线程已处理一条命令，并准备释放内存并重置某些状态变量。
• closing tables：线程正在将更改后的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果不是，验证你是否没有满磁盘，并且磁盘没有在非常繁重的使用中。
• committing alter table to storage engine：服务器已完成就地ALTER TABLE并正在提交结果。
• converting HEAP to ondisk：线程正在将内部临时表从MEMORY表转换为磁盘上的表。
• copy to tmp table：线程正在处理ALTER TABLE语句。在已创建新结构的表之后但在将行复制进去之前发生此状态。
• 对于此状态的线程，可以使用性能模式来获取复制操作的进度。
• Copying to group table：如果语句具有不同的ORDER BY和GROUP BY条件，则将按组排序行并复制到临时表中。
• Copying to tmp table：服务器正在将数据复制到内存中的临时表。
• Copying to tmp table on disk：服务器正在将数据复制到磁盘上的临时表。临时结果集已经变得过大。因此，线程正在将临时表从内存格式更改为基于磁盘的格式以节省内存。
• Creating index：线程正在处理MyISAM表的ALTER TABLE ... ENABLE KEYS。
• Creating sort index：线程正在处理使用内部临时表解析的SELECT。
• creating table：线程正在创建表。这包括创建临时表。
• Creating tmp table：线程正在在内存或磁盘上创建临时表。如果在内存中创建了表，但稍后将其转换为磁盘上的表，则在该操作期间的状态为Copying to tmp table on disk。
• deleting from main table：服务器正在执行多表删除的第一部分。它只从第一个表中删除，并保存列和偏移量以用于从其他（引用）表中删除。
• deleting from reference tables：服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行。
• discard_or_import_tablespace：线程正在处理ALTER TABLE ... DISCARD TABLESPACE或ALTER TABLE ... IMPORT TABLESPACE语句。
• end：在ALTER TABLE，CREATE VIEW，DELETE，INSERT，SELECT或UPDATE语句的结束但在清理之前发生。
• 对于结束状态，可能正在发生以下操作：
• 将事件写入二进制日志
• 释放内存缓冲区，包括blob：executing：线程已开始执行语句。
• Execution of init_command：线程正在执行init_command系统变量的值中的语句。
• freeing items：线程已执行一条命令。这个状态通常在cleaning up之前出现。
• FULLTEXT initialization：服务器正在准备进行自然语言全文搜索。
• init：在初始化ALTER TABLE，DELETE，INSERT，SELECT或UPDATE语句之前发生。在这种状态下，服务器采取的操作包括刷新二进制日志和InnoDB日志。
• Killed：有人向线程发送了一个KILL语句，它应该在下次检查kill标志时中止。该标志在MySQL的每一个主要循环中都会被检查，但在某些情况下，线程可能还需要短暂的时间才能死亡。如果线程被其他线程锁定，则在其他线程释放其锁定后立即生效。
• Locking system tables：线程正在尝试锁定一个系统表（例如，一个时区或日志表）。
• logging slow query：线程正在将语句写入慢查询日志。
• login：连接线程的初始状态，直到客户端成功进行身份验证。
• manage keys：服务器正在启用或禁用表索引。
• Opening system tables：线程正在尝试打开一个系统表（例如，一个时区或日志表）。
• Opening tables：线程正在尝试打开表。这应该是非常快速的过程，除非有什么阻止打开。例如，ALTER TABLE或LOCK TABLE语句可以阻止打开表，直到语句完成。还值得检查您的table_open_cache值是否足够大。
• 对于系统表，使用Opening system tables状态代替。
• optimizing：服务器正在为查询执行初始优化。
• preparing：此状态在查询优化期间发生。
• preparing for alter table：服务器正在准备执行就地ALTER TABLE。
• Purging old relay logs：线程正在删除不需要的中继日志文件。
• query end：处理查询后但在freeing items状态之前发生此状态。
• Receiving from client：服务器正在从客户端读取数据包。
• Removing duplicates：查询正在以SELECT DISTINCT的方式使用，以致MySQL无法在早期阶段优化掉DISTINCT操作。由于此原因，MySQL需要一个额外的阶段来移除所有重复的行，然后再将结果发送给客户端。
• removing tmp table：线程在处理完SELECT语句后正在移除内部临时表。如果没有创建临时表，则不使用此状态。
• rename：线程正在重命名表。
• rename result table：线程正在处理ALTER TABLE语句，已经创建了新表，并正将其重命名以替换原始表。
• Reopen tables：线程获取了表的锁，但在获取锁后发现表的底层结构已更改。它已释放了锁，关闭了表，正在尝试重新打开它。
• Repair by sorting：修复代码使用排序来创建索引。
• Repair done：线程已完成MyISAM表的多线程修复。
• Repair with keycache：修复代码使用一次通过键缓存创建一个键。这比Repair by sorting慢得多。
• Rolling back：线程正在回滚事务。
• Saving state：对于MyISAM表操作，如修复或分析，线程正在将新表状态保存到.MYI文件头。状态包括行数、AUTO_INCREMENT计数器和键分布等信息。
• Searching rows for update：线程正在进行第一阶段，查找所有匹配的行，然后更新它们。如果UPDATE改变了用于查找所涉及行的索引，则必须这样做。
• Sending data：在MySQL 8.0.17之前：线程正在读取和处理SELECT语句的行，并将数据发送给客户端。因为在此状态期间发生的操作倾向于执行大量的磁盘访问（读取），所以它通常是给定查询在其生命周期内运行时间最长的状态。MySQL 8.0.17及更高版本：此状态不再单独指示，而是包含在Executing状态中。
• Sending to client：服务器正在将数据包写入客户端。
• setup：线程开始进行ALTER TABLE操作。
• Sorting for group：线程正在执行排序以满足GROUP BY。
• Sorting for order：线程正在执行排序以满足ORDER BY。
• Sorting index：线程正在对索引页进行排序，以便在MyISAM表优化操作期间更有效地访问。
• Sorting result：对于SELECT语句，此为与Creating sort index类似，但适用于非临时表。
• starting：语句执行开始的第一阶段。
• statistics：服务器正在计算统计信息以制定查询执行计划。如果线程在此状态下停留了很长时间，那么服务器可能在磁盘绑定中执行其他工作。
• System lock：线程已调用mysql_lock_tables()并且自那时起线程状态未更新。这是一个非常通用的状态，可能出于许多原因。
• 例如，线程将请求或正在等待表的内部或外部系统锁。当InnoDB在执行LOCK TABLES期间等待表级锁时，可能会发生这种情况。如果由于请求外部锁而导致这种状态，并且您没有使用访问相同MyISAM表的多个mysqld服务器，则可以使用--skip-external-locking选项禁用外部系统锁。然而，默认情况下是禁用外部锁定的，所以有可能这个选项没有效果。对于SHOW PROFILE，此状态意味着线程正在请求锁（不是等待它）。
• 对于系统表，使用Locking system tables状态代替。
• update：线程准备开始更新表。
• Updating：线程正在搜索要更新的行并更新它们。
• updating main table：服务器正在执行多表更新的第一部分。它只更新第一个表，并保存列和偏移量以用于更新其他（引用）表。
• updating reference tables：服务器正在执行多表更新的第二部分，并更新其他表中的匹配行。
• User lock：线程将请求或正在等待使用GET_LOCK()调用请求的咨询锁。对于SHOW PROFILE，这种状态意味着线程正在请求锁（而不是等待它）。
• User sleep：线程已调用SLEEP()。
• Waiting for commit lock：FLUSH TABLES WITH READ LOCK正在等待提交锁。
• waiting for handler commit：线程正在等待事务提交，与查询处理的其他部分相比。
• Waiting for tables：线程收到通知，表的底层结构已经更改，它需要重新打开表以获取新的结构。但是，要重新打开表，它必须等待所有其他线程都关闭了所述表。
• 如果另一个线程在所述表上使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE，OPTIMIZE TABLE，则会发生此通知。
• Waiting for table flush：线程正在执行FLUSH TABLES并等待所有线程关闭其表，或者线程收到通知说表的底层结构已经更改，它需要重新打开表以获取新的结构。但是，要重新打开表，它必须等待所有其他线程都关闭了所述表。
• 如果另一个线程在所述表上使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name，ALTER TABLE，RENAME TABLE，REPAIR TABLE，ANALYZE TABLE，OPTIMIZE TABLE，则会发生此通知。
• Waiting for lock_type lock：服务器正在等待获得THR_LOCK锁或元数据锁定子系统的锁，其中lock_type指示锁的类型。
• 这种状态表示等待一个THR_LOCK：
• Waiting for table level lock：这些状态表示等待元数据锁：
• Waiting for event metadata lock：Waiting for global read lock：Waiting for schema metadata lock：Waiting for stored function metadata lock：Waiting for stored procedure metadata lock：Waiting for table metadata lock：Waiting for trigger metadata lock：有关表锁指标的信息。有关元数据锁定的信息。要查看哪些锁阻止了锁请求。
• Waiting on cond：线程在等待某个条件变为真的通用状态。没有特定的状态信息可用。
• Writing to net：服务器正在将数据包写入网络。