我的股票报价表有900万行，我需要删除该表上的271k行，为期一天（2018-06-26）。为什么我的DELETE不使用索引并且查询异常终止是有原因的？

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我如何使此删除工作？

询问：

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的相同查询有效：

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：

+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
| 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的结果：

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的结果：

Running for a looong time. Killed the process.