在上一小节,我们提到,主键顺序插入的性能是要高于乱序插入的。 这一小节,就来介绍一下具体的 原因,然后再分析一下主键又该如何设计。
数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表 (index organized table IOT)。
在innoDB中索引分两类,聚集索引和二级索引,聚集索引下挂的是行数据,而二级索引挂的是主键。而默认主键索引就是聚集索引。所以我们表中的数据是按照主键进行顺序存储的
逻辑存储结构
最外面是表空间Tablespace,里面存储的是一个个Segment段,段中存储的是Extent区,而一个区的大小是固定的,是1m。区当中存储的是一个个Page页,页里存储的是Row行,行中存储的就是字段。
在InnoDB引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认16K,一个区最多包含64个页。 那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行row在该页存储不小,将会存储 到下一个页中,页与页之间会通过指针连接。
页分裂
页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大,会行溢出,而如果只存储一个其实就变成了链表),根据主键排列。
主键顺序插入
从磁盘中申请页, 主键顺序插入
第一个页没有满,继续往第一页插入
当第一个也写满之后,再写入第二个页,页与页之间会通过双向指针连接
当第二页写满了,再往第三页写入
主键乱序插入
加入1#,2#页都已经写满了,存放了如图所示的数据
此时再插入id为50的记录,我们来看看会发生什么现象?会再次开启一个页,写入新的页中吗?
不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在47之后。但是47所在的1#页,已经写满了,存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。然后会将1#页后一半的数据,移动到3#页,然后在3#页,插入50。
移动数据,并插入id为50的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1#的下一个 页,应该是3#, 3#的下一个页是2#。 所以,此时,需要重新设置链表指针。
上述的这种现象,称之为 "页分裂",是比较耗费性能的操作。
页合并
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间 变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
这个里面所发生的合并页的这个现象,就称之为 "页合并"。
MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
主键设计原则
- 降低主键的长度可以减少存储空间的使用,同时也可以提高查询效率。因为主键是用于唯一标识表中的每一行数据的,所以如果主键的长度过长,查询效率就会受到影响。
- 顺序插入是指按照数据的插入顺序逐行插入,而不是随机插入。使用AUTO_INCREMENT自增主键可以保证每一条数据的插入顺序是连续的,这有利于提高数据的可读性和可维护性。
- UUID是一种随机生成的字符串,可以用来作为主键。但是,由于UUID的长度较长,会占用较多的存储空间,而且UUID的生成方式并不稳定,容易受到随机因素的影响,因此不建议使用UUID作为主键。身份证号等自然主键虽然长度较短,但是它们包含了很多个人信息,如果被不法分子获取到,可能会造成个人信息泄露的风险。
- 避免对主键进行修改是因为主键在表中是唯一的,如果修改了主键,会导致表中的数据不一致,影响系统的稳定性和可靠性。如果必须要修改主键,可以先将该行的数据备份到其他表中,然后删除原表中的数据,最后再重新插入新的数据。