MariaDB 10.6.x/InnoDB：复合主键与未用代理整数主键性能对比？

InnoDB的聚簇索引特性是核心——所有表数据都存储在主键索引的叶子节点中，主键的选择直接决定了数据的存储布局和操作性能，这一点在MariaDB 10.6.x中和早期MySQL InnoDB的逻辑一致，但细节优化更完善，以下结合你的问题给出具体判断依据：

1. 天然主键为字符串类型：一定要加代理整数主键 #

哪怕这个代理主键永远不用于JOIN或关联操作，理由如下：

• 存储空间损耗：字符串主键（比如UUID、业务编码）占用的字节数远大于整数，会让聚簇索引页能容纳的记录数大幅减少，导致查询时需要更多IO操作。同时所有二级索引都会存储主键值，字符串主键会让二级索引体积翻倍甚至更多，进一步拉低查询效率。
• 插入性能瓶颈：字符串主键几乎不可能是严格递增的，插入时会分散到不同的索引页，频繁触发页分裂和随机IO；而自增整数主键只会追加到聚簇索引的最后一页，全程顺序IO，插入性能差距在高并发场景下会被放大。

2. 天然主键为复合整数主键：视实际场景决策 #

以你提到的my_table(category_id, item_id, detailA, detailB)为例，复合主键(category_id, item_id)均为整数，是否加代理主键要重点看以下两点：

适合加代理主键的场景 #

• 插入模式随机：如果category_id和item_id的组合没有递增规律（比如随机插入不同分类的条目，item_id也无连续递增逻辑），插入时会频繁触发聚簇索引页分裂，此时自增代理主键能保证顺序插入，彻底避免页分裂问题，大幅提升插入性能。
• 二级索引数量多：复合主键（两个INT共8字节）比单个自增INT主键（4字节）长度大一倍，表上的每一个二级索引都会因此增加额外的存储开销。如果表上有3个及以上二级索引，代理主键能显著减小索引整体体积，降低查询时的IO成本。

不建议加代理主键的场景 #

• 插入模式有序：如果插入的记录是按category_id+item_id递增的（比如先批量插入分类1的所有条目，再处理分类2），此时复合主键的插入是顺序的，不会引发大量页分裂，性能和自增主键差异极小，没必要额外维护代理主键。
• 高频查询依赖复合主键：如果大部分查询都是直接基于category_id和item_id的组合（比如WHERE category_id=? AND item_id=?），复合主键本身就是聚簇索引，查询时直接命中数据，无需回表，性能最优。此时加代理主键反而需要额外维护一个主键索引，还得给复合主键加唯一约束，徒增存储和维护成本。

性能与完整性的核心考量 #

性能层面 #

• 聚簇索引页利用率：主键越小，每页能存储的记录越多，IO效率越高。自增INT主键的页利用率是复合INT主键的2倍左右，更是字符串主键的数倍。
• 插入稳定性：顺序插入的自增主键能避免页分裂和碎片产生，减少磁盘IO和锁竞争，在高并发写入场景下优势尤为明显。
• 索引维护成本：单一代理主键的维护成本低于复合主键，但如果需要保留复合主键作为唯一约束，要额外承担唯一索引的维护开销，需要权衡。

完整性层面 #

• 无论是否使用代理主键，都必须保证天然主键的唯一性：如果用代理主键，一定要给字符串或复合主键添加唯一约束，防止数据重复。
• 代理主键无业务意义，不会和业务逻辑绑定，能避免后续业务主键规则变更带来的表结构调整风险。

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