MySQL 8中UPDATE查询无法充分利用索引问题排查

这事儿我之前排查类似问题时也遇到过，咱们一步步拆解为什么同条件下SELECT能完美用上索引，UPDATE却非要走filesort：

首先先看咱们的表结构：

CREATE TABLE `queue` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `type` int(10) unsigned NOT NULL, 
  `posted_on` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
  `status` enum('pending','complete','error') NOT NULL DEFAULT 'pending', 
  `body` blob NOT NULL, 
  `process_id` int(10) unsigned DEFAULT NULL, 
  `acquired_on` datetime(6) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `acquiredon` (`acquired_on`), 
  KEY `type_status_processid_postedon` (`type`,`status`,`process_id`,`posted_on`) USING BTREE 
);

为什么SELECT能完美利用索引？ #

咱们看这条SELECT的EXPLAIN结果：

EXPLAIN SELECT * FROM `queue` FORCE INDEX (`type_status_processid_postedon`) 
WHERE type = 1 AND `status` = 'pending' AND `process_id` IS NULL 
ORDER BY `posted_on` ASC LIMIT 1;

-- 执行计划结果
+----+-------------+-------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                  | key                            | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------+
| 1  | SIMPLE      | queue | NULL       | ref  | type_status_processid_postedon | type_status_processid_postedon | 10      | const,const,const | 1    | 100.00   | Using index condition |
+----+-------------+-------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------+

这里的联合索引type_status_processid_postedon顺序是type → status → process_id → posted_on，咱们的WHERE条件刚好命中了前三个字段的等值匹配（包括process_id IS NULL），而ORDER BY的posted_on正好是索引的第四个字段。

对SELECT来说，索引本身就是按posted_on有序排列的，所以MySQL可以直接顺着索引找到符合条件的第一条数据，完全不需要额外排序，自然不会触发filesort。

为什么UPDATE却触发了Filesort？ #

再看同条件的UPDATE执行计划：

EXPLAIN UPDATE `queue` FORCE INDEX(`type_status_processid_postedon`) 
SET `process_id` = 1 
WHERE `type` = 1 AND `status` = 'pending' AND `process_id` IS NULL 
ORDER BY `posted_on` ASC LIMIT 1;

-- 执行计划结果
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys                  | key                            | key_len | ref               | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------------+
| 1  | UPDATE      | queue | NULL       | range | type_status_processid_postedon | type_status_processid_postedon | 10      | const,const,const | 1    | 100.00   | Using where; Using filesort |
+----+-------------+-------+------------+-------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-----------------------------+

核心原因在于MySQL处理UPDATE（结合ORDER BY和LIMIT）的执行逻辑和SELECT完全不同：

• 对UPDATE来说，它的执行流程是先通过WHERE条件找到所有符合要求的行，然后把这些行提取出来，再执行ORDER BY排序，最后取LIMIT 1进行更新。
• 虽然索引能帮它快速定位到符合WHERE条件的行，但MySQL的优化器在处理UPDATE的ORDER BY时，并没有利用索引本身的有序性来跳过排序步骤。这是因为更新操作涉及到行锁、事务一致性等额外考量，优化器会优先确保找到所有匹配行，再进行排序筛选，而不是直接顺着索引的顺序去取第一条。

简单说就是：SELECT可以直接利用索引的有序性一步到位找到目标行，但UPDATE需要先把所有匹配行捞出来再排序，哪怕只有1行，也会触发filesort的逻辑。

内容的提问来源于stack exchange，提问作者Ian