MySQL多列索引未生效及查询优化问题咨询

表结构 #

CREATE TABLE `Score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `playerId` int NOT NULL,
  `points` int NOT NULL,
  `gameId` int NOT NULL,
  `isDeleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Score_playerId_fkey` (`playerId`),
  KEY `Score_gameId_fkey` (`gameId`),
  CONSTRAINT `Score_gameId_fkey` FOREIGN KEY (`gameId`) REFERENCES `Game` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `Score_playerId_fkey` FOREIGN KEY (`playerId`) REFERENCES `Player` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=813 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

待优化查询 #

SELECT *
FROM `Score`
WHERE `Score`.`gameId` = 2 AND `Score`.`isDeleted` = 0
ORDER BY `Score`.`points` DESC
LIMIT 50 OFFSET 0;

初始EXPLAIN结果 #

id  select_type table   partitions  type    possible_keys       key                 key_len ref     rows    filtered    Extra
1   SIMPLE      Score   NULL        ref     Score_gameId_fkey   Score_gameId_fkey   4       const   160     100.00      Using where; Using filesort

补充信息 #

实际表中gameId=2的行数为160：

SELECT COUNT(*) FROM Score WHERE gameId = 2;
-- 结果：160

尝试添加两个索引后：

CREATE INDEX gameId_isDeleted ON `Score` (`gameId`, `isDeleted`);
CREATE INDEX isDeleted ON `Score` (`isDeleted`);

修改后的EXPLAIN结果：

id  select_type table   partitions  type    possible_keys                                   key                 key_len ref     rows    filtered    Extra
1   SIMPLE      Score   NULL        ref     Score_gameId_fkey,gameId_isDeleted,isDeleted    Score_gameId_fkey   4       const   160     100.00      Using where; Using filesort

疑问 #

1. 对EXPLAIN结果的理解是否正确？
2. 如何最优优化此查询？
3. 添加points字段索引能否避免ORDER BY导致的filesort？

1. 对EXPLAIN结果的理解是否正确？ #

你的理解完全正确：

• 优化器选择了Score_gameId_fkey索引，先筛选出gameId=2的160条数据
• 之后逐行检查isDeleted=0的条件（因为原有索引不包含isDeleted字段）
• 最后对符合条件的数据执行filesort，完成ORDER BY points DESC的排序

新增的gameId_isDeleted索引没被选中的原因是：isDeleted字段区分度极低（大部分值都是0），优化器判断用原有单字段索引和联合索引的查询成本几乎一致，且单字段索引体积更小，所以优先选择了原有索引。而单独的isDeleted索引完全不适合这个查询——它会返回大量数据，再筛选gameId=2的效率远低于用gameId索引。

2. 最优优化方案 #

要同时解决Using where和Using filesort的问题，需要创建覆盖查询条件与排序逻辑的联合索引：

CREATE INDEX idx_gameId_isDeleted_points ON `Score` (`gameId`, `isDeleted`, `points` DESC);

这个索引的设计逻辑：

• 前两列gameId+isDeleted直接匹配WHERE条件，快速筛选出符合要求的行
• 第三列points DESC让索引本身按分数降序排列，无需再执行filesort
• 额外优化：如果查询不需要返回全表字段，把SELECT *改成只查询需要的字段（比如SELECT playerId, points），这样索引就能完全覆盖查询，不需要回表取整行数据，效率会进一步提升。

即使必须用SELECT *，这个联合索引依然能消除filesort，同时降低数据筛选的成本。

3. 单独添加points索引能否避免filesort？ #

不能。单独的points索引无法匹配WHERE条件里的gameId和isDeleted，优化器不会选择它——总不能先按points排序全表，再筛选gameId=2和isDeleted=0的数据，这种方式效率远低于现有方案。只有当索引能同时覆盖WHERE筛选和ORDER BY排序逻辑时，才能避免filesort。

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