如何利用索引删除mysql上的多行

xbp102n0 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(310)

我的表有大约9m行，我想删除大约270k行。我有一个sql，但不能利用我的索引，所以它在资源失败。
我每天有多行（创建于），用于每个exchange/base\u currency/quote\u currency对。
我的列是：

Id  exchange    base_currency       quote_currency  created_at

我的索引是：

ix1 id
Ix2 exchange    base_currency       quote_currency  created_at

对于每个exchange/base\u currency/quote\u currency对，我希望保留当天的最新行（最新id或创建时间）。因此，我找到了每天/exchange/base\u currency/quote\u currency的所有max（id），然后尝试删除所有未选中（不在中）的行。
我的问题是：

DELETE FROM `tickers` 
WHERE
      DATE(`created_at`) = '2018-06-26'
  AND id  NOT IN 
    (SELECT MAX(id) FROM (select * FROM `tickers`) as t2 
          WHERE DATE(`created_at`) = '2018-06-26'
         GROUP BY
          exchange
         , base_currency
         , quote_currency
         , DATE(created_at)
        )

selectmax（id）使用了ix2索引，因为groupby，但是我认为delete做了一个tablescan来提取带有日期的行( created_at )='2018-06-26'，这是缓慢的。
有没有什么方法来构造这个查询以便我在delete上也使用ix2索引？或者我应该只在创建的\上创建另一个索引吗？

sql mysql

来源：https://stackoverflow.com/questions/51546325/how-to-make-use-of-index-on-delete-multiple-rows-on-mysql

1条答案

按热度按时间

gopyfrb31#

为了使用索引，您需要做两件事：
为创建索引 created_at :

create index ix3 on `tickers` (`created_at`);

避免在where条件下使用公式。例如，使用 between 取而代之的是：

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 DATE(`created_at`) = '2018-06-26'
         GROUP BY
          exchange
         , base_currency
         , quote_currency
         , DATE(created_at)
        )

但是，您正在删除表的一个重要百分比（3%？）。当发生这种情况时，数据库引擎可能会忽略索引，而不管怎样，它还是更喜欢全表扫描。
您需要测试并查看数据库的用途。

展开查看全部

赞(0）回复(0）举报 2021-06-21

我来回答

如何利用索引删除mysql上的多行

1条答案

相关问题

热门标签

最新问答