MySQL中如何为高频查询的外键列设计合适的聚簇索引

参考消息系统初始建表语句：

create table chat_group
(
    id           int auto_increment primary key,
    title        varchar(100)         not null,
    date_created date                 not null
)

create table chat_message
(
    id                int auto_increment,
    user_id           int                  not null,
    chat_group_id     int                  not null,
    message           text charset utf8mb4 not null,
    date_created      datetime             not null
)

chat_message表最高频查询为SELECT * FROM chat_message where chat_group_id = ?，预期目标是让同群组消息在磁盘上按群组维度聚合存储，降低查询IO，但MySQL InnoDB的聚簇索引就是主键索引，要求主键必须全局唯一，无法直接用非唯一的chat_group_id作为主键。

直接用联合主键即可完美适配需求，不需要额外引入复杂设计：

• 将chat_message的主键从原来的单列自增id，调整为(chat_group_id, id)的联合主键
  InnoDB聚簇索引会按照主键定义的顺序组织物理存储：先按chat_group_id排序，同一个群组内的消息再按自增id排序，正好实现同群组消息连续存储的目标，同时chat_group_id + id的组合全局唯一，完全满足主键的唯一性约束。
• 调整后的建表语句如下：

create table chat_message
(
    id                int auto_increment,
    user_id           int                  not null,
    chat_group_id     int                  not null,
    message           text charset utf8mb4 not null,
    date_created      datetime             not null,
    primary key (chat_group_id, id)
)

注意事项：InnoDB支持联合主键中包含自增列，只要自增列不是联合索引的最左前缀，自增属性可以正常生效，不会出现主键重复问题。

• 适配其他查询场景：如果业务存在按消息ID单条查询的需求，只需给id字段单独创建一个二级唯一索引即可。二级索引叶子节点存储的是主键值，这类查询只需要一次回表，性能损耗极低，完全不会影响核心的按群组查询消息的性能。

这种设计下，按chat_group_id查询消息时会直接走聚簇索引做连续范围扫描，不需要回表，IO开销和预期的单字段chat_group_id聚簇索引效果完全一致，是该场景下的标准最优方案。

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