我有这样的表用于在mysql中实现队列:
CREATE TABLE `queue` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`queue_name` varchar(255) NOT NULL,
`inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`inserted_by` varchar(255) NOT NULL,
`acquired` timestamp NULL DEFAULT NULL,
`acquired_by` varchar(255) DEFAULT NULL,
`delayed_to` timestamp NULL DEFAULT NULL,
`priority` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`status` varchar(255) NOT NULL DEFAULT 'new',
PRIMARY KEY (`id`),
KEY `queue_index` (`acquired`,`queue_name`,`priority`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
我的问题是mysql在运行update时使用filesort . 执行速度非常慢(表中的800k行为5秒) .
DESCRIBE UPDATE queue SET acquired = "test" WHERE acquired IS NULL AND queue_name = "q1" ORDER BY priority, id LIMIT 1;
+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+
| 1 | SIMPLE | queue | range | queue_index | queue_index | 772 | const,const | 409367 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+-------------+---------+-------------+--------+-----------------------------+
奇怪的是,当我运行具有相同WHERE条件的SELECT查询并且未使用ORDER列filesort时:
DESCRIBE SELECT id FROM queue WHERE acquired IS NULL AND queue_name = "q1" ORDER BY priority, id LIMIT 1;
+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+
| 1 | SIMPLE | queue | ref | queue_index | queue_index | 772 | const,const | 409367 | Using where; Using index |
+----+-------------+-------+------+---------------+-------------+---------+-------------+--------+--------------------------+
(查询时间0s)
有谁知道如何避免在更新查询中使用filesort或如何提高其性能?
问候,Matzz
1 回答
在mysql论坛(http://forums.mysql.com/read.php?24,620908,620908#msg-620908)讨论后,我报告了错误http://bugs.mysql.com/bug.php?id=74049(已经过验证) . 可以使用SELECT FOR UPDATE绕过问题: