MySQL 自增列解析（Auto

MySQL数据库为列提供了一种自增属性，当列被定义为自增时。Insert语句对该列即使不提供值，MySQL也会自动为该列生成递增的唯一标识，因此这个特性广泛用于主键的自动生成。

目录

• 一、自增列的用法
• • 1.1 基本用法
  • 1.2 自增列特性
  • 1.3 通过last_insert_id()获取自增值
  • 二、自增计数器
  • 三、自增值生成模式
  • • 3.1 传统模式
    • 3.2 连续模式
    • 3.3 交错模式
    • 四、调整自增偏移
    • 五、监控自增值的使用比例

      一、自增列的用法

      自增列具有自动生成序列值，整型，单调递增这些特点，非常适合作为索引组织表的主键，新插入的数据会附加在已有的数据后面，不会出现页分裂现象，且整型的主键查找效率非常高。

      1.1 基本用法

      在创建表时，只需在某个整型列（tinyint,smallint, mediumint, int, bigint）上指定auto_increment，即可打开自增属性。

      一张表中只能指定一个自增列，且必须建立索引，示例中 id列没有指定为索引列，建表报错（must be defined as a key）：

      create table t2(
      id int auto_increment,
      name varchar(32));
      

      

      指定自增列为主键，创建成功：

      create table t (
      id int primary key auto_increment,
      name varchar(32));
      

      

      插入数据时，即使insert语句未包含自增列，MySQL也会自动为该列生成值：

      insert into t(name) values('Vincent'); -- 未指定id列
      select * from t;
      

      

      如果指定了0或null，同样也可以生成自增值：

      insert into t values (0, 'Victor'), (null, 'Grace');  -- 指定0和null
      select * from t;
      

      

      注意：由于0会触发自增，如果ID列本来保存的数据就包含0，那么在数据导出和导入过程中，数字0可能会因此触发自增而被修改，导致数据不一致。 这种情况可以打开sql_mode参数中的no_auto_value_on_zero选项（可以在会话和全局修改），打开该选项后，只有null可以触发自增，0不再触发。

      set sql_mode=concat(@@session.sql_mode, ',no_auto_value_on_zero');
      insert into t values(0, 'Tom');
      select * from t;
      

      

      1.2 自增列特性

      自增列除了让其自动生成值之外，也可以显式赋值，使用中注意以下几点：

      • 显式赋值可能导致大量值被浪费
      • 事务回滚不会回滚已使用的自增值
      • truncate table清除数据的同时也会让自增值初始化
      • alter table … auto_increment=1 可以让自增值恢复到最大可用值，消除间隙（并不会设定成1）

        自增列可以显式赋值，但如果指定的值超过目前auto_increment的最大值，则MySQL会从你指定的值之后开始继续递增，即使前面有可用的值也不再使用，示例中显式指定id列为1000，那么下一条数据会从1001开始自增。

        insert into t values(1000, 'Jerry');
        insert into t values(null, 'Spike');
        

        

        事务中如果使用了自增值，即使回滚，自增值也不会恢复，示例中的事务消耗了2个自增值(1002, 1003)，然后事务回滚了，但是下一条insert语句自增值是从1004开始的：

        begin;
        insert into t values(null, 'Spike'); 
        insert into t values(null, 'Spike');  
        rollback;  -- 事务回滚
        insert into t values(null, 'Tyke'); -- 自增值不会回滚
        select * from t;
        

        

        示例删除了id为1000及以上的数据后，使用alter table … auto_increment=1使自增值恢复到当前数据的最大值：

        delete from t where id>=1000;
        alter table t auto_increment=1;
        insert into t values(null, 'Jerry');
        select * from t;
        

        

        1.3 通过last_insert_id()获取自增值

        MySQL提供了函数last_insert_id()，用于获取上一个成功执行的insert语句所生成的第一个自增值：

        truncate table t;
        insert into t values(null,'Vincent');
        select last_insert_id();
        

        

        单一insert语句如果插入多行，获取的是语句中第一个产生的自增值，而不是最后一个，下面insert语句插入了2条记录，但last_insert_id()返回的是2而不是3（虽然表中id增长到3）：

        insert into t values(null,'Victor'),(null,'Grace');
        select last_insert_id();
        select * from t;
        

        

        如果在事务中手动回滚，last_insert_id()的值也是不会回滚的，其代表的是曾经成功插入的自增值，而不判断事务是否最终提交（有一定误导性，不能用作判断实际插入的值）：

        begin;
        insert into t values(null,'Vincent');
        insert into t values(null,'Vincent');  -- 成功插入，last_insert_id()为5
        rollback;  -- 事务回滚
        select last_insert_id();  -- last_insert_id()依然是5，但数据实际未插入
        

        

        last_insert_id(expr)还有个可选的参数，如果提供参数expr，则会返回该值，并将expr记录为下一个last_insert_id()的返回值：

        select last_insert_id(100);
        select last_insert_id();
        

        

        二、自增计数器

        在MySQL8.0之前，对于auto_increment的值会在内存中维护一个计数器（不保存在磁盘上），在服务器启动时会对每张表执行类似select max(auto_column) from t for update;语句获取当前表中的最大自增值，用于初始化这个计数器。

        MySQL8.0以后，这个计数器的值会在每次变更时写入重做日志和数据字典（保存到磁盘上）。服务器重启时直接读取数据字典即可，不必再通过查询表初始化。

        三、自增值生成模式

        上面的都是单线程下自增值的生成示例，但在并发时，多个事务可能会同时向表中插入数据，事务之间存在争用。MySQL为并发场景下自增值的生成提供了3种不同的模式。3种模式由innodb_autoinc_lock_mode控制（只读变量，修改需要重启），对应的值分别为0, 1, 2：

        • 0, 传统模式（Traditional Lock Mode）
        • 1, 连续模式（Consecutive Lock Mode）
        • 2, 交错模式（Interleaved Lock Mode）

          在解释3种模式的区别前，先了解一下insert语句的分类，insert语句可以分为以下3类：

          • 简单插入（Simple Inserts），如单记录insert，或者多记录insert，在解析SQL时就可以确定要加载的记录数（即要生成自增值数量）
          • 批量插入（Bulk Inserts），如insert … select, load data等，在解析SQL时不确定需要加载的记录数
          • 混合插入（Mixed-Mode Inserts），在多记录简单插入中，为自增列显式指定了部分值，如 insert into … values (null,‘a’), (5, ‘b’), (null, ‘c’)

            3.1 传统模式

            在传统模式下（innodb_autoinc_lock_mode=0），所有类型的insert都会使用表级X锁，并且持有到insert语句结束，这意味着同一时间只有1条insert语句可以执行，但可以保证单条insert语句产生的自增值是连续的。

            在基于语句的主从复制（Statement-Based Replication）模式下insert语句在主从可以生成相同的值。传统模式只是为了向前兼容，现在已经不会使用了。

            3.2 连续模式

            连续模式（innodb_autoinc_lock_mode=1）是对传统模式的优化，对于批量插入这种不确定需要需要多少自增值的insert，会和传统模式一样，使用表级锁直至insert语句执行完成。

            而对于可以事先确定插入记录数的简单插入，MySQL会用mutex（闩，更轻量级的锁）仅在预先分配自增值时锁定，在insert语句执行完成前就已经释放了。连续模式也可以保证基于语句的复制主从可以生成相同的自增值，但性能比传统模式更好。

            对于混合插入类型（多行简单插入中，部分行显式指定自增值，部分行未指定），连续模式下会预先生成比要插入行更多的自增值，然后以连续方式分配给需要自增的行，多余的值就丢弃了。

            MySQL8.0以前的版本默认为连续模式。

            3.3 交错模式

            交错模式下（innodb_autoinc_lock_mode=2），不使用表锁，任何并发insert都可以同时执行，这意味着多条insert语句生成的自增值是可能是交错的，单条insert语句无法保证生成连续的自增值，但这种模式并发性能是最好的。

            因为缺乏了表锁控制，多条insert并发插入，在主从执行时无法保证自增值完全相同，此模式对基于语句的复制（应该没人用了吧？）是不安全的，建议配合基于行的复制（Row-Based Replication）使用（MySQL8.0默认）。

            MySQL8.0默认为交错模式。

            四、调整自增偏移

            自增列的默认初始值为1，步长为1。但在多主复制、组复制这类可以多点写入的环境，可能会产生冲突。

            为了保证不出现冲突，可以设置auto_increment_offset和auto_increment_increment来修改自增的初始值和步长，使各个写入点产生的自增值不重叠（可以在会话和全局级别修改）。

            例如现在有一个双主环境，可以在一台主机上配置初始值为1（默认），步长为2，这样生成的自增值都是单数：

            set auto_increment_increment=2;
            

            

            而在另一台主机上配置初始值为2，步长为2，生成自增值都是双数，这样可以避免并发写入时发生冲突：

            set auto_increment_offset=2;
            set auto_increment_increment=2;
            

            

            五、监控自增值的使用比例

            由于数据类型最大值的限制，自增并不是没有上限的。当到达上限时数据无法继续插入，导致业务中断，因此DBA需要监控自增值的使用情况，在达到上限之前及时采取扩容措施。

            示例：这里又新建2张表，设置自增列类型为tinyint（8位有符号整数，范围为-128~127），然后将自增值分别设置为64和127

            create table t2(id tinyint primary key auto_increment);
            create table t3(id tinyint primary key auto_increment);
            alter table t2 auto_increment=64;
            alter table t3 auto_increment=127;
            

            

            使用下面的SQL即可查询test数据库下所有表的自增列使用比例（可根据情况调整，去除t.table_schema='test’可以查询所有库）：

            select
            t.table_schema,
            t.table_name,
            t.auto_increment,
            c.column_type,
            concat(round((t.auto_increment /
            (case data_type
            when 'tinyint'then if(column_type like '%unsigned', 255, 127)
            when 'smallint' then if(column_type like '%unsigned', 65535, 32767)
            when 'mediumint' then if(column_type like '%unsigned', 16777215, 8388607)
            when 'int'then if(column_type like '%unsigned', 4294967295, 2147483647)
            when 'bigint' then if(column_type like '%unsigned',18446744073709551615,9223372036854775807)
            end))*100,2),'%') used_percentage
            from information_schema.tables t
            join information_schema.columns c on t.table_schema = c.table_schema and t.table_name = c.table_name
            where t.auto_increment is not null and c.extra='auto_increment' and t.table_schema='test';
            

            

            从结果可以看到t3表的自增列已经使用100%，再插入新的数据就会报错了。

            insert into t3 values(null);