关于MariaDB中comments表改用复合自增主键优化性能的咨询

你提出的将主键从单一comment_id改为(post_id, comment_id)复合主键的思路完全可行，且非常贴合你的查询场景——InnoDB的聚簇索引特性会让同一post_id的评论物理连续存储，大幅减少关联查询时的数据页访问次数，直接提升查询效率。以下是具体的优化细节和补充建议：

一、主键调整实操步骤 #

1. 先做数据备份：执行任何结构变更前，务必备份comments表，避免数据丢失。
2. 移除原主键：

   ALTER TABLE comments DROP PRIMARY KEY;
   

3. 添加复合主键：

   ALTER TABLE comments ADD PRIMARY KEY (post_id, comment_id);
   

   注意保留comment_id的AUTO_INCREMENT属性，确保能自动生成唯一的评论ID，满足同一帖子下多条评论的需求。MariaDB支持将自增列作为复合主键的第二列，此时comment_id仍保持全局自增，同一post_id下的评论会按comment_id递增顺序存储。

二、索引优化补充 #

1. comment_id二级索引：由于后续需要通过comment_id单独查询（如用户历史功能），需创建单独的二级索引：

   CREATE INDEX idx_comment_id ON comments(comment_id);
   

   复合主键(post_id, comment_id)无法直接高效支持单comment_id查询，这个二级索引是必要的。
2. author_id索引按需创建：因为用户历史功能访问频率极低，无需提前创建author_id索引，等实际有需求时再添加即可，避免占用额外存储空间。

三、写入与性能细节 #

• 虽然写入操作不频繁，但复合主键的插入逻辑更贴合你的查询场景：同一post_id的新评论会追加到该帖子对应的聚簇索引末尾，比原全局自增主键的插入（追加到表末尾）在后续查询时更高效。
• 若comment_id的增长范围可控，可考虑将其数据类型从INT改为MEDIUMINT（最大支持1677万），进一步减少存储空间，提升数据页的缓存命中率。

四、缓存策略强化 #

既然你已经了解memcached缓存方案，建议：

• 以post_id为缓存key，缓存热门帖子的完整评论列表，直接绕过数据库查询，应对高查询量。
• 由于写入不频繁，缓存过期时间可设置为1~2小时；当有新评论插入时，主动更新对应post_id的缓存，保证数据一致性。

五、进阶优化（可选） #

如果你的评论总量极大（千万级以上），可考虑：

• 按post_id范围分区：将comments表按post_id的区间进行分区，查询特定帖子评论时仅扫描对应分区，进一步降低IO开销。
• 拆分冷数据：将超过一定时间的旧评论迁移到归档表，减少主表的数据量，提升查询速度。

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