通过主键进行的 MySQL UPDATE 查询有时非常慢

Question

我们网站上的特定 UPDATE 查询有时会运行得非常慢，并且会检查比必要的多得多的行。它按主键过滤，所以我希望 MySQL 总是只需要检查一行。

以下是 MySQL 的慢查询日志中的一些示例:

# Time: 090702 12:59:06
# User@Host: XXX[XXX] @ XXX [XXX]
# Query_time: 21  Lock_time: 0  Rows_sent: 0  Rows_examined: 500500
SET timestamp=1246532346;
UPDATE `folders` SET `folder_id` = '1705641', `updated_at` = now() WHERE `folders`.`id` = '1682995';
# Time: 090702 14:13:44
# User@Host: XXX[XXX] @ XXX [XXX]
# Query_time: 17  Lock_time: 0  Rows_sent: 0  Rows_examined: 16816745
SET timestamp=1246536824;
UPDATE `folders` SET `folder_id` = '417997', `updated_at` = now() WHERE `folders`.`id` = '1705956';
# Time: 090702 14:15:42
# User@Host: XXX[XXX] @ XXX [XXX]
# Query_time: 13  Lock_time: 0  Rows_sent: 0  Rows_examined: 16816719
SET timestamp=1246536942;
UPDATE `folders` SET `folder_id` = '1706267', `updated_at` = now() WHERE `folders`.`id` = '1705956';
# Time: 090702 16:07:43
# User@Host: XXX[XXX] @ XXX [XXX]
# Query_time: 499  Lock_time: 0  Rows_sent: 0  Rows_examined: 88668449
SET timestamp=1246543663;
UPDATE `folders` SET `folder_id` = '1707407', `updated_at` = now() WHERE `folders`.`id` = '1706992';

不过，查询的运行频率更高，因此它并不总是会暴露此行为。此外，如果我手动运行相同的查询，它们运行良好并立即返回。

我也验证了表格，据我所知应该没问题:

mysql> describe folders;
+------------------+-----------------------+------+-----+---------------------+----------------+
| Field            | Type                  | Null | Key | Default             | Extra          |
+------------------+-----------------------+------+-----+---------------------+----------------+
| id               | mediumint(8) unsigned | NO   | PRI | NULL                | auto_increment | 
| user_id          | mediumint(8) unsigned | NO   | MUL | NULL                |                | 
| folder_id        | mediumint(8) unsigned | YES  | MUL | NULL                |                | 
| created_at       | timestamp             | NO   |     | CURRENT_TIMESTAMP   |                | 
| updated_at       | timestamp             | NO   |     | 0000-00-00 00:00:00 |                | 
| modified_at      | timestamp             | NO   |     | 0000-00-00 00:00:00 |                | 
| name             | varchar(255)          | NO   |     | NULL                |                | 
| guest_permission | tinyint(3) unsigned   | NO   |     | 1                   |                | 
+------------------+-----------------------+------+-----+---------------------+----------------+
8 rows in set (0.00 sec)

mysql> show index from folders;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| folders |          0 | PRIMARY   |            1 | id          | A         |      760318 |     NULL | NULL   |      | BTREE      |         | 
| folders |          1 | user_id   |            1 | user_id     | A         |       69119 |     NULL | NULL   |      | BTREE      |         | 
| folders |          1 | folder_id |            1 | folder_id   | A         |      380159 |     NULL | NULL   | YES  | BTREE      |         | 
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

另一件事是 MySQL 服务器有时会锁定并停止接受连接，每次发生这种情况时，我通常会在失败前立即在日志文件中找到这些慢速查询之一。我在其他日志文件中看不到任何相关的错误消息，但 MySQL 重新启动使其再次响应。

有没有人知道发生了什么，或者我可以检查哪些内容来缩小问题范围？

编辑:我们在专用服务器上使用 MySQL 5.0.51a，目前有 6 个网络服务器运行 PHP 5.2.6 并通过 PDO 连接到 MySQL 服务器。所有服务器都运行 Debian Lenny。慢查询发生在所有网络服务器上。

编辑:这是相关查询的解释，带引号和不带引号:

mysql> explain SELECT * FROM `folders` WHERE `folders`.`id` = '1682995';
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | folders | const | PRIMARY       | PRIMARY | 3       | const |    1 |       | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM `folders` WHERE `folders`.`id` = 1682995;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | folders | const | PRIMARY       | PRIMARY | 3       | const |    1 |       | 
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Answer 1

这很奇怪，但我猜可能是因为您的 id 字段是 int 但您传递的是字符串(带引号)。尝试不使用字符串，看看是否有帮助。

为了帮助弄清楚到底发生了什么，将查询更改为具有相同 WHERE 子句的 SELECT 并通过 EXPLAIN 运行它。像这样:

EXPLAIN SELECT * FROM `folders` WHERE `folders`.`id` = '1682995';
EXPLAIN SELECT * FROM `folders` WHERE `folders`.`id` = 1682995;

看看有没有区别。

More info on EXPLAIN .