MySQL面试题InnoDB引擎为什么推荐使用自增ID作为主键?
回答:MySQL InnoDB 引擎底层数据结构是 B+ 树,所谓的索引其实就是一棵 B+ 树,一个表有多少个索引就会有多少颗 B+ 树,MySQL 中的数据都是按顺序保存在 B+ 树叶子节点上的。
MySQL 在底层又是以数据页为单位来存储数据的,一个数据页大小默认为 16k,当然你也可以自定义大小,也就是说如果一个数据页存满了,MySQL 就会去申请一个新的数据页来存储数据。如果主键为自增 id 的话,MySQL 在写满一个数据页的时候,直接申请另一个新数据页接着写就可以了。如果主键是非自增 id,为了确保索引有序,MySQL 就需要将每次插入的数据都放到合适的位置上。
当往一个快满或已满的数据页中插入数据时,新插入的数据会将数据页写满,MySQL 就需要申请新的数据页,并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂,这个大量移动数据的过程是会严重影响插入效率的。
自增id 可以保证每次插入时B+索引是从右边扩展的,可以避免B+树频繁合并和分裂(对比使用UUID而言)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
如果表记录超过1万行,建议用自增数字当主键,类型用BIGINT UNSIGNED。 下面有详细的解释。
一、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 (不会锁表,来一个处理一个,并发最高)(此处已添加圈子卡片,请到今日头条客户端查看)