MySQL搜索记录表索引优化：合并特定索引是否可行？

表结构 #

CREATE TABLE `searches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(1) unsigned NOT NULL,
  `query` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userId` int(10) unsigned NOT NULL,
  `connectionId` int(10) unsigned NOT NULL,
  `pluginId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key,deleted` (`publicId`,`deleted`),
  KEY `deleted,userId` (`deleted`,`userId`),
  KEY `deleted,connectionId,pluginId` (`deleted`,`connectionId`,`pluginId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

核心查询场景 #

• 查询特定用户的所有搜索记录：依赖deleted+userId联合索引
• 查询特定connection和plugin下的所有搜索记录：依赖deleted+connectionId+pluginId联合索引

索引合并方案分析 #

直接将两个索引合并为deleted+userId+connectionId+pluginId的联合索引不可行，无法同时满足两个场景的性能需求，具体原因：

1. 针对「特定用户搜索记录」查询：
   该索引的前缀是deleted+userId，符合前缀匹配规则，能正常利用索引定位，性能和原deleted,userId索引相当。
2. 针对「特定connection+plugin搜索记录」查询：
   该查询条件不含userId，无法匹配索引的前缀规则，MySQL只能通过deleted字段过滤数据，之后需要扫描大量符合deleted条件的记录来匹配connectionId和pluginId，查询性能会大幅下降，远不如原deleted,connectionId,pluginId索引高效。

可行的索引体积优化方案 #

1. 删除无用冗余索引 #

检查表中key,deleted索引，表结构中未定义publicId字段，大概率是笔误或废弃索引，直接删除可减少索引体积。

2. 采用部分索引（MySQL 8.0+支持） #

如果deleted是软删除标记，且绝大多数查询都是针对deleted=0（未删除）的记录，可将两个索引改为部分索引：

-- 替换原`deleted,userId`索引
CREATE INDEX idx_user_deleted ON searches(userId) WHERE deleted=0;
-- 替换原`deleted,connectionId,pluginId`索引
CREATE INDEX idx_conn_plugin_deleted ON searches(connectionId, pluginId) WHERE deleted=0;

部分索引仅包含deleted=0的记录，体积会比全量索引小很多，同时完全满足日常查询需求。

3. 清理历史数据 #

如果业务中极少查询已删除的记录，可定期清理deleted=1的历史数据，从根源上减少表和索引的整体体积。

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