MySQL多客户端同schema数据库Deals表主键选择方案咨询

核心结论 #

你倾向的方案3（仅以自增deal_id作为主键，额外建立client_id相关二级索引） 是该场景下的最优选择，完全符合InnoDB引擎的主键设计最佳实践。 #

前提：InnoDB聚簇索引核心特性 #

所有方案的性能差异本质上都源于InnoDB的聚簇索引设计，先明确两个核心规则：

• 主键的排序逻辑决定了数据行的物理存储顺序，自增主键插入时永远追加在数据页尾部，不会产生页分裂，写入性能最优
• 所有二级索引的叶子节点存储的是主键值，主键长度越小，所有二级索引的空间占用越低，内存能缓存的索引条目越多，查询性能越好

三个方案的优劣补正 #

方案1：联合主键(client_id, deal_id) #

你提到的跨页问题本质是高频页分裂+随机IO：不同client的写入请求是交替到达的，同一client_id对应的交易数据不会连续写入，会导致同一client的行分散在多个物理页，插入时频繁触发页分裂，查询同一client的交易时需要扫描多个物理页，数据量超过百万级后性能下降非常明显。另外联合主键总长度是单int主键的2倍，会导致所有二级索引的空间占用直接翻倍。

方案2：联合主键(deal_id, client_id) #

该方案的写入性能和单主键接近，但性价比极低：

• 主键额外存储了client_id字段，全表存储空间会多占用一列的容量
• 二级索引叶子节点存储的是8字节的联合主键（假设两个字段都是int类型），空间占用比单主键高一倍
• 所有带client_id的过滤查询都无法用到主键前缀匹配，还是需要额外建立client_id的二级索引，完全没有额外收益

方案3：自增deal_id作为主键，单独建立client_id二级索引 #

除了你提到的空间优势外，还有几个你没考虑到的核心收益：

• 关联逻辑更简单：后续如果有其他表需要关联Deals表，只需要存储deal_id单个字段即可，不需要同时存储client_id和deal_id，减少数据冗余，关联查询条件更简洁
• 扩展性更强：如果后续数据量上涨需要分库分表，单字段主键的路由逻辑远简单于联合主键，兼容几乎所有分库分表中间件的默认规则
• 维护成本更低：如果后续业务需要调整交易所属的客户，仅需要修改client_id字段的值即可，不会触及主键变更；如果是联合主键，修改主键值会触发整行数据的物理位置移动，性能损耗极高，还有概率引发索引异常
• 单行查询性能更高：针对按deal_id查交易详情的高频场景，直接走主键索引即可返回结果，不需要多匹配一个client_id字段，查询效率更高

额外优化建议 #

建立client_id相关二级索引时，建议直接建成覆盖索引，把常用的查询字段加入索引，比如高频查询需要返回交易时间、交易金额，就建立(client_id, deal_time, amount)的联合索引，查询时不需要回表查主键索引，性能可以提升数倍。

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