《Mysql是怎样运行的》读书笔记二

阅读量：

《Mysql是怎样运行的》读书笔记二

一、InnoDB的表空间(存放InnoDB页)

关于系统表空间和独立表空间自行百度

1.1页面类型

InnoDB以页单元的方式进行存储空间管理。聚簇索引与二级索引按照B+树组织在表空间中。在B+树结构中每个节点对应一个数据页。Page type is FIL_PAGE_INDEX;此外，在Innodb中还存在多种不同的页面类型.

1.2页面通用部分

FileHeader：用于记录页面的基本信息
- File Trailer：确保页面完整性，并保证数据在刷新过程中的一致性

FileHeader重温

1.3独立表空间结构

区（extent）

当表空间中的页面数量较多时，在MySQL中，默认采用了分片（extent）的技术来优化存储效率。对于单个页面大小为16KB的情况，在这种情况下，默认的一个分片通常由连续的64个页面组成。因此，在这种情况下，默认的一个分片占据的空间为1MB。无论是在系统表空间还是独立表空间中，在这种情况下，默认的一个分片占据的空间均为1MB，并且无论是在系统表空间还是独立表空间中

为何采用分区机制？
在数据库设计中，默认情况下一个分区占据连续的64个页。
当处理的数据量变得非常大时，在创建大型索引时，则会将存储空间划分为更大的分区。
其主要目的十分明显——为了减少随机I/O操作的影响。
由于B+树结构的特点，在某些情况下相邻节点的位置可能相差较大。

由上图可得，

该表的初始组中的前三个页面类型是固定设置的
FSP_HDR 负责记录整个表空间的关键信息以及该区域内的所有分页（从 0 到 255 号分页），整个表空间仅有一个 FSP_HDR 实例
IBUF_BITMAP 类型的页面存储本组所有分页的插入缓冲信息 * INODE 类型的页面包含大量 INODE 数据结构
在表格中，其余部分初始的前两个页面类型是固定设置。
XDES类型的缩写即是extent descriptor这一术语，在该分段中的256个区域中用于记录其属性信息，并引用 $IBUF_BITMAP$ 这一数据字段。

断（segment）

分区为了消除随机I/O，分段的原因呢？

范围查询，其实就是对B+树叶子节点的记录进行顺序扫描，如果不区分叶子节点和非叶子节点，统统把节点页申请到区，扫描的效果会很差。索引InnDB对B+树的叶子节点和非叶子节点进行了区别对待。

叶子节点有自己独特的区。存放叶子节点的区的集合即为一个段（叶子节点段）

非叶子节点也有自己的区。存放非叶子节点的区的集合即为一个段（非叶子节点段）

碎片区（属于表空间，不属于如何一个段）

该系统采用的是基于区域划分的方式进行管理（具体划分单位为每1MB）。然而这种做法在面对少量的页面或者少数记录时仍然会导致资源浪费问题。因此InnoDB数据库引擎提出了碎片化片的概念：在一个碎片化片中并非所有的页面都是专门用于存储同一特定的数据块；相反地这些页面可服务于多种不同的目的。（其中一些属于片段A另一些则属于片段B）

所以之后段的分配存储空间的策略：

在向数据库表中插入数据初期阶段时，
- 数据段从一个特定的碎片区开始按单独页面块进行存储空间分配。
- 当某个数据段已占用32个碎片区单独页面块后，则会采用完整区域作为单位来进行后续的存储空间分配策略。

区的分类（4类）—》对应区的4种状态

当前尚未在该区域中使用任何页面，则该区域归类为空闲表空间。
- 若有剩余容量，则称该碎片区域具备可使用的存储位置。
- 若无剩余容量，则表明该区域已无可用空间。
- 此区域与特定段落直接相关联。

XDES Entry（Extent Descriptor Entry）

为了管理这些区，每个区对应着一个XDES Entry结构

XDES Entry 是一个40个字节的结构，大致分为4个部分，各个部分的释义如下：

Segment ID

每个段都有一个独特的标识符。
在本处，
即属于该区的 Segment ID字段。
当然前提是该区域已经被分配给某个段。

List Node （这个部分可以将若干个 XDES Entry 结构串联成一个链表）

确定表空间中某个特定的位置可以通过指定页号值以及该位置相对于所选页的偏移量来实现

其定义为 Pre Node Page Number 和 Pre Node Offset 这两个参数的集合用于指示前驱项

Next Node Page Number和Next Node Offset的综合相当于指向后一个XDES Entry的指针。

State

可选的值包括之前提到的四个具体数值：FREE、FREE_FRAG、FULL_FRAG和FSEG

Page State Bitmap(16字节)

这部分占用16个字节（等于128个比特位）。我们说一个区默认配置有64个页面（即64 pages），这128个比特位被划分为64组（共有 64 groups），每一组对应着该区域中的一个页面（each group corresponds to a single page within the region）。例如，在Page State Bitmap字段中（within the Page State Bitmap field），前两位表示第一个页面的状态（the state of the first page），接下来的两位表示第二个页面的状态（the state of the second page），依此类推……其中第一个二进制位指示该页是否为空闲状态（the first binary bit indicates whether the page is in a free state），第二个二进制位尚未被定义（the second binary bit remains undefined at this stage）。

XDES Entry链表

插入一条数据时，在聚簇索引以及该表的所有二级索引的B+树中进行操作。
由于Index页之间的物理空间可能不连续，
这会导致随机I/O操作的发生。

为了提高效率，
我们引入了"区"的概念，
在一个区中为这些页面分配了一段连续的空间。

这样一来，
不论是插入数据，
还是执行查询或修改操作，
都只需要进行顺序I/O，
从而提升了系统性能。

在实际应用中，
我们会将这些页面所在的区域划分为不同的类型：
叶子节点负责存储实际的数据页，
而非叶子节点用于构建索引结构。

这种分段管理的方式显著提升了系统的性能水平。

既然这么好的情况下，
为何还要引入XDES Entry来管理？
为了提高向表插入数据的效率，
并不希望数据量少而导致浪费表空间。
原来是因为担心随意开辟区（1MB大小导致浪费表空间）。

捋一捋向某个段插入数据的过程

当需要处理数据量较小时（即段内存储的空间不足时），系统会首先检查表空间中的可用区域是否存在处于"FREE_FRAG"状态（即有空闲空间且尚未被使用的碎片区）的情况。如果找到了这样的可用区域，则会从该区域内提取若干碎片化的页面并将其填充到当前的数据段中；如果未找到可用的"FREE"状态（即完全空闲）区域，则系统会自动创建一个新的"FREE"状态（即完全空闲）的分区，并将其标记为"FREE_FRAG"状态，并从中提取部分碎片化的页面进行填充操作。随后系统会对所有需要插入碎片化页面的不同数据段统一采用相同的策略进行处理，并持续从该特定分区中提取碎片化页面直到无法继续获取为止。最后将这些分区的状态更新至"FULL_FRAG"状态

如何知道表空间哪些区是FREE哪些是FREE_FRAG？

通过XDES Entry的List Node:(从链表的表头开始遍历)

复制代码

 * 把状态为 FREE 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FREE 链表。
 * 把状态为 FREE_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FREE_FRAG 链表。
 * 把状态为 FULL_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之为 FULL_FRAG 链表。

当段中的数据以及占满32个零散页后，就直接申请完整的区来插入数据

问题1：我们如何知道哪些区属于哪些段？遍历所以的XDES Entry结构？把状态为FSEG的区对应的XDES Entry结构都加入一个链表？显然不可能。

Innodb的每个段都有其独立的链表，可以根据段号来建立链表，有多少个段就建立多少个链表。

但一个段有好多个区，有的区完全空闲，部分空闲，还要FULL，其实每个段中的区对应的XDES Entry结构建立了三个链表：
复制代码
 * FREE链表：同一个段中，所有页面都是空闲的区对应的 XDES Entry 结构会被加入到这个链表。
 * NOT_FULL链表：同一个段中，仍有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
 * FULL链表：同一个段中，已经没有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。

链表基节点List Base Node

上述一组复杂的列表结构，请问您希望获取哪些具体的列表信息呢？尤其是各个列表对应的首尾指针位置。

在List Base Node字段中，请您明确列出所包含的具体信息内容。

这些包括：

各个子列表的首尾指针位置

每个子列表所包含的具体数据项数量

子列表之间的关联关系

以上就是该字段所记录的关键信息点。

我们上边介绍的每个链表都对应这么一个 List Base Node 结构，其中：

List Length 表明该链表一共有多少节点，

页码信息和偏移信息表示该链表的头节点在表空间中的位置

Tail Node Page Number 和 Tail Node Offset 表明该链表的尾节点在表空间中的位置

通常我们会将某个链表对应的List Base Node结构以特定区域内的固定位置存放起来；通过这种方式，在查找定位特定链表时会更加便捷。

段的结构（INODE Entry）

实际上，并非表空间中某一个连续的物理区域这一概念能够精确对应到表空间中的某个具体位置；而是作为一个逻辑上的概念存在，在表空间中表现为零散页面以及完整的分区组合体。

类似地，在InnoDB体系中为每个数据段（即"大叔"）都设置了相应的XDES Entry来记录其属性信息；与此同时，在设计阶段也对每个数据段进行了相应的规划与配置工作。

需要特别指出的是，在InnoDB中为每个数据段定义了一个特定的数据结构（即INODE Entry），该结构负责存储关于数据段的关键信息参数。

各类型页面详细情况

您是否还记得FSP_HDR？它即位于表空间的第一个组的第一页，并用于记录表空间的相关属性以及在0-255区间内对应着XDES ENTRY结构

File Space Header 部分

这个部分用来存储表空间的一些整体属性

IBUF_BITMAP 类型

这种类型的页里边记录了一些有关 Change Buffer 的内容，后面会涉及到

INODE 类型

List Node for INODE Page List

由于一个表空间中可能存在超过85个段的存在性问题而导致单个INODE类型页面的空间不足性问题因而必须采用多个INODE类型页面以满足存储需求的要求同时为了便于管理和维护这些INODE类型页面的设计者将它们组织为两个独立的列表结构以提高数据管理和效率水平

SEG_INODES_FULL 链表：该链表中的 INODE 类型的页面已无足够的空间来新增INODE Entry结构。

SEG_INODES_FREE列表：该列表中的INODE类型的页面剩余空间可用于容纳额外的INODE Entry结构。

一个索引会产生两部分数据存储区域（子节点的数据存储区域和父节点的数据存储区域），每部分数据存储区域都与一个INODE记录项相关联。那么如何确定某一部分数据存储区域与哪个INODE记录项相关联呢。

还记得数据页，也就是 INDEX 类型的页时有一个 Page Header 部分

其中的 PAGE_BTR_SEG_LEAF 和 PAGE_BTR_SEG_TOP 分别各自占据10个字节；它们实际上被被称为 Segment Header 的结构。

1.4 Innodb 数据字典

Mysql除了保存我们插入用户的数据之外，还有：

某个表属于哪个表空间，表里边有多少列

表对应的每一个列的类型是什么

此表共有多少索引？每个索引具体对应哪些字段？此表空间中的各个索引对应的根页面位于哪些页面上？

该表有哪些外键，外键对应哪个表的哪些列

某个表空间对应文件系统上文件路径是什么

在不得不引入一些新的元数据以更好地进行管理和维护这些用户信息的过程中

InnoDB存储引擎特别定义了某些列的内部系统表（internalsystem table），专门用于存储关于这些元数据的信息。

二、单表访问的方法

2.1访问方法（access method）的概念

以下是经过同义改写的文本

2.2 几种访问类型（const ref ref_or_null range index all)

const ：通过主键或者唯一二级索引列来定位一条记录的访问方法

唯一二级索引查询的等值为NULL的情况特殊，另外讨论

ref：普通二级索引列与常数进行等值比较

二级索引列值为 NULL 的情况

无论是普通二级索引还是唯一二级索引
其对包含 NULL 值的数量均无限制
因此在这种情况下我们仅限于通过 ref 访问方式来处理 key IS NULL 的搜索条件
当二级索引用联合索引时
只要最左边的一组连续字段与常数值进行等值匹配就可以采用 ref 访问方式

有时候我们不仅希望找到对应二级索引字段数值与特定基准一致的数据，并且还需同时检索该字段数值为空的数据条目

复制代码

    SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

range:索引列需要匹配某个或某些范围的值

对于聚簇索引也是类似于 ref

index:采用遍历二级索引记录的执行方式称之为： index

我认为这相当于遵循索引树这一路径，在这种情况下由于隐含条件导致无法直接使用默认方法因此必须重新遍历整个索引树这样的做法相较于遍历聚簇式的结构的优势在于避免了隐含条件所带来的计算开销从而显著提升了性能特别适用于覆盖式索引场景即无需进行表连接操作

all：全表扫描

。。。：其实还有很多，这里不再赘述，明白前面几个就OK了

2.3 注意事项

1、二级索引+回表（多个二级索引情况）

一般情况下，只能利用单个二级索引执行查询

复制代码

    SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件：

key1 = ‘abc’
key2 > 1000

优化器通常会基于 single_table 表中的统计信息进行评估，并确定哪一个条件将被分配到对应的二级索引以减少查询扫描的行数

分为两个步骤：

基于二级索引定位技术的具体实施步骤如下：
首先启动回表操作流程；
随后依据上一阶段获取到的相关记录主键值实施回表操作；
接着在聚簇索引结构中查找对应的完整用户数据记录；
然后基于key2 > 1000这一条件，在完整用户的数据库中进一步筛选符合条件的信息；
最后将满足所有筛选条件的信息集合返回给系统参与者。

一般情况下执行一个查询只会用到单个二级索引

2、明确range访问方法使用的范围区间

在B+树索引中，当使用=号或<>号等操作符将索引列与常数连接时（包括IN, NOT IN等连接词进行关联），同时考虑IS NULL或IS NOT NULL等条件表达式时（即可行），从而形成一个称为区间的结构。

注意点：

模糊查询机制LIKE，在仅当匹配完整字符串或部分字符串（即前缀）时才可应用索引。
IN操作符的作用等价于将多个等值匹配操作符=通过逻辑OR连接起来的效果。
一个where子句可能包含多个小范围的搜索条件，在这些条件下需要用AND或者OR操作符进行连接；但对于范围查询而言，在处理AND和OR时如何准确提取相应的区间？下文将分别探讨这两种情况。

3.1 所有搜索条件都可以使用某个索引的情况

复制代码

    SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;
    对于AND而言，取交集即等价于 SELECT * FROM single_table WHERE  key2 > 200;
    SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;
    对于OR而言，取并集即等价于 SELECT * FROM single_table WHERE  key2 > 100;

3.2 有的搜索条件无法使用索引的情况

解决方式：将非该索引的搜索条件都变为TRUE

SELECT * FROM single_table WHERE key2 > 100 AND common_field = ‘abc’;

等价于：SELECT * FROM single_table WHERE key2 > 100 AND TRUE;（SELECT * FROM single_table WHERE key2 > 100）

SELECT * FROM single_table WHERE key2 > 100 OR common_field = ‘abc’;

等价于： SELECT * FROM single_table WHERE key2 > 100 OR TRUE;（SELECT * FROM single_table ）

3.3 复杂搜索条件下找出范围匹配的区间

有的查询的搜索条件可能特别复杂，范围匹配的各个区间复杂，比如：

复制代码

    SELECT * FROM single_table WHERE 
     (key1 > 'xyz' AND key2 = 748 ) OR
     (key1 < 'abc' AND key1 > 'lmn') OR
     (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

分析：

首先考察 WHERE 子句中的搜索条件涵盖哪些列，并确定哪些列可能依赖索引

该查询的搜索条件包括了key1、key2和common_field这三个字段。其中key1字段具有普通的一级索引idx_key1 ，而key2字段则拥有唯一的二级索引idx_key2。

对于那些可能用到的索引，分析它们的范围区间。

假设我们使用 idx_key1 执行查询

复制代码

        (key1 > 'xyz' AND TRUE ) OR

         (key1 < 'abc' AND key1 > 'lmn') OR
         (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
         
        #结果
         (key1 > 'xyz') OR (key1 > 'zzz')
         即  key1 > xyz

idx_key2 执行查询

复制代码

         (TRUE AND key2 = 748 ) OR

         (TRUE AND TRUE) OR
         (TRUE AND TRUE AND (key2 < 8000 OR TRUE))
         
         #结果
         key2 = 748 OR TRUE 即 TRUE

索引最终我们选择key1作为所谓查询的单个索引

3、索引合并

通常情况下，在常规操作中，默认情况下只会调用单个二级索引进行查询。然而，在某些特定情况下可能会同时使用多个二级索引来优化查询效率。这种方法被称为：Index Merge Technique。具体来说，这种技术通常采用以下三种方式实现：

Intersection合并 （AND情况）

Intersection 的意思是交集。这里指的是某个查询能够利用多个二级索引进行检索，并最终确定符合条件的数据。

复制代码

    SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

步骤：

利用 idx_key1 第二级索引来获取 key1 = ‘a’ 相关文档。
利用 idx_key3 第二级索引来获取 key3 = ‘b’ 相关文档。
所有二级索引项均基于索引字段加主键组合形成，在此基础之上我们能够求得这两个结果集 id 值的共同集合。（建议首先确定相应的id集合避免立即执行查询操作这种做法可有效降低因频繁查询而产生的I/O开销

使用交集合并的条件：

在处理联合索引时，在第二级层次的字段（即第二级层次的索引字段）中实施等值匹配的处理方式。
主键字段支持范围匹配。

复制代码

    SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

Union合并

通常我们在编写查询语句时会希望筛选出满足特定条件的记录。具体来说，在进行查询操作时，如果我们需要找到同时符合多个不同搜索条件的所有记录，则这些条件之间的关系被定义为逻辑或关系。

复制代码

    SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

使用并集合并的条件 ：

在联合索引结构中采用等值对应的方式处理二级索引字段，在实际应用中需要确保每个参与联合的字段都满足等值对应的要求，并禁止仅部分字段满足的情况出现。
主键字段允许进行范围匹配操作。
通过使用Intersection索引进行搜索的条件构建方式能够实现多个查询结果的交集检索功能。

复制代码

     SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 =
    'c' OR (key1 = 'a' AND key3 = 'b');

Sort-Union合并

Union 索引合并的应用条件过于严格要求，在执行等值匹配操作时需要满足各个二级索引列之间的对应关系才能发挥作用。

复制代码

    SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

因为基于 key1 < ‘a’ 从 idx_key1 索引中进行二级索引记录的主键值查找操作未能得到有序排列的结果，并且基于 key3 >‘z’ 从 idx_key3 索引中执行类似操作也未能获得预期结果；然而，在这两个条件所表达的独特吸引力面前仍持开放态度；因此我们可以采取以下措施

首先依据key1小于‘a’这一条件全面从idx_key1中获取相关记录，并按记录中的主键值进行排序；
接着依据key3大于‘z’这一条件全面从idx_key3中获取相关记录，并按记录中的主键值进行排序；
由于上述两个二级索引中的主键值均为有序排列状态，后续的操作与Union索引合并的方式将保持一致。

没有Sort-Intersection索引合并

全部评论 (0)

还没有任何评论哟~

《Mysql是怎样运行的》读书笔记二

《Mysql是怎样运行的》读书笔记二一、InnoDB的表空间存放InnoDB页关于系统表空间和独立表空间自行百度 1.1页面类型 InnoDB是以页为单位管理存储空间，聚簇索引和二级索引以B+索引...

《Mysql是怎样运行的》读书笔记三

《Mysql是怎样运行的》读书笔记三一、两个表的连接连接的原理连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。 1.1连接的过程任意连接数张表，如果没有任何限制...

《MySQL是怎么运行的》读书笔记

MySQL是怎样运行的第一章初识MySQL 总结第二章MySQL启动选项和系统变量总结第三章字符集与比较规则（排序规则）总结第四章InnoDB记录的存储结构总结第五章InnoDB数据页...

《MySQL是怎样运行的》读书笔记(一)重新认识MySQL

本人淘天面试快两小时，前面很顺但最后暴露不知道给哪些列加索引给面试官整懵逼了，遂痛改前非，打算通过系统阅读底层原理的技术书籍从根儿上理解MySQL，望周知第一章主要是宏观上的架构和对连接过程的介绍，...

《MySQL是怎样运行的》读书笔记(三) B+树索引

前言从前面数据存储结构中我们已经知道了页和记录的关系示意图: 其中页a、页b、页c...页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。在正式介绍索引之前，我们需要了解一下没有索引的...

《MySQL是怎样运行的》读书笔记(二) 从一条记录说起-InnoDB记录结构

前言到现在为止，MySQL还是一个黑盒，只知道使用客户端发送请求并等待服务器返回结果那么表中的数据到底存到了哪里?以什么格式存放的?MySQL是以什么方式来访问的这些数据? 相应的知识储备我只知道...

《MySQL 是怎样运行的》学习笔记

初识MySQL 服务器处理客户端请求 1\.服务器会维护线程池，为每个客户端连接分配一个线程，线程数可以动态配置 2\.解析优化查询缓存。从MySQL5.7.20开始，不推荐使用查询缓存，并在MyS...

《Linux是怎样工作的》读书笔记

目录前言 Chapter1计算机系统概要 1.1计算机的硬件 1.2OS 1.3用户模式和内核模式 1.4内核 Chapter2用户模式实现的功能 2.1系统调用 2.2系统调用的包装函数 2.3O...

Mysql是怎样运行的

《Mysql是怎样运行的》读书笔记一开始第三刷一、Mysql客户端和服务端连接（1）通过tcp连接真实环境中，数据库服务器进程和客户端进程可能运行在不同的主机中，它们之间必须通过网络来进行通...

mysql是怎样运行的

mysql是怎样运行的前言 1、Mysql存储引擎 1.1、InnoDB底层存储结构 1.1.1、行数据 1.1.2、页数据 1.1.3、区数据 1.1.4.段数据 1.2.数据插入时的逻辑 1.3...

是否确定退出登录?

《Mysql是怎样运行的》读书笔记二