高效访问数据的关键：解析MySQL主键自增长的运作机制！

高效访问数据的关键：解析MySQL主键自增长的运作机制！"/>

高效访问数据的关键：解析MySQL主键自增长的运作机制！

文章目录

• • 🍊 主键自增长的概念
  • 🍊 主键自增长的数据类型
  • 🍊 主键自增长的步长
  • 🍊 主键自增长的性能优化
  • • 🎉 为什么需要主键自增长的性能优化?
    • 🎉 主键自增长的性能优化方案
    • • 📝 1. 调整主键自增长的步长
      • 📝 2. 使用多个主键自增长列
    • 🎉 如何选择主键自增长的性能优化方案？
  • 🍊 自增长在数据库中的应用和机制
  • 🍊 AUTO-INC Locking 的性能问题
  • 🍊 InnoDB 存储引擎中的优化
  • 🍊 自增长的坏处

📕我是廖志伟，一名Java开发工程师、Java领域优质创作者、CSDN博客专家、51CTO专家博主、阿里云专家博主、清华大学出版社签约作者、产品软文创造者、技术文章评审老师、问卷调查设计师、个人社区创始人、开源项目贡献者。🌎跑过十五公里、徒步爬过衡山、🔥有过三个月减肥20斤的经历、是个喜欢躺平的狠人。

📘拥有多年一线研发和团队管理经验，研究过主流框架的底层源码(Spring、SpringBoot、Spring MVC、SpringCould、Mybatis、Dubbo、Zookeeper)，消息中间件底层架构原理(RabbitMQ、RockerMQ、Kafka)、Redis缓存、MySQL关系型数据库、 ElasticSearch全文搜索、MongoDB非关系型数据库、Apache ShardingSphere分库分表读写分离、设计模式、领域驱动DDD、Kubernetes容器编排等。🎥有从0到1的高并发项目经验，利用弹性伸缩、负载均衡、报警任务、自启动脚本，最高压测过200台机器，有着丰富的项目调优经验。

📙经过多年在CSDN创作上千篇文章的经验积累，我已经拥有了不错的写作技巧。同时，我还与清华大学出版社签下了四本书籍的合约，并将陆续在明年出版。这些书籍包括了基础篇、进阶篇、架构篇的📌《Java项目实战—深入理解大型互联网企业通用技术》📌，以及📚《解密程序员的思维密码–沟通、演讲、思考的实践》📚。具体出版计划会根据实际情况进行调整，希望各位读者朋友能够多多支持！

希望各位读者大大多多支持用心写文章的博主，现在时代变了，信息爆炸，酒香也怕巷子深，博主真的需要大家的帮助才能在这片海洋中继续发光发热，所以，赶紧动动你的小手，点波关注❤️，点波赞👍，点波收藏⭐，甚至点波评论✍️，都是对博主最好的支持和鼓励！

• 💂 博客主页： 我是廖志伟
• 👉开源项目：java_wxid
• 🌥 哔哩哔哩：我是廖志伟
• 🎏个人社区：幕后大佬
• 🔖个人微信号： SeniorRD

💡在这个美好的时刻，本人不再啰嗦废话，现在毫不拖延地进入文章所要讨论的主题。接下来，我将为大家呈现正文内容。

🍊 主键自增长的概念

在数据库中，我们经常会用到主键作为特定列的唯一标识符。但是，如果每次插入数据时都手动指定主键值，那将是一项繁琐的工作。这时，主键自增长技术就派上用场了。

主键自增长指的是，当我们向数据库中插入一条新记录时，主键值会自动递增生成一个新的可用唯一标识符。这样，我们就无需手动指定主键值，大大简化了数据插入的操作流程。

举个例子，我们有一张学生表，其中包含学生id、姓名、年龄等字段，id是主键。如果我们每次插入新数据时都需要手动为id分配一个唯一的值，那将是十分麻烦的。但是，如果我们使用主键自增长技术，只需要在表定义时指定id为自增长属性，然后每次插入新数据时，id就会自动递增生成一个唯一的值，这样就方便了许多。

🍊 主键自增长的数据类型

主键自增长的数据类型通常使用整型数据类型，比如INT、BIGINT等。这些数据类型在数据库中的应用非常广泛，因为它们具有以下优点：

1. 整型数据类型占用的存储空间比字符串等其他数据类型小，因此可以节约存储空间。

2. 整型数据类型在数据库中的存储和操作速度都非常快，可以提高数据库的响应速度和性能。

3. 整型数据类型的取值范围比较广，可以满足大部分数据存储需求。

比如，在一个商品表中，我们可以设置一个自增长的主键列，用来唯一标识每一条记录。例如：

CREATE TABLE `product` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL COMMENT '商品名称',`price` decimal(10,2) NOT NULL COMMENT '商品价格',`inventory` int(11) NOT NULL COMMENT '商品库存',`create_time` datetime NOT NULL COMMENT '创建时间',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

在上面的SQL语句中，我们为商品表创建了一个自增长的bigint类型主键列id。每次插入一条新的商品记录时，id列的值就会自动加1，确保每个商品都有唯一的id。

当然，主键自增长不仅仅可以用于整型数据类型，也可以用于其他数据类型，例如日期时间类型、浮点数类型等。只要数据库支持自增长功能，就可以为任意一个列设置自增长属性，以保证其唯一性。

🍊 主键自增长的步长

在数据库中，每张表都需要有一个主键，它是用来在表中唯一标识某一行记录的。比如说，我们有一张学生信息表，其中包含学号、姓名、年龄等字段，我们可以将学号设为主键，这样每个学生的信息就可以通过学号来唯一确定了。数据库在插入新记录时，会自动为主键字段赋一个递增的值。比如说，在我们刚才说的学生信息表中，我们将学号设为主键自增长，那么当我们插入第一个学生时，学号会被赋值为1；插入第二个学生时，学号会被赋值为2；以此类推。

但是，有些时候我们不希望每次主键自增长的步长都是1，这时候我们就可以手动设置主键自增长的步长。比如说，我们现在有一张订单表，其中包含订单号、下单时间、订单金额等字段，我们可以将订单号设为主键自增长。但是，每条订单记录的下单时间都非常接近，如果我们每次插入新记录时都将订单号自增1，那么可能会出现订单号非常接近的情况，这样后期查询订单时可能会很麻烦。因此，我们可以将主键自增长的步长设置为100，这样每次插入新的订单记录时，订单号就会自动递增100，这样就能保证每个订单号之间都有足够的间隔。

那么如何设置主键自增长的步长呢？我们可以在创建表时，在主键字段后加上“auto_increment=步长”的语句。比如说，我们要创建一张学生信息表，并将学号设为主键自增长，步长设置为2，可以这样写：

CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT=2,name VARCHAR(20),age INT
);

这样，我们每次插入新的学生记录时，学号就会自动递增2。

总结一下，主键自增长的步长是用来控制主键字段每次递增的数值大小的。通过设置主键自增长的步长，我们可以避免主键重复的问题，也能够让主键之间有足够的间隔，方便后期的查询。在创建表时，我们可以通过在主键字段后加上“auto_increment=步长”的语句来设置主键自增长的步长。

🍊 主键自增长的性能优化

🎉 为什么需要主键自增长的性能优化?

首先，我们需要了解主键自增长碎片化的概念。主键自增长，在插入数据时会自动递增，但是在删除数据时，并不会自动回收被删除数据的主键值。这就会导致主键值的“碎片化”，即主键值不连续，而是存在很多的空隙。这些空隙直接影响到数据库查询性能。

举个例子：假设有一个一万行的表，其中有5000行数据被删除，那么主键值就会变成1,2,3,6,7,8……而不是1,2,3,4,5,6……如果进行查询，就会造成大量的IO操作和浪费。

而另一方面，插入数据的频繁操作也会影响性能，因为每次插入都需要重新计算主键值，可能会增加锁的等待时间和CPU负载，导致响应时间延迟。

那么，如何解决这些问题呢？就要考虑主键自增长的性能优化了。

🎉 主键自增长的性能优化方案

📝 1. 调整主键自增长的步长

调整主键自增长的步长是一种常用的性能优化方案。步长是每次自增的数量，如果设为1，就是每插入一条数据就加1，也就是我们常说的默认值。但是，这样会使主键值不连续，造成碎片化。

我们可以尝试将步长设为较大的值，比如设为1000，这样就可以使主键值在一定范围内连续，减少碎片化问题。例如，我们可以这样创建表：

CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL COMMENT '姓名',`age` int(11) NOT NULL COMMENT '年龄',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

在这个表中，我们可以使用如下命令调整步长：

ALTER TABLE `test` AUTO_INCREMENT = 1000;

这样的话，主键值就会从1000开始自增，而不是从1开始。

需要注意的是，步长也不能设置太大，因为如果步长设置过大，可能就会浪费很多主键值，而且在多表关联查询时也会变得麻烦。

📝 2. 使用多个主键自增长列

如果调整步长并不能解决问题，我们可以考虑使用多个主键自增长列。通过使用多个自增长列，可以分摊插入负载，提升性能。

举个例子：我们可以这样创建表：

CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`id2` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL COMMENT '姓名',`age` int(11) NOT NULL COMMENT '年龄',PRIMARY KEY (`id`, `id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在这个表中，我们定义了两个自增长列id和id2，它们的值都会自动递增。主键由这两个列组成，可以保证每条数据的唯一性。

这种方法可以使插入操作更加分散，避免因为插入导致的等待时间和CPU负载过高。如果你的表中有很多字段，可以将自增长列和其他字段隔离开来。

🎉 如何选择主键自增长的性能优化方案？

主键自增长的性能优化方案可以根据实际情况进行选择。

如果你的表中有大量的删除操作，那么可以考虑调整主键自增长的步长，减少主键值的碎片化，提高查询效率。

如果你的表中插入操作非常频繁，可以考虑使用多个自增长列，分摊负载，减少等待时间和CPU负载。

当然，对于不同的表，需要根据实际情况进行选择。如果你的表数据量较小，或者主键值的连续性对查询的性能没有影响，那么不需要考虑这些优化方案。

🍊 自增长在数据库中的应用和机制

自增长是指数据库表中的一个属性，常用于设置数据库表的主键。在数据库中，每个含有自增长值的表都有一个自增长计数器，在插入操作时这个计数器会被初始化，并依据计数器值加1给自增长列赋值。

当插入一条新记录时，MySQL会检查该表的自增长计数器，将其值加1，并将其作为新记录的主键值。MySQL会在内部自动处理这个过程，而不需用户手动指定主键值。

需要注意的是，自增长计数器的值是在表级别上维护的，因此如果在不同的MySQL实例中操作同一个表，可能会导致计数器出现不同步的情况。此外，在插入记录时如果指定了一个已经存在的主键值，MySQL会抛出重复键错误。

在物理层面，MySQL为自增长计数器分配了一块内存来保存其当前值。这块内存通常较小，但足够保存整个表的自增长值范围。如果自增长值超出了这个范围，MySQL会抛出溢出错误。为了避免这种情况，用户可以通过调整自增长值的初始值和步长来扩大自增长值的范围。

自增长常用的方式是 AUTO-INC Locking，即采用一种特殊的表锁机制，在完成对自增长值插入的SQL语句后立即释放锁。

🍊 AUTO-INC Locking 的性能问题

虽然 AUTO-INC Locking 从一定程度上提高了并发插入的效率，但是存在一些性能问题。首先，对于有自增长值的列的并发插入性能较差，因为事务必须等待前一个插入的完成。其次，对于 INSERT…SELECT 的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

🍊 InnoDB 存储引擎中的优化

在InnoDB存储引擎中，MySQL主键自增长的运作机制是通过在表上创建一个自动增量列来实现的。当新的数据行插入到表中时，MySQL会查找该自动增量列的当前值，并将其增加1来产生一个新的唯一值。然后，MySQL将该值赋给新插入的数据行的自动增量列。

为了优化MySQL主键自增长的性能，InnoDB存储引擎会使用一个专门的优化层面来处理自动增量列。这个优化层面称为“自增长计数器”，它会缓存自动增量列的当前值，并在需要时增加它。通过使用这个缓存，InnoDB存储引擎可以避免频繁地更新自动增量列的值，从而提高性能。

此外，InnoDB存储引擎还支持通过使用多个自动增量列来优化MySQL主键自增长的性能。这种方式称为“交替自增长”，它会创建多个自动增量列，并在每次插入数据时交替使用它们。这种方法可以减少自增长计数器的争用，并提高整个系统的并发性能。

从 MySQL5.1.22 版本开始，InnoDB 存储引擎中提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的性能。并且从该版本开始，InnoDB 存储引擎提供了一个参数 innodb_autoinc_lock_mode 来控制自增长的模式，该参数的默认值为 1。innodb_autoinc_lock_mode 有三个选项：

• 0：是 MySQL5.1.22 版本之前自增长的实现方式，通过表锁的 AUTO-INC Locking 方式实现的。
• 1：是默认值，对于简单的插入，会用互斥量去对内存中的计数器进行累加操作。对于批量插入，还是通过表锁的 AUTO-INC Locking 方式实现。在这种配置下，如果不考虑回滚操作，对于自增值的列，它的增长还是连续的，而且 statement-based 方式的 replication 还是很好的工作。但是如果使用了 AUTO-INC Locking 方式去产生自增长的值，这个时候再进行简单插入操作，就需要等待 AUTO-INC Locking 释放。
• 2：在这个模式下，对于所有的插入的语句，它自增长值的产生都是通过互斥量，不是通过 AUTO-INC Locking 方式，这是性能最高的方式，但是如果是并发插入，在每次插入的时候，自增长的值就不是连续的，而且基于 statement-based replication 会出现问题，所以在这个模式下，任何时候都要用 row-base replication，这样才可以保证最大的并发性能和 replication 主从数据的一致。

🍊 自增长的坏处

使用自增长的坏处主要有四个方面。第一，强依赖数据库，不同数据库语法和实现不同，数据库迁移、多数据库版本支持和分表分库时需要处理，会比较麻烦，而且当数据库异常时整个系统会不可用，属于致命问题。第二，单点故障。在单个数据库或读写分离或一主多从的情况下，只有一个主库可以生成，存在单点故障的风险。第三，数据一致性问题。配置主从复制可以尽可能地增加可用性，但是数据一致性在特殊情况下难以保证，主从切换时的不一致可能会导致重复发号。第四，难于扩展。在性能达不到要求的情况下，比较难于扩展，ID 发号性能瓶颈限制在单台 MySQL 的读写性能。

🔔如果您需要转载或者搬运这篇文章的话，非常欢迎您私信我哦~

希望各位读者大大多多支持用心写文章的博主，现在时代变了，信息爆炸，酒香也怕巷子深，博主真的需要大家的帮助才能在这片海洋中继续发光发热，所以，赶紧动动你的小手，点波关注❤️，点波赞👍，点波收藏⭐，甚至点波评论✍️，都是对博主最好的支持和鼓励！

• 💂 博客主页： 我是廖志伟
• 👉开源项目：java_wxid
• 🌥 哔哩哔哩：我是廖志伟
• 🎏个人社区：幕后大佬
• 🔖个人微信号： SeniorRD

📥博主的人生感悟和目标

• 🍋程序开发这条路不能停，停下来容易被淘汰掉，吃不了自律的苦，就要受平庸的罪，持续的能力才能带来持续的自信。我本身是一个很普通程序员，放在人堆里，除了与生俱来的盛世美颜，就剩180的大高个了，就是我这样的一个人，默默写博文也有好多年了。
• 📺有句老话说的好，牛逼之前都是傻逼式的坚持，希望自己可以通过大量的作品、时间的积累、个人魅力、运气、时机，可以打造属于自己的技术影响力。
• 💥内心起伏不定，我时而激动，时而沉思。我希望自己能成为一个综合性人才，具备技术、业务和管理方面的精湛技能。我想成为产品架构路线的总设计师，团队的指挥者，技术团队的中流砥柱，企业战略和资本规划的实战专家。
• 🎉这个目标的实现需要不懈的努力和持续的成长，但我必须努力追求。因为我知道，只有成为这样的人才，我才能在职业生涯中不断前进并为企业的发展带来真正的价值。在这个不断变化的时代，我必须随时准备好迎接挑战，不断学习和探索新的领域，才能不断地向前推进。我坚信，只要我不断努力，我一定会达到自己的目标。