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

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

admin4周前 (04-24)数码8

概述

在 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-提升查询性能的秘诀 (优化数据统计工具)

优化数据统计的终极指南-MySQL-提升查询性能的秘诀 (优化数据统计工具)

在业务场景中,我们经常需要统计当前已有的业务数据,例如商品库内商品的数量、每天的用户订单数量等。此时,我们需要使用统计功能来实现。 count()实现方式 对于不同的数据库引擎,co...

主从复制原理简介-MySQL (主从复制原理mysql)

主从复制原理简介-MySQL (主从复制原理mysql)

主从复制(Master-SlaveReplication)是一种数据复制技术,用于在多个数据库主机之间的数据同步。在主从复制架构中,一个主机被设置为主主机(Master),充任数据源,其余主机被设...

核心模块-深入探索数据库引擎-MySQL-揭秘 (核心模块英文)

核心模块-深入探索数据库引擎-MySQL-揭秘 (核心模块英文)

简介 Savepoint 是 MySQL 中的一种机制,允许在事务中创建标记点,以便在事务失败时回滚到该标记点。Savepoint 的原理是将事务的当前状态存储在各种数据结构中,包括服务器层、b...

使用-数据库并自动发送备份文件到指定邮箱-K8s-定期备份-MySQL (使用数据库的命令)

使用-数据库并自动发送备份文件到指定邮箱-K8s-定期备份-MySQL (使用数据库的命令)

简介 本文档描述了一个使用脚本来监控服务器高占用率进程并通过电子邮件发送警报的项目。本文还探讨了使用相同机制备份数据库的可能性。 技术 Python psuti...

EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)

EXPLAIN-代价模型-MySQL-浅析-提早预知索引优化战略-优化查问性能-告别自觉经常使用 (explain)

背景 在中,当咱们为表创立了一个或多个索引后,通常须要在索引定义成功后,依据详细的数据状况口头EXPLN命令,才干观察到数据库实践经常使用哪个索引、能否经常使用索引。这使得咱们在参与新索引之...

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

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

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

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

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

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

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

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

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