MySQL查询被杀死后不会消失 [英] MySQL query not going away after being killed

问题描述

我有一个MySQL查询，该查询将数据从一个表复制到另一个表进行处理.由于某种原因，此查询通常需要花费几秒钟的时间才能锁定一整夜，然后运行了几个小时.当我今天早上登录时，我试图终止该查询，但该查询仍列在进程列表中.

I have a MySQL query that is copying data from one table to another for processing. For some reason, this query that normally takes a few seconds locked up overnight and ran for several hours. When I logged in this morning, I tried to kill the query, but it is still listed in the process list.

| Id      | User     | Host      | db   | Command | Time  | State        | Info                                                                                 |
+---------+----------+-----------+------+---------+-------+--------------+--------------------------------------------------------------------------------------+
| 1061763 | tb_admin | localhost | dw   | Killed  | 45299 | Sending data | INSERT INTO email_data_inno_stage SELECT * FROM email_data_test LIMIT 4480000, 10000 |
| 1062614 | tb_admin | localhost | dw   | Killed  |   863 | Sending data | INSERT INTO email_data_inno_stage SELECT * FROM email_data_test LIMIT 4480000, 10000 |

可能是什么原因造成的，如何杀死这个过程，以便继续工作?

What could have caused this, and how can I kill this process so I can get on with my work?

推荐答案

如果表email_data_test是MyISAM并被锁定，那将阻止INSERT.

If the table email_data_test is MyISAM and it was locked, that would have held up the the INSERT.

如果表email_data_test是InnoDB，则很多MVCC数据正在ib_logfiles中写入，这可能尚未发生.

If the table email_data_test is InnoDB, then a lot of MVCC data was being written in ib_logfiles, which may not have occurred yet.

在这两种情况下，您都可以通过LIMIT子句滚动到4,480,000行，而实际插入到实际需要的10,000行.

In both cases, you had the LIMIT clause scroll through 4,480,000 rows just to get to 10,000 rows you actually needed to INSERT.

杀死查询只会使InnoDB表email_data_inno_stage执行回滚.

Killing the query only causes the InnoDB table email_data_inno_stage to execute a rollback.

这篇关于MySQL查询被杀死后不会消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！