MySQL InnoDB表生成year/number组合键的问题咨询

1. 并发插入时是否会生成重复的year/number组合键？如何解决？ #

会出现重复的情况。并发场景下，多个事务同时执行SELECT MAX(num_field)+1时，会读取到相同的最大值，最终插入相同的num_field值，导致year_field+num_field的组合重复。

解决方法：

• 添加唯一约束兜底：给(year_field, num_field)创建唯一约束，强制数据库拒绝重复数据。即使触发器生成了重复值，数据库会抛出错误，应用捕获后重试插入即可（重试时触发器会重新计算正确的num_field）。执行语句：

  ALTER TABLE test_db.test_table ADD UNIQUE KEY uk_year_num (year_field, num_field);
  

• 使用序列表维护编号：创建一张单独的序列表year_seq，结构为year_field INT PRIMARY KEY, current_num INT DEFAULT 1。触发器中先更新该表的编号（不存在则插入），再将编号赋值给New.num_field。这种方式利用数据库行锁避免并发冲突，比直接查原表MAX值更可靠。示例触发器代码：

  BEGIN
    INSERT INTO year_seq (year_field, current_num)
    VALUES (New.year_field, 1)
    ON DUPLICATE KEY UPDATE current_num = current_num + 1;
    
    SELECT current_num INTO New.num_field
    FROM year_seq
    WHERE year_field = New.year_field;
  END
  

2. 多用户环境下使用该触发器是否会拖慢表操作？ #

会有一定性能损耗，主要原因：

• 原触发器每次插入都要执行SELECT MAX(num_field)+1，如果year_field没有索引，会触发全表扫描；即使有索引，高并发下索引扫描的锁竞争和IO开销也会被放大。
• 若使用唯一约束，插入失败后的重试操作也会增加额外的执行成本。

优化建议：

• 给(year_field, num_field)创建唯一索引，既解决重复问题，又能加速MAX值的查询。
• 替换原触发器逻辑为序列表方案，序列表的更新是行级锁操作，开销远小于扫描原表。
• 尽量合并批量插入操作，减少触发器的执行次数，降低整体开销。

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