`
丁林.tb
  • 浏览: 797161 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

MySQL源码学习:innodb_autoinc_lock_mode 下自增id不连续的原因

阅读更多

一、问题复现

文件/tmp/data.sql两列,每列一个数字1;

 

输入

CREATE TABLE `t` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

load data infile '/tmp/data.sql' into table t(k);

show create table t;

 

结果:

CREATE TABLE `t` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

 

二、原因分析

我们知道在5.1.22在之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode。这个值为0时,每次申请自增主键时需要锁表。

这个参数的默认值是1,设为此值时,每次会“预申请”多余的id(handler.cc: compute_next_insert_id),而insert执行完成后,会特别将这些预留的id空出,动作就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)

 

三、简单计算预留

注意这个预留的策略是“不够时多申请几个”, 实际执行中是分步申请。至于申请几个,是由当时“已经插入了几条数据N”决定的。当auto_increment_offset=1时,预申请的个数是 N-1。

所以会发现,当data.sql中只有一行时,你看不到这个现象,并不预申请。

而当有两行时(如文章开头的例子),则需要。多申请的数目为1,因此执行后的自增值为4 (1+2+1)

data.sql中有三行呢?由于执行第三行的id已经在执行第二行时预留了,所以直接使用,结果的自增值仍为4

后续的就类推了,可自行分析下。

实际insert

自增id增加值

23

3

4567

7

815

15

 

1
1
分享到:
评论
2 楼 xtha21 2016-10-27  
预申请ID的个数为:大于批量插入记录数的最近一个2的指数值再-1个,如
> 记录数是5,那最近一个2的指数值是8,那预申请ID的个数为8-1=7;
> 记录数是8,那最近一个2的指数值是16,那预申请ID的个数为16-1=15
1 楼 whitesock 2011-12-29  
预分配的策略是首先分配1,如果在SQL的执行过程中用尽,那么乘2;如果再用尽,那么再乘2; 也就是1,2,4,8...

其它那些普通的场景,我补充一下: http://whitesock.iteye.com/blog/1329857

相关推荐

    关于MySQL innodb_autoinc_lock_mode介绍

    MySQL的InnoDB存储引擎在处理带有`auto_increment`列的表时,会受到`innodb_autoinc_lock_mode`参数的影响。这个参数决定了插入数据时如何管理自动递增锁,从而在性能和数据安全性(主从数据一致性)之间取得平衡。 ...

    mysql-innodb

    lock_mode`控制自增列的锁定模式,`innodb_buffer_pool_size`设定缓冲池大小,以及`innodb_flush_log_at_trx_commit`决定事务日志何时写入磁盘等。这些变量可以根据系统需求进行优化。 7. **其他特性**: InnoDB还...

    mysql ocp 实战整理题库,高命中率.docx

    - **选项A和D**:InnoDB在某些情况下会使用表级锁来保护自动递增更新,但在较新的版本中可以通过调整`innodb_autoinc_lock_mode`来减少这种锁定的影响。 - **选项B**:InnoDB确实使用多种类型的锁机制,包括行级锁...

    MySQL分表自增ID问题的解决方法

    MySQL的`innodb_autoinc_lock_mode`参数可以调整自增锁的行为,设置为0(传统的表锁)、1(连续插入时尝试批量锁)或2(交错模式,减少锁竞争)。MyISAM引擎始终使用传统表锁,而InnoDB引擎在不同模式下有不同的...

    AliSQL数据库开源功能特性.pdf

    5. **Persistent AUTO_INCREMENT**:解决历史数据归档可能导致的自增ID冲突问题,通过`innodb_autoinc_persistent`和`innodb_autoinc_persistent_interval`参数实现自增ID的持久化,确保在归档后的新数据中避免冲突...

    Mycat+MySQL Galera读写分离验证安装手册

    innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 innodb_flush_log_at_trx_commit=0 innodb_doublewrite=0 innodb_file_per_table=1 binlog_format=ROW log-bin=mysql-bin server-id=101 ...

    AliSQL数据库开源功能特性.pptx

    另外,AliSQL的Persistent AUTO_INCREMENT特性解决了历史数据归档可能导致的自增ID冲突问题,通过innodb_autoinc_persistent和innodb_autoinc_persistent_interval参数控制,实现了自增ID的持久化。 在复制和安全性...

    MySQL中由load data语句引起死锁的解决案例

    `innodb_autoinc_lock_mode`参数控制着自增锁的行为。默认情况下,该参数为1,但在某些操作如`LOAD DATA INFILE`时,即使设置为1,实际操作也会按照模式0进行,即整个语句执行期间保持持有自增锁,直到语句结束。 ...

    Mysql auto_increment 重新计数(让id从1开始)

    2. **自增ID的递增策略**:默认情况下,每次插入新行时,`auto_increment`会递增1,但可以通过设置`innodb_autoinc_lock_mode`来改变这一行为。 3. **多表共享自增ID**:在一个数据库中,可以通过设置多个表的`auto...

    Mariadb集群+haproxy+keepalived +性能测试

    innodb_autoinc_lock_mode调整自增锁的行为等。这些参数对集群的性能和稳定性有着直接的影响。 最后,文中还提到了MariaDB集群中的一些优化参数设置,比如key_buffer_size、max_allowed_packet、sort_buffer_size等...

    合并到 XtraDB 存储引擎集群

    而`innodb_autoinc_lock_mode`设置为2,可以提高性能。 其他关于Galera的设置还包括集群通信选项以及全局缓存大小设置等。`wsrep_provider_options`中的`gcache.size`用于配置全局缓存的大小,这对于集群的性能至关...

    pt-osc在线重建表导致死锁的分析及对应的优化方案1

    - innodb_autoinc_lock_mode设置为1,这可能影响自增ID的锁定方式。 3. **死锁日志分析**: - 日志显示死锁发生在设置auto-inc lock时,即在插入新记录时。 - 两个事务分别在等待对方释放锁,形成循环等待,导致...

    MySQL高效导入多个.sql文件方法详解

    8. 如果有自增列,可将`innodb_autoinc_lock_mode`设置为2,以优化插入性能。 实施这些优化时,需要注意将相关配置更改写入my.cnf文件并重启MySQL服务,或者在SQL文件开头和结尾添加相应设置,如关闭和开启检查的...

    超详细的MariaDB Galera cluster 多主复制配置!

    innodb_autoinc_lock_mode=2 ``` 六、停止 SELinux 安全限制 我们需要在所有节点上停止 SELinux 服务。首先,我们可以暂时停止 SELinux 服务: ``` setenforce 0 ``` 然后,我们可以永久停止 SELinux 服务: ``` vi...

Global site tag (gtag.js) - Google Analytics