「笔记」MySQL 实战 45 讲 - 实践篇（六）

不连续的自增主键

• 由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑

• 自增值保存在哪儿

  • 表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。
  • 不同的引擎对于自增值的保存策略不同
    • MyISAM 引擎的自增值保存在数据文件中
    • InnoDB 引擎的自增值，其实是保存在了内存里（MySQL 8.0 版本后才有自增值持久化的能力
      • 在 MySQL 5.7 及之前的版本
        • 自增值保存在内存里，并没有持久化，每次重启后主动将当前 max(id) + 1作为自增值
        • MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值（作关联外键时需当心
      • 在 MySQL 8.0 版本
        • 自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

• 自增值修改机制

  • 如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下

    • 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
    • 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值（插入值 X，自增值是Y
      • 如果 X<Y，那么这个表的自增值不变；
      • 如果 X≥Y，就需要把当前自增值修改为新的自增值；

  • 自增值生成算法

    • 从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值
      • auto_increment_offset：自增的初始值（默认值是1
      • auto_increment_increment：步长（默认值是1
    • 在一些场景下，使用的就不全是默认值，双 M 主备结构要求双写时，步长设置为2
      • 让一个库的自增 id 都是奇数，另一个库都是偶数，避免两个库生成主键发生冲突

  • 自增值的修改时机

    • 模拟唯一键冲突时流程来演示自增值的修改时机

      

      • 这个表的自增值改成 3，是在真正执行插入数据的操作之前
      • 碰到唯一键冲突时，并未自增值再改回去
      • 唯一键冲突是导致自增主键 id 不连续的第一种原因

    • 事务回滚也会产生类似的现象，这就是第二种原因

    • 自增值不回退主要原因是为了提高性能（若要实现回退，将极大降低系统并发能力

  • 自增锁的优化

    • 自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请
    • 在 MySQL 5.0 版本的时候，自增锁的范围是语句级别（影响并发度
      • 果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放
    • MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1
      • 这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略
      • 这个参数的值被设置为 1 时
        • 普通 insert 语句，自增锁在申请之后就马上释放
        • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才释放
      • 这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁
    • 在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能考虑
      • 建议设置为：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row
      • 既能提升并发性，又不会出现数据一致性问题
      • 批量插入数据包含的语句类型是 insert … select、replace … select 和 load data 语句
    • 对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略
      • 语句执行过程中，第一次申请自增 id，会分配 1 个;
      • 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
      • 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
      • 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。
    • 批量申请自增 id 的策略也正是出现自增 id 不连续的第三种原因

insert 锁情况

• insert … select 语句

  • 栗子（可