<<MySql是怎样运行的>>小记

第一章#

  Mysql也是基于客户端和服务端的架构,由客户端连接上服务端,进行登录,而后在客户端输入命令到服务端,由服务端来处理这些命令,对数据进行处理.Mysql服务端进程被称为数据库实例.

  Mysql的服务端和客户端连接也就是进程之间的通信,主要的方式有TCP、命名管道、共享内存、Unix套接字.

服务端对客户端请求的处理主要分为三个步骤:连接管理,解析与优化,存储引擎#

连接管理:#

客户端与服务端进行连接的时候,服务端会创建一个线程来进行处理客户端的请求,而这些线程由线程池管理,避免重复创建销毁线程和管理连接.客户端首先携带主机信息,用户名,密码等信息进行请求连接,与服务端会根据这些信息进行认证.客户端和服务端之间的通信可以通过TLS协议来进行加密.建立连接后服务端对应的线程会一直等待客户端的请求,进行处理.

解析与优化比较主要的部分:#

查询缓存:
如果最近服务端所接收到的查询语句一模一样,并且查询语句中不包含一些系统表或是函数,则会从之前的查询结果直接返回给后一个查询请求,如果有其他改变数据的行为也会将该查询缓存删除,因为维护缓存会造成许多开销,查询缓存在5.7.20已经不推荐使用了,在8.0版本已经删除.

语法解析:
分析客户端命令中的语法是否有错误,并且将命令中有关的表,查询条件等放到Mysql内部所使用的数据结构上

查询优化:
可能客户端的Sql语句有效率不足或是效率可优化的情况,该步骤会将对应的语句进行一个优化,如外连接转换成内连接,表达式简化等.

存储引擎:#

到这一步服务端才开始真正的进行访问表中的数据,不同存储引擎为服务端提供了统一的对数据的存取和更新操作接口,不同的存储引擎有不同的长处.服务端在寻找到符合要求的数据时,会先将数据发送到缓存区,当缓存区满的时候统一发送数据到客户端.

第二章#

启动项和配置文件#

Mysql的服务端和客户端都有一些选项进行配置,可以在启动脚本命令增加对应的属性或是在配置文件中进行配置来进行设置.启动项中配置的只对当次启动生效,而配置文件中配置的每次启动都生效,只需要配置一次.
如果在不同的配置文件中配置了相同的设置,会按照顺序来进行配置,保留的就是最后一个,如果配置文件和命令行都有配置相同的设置,一般已命令行中的为准

系统变量#

Mysql服务端程序有许多变量会影响程序行为, 如最大客户端连接数(max_connection)和查询缓存大小(query_cache_size)等,我们将这些变量称之为系统变量.
每个系统变量都有一个默认的值,我们可以通过命令行或是配置文件来进行这些系统变量的配置, 从而来影响我们的服务端程序.
查看系统变量和该系统变量的当前值:
SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式]
可以使用通配符,如LIKE '%关键字%' 来进行匹配.
大部分的系统变量都可以在运行的时候进行更改,而无需去重启服务端程序.

系统变量范围:#

Session(会话):对应客户端连接的操作
Global(全局):对应服务端全局的操作
修改系统变量语法:
SET [GLOBAL|SESSION] 系统变量名=值;
如果省略指定系统变量范围则默认为SESSION,这点SHOW命令也一样,增加范围修饰可以使显示出对应系统变量所对应的全局值或是会话值.
有些系统变量只具有SESSION范围或是GLOBAL范围,这要依据情况而定,比如只针对服务端有效或是只针对客户端有效的变量一般具有该特性.

状态变量#

状态变量是用于描述MYSQL服务器运行情况的变量,如Threads_connected(当前客户端建立连接数)等等.
状态变量的查询语法与系统变量相似:
SHOW [GLOBAL|SESSION] STATUS[LIKE 匹配的模式]
注意:状态变量只能查询,不能设置.

第三章:字符集和比较规则#

字符集是一种表示处理字符与二进制之间编码和解码的规则.
字符之间有时需要进行大小比较,一种字符集可以有多种比较规则.
有许多字符集可以不同的字符可能会有不同的字节长度来存储,比如GB2312字符集,该字符集包含的ASCII字符集,如果对应的字符是ASCII字符集中的字符,则只会用一个字符来存储.那如何判断用几个字节来存储呢?如果是ASCII字符集(0-127)的字符,则最高位会为0,如果为0则表示是ASCII字符集的字符,否则该字节就是其他字符的一部分.我们可以称这种编码为可变长编码方式.
不同字符集可能对相同的字符有不同的编码方式,这一点要注意.

MYSQL支持的字符集#

utf8和utf-8mb4#

utf8使用1-3个字节来存储字符
utf8mb4使用1-4个字节
如果需要存储emoji表情或是四个字节存储的字符,请使用utf8mb4,该字符集在Mysql8.0版本已经成为了默认字符集
查看当前数据库所支持的字符集:
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

查看MYSQL中支持的比较规则:
SHOW COLLATION [LIKE 匹配的模式];

字符集和比较规则的应用#

字符串和比较规则有四个级别:服务器级别,数据库级别,表级别,列级别.

服务器级别:在该服务端创建表所对应的字符集和比较规则.
SHOW VARIABLES LIKE 'character_set_server';
数据库级别:在创建数据库或是修改数据库的时候所指定的字符集和比较规则.想要查看当前数据库所对应的字符集和比较规则:
USE 数据库名;
SHOW VARIABLES LIKE 'character_set_database';
表级别:在创建表和修改表时所指定的字符集和比较规则.
列级别:对于存储字符串的列,同一个表中不同列可以使用不同的字符集和比较规则,这需要在创建列或是修改的时候进行指定.
注意:当修改字符集或是比较规则的时候,所对应的另外一个也会跟着变化,比如修改了字符集,比较规则会变成该字符集默认的比较规则.如果修改了比较规则, 则字符集会被修改为该比较规则适用的字符集.

客户端和服务器通信过程中所使用的字符集#

编码和解码所使用的的字符集不一致可能会导致出现不理想的结果.如utf-8编码的字符,服务端使用ISO字符集去解码.从而获取了不同的字符.

字符集转换#

当接收字节序列时,先使用编码的字符集进行解码,而后再使用目标的字符集进行编码、存入,我们称这是字符集转换
在客户端进行请求服务端,服务端返回数据,可能服务端会对这次请求的字节序列进行多次字符集转换处理.
在客户端与服务端进行连接之后,服务端会单独为客户端维护一个character_set_client的(SESSION)变量来表示客户端的字符集.
如果客户端所实际使用的字符编码方式和character_set_client所表示的字符集不能解码,会发生错误.
服务端在真正处理的时候会将对应字节序列 character_set_client变量的字符集进行转换为character_set_connection变量对应的字符集.这俩个变量都是SESSION级别.
在服务端返回数据给客户端的时候也可以通过character_set_result这个变量来指定所返回的字符集.

字符集有关系统变量(都是SESSION范围,即对会话进行维护的)
character_set_client 客户端的字符集
character_set_connection 服务器在处理请求时所使用的字符集
character_set_result 服务器返回给客户端的编码的字符集

上述系统变量在客户端成功和服务端进行连接后,服务端会单独对每个客户端连接维护一组相应的变量.

第四章 InnoDB记录存储结构#

存储结构或是对表的读取、操作这些都是由表的存储引擎来决定的.所以一般不同的存储引擎的存储结构也不同.

InnoDB页#

InnoDB引擎是一个将数据存储在磁盘的存储引擎,而数据处理都是发生在内存的,如果每次读取数据和新增或是修改数据都是一条一条的操作,那会让操作变得非常慢.
所以InnoDB引擎将页作为与磁盘和内存交互的基本单位,将数据分成若干个页.一般来说InnoDB页的大小(innodb_page_size)是16KB.也就是一次操作最少使用16KB的内容.

InnoDB行#

我们在表中操作数据都是以记录为单位进行操作的,也可以称为行格式.
行格式有:COMPACT REDUNDANT DYNAMIC COMPRESSED

我们可以通过ROW_FORMAT属性来进行指定对于表的行格式
CREATE TABLE 表明 (列的信息) ROW_FORMAT=行格式;
ALTER TABLE 表明  ROW_FORMAT=行格式;

COMPACT#

额外信息
变长字段长度列表

用于存放某些可变长数据类型(如VARCHAR,VARBINARY,BLOB等)字段的真实占用的字节数,并且该条数据的该字段非null.按照列的顺序逆序来存储.
存储变长字段长度字节数判断方式:如果(字符集表达字符最大字节数 * 变长数据类型的字符数 ) > 255 字节 ,并且真实存储的数据字节数>127 ,则采取2个字节来表示字节长度,否则则用1个字节来表示.
如果某个字段特别长,InnoDB可能会将该字段的部分值存放到所谓的溢出页,该长度就会只是存放在本页面的长度了.而溢出字段的占用字节数长度就有另一种特殊表示方式了.

NULL值列表

如果该条记录的某个字段为NULL值,再将其NULL值存储,会造成空间浪费,所以我们将为NULL值的字段在NULL值列表中作表示,而在真实数据部分忽略这些字段的存储.
1:统计某个表允许存放NULL值的字段
2:表示该条记录的对应列是否为NULL值,每个允许存放NULL值的列都会有个对应的二进制位,如果为1则该列为NULL值,为0则不为NULL值.二进制和变长字段长度列表同样按照列的顺序逆序排位.
3:NULL值列表必定为整数字节表示,比如如果一个表允许NULL值的字段有3个,则NULL值列表为1字节,而没有使用到的字节中的高位补0.

记录头信息

记录头信息固定为5个字节组成.用于描述一些记录的属性.

真实数据

这一段是用来存储这条记录真实的值
除了我们自己定义的列之外, MYSQL还为每个记录默认添加了一些列(也可以说是隐藏列)

列名         是否必须    占用空间(字节)    描述
row_id       否          6              行ID,唯一标识一条记录
trx_id       是          6              事务ID
roll_pointer 是          7              回滚指针

其中row_id是如果用户创建表的时候没有自定义主键,并且也没有使用UNIQUE约束并不允许NULL值的字段作为主键,则会给表默认创建一个row_id隐藏列来充当该表的主键.

可变长数据

虽然一些数据类型并不是可变长度类型,但是根据字符集的不同还是可能会给他分配变长字段长度.
比如CHAR(M)类型,当他采用ASCII字符集时候,就不会给他表示对应的占用字节长度.但是如果CHAR(M)的字符集使用UTF8这类使用变长编码来表示字符的字符集的话,也会给该字段进行表示占用字节长度.
同时对于该字段在真实数据空间中至少占用M个字节,即使只是空字符串.这样在更新该记录的该字段时,只要该字段的值大于旧值的字节长度又小于M个字节时,就可以直接进行更新,而不需要重新分配新的记录空间.

REDUNDANT#


REDUNDANT是MYSQL5.0之前就在使用的行格式.

字段长度偏移列表

这个行格式会将所有的字段的长度信息都逆序存储到这个列表中,并且他和COMPACT直接存储长度信息不一样,他是通过俩个相邻偏移量的差值来计算长度的.
比如一个记录的字段长度偏移列表(在真实数据中的列开始位置和结束位置)是
06 0C 13
那对应的长度就是
0x13 - 0x0C = 7字节
0X0C - 0x06 = 6字节
0x06 - 0x00 = 6字节
那第一

记录头信息

该行格式记录头信息占用6字节(48位)
比COMPACT多了n_filed(列的数量)和1byte_offs_flag(标记字段偏移列表偏移量使用1个字节还是2个字节表示)这个俩个属性,少了record_type(当前记录的类型)属性
1byte_offs_flag(1:1字节 0:2字节):如果该条记录真实的数据所占用的字节数<=127 则每个列对应的偏移量为1个字节.
大于127但不大于32767时,则每个列对应的偏移量占用2字节.
如果大于32767则存放到所谓的溢出页中.在本页中只保留前768字节和20字节的溢出页地址.偏移量还是用俩个字节来存储.

NULL值处理

如果某列对应的偏移量第一个bit位是1,那该列就是NULL值,所以如果真实数据不大于127就用1字节来表示偏移量,因为首位要表示是否为NULL.
如果某列是定长类型(如CHAR(M)),则NULL值也会占用真实数据部分空间,并且全用0x00字节填充.
如果某列为不定长类型如(VARCHAR(M)),则NULL值不在真实数据部分占用任何空间.

存储格式字符集

REDUNDANT对于变长编码的处理方式十分简单粗暴, 直接按照该字符集所使用的表示一个字符的最大字节数*对应长度的真实空间进行分配.
好处:更新时不用申请新的存储空间,可以直接在原空间分配 坏处:会浪费一些存储空间

溢出列#

InnoDB操作单位一个页的大小一般为16KB,但是不排除某些记录的数据大小超过16KB,而这个时候某些特别长的字段就不会全部存放在该条记录的真实记录处了,会存放在该字段的真实数据处位置放置一部分的真实数据,然后使用20字节来存放真正存储数据的页地址和分散在其他页面上数据的占用字节数.从而找到剩余数据.(一般来说只会存储该字段的前768字节和一个指向其他页的地址,我们称该其他页为溢出页,该字段为溢出列)

那么在什么时候才会产生溢出列呢?

MYSQL中规定到一页至少要有俩条记录
页中存放额外的信息占用132字节
每个记录额外信息为27字节
如果我们有一个demo表,该demo表中只有一个varchar字段.
那不发生溢出现象得满足:
132+2*(27+n)<16384
n<8099
长度得小于8099,这条记录才不会出现溢出列现象.
不过一般表中不止有一个字段,一般不需要去关注这个临界点,只要了解如果一个列过长,就有可能会出现溢出列.

DYNAMIC 和 COMPRESSED#

DYNAMIC和COMPRESSED与COMPACT行格式的差别就是对于溢出列的处理不同,他不像COMPACT会将溢出列的一部分留存在真实数据空间,而是会只留存一个20字节的溢出页地址和真实数据字节数,而真实数据全部存放在对应的溢出页中.
并且COMPRESSED会采用压缩算法对页面进行压缩.

第五章 InnoDB数据页结构#

页:InooDB管理存储的基本单位,一般大小为16KB
页有存在许多种,比如存放数据的页,存放undo日志的页,这章主要讲的是存放表中记录的页(索引页).

数据页结构#

页中的存储#

当一个页刚生成的时候,是没有User Records部分的,只有Free Space空间,有新的记录要插入到该页中时, 会在该页的Free Space申请一个记录大小的空间,然后占用该区域(即划分为User Records部分).
当该页的Free Space空间使用完,如果还有新的记录,就会申请新的页去存储该记录.

记录头信息#

delete_flag

当一条记录被删除的时候,不会从磁盘上移除,而是在这条记录打上一个删除标志(即值为1),这些删除的记录会形成一个垃圾链表(用于记录可重用空间),当有新记录插入时,可以直接在这些空间上覆盖掉被删除的记录空间.
这样避免了直接删除时, 其他记录所要进行的重新排列.

mini_rec_flag

如果该记录是B+树每层非叶子结点中最小的目录项,则该值为1.

n_owned

当该记录是槽中最大的元素时记录了该槽的记录条数,否则为0.

heap_no

我们可以称记录一条条亲密排列的结构为堆(heap).heap_no就是为了方便管理该堆而设计的.
heap_no记录了记录在数据页中的物理相对位置.每条记录的heap_no都比物理位置在他前面的那条记录heap_no大1,而比后面的小1.
在数据页中有俩个自动创建的记录.infimum和supremum记录,他们的heap_no值为0和1,所以他们在堆中的最前面
InnoDb有一个规定,在该页面中任何用户记录都比infimum记录大,而任何用户记录都比supremum小(对于一条完整记录来说,这个大小指的是主键的大小)
heap_no值一旦分配,就不会修改了,即使被删除.

record_type

表示记录的类型.

0 普通记录
1 B+树非叶结点的目录项记录
2 Infimum记录
3 supremum记录
next_record

表示了当前记录的真实数据到下一条记录的真实数据空间的距离(字节).即对应的位置左边是下一条记录的信息头,右边是下一条信息的真实数据(NULL值列表和变长字段长度列表都是逆序存放的,这样可能能提高高速缓存的命中率).
这个下一条记录是指按照主键值从小到大排列的顺序.
next_record是为了维护页中的记录的一个单向链表,链表汇总的各个结点通过主键值从小到大串在一起.
该链表的起点是infimum 终点是supremum.
如果删除了某个结点,会将该结点的next_record值置为0和delete_flag置为1,并且将指向这个结点的next_record值指向下一个结点,跳过该结点.
在进行增删改的时候,InnnoDb也会维护记录的该链表.
可以理解,heap_no表示了该页中该记录在堆中的位置.而next_record记录了该页中该记录的逻辑(即根据主键排列)顺序.

Page Directory(页目录)#

页目录是一种InnoDb为了加快查询速度而应用的一种数据结构.在存放在靠近页尾部的地方.
页目录将页面中的记录(包括infimum和supremum)给按照从小到大依次分组.
我们将每一个分组称为槽(slot),槽记录其最大记录的页面偏移量,而槽中最大的那个记录的n_owned记录了该槽中记录的数量.每个槽占用2个字节.
在infimum所在的槽,只能有一条记录,而supremum所在的槽只能有18条记录,其余的槽只能有48条记录.
新记录的插入
当所对应槽的记录的now_owned大小为<8时,会寻找对应记录的主键值比待插入记录的主键值大,但是差值最小的槽,然后会将对应槽的n_owned值+1,表示该槽又多了一条记录.
当所对应槽的记录的now_owned大小=8时,再插入一条记录,会将原有槽拆分成俩个槽,一个槽4条记录,一个5条记录,会在页目录中新增一个槽,并记录该槽最大记录的偏移量.
查询记录时
因为在页目录中的槽是按照从小到大排序的,所以可以使用二分法在槽中寻找对应记录所在的槽的位置.
当找到槽的位置后,找到该槽中主键值最小的那条记录(从上个槽的末尾记录的next_record寻找),而后从那条记录的next_record属性遍历寻找该槽的记录从而寻找到对应的记录.

Page Header(页头部)#

这部分主要存储和存储在数据页记录相关的状态信息,如数据页中的记录条数,Free Space在页面汇总的地址偏移量,页目录中的槽个数等.
比如:
PAGE_DIRECTION:表示最后插入一条数据的方向,如果新插入的数据的主键值比上一条记录主键值的大,那我们称这条记录的插入方向是右边, 否则反之.
PAGE_N_DIRECTION:一个方向连续插入的数量,如果有某条记录插入方向相反,该值会清零而后重新统计.

File Header(文件头部)#

页头部所描述的是数据页的信息,而File Header所描述的是通用于各种文件的页信息.
文件头部固定占用38个字节.
FIL_PAGE_SPACE_OR_CHKSUM:MYSQL 4.014之前是本页面所在的表ID,之后为页的校验和, 可以在长字符串比较前先校验该值,如果该值不同就对应的字符串肯定也不同,能够节省时间.
FIL_PAGE_OFFSET:页号,用于让Innodb来唯一定位一个页.
FIL_PAGE_TYPE:用于区分不同的页类型.比如undo日志页,change Buffer空闲列表,溢出页,索引页(数据页)等.
FIL_PAGE_PREV,FIL_PAGE_NEXT:用于关联上一页和下一页,使这些页逻辑上形成一个双向链表串联起来.

File Trailer(文件尾部)#

和文件首部一样通用于各种的页.
该部分主要是为了防止当数据更新时,数据页在内存数据刷新到磁盘中,出现断电或是其他中断情况出现.只刷新了一部分数据.
文件尾部分成俩部分
页的校验和(8字节):在数据页刷新到磁盘前,要先将校验和计算出来,如果出现中断情况,因为文件首部会首先刷新到磁盘,文件首部的校验和(FIL_PAGE_SPACE_OR_CHKSUM)就会和该校验和(文件尾部的)不相等.这样我们就可以判断出刷新出现错误了.
页面最后被修改时对应的LSN的后4字节(8字节):和FIL_PAGE_LSN(页面被最后修改时对应的LSN(LOG Sequence Number 日志序号值)值)正常情况下相同.也是用来校验页的完整性.

第六章 快速查询的秘籍 B+树索引#

InnoDB上数据页中存储着记录,数据页之间通过双向链表关联,在数据页中快速找到某条记录可以通过页目录来通过二分法寻找对应的槽,而后遍历获取.
暂时讨论查询某一列等于某个常数的情况.

没有索引时查询#

以主键为搜索条件,在单个数据页查询会根据主键值寻找对应的槽,然后去遍历记录寻找对应的值.但是如果不是以主键为搜索条件,就不能使用页目录来快速搜索,就只能一条一条去遍历记录,效率很低.
如果需要在许多页中查询那条记录,还需要先定位到该记录所在的页,然后才能查找相应的记录,但是没有索引的情况下,即使使用主键或是其他条件进行条件查询,也没有办法定位到记录所在的页,就只能一页一页的寻找下去,需要遍历所有的数据页,效率同样很低.

索引#

我们可以先自己制定一个简易索引方案,该方案的要点如下:
一个数据页中的记录一定比上一个数据页中的记录主键值大,比下一个页中的数据小(通过主键大小比较).这样可以避免数据页上的主键值没有规律,不利于我们寻找对应记录主键值所在的记录页.
我们可以模仿页目录,给所有的页建造对应的目录项,用于记录数据页(page_no)的顺序,因为数据页可能并不是连续的.并且还要记录对应数据页中的最小主键值(key),这样就有助于让我们快速寻找到对应主键值记录所在的页.

这样一个简易的索引方式就成立了,我们查询数据的时候,先在目录项中根据主键值二分查找寻找到对应的页.当插入或是修改数据的时候,就根据得根据主键值寻找到对应的页进行插入,或是从原本的页移动到新的页中,保持数据页间的记录大小约束成立.
我们称这个目录为:索引.

InnoDB的索引方案#

我们在上面制作的简易索引方案有俩个缺点:InnoDB使用页来作为管理存储空间的基本单位,最多只能保证16KB的连续存储空间,虽然一个目录项占用不了多少空间,但是当表中数据量膨胀,此时需要十分大的连续存储空间才能放下所有的目录项,这不太现实.
另一个:当对记录进行修改操作(增删改)时,比如有一整个页的数据被删除,那其数据页对应的目录项也就失去了意义,如果要移除对应的目录项就需要将后面的目录项进行向前移动,这种牵一发而动全身的设计不好,或者是将该空间冗余,又浪费了空间.
改进的方法:这些目录项可以看作是列只有主键和页号的用户记录,可以将这些目录项存储在存储用户记录的数据页中统一管理,为了与用户记录区分开来,使用记录头的record_type来区分.

record_type
0:普通的用户记录
1:目录项记录
2:infimum记录
3:superemum记录

目录项记录的min_rec_flag(B+树的每层非叶子结点的最小目录项记录都会添加该标记)属性可能为1, 而用户记录的该属性值都为0
这样查询的流程大致为:先找到对应表的存储目录项记录的页,通过主键值确定该记录所在的页,而后在该页中寻找记录所在的槽,在槽中遍历寻找到对应的记录.
将目录项存储在数据页中可使数据页存储超过16KB时,可以直接分配新的数据页进行存储.通过FIL_PAGE_PREV,FIL_PAGE_NEXT关联.
但是这样又有了另一个问题,当存放页面项的页很多的时候,那我们怎么通过主键值去快速定位到对应存储页面项的页呢?
这里给出的解决办法是:对于页面项记录存储的页,建造更高级的目录,就像高级目录一样.

每次查询从根部,即最高级的目录项数据页进行查询,然后循环渐进,寻找到对应的记录.
我们也称这种数据结构为B+树.我们真正存放用户记录的页都在B+树最底层的结点(叶子结点).其余结点(非叶子结点,内结点)都用来存储目录项记录.最高层的那个结点称为根结点.
最大存储的用户记录数=((B+树层级-1)每个页面能存储的目录项个数)每个页能存储的用户记录条数.
数据页面头的PAGE_LEVEL就代表该数据页作为结点在B+树的层级

聚簇索引#

特点:1.使用记录主键值的大小进行记录和页的排序 2.B+树的叶子结点存储的是完整的用户记录.

二级索引#

虽然聚簇索引查询速度非常快,但是只能根据主键值来查找对应的记录,如果有别的查询条件怎么办?
我们可以根据对应的查询条件的列,再创建对应的B+树.
二级索引的特点:1.使用指定列(非主键)的大小进行记录和页的排序 2.B+树的叶子结点只存储了对应记录的主键值和指定列 3.指定的列除了有唯一约束外能够重复.

查询过程:
先从根目录开始寻找到C2值对应所在的目录项中,寻找到对应的目录项,查询到C2值所在的页,根据C2值寻找到对应的槽位进行遍历查询记录,如果找到了符合条件的记录,使用对应的记录中的主键去聚簇索引中寻找到完整的用户记录信息(称这种行为为回表),而后回到该二级索引的叶子结点,继续向后遍历,符合条件就回表获得完整记录,直到下一条记录不满足条件为止.

为什么二级索引不存储下完整的用户记录,而是每次要进行回表呢?
因为太浪费存储空间了,并且在每个索引下都存储完整的用户记录都会造成空间的冗余,重复存储了相同的记录.

联合索引#

我们可以使用使用多个列来创建索引.
比如我们使用C2列和C3列来创建索引.该索引中就会先根据C2列来排序,如果C2列相同,就会采用C3进行排序.
特点:每个目录项由 索引列和页号组成,如例子中就是C2和C3和页号
叶子结点中存储了索引列和主键列.

InnoDB B+树索引的注意事项:#

B+树的形成过程#

1.首先每当为一个表创建B+树索引的时候,都会给这个索引分配一个根结点的页面.最开始的时候表中没有数据,根结点也是没有目录项或是用户记录的.

2.而后向表中插入数据的时候,会先把用户记录存储到根节点中(如果根节点页面空间足够存储用户记录).

3.当插入到一定数量,根节点页面可用空间用完之后,继续插入记录,此时会将该根节点页面的所有记录复制到一个新分配的页(比如页a),然后对该新页(页a)进行页分裂操作得到另一个新页(比如页b).

这个时候再将新插入的根据索引列值来选择插入到页a还是页b中.而根节点升级为目录项记录页,将页a和页b所对应的目录项插入到根节点中.
一个B+树的索引的根节点页面从创建开始,就不会再移动了(即页号不会更改),然后记录到某个地方.当有需要用到该索引的时候,直接取出对应根节点的页号即可.

内结点的目录项具有唯一性#

为什么需要具有唯一性呢?我们可以设想一个场景:
如果我们二级索引建造的目录项以索引列和页号作为内容记录,那要是有某个索引值数量非常多,导致一整个数据页里或者甚至更多个数据页里都是相同索引值的记录,所对应的目录项记录的索引值也是相同的.
这样如果有一个数据插入进来,或是想要查询对应的数据,我们怎么样才能定位到对应的数据页呢?难道第一个页一个个遍历吗?
所以我们在建立二级索引的目录项时,需要将主键也给记录下来,这样就保证了目录项的唯一性,我们先使用索引列去寻找对应目录项,而后再使用主键再进行筛选过滤,这样就可以确保寻找到一个页所对应的目录项记录.
所以二级索引建造B+树目录项的时候不止会记录索引列,还会记录主键列.
对于二级索引记录项来说,会先根据二级索引的索引列进行排序,当索引列值相等时,就根据主键值去排序.
建立了一个二级索引,就相当于对(索引列,主键列)建立了一个联合索引.

一个页面至少容纳2条记录#

如果一个页面只包含一个子目录,会导致页面的层级非常多,所以Mysql规定了一个数据页最少得能容纳俩条数据(如果超出会形成溢出列,保证能够容纳2条).这样能够确保记录的查询速度.

MYISAM的索引方案#

MYISAM的记录是按照插入顺序单独存储到一个文件之中,并不进行划分数据页,而是有多少记录就往文件中塞多少记录.我们可以通过行号来进行快速访问一条记录.
MYISAM的索引是单独存储到另一个索引文件中,以表的追歼单独创建一个索引,只不过叶子结点存储的不是完整的用户记录,而是主键值和行号的组合,寻找到对应的行号后再去数据文件中寻找完整数据.
也就是MYISAM引擎即使使用主键查询,也需要进行一次回表操作.相当于MYISAM引擎索引都相当于二级索引.
MYISAM的二级索引叶子结点中也是存储的列+行号.
MYISAM的索引和数据是分开的,而Innodb中索引即数据,数据即索引.

第七章 B+树索引的使用#

B+树的特点总结:#

谈及B+树索引的使用,我们要先明确其B+树索引的特点,这样能更好的帮助我们熟悉使用时所要注意的点

  • 每一个索引都有其对应的B+树.而B+树的有许多层,叶子结点存储用户记录,而其他结点(内结点)存储目录项.
  • InnoDB引擎会为自动为主键生成聚簇索引(如果创建表的时候没有生成主键,会寻找表中有没有不可为NULL值并且UNIQUE约束的键,则会默认该列为索引,否则InnoDB会默认添加一个row_id主键列),聚簇索引的B+树的叶子结点存储完整的用户记录.
  • 我们可以为其他可能成为查询条件的列创建一个二级索引,二级索引B+树的目录项包括(最小索引列值,最小主键值,页码),而叶子结点包括索引列和主键.如果想要使用二级索引查询到完整的用户记录,需要先查询该二级索引树查询到对应记录的主键,再使用主键去聚簇索引对应的B+树中再进行查询,我们称这种行为叫回表.
  • B+树的每层结点(数据页)都按照索引列的大小来进行排序并且形成一个双向链表.而每个页内的记录也根据索引列形成了一个单向列表.如果是联合索引就根据先后顺序来进行排序.如果前一个条件相等就按照后一个条件来继续排序.因为要保证目录项的唯一性,所以当索引相关的列值都相等时,还会根据主键来排序.
  • 通过索引查找记录时,先通过根节点一层一层向下寻找,当寻找到对应记录存在的页后,再使用索引列在页目录中寻找到对应的slot(槽),而后去遍历槽内的记录获取到对应的记录.

索引的代价#

虽然索引对于我们在寻找一条记录根据某些条件获取他所应该在的数据页时十分好用,但是我们还是需要了解索引所带来的负面代价.

  • 空间代价:每创建一个索引,都要为其索引创建一个对应的B+树,每一颗B+树的每一个结点都是一个数据页,一个数据页默认占用16KB,如果B+树结点很多,则占用的空间会很多.
  • 时间代价:在增删改操作的时候,存储引擎需要对该表所关联的B+树进行维护更新,包括页分裂、页面回收等操作.以此保证B+树的每层结点都按照索引列值的大小从小到大的顺序排序组成双向链表.并且当查询查询语句在执行之前要先生成一个执行计划,在生成执行计划的时候,存储引擎要去需要去计算不同索引进行查询所需要的成本,从中选取一个成本最低的索引进行查询,如果索引过多会导致成本分析计算所花费的时间过多,影响查询的执行性能.

应用B+树索引#

扫描区间和边界条件#

我们称一个查询语句中根据条件而在索引B+树中划分的符合条件的区域叫做扫描区间,而那些条件叫做边界条件.
比如:
select * from t where id>=10 and id<=100;
其中[10,100]就是这条语句的扫描区间,而id>=10 and id<=100就是边界条件.
而如果是使用=符号充当条件或是IN之类的,判断某列等于某个常数的,我们称之为单点扫描区间.

如果有一个查询语句直接通过扫描聚簇索引B+树来从头到为进行遍历查询,然后比较查询条件,符合就将完整记录丢给客户端,否则就继续遍历,我们称这种查询为全表扫描.虽然这样是一种很笨的并且没有效率的查询方式,但是什么查询都可以这样使用.

只要索引列和常数使用=,<=>,IN,NOT IN, IS NULL,IS NOT NULL,>,<,>=,<=,BETWEEN,!=或者LIKE操作符连接起来,就会产生扫描区间
当我们执行一条查询语句的时候,需要找出所有可用的索引和它们所对应的扫描区间.

对于每一个搜索条件都可以生成合适的扫描区间的情况.

在使用某一个索引的时候,所有的条件都可以生成对应的扫描区间.
比如:
select * from t where key>=10 and key>=20;
这条语句的俩个搜索条件就各生成了一个[10,+∞)和[20,+∞)的扫描区间,而and是对着俩个扫描区间取交集,获取的最后的扫描区间就是[20,+∞)了,条件就是key>=20
如果我们将语句中的and换成or:
select * from t where key>=10 or key>=20;
这俩个搜索条件对应的搜索区间还是一样,但是操作符换成了or,对这俩个扫描区间取并集,结果就是[10,+∞)了.条件就是key>=10
这俩条语句都使用key对应的索引来对对应的扫描区间进行查询.

有的搜索条件不能生成合适的扫描区间的情况.

当使用某一个索引进行查询的时候,有些条件根据该索引无法生成扫描区间.
比如:
select * from t where key1>=10 and common_field = 10;
这里使用key1对应的二级索引来进行查询数据,可以发现该B+树中根本就没有记录common_field相关的值,或者根据该值排序.
在使用该索引进行查询的时候(因为根据common_field = 10条件无法缩减在对应索引树上的扫描范围),对应生成的扫描区间就为key1>=10,使用该索引查询需要先获取到符合key1>=10的记录,然后再去聚簇索引树上找到对应的记录,再去判断记录是否符合common_field = 10条件,然后再返回给客户端.
那如果我们操作符换成or呢?
select * from t where key1>=10 or common_field = 10;
如果这个时候还使用key1的索引来进行查询,common_field = 10在语句中没起然后作用,化简为:
select * from t where key1>=10 or common_field = true;
or操作符取并集,扫描区间就是[10,+∞)∪(-∞,+∞) = (-∞,+∞)
可以看到,如果使用该索引查询,该sql语句的条件并不能很好的缩减扫描区间,还需要扫描整颗树的叶子结点,并且二级索引还需要回表,这比直接进行全表扫描消耗的性能还要多.
这种情况下我们不会采取使用该索引进行查询的.

从复杂的搜索条件中找到扫描区间

有时候查询语句中会有多个搜索条件,并且搜索条件之间嵌套着许多的条件,包含不同列的条件.
这种时候我们不要先被复杂的条件吓到,可以尝试先简化条件,先查看条件中的列是否都是索引包含的列,然后去查看相应的条件是否可以简化,分析它们的扫描区间.
比如

SELECT * FROM t
WEHRE
(key1>'xyz' AND key2=748) OR
(key1 LIKE '%suf%' AND key1> 'zzz' AND (key2<8000 OR common_filed='abc'));

如果我们想使用key1索引来进行查询,获取他的扫描区间.
先简化条件中其他的列为TRUE,比如key2和common_field

SELECT * FROM t
WEHRE
(key1>'xyz' AND TRUE) OR
(key1 LIKE '%suf%' AND key1> 'zzz' AND (TRUE OR TRUE));

再省略TRUE:

SELECT * FROM t
WEHRE
(key1>'xyz' ) OR
(key1 LIKE '%suf%' AND key1> 'zzz');

再根据条件简化,key1 LIKE '%suf%'这个条件对应的是(-∞,+∞),与key1> 'zzz'对应的(zzz,+∞)取交集,为(zzz,+∞)
然后简化为:

SELECT * FROM t
WEHRE
(key1>'xyz') OR
(key1>'zzz');

而(key1>'xyz') OR (key1>'zzz');这个条件也是可以继续化简的,他们的并集为(xyz,+∞).
这个语句使用key1所对应的扫描区间就是(xyz,+∞)了.

使用联合索引执行查询时的扫描区间

使用联合索引进行查询的扫描区间有许多情况,因为联合索引的记录排序和多个条件的大小有关.
比如我们对表t建立了一个联合索引(part1,part2,part3)
在记录排序的时候,会先去判断part1列的值,如果相同则按照part2的值来排序,如果part2的值也相同,则按照part3的值来排序.都相同则按照主键列的值排序.
我们接下来根据对应的查询语句来判断联合索引的扫描区间(以下语句默认都使用part1,part2,part3建立的联合索引来查询):

SELECT * from t where part1 = 1;
因为该联合索引中先根据part1来进行排序,所以可以根据该查询条件来筛选扫描区间,为[1,1]
SELECT * from t where part1 = 1 and part2 = 2;
该语句中的条件有part1和part2,因为先根据part1来排列,所以可以根据part1=1得出[1,1]这个扫描区间,而后在part1值已经确认的前提下,根据part2来缩减扫描区间,最后得到的扫描区间是[(1,2),(1,2)]
SELECT * from t where part1 = 1 and part2 = 2 and part3=3;
该语句中的条件有part1和part2,因为先根据part1来排列,所以可以根据part1=1得出[1,1]这个扫描区间,而后在part1值已经确认的前提下,根据part2来缩减扫描区间,得到的扫描区间是[(1,2),(1,2)],
再根据part3进行缩减,获得的是[(1,2,3),(1,2,3)]这个扫描区间
SELECT * from t where part1>1;
因为该查询中索引中是先按照part1来进行排序的,所以根据part>1这个条件可以得知扫描区间为(1,+∞)
SELECT * from t where part1>1 and part2>10 and part2<20;
因为该查询中索引中是先按照part1来进行排序的,而后根据part2排序,所以我们可以获得扫描区间((1,10),(+∞,20))

先前我们的查询语句的条件中都有包含前面的索引列,比如part1,part1和part2,part1和part2和part3.但要是只出现part2呢?

SELECT * from t where part2=10;
当我们查询的时候发现,因为该索引先使用part1排序,才会使用part2,如果没有指定part1的值,part2在索引中是无序的,也就是可能一个part2=10的记录会在part2=11的后面(如果后者的part1值比前者小),并不相邻
所以如果只有part2条件,我们就需要进行全表扫描.所以不会使用该联合索引查询
SELECT * FROM t where part1=1 and part3=3;
虽然这个查询语句中,有part1条件,但是没有part2条件,在part3排序之前还有按照part2排序,我们无法通过part3=3这个条件来缩减扫描区间.
所以该语句的扫描区间为[1,1].
SELECT * FROM t where part1<10 and part2=2;
这个查询语句中虽然有part1和part2条件,数据先按照part1条件排序,获得扫描区间(-∞,10),而后看到part2条件,因为对于符合该扫描区间的记录来说,并不是直接按照part2条件来进行排序的,还是得先根据part1来排序,而后再根据part2排序.
所以该语句的扫描区间还是(-∞,10),part2条件无法影响到该扫描区间.
SELECT * FROM t where part1<=10 and part2=2;
和上一条语句比较,part1条件变成了<=,这个时候part2就能稍微影响到扫描区间了,当part1为10的时候,在这个区间的记录都是按照part2条件来排序.
所以扫描区间就为((-∞,-∞),[10,2]),当到达part1值为10而part2值为2之后的记录,如果不符合条件就会直接结束,而不需要将所有part1为10的记录扫描完.

因为联合索引的情况比较多,所以我们要根据情况,来进行判断使用.

索引用于排序#

我们的编写语句经常要使用到排序功能将查询出来的记录根据某种规则排序,比如ORDER BY,我们需要先将需要排序的记录加载到内存上,再使用排序算法在内存进行排序,如果需要排序的数据集太大内存无法存放下来,就会先将中间结果存放在磁盘中,再排序完成后再将结果集返回给客户端.我们称这种行为叫文件排序.

但是如果我们在ORDER子句中使用索引列,就可能能够省去在内存或者在磁盘中排序的步骤,只需要再对每条记录进行回表操作,将最后的结果集返回给客户端即可.

想要省略文件排序的操作,我们要确保子句中的能够利用到索引.比如使用联合索引排序要根据定义顺序来排序,或是条件中已经确认了前面索引列的值.
SELECT * FROM t WEHERE part1=1 and part2=2 ORDER BY part3
这种情况也是可以利用到索引的

不可以使用索引进行排序的情况
  • 在ORDER BY语句中ASC和DESC混用,在使用联合索引在进行排序的时候,我们要保证联合索引的各列的排序规则是一致的,要么全部升序要么全部降序.
    为什么呢?我们可以假设这样的一个排序规则不一致的查询:
    SELECT * FROM t GROUP BY part1,part2 Desc LIMIT 10;

    如图所示,如果我们对这样的索引进行一个这样的查询语句的执行的话,首先我们要先找到最小的10条记录,这里已经找到了,part1值分别为1,2,3;根据part1值升序的话直接从左往右取即可.
    但是还没完,我们这里还需要根据part2值来进行降序处理,那这里我们需要怎么样处理呢?我们就只能先寻找到值最小的的part1值,这里为1,将1的记录从左到右遍历到值为2的记录为止,有4条记录,将这4条记录倒序获取.
    而后再去寻找part1值为2的,再遍历到part1值为3的记录,倒序获取3条记录,还是不够10条,这样依次进行,直到获取了足够数目的记录为止.
    这样需要较为复杂的算法来进行索引的读取,不能高效的使用索引,所以在这种情况下是不会使用联合索引执行排序操作的.
    不过MYSQL8新增了Descending Index的特性,可以支持排序规则混用.

  • GROUP BY子语句中包含非同一个索引的索引列
    SELECT * FROM t GROUP BY key1,key2 LIMIT 10;
    像这种key1和key2不是同一个索引的时候也不会使用索引来进行排序,key1的索引里只根据key1排序,是不按照key2排序的,而key2也一样,只根据key2的值进行排序,这种情况下就不能使用上述索引来执行上述查询了

  • 排序的列使用的是联合索引的索引列,但是索引列并不连续

SELECT * FROM t GROUP BY part1,part3 LIMIT 10;
SELECT * FROM t GROUP BY part2,part1 LIMIT 10;

像是上述情况,如果出现索引列中断或是顺序和定义顺序不一致,也无法利用索引.

  • 用来形成扫描区间的索引列与排序列不同
    SELECT * FROM t WHERE key1=1 GROUP BY key2 LIMIT 10;
    像是上述这种情况,形成扫描区间的是key1=1这个条件,直接使用key1的索引去查询即可,也就无法使用key2来执行上述查询

  • 排序列不是以单独列名的形式出现
    SELECT * FROM t GROUP BY UPPER(key1) LIMIT 10;
    列名经过了UPPER()函数(将对应的值转为大写)修饰,所以不能使用索引执行查询.

使用索引进行分组#

有许多统计语句都需要使用分组功能,我们可以利用索引来提高分组效率
SELECT part1,part2,count(*) from t GROUP BY part1,part2;
这个查询语句就是一个统计part1和part2值的记录条数.
先根据part1值来分成不同的组,再将这些组根据part2值分成更小的组.
如果没有对应子句中的索引的话,就需要去创建一个用于统计的临时表,然后在扫描聚簇索引的时候将统计的中间结果记录到该临时表中,当扫描完记录后再将该临时表中的结果返回给客户端.
但要是有索引可以利用,就可以直接在使用该索引进行分组,就不需要再建立临时表了.

回表的代价#

我们可以先举个例子:
比如这一条查询语句
SELECT * FROM t WHERE key1>'a' and key1<'c';
这一条查询语句我们可以选择俩种查询方式:

  • 使用聚簇索引进行全表扫描,当遇到满足条件的记录就返回给客户端
  • 使用key1所对应的二级索引进行扫描
    如果我们采用key1所对应的二级索引进行查询,因为该语句是个查询该表所有字段的语句,所以需要先去获取满足条件的二级索引记录,而后对这些二级索引记录每条进行回表操作,获取到完整记录再返回给客户端.
    之前说过,同一个索引每层的相邻结点的数据页在分配时,会尽量使它们相邻(但不要求必须相邻),所以当读取这些二级索引记录的时候,代价还是较小的,能够一次将多个相邻页面的二级索引记录读取到内存中,但是执行回表操作时,二级索引记录的主键值是毫无规律的,这个时候我们要通过主键值去聚簇索引中寻找对应的完整记录,如果所在主键值所在的聚簇索引记录的数据页不在内存中,还需要去将数据页从磁盘读取到内存中,由于这些主键值很多都是并不连续的,这些聚簇索引记录又在不同的数据页中,就会导致大量的随机IO,如果我们这张t表里面95%的记录都满足该条件呢?这样还不如直接使用聚簇索引进行全表扫描,还省去了回表步骤,所以当需要查询的数据量越大的时候,二级索引所带来的消耗就越高.
    选择二级索引+回表还是聚簇索引全表扫描的方式来进行查询?这是由查询优化器来进行选择的,它会事先去计算该表的一些统计数据,然后利用这些统计数据或者访问表中的少量记录来计算需要回表的记录数目,如果数据量越大,则越倾向选择聚簇索引来进行查询,反之亦然.如果查询的数据量较少或者使用LIMIT子句来限制了查询条数,则会更倾向于使用二级索引+回表的方式.

更好的创建和使用索引#

  • 只为了搜索,排序,分组的列创建索引:没有必要为只出现在查询列的列创建索引

  • 考虑索引列中不重复值的个数:如果索引列的值容易重复,导致一个区间内有大量的二级索引记录,就会导致查询该区间记录的语句的回表次数变多.造成性能的损耗

  • 索引列的类型尽量小:再表示的范围允许的情况下,尽量让索引列使用较小的类型,因为如果索引列的占用空间较小,索引B+树里就能存储更多的索引记录,一次页面IO就能将更多的记录加载到内存中来.这样读写效率就更高了.并且这个道理对主键索引更加适用,因为不光聚簇索引需要存储下主键值,其他二级索引的结点也需要存储下一份主键值.如果主键适用更小的数据类型,就能省下更多空间.

  • 可以对字符串只用列前缀建立索引:一个字符串由多个字符组成,当采用utf8的字符集时,一个字符占用1~3个字节的空间,如果一个字符串非常长的话,那对这个字符串列创建索引占用的存储空间会非常大.我们这个时候可以采取只对前几位的字符进行创建索引:
    ALTER TABLE t ADD INDEX index_prex_key(key1(10))
    这样的话该二级索引记录里只需要保留该索引列的前十个字符,再查询的时候先通过前十个字符去定位对应的记录,在扫描这些二级索引记录的时候在判断是否满足对应列的条件,这样可以明显减少索引的大小.
    但是如果采用这种方式建立索引,想要使用该字段进行排序的话就无法利用该索引进行排序了,只能进行全表扫描+文件排序的方式,因为该二级索引中只存储了前缀.

  • 覆盖索引:当查询的列都在索引记录中,就无须回表进行读取完整的用户记录,能够直接读取二级索引中的记录值返回给客户端.
    比如我们有一个索引(part1,part2)
    执行该查询语句:

SELECT part1,part2
FROM t
WHERE part1=10;

因为part1和part2都在该二级记录中,他使用该索引进行查询之后,发现查询列都在索引列中,就会直接返回给客户端,而不需要进行回表操作.我们称这种索引叫做覆盖索引.

SELECT part1,part2
FROM t
ORDER BY part1;

遇到这种排序语句也是,即使没有LIMIT语句,但是可以采用覆盖索引来去除回表的损耗,所以查询优化器还是会采取二级索引来进行查询.

  • 让索引列以列名的形式在搜索条件中单独出现:在WEHRE子句中,尽量不要让一些运算逻辑或是操作在列名出现,比如'WHERE x*2>10'最好使用'WHERE x>5;'这样的方式.Mysql并不会去尝试去简化这些表达式,会直接认为这个搜索条件不能形成扫描区间来减少扫描的记录数目,会直接以全表扫描的方式来进行查询.

  • 新插入记录时主键大小对效率的影响: 我们可以知道表中的完整记录是存储到聚簇索引中的,如果聚簇索引记录经常在中间进行插入,会发生什么?如果数据页面空间满了,就会造成页分裂,还需要将记录移动到新的页中,造成无谓的损耗.但要是如果主键依次递增,每次都在最后新增,就能够减少无谓的消耗.

  • 避免创建冗余索引和重复索引:就比如对主键创建二级索引,或是对联合索引的第一列再次创建索引,我们应该避免


第八章 数据的家-MYSQL数据目录#

数据库和文件系统的关系#

数据库把表都存在磁盘上,而操作系统使用文件系统来管理磁盘,所以数据库想要对表的信息进行读取或是修改、新增,都是通过文件系统来进行操作.

MYSQL数据目录#

MYSQL服务器在启动之后进行读取的数据和存储的数据都会在某个目录下的文件之中,我们称该目录为MYSQL的数据目录.

安装目录主要用于存储许多控制客户端和服务端的命令(可执行文件等).而数据目录主要存储MYSQL运行中产生的数据.

通过SHOW VARIABLE LIKE 'datadir'命令可以确定MYSQL的数据目录位置.

数据目录的结构#

数据目录中主要存储的是:数据库,表,视图,触发器等用户数据与一些额外数据.
数据库在被创建时,会先在数据目录下创建一个对应的子目录,然后在该子目录下创建一个db.opt的文件,该文件记录了该数据库的一些属性(如字符集和比较规则等).
infomation_schema系统数据库是没有对应的子目录的,因为设计者对他进行的特殊处理.

表在文件系统中的表示#

表的信息主要有俩种:表结构的定义(如表名,数据类型,记录条数,约束,索引等信息)和表中的数据.
表结构信息(InnoDB和MYISAM)存储在对应数据库子目录下的表名.frm文件中.

表数据的存储#

InnoDB的表数据存储方式

InnoDB中索引即数据(索引叶子结点存储完整数据,并且数据页之间可以物理不连续,以双向链表维护页的顺序),以数据页为基本单位来管理存储空间.
InnoDB存储引擎的设计者提出了一种抽象概念:表空间
表空间可以对应多个文件系统中的真实文件,可以将表空间划分为多个页,表空间就存储在某个表空间下的某些页中.
表空间有分为多种:

  • 系统表空间:默认情况下,MYSQL的数据目录下有一个ibdata1的文件,这个文件就是对于的系统表空间.这个文件具有自扩展的特性,当空间不足时会自动增加大小.
    通过修改配置文件中的innodb_data_file_path全局服务器属性可以配置该系统表空间.一个服务器中只有一个系统表空间.
    在MYSQL5.5.7~5.6.5的版本中,表中的数据都会存到这个系统表空间中.
  • 独立表空间:MYSQL5.6.6及之后的版本,各个表的数据并不会存储到系统表空间了,而是存储到子目录下对应的独立表空间.一个表对应着一个独立表空间.
    文件名为表名.ibd.InnoDB引擎下子目录会有ibd文件和frm文件(不过frm文件在MYSQL8.0被移除了,合并在ibp文件中).
    可以通过innodb_file_per_tabl启动选项来控制新建的表使用系统表空间还是独立表空间来存储数据,该值为0是使用系统表空间,为1是使用独立表空间.
    使用该语句可以修改已创建表为哪种表空间方式存储ALTER TABLE 表名 [=] (innodb_file_per_table|innodb_system);
  • 其他类型:通用表空间,undo表空间,临时表空间,这些等后面用到再讲解.
MYISAM的表数据存储方式

MYISAM的索引都是二级索引,并且索引与完整数据分开存放,所以在文件系统中将索引和数据文件分开为不同的文件进行存放.
并且MYISAM是没有表空间的概念的.
MYISAM引擎下的子目录下文件有:frm(表属性文件),MYD(数据文件),MYI(索引文件)

其他文件#

  • 服务器进程文件:每运行一个MYSQL服务器程序,都意味着启动了一个进程,MYSQL服务器会将自己的进程ID写入到该文件.
  • 服务器日志文件:在运行中产生的日志文件,比如redo日志,查询日志,错误日志等
  • SSL和RSA证书和密钥文件

文件系统对数据库的影响#

MYSQL的数据都是存储在文件系统中的,所以MYSQL存储数据也会受到文件系统的制约.

  • 数据库名称和表名称不能超过文件系统所允许的最大长度
  • 特殊字符 MYSQL将数据库和表名中除数字和拉丁字母外的所有字符都映射成@+编码值的形式,并作为文件名.就是为了防止文件系统不支持某些特殊字符.
  • 文件长度受文件系统最大长度的限制

系统数据库#

  • mysql:存储MYSQL的用户账户信息,权限信息,一些存储过程和事件的定义信息,一些运行时的日志信息,一些帮助信息和时区信息
  • information_schema: 保存着MYSQL服务器维护的其他数据库的信息,如表、视图、触发器、列等一些描述性信息数据.也被称作元数据
  • performance_schema:MYSQL服务器运行时的状态,算是一个对服务器的性能监控,存储最近执行的语句,在执行过程中每个阶段花费的时间,内存的使用情况等。
  • sys: 将information_schema和performance_schema的信息结合起来,更方便浏览.

第九章 存放页面的大池子-InnoDB表空间#

表空间可以对应一个或者多个文件,而独立表空间对应着对应子目录下的ibd的实际文件.
像是当想要插入某条数据的时候,就会从这个表空间中寻找对应的页来写进去.

前景回顾#

页面类型#

类型名称(省略了FIL_PAGE前缀) 描述
ALLOCATED 最新分配还未使用
UNDO_LOG undo日志页
INODE 存储段的信息
IBUF_FREE_LIST change Buffer空闲列表
IBUF_BITMAP change Buffer的一些属性
SYS 存储一些系统数据
TRX_SYS 事务系统数据
FSP_HDR 表空间头部信息
XDES 存储区的一些属性
BLOB 溢出页
INDEX 索引页,也称数据页

页面通用部分#

不管是什么类型的页面都有相同的一部分,我们称这是页面通用部分,为File Header(大小38B 记录页面的一些通用信息)和File Trailer(大小8B 校验页是否完整).中间部分占16338B,这三个部分合起来是16KB.
File Header组成

名称 占用空间大小(字节) 描述
FiL_PAGE_SPACE_OR_CHKSUM 4 MYSQL版本<4.0.14时代表本页面表空间ID,之后的版本为页面的校验和
FIL_PAGE_OFFSET 4 页号
FIL_PAGE_PREV 4 上一个页的页号
FIL_PAGE_NEXT 4 下一个页的页号
FIL_PAGE_LSN 8 页面最后被修改时对应的LSN(日志序列号)值
FIL_PAGE_TYPE 2 页面类型
FIL_PAGE_FILE_FLUSH_LSN 8 仅在系统表空间的第一个页中定义,代表文件至少刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 页属于哪个表空间

独立表空间结构#

区的概念#

我们前面可以知道,一个表空间可以对应一个或者多个页,可是一般表空间中的页十分之多,毕竟一个页16KB,而一个表可能数据就有几十几百MB,那会有几千几万个页.
为了更加方便管理这些页,INNODB的设计者提出了一个区的概念,对于16KB的页来说,连续的64个页就为一个区.一个区为1MB大小.

系统表空间和独立表空间都是由许多个连续的区组成的.而256个区又被划分为一组(256MB).

第一个组最开始的三个页面的类型是确定的,即FSP_HDR(登记整个表空间的整体属性和记录本组所有的区,一个表空间只有一个该页面)、IBUF_BITMAP(存储关于CHANGE BUFFER的一些信息)、INODE(存储了INODE Entry的数据结构).

其他组的最开始俩个页面类型是确定的:XDES(extent descriptor,用来登记本组256个区的属性),IBUF_BITMAP(存储关于CHANGE BUFFER的一些信息)

64页->1区 256区->1组

段的概念#

我们可以先来想想为什么已经有页为单位了,还需要引入区呢?
我们在查询数据的时候,本质上是对索引B+树进行查询,我们前面说过,数据页在物理空间上可以并不连续,可以通过FIL_PAGE_PREV和FIL_PAGE_NEXT来串联成一个双向链表.而查询本质上是寻找到符合条件的第一条数据,而后往后进行遍历,但是如果一个表的数据量大了起来,有许多页都是物理空间非连续的,
那对于传统的磁盘来说,就得重新定位磁头位置,产生随机IO,这对性能产生影响,所以我们要尽量让一个树中页面链表相邻的页面在物理空间也连续,使大量记录查询时变成顺序IO.

当数据量到达一定程度时,为某个索引分配空间就不会按照页来进行分配,而是直接按照区来进行分配了.甚至数据非常多的时候会一次性分配多个连续的区,虽然会造成一定的空间损失(会出现一定空间没有数据),不过对比起消除了大量的随机IO来说利大于弊.

而一个索引树里,会对叶子节点和非叶子节点存放在不同的区中,我们称存放叶子节点的区为一个段,而存放非叶子节点区的集合也算是一个段.一个索引有俩个段(非叶子节点段和叶子节点段).

为什么要将索引树区分成俩个段来存储呢?
我们可以先从索引的查询过程来分析,首先根据查询条件,通过非叶子结点来寻找到下一个符合条件的数据页,而一般访问顺序是根据层级往下遍历的,一般产生的都是随机IO,而我们将多个连续的数据页存放在区中,以区来作为分配单位,是为了减少随机IO的次数,对于非叶子结点作用较少.并且当数据当数据增长而需要给他分配新的区时,如果不对叶子节点和非叶子节点进行区分的话,将他们一起存放到一片区中,就可能会造成叶子节点之间间隔着非叶子节点,这可能会造成随机IO次数的增加.

我们可以知道,一个区大小为1MB,而一个表里至少会有一个聚簇索引树,一个树有俩个段,那么难道对于一个没存有多少数据的表,也要占用2MB的空间吗?并且每加一个索引还要多占用2MB空间.这非常浪费.

针对这种情况,InnoDB设计者又提出了一个概念:碎片区
在一个碎片区中并不是一整个区都是为了存储一个段的数据,可以有些页存储段A的数据,而有些页存储段B的数据,或者不属于任何段.
碎片区直属于表空间.
而引入了碎片区的概念,我们在插入数据的时候为段分配存储空间的时候的策略是这样的:

  • 当表的数据少于32个页时,直接在碎片区中以单个页面为单位来进行分配.
  • 而当占用32个页之后,再次分配存储空间会以区(64页)为单位来进行分配.但是并不会将原本存在于碎片区的数据复制到新的区中.

段能够对应一些存在于碎片区的零碎页或是完整的区的集合.

区的分类#

状态名 含义
FREE 空闲的区
FREE_FRAG 有剩余空闲页面的碎片区
FULL_FRAG 没有剩余空闲页面的碎片区
FSFG 附属于某个段的区

除了FSFG,其他的状态都是直属于表空间的.

而为了更加方便的管理这些区,又有了XDES Entry(extent description Entry)的结构用于记录区的一些属性.
每一个区都对应着一个XDES Entry.

XDES Entry结构组成部分 占用字节数 描述
Segment Id 8 该区所属的段编号
ListNode 12 可以将XDES结构串联在一起形成链表
State 4 区的状态:比如上述的 FREE,FSFG等
Page State BitMap 16 一个区有64页,16个字节有128位,每个页顺序使用其中的俩位,其中第一位表示对应的页是否空闲
List Node结构(Page Number和Offerset组合就是能够指向前一个或是后一个XDES结构的指针) 占用字节数
Prev Node Page Number 4
Prev Node Offerset 2
Next Node Page Number 4
Next Node Offerset 2
XDES ENTRY链表

我们引入区、段、碎片区、附属于段的区、XDES Entry结构等概念,就是为了让查询的时候减少IO次数,又不至于让表的数据少的时候浪费空间.我们可以通过XDES Entry来快速访问对应区域的XDES Entry结构,比如我们想访问extent 0(首个区)的XDES Entry结构,就可以直接通过偏移量150字节(File Header38+)
我们可以先明确下向段中插入数据,申请新页面的过程:

  • 如果表中数据较少,会先查找是否有FREE_FRAG状态下的区,然后从该区中寻找到一个空白的数据页进行数据插入.
  • 如果没有FREE_FRAG状态的区,则会去表空间中申请一个状态为FREE的区(空闲的区),而后再将该区的状态变为FREE_FRAG,然后再从该新区中取一个零散页存入数据.
  • 之后在不同的段需要使用到零散页(即数据较少的段进行数据插入时),就会从该区中取出一个零散页来使用,直到该区没有空闲页面,则就将该区的状态更改为FULL_FRAG.
    可是我们怎么直到某个区的状态为FREE_FRAG或是FREE呢?当表空间的数据量大到一定程度,区的数量级可能有成千上万,我们不能将每个区的XDES ENTRY都遍历一遍吧?
    所以我们现在可以利用ListNode,根据某些条件将区给串联起来形成一个链表,将FREE,FREE_FRAG,FULL_FRAG状态的页各自串联起来形成链表.
    当想要使用对应状态的区时,就直接从该链表的头结点去获取对应的区,比如想要一个FREE_FRAG的页,直接去FREE_FRAG链表中取出头结点,当这个区对应的页中没有空闲页了,就修改该页的STATE状态,然后移动到FULL_FRAG链表中.
    如果FREE_FRAG链表中没有结点了,则从FREE链表中取出页,将这个页的状态修改为FREE_FRAG并移动到FREE_FRAG链表中.再进行数据插入.

但还有一种情况:当段的数据量到达一定程度(即占用了32个零散页时),我们则要以页为单位来进行申请空间,哪些页属于这个段?难道再遍历所有的XDES ENTRY结构吗?
这种情况我们是不可能去遍历的,这个问题同样可以使用XDES Entry结构来解决,我们可以根据段号来建立各自的链表,但是分配空间的时候还需要知道该区中的页面数据占用情况,如该区中的页面是否都是空闲的,或是该区中是否还有空闲的页面,或是该区中的页面已满没有空闲页面.
所以我们需要通过页面占用情况再细分,为每个段创建对应的链表:

  • FREE 链表
  • NOT NULL 链表
  • FULL 链表

这样每个段都有其对应的三个链表了.一个索引就对应的六个链表.加上前文直属于表空间的三个链表,独立表空间(一个表对应一个独立表空间)总共需要维护九个链表.

独立表空间(一个表对应一个独立表空间)->段(一个索引为俩个段)->区(一个段对应数个碎片页或是区,一个区占有64页)->页(一个页16KB)->记录(一个页中至少有俩条以上数据)
其中还有组(1组=256区)等概念

链表基结点

前面介绍了那么多链表,有指向全为空闲碎片区的,有空余区域碎片区的,无空闲区域碎片区的.还有指向归属于段的区的链表,如FREE,NOT_NULL,FULL链表.我们可以通过这些链表去寻找到对应区的XDES_ENTRY的位置,但是我们如何去寻找这些链表呢?
Innodb的设计者设计了一个名为List Base Node(链表基结点)的结构,用于存储链表头结点和尾结点的指针,以及该链表中节点的个数.

名称 占用空间 描述
List Length 4 字节 该链表的节点数
First Node Page Number 4字节 和First Node Offset组成XDES ENTRY链表头结点的指针(在表空间的位置)
First Node Offset 2字节
Last Node Page Number 4字节 和Last Node Offset组成XDES ENTRY链表尾结点的指针(在表空间的位置)
Last Node Offset 2字节

该结构会被放在表空间的固定位置,每次需要去定位链表的时候直接访问即可.

链表小结:

一个表空间由多个区组成,而其中每个区都有一个其对应的XDES Entry结构.根据XDES Entry结构区的STATE字段可以将区分为:FREE,FREE_FRAG,FULL_FRAG,FSEG四种状态,
其中除了FSEG属于某个段,其他三种区都是直属于表空间的碎片区,由表空间来处理和使用.而FSEG状态的区时所属于段的,每个段中的FSEG状态的区会根据区的情况来分成FREE,NOT_NULL,FULL这三种状态的XDES Entry链表.
而每一个XDES Entry链表都有一个对应的List Base Node结构(链表基结点)来记录其链表的头结点和尾节点与节点个数,用来定位和管理对应的链表.

段的结构#

段是一种抽象概念,对应着一些零碎的页面和区.而一个索引对应着俩个段,分别是用于存储叶子结点的和非叶子结点的段.
Inode Entry:是一种记录段属性的的结构.

名称 占用字节数 描述
Segment Id 8 该InnoDB Entry结构所对应的段id
NOT_FULL_N_USED 4 在NOT_FULL链表中已经使用了多少页面
List Base Node FOR FREE LIST 16 对应着FREE链表的基结点
List Base Node FOR NOT NULL LIST 16 对应着NOT NULL链表的基结点
List Base Node FOR FULL LIST 16 对应着FULL链表的基结点
MAGIC NUMBER 4 魔法值,用来表示该InodeEntry结点是否已初始化,为97937874则已初始化
FRAGMENT ARRAY ENTRY 0 4 每一个该结构对应一个零碎页,代表一个零碎页的页号
FRAGMENT ARRAY ENTRY 1 4 .....
....
FRAGMENT ARRAY ENTRY 31 4 当页面总数为32个时,用区作为单位分配

各页面详细情况#

我们上面介绍了表空间,段,区,XDES Entry,Inode Entry还有以XDES Entry为结点的链表等概念,那这些结构在表空间中是怎么存储的?存储在数据页的什么地方?以什么方式存储?
我们接下来就是要解决掉这些疑惑.
我们可以从不同类型的页来解决这些问题.

FSP_HDR类型

在表空间中的首个组的首个页面就是FSP_HDR类型的页面.该页面存储了表空间的一些整体属性和第一个组内256个区的对应的XDES Entry结构.

FSP_HDR页面的组成:

名称 中文名称 占用空间大小(字节) 简单描述
File Header 文件头部 38 页的一些通用信息
File Space Header 表空间头部 112 表空间的一些整体信息
XDES Entry 区描述信息 10240 存储本组中的256个区的属性信息
Empty Space 尚未使用空间 5986 用于页结构的填充,没有实际意义
File Trailer 文件尾部 8 校验页是否完整

对于这个页面我们主要需要关注的主要部分是File Space Header(表空间头部)和XDES Entry.

File Space Header
名称 占用空间大小(字节) 描述
Space Id 4 表空间ID
Not Used 4 未被使用,可以忽略这一部分
Size 4 当前表空间拥有的页面数
Free Limit 4 尚未被初始化的最小页号,大于或者等于这个页号的区对于的XDES Entry结构都还没加入Free链表
Space Flags 4 表空间的一些占用存储空间比较小的属性
Frag_N_USED 4 FREE_FRAG链表中已经使用的页面数量
List Base Node for Free List 16 Free链表的基结点
List Base Node for Free_Frag List 16 Free_Frag链表的基结点
List Base Node for FULL_Frag List 16 FULL_Frag链表的基结点
Next Unused Segment ID 8 当前表空间下一个未使用的SegmentId
List Base Node for SEG_INODES_FULL List 16 SEG_INODES_FULL链表的基结点
List Base Node for SEG_INODES_FREE List 16 SEG_INODES_FREE链表的基结点
  • List Base Node for Free List,List Base Node for Free_Frag List,List Base Node for FULL_Frag List: 这三者指向直属与表空间的碎片区链表的链表基结点,我们可以通过这个基结点来快速定位直属于该表空间的碎片区.

  • Free Limit: 我们可以设想,如果一个表空间刚刚建立,需要对表空间进行初始化,而对应磁盘文件的大小是可以指定的.那么对表空间中的区的XDES Entry、Innode Entry等结构一开始进行初始化,在初始化之后还有一定空闲空间.那对于这些空闲空间要怎么处理呢?是要一开始将他们全部加入到FREE链表中,还是只加入一部分,当需要更多的区域的时候(空闲链表中的区不够使用)再进行加入呢?
    InnoDB这采用的是第二种办法,一开始只把一些空闲区域加入到Free链表,当需要使用更多的时候再将剩余空闲区域加入到空闲链表之中.而Free Limit就为我们表示已经被加入到Free链表中的页号,在该页号值之后的区都没有被加入到Free链表.

  • Next Unused Segment ID: 表中的每个索引都对应俩个段,而每个段都有一个唯一的ID值.当每次新增索引之后,就直接使用这个值,而后再对这个值进行递增.

  • Space Flags: 表空间中布尔类型相关的属性或是占用空间较少的属性.

    标志名称 占用空间大小(比特) 描述
    POST_ANTELOPE 1 表示文件格式是否在ANTELOPE格式之后
    ZIP_SSIZE 4 表示压缩页面的大小
    ATOMIC_BLOBS 1 表示是否自动把占用存储空间非常多的字段放到溢出页中
    PAGE_SSIZE 4 页面大小
    DATA_DIR 1 表示表空间是否是从数据目录中获取的
    SHARED 1 是否为共享表空间
    TEMPORARY 1 是否为临时表空间
    ENCRYPTION 1 表空间是否加密
    UNUSED 18 没有使用到的比特
  • List Base Node for SEG_INODES_FULL List , List Base Node for SEG_INODES_FREE List:每个段对应的Inode Entry结构会被集中存放在INODE类型的页中,但是如果一个表空间的段特别多呢?多到一个页面存不下所有的INode Entry结构.
    这俩个属性就是为了解决这种情况.这种情况会再创建一个页面来存储Inode Entry结构,并且构成并维护俩个链表,SEG_INODES_FULL List该链表中的Inode类型页面已经没有空余空间来放置Inode Entry数据了.SEG_INODES_FREE List该链表中的Inode类型页面仍有空间存放Inode Entry数据.

XDES Entry

FDR页面中的XDES Entry部分,因为第一个页面空间有限,一个XDES Entry结构占用40个字节,所以我们将256个区划分为1组,在每组的第一个页面存放组中256个区的XDES Entry结构.这里的XDES Entry部分是按照区的顺序来存放的,如果我们想要访问对应区域的XDES结构,可以直接通过偏移量访问,比如访问extent 0所对应的XDES Entry结构,可以直接通过该页面150字节(FileHeader+FileSpaceHeader)的偏移量来访问.

XDES类型

我们之前在表空间第一个组中的第一个页面那个FSP_HDR类型的页面中存储了第一组中的所有区的XDES Entry信息,但是一个表空间只需要有一个FSP_HDR类型的页面来存储表空间的一些属性,其他组的XDES Entry信息应该存储再哪呢?
这里单独拿出了一种XDES类型的页面来存储本组的XDES Entry结构,该类型的页面只存储了本组的XDES Entry,而没有存储一些表空间相关的属性.

名称 占用空间
File Header 38B
没有使用 112B
XDES Entry 0 40B
... ...
XDES Entry 255 40B
Empty Space 5986B
File Trailer 8B
IBUF_BITMAP类型

每个分组的第二个页面就是IBUF_BITMAP类型的页面,这个页面主要记录了和Change Buffer有关的内容.
插入数据主要是对该表的聚簇索引和二级索引的B+树进行数据插入,但是数据所在的位置是随机的,会导致大量的随机IO.
主要是当对某个未在磁盘上的数据进行更新,删除或是插入操作时,会先将对应的操作暂时缓存到change Buffer中,之后当服务器空闲或是存放该数据的页面被加载到了内存上时,再将修改合并到对应页面.

INODE类型

在一个表空间的第一个组的第三个页面就是Inode页面了,Inode页面主要用来存储Inode Entry段的信息
Inode页面的构成

名称 中文名 占用空间大小(字节) 简单描述
File Header 文件头部 38 页的一些通用信息
List Node for Inode Page List 通用链表节点 12 存储上一个Inode页面和下一个Inode页面的指针
Inode Entry 段描述信息 16320 具体的Inode Entry结构
Empty Space 尚未使用空间 6 用于页结构的填充
File Trailer 文件尾部 8 校验页是否完整
  • Inode Entry : 主要存储了对应段的一些信息与段中不同状态区的链表基结点和零碎页.一个Inode Entry占用192字节,一个页面中可以存储85个这样的结构.
  • List Node for Inode Page List : 我们上面说了一个页面最多可以存储85个InodeEntry结构,如果要是一个表空间中存在的段超过这个数呢?要怎么存储?这个时候我们需要再创建一个Inode类型页面来存储InodeEntry结构,这个部分就是为了让多个Inode页面可以串联起来形成一个链表,而在FSP_HDR页面中还有俩个部分:List Base Node for SEG_INODES_FULL List 、List Base Node for SEG_INODES_FREE List
    这俩个属性主要存储的就是Inode页面链表的基结点。
    SEG_INODES_FULL:存储的Inode页面已没有空闲空间来存储新的InodeEntry结构.
    SEG_INODES_FREE:存储的Inode页面还有空闲空间来存储新的InodeEntry结构.
    每当以后对该表进行创建索引,需要创建对应的段,先看看SEG_INODES_FREE链表中是否还有页面,如果没有则去表空间申请一个页面,将其类型设置为Inode,放到SEG_INODES_FREE链表中,然后将新建段的InodeEntry放到该页面中。但是如果SEG_INODES_FREE链表还有页面,就会取出该页面将InodeEntry放到该页面中,如果该页面无剩余空间就把该页面放到SEG_INODES_FUL页面之中。
Segment Header结构

InodeEntry结构我们有了一定了解,并且一个索引对应俩个段,也就是俩个Inode Entry结构,我们怎么去某个段对应的InodeEntry结构呢?
数据页(Index类型的页)中有一个Pager Header部分,其中有俩个部分为:PAGE_BTR_SEG_LEAF(B+树叶子节点段的头部信息),PAGE_BTR_SEG_TOP(B+树非叶子节点段的头部信息)
这俩者都使用同一种结构 Segment Header

名称 占用空间大小(字节) 描述
Space Id of the Inode Entry 4 Inode Entry结构所在的表空间ID
Page Number of the Inode Entry 4 Inode Entry结构所在的页面页号
Byte Offset of the Inode Entry 2 Inode Entry 结构在该页面的偏移量

这样当一个索引想寻找他所对应非叶子段或是叶子段的Inode Entry结构,就可以直接根据根节点数据页上存储的Space Id of the Inode Entry 寻找到表空间Id,然后再根据Page Number of the Inode Entry 寻找到在表空间中的页号,最后使用Byte Offset of the Inode Entry寻找到对应InodeEntry的具体位置.
要注意,因为一个索引只对应俩个段,所以只需要在根节点的页面上存储InodeEntry所在位置和索引的关联关系即可.

系统表空间#

整个Mysql进程只有一个系统表空间.
系统表空间中比独立表空间多记录了一些与整个系统相关的信息.它的表空间ID是0.

整体结构#

独立表空间第一个组的前三个页面同样是:FSP_HDR,IBUF_BITMAP,INODE类型的页面
但是页号3~7的页面是系统表空间所独有的.

页号 页面类型 英文描述 描述
3 SYS Insert Buffer Header 存储 Change Buffer 的头部信息
4 INDEX Insert Buffer Root 存储 Change Buffer的根页面
5 TRX_SYS Transaction System 事务系统的相关信息
6 SYS First Rollback Segment 第一个回滚段的信息
7 SYS Data Dictionary Header 数据字典头部信息

系统表空间的extent 1(64~127页)和 extent 2(128~191页)被称为Doublewrite Buffer(双写缓冲区).

InnoDB数据字典

我们使用Insert语句插入插入的数据叫做用户数据,Mysql作为软件为我们保管这些用户数据,并且对我们提供一些便于增删改查的接口.但是在使用的过程中,Mysql可能要直到,客户端命令中所使用的表是否存在?表中是否有命令中的该列?表中的列所对应的数据类型是什么?该表中又有什么索引?
这些信息都存储在Mysql的系统表空间中,除了保存我们的用户数据之外,还需要存储一些额外的数据来帮助我们管理这些用户数据.
我们也称这些数据为元数据.这些数据存储在一系列内部系统表中(internal system table)

内部系统表

内部系统表的种类:

表名 描述
SYS_TABLES 整个InnoDB存储引擎中所有表的信息
SYS_COLUMNS 整个InnoDB存储引擎中所有列的信息
SYS_INDEXES 整个InnoDB存储引擎中所有索引的信息
SYS_FIELDS 整个InnoDB存储引擎中所有索引对应列的信息
SYS_FOREIGN 整个InnoDB存储引擎中所有外键的信息
SYS_FOREIGN_COLS 整个InnoDB存储引擎中所有外键所对应的列的信息
SYS_TABLESPACES 整个InnoDB存储引擎中所有表空间的信息
SYS_DATAFILES 整个InnoDB存储引擎中所有表空间对应的文件系统的文件路径信息的信息
SYS_VIRTUAL 整个InnoDB存储引擎中所有虚拟生成的列的信息

这些系统表也被称为数据字典,以B+数形式存储在系统表空间的某些页面中.
在这些内部系统表之中,我们需要比较关注的是SYS_TABLES,SYS_COLUMNS,SYS_INDEXES,SYS_FIELDS这四个内部系统表.我们称这四张表为基本系统表.

  • SYS_TABLES表 :该表中主要存储存储引擎中的所有表的一些信息,比如:表名称 表ID 表所拥有的列数 表的类型 表的一些额外属性 表所属的表空间ID等信息.
    该表有俩个索引:以NAME列为主键的聚簇索引,以ID列为索引的二级索引

  • SYS_COLUMNS表:该表中主要存储存储引擎里所有列的信息,主要存储: 该列所属表的ID 该列在表中的位置 该列的名称 该列的数据类型 精确数据类型(修饰主数据类型的东西,比如是否允许NULL值等等) 该列最多占用存储空间的字节数 该列的精度(默认值为0,似乎没有使用)
    该表只有一个聚簇索引,以(TABLE_ID,POS)列为主键

-SYS_INDEXES表:该表主要存储存储引擎里所有的聚簇索引的信息,比如:索引所在的表的ID 索引ID 索引名称 索引中列的个数 索引类型 索引根节点所在的表空间ID 索引根节点所在的页面号 合并比例(页面的记录被删除的一定比例会和相邻页面进行合并)
该表只有一个聚簇索引:(TABLE_ID,ID)为主键

-SYS_FIELDS表: 该表存储了所有索引所包含的列的信息,比如:索引列所对应的索引ID 该列在索引中的位置 该列的名称
聚簇索引(INDEX_ID,POS)

Data Dictionary Header 页面

有了上述的四个基本表,我们就可以通过这些表去获取其他系统表和用户定义的表的所有元数据了.我们可以根据这些基本表去逐一寻找到一张表的完整描述信息.
而这四张表的元数据, 即有哪些列?有哪些索引?这些表的B+树根节点的位置?等等这些信息去哪里获取呢?
这些信息无法再存储到别的表中,只能在代码中进行硬编码,并拿出一个固定的页面来进行存放这些信息.
该页面就是页号为7的页面,类型为SYS,记录了Data Dictionary Header(数据字典头部信息).
该页面除了存储这些四个基本表的元数据外,还存储了一些存储引擎的全局属性.

  • 页号为7的页面构成:
名称 占用空间大小(字节) 简单描述
File Header 38 页的一些通用信息
Data Dictionary Header 52 数据字典头部,记录了一些基本系统表的根页面位置和Innodb存储引擎的一些全局属性
Unused 4 未使用
Segment Header 10 段头部
Empty Space 16272 用于页结构的填充
File Trailer 8 校验页完整

该页面有Segment Header信息,说明设计者是将这个页面来当做段的一个碎片页来存储信息的.

Data Dictionary Header构成:

  • MAX ROW ID:我们知道, 如果我们没有对一张表显式的定义主键,会自动选择Unique约束的列或是自动创建一个row_id列充当主键,如果我们让row_id为主键,那么该主键的值是怎么得出的呢?主键只要求一张表中的数据不重复就行,但是这里采取的是另一种处理方式, 无论是哪个拥有row_id列的表,插入新纪录时,都是使用该属性的值,而后再对该属性自增1,也就是说MAX Row Id是全局共享的.但是并不是每次分配一个row_id都会将该MAX ROW ID值刷入到磁盘中.
  • MAX Table Id:Innodb中每一张表都有其唯一的ID,每次新建一个表就会将该值+1,然后将其作为该表的ID.
  • MAX Index Id:Innodb中每一个索引都有其唯一的ID,每次新建一个索引就会将该值+1,然后将其作为该索引的ID.
  • MAX Space Id:Innodb中每一个表空间都有其唯一的ID,每次新建一个表空间就会将该值+1,然后将其作为该表空间的ID.
  • Mix ID Low :该字段没啥用,跳过
  • Root of SYS_TABLES clust index:表示SYS_TABLES表聚簇索引的根页面的页号.
  • Root of SYS_TABLE_IDS sec index:表示SYS_TABLES表为ID列建立的二级索引的根页面的页号.
  • Root of SYS_COLUMNS clust index:表示SYS_COLUMNS表聚簇索引的根页面的页号.
  • Root of SYS_INDEXES clust index:表示SYS_INDEXES表聚簇索引的根页面的页号.
  • Root of SYS_FIELDS clust index:表示SYS_FIELDS表聚簇索引的根页面的页号.
information_schema 系统数据库

用户并不能直接访问InnoDB的这些内部系统表,但是设计者考虑到用户可能会需要使用到这些表的聂荣,或是有助于大家分析问题,所以在information_schema中提供了一些以InnoDB_SYS开头的表来让大家查看这些信息.
当然,information_schema中的并不是真正的内部系统表,而是在启动存储引擎的时候读取的这些内部系统表,再填充到这些InnoDB_SYS开头的表中.俩者字段并不完全一样.

posted @   况况况  阅读(105)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示
主题色彩