单列索引与首列相同的复合索引性能差异及删除优化疑问

我有一张映射表，结构如下：

group_id (int)
item_id (int)

目前已存在两个复合索引：group_id, item_id 和 item_id, group_id。

执行按group_id删除表中所有记录的操作（例如DELETE FROM table_name WHERE group_id = 1）时速度很慢。通过EXPLAIN分析及资料查阅得知，即使没有group_id的单列索引，数据库也会使用以group_id为首列的复合索引group_id, item_id。

我有两个疑问：

1. 给group_id创建专用单列索引，性能提升幅度是边际提升还是显著提升？
2. 是否是item_id, group_id索引因需要清理索引条目而影响了删除性能？

一、单列索引group_id的性能提升幅度 #

执行DELETE WHERE group_id = ?时，现有复合索引group_id, item_id确实能用来定位目标行，但这个索引的每条条目都额外包含了item_id数据，相比单列索引group_id，它的索引体积更大——同样的内存能缓存的索引条目更少，磁盘IO次数会更多，数据量越大，差异越明显。

性能提升幅度要看你的数据规模：

• 如果每个group_id对应的item_id数量不多，索引体积差异不大，此时添加单列索引的提升是边际性的，只会减少少量IO开销。
• 如果每个group_id关联的item_id数量极多（比如上万甚至数十万条），复合索引group_id, item_id的体积会比单列索引大很多，此时添加单列索引能显著减少磁盘IO，提升删除速度，属于显著提升。

另外，删除操作的瓶颈除了索引查找，还涉及事务日志写入、表数据碎片整理以及其他索引维护，所以单列索引的提升要建立在“索引查找是当前瓶颈”的前提下。

二、item_id, group_id索引对删除性能的影响 #

是的，这个索引确实会拖慢删除速度。

每次执行DELETE时，数据库不仅要删除表中的数据行，还要维护所有相关索引：

• 对于group_id, item_id索引，因为是按group_id定位行，删除时可以批量定位并清理对应索引条目，相对高效。
• 对于item_id, group_id索引，它的首列是item_id，数据库无法通过group_id快速定位该索引中需要删除的条目，只能逐个遍历索引条目匹配group_id = ?，或者先找出所有要删除的item_id，再逐个去这个索引中删除对应条目——这个过程开销极大，尤其是要删除的行数很多时，相当于对该索引做了大量随机IO操作。

如果你的业务不需要通过item_id反向查询group_id（比如没有SELECT * FROM table WHERE item_id = ?这类查询），可以直接删除item_id, group_id索引，这会大幅提升删除操作的速度。

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