为什么 Mysql 推荐使用整形自增的主键而不使用 UUID
这个问题可以进行拆解来回答
一、为什么使用整形
Mysql Innodb 存储引擎是基于聚集索引来实现的,如果存在主键的情况下,主键索引就是聚集索引,Mysql 主键索引维护了主键和完整的表数据
以下表为例
基于这张表建立的主键索引如下
索引查找的时候都是从 B+ 树的根节点开始,先将根节点所在的 page 页从磁盘上加载到内存中,然后在内存中不断的比对,最终定位到存储数据的叶子节点上,从叶子节点上取出对应的行数据即可
之所以不使用字符串类型的 UUID 作为主键,主要有以下几个方面的考虑
1、在各个节点上进行比较的时候,是整形的 1 < 2 比较的效率高还是 32 位的 UUID 字符串 ad345898312411ea8d520050569433eb < db197298312411ea8d520050569433eb 比较效率高呢?显然是整形的比较效率更高,因为字符串的比较是需要换算成 ASCII 码一位一位的去比较的,当第一个字符串相同就去比较第二个字符串,一直到能比较出大小为止,这样的比较过程所耗费的时间明显是高于整形比较的,CPU 每次最多可以比较 8 个字节的整数值,但对于字符串,必须一个字符一个字符比较过去.有测试说明,在查询比较时,使用整数的速度比使用字符串的速度快数倍到数十倍之间
2、整形的 int 存储是 4 个字节,bigInt 是 8 个字节,假设我们采用的是 UTF-8 字符集, UUID 它所占的字节数是 2 + 3 * 32 = 110 字节(前面 2 个字节用来存储字符串长度,后面每个字符 3 个字节).相较而言,一个整数只有 4 个字节,相差 25 倍.数据库采用 B+ 树索引,其中主键索引的叶子节点指向数据行,而二级索引的叶子节点存储着主键,之后再通过主键索引回表查数据.也就是说,有多少个二级索引,主键就需要被存储几次,因此,索引的空间需求就极速扩张了,还有就是使用整形的主键可以在一个 page 中存储更多的主键,跨 page 遍历的次数就会更少
二、为什么使用自增
插入数据时可能会出现页分裂;删除、修改数据时可能会有页合并,页分裂、页合并都是及其耗费性能的
Mysql 存储的基本单位为 page(大小默认为 16 KB),一个页可以为空、可以只填充一半、也可以填充 100%,每个 page 包含 2-N 条数据,如果某一行数据过大,会自行溢出
1、主键顺序插入的过程
首先从磁盘上申请 page 页,往里面插入数据
如果第一个 page 页没满,那么就继续往该 page 页插入数据,直到该 page 页插满为止
第一个 page 页插满之后,如果还需要插入数据,则重新从磁盘上申请第二个 page 页,往第二个 page 页继续插入数据,page 页之间通过双向指针进行连接
2、乱序插入的过程(页分裂)
前面的过程也是类似的,假设 page1 和 page2 已经插满了
这个时候我要插入 id = 22 的行记录,那么 Mysql 会从磁盘上申请第三个 page 来存储 id = 22 的记录行吗?
答案是不会的,因为 Mysql 主键索引是聚集索引,它默认的是根据 id 从小到大进行排序,那么 id = 22 的数据会插入在 id = 19 和 id = 26 的数据行之间,可是 id 为 19 和 26 所在的数据页都存满了啊,那该如何继续存储呢?
Mysql 会先向磁盘申请第三个 page 页,然后把第一个 page 页一半的数据(1/2 page 存储量的数据)先移动到第三个 page 页上,然后在第三个 page 页上插入 id = 22 的数据,最后移动指针将 page1 指向 page3,page3 再指向原来的 page2,该过程就被称为 页分裂
由于 UUID 是乱序的,使用 UUID 插入的时候就有可能发生页分裂的情况
三、补充知识
既然说到了页分裂,就顺便提一下页合并吧,页合并与主键自增和非自增并没有关系,它是在删除行或者通过 UPDATE 操作缩短行时才有可能发生的情况
当 DELETE 或 UPDATE 缩短了行长度时,索引页的 "page-full" 百分比低于 MERGE_THRESHOLD 值,InnoDB 会尝试将索引页与相邻索引页合并.
MERGE_THRESHOLD 的最小值为 1,最大值为 50,默认值就是 50,当索引页面的 "page-full" 百分比低于 50％ 时,Innodb 会尝试将索引页与相邻页合并.如果两个页面都接近 50％ 已满,则在合并页面后很快就会发生页面拆分, 如果频繁发生此合并拆分行为,则可能会对性能产生负面影响.为避免频繁的合并拆分,可以降低 MERGE_THRESHOLD 值,以便 InnoDB 以较低的 "page-full" 百分比尝试页面合并,以较低页面满百分比合并页面会在索引页面中留出更多空间,并有助于减少合并拆分行为.可以为表或单个索引定义索引页的 MERGE_THRESHOLD,为单个索引定义的MERGE_THRESHOLD 值优先于为表定义的 MERGE_THRESHOLD 值.如果未定义,则 MERGE_THRESHOLD 值默认为 50.
下面就以删除来演示页合并现象
假设原始数据如下
现在我们要删除 id = 60 的数据行
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用,删除了 id= 60 数据之后整个数据的存储并不会发生任何变化
当继续同时删除 id 为 41、44、51、54 的数据之后
页中删除的记录达到 MERGE_THRESHOLD(默认为页存储总量的50%),Innodb 会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用
