MySQL索引技术咨询：主键索引及关联字段、多对多表索引设置疑问

1. MySQL中，是否需要在id字段上创建索引？ #

不需要额外创建。因为AUTO_INCREMENT PRIMARY KEY本身就是InnoDB引擎的聚簇索引，主键索引默认已存在，用于快速定位行数据，无需再单独给id字段添加普通索引。

2. 请问是否应该为conversation表的staff_id和client_id字段创建INDEX？ #

从你提供的建表语句来看，已经创建了INDEX( staff_id, client_id )联合索引，这个做法是合理的，原因如下：

• InnoDB引擎要求外键字段必须有索引，否则无法创建外键约束，staff_id和client_id作为外键，索引是必要的；
• 联合索引(staff_id, client_id)可以优化两类查询：一是同时按staff_id和client_id过滤的查询，二是单独按staff_id过滤的查询（联合索引的前缀列生效）；
• 如果业务中存在单独按client_id过滤查询的场景，建议再单独创建一个INDEX(client_id)，因为当前的联合索引无法直接优化单独查询client_id的请求。

3. 对于多对多关系表（client_contact表），创建索引是否有意义？ #

非常有意义，而且你已经创建的UNIQUE ( client_id , contact_id )联合唯一索引是很必要的，具体分析：

• 多对多表的核心操作就是通过client_id关联contact_id，或者通过contact_id关联client_id，索引能大幅提升这类关联查询的效率；
• 联合唯一索引(client_id, contact_id)既可以避免同一个客户和联系人重复关联，又能优化按client_id查询的请求；
• 建议可以考虑去掉自增id主键，把(client_id, contact_id)设为主键（因为它本身就是唯一的），这样更节省存储空间，也更符合多对多表的设计规范；
• 如果需要频繁通过contact_id反向查询关联的client_id，建议再创建一个INDEX(contact_id, client_id)联合索引，优化这类查询。

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