mysql主键下一个值_INNODB自增主键的一些问题 vs mysql获得自增字段下一个值

2023-10-07 13:30
文章标签 mysql 问题 vs 主键 innodb 自增字 database 获得 自增

本文主要是介绍mysql主键下一个值_INNODB自增主键的一些问题 vs mysql获得自增字段下一个值，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

今天发现 批量插入下，自增主键不连续了。。。。。。。

InnoDB AUTO_INCREMENT Lock Modes

This section describes the behavior of AUTO_INCREMENT lock modes used to generate auto-increment values, and how each lock mode affects replication. Auto-increment lock modes are configured at startup using the innodb_autoinc_lock_mode configuration

来自

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

背景：

自增长是一个很常见的数据属性，在MySQL中大家都很愿意让自增长属性的字段当一个主键。特别是InnoDB，因为InnoDB的聚集索引的特性，使用自增长属性的字段当主键性能更好，这里要说明下自增主键需要注意的几个事项。

问题一：表锁

在MySQL5.1.22之前，InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞。

在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

插入类型说明：

INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等

Simple inserts：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。

Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.

Mixed-mode inserts:指其中一部分是自增长的，有一部分是确定的。

0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking。

1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。

2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。

在mysql5.1.22之前，mysql的INSERT-LIKE语句会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住，直到整个语句结束(而不是事务结束)。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时，LOAD DATA等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的insert-like,update等语句。推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间。

解决：

通过参数innodb_autoinc_lock_mode =1/2解决，并用simple inserts 模式插入。

问题二：自增主键不连续

5.1.22后 默认：innodb_autoinc_lock_mode = 1

直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。

48304ba5e6f9fe08f3fa1abda7d326ab.png

root@localhost : test 04:23:28>show variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 1 |

+--------------------------+-------+

1 row in set (0.00 sec)

root@localhost : test 04:23:31>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;

Query OK, 0 rows affected (0.16 sec)

root@localhost : test 04:23:35>insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;

Query OK, 10 rows affected (0.00 sec)

Records: 10 Duplicates: 0 Warnings: 0

root@localhost : test 04:23:39>show create table tmp_auto_inc\G;

*************************** 1. row ***************************

Table: tmp_auto_inc

Create Table: CREATE TABLE `tmp_auto_inc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`talkid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

48304ba5e6f9fe08f3fa1abda7d326ab.png

插入10条记录，但表的AUTO_INCREMENT=16，再插入一条的时候，表的自增id已经是不连续了。

原因：

参数innodb_autoinc_lock_mode = 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。

所以会发现：插入只有1行时，你看不到这个现象，并不预申请。而当有N>1行时，则需要。多申请的数目为N-1，因此执行后的自增值为：1+N+(N-1)。测试中为10行，则：1+10+9 =20，和 16不一致？原因是：当插入8行的时候，表的AUTO_INCREMENT已经是16了，所以插入10行时，id已经在第8行时预留了，所以直接使用，自增值仍为16。所以当插入8行的时候，多申请了7个id，即：9，10，11，12，13，14，15。按照例子中的方法插入8~15行，表的AUTO_INCREMENT始终是16

验证：

插入16行：猜测 预申请的id：1+16+(16-1)= 32，即：AUTO_INCREMENT=32

48304ba5e6f9fe08f3fa1abda7d326ab.png

root@localhost : test 04:55:45>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;

Query OK, 0 rows affected (0.17 sec)

root@localhost : test 04:55:48>insert into tmp_auto_inc(talkid) select talkId from sns_talk_dialog limit 16;

Query OK, 16 rows affected (0.00 sec)

Records: 16 Duplicates: 0 Warnings: 0

root@localhost : test 04:55:50>show create table tmp_auto_inc\G;

*************************** 1. row ***************************

Table: tmp_auto_inc

Create Table: CREATE TABLE `tmp_auto_inc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`talkid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

48304ba5e6f9fe08f3fa1abda7d326ab.png

和猜测的一样，自增id到了32。所以当插入16行的时候，多申请了17,18,19...,31 。

所以导致ID不连续的原因是因为innodb_autoinc_lock_mode = 1时，会多申请id。好处是：一次性分配足够的auto_increment id，只会将整个分配的过程锁住。

5.1.22前 默认：innodb_autoinc_lock_mode = 0

48304ba5e6f9fe08f3fa1abda7d326ab.png

root@localhost : test 04:25:12>show variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 0 |

+--------------------------+-------+

1 row in set (0.00 sec)

root@localhost : test 04:25:15>create table tmp_auto_inc(id int auto_increment primary key,talkid int)engine = innodb default charset gbk;

Query OK, 0 rows affected (0.17 sec)

root@localhost : test 04:25:17>insert into tmp_auto_inc(talkid) select talkId from talk_dialog limit 10;

Query OK, 10 rows affected (0.00 sec)

Records: 10 Duplicates: 0 Warnings: 0

root@localhost : test 04:25:21>show create table tmp_auto_inc\G;

*************************** 1. row ***************************

Table: tmp_auto_inc

Create Table: CREATE TABLE `tmp_auto_inc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`talkid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

48304ba5e6f9fe08f3fa1abda7d326ab.png

插入10条记录，但表的AUTO_INCREMENT=11，再插入一条的时候，表的自增id还是连续的。

innodb_autoinc_lock_mode =2和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个，而不会锁表，只会锁住分配id的过程，和1的区别在于，不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时存在问题

解决：

尽量让主键ID没有业务意义，或则使用simple inserts模式插入。

结论：

当innodb_autoinc_lock_mode为0时候， 自增id都会连续，但是会出现表锁的情况，解决该问题可以把innodb_autoinc_lock_mode 设置为1，甚至是2。会提高性能，但是会在一定的条件下导致自增id不连续。

总结：

通过上面2个问题的说明，自增主键会产生表锁，从而引发问题；自增主键有业务意义，不连续的主键导致主从主键不一致到出现问题。对于simple inserts 的插入类型，上面的问题都不会出现。对于Bulk inserts的插入类型，会出现上述的问题。

更多信息：

~~~~~~~~~~~~~~~ 万物之中,希望至美 ~~~~~~~~~~~~~~~

目录

初次研究：

表：

2382453bdb89027d33d556f37cd3d07f.png

sql:

show table status from carsale_db LIKE 'tb_car'

结果：

c110e50423c45bc4875735aa2f71e7ce.png

想办法取得这其中的值....

在Internet上找到这个资料：

MySQL中可以使用 show table status 查看表的状态，但是不能像select 语句选出结果那样做结果过滤。

有没有办法像select语句那样过滤呢，答案是有的，就是从information_schema库的tables表中查询。

如下是模仿show table status 的SQL:

48304ba5e6f9fe08f3fa1abda7d326ab.png

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,

Data_length,Max_data_length,Index_length,Data_free,Auto_increment,

Create_time,Update_time,Check_time,table_collation,Checksum,

Create_options,table_comment

FROM information_schema.tables

WHERE Table_Schema='MyDataBaseName';

48304ba5e6f9fe08f3fa1abda7d326ab.png

注意替换MyDataBaseName的名称为自己的库名称，这样就可以方便在Where部分添加各种条件过滤了。

于是，复制->粘贴，修改所需字段：

48304ba5e6f9fe08f3fa1abda7d326ab.png

SELECT table_name,Auto_increment,Engine,Version,Row_format,table_rows,Avg_row_length,

Data_length,Max_data_length,Index_length,Data_free,

Create_time,Update_time,Check_time,table_collation,Checksum,

Create_options,table_comment

FROM information_schema.`TABLES`

WHERE Table_Schema='carsale_db'

48304ba5e6f9fe08f3fa1abda7d326ab.png

结果：

9e24302c4156604ff4321320360715cb.png

感觉表太多了，修改：

48304ba5e6f9fe08f3fa1abda7d326ab.png

SELECT table_name,Auto_increment,Engine,Version,Row_format,table_rows,Avg_row_length,

Data_length,Max_data_length,Index_length,Data_free,

Create_time,Update_time,Check_time,table_collation,Checksum,

Create_options,table_comment

FROM information_schema.`TABLES`

WHERE Table_Schema='carsale_db'

AND table_name = 'tb_car'

48304ba5e6f9fe08f3fa1abda7d326ab.png

结果：

09a8f8bf470f8d0b82e43334db9d35df.png

排除不需要的字段：

SELECT Auto_increment

FROM information_schema.`TABLES`

WHERE Table_Schema='carsale_db'

AND table_name = 'tb_car'

结果：

4fd11e3a1b130ccb22bd07f4af7d87d9.png

OK，这就是我想要的结果.....

这篇关于mysql主键下一个值_INNODB自增主键的一些问题 vs mysql获得自增字段下一个值的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/158063。

相关文章

数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...
如何解决idea的Module:‘:app‘platform‘android-32‘not found.问题

如何解决idea的Module:‘:app‘platform‘android-32‘not found.问题

《如何解决idea的Module:‘:app‘platform‘android-32‘notfound.问题》：本文主要介绍如何解决idea的Module:‘:app‘platform‘andr... 目录idea的Module:‘:app‘pwww.chinasem.cnlatform‘android-32
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
kali linux 无法登录root的问题及解决方法

kali linux 无法登录root的问题及解决方法

《kalilinux无法登录root的问题及解决方法》：本文主要介绍kalilinux无法登录root的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,... 目录kali linux 无法登录root1、问题描述1.1、本地登录root1.2、ssh远程登录root2、
阅读更多...
SQL server配置管理器找不到如何打开它

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一：桌面图标进入方法二：运行窗口进入方法三：查找文件路径方法四：检查 SQL Server 安
阅读更多...
MySQL 中的 LIMIT 语句及基本用法

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,：本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(
阅读更多...
SpringBoot应用中出现的Full GC问题的场景与解决

SpringBoot应用中出现的Full GC问题的场景与解决

《SpringBoot应用中出现的FullGC问题的场景与解决》这篇文章主要为大家详细介绍了SpringBoot应用中出现的FullGC问题的场景与解决方法,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录Full GC的原理与触发条件原理触发条件对Spring Boot应用的影响示例代码优化建议结论F
阅读更多...
MySQL 分区与分库分表策略应用小结

MySQL 分区与分库分表策略应用小结

《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如... 目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念
阅读更多...
MySQL高级查询之JOIN、子查询、窗口函数实际案例

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》：本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN（连接查询）1.1 内连接（INNER JOIN）1.2 左连接（LEFT JOI
阅读更多...
MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2