回答：MySQL InnoDB 引擎底层数据结构是 B+ 树，所谓的索引其实就是一棵 B+ 树，一个表有多少个索引就会有多少颗 B+ 树，MySQL 中的数据都是按顺序保存在 B+ 树叶子节点上的。

MySQL 在底层又是以数据页为单位来存储数据的，一个数据页大小默认为 16k，当然你也可以自定义大小，也就是说如果一个数据页存满了，MySQL 就会去申请一个新的数据页来存储数据。

• 如果主键为自增 id 的话，MySQL 在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。
• 如果主键是非自增 id，为了确保索引有序，MySQL 就需要将每次插入的数据都放到合适的位置上。

当往一个快满或已满的数据页中插入数据时，新插入的数据会将数据页写满，MySQL 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。

自增id 可以保证每次插入时B+索引是从右边扩展的，可以避免B+树频繁合并和分裂（对比使用UUID而言）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

◆ 一、InnoDB中的B+树

先理解InnoDB中的B+树，如图所示。

InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+树）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁地移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

◆ 二、尽量使用更小的主键

在满足业务需求的情况下，尽量使用占空间更小的主键。

• 主键占用空间越大，每个页存储的主键个数越少，B+树的深度会变长，导致IO次数会变多。
• 普通索引的叶子节点上保存的是主键 id 的值，如果主键 id 占空间较大的话，那将会成倍增加 MySQL 空间占用大小。

◆ 三、什么时候不需用自增主键？

（1）数据量小

数据量很小，小到全表扫描效率比扫描索引树要高时，不适合建立索引，就更没有自增主键的必要了。

数据量千级，索引树大小不大，对性能和空间影响都不会很大。

（2）KV场景

在全表只有一个唯一索引（Key-Value场景），且读多写少的前提下，应尽量避免查询时回表（也就是搜索两颗索引树），这种情况可以考虑用业务字段做主键。

◆ 四、主键自增带来的劣势是什么？

在高并发的场景下，自增主键也有一些弊端。

在InnoDB中按主键顺序插入可能会造成明显的争用。主键上界会成为”热点”，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制：如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode（自增锁模式）来控制自增长的模式，该参数的默认值为1或者2。

show variables like 'innodb_autoinc_lock_mode';

参数innodb_autoinc_lock_mode总共有三个有效值可供设定，即0、1、2。从MySQL 8.0 开始默认是 2。

三种模式简要说明:

0：traditonal （每次都会产生表锁）

1：consecutive （会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）

2：interleaved （不会锁表，来一个处理一个，并发最高）

来源：

https://www.toutiao.com/a7028920796470952485/?log_from=f2ca5b7baf4e4_1637026606133

“IT大咖说”欢迎广大技术人员投稿，投稿邮箱：aliang@itdks.com