mysql 减少文件排序_消除更新查询中的mysql文件排序

我有这样的表用于在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