MySQL复合索引优化疑问：索引类型提升但扫描行数增加

一直在探索MySQL复合索引优化，之前仅使用单列索引时性能表现不佳，复合索引曾大幅提升过性能，但碰到了如下问题：

目标查询语句 #

SELECT MAX(`rtm_psa_appointment`.updated_at)
FROM `rtm_psa_appointment`
INNER JOIN `rtm_user` ON `rtm_user`.id = `rtm_psa_appointment`.user_id
INNER JOIN `rtm_psa_entity` ON `rtm_psa_entity`.id = `rtm_psa_appointment`.resource_id
WHERE (`rtm_user`.`id`=106) AND (`rtm_psa_entity`.`id`=456968)

两种索引策略的EXPLAIN结果 #

• 仅使用单列索引时：查询类型为index_merge，扫描行数估算为1×1×69
• 创建rtm_psa_appointment表的复合索引(resource_id, user_id, id)并移除resource_id单列索引后：查询类型变为ref，但扫描行数估算增至1×1×414；后续调整复合索引为(resource_id,user_id,updated_at,id)，结果仍无改善

疑问 #

此场景下，复合索引的ref查询相比原index_merge是否更优？

原表结构 #

CREATE TABLE `rtm_psa_appointment` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `oplock` INT(10) NOT NULL DEFAULT '0',
    `user_id` INT(10) NOT NULL,
    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL DEFAULT NULL,
    `deleted_at` DATETIME NULL DEFAULT NULL,
    `deleted_by` INT(10) NULL DEFAULT '0',
    `is_deleted` TINYINT(1) NULL DEFAULT '0',
    `created_by` INT(10) NULL DEFAULT NULL,
    `updated_by` INT(10) NULL DEFAULT NULL,
    `psa_id` INT(10) NOT NULL,
    `psa_created_at` DATETIME NOT NULL,
    `psa_last_mod_at` DATETIME NOT NULL,
    `start_at` DATETIME NOT NULL,
    `end_at` DATETIME NOT NULL,
    `title` VARCHAR(256) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `resource_id` INT(10) NOT NULL,
    `entity` JSON NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `created_at` (`created_at`) USING BTREE,
    INDEX `updated_at` (`updated_at`) USING BTREE,
    INDEX `deleted_at` (`deleted_at`) USING BTREE,
    INDEX `deleted_by` (`deleted_by`) USING BTREE,
    INDEX `is_deleted` (`is_deleted`) USING BTREE,
    INDEX `oplock` (`oplock`) USING BTREE,
    INDEX `user_id` (`user_id`) USING BTREE,
    INDEX `psa_created_at` (`psa_created_at`) USING BTREE,
    INDEX `psa_last_mod_at` (`psa_last_mod_at`) USING BTREE,
    INDEX `start_at` (`start_at`) USING BTREE,
    INDEX `end_at` (`end_at`) USING BTREE,
    INDEX `resource_id` (`resource_id`) USING BTREE,
    CONSTRAINT `rtm_psa_appointment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `forge`.`rtm_user` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT `rtm_psa_appointment_ibfk_2` FOREIGN KEY (`resource_id`) REFERENCES `forge`.`rtm_psa_entity` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=44203
;

解答 #

不能仅凭EXPLAIN的估算扫描行数判断性能，得结合实际执行时间、逻辑IO等真实指标分析，以下是具体拆解：

1. 两种执行方式的原理差异

   • Index Merge（交集合并）：MySQL会分别通过user_id和resource_id的单列索引找到符合条件的记录集，再对两个集取交集，最后回表读取updated_at计算最大值。优势是当两个单列索引的过滤性都较强时，交集后的实际行数很少；缺点是需要额外做索引交集运算，且必须回表。
   • 复合索引的ref查询：使用(resource_id, user_id, ...)复合索引时，MySQL会先匹配resource_id=456968，再在该分支下匹配user_id=106，直接遍历匹配条目获取updated_at。如果复合索引包含updated_at（如你调整后的(resource_id,user_id,updated_at,id)），这就是覆盖索引，无需回表，理论上性能更优；但你没看到改善，大概率是MySQL统计信息过时，导致估算行数不准，或者该复合索引的基数分布不理想。

2. 实操优化建议

   • 用真实指标判断性能：执行两次查询，用SET profiling = 1;开启 profiling，之后跑查询再用SHOW PROFILE;看实际执行耗时；或者查看SHOW SESSION STATUS LIKE 'Handler_%'的输出，Handler_read_key（索引键读取次数）、Handler_read_rnd_next（回表读取次数）这些指标比EXPLAIN的估算行数更靠谱。
   • 创建最优复合索引：你的查询目标是求MAX(updated_at)，最优索引应为(resource_id, user_id, updated_at DESC)。这个索引能让MySQL直接定位到匹配resource_id和user_id的第一条（按updated_at降序排列的）记录，直接返回最大值，不需要扫描所有匹配条目，效率最高。
   • 更新统计信息：执行ANALYZE TABLE rtm_psa_appointment;更新表统计信息，让MySQL的优化器能基于最新数据生成更准确的执行计划。
   • 保留必要的单列索引：不要随意删除resource_id单列索引，除非确认所有用到该列的查询都能被复合索引覆盖，否则会影响其他依赖该单列索引的查询性能。

3. 结论
   如果能创建覆盖目标查询的最优复合索引（包含updated_at并降序排列），且统计信息准确，那么复合索引的ref查询性能会远优于index_merge；如果只是普通复合索引需要回表，那实际性能可能和index_merge持平甚至更差，具体取决于数据分布。

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