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

于 2024-09-01 01:14:44 发布
阅读量218 收藏 3
点赞数 3
分类专栏： YY滴《数据库系列》 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/YYDsis/article/details/141761851
版权

前言

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

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

目录

一.主键优化

1.主键设计原则

  1. 满足业务需求的情况下， 尽量降低主键的长度。 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
  • 对于一个表。聚集索引有一个，但二级索引有很多，二级索引到叶子节点中挂的就是主键。主键比较长，二级索引比较多，会占用许多空间，搜索时耗费更多磁盘io
    在这里插入图片描述
  1. 业务操作时，避免对主键的修改。
  2. 插入数据时，尽量选择 顺序插入 ，选择使用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，触发页合并

-

  • 寻找最靠近的页(前或后)，将两个页合并以优化空间使用
    在这里插入图片描述
YY的秘密代码小屋
关注
  • 3
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL高级-SQL优化-主键优化（页分裂&页合并）
小丁的博客
06-28 424
MySQL高级-SQL优化-主键优化（页分裂&页合并）
MySQL进阶——插入数据、主键优化
霖hero
06-30 860
在上篇文章学习了MySQL进阶——索引使用规则，这篇文章学习MySQL进阶——插入数据、主键优化。
SQL优化——主键优化、页分裂、页合并、主键设计原则
weixin_44860226的博客
07-22 199
这个b+树的数据结构当中，所有的数据都会出现在叶子节点，分叶子节点只起到一个索引数据的作用，分叶子节点的索引和叶子节点的数据最后都是存放在一个结构当中，页（page）当中，下面图中所有的黄色的块都是一个一个的page（页），每个页的大小都是固定的。页是innoDB磁盘管理的最小单元，一个页的大小默认为16k，一个区当中包含64个页。主键乱序的情况下有可能发生页分裂。页之间的链表指针会重新设置。
SQL优化——插入数据、主键优化、order by 优化、group by 优化、limit 优化、count优化、update优化、
wjl990316fddwjl的博客
03-04 2191
在SQL优化中，如何高效插入数据也是比较重要的一点，那么现有的插入数据类型主要分为普通插入和大批量插入。
MySQL—SQL优化：插入数据（insert、大批量插入）、主键优化（数据组织方式、页分裂、页合并、索引设计）、order by 、group by 、limit 、count 、update 优化
放下华子我只抽RuiKe5的博客
05-04 6278
​ 在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。nnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
MySQL主键顺序插入和随机插入对性能的影响
qq_34999565的博客
02-04 2955
本文是针对聚簇索引 首先来看顺序插入的情况。如果主键是顺序的，所以InnoDB会把每插入的记录存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是15/16，流出不封空间用于以后可能产生的修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的的方式插入，主键就会近似于被顺序的记录填满。 再来看随机插入的情况，比如使用了uuid聚簇索引的表插入数据。因为新插入的值是随机，可能表上一个插入的主键值大，也可能小，所以InnoDB无法简单的总是把新的记录插入到索引的最后，也就是
MySQL主键谁与争锋：MySQL为何钟爱自增主键ID+UUID？
Hanko的专栏
12-21 1811
随着数据库应用的不断普及，设计一个高效且可维护的数据库结构变得尤为重要。在MySQL中，选择主键类型是数据库设计中的一个关键决策。本文将深入分析为何在MySQL中主键建议使用自增类型，并探讨这种做法的优缺点。
mysql 自增id主键 和 随机字符串主键 插入性能比较
stevewong的专栏
04-14 4624
理论上，自增id作为主键的数据，由于不需要频繁的节点分裂（页分裂），其性能要比随机字符串作为主键性能要好。下面通过实验看一下对比 自增id的数据表： CREATE TABLE IF NOT EXISTS `auto_inc_pk`( `id` BIGINT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, PRIMARY...
MYSQL——索引概念&&索引结构
阿常的博客
03-31 1070
索引是帮助数据库高效获取数据的排好序的数据结构。
MYSQL开发性能研究之批量插入数据的优化方法
12-16
批量插入数据是一种常见的优化手段，能够显著提升数据插入的效率。本文主要探讨了在MySQL中如何通过批量插入优化性能，对比了几种不同的批量插入方法，并进行了实验验证。 首先，传统的单条插入方式在面对大量数据...
MySQL索引之主键索引
12-15
- 自增属性：常选择无业务意义的自增整数列作为主键，如`auto_increment`，这样新插入的数据将按顺序添加，减少页分裂。 - 不变性：主键值一旦设定，不应更改，以减少因值变化导致的行移动。 - 避免动态更新：不选用...
MySQL 中主键索引的页分裂：深入探讨
abzz的博客
08-14 448
在 MySQL 的 InnoDB 存储引擎中，页（Page）是数据存储的基本单位。每个页通常包含了多个数据行或索引项。当对表执行插入或更新操作时，数据需要被存储在这些页中。页分裂是指在插入新数据时，原本已经满了的页会被分裂成两个页的过程。这个过程是为了保持 B+ 树索引的平衡性和高效性。页分裂的发生使得数据在新的页上继续存储，从而避免页的过度拥挤。页分裂是 MySQL InnoDB 存储引擎中 B+ 树索引的重要维护机制，它确保了数据结构的平衡和查询的高效性。
MySQL——事务与存储过程（一）事务管理（3）事务的回滚
PAP
08-30 277
MySQL——事务与存储过程（一）事务管理（3）事务的回滚
mysql-day02
m0_64370841的博客
08-29 1347
【代码】mysql-day02。
MySQL 函数、约束、多表查询与事务详解
最新发布
A_cot的博客
08-30 1064
在 MySQL 数据库中，函数、约束、多表查询和事务是非常重要的概念，它们可以帮助我们更好地管理和操作数据。本文将详细介绍这些概念，并通过代码演示来帮助你更好地理解。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 781
MySQL——子查询（5）带比较运算符的子查询
mysql的聚簇索引、非聚簇索引、回表
求知路上的同行者的博客
08-27 794
mysql聚簇索引
xampp安装federated插件，实现mysql数据同步
阿拉丁的知识分享库
08-30 184
在a服务器上的data表创建触发器，data表插入数据后执行触发器插入到a服务器上data1。此时查看b服务器上的data表，数据已同步过来。需求：a服务器上的mysql数据库data表插入新数据时，需要同步到b服务器上的data表中。解决：a服务器上开启federated引擎插件，创建data1对应b服务器上的data表。把安装目录下MariaDB 10.4\lib\plugin的。复制到xampp\mysql\lib\plugin目录下。在a服务器data表增加触发器。1、查看mysql版本。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

YY的秘密代码小屋

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值