随机插入场景下联合主键表是否会碎片化？需添加自增主键吗？

1. 随机插入会导致表碎片化吗？ #

会的。InnoDB的主键是聚簇索引，数据会按照主键顺序物理存储。你的表用(obj_id, attr_id)作为联合主键，随机插入意味着主键值无序，插入时需要定位到对应主键的存储页；如果目标页剩余空间不足，就会触发页分裂——将一个页拆分为两个，这会导致页内出现空闲空间，长期积累后必然产生碎片化。碎片化会增加磁盘IO开销，影响整体读写性能。

2. 是否需要添加自增主键？ #

不建议添加，除非业务有特殊需求。你明确更关注SELECT性能，当前的联合主键作为聚簇索引，刚好能匹配绝大多数基于obj_id和attr_id的查询：查询时直接通过聚簇索引定位数据，无需回表。如果添加自增主键，原联合主键会降级为二级唯一索引，此时查询obj_id和attr_id需要先扫描二级索引，再通过自增主键回表取完整数据，反而会增加IO开销，降低SELECT性能。

3. 添加自增主键是否仅提升插入速度，对SELECT无帮助？ #

不是。添加自增主键确实能让插入更顺序化，减少页分裂和碎片化，理论上能降低整体磁盘IO压力，但这对SELECT的帮助非常有限，甚至可能起反作用：

• 如果核心查询是基于obj_id和attr_id，二级索引+回表的模式会比直接用聚簇索引查询慢很多。
• 只有当查询大多基于自增主键，或表碎片化严重到影响所有查询时，自增主键对SELECT的间接帮助才会体现，但这显然不符合你的场景。

4. 随机插入场景下，更优的表定义（侧重SELECT） #

保留当前的联合主键作为聚簇索引，同时通过以下方式优化随机插入带来的碎片化问题：

• 调整页填充因子：设置innodb_fill_factor=90（默认是100），给每个数据页预留10%的空间，减少随机插入时的页分裂概率。
• 批量插入同组数据：如果业务允许，尽量批量插入相同obj_id的记录，此时主键相对有序，能减少页分裂。
• 定期维护表：在业务低峰期执行OPTIMIZE TABLE Associations，整理碎片化的页，但注意该操作会锁表，需控制执行频率。
• 确认主键顺序匹配查询模式：如果你的查询大多是按obj_id过滤、再匹配attr_id，当前(obj_id, attr_id)的主键顺序是最优的——同obj_id的记录物理相邻，范围查询时能快速扫描连续的页。

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