使用mysql delete commnad时，锁的总数超过锁表大小

Question

我在Centos 6.4 64位操作系统上使用mysql服务器v5.1.73。我有一个表，其中包含大约17m条记录，其大小约为10 is。此表的mysql引擎是innodb。

这个表有10列，其中一列是'date‘，它的类型是datetime。我想用mysql ' delete‘命令删除特定日期的记录。

delete
from table
where date(date) = '2015-06-01'
limit 1000

但是当我运行这个命令时，我得到一个错误“锁的总数超过了锁表的大小”。我以前遇到过这个问题，当我更改innodb_buffer_poolsize时，它会解决这个问题，但这次即使增加这个数量，问题仍然存在。

我尝试了许多技巧，比如将限制值更改为100，甚至1个记录，但都不起作用。我甚至将innodb-buffer-poolsize增加到20 to，但没有任何变化。

我还阅读了这些链接：

"The total number of locks exceeds the lock table size" Deleting 267 Records和

The total number of locks exceeds the lock table size

但他们并没有解决我的问题。我的服务器有64 my的内存。

另一方面，我可以在不使用特定日期的过滤器时删除记录：

delete
from table
limit 1000

我也可以毫无问题地选择当天的记录。

有人能帮我吗？如果您能帮助我解决这个问题，我将不胜感激。

Answer 1

不要使用date(date)，它不能使用INDEX(date)。相反，只需使用date并使用以date开头的索引。

More ways to do a chunking delete。

Answer 2

WHERE子句中的date(date)要求数据库在运行时为数据库中的所有17m行计算该列的值- DB将为17m行逐行创建date(date)，然后表扫描(没有索引)这17m行来计算结果集。这就是资源耗尽的地方。

您需要删除计算列的使用，这可以通过几种不同的方法来解决。

将比较改为: WHERE date >= '2015-06-01 00:00:00‘和date <= '2015-06-01 23:59:59’，而不是执行date(date)

这将直接命中date列上的索引(我假设您在此列上有一个索引，只是原始查询不会使用它)

另一种解决方案是向DATE类型的表中添加一列，并在该列中永久存储每个DATETIME的日期(显然还会为新的DATE列添加索引)。这将允许您运行任何只想要检查日期部分的查询，而不必指定时间范围。如果您有其他查询正在使用date( date )，那么与在查询中添加时间范围相比，只包含日期的列可能是更好的解决方案(添加时间范围对于像这里这样的SELECT或DELETE中的直接索引比较是很好的，但对于涉及JOIN、GROUP BY等的其他查询可能不是一个可用的解决方案)。