本文主要是介绍【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.主键设计原则
- 2.数据组织方式
- 【1】主键顺序插入
- 【2】页分裂(主键乱序插入)
- 【3】页合并(主键乱序删除)
一.主键优化
1.主键设计原则
- 满足业务需求的情况下, 尽量降低主键的长度。 尽量不要使用UUID做主键或者是其他自然主键,如身份证号
- 对于一个表。聚集索引有一个,但二级索引有很多,二级索引到叶子节点中挂的就是主键。主键比较长,二级索引比较多,会占用许多空间,搜索时耗费更多磁盘io
- 业务操作时,避免对主键的修改。
- 插入数据时,尽量选择 顺序插入 ,选择使用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,触发页合并
- 寻找最靠近的页(前或后),将两个页合并以优化空间使用
这篇关于【MySQL】主键优化原理篇——【数据组织方式&主键顺序插入&主键乱序插入&页分裂&页分裂】的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!