【MySQL】主键优化原理篇——【数据组织方式＆主键顺序插入＆主键乱序插入＆页分裂＆页分裂】

前言

大家好吖，欢迎来到 YY 滴MySQL系列 ，热烈欢迎！ 本章主要内容面向接触过C++ Linux的老铁
主要内容含：

欢迎订阅 YY滴C++专栏！更多干货持续更新！以下是传送门！

• YY的《C++》专栏
• YY的《C++11》专栏
• YY的《Linux》专栏
• YY的《数据结构》专栏
• YY的《C语言基础》专栏
• YY的《初学者易错点》专栏
• YY的《小小知识点》专栏
• YY的《单片机期末速过》专栏
• YY的《C++期末速过》专栏
• YY的《单片机》专栏
• YY的《STM32》专栏
• YY的《数据库》专栏
• YY的《数据库原理》专栏

一.主键优化

1.主键设计原则

1. 满足业务需求的情况下， 尽量降低主键的长度。 尽量不要使用UUID做主键或者是其他自然主键，如身份证号

• 对于一个表。聚集索引有一个，但二级索引有很多，二级索引到叶子节点中挂的就是主键。主键比较长，二级索引比较多，会占用许多空间，搜索时耗费更多磁盘io
  

2. 业务操作时，避免对主键的修改。
3. 插入数据时，尽量选择 顺序插入 ，选择使用AUTOINCREMENT自增主

• 顺序插入可以减少 页分裂 （可以了解下按下面的数据组织方式）

2.数据组织方式

【1】主键顺序插入

• 在大多数数据库系统中，如表数据是使用B树（或其变种如B+树）这样的数据结构进行索引的。
  
• 主键顺序插入
• 第一个页满了，插入第二个页，一页16k，以此类推
  

【2】页分裂（主键乱序插入）

下面演示页分裂：

• 此时两页都满了， 我们要插入id为50的数据 ，他会开辟一个新的数据页，但并不是直接插入到第三个数据页当中

• 找到第一个 数据页百分之50的位置 ，大于百分之50的部分移动的新开辟的数据页当中
• 之后插入id为50的数据

• 插入要进入的50数据时，此时就应该改动链表指针

【3】页合并（主键乱序删除）

• 当删除一行记录时，实际上记录并 没有被物理删除 ，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
• 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后) 看看是否可以将两个页合并以优化空间使用。
• MERGE THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

演示：

• 有三个页，我们删除中间页的数据，依次删除主键为16，15，14，13的数据，此时达到了MERGE_THRESHOLD，触发页合并

• 寻找最靠近的页(前或后)，将两个页合并以优化空间使用