当前位置:首页 > 数码 > MySQL-自增主键不一定是连续的-深入了解自增机制及其影响 (mysql-bin文件可以删除吗)

MySQL-自增主键不一定是连续的-深入了解自增机制及其影响 (mysql-bin文件可以删除吗)

admin8个月前 (04-24)数码31

概述

在 MySQL 中,自增主键是一种特殊的数据类型,它提供了一种在插入新记录时自动生成唯一标识符的方法。自增主键通常用于充当数据库表的唯一标识符,确保每条记录都有一个不同的 ID。 自增主键的实现并非像想象中那么简单。本文将深入探究自增主键的属性、修改机制以及影响其连续性的因素。

自增值的属性特征

1. 自增主键值存储位置 在 MySQL 5.7 及之前版本中,自增值存储在内存中。每次重新启动后,第一次打开表时,MySQL 会查找自增值的最大值 (max(id)),然后将 max(id)+1 作为该表的当前自增值。 在 MySQL 8.0 及更高版本中,自增值的更改会记录在 redo log 中。在重新启动时,MySQL 会依靠 redo log 恢复重新启动之前的值。 2. 自增主键值的修改机制 在表中定义主键 id 为自增值后,在插入新行时,自增值的修改机制如下: 1. 如果插入数据时 id 字段指定为 0、null 或未指定值,那么 MySQL 会将该表的当前自增值填写到自增字段中。 2. 如果插入数据时 id 字段指定了具体的值,MySQL 会直接使用语句中指定的值。 根据要插入的值和当前自增值的大小关系,自增值的更改结果也不同。假设要插入的值为 X,当前自增值为 Y: 1. 如果 X < Y,则该表的自增值保持不变。 2. 如果 X ≥ Y,则 MySQL 需要将当前自增值修改为新的自增值。

新增语句对自增主键的影响

让我们观察一下以下 SQL 语句是如何影响自增主键的: sql INSERT INTO t VALUES (NULL, 1, 1); 流程图如下: 流程步骤: 1. AUTO_INCREMENT = 1(表示下次插入数据时,如果需要自动生成自增值,将生成 id = 1。) 2. INSERT INTO t VALUES (NULL, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的值为 (0, 1, 1)) 3. getAUTO_INCREMENT = 1(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 1) 4. AUTO_INCREMENT = 2 INSERT INTO t VALUES (1, 1, 1)(将传入行的值更改为 (1, 1, 1),并将自增值更改为 2) 5. INSERT (1,1,1) 执行插入操作,至此流程结束。 从流程中可以看出,MySQL 先将自增值 +1,然后再执行新增语句。需要注意的是,此过程不是原子操作。如果 SQL 语句执行失败,则自增值可能无法连续。

自增主键值不连续的情况

1. 唯一主键冲突 当执行以下 SQL 语句时: sql INSERT INTO t VALUES (NULL, 1, 1); 第一次插入可能会成功,因为自增值的修改机制如下: 如果插入数据时 id 字段指定为 0、null 或未指定值,那么 MySQL 会将该表的当前自增值填写到自增字段中。 但是,当再次执行以下 SQL 语句时,可能会出现错误: sql INSERT INTO t VALUES (2, 1, 1); 因为表 t 中的 c 字段是唯一索引,因此会出现 Duplicatekeyerror 错误,导致新增失败。 错误流程步骤: 1. AUTO_INCREMENT = 2(表示下次插入数据时,如果需要自动生成自增值,将生成 id = 2。) 2. INSERT INTO t VALUES (NULL, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的值为 (0, 1, 1)) 3. getAUTO_INCREMENT = 2(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 2) 4. AUTO_INCREMENT = 3 INSERT INTO t VALUES (2, 1, 1)(将传入行的值更改为 (2, 1, 1),并将自增值更改为 3) 5. INSERT (2,1,1) 执行插入操作,由于已经存在 c = 1 的记录,所以报错 Duplicatekeyerror,语句返回。 可以看到,即使插入操作失败,自增值仍然会被修改,从而导致自增主键不连续。

结论

在 MySQL 中使用自增主键时,了解其属性、修改机制以及影响其连续性的因素非常重要。通过理解这些机制,可以避免自增主键不连续,并确保数据库表的完整性和准确性。 为了确保自增主键的连续性,建议采用以下最佳实践: 仅在需要时使用自增主键。 避免在自增主键字段上使用显式值。 定期运行 OPTIMIZE TABLE 语句来回收未使用的自增值。 了解 redo log 和崩溃恢复如何影响自增主键。

mysql的设置主键自增

自增主键不一定是连续的

列的自增属性,一般用来设置整数列根据一定步长逐步增长的值,类似于其他数据库的序列。 不过这里的“序列”是基于特定一张表的。 关于自增属性的相关特性如下:1. 控制自增属性性能的变量:innodb_autoinc_lock_modeinnodb_autoinc_lock_mode=0代表传统模式,也就是说,在对有自增属性的字段插入记录时,会持续持有一个表级别的自增锁,直到语句执行结束为止。 比如说下面两条语句,SQL 1 在执行期间,一直持有对表 f1 的表级自增锁,接下来 SQL 2 执行时锁超时。 innodb_autoinc_lock_mode=1代表连续模式,和传统模式差不多,不同的点在于对于简单的插入语句,比如 SQL 2,只在分配新的 ID 过程中持有一个轻量级的互斥锁(线程级别,而不是事务级别),而不是直到语句结束才释放的表锁。 代表交错模式。 这个模式下放弃了自增表锁,产生的值会不连续。 不过这是性能最高的模式,多条插入语句可以并发执行。 MySQL 8.0 默认就是交错模式。 那针对复制安全来说,以上三种模式,0 和 1 对语句级别安全,也就是产生的二进制日志复制到任何其他机器都数据都一致;2 对于语句级别不安全;三种模式对二进制日志格式为行的都安全。 2. 控制自增属性的步长以及偏移量一般用在主主复制架构或者多源复制架构里,主动规避主键冲突。 auto_increment_increment 控制步长auto_increment_offset 控制偏移量3. 对于要立刻获取插入值的需求就是说要获取一张表任何时候的最大值,应该时刻执行以下 SQL 3 ,而不是 SQL 2。 SQL 2 里的函数 last_insert_id() 只获取上一条语句最开始的 ID,只适合简单的 INSERT。 4. 自增列溢出现象自增属性的列如果到了此列数据类型的最大值,会发生值溢出。 比如变更表 f1 的自增属性列为 tinyint。

mysql自增序列过大性能影响

自增列可能会成为一个性能瓶颈。 当innodb_autoinc_lock_mode是0时,一个自增列会在一个插入语句执行期间被一直锁住,这样它就成为一个严重的性。

免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。

标签: MySQL

“MySQL-自增主键不一定是连续的-深入了解自增机制及其影响 (mysql-bin文件可以删除吗)” 的相关文章

实用性极强的-MySQL-查询优化策略 (实用性极强的app)

实用性极强的-MySQL-查询优化策略 (实用性极强的app)

在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员或架构师进行系统调优工作了。其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到...

实战-MySQL-数据库压力测试与性能评估方法-Java (实战篮球鞋排名)

实战-MySQL-数据库压力测试与性能评估方法-Java (实战篮球鞋排名)

压力测试的目的和重要性 压力测试是模拟真实环境中并发用户访问数据库的场景,通过增加负载来测试数据库系统的性能表现。压力测试的目的是发现数据库在高负载下的性能瓶颈、资源利用情况和响应时间等指...

全面指南-如何解决-MySQL-主从延时问题 (全面指导)

全面指南-如何解决-MySQL-主从延时问题 (全面指导)

一、什么是主从延时? 主从延时,是指从数据库从主数据库复制数据时产生的时间差。它会导致从库中的数据与主库不一致。 二、为什么会主从延时? 1. 主从复制原理 MySQL的...

如何在MySQL中成功数据的版本治理和回滚操作 (如何在mysql数据库中添加数据)

如何在MySQL中成功数据的版本治理和回滚操作 (如何在mysql数据库中添加数据)

成功数据的版本治理和回滚操作在中可以经过以下几种模式成功,包含经常使用事务、备份恢复、日志和版本控制工具等。上方将详细引见这些方法。 1.经常使用事务: MySQL允许事务操作,可以经...

MySQL-实现非中断亿级数据处理的秘密 (mysql-bin文件可以删除吗)

MySQL-实现非中断亿级数据处理的秘密 (mysql-bin文件可以删除吗)

MySQL 在海量数据管理方面表现得非常出色,能够存储上亿级别的数据,同时还具有极高的数据可靠性,几乎不会发生数据丢失的情况。这一强大的特性离不开 MySQL 的两大日志系统:binlog 和 r...

揭秘MySQL中Varchar和Int的隐式转换 (揭秘国安部点名的间谍机构)

揭秘MySQL中Varchar和Int的隐式转换 (揭秘国安部点名的间谍机构)

前言 在一次例行测试中,我们遇到了一个奇特的现象。一条查询库存数量的SQL语句,居然返回了0条记录。当我们手工执行SQL时,却查询到了一条记录。调查发现,原因在于MySQL的优化器在判断数据类型...

揭开其背后的神奇世界-深入探寻-索引的底层逻辑-MySQL (揭开其背后的真相)

揭开其背后的神奇世界-深入探寻-索引的底层逻辑-MySQL (揭开其背后的真相)

什么是索引? 索引是数据库系统中一种特殊的数据结构,用于快速查找数据。索引以某种方式引用(指向)数据,允许在索引上实现高级查找算法,从而提高查询速度。 索引的种类 最常见的索引类型有:...

运行系统 (列车自动运行系统)

运行系统 (列车自动运行系统)

作者:徐良,现任中国移动智慧家庭经营中心数据库初级经理,多年数据库运维优化阅历,历任华为、一线互联网公司初级DBA。目前关键担任中移智家基于规模的价值经营场景下数据库稳固性、容灾优化、他乡多活等相...