答案是优化MySQL自增主键效率需调整innodb_autoinc_lock_mode为2以减少锁竞争，选用UNSIGNED BIGINT作主键类型，并避免使用UUID。具体而言，将innodb_autoinc_lock_mode设为2可提升并发插入性能，尽管可能产生ID空洞；选择UNSIGNED BIGINT确保ID范围充足且兼容未来扩展；因UUID非递增会导致索引碎片和随机I/O，不推荐作为聚簇索引主键。

MySQL自增主键的效率优化，核心在于减少并发写入时的锁竞争，并确保数据写入和索引维护的开销最小化。这不只是调几个参数那么简单，它往往牵涉到对数据库工作原理的理解，甚至是对业务场景的预判。

解决方案

要优化MySQL自增主键的效率，我们主要从以下几个方面入手：调整

innodb_autoinc_lock_mode

参数，合理选择主键数据类型，并考虑应用层的写入策略。很多时候，我们发现问题并非出在主键本身，而是其背后的锁机制在拖后腿。

为什么MySQL自增主键会成为性能瓶颈？

我们都知道自增主键用起来方便，但它在并发量大的时候，确实会成为一个瓶颈。这背后的原因其实挺直接的：当多个事务同时尝试向同一个表插入数据时，为了保证自增主键的唯一性和递增性，MySQL内部需要一个机制来分配下一个ID。这个机制，就是所谓的

AUTO-INC

锁。

想象一下，如果每次分配ID都需要一个全局锁，那在高并发场景下，所有插入操作都得排队等这个锁，这不就成了串行化了吗？即便MySQL在后续版本中优化了锁机制，但在某些模式下，这种锁竞争依然存在。尤其是当你混合了简单的

INSERT

和复杂的

INSERT ... SELECT

语句时，锁的持有时间可能会变长，进一步加剧了竞争。

此外，自增主键通常也是聚簇索引（对于InnoDB表），这意味着新插入的数据会按照主键顺序写入磁盘。如果主键是严格递增的，那么新的数据总是被追加到索引的末尾。这虽然有利于顺序写入，减少随机I/O，但在高并发下，所有写入都集中在索引的“热点”区域，可能导致页分裂，或者缓存失效，进而影响写入性能。

如何配置

innodb_autoinc_lock_mode

来提升性能？

innodb_autoinc_lock_mode

这个参数是优化自增主键效率的关键所在。它有三个值，每个值都有其适用场景和优缺点。我个人在实践中，更多地倾向于根据业务对ID连续性的要求来选择。

• innodb_autoinc_lock_mode = 0

  (传统模式): 这是最保守的模式。每次有插入操作时，都会获取一个

  AUTO-INC

  表级锁，直到语句执行完成。这意味着只要有插入，其他插入就得等着。在事务回滚时，已分配的ID不会被回收，可能导致ID序列出现大的空洞。这种模式下，ID的连续性最好，但并发性能最差。我通常只在对ID连续性有极高要求，且并发写入不多的场景下考虑它。

• innodb_autoinc_lock_mode = 1

  (连续模式): 这是MySQL的默认值。对于简单的

  INSERT

  语句（可以预知要插入多少行），它会提前分配好一批ID，然后释放

  AUTO-INC

  锁，允许其他事务继续插入。但对于不确定行数的

  INSERT ... SELECT

  或

  REPLACE

  语句，它依然会使用表级锁。这种模式在大多数情况下表现不错，平衡了性能和ID连续性。

• innodb_autoinc_lock_mode = 2

  (交错模式/无锁模式): 这是并发性能最好的模式。它完全避免了

  AUTO-INC

  表级锁，转而使用轻量级的互斥锁来分配ID。每个事务在需要ID时，都能快速获取并分配，大大减少了等待时间。但代价是，如果一个事务回滚，或者有多个事务并行插入，ID序列可能会出现较大的空洞，甚至在语句级别上，ID也可能不是连续的。例如，一个

  INSERT ... SELECT

  语句，其分配的ID可能与另一个并发的

  INSERT

  语句的ID交错。对于大部分互联网应用，只要ID唯一就行，不强求连续性，那么模式2无疑是首选。

  

  百度文心百中

  百度大模型语义搜索体验中心

  23

  查看详情

在我的经验里，如果业务对ID的连续性没有硬性要求，将

innodb_autoinc_lock_mode

设置为2，通常能带来显著的写入性能提升。

你可以通过以下SQL语句来查看和修改这个参数：

-- 查看当前值 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';  -- 修改为交错模式 (需要重启MySQL服务才能生效，或者在my.cnf/my.ini中配置) -- 在my.cnf中添加或修改： -- [mysqld] -- innodb_autoinc_lock_mode = 2

选择哪种数据类型作为自增主键最合适？

选择自增主键的数据类型，这可不是小事。它直接关系到你能存多少数据，以及索引的效率。

• INT

  vs.

  BIGINT

  :

  • INT

    （有符号）最大值大约21亿，无符号

    UNSIGNED INT

    最大值大约42亿。对于很多中小规模的应用，

    UNSIGNED INT

    可能就够用了。

  • BIGINT

    （有符号）最大值大约9 x 10^18，无符号

    UNSIGNED BIGINT

    最大值大约1.8 x 10^19。这个范围几乎可以满足所有你能想象到的场景。

  • 我的建议是，如果没有明确的理由选择

    INT

    ，那就直接用

    BIGINT

    。 提前规划，避免后期因为ID不够用而进行痛苦的数据迁移。虽然

    BIGINT

    会占用更多的存储空间（8字节 vs 4字节），但在现代硬件条件下，这点存储差异通常不是主要矛盾，而避免溢出的风险和带来的重构成本，是远超这点存储开销的。

• UNSIGNED

  的考量:

  • 使用

    UNSIGNED

    类型可以扩大一倍的存储范围，因为它们不存储负数。对于自增主键这种只可能递增的数值，

    UNSIGNED

    是自然的选择。

• 数据类型对索引的影响:

  • 主键的字节大小直接影响索引的大小。索引越大，存储开销越大，加载到内存中的索引页就越少，可能导致更多的磁盘I/O。所以，在满足业务需求的前提下，选择尽可能小的数据类型是明智的。但正如我前面说的，

    BIGINT

    的额外开销在大多数情况下是可接受的。

• UUID作为主键的思考:

  • 有时你会听到有人提议用UUID作为主键。UUID确实能解决分布式环境下的ID生成问题，避免中心化ID服务的依赖。但对于MySQL的InnoDB表，UUID作为主键通常不是一个好主主意，因为它不是顺序的。非顺序的UUID会导致新的数据插入到索引的随机位置，造成大量的随机I/O，频繁的页分裂，严重影响写入性能和查询效率。如果非要用UUID，可以考虑将其作为唯一索引，而使用一个单独的自增ID作为聚簇主键。但这就引入了额外的存储和索引开销。所以，对于自增主键优化，UUID通常是反模式。

总的来说，选择一个

UNSIGNED BIGINT

作为自增主键，在大多数高并发场景下，既能保证足够的ID范围，又能兼顾存储和索引效率，是一个稳妥且前瞻性强的选择。