Mysql DELETE(带有NOT IN子选择)不使用索引并出现错误1205(HY000):超出了锁定等待超时； [英] Mysql DELETE (with NOT IN subselect) not using index and getting ERROR 1205 (HY000): Lock wait timeout exceeded;

问题描述

我的股票报价表有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屋！