MySQL查询被杀死后不会消失

本文介绍了MySQL查询被杀死后不会消失的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个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.