Mysql 没有在 where 上使用索引

Question

我有一个非常简单的表，用于记录成员(member)个人资料的访问情况，具有多列键(member_id、visitor_id、month_visited)和更精确的日期。 month_visited 是一个像这样的 CHAR(7) 列:'2013-10'

每个新月，我想将上个月的数据压缩到另一个表中，然后删除它。

我的要求很简单:

DELETE FROM visits WHERE month_visited = '2013-10'

删除这些行需要 AGES，就像在我的专用服务器上需要几分钟。当我只查询一个简单的 SELECT COUNT(*) FROM visits 时也是如此。

2013-10 年我有 180 万条条目。

但这需要很长时间。当我尝试

EXPLAIN SELECT * FROM visits WHERE month_visited = "2013-10"

它告诉我:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  visits  ref idx_month_visited   idx_month_visited   21  const   1782148 Using where

“在哪里使用”，认真的？？

编辑:抱歉，我忘了说明我还在 month_visited 列上添加了一个索引 :)(实际上，如 EXPLAIN 所示，但它没有使用它...)

我如何改进那些(显然)简单的查询？我是 MySQL 的菜鸟，但我认为执行这些查询需要几分钟时间是不正常的。

感谢任何输入!

最好的问候，

Answer 1

我在这个回答中总结了我的评论。

一般来说，当一个索引没有被使用时，那是因为使用它不会有太大帮助。也就是说，与全表扫描相比，它不会节省多少时间(当索引的基数较低时，这种情况往往会发生)。这似乎是这里的情况，因为表中的行数与要选择的行数大致相同。在这种情况下，完全扫描通常比使用索引更便宜。

此外，删除是一个“写入” Action 。索引优化读取，代价是使写入更昂贵(因为写入时重建索引)。所以你有一些复杂的索引这一事实并没有帮助，反而加剧了问题。索引在缩小要检索的行数时才有意义；否则它不会提供真正的 yield ，甚至可能会增加一些额外的开销。此外，在最好的情况下，索引可以使 SELECT 更有效。但它不会使写入(插入、更新和删除)工作得更快；相反，这会使他们表现更差。

所以，你应该尽量去掉那些不是绝对必要的索引。请记住，索引是一种权衡，它可能会使读取操作(选择)更快，但代价是使写入操作(插入、更新、删除)变慢。这是因为索引必须在写入后重建。

您可能想尝试一下:“如果您要从表中删除很多行，使用 DELETE QUICK 后跟 OPTIMIZE TABLE 可能会更快。这会重建索引而不是执行许多索引 block 合并操作” dev.mysql.com/doc/refman/5.0/en/delete.html

还有另一种选择(可能有效或无效，只是在这里大声思考):如果您想从 visitss 中删除除几行以外的所有行，也许您可​​以将行“WHERE month != '2013-10' 插入到辅助表，TRUNCATE visits，然后将 aux 表中的行插入到 visits 中，最后 TRUNCATE aux 表。正如您所指出的，您需要在此过程运行时进行某种锁定。