MySQL多对多表最优索引设计问询：tag_to_photo双向查询优化

先明确你的表结构：

CREATE TABLE `tag_to_photo` ( 
  `tag_id` INT UNSIGNED NOT NULL, 
  `photo_id` INT UNSIGNED NOT NULL, 
  PRIMARY KEY (`tag_id`, `photo_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

咱们一步步拆解两种查询场景的索引利用逻辑，以及最优方案：

场景1：查询某标签关联的所有照片（WHERE tag_id = ?） #

你的主键是(tag_id, photo_id)的联合聚簇索引，InnoDB的数据是按照主键顺序物理存储的。对于WHERE tag_id = ?的查询，MySQL可以直接利用联合索引的前缀匹配特性，快速定位到所有该tag_id对应的行，而且因为是聚簇索引，不需要额外回表就能拿到所有数据。

所以这种场景下，完全不需要额外创建单独的tag_id索引——单独建索引只会浪费存储空间，还会增加插入/更新/删除操作时的索引维护成本，属于冗余操作。

场景2：查询某照片关联的所有标签（WHERE photo_id = ?） #

这时候现有主键索引就帮不上忙了：因为主键的第一列是tag_id，photo_id是第二列，MySQL无法通过前缀匹配快速定位photo_id相等的行，只能低效地扫描全表或走非最优索引路径。所以必须补充一个针对性的索引。

最优选择是创建**(photo_id, tag_id)的联合索引**，而非单独的photo_id索引：

• 这是一个覆盖索引：当执行SELECT tag_id FROM tag_to_photo WHERE photo_id = ?时，MySQL可以直接从这个索引中获取所需的tag_id值，不需要回表查询聚簇索引，性能提升明显；
• 存储空间上，(photo_id, tag_id)联合索引和单独的photo_id索引差异极小（都是两个INT列），但覆盖索引的性能优势是实打实的。

最终结论 #

• 保留现有PRIMARY KEY (tag_id, photo_id)，无需额外创建tag_id单独索引；
• 新增索引：CREATE INDEX idx_photo_tag ON tag_to_photo (photo_id, tag_id);

这样就能同时高效支持两种查询场景，并且把索引的维护成本和存储空间控制在最优水平。

内容的提问来源于stack exchange，提问作者no one special