MySQL中一个简单的更新查询需要执行很长时间

Question

我在检查 MySQL 的慢查询日志时，发现了以下条目：

# Time: 131108  4:16:34

# Query_time: 14.726425  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 1
SET timestamp=1383884194;
UPDATE `Artist` SET ImageFilename = NULL, Title = 'Elton John', PopularityRating = 657, UniqueID = NULL, Description = NULL, IsFeatured = 0, FeaturedText = '', MetaDescription = '', MetaTitle = NULL, _Temporary_LastUpdOn = '2013-11-08 04:15:58 ', _Temporary_Flag = 0, _Deleted = 0, _DeletedOn = NULL, Priority = 0 WHERE ID = 3449748;

正如您所看到的，执行这个查询竟然花费了惊人的14.72秒，而它只是一个通过主键进行简单更新的WHERE。我已经尝试重新执行该查询，现在执行时间为0.095秒，更加合理。

有任何想法可以调试为什么那个特定的时间会花费这么长时间吗？

编辑1：query_cache％变量

mysql> SHOW variables where variable_name like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 210763776 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

编辑2：艺术家表信息

CREATE TABLE `artist` (
  `ID` bigint(20) NOT NULL,
  `ImageFilename` mediumtext,
  `Title` varchar(1000) DEFAULT NULL,
  `PopularityRating` int(11) DEFAULT '0',
  `UniqueID` mediumtext,
  `Description` mediumtext,
  `IsFeatured` tinyint(1) DEFAULT '0',
  `FeaturedText` mediumtext,
  `_Temporary_LastUpdOn` datetime DEFAULT '0001-01-01 00:00:00',
  `_Temporary_Flag` tinyint(1) DEFAULT '0',
  `_Deleted` tinyint(1) DEFAULT '0',
  `_DeletedOn` datetime DEFAULT NULL,
  `Priority` int(11) DEFAULT '0',
  `MetaDescription` varchar(2000) DEFAULT NULL,
  `MetaTitle` mediumtext,
  PRIMARY KEY (`ID`),
  KEY `_Temporary_Flag` (`_Temporary_Flag`),
  KEY `_Deleted` (`_Deleted`),
  KEY `Priority` (`Priority`),
  KEY `PopularityRating` (`PopularityRating`),
  KEY `Title` (`Title`(255)),
  KEY `IsFeatured` (`IsFeatured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

- Karl Cassar

---

可能那时候桌子被锁住了。 - juergen d

你启用了缓存吗？也许有些牵强，但如果查询更改了缓存结果，则可能会导致性能下降。 - Mad Dog Tannen

@KayNelson 我怎么知道我的缓存是否启用？ - Karl Cassar

@KarlCassar 执行此 SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'query_cache%'; - Mad Dog Tannen

@KayNelson 刚刚更新了帖子，并附上了输出结果。 - Karl Cassar

Answer 1

鉴于您提供的输出，我的建议是尽量减小缓存大小。我认为这可能导致更新时间超过15秒，因为查询本身使用了主键上的WHERE，这只是我的最佳猜测。

由于您无法重现问题，所以很难确定原因。

我再次阅读缓存文档以获取一些信息。

引用如下：

当表被修改时，任何相关的查询缓存条目都会被刷新。这可能是你进行的更新需要刷新缓存数据的原因之一。

文档的另一部分：

要谨慎地将查询缓存大小设置得过大，这会增加维护缓存所需的开销，可能超出启用它的好处。通常情况下，几十兆字节的大小是有益的。数百兆字节的大小可能不是。

无论如何，既然您已经启用了查询缓存，我认为这是一个好的起点。

在生产环境中设置新的查询缓存。

SET GLOBAL query_cache_size = 1000000;

MySQL会自动将大小设置为最接近1024字节块的对齐大小。

仔细阅读此文档，有助于理解。查询缓存可以同时成为您最好和最糟糕的设置。

http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

- Mad Dog Tannen

Answer 2

如果有人错过了上面的评论：

也许那时候表被锁住了。

由于您无法重现这个问题，很可能是这种情况。

- Avatar

Answer 3

我认为您还没有调整MySQL服务器变量，调整服务器变量以提高性能非常重要。建议查看key_buffer_size和table_cache变量。

key_buffer_size变量控制可用于MySQL索引缓冲区的内存量。该值越高，可用于索引的内存就越多，性能也越好。

table_cache变量控制可用于表缓存的内存量，因此MySQL可以在任何给定时间打开的表的总数。对于具有许多数据库和表的繁忙服务器，应增加此值，以便MySQL可以可靠地处理所有请求。

- user7161651

Answer 4

你的表存在一个问题。你为该表创建了多个索引，其中包括你将在 SQL 中更新的字段。这样每次 MySQL 都需要重建索引。

- user2963713