读书笔记 | Mysql是怎样运行的

一、mysql常用命令

1. 查看表的基本信息show table status

• 使用show table status like tableName可以查看指定表的基本信息,包括存储引擎,行格式等

二、InnoDB记录存储结构

InnoDB中数据的存储是在磁盘上的,而数据处理的过程则在内存中.InnoDB将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDb中页的大小一般为16KB

1. InnoDB行格式

1-1. InnoDB中的行格式类别

1. compact,version 5.6 默认使用
2. redundant,
3. dynamic,version 5.7默认使用
4. compressed

1-2. 如何指定行格式

• CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
• ALTER TABLE 表名 ROW_FORMAT=行格式名称

1-3. 各种行格式的区别

1-3-1. compact行格式

• compact行格式的内容:变长字段?度列表 + NULL列表 + 记录头信息 + 列值

1-3-1-1. 变长字段?度列表

在mysql中,变长字段中存储多少字节的数据是不固定的,所以在存储时,需要将这些数据占用的字节数也存起来,所以这些变长字段占用的存储空间分为两部分:
1.真正的数据内容
2.字段的长度
在compact行格式中,把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位,从而形成一个变长字段长度列表,各变长字段数据占用的字节数按照列的顺序逆序存放.注意,如果某个字段内容为null,是不会放入这个列表中的.
字段的长度用1或2个字节表示,具体规则如下:

• 当字段最大长度 <= 255字节时用一个字节表示
• 字段最大长度 > 255,但实际使用字节 <= 127,也使用1个字节表示
• 其他情况使用2个字节表示

1-3-1-2. NULL列表

• 存储每条记录中允许为NULL的字段,将实际为NULL的字段用1表示,实际不为NULL的字段用0表示,也是逆序存放,每个允许为NULL的列用一个位来表示,如果记录中所有的列都不允许为null,则null列表就不存在.
• MySQL规定NULL值列表必须用整数个字节的位表示,如果使用的二进制位个数不是整数个字节,则在字节的高位补0.

1-3-1-3. 记录头信息

• 记录头信息由5个字节组成,也就是40个二进制位

1-3-1-4. 列值

• MySQL自动添加的隐藏列
  MySQL会为每个记录添加一些隐藏列,如DB_ROW_ID,DB_TRX_ID,DB_ROLL_POINTER,具体情况如下:

• 行溢出数据
  • 在compact和redundant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据分散存储在几个其他的页中,然后记录的真实数据处用20个字节存储指向这些页的地址,dynamic行格式对于非常大的数据,会把它的全部数据存储到其他页中,然后在记录的真实数据处存储这些页的地址

1-3-2. redundant行格式

redundant行格式是MySQL5.0之前用的一种行格式,字段长度偏移列表 + 记录头信息 + 列值

1-3-2-1. 字段长度偏移列表

redundant行格式会把记录中所有列的长度信息都按照逆序存储到字段长度偏移列表中.而且存储长度的方式是采用相邻数值的差值来计算各个列值得长度

1-3-2-2. 记录头信息

1. redundant行格式多了n_field和1byte_offs_flag
   • n_field:表示记录中列的数量
   • 1byte_offs_flag:标记字段长度偏移列表中的偏移量是使用1字节还是2字节表示的
2. redundant行格式没有record_type属性

1-3-2-3. redundant中null的处理

• 如果变长数据类型的列值为null,则在字段长度偏移列表中记录偏移量为0即可,也就是会重复上一个偏移量的值,不用占用记录的真实数据部分
• 如果存储null值的字段是char(M)数据类型的,则占用记录的真实数据部分,并把该字段对应的数据使用0x00字节填充.

1-3-2-4. 列值和compact类似

1-3-3. dynamic行格式和compressed行格式

• 这两种行格式类似于compact行格式,只不过在处理行溢出数据时有点不一样,它们把该列的所有字节都存储到其他页上,只在记录的真实数据处存储其他页的地址,另外compressed行格式会采用压缩算法对页面进行压缩.

2. 通过表的真实存储文件读取行数据信息

• 找到mysql中test数据库的存储位置
  • /usr/local/mysql/data/test/
  • 有些文件夹默认是无法访问的,需要使用sudo chmod 755 fileName授权

1. 创建数据库并添加两条数据

CREATE TABLE record_format_demo (
     c1 VARCHAR(10),
     c2 VARCHAR(10) NOT NULL,
     c3 CHAR(10),
     c4 VARCHAR(10)
 ) CHARSET=ascii ROW_FORMAT=COMPACT;
 
INSERT INTO record_format_demo(c1, c2, c3, c4)
VALUES (‘aaaa‘, ‘bbb‘, ‘cc‘, ‘d‘),
       (‘eeee‘, ‘fff‘, NULL, NULL);

2. idb文件中真实的数据

插入了两条数据,这时只有一个索引页
通过查找 73 75 70 72 65 6D 75 6D 即supremum关键字,其后面的数据就是第一条数据的开始位置.又因为插入的第一条数据的最后一个列是d,对应的l6进制是64,可找到第一条数据的结束位置.然后再后面就是第二条数据的开始位置
第一条数据
01 03 04 00 00 00 10 00 2D 00 00 00 00 02 00 00 00 00 00 4B 09 A9 00 00 01 1D 01 10 61 61 61 61 62 62 62 63 63 20 20 20 20 20 20 20 20 64
第二条数据
03 04 06 00 00 18 FF C2 00 00 00 00 02 01 00 00 00 00 4B 09 A9 00 00 01 1D 01 1E 65 65 65 65 66 66 66

3. compact行格式解析
   • 解析可变字段长度列表
     • 因为表中只有c1,c2,和c4是可变长度,且其最大长度不超过255,所以这三个列都是用一个字节来表示其长度.
     • 第一条记录中这三个位置都不为null,所以这个记录以c1,c2,c4三个数据的长度的倒序开始,也就是010304,
     • 第二条记录中c4是null,所以这个记录以c1,c2列数据的长度倒序开始,也就是0304
   • 解析NULL列表
     • 因为表中只有c1,c3,c4列是可以为null的,所以null列表只需要三个位就能够记录,所以只需要一个字节就可以表示,且字节的前五个位自动补0
     • 第一条记录中c1,c3,c4都不为空,对应为000,倒序之后还是000,自动补0后为00000 000,对应的字节为0x00
     • 第二条记录中c3,c4为null,对应为011,倒序之后110,自动补0后为00000110,对应的字节为0x06
   • 解析记录头信息(5个字节)
   • 解析列值
     • 因为该表没有显式的设置主键,且没有unique的列,所以自动生成一个row_id列
     • row_id列 6字节
     • transaction_id 6字节
     • roll_point 7字节
     • 自定义的列,先判断是否在null列表中,在就直接跳过,不在null列表中的话,在看是否是定长,如果是定长,直接向后数指定的长度个字节,如果不是定长,再看可变字段长度列表中相应位置的大小,数相应长度个字节.

3. InnoDB数据页结构