MySQL 自增键

2022/1/3 19:12:51

编程Tag： mysql Auto Mode 语句 主键 lock autoinc 自增键

本文主要是介绍MySQL 自增键，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

在MySQL数据库表设计中有个属性，数字类型的自增列(auto incremnet)，从设计原初是为了满足递增规则解决计数，或无主键下采用主键(由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑）。

在数据库个需要分清Sequence 和 自增键的区别。像mariadb，oracle 都有这个功能。但mysql 目前暂无提供。自增和Sequence 都是数字类型的，自增是单存的向上累加 无法保证等间隔，序列(Sequence)可以完美的保证连贯性可以为表中的行自动生成序列号，产生一组等间隔的数值。

但自增键也有一些不为关注的点，虽然数字类型，设计初如果可能的话，使用UNSIGNED属性来允许更大的范围。例如，如果使用Tinyint，最大允许的序号是127，对于tinyint 使用UNSIGNED属性，最大值为255。需要了解所有整型类型的范围。

image.png

自增锁和模式：

自增对于数据来说，就是插入的时候的特殊处理，对于插入情况，在自增字段下可分为3种模式：

插入模式说明例子
Simple inserts可以预先确定要插入的行数的语句(在语句最初处理时)，不包含INSERT … ON DUPLICATE KEY UPDATE.INSERT INTO Table1(field1) values(‘test’)
Bulk inserts对于要插入的行数(以及所需的自动递增值的数量)，事先不知道的语句。nnoDB在处理每一行时为AUTO_INCREMENT列一次赋一个新值。比如：INSERT … SELECT, REPLACE … SELECT, LOAD DATAINSERT INTO Table2(field1,field2,…) select value1,value2,… from Table1
Mixed-mode inserts为一些(但不是所有)新行指定自动增加的值。另一种INSERT…ON DUPLICATE KEY UPDATE，在最坏的情况下，实际上是一个INSERT，后面跟着一个UPDATE，其中为AUTO_INCREMENT列分配的值在更新阶段可能使用，也可能不使用。INSERT INTO Table2(field1,field2) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’);

自增锁机制：

InnoDB对自增字段提供了一个可配置的3种锁机制配合插入时保证唯一性和连续性：

  • 传统:“traditional”,
  • 连续:“consecutive”,
  • 交错:“interleaved”

注意：自增锁是在语句级而不是事务级的，一个事务可能包涵多个语句。

当两个Tx1,Tx2事务中那个语句先执行，就先获取自增递归值，跟最后提交commit无关。

1）innodb_autoinc_lock_mode = 0 (“传统”)

  • 传统的锁模式选项提供向后兼容性的，
  • 语句都获得一个特殊的表级AUTO-INC锁，表级锁一直保持到语句结束。
  • 以确保对给定的INSERT语句序列：可预测，可重复，增值连续的。
  • 当多个事务同时执行插入语句时，这些表级锁限制了并发性和可伸缩性。

在这种锁模式下，所有“类insert”语句都获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。这个锁通常持有的最后结束(事务),以确保自动递增值被分配在一个可预见的和可重复的订单对于一个给定的INSERT语句序列,并确保连续自动递增值。

2）innodb_autoinc_lock_mode = 1(“连续”)

插入模式说明
Simple inserts提前已知要插入的行数，避免表级锁AUTO-INC
Bulk inserts使用特殊的AUTO-INC表级锁，并将其保持到语句结束
Mixed-mode insertsInnoDB会分配比要插入的行数更多的自动增量值。但是，所有自动分配的值都是连续生成的。下一个单独的可能是跳过的。

简单地说，这种锁模式显著提高了可伸缩性，同时对于基于语句的复制来说是安全的，并为statement-based复制操作是安全

3）Innodb_autoinc_lock_mode = 2(“交叉”)

没有语句使用表级AUTO-INC锁，多个语句可以同时执行。
自动递增值保证在所有并发执行的“类insert”语句中是唯一的，并且是单调递增的。但是，由于多条语句可以同时生成数字，数字的分配是在语句之间交叉进行的，因此任何给定语句插入的行所生成的值可能不是连续的。

4）不同版本模式不同

在MySQL 8.0中，交错锁模式(innodb_autoinc_lock_mode=2)是默认设置。
在MySQL 8.0前，连续锁模式是默认的(innodb_autoinc_lock_mode=1)。

5）索引必不可少

为了在InnoDB表中使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为某个索引，这样就可以在表上执行等价的SELECT MAX(ai_col)查找来获得最大的列值。索引类型不需要是PRIMARY KEY或UNIQUE，但是为了避免AUTO_INCREMENT列中的重复值，建议使用这些索引类型。普通索引可以指定插入重复值的。

table definition; there can be only one auto column and it must be defined as a key

 

DROP TABLE IF EXISTS  t_user;
CREATE TABLE `t_user` (
  `id` int  unsigned  zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(30)  NOT NULL,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB ;

6）参数

mysql > SHOW VARIABLES WHERE  variable_name LIKE 'auto_incre%' OR variable_name LIKE 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
3 rows in set (0.00 sec)
参数范围说明
innodb_autoinc_lock_mode全局变量0,1,2 三种锁模式
auto_increment_offset动态变量表示自增长字段从那个数开始，取值范围是1…65535
auto_increment_increment动态变量表示自增长字段每次递增的量，其默认值是1，取值范围是1…65535

如这两个变量（auto_increment_offset，auto_increment_increment）的值设置为大于65,535 整数将导致其值被设置为65,535 ，如小于0的将导致其值被设置为 1；

7)复制中自增

  • 基于STATEMENT语句的复制时，副本点没有人为写入的情况下innodb_autoinc_lock_mode设置为0或1，能确保生成的自增是一致的。如使用innodb_autoinc_lock_mode = 2，则不能确保副本上的自动增量值与源上的相同。

  • 如果使用基于ROW或MIXED格式的复制，那么所有自动增量锁模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感(混合格式对任何对基于语句的复制不安全的语句使用基于行的复制)。

mysql> INSERT INTO t_user01(name)  SELECT name  FROM t_user;

#binlog解析：
BEGIN
/*!*/;
# at 309
#211212 17:10:30 server id 129  end_log_pos 385 CRC32 0x387058ca 	Rows_query
# insert into t_user01(name)  select name  from t_user
# at 385
#211212 17:10:30 server id 129  end_log_pos 444 CRC32 0x671a7ed0 	Table_map: `db8`.`t_user01` mapped to number 107
# at 444
#211212 17:10:30 server id 129  end_log_pos 493 CRC32 0xddc7d069 	Write_rows: table id 107 flags: STMT_END_F
### INSERT INTO `db8`.`t_user01`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='A' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### INSERT INTO `db8`.`t_user01`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='C' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 493
#211212 17:10:30 server id 129  end_log_pos 524 CRC32 0xee8d4eb5 	Xid = 12
COMMIT/*!*/;

8)"Lost"自动递增连续性

在所有锁模式(0、1和2)中，如果生成自动增值值的事务回滚，那么这些自动增值值将"丢失"。一旦为自动增量列生成了一个值，无论"类insert"语句是否完成，以及包含的事务是否回滚，都不能回滚该值。这些丢失的值不会被重用。

image.png

9)自增列值重复利用问题

在MySQL 5.7及更早版本中，自动增量计数器存储在内存中，而不是磁盘上。在服务器重启后初始化一个自动增量计数器，InnoDB会在第一次插入包含AUTO_INCREMENT列的表时执行如下语句。



SELECT MAX(ai_col) FROM table_name FOR UPDATE; 


在MySQL 8.0中，这种行为被改变成，当前最大的自动增量计数器值被写入重做日志，每次它改变并保存到每个检查点的数据字典。这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。


在崩溃恢复的服务器重启过程中，InnoDB使用存储在数据字典中的当前最大自动增量值初始化内存中的自动增量计数器，并扫描重做日志中自上次检查点以来写入的自动增量计数器值。如果重做记录的值大于内存中计数器的值，则应用重做记录的值。


注意：如出现redo丢失 或损坏，问题可能还会存在。


10）常见问题


  •  
    TRUNCAT命令会把初始化自增值，从最初的值开始，DELETE则不会
     
  •  
    不管复制集群之间怎样操作，步长是按照之前设置进行增长，除非人为更改。但也可能存在bug情况（https://bugs.mysql.com/bug.php?id=105871）
     
  •  
    补齐宽度值ZEROFILL，用处不大
     


CREATE TABLE `t_user` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENTS=1;


  •  
    自增主键是否适合业务主键
     无主键需求的时候，比如日志数据，为了提高复制，运维效率，需要添加自曾主键。
     有的业务设计依赖于自增主键的连续性,或自增键作为业务主键 是坚决不建议使用的。本身不存在业务意义。如出现bug问题，关联表会存在数据冲突问题。
     
  •  
    当AUTO_INCREMENT整数列的值用完时，后续的INSERT操作将返回一个复制键错误（主键唯一索引下），普通索引会持续的插入最大值。
     
  •  
    日常统计自增字段使用情况：避免自增序列满的情况发生
     


SELECT  infotb.TABLE_SCHEMA  , infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE 
FROM  information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl 
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA   
AND infotb.TABLE_NAME = infocl.TABLE_NAME 
AND infocl.EXTRA='auto_increment';




总结：


首先三种innodb_autoinc_lock_mode模式合理选择:


  • 0：traditonal （每次都会产生表锁）
  • 1：consecutive （会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）
  • 2：interleaved （不会锁表，来一个处理一个，并发最高）
     建议可将innodb_autoinc_lock_mode设置为2，这时可在所有insert情况下表获得最大并发度。


除此之外日常运维中需要关注的问题：


  • 自增用完的情况。
  • 多节点写入中设置不同的步长（逻辑复制，延迟导致冲突）。
  • 自增键选择主键问题。


持续关注看是否能把Sequence结合进来，进一步完善机制。


 



                   


                   

                        这篇关于MySQL 自增键的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
                    

                     

                 
                

                
                    

                

                
 
                
                

                    

                        
相关编程文章

                        更多>
                    

                    
                    
                    
                


            

        


        

             


    

        
    




             

        


    

    
    
        


    

        

            

            

            


        

        

            

                本站官方QQ
                

                    
官方QQ:

                    
23002807

                

            

            
        

        

           
            

                沪ICP备2023033072号-1
                
                  
                 
                
            

            

                      
                
            

        

    







    
    
    


        
        扫一扫关注最新编程教程