mysql出现很多sleep线程（为什么线程有时会在）

问题描述

您是否曾经试图杀死一个查询，但它并没有消失，而是在运行的查询中停留了很长一段时间？或者你注意到一些被标记为被杀死的线程不时出现并且实际上并没有死亡。这些僵尸线程是什么？为什么 MySQL 有时似乎无法快速终止查询？本章对此问题进行剖析。

Threads and connections

MySQL 为每个客户端连接使用单独的线程。发送到 MySQL 的查询由先前与查询的连接关联的线程处理。任何拥有足够权限的人都可以通过运行SHOW PROCESSLIST命令查看当前活动线程的列表以及一些其他详细信息，该命令返回一个类似表的视图，每一行都是个连接。具体如下：

任何正在运行的查询或列表中的任何现有连接都可以使用KILL命令进行终止。

命令语法：

KILL [QUERY | CONNECTION] <thread_id>

其中thread_id是来自进程列表输出的第一列的值。可选参数是只运行的查询还是终止整个会话。它默认为后者，因此不需要指定CONNECTION 。

运行该命令实际上并没有做任何事情，除了在所选线程内设置一个特殊标志。因此 kill 操作不会与相应的 request 同步发生。在许多情况下，线程或查询需要一些时间才能停止。在语句执行的各个阶段检查标志。它何时准确发生或发生频率如何，取决于线程实际正在执行的工作。

例如：

• 在ALTER TABLE期间，当 MySQL 将数据重写到新的临时表中时，在从原始表中读取每个行块之前检查它。在此过程中，临时表被删除，原始结构保持不变。
• 对于UPDATE和DELETE，在每个更新或删除的行之后还有一个额外的检查。如果查询注意到请求终止之前，进行了任何更改，则必须回滚它们。注意：如果表的存储引擎不支持事务（例如MyISAM），则更改无法回滚，因此该操作将导致部分更新！
• SELECT在读取一行行后也会检查它。

当然，这些只是基本示例，不同情况下，可能会有所不同。在某些情况下，某些查询甚至可能无法终止。从INFORMATION_SCHEMA.InnoDB_BUFFER_POOL_PAGES_INDEX表中读取时发生过一次这种情况。数据库当时存在性能问题，查询执行基本上停止在 InnoDB 深处的某个锁上，并且它再也没有回到可以再次检查标志值的点上。

线程长时间处于终止状态。这是什么意思？

实际上，可以有两种不同的情况。如果发出KILL <thread_id>，则列表中的进程将被杀死。KILL QUERY <thread_id>不会终止连接，而是只停止连接中正在运行的查询，因此在这种情况下，可能会出现查询结束文本。

如果你看到这样的东西怎么办？

它可能是错误（例如错误#52528），或者更可能是数据库在内部执行一些工作，以便任务终止后进行清理。

当然，没有简单的方法来确认这是否是一个错误。因此，为了弄清楚它，您应该寻找证明它不是错误的证据。您所看到的只是标准操作的效果，MySQL 在查询或事务之后必须执行该操作以进行清理。

一个线程在被杀死或查询结束的情况下停留更长时间的最常见原因可能是等待InnoDB 表上的事务回滚。这有时可能需要很长时间才能完成，尤其是当必须删除数十万或数百万的更改时。

如何验证？

检查是SHOW ENGINE INNODB STATUS\G的输出。如果当前正在进行回滚，会有如下简单的信息：

---TRANSACTION 0 10411, ACTIVE 28 sec, process no 15506, OS thread id 140732309711184 rollback mysql tables in use 1, locked 1 ROLLING BACK 7585 lock struct(s), heap size 751600, undo log entries 798854 MySQL thread id 14 , query id 206 localhost root end update testdb.sometable set cc=sqrt(id)

该示例显示了如果一个线程被标记为kill或query end，并且 InnoDB 引擎状态报告同一线程的回滚，请等待它结束。

如果不是这样呢？

它可能与从磁盘中删除一些临时表有关。一个ALTER TABLE可能需要丢弃一个非常大的临时表，而在某些文件系统（例如ext3或ext4）上删除大文件可能相当慢，因此可能需要几秒钟甚至更长的时间。任何 DML 语句也可以创建临时表，但通常不会那么大。

在最极端的情况下，删除文件也不会超过十或二十秒，但在 I/O 负载较重的情况下，它可能会更长，所以有什么方法可以查看是否有临时表是否被创造？在Percona Server或MariaDB中，您检查INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLE和INFORMATION_SCHEMA.TEMPORARY_TABLE的内容。它仅适用于使用CREATE TEMPORARY TABLE语句手动建立的临时表，不适用于 MySQL 隐式创建的执行复杂查询时的临时表。

SESSION_ID与进程列表中的线程标识符相同，因此您可以杀死任何连接到临时表的线程。

其他类型的临时表不容易追踪。有时可以在SHOW PROCESSLIST输出中发现有用信息：

有时您可以检查实例打开的临时文件：

红色标记的值是文件的大小。这些可以帮助您定位正在删除的临时表。

结论

有时看到一个数据库线程被杀死，但线程并没有立即被终止，或者一个查询似乎无法结束，这可能是正常的。因为实际操作不会与 kill 命令同步执行，之后 MySQL可能仍需要一些时间才能正确清理。不管真正的原因是什么，如果不重新启动数据库，就不可能摆脱这种挂起的线程。也没有办法像Unix 系统中的kill -9 <pid>那样立即强制执行kill。

,