通过主键MySQL UPDATE查询有时非常缓慢
我们网站上的特定UPDATE查询有时运行非常缓慢,并且检查的行数多于必要数量。它通过主键过滤,所以我希望MySQL总是只需要检查一行。通过主键MySQL UPDATE查询有时非常缓慢
下面是MySQL的慢查询日志的一些例子:
# Time: 090702 12:59:06
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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
# [email protected]: 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个webservers运行PHP 5.2.6并通过PDO连接到MySQL服务器。所有的服务器都在运行Debian Lenny。缓慢的查询发生在所有的Web服务器上。
编辑:下面是相关的查询的解释,不管有没有行情:
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)
傻了。我忘记folders
表中有一些触发器,当然这是一个内部查询导致我的问题...
有一个额外的表tree
维护文件夹之间的关联,当在层次结构中删除或移动文件夹时触发器更新这些关联。在UPDATE
触发器中,它必须在添加新引用之前删除对该文件夹的所有现有引用。相关DELETE
查询开始如下:
DELETE FROM `tree`
WHERE `folder_id` IN (
SELECT `folder_id` FROM `children`
)
AND ...
children
是我事先存储的文件夹ID的我需要一个临时表。 现在,由于某种原因,MySQL不能优化这个查询,但如果我使用一个RIGHT JOIN
而是工作完全正常:
DELETE `tree`.* FROM `tree`
RIGHT JOIN children USING (folder_id)
WHERE ...
既然我已经改变了这个查询MySQL的慢查询日志仍然幸福地空着,我们没有经历任何MySQL锁定。
这是奇怪,但我的猜测是它可能是因为你的id字段数据类型为int,但你传递一个字符串(引用)。尽量不要使用字符串,看看它是否有帮助。
为了帮助找出究竟发生了什么,将查询更改为具有相同WHERE子句的SELECT并通过EXPLAIN运行。像这样:
EXPLAIN SELECT * FROM `folders` WHERE `folders`.`id` = '1682995';
EXPLAIN SELECT * FROM `folders` WHERE `folders`.`id` = 1682995;
看看是否有区别。
没有区别。另外,如果这真的是我期望查询总是这么慢的原因。但是,感谢您的帮助,无论如何! – toupeira 2009-07-02 15:06:46
这真的很有趣,我想知道为什么它也这样做。你尝试过EXPLAIN SELECT * FROM`folders` WHERE`id` = something? – 2009-07-02 14:46:05