事件描述

环境 mysql 8.0.24

  1. 在对同步数据的检查时发现了ID异常，明明数据只有几百条但是ID却到了几千几万

    1. image.png

      问题排查

      同步方法为 springbatch 定时对全部数据进去入库，入库时会使用的sql为**INSERT ... ON DUPLICATE KEY UPDATE **

  2. 第一反应就是同步的SQL有问题

    1. 一开始sql考虑了 on duplicate key update 和 replace into，但是由于 replace into会做删除在新增的操作导致无限消耗自增ID ，所以最中选择了 on duplicate key update ，但是目前来看 on duplicate key update 这个也有问题😫
  3. 通过测试发现真有问题，insert错误时在进行更新会出现Affected rows: 2，也不指定怎么回事，最后看到个博文跟我的情况一模一样

  4. 原来是 **MySQL innodb_autoinc_lock_mode **的问题

    INSERT … ON DUPLICATE KEY UPDATE …对主键的影响 会出现自增字段突然跳跃式增长的问题

    1. select @@innodb_autoinc_lock_mode;
    2. 关于MySQL innodb_autoinc_lock_mode介绍
    3. MySQL文档

      处理

  5. 修改 MySQL innodb_autoinc_lock_mode

    1. 编辑/etc/my.cnf
    2. [mysqld] 加入如下行: innodb_autoinc_lock_mode=0
  6. 数据处理完之后 先删除 表 ID， 在新增 表ID
    1. ALTER TABLE test_on_duplicate_key_update DROPid;
    2. ALTER TABLE test_on_duplicate_key_update ADDidmediumint(11) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
  7. 这个博主给出的建议
    1. 修改业务逻辑
      1. 修改业务逻辑，将INSERT … ON DUPLICATE KEY UPDATE …语句拆开，先去查询，然后去更新，这样就可以保证主键不会不受控制的增大，但增加了复杂性，原来的一次请求可能变为两次，先查询有没有，然后去更新。
    2. 删除表的自增主键
      1. 删除自增主键，让唯一索引来做主键，这样子基本不用做什么变动，只要确定目前的自增主键没有实际的用处即可，这样的话，插入删除的时候可能会影响效率，但对于查询多的情况来说，小A比较两种之后更愿意选择后者。

后记

  1. 我根据我这边的实际情况选择了 第一种
    1. 注意这种可能会出现死锁。
    2. 插入的并发性也会受很大影响
  2. 近期准备更换数据同步方式，改为Flink来基于BinLog同步