Mysql Update 性能突然很糟糕

Question

MySQL 5.1、Ubuntu 10.10 64 位、Linode 虚拟机。

所有表均为InnoDB。

我们的一台生产机器使用包含 31 个相关表的 MySQL 数据库。在一个表中，有一个字段包含显示值，这些值每天可能会更改几次，具体取决于条件。

这些对显示值的更改会在全天的使用时间内延迟应用。脚本定期运行并检查一些可能导致更改的廉价条件，并在满足条件时更新显示值。但是，这种惰性方法无法捕获应更新显示值的所有可能情况，以便在工作时间将后台进程负载保持在最低水平。

每晚一次，脚本会清除表中存储的所有显示值并重新计算它们，从而捕获所有可能的更改。这是一个昂贵得多的操作。

这一切已经持续运行了大约 6 个月。三天前，夜间脚本的运行时间突然从平均 40 秒缩短到 11 分钟。

存储数据的总体比例没有发生显着变化。

我已尽我所能进行调查，脚本中突然运行速度变慢的部分是写入新显示值的最后一个更新语句。给定行的 (INT(11)) id 和新的显示值（也是 INT），每行执行一次。

update `table` set `display_value` = ? where `id` = ?

有趣的是，清除所有先前值的执行方式如下：

update `table` set `display_value` = null

并且该语句仍然以与往常相同的速度运行。

display_value 字段未建立索引。 id 是主键。 table 中还有 4 个其他外键，它们在执行过程中的任何时候都不会被修改。

最后的曲线球：如果我将此架构转储到测试虚拟机，并执行相同的脚本，它将在 40 秒而不是 11 分钟内运行。我没有尝试在生产计算机上重建架构，因为这根本不是一个长期解决方案，我想了解这里发生了什么。

我的索引有问题吗？在对同一行进行数千次更新后，它们是否会变得粗糙？

---

更新

我能够通过对架构运行优化来完全解决这个问题。由于InnoDB不支持优化，因此强制重建并解决了问题。也许我的索引已损坏？

mysqlcheck -A -o -u <user> -p

Answer 1

UPDATE 语句有可能不会使用 id 上的索引，但是，对于像您这样的查询来说这是非常不可能的（如果可能的话）。

您的表是否有可能被长时间运行的并发查询/DML 锁定？该表使用哪种引擎？

此外，逐条记录更新表的效率也不高。您可以将值批量加载到临时表中，并使用单个命令更新主表：

CREATE TEMPORARY TABLE tmp_display_values (id INT NOT NULL PRIMARY KEY, new_display_value INT);

INSERT
INTO    tmp_display_values
VALUES
(?, ?),
(?, ?),
…;

UPDATE  `table` dv
JOIN    tmp_display_values t
ON      dv.id = t.id
SET     dv.new_display_value = t.new_display_value;