Mysql DELETE(带有NOT IN子选择)不使用索引并出现错误1205(HY000):超出了锁定等待超时; [英] Mysql DELETE (with NOT IN subselect) not using index and getting ERROR 1205 (HY000): Lock wait timeout exceeded;
本文介绍了Mysql DELETE(带有NOT IN子选择)不使用索引并出现错误1205(HY000):超出了锁定等待超时;的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的股票报价表有900万行,我需要删除该表上的271k行,持续一天(2018-06-26).为什么我的DELETE没有使用索引并且查询异常终止?
My ticker table has 9M rows and I need to delete 271k rows on that table, for one day (2018-06-26). Is there a reason why my DELETE is not using an index and the query is abending with?
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
如何使此DELETE工作?
How can I make this DELETE work?
查询:
DELETE FROM `tickers`
WHERE
`created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
AND id NOT IN
(SELECT MAX(id) FROM (select * FROM `tickers`) as t2
WHERE `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
GROUP BY
exchange
, base_currency
, quote_currency
, DATE(created_at)
);
索引:
index#1 unique index on id
index#2 index on exchange, base_currency, quote_currency, created_at
index#3 index on created_at
说明:
+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+
| 1 | PRIMARY | tickers | range | tickers_created_at_index | tickers_created_at_index | 5 | NULL | 554300 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 8712002 | Using where; Using temporary |
| 3 | DERIVED | tickers | ALL | NULL | NULL | NULL | NULL | 8712002 | |
+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+
使用SELECT而不是DELETE的相同查询有效:
The same query with a SELECT instead of DELETE works:
SELECT id FROM `tickers`
WHERE
`created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
AND id NOT IN
(SELECT MAX(id) FROM (select * FROM `tickers`) as t2
WHERE `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
GROUP BY
exchange
, base_currency
, quote_currency
, DATE(created_at)
);
建议的解决方案1:
DELETE t
FROM tickers t LEFT JOIN
(SELECT MAX(t2.id) as max_id
FROM tickers t2
WHERE t2.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
GROUP BY exchange, base_currency, quote_currency, DATE(created_at)
) t2
ON t2.max_id = t.id
WHERE t.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59' AND
t2.max_id IS NULL;
说明建议的解决方案1:
Explain of proposed solution #1:
+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
| 1 | PRIMARY | t | range | tickers_created_at_index | tickers_created_at_index | 5 | NULL | 554300 | Using where |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | dbdevinputspike.t.id | 10 | Using where |
| 2 | DERIVED | t2 | range | tickers_created_at_index | tickers_created_at_index | 5 | NULL | 554300 | Using where; Using temporary; Using filesort |
+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
建议的解决方案1的结果:
Result of proposed solution #1:
First got: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
Then, noticed that a process was running (SHOW PROCESSLIST;).
Killed the process (KILL <process id>;) and query ran successfully.
建议的解决方案2:
CREATE TEMPORARY TABLE IF NOT EXISTS tickers_temp AS
(
SELECT id FROM `tickers`
WHERE
`created_at` between '2018-06-28T00:00:00' and '2018-06-28T23:59:59'
AND id NOT IN
(SELECT MAX(id) FROM (select * FROM `tickers`) as t2
WHERE `created_at` between '2018-06-28T00:00:00' and '2018-06-28T23:59:59'
GROUP BY
exchange
, base_currency
, quote_currency
, DATE(created_at)
)
) ;
DELETE FROM `tickers` WHERE id IN (SELECT id FROM `tickers_temp`);
建议的解决方案2的结果:
Result of proposed solution #2:
Running for a looong time. Killed the process.
推荐答案
尝试使用join
:
DELETE t
FROM tickers t LEFT JOIN
(SELECT MAX(t2.id) as max_id
FROM tickers t2
WHERE t2.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
GROUP BY exchange, base_currency, quote_currency, DATE(created_at)
) t2
ON t2.max_id = t.id
WHERE t.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59' AND
t2.max_id IS NULL;
这篇关于Mysql DELETE(带有NOT IN子选择)不使用索引并出现错误1205(HY000):超出了锁定等待超时;的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文