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

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

• 一、mysql基本知识
  • 1. 查看表的基本信息show table status
  • 2. MySQL数据类型的大小
  • 3. update的大致流程
• 二、InnoDB记录存储结构
  • 1. InnoDB行格式
    • 1-1. InnoDB中的行格式类别
    • 1-2. 如何指定行格式
    • 1-3. 各种行格式的区别
      • 1-3-1. compact行格式
        • 1-3-1-1. 变长字段⻓度列表
        • 1-3-1-2. NULL列表
        • 1-3-1-3. compact记录头信息5字节
        • 1-3-1-4. 列值
      • 1-3-2. redundant行格式
        • 1-3-2-1. 字段长度偏移列表
        • 1-3-2-2. redundant记录头信息6字节
        • 1-3-2-3. redundant中null的处理
        • 1-3-2-4. 列值和compact类似
      • 1-3-3. dynamic行格式和compressed行格式
  • 2. InnoDB数据页结构
    • 2-1. 常见的数据页类型
    • 2-2. 索引页
      • 2-2-1. 索引页结构
        • 2-2-1-1. File Header文件头部38字节
      • 2-2-2. Page Header页面头部56字节
        • 2-2-2-1. 页目录Page Derectory
        • 2-2-2-2. File Trailer文件尾部8字节
• 三、通过表的真实存储文件读取行数据信息
• 四、B+树索引
  • 1. InnoDB中的索引方案
    • 1-1. 目录项记录和普通用户记录的不同
    • 1-2. 聚簇索引
    • 1-3. 二级索引
    • 1-4. 联合索引
    • 1-5. B+树索引是如何生成的
  • 2. MyISAM中的索引方案
  • 3. 创建索引和删除索引
  • 4. B+树索引的使用
    • 4-1. 索引的代价
    • 4-2. 哪些查询会用到索引
    • 4-3. 使用索引时要注意的事项
• 五、MySQL的数据目录
  • 1. 创建数据库时MySQL做了什么
  • 2. 创建的表在文件系统中如何表示
    • 2-1. 视图在文件系统中的表示
    • 2-2. 其他的文件
  • 3. 操作系统对数据库的限制
• 六、Buffer Pool
  • 1. 如何判断一个页是否已经加载到buffer pool中了
  • 2. Buffer Pool的内部组成
  • 3. free链表
  • 4. flush链表
  • 5. LRU链表
    • 5-1. LRU链表的优化
      • 5-1-1. 对预读机制的优化(初次加载到old区域的头部)
      • 5-1-2. 对全表扫描的优化(间隔1S后再次访问才算热点数据)
      • 5-1-3. 对young区域数据的优化(对young区前1/4数据的访问,不用把它们再次放到young区的头部)
  • 6. 刷新脏页到磁盘
• 七、redo log
  • 1. 什么是redo log
  • 2. 不使用redo log有什么问题
  • 3. redo log buffer
  • 4. redo log buffer刷盘的时机
  • 5. redo log磁盘中存放的位置
  • 6. redo log的结构
  • 7. redo log的优势
  • 8. 日志序列号LSN(Log Sequeue Number)
  • 9. redo日志崩溃恢复
    • 9-1. 确定恢复的起始lsn和结束lsn
    • 9-2. 如何恢复
• 八、undo log
  • 1. undo日志的类型与格式
    • 1-1. insert操作对应的undo日志
    • 1-2. delete操作对应的undo日志
    • 1-3. update操作对应的undo日志
      • 1-3-1. 不更新主键
      • 1-3-2. 更新主键
• 九、MySQL事务
  • 1. 事务的特性
    • 1-1. 原子性Atomicity
    • 1-2. 一致性Consistency
    • 1-3. 隔离性Isolation
    • 1-4. 持久性Durability
  • 2. 事务的状态
  • 3. MySQL中事务的语法
    • 3-1. 开启事务
    • 3-2. 提交事务
    • 3-3. 手动中止事务
    • 3-4. 事务的自动提交
    • 3-5. 隐式提交
    • 3-6. 保存点
  • 4. 支持事务的存储引擎
  • 5. 事务ID
    • 5-1. 给事务分配ID的时机
    • 5-2. 事务ID是怎么生成的
• 十、事务隔离级别和MVCC
  • 1. 事务隔离级别
    • 1-1. 事务并发执行遇到的问题
    • 1-2. SQL标准中的四种隔离级别
  • 2. MVCC原理(Multi-Version-Concurrency-Control)
    • 2-1. 版本链
    • 2-2. ReadView
• 十一、MySQL锁相关
  • 1. 一致性读
  • 2. 共享锁和独占锁
  • 3. InnoDB中的锁

一、mysql基本知识

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

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

2. MySQL数据类型的大小

• tinyint 1字节
• int,integer 4字节
• bigint8字节
• float4字节
• double8字节
• varchar(n)表示n个字符，无论汉字和英文，Mysql都能存入n个字符，仅是实际字节长度有所区别
• char(n),定长,表示该列占用n个字符,根据不同的字符集,实际占用字节大小不一样
  • 对于定长字符集如ascii,char(n)固定占用n个字节,如果实际数据不足n个字节,会在末尾补上空字符(0x00)
  • 对于变长字符集如utf8(1~3),gbk(1~2),也会在变长字段长度列表中存储该列的长度

3. update的大致流程

二、InnoDB记录存储结构

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

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,是不会放入这个列表中的,而是放在了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. compact记录头信息5字节

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

名称	大小(bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	表示该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前页目录中拥有的记录数
heap_no	13	表示当前记录在本⻚中的位置,因为页中会自动记录最小记录和最大记录,分别放在第0位和第1位,所以用户添加的记录在页中的位置是从2开始的
record_type	3	表示当前记录的类型,0表示普通记录,1表示B+数非叶子节点记录,2表示最小记录,3表示最大记录
next_record	16	表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量

1-3-1-4. 列值

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

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID,当我们创建的表中没有明确指定主键,且表中没有unique修饰的列时,会自动创建这个列,用来唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

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

1-3-2. redundant行格式

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

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

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

1-3-2-2. redundant记录头信息6字节

与compact行格式的记录头信息对比来看,有两处不同:

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. InnoDB数据页结构

2-1. 常见的数据页类型

1. 存放表空间头部信息的页
2. 存放insert buffer信息的页
3. 存放inode信息的页
4. 存放undo日志信息的页
5. 存放记录的页(索引页)

2-2. 索引页

2-2-1. 索引页结构

名称	中文名	占用空间	简单描述
File Header	文件头部	38字节	页的一些通用信息(上一页,下一页等)
Page Header	页面头部	56字节	数据页专有的一些信息
Infinum + Supremun	最小记录和最大记录	26字节	两个虚拟的行记录
User Records	用户记录	动态改变	实际存储的行记录内容
Free Space	空间空间	动态改变	页中尚未使用的空间
Page Directory	页目录	动态改变	页中的某些记录的相对位置
File Trailer	文件尾部	8字节	校验页是否完整

2-2-1-1. File Header文件头部38字节

• 文件头部的组成部分

名称	占用空间(字节)	描述
fil_page_space_or_chksum	4	页的校验和
fil_page_offset	4	页号
fil_page_prev	4	上一页的页号
fil_page_next	4	下一页的页号
fil_page_lsn	8	页被最后修改时对应的日志序列位置(Log Sequence Number)
fil_page_type	2	当前页的类型
fil_page_file_flush_lsn	8	仅在系统表空间的一个页中定义,代表文件至少被刷新到了对应的lsn值
fil_page_arch_log_no_or_space_id	4	页属于哪个表空间

• fil_page_type当前页的类型:

类型名称	十六进制	描述
fil_page_type_allocated	0x0000	最新分配，还没使用
fil_page_undo_log	0x0002	Undo日志⻚
fil_page_inode	0x0003	段信息节点
fil_page_ibuf_free_list	0x0004	Insert Buffer空闲列表
fil_page_ibuf_bitmap	0x0005	Insert Buffer位图
fil_page_type_sys	0x0006	系统页
fil_page_type_trx_sys	0x0007	事务系统数据
fil_page_type_fsp_hdr	0x0008	表空间头部信息
fil_page_type_xdes	0x0009	扩展描述页
fil_page_type_blob	0x000A	blob页
fil_page_index	0x45BF	索引页,也就是我们所说的数据页

2-2-2. Page Header页面头部56字节

名称	占用空间(字节)	描述
page_n_dir_slots	2	本页中页目录的数量
page_heap_top	2	还未使用的空间的最小地址,也就是说从该地址之后就是Free Space
page_n_heap	2	本页中记录的数据(包括最小和最大记录以及被标记为删除的记录)
page_free	2	第一个被标记为删除的记录地址(各个已删除的记录通过next_record也会组成一个单链表,这个单链表中的记录可以被重新利用)
page_garbage	2	已删除记录占用的字节数
page_last_insert	2	最后插入记录的位置
page_derection	2	记录插入的方向
page_n_direction	2	一个方向连续插入的记录数量
page_n_recs	2	该页中记录的数量(不包括最小和最大记录以及被标记为删除的记录)
page_max_trx_id	8	修改当前页的最大事务ID,该值仅在二级索引中定义
page_level	2	当前页在B+树中所处的层级
page_index_id	8	索引ID,表示当前页属于哪个索引
page_btr_seg_leaf	10	B+树叶子段的头部信息,仅在B+树的Root页定义
page_btr_seg_top	10	B+树非叶子段的头部信息,仅在B+树的Root页定义

2-2-2-1. 页目录Page Derectory

• 页目录的作用
  • 页目录将页中记录按顺序划分为几组,并在页目录中记录每组的中的最大值,这样就可以通过二分查找法快速定位要查找目标所在的组,然后遍历该组中数据即可查到.
• 页目录的划分规则
  • 对于最小记录所在的分组只能有一条记录,最大记录所在的分组可有有1~8条记录,其他分组中可以有4~8条记录.
  • 一开始数据页中只有最小纪录和最大记录两个记录,它们分属于两个分组
  • 之后每插入一条记录,都会从⻚目录中找到主键值比本记录的 主键值大并且差值最小的槽，然后把该槽对应的记录的 n_owned值加1，表示本组内又添加了一条记录，直到该组中 的记录数等于8个
  • 在一个组中的记录数等于8个后再插入一条记录时，会将组中 的记录拆分成两个组，一个组中4条记录，另一个5条记录。这 个过程会在⻚目录中新增一个槽来记录这个新增分组中最大的 那条记录的偏移量
• 简化的数据图,注意其中记录的n_owned值,只有每组中最大的那条数据中会记录该组中有多少条记录
  

2-2-2-2. File Trailer文件尾部8字节

• 前4个字节代表页的校验和
  • 这个部分是和File Header中的校验和相对应的.每当一个页在内存中被修改了,在同步之前先算出它的校验和,因为File Header在页面的前边,所以校验和会被首先同步到磁盘,当完全写完时,校验和也会被写到页的尾部,如果完全同步成功,则页的首部和尾部的校验和应该是一致的.如果同步到一半,断电了,那么File Header中的校验和就代表已经修改过的页,File Trailer中的校验和代表原先的页.
  • 后4个字节代表页面最后被修改时对应的日志序列位置(LSN)

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

• 找到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列表中的话,在看是否是定长,如果是定长,直接向后数指定的长度个字节,如果不是定长,再看可变字段长度列表中相应位置的大小,数相应长度个字节.

四、B+树索引

1. InnoDB中的索引方案

1-1. 目录项记录和普通用户记录的不同

• 目录项记录(也就是非叶子节点)的record_type值是1,而普通用户记录(叶子节点)的record_type值是0
• 目录项记录只有主键值和页的编号两个列,而普通用户记录(叶子节点)中会包含所有的列,包括隐藏列.(实际上二级索引的非叶子节点中会记录当前索引的列的值,页的编号,以及主键值,叶子节点中记录的是主键ID)
• 记录头信息中的min_rec_mask属性,只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1,其他别的记录的min_rec_mask值都是0
  

1-2. 聚簇索引

• 聚簇索引特征
  1. 使用数据主键值的大小进行记录和页的排序.
     1. 页内的记录按照主键大小顺序排成一个单向链表
     2. 同一层次的页按照主键大小顺序排成一个双向链表
  2. B+树的叶子节点存储的是完整的用户记录
  3. 非叶子节点中记录的是主键+页号
• 在InnoDB中,聚簇索引就是数据的存储方式,也就是所谓的索引即数据,数据即索引

1-3. 二级索引

• 二级索引的特征
  1.使用数据中指定的列的大小进行记录和页的排序
  2.叶子节点中只存储指定的列以及主键的值
  3.非叶子节点中存储的是指定的列+主键+页号
  3.先使用二级索引找到主键的值,在回表到主键索引中去找到实际数据

1-4. 联合索引

• 联合索引的特征
  • 使用数据中联合的列的大小进行记录和页的排序
  • 叶子节点中只存储联合的所有的列+主键
  • 非叶子节点中存储联合的所有的列 + 主键 + 页号

1-5. B+树索引是如何生成的

• 每当为某个表创建一个B+树索引的时候,都会为这个索引创建一个根节点页,最开始表中没有数据的时候,根节点页中既没有用户记录,也没有目录项目录
• 插入数据时,先把数据存储到这个根节点中
• 当根节点中的可用空间用完时继续插入记录,此时会将根节点中的所有记录复制到一个新分配的页,比如a页,然后对这个新页进行分裂的操作,得到另一个新页,比如b.这时新插入的记录根据键值得大小就会被分配到页a或者页b中,而根节点便升级为存储目录项记录的页.

2. MyISAM中的索引方案

• MyISAM中索引和真实的数据是分开存储的
• 表中的记录按照插入的顺序单独存储在一个文件中,可以通过行号快速访问到一跳记录
• 索引的叶子节点中记录的是主键值 + 行号,先通过索引找到对应的行号,再通过行号去找对应的记录.MyISAM中建立的索引都是二级索引.

3. 创建索引和删除索引

//建表的时候创建索引
CREATE TABLE index_demo( 
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1),
INDEX idx_c2_c3 (c2, c3)
);
//添加索引
ALTER TABLE 表名 ADD INDEX 索引名 (索引的列)
//删除索引
ALTER TABLE 表名 DROP INDEX  索引名

4. B+树索引的使用

4-1. 索引的代价

1. 空间上的代价
   每个索引都要建立一个B+树,B+树的节点存储在数据页中
2. 时间上的代价
   每次对数据的增删改操作,都需要维护表的所有索引.

4-2. 哪些查询会用到索引

• 全值匹配
  搜索条件中的列和索引列一致
• 匹配左边的列
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另一列
• 用于排序
  • 联合索引的排序,排序的列必须和联合索引定义的列的顺序相同,否则无法使用索引
• 用于分组

4-3. 使用索引时要注意的事项

• 只为用于搜索,排序或分组的列创建索引
• 考虑列的基数,选择那些基数大的列建立索引
• 索引列的类型尽量小
• 索引字符串的前缀add index idx_name_phone (name(10),phone)
  • 注意:索引列前缀的方法不支持使用索引列排序,只能使用文件排序
• 让索引列在表达式中单独出现
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况,建议让主键拥有自动增长属性
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询,避免回表带来的性能损耗

五、MySQL的数据目录

• 使用mysqld --verbose --help | grep datadir可以不登录就能查看MySQL的数据目录
• 使用show variables like 'datadir'可以查看MySQL的数据目录,数据目录下每个数据库一个文件夹

1. 创建数据库时MySQL做了什么

• 当我们创建数据库时,MySQL做了两件事
  • 在数据目录下创建一个与数据库名同名的子文件夹
  • 在子文件夹中创建一个db.opt文件,其中包含了该数据库的各种属性,如果数据库的字符集,比较规则等.

2. 创建的表在文件系统中如何表示

• 表结构的定义
  InnoDB和MyISAM都在数据库子目录下创建了一个专门用于描述表结构的文件,表名.frm
• 表中的数据
  • InnoDB:
    • 表名.IDB索引即数据,在一起
  • MyISAM:
    • 表名.MYI保存索引
    • 表名.MYD保存数据

2-1. 视图在文件系统中的表示

• 视图其实是虚拟的表,只是查询语句的一个别买而已,不需要存储真实的数据的,只需要把它的结构存储起来就行了,只会存储一个视图名.frm文件

2-2. 其他的文件

• 服务器进程文件
• 服务器日志文件
• SSL和RSA证书和秘钥文件

3. 操作系统对数据库的限制

• 数据库名称和表名称不得超过文件系统所允许的最大长度
• 特殊字符会被映射成编码值
• 文件大小受文件系统最大长度限制

六、Buffer Pool

• MySQL在启动时就申请了一片连续的内存,用来缓存磁盘中的页信息,这片内存区域就叫做Buffer Pool.默认大小是128M,可以在配置文件中修改innodb_buffer_pool_size属性来控制大小

1. 如何判断一个页是否已经加载到buffer pool中了

• MySQL内部维护了一个已经加载的缓存页的哈希表,用表空间+页号作为key,缓存页作为value,当需要访问某个页的数据时,先去哈希表中根据表空间+页号查看是否有对应的缓存页,如果有直接使用该缓存页,如果没有,再从磁盘中加载并添加的哈希表中.

2. Buffer Pool的内部组成

Buffer Pool中的默认缓存页大小也是16K,每个缓存页都有一个与之对应的控制块,用来保存该页的一些信息,比如表空间编号,页号,缓存页在Buffer Pool中的地址,链表节点信息,一些锁信息以及LSN信息等.

• Buffer Pool的组成
  

3. free链表

free链表

• free链表:把所有空闲的缓存页对应的控制块作为节点放到链表中形成一个free链表
• 每当需要从磁盘中加载一个页到Buffer pool中时,就从free链表中取一个空闲的缓存页,并把该缓存页对应的控制块中填入该页的信息,如表空间,页号之类的信息.然后把缓存页对应在free链表的节点移除.

4. flush链表

• 修改了Buffer Pool中某个缓存页的数据,并不会立即将修改的页刷新到磁盘中,而是用一个链表保存被修改后的缓存页,叫做flush链表,MySQL有一个后台线程专门的将flush链表中的缓存页刷新到磁盘中去.
• mtr执行过程中会把修改过的页面加入到Buffer Pool的flush链表中,缓存页对应的控制块中有两个参数oldest_modification和newest_modification,分别记录页面第一次被修改时的lsn和最新一次被修改时的lsn
• flush链表是按照页面第一次被修改的顺序排序的,当页面被重新刷新会磁盘时,会将它的oldest_modification记录到redo日志的checkpoint block中

5. LRU链表

随着不断的从磁盘加载数据到Buffer Pool的缓存页中,当Buffer Pool中不再有空闲的缓存页时,就需要淘汰掉部分最近很少使用的缓存页,我们需要用LRU链表来记录加载的缓存页,让最近最少使用的缓存页排在链表的最后,每次对缓存页的访问,就把它放到链表的头部,这样需要淘汰缓存页释放空间时,就可以直接从LRU链表的最后去释放空间.
但是如果只是这种简单的LRU算法的话,会有很大的问题

1. 加载到Buffer Pool中的页不一定被用到
   因为InnoDB有一个预读机制,在执行当前请求的时候,InnoDB会预先加载一些它认为你之后可能会用到的数据.
2. 比如全表扫描时,大量的页被同时加载到Buffer Pool时,可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉
   对于这些问题,InnoDB都做了相应的优化.
   首先把LRU链表按照比例分成两部分
   一部分用来存储使用频率非常高的缓存页,叫做young区域
   另一部分用来存储使用频率不是很高的缓存页,叫做old区域.

5-1. LRU链表的优化

5-1-1. 对预读机制的优化(初次加载到old区域的头部)

• 存在的问题:预读加载缓存页不一定会被使用到
• 解决方法:
  当磁盘上某个页面在初次加载到Buffer Pool中的某个缓存页时,该缓存页对应的控制块会被放到old区域的头部,这样针对预读的缓存页,在后续访问中如果没有被使用到,就会被逐渐移动到old区域的末端,最终被释放掉.

5-1-2. 对全表扫描的优化(间隔1S后再次访问才算热点数据)

• 存在的问题:
  全表扫描时,虽然首次被加载时被放在了old区域,但是后续会立马被访问到,而且一个页中有多条数据,读取完某个页面的记录就相当于访问了这个页面多次.如果这时候把这些数据放到young区头部的话还是会顶掉真正热门的数据.
• 解决办法:
  记录下old区域缓存页的第一次访问时间,如果后续访问时间与第一次访问时间相差超过了1s,就把它移动到young区的头部.

5-1-3. 对young区域数据的优化(对young区前1/4数据的访问,不用把它们再次放到young区的头部)

6. 刷新脏页到磁盘

后台有专门的线程每个一段时间负责把脏页刷新到磁盘

• BUF_FLUSH_LIST
  后台线程定时从flush链表中刷新一部分页面到磁盘
• BUF_FLUSH_LRU
  后台线程定时从LRU链表尾部开始扫描一定数量页面,如果发现了脏页,会把他们刷新到磁盘
• BUF_FLUSH_SINGLE_PAGE
  用户线程在准备加载页面到Buffer Pool中时,没有可用的缓存页,而且LRU尾部也没有可以直接释放的未修改页面,这时用户线程会将LRU链表尾部的一个脏页同步刷新到磁盘

七、redo log

1. 什么是redo log

事务对缓存页中数据的修改并不会立即刷新到磁盘中去,如果这时系统崩溃了,那缓存页中修改过的还未刷新到磁盘的数据就会丢失了,为了避免这种状况发生,使用redo log记录下事务对页数据的修改,并定时保存到redo log磁盘文件中,由于redolog数据相对较小,而且是顺序写入,所以速度比较快,可以在事务提交后确保事务的修改都被记录下来了,即使系统崩溃重启后还是可以根据redo日志重新找回原先的修改.
redo log记录了一下事务对数据库做了哪些修改. 在事务提交时,将redo log中的内容刷新到磁盘
redo日志会把事务在执行过程中 对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把 事务所做的任何修改都恢复出来。

2. 不使用redo log有什么问题

• 刷新整个数据页太浪费了
  InnoDB中是以页为单位来进行磁盘IO的,如果直接在页上修改数据,那么就要把整个修改的页都重新刷新回磁盘,一个页默认是16K,如果我们只修改了一个字节就要刷新16K的数据到磁盘上显然太浪费了
• 随机IO刷起来比较慢
  一个事务可能修改多个页,这些页可能并不相邻,这就意味着将某个事务修改的Buffer Pool中页面刷新到磁盘时,需要很多随机IO访问,随机IO的性能是比较差的.

3. redo log buffer

• MySQL服务器启动时就向操作系统申请了一个redo log buffer连续内存空间. MySQL5.7中,这个空间的默认大小为16M
• 对底层页面的一次原子访问的过程称之为一个Mini-Transaction,简称mtr,一个mtr可以包含一组redo日志,在进行崩溃恢复时,这一粗redo日志作为一个不可分割的整体.
• 在每个mtr结束时,会将过程中产生的一组redo日志全部复制到log buffer中. 在某个时机,会将log buffer中的日志缓存刷新到磁盘中.

4. redo log buffer刷盘的时机

• log buffer空间不足时
  日志量达到log buffer总容量的一半左右,就会自动刷盘
• 事务提交时
• 后台定时1秒刷新一次
• 正常关闭服务器时

5. redo log磁盘中存放的位置

• redo日志保存的位置:
  和MySQL的数据库文件在同一个目录下,使用show variables like 'datadir'可以查看到
• 日志文件组:
  5.7版本的时候,默认会有两个redo log文件,分别是ib_logfile0和ib_logfile1,每个文件48M
• 在写入redo日志时,会从ib_logfile0开始写,写满之后就往ib_logfile1里面写,ib_logfile1再写满之后,会重新循环往ib_logfile0里面写.

6. redo log的结构

• log buffer的本质是一片连续的内存空间,被划分成若干个512字节大小的block.将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中.
• redo日志文件格式
  
  
  redo日志文件组中的每个文件大小都一样,格式也一样.都是由两部分组成
  • 前2048个字节,也就是前4个block,用来存储一些管理信息的,其中包括两个checkpoint
  • 之后才是真正存储log buffer中同步过来的redo log block

7. redo log的优势

• redo日志占用的空间非常小
• redo日志是顺序写入磁盘的,顺序IO速度比较快

8. 日志序列号LSN(Log Sequeue Number)

• lsn的值是代表系统写入的redo日志量的一个总和
• 每一组有mtr生产的redo日志都有一个唯一的LSN值与其对应,LSN值越小,说明redo日志产生的越早

9. redo日志崩溃恢复

lsn和flushed_to_disk_lsn

• lsn:当前log buffer中已经写入的日志编号
• write_lsn:当前已经写入到操作系统的缓存中的日志的编号
• flushed_to_disk_lsn:真正写入到磁盘redo log文件中的日志的编号
• checkpoint_lsn:在这之前的redo日志所代表的修改都已经同步到了磁盘中.也就是说在这之前的redo日志都是可以被覆盖的.

9-1. 确定恢复的起始lsn和结束lsn

• 确定起始位置
  redo日志文件的头部存储了最近两个checkpoint的信息,其中checkpoint no最大的那个对应的lsn值就是需要恢复数据的开始位置
• 确定结束位置
  因为log block是顺序写入的,前一个block写满后才会往后一个写入,所以只要找到第一个block没有写满的位置就是结束位置了.

9-2. 如何恢复

• 使用哈希表
  根据redo日志的表空间和页号计算出散列值,把表空间和页号相同的redo日志放到哈希表的同一个槽里,使用链表按照日志生成的顺序链接起来,这样就可以让对同一个页面的修改一次性的修复好,避免了重复读取同一个页面的随机IO
• 跳过已经刷新到磁盘的页面
  如果在做了某次checkpoint之后有脏页被刷新到磁盘中,那么该页对应的FIL_PAGE_LSN代表的lsn值肯定大于checkpoint_lsn的值,这时就不需要重复执行lsn值小于FIL_PAGE_LSN的redo日志了.

八、undo log

当我们要对一条记录做改动时,都需要把回滚时需要的东西记下来
为了回滚而记录的这些就是undo日志

1. undo日志的类型与格式

1-1. insert操作对应的undo日志

向表中插入一条数据,想要回滚的话,只需要在插入时记下主键ID,回滚时根据主键ID去删除这条记录就可以来
插入类型的undo日志trx_undo_insert_rec

• undo no在一个事务中是从0开始递增的,也就是说只要事务没提交,每生成一条undo日志,那么该条日志的undo no就增1
• 主键各列信息:如果记录中的主键只有一个列,那么就要把该列占用的存储空间大小和真实值记录下来,如果记录中的主键包含多个列,那么每个列占用的存储空间大小和真实值都需要记录下来

1-2. delete操作对应的undo日志

插入到页面的记录会根据记录头信息中的next_record属性组成一个单项链表,称之为正常记录链表,被删除的记录也会根据next_record属性组成一个链表,称之为垃圾链表

• 将一条记录从正常记录链表中删除的步骤分为两个阶段:
  1. delete mark
     在事务中执行删除语句,但是还未提交事务时,只会将记录头信息中的delete_mask标志位设置为1,
  2. purge
     当该删除语句所在的事务提交后,有专门的线程把记录从正常记录链表中移除,并添加到垃圾链表中.

• 对一条记录进行delete_mark操作前,需要把该记录的旧的trx_id和roll_pointer的值都记到对应的undo日志中来,这样就可以通过undo日志的old roll_pointer找到记录在修改之前对应的undo日志
• 与insert的undo日志不同的是,delete的undo日志中需要记录所有索引相关的列的信息,因为在purge阶段,需要去删除该记录所有索引信息.

1-3. update操作对应的undo日志

对于update操作,update时更新主键和不更新主键是两种不同的处理方案.

1-3-1. 不更新主键

不更新主键又分为两种情况:

1. 记录更新的每一列的数据和更新前占用的空间完全一样
   这时可以直接在原纪录上更新,在update的undo日志中记录下原先的列的值
2. 更新前后有列的占用空间不一样
   占用空间不一样,就不能再原纪录上直接更新了,新建update的undo日志,记录要修改的列的原值,然后需要把原记录直接删除掉,然后再插入新记录.这里的删除是直接将记录从正常记录链表中移除并加入到垃圾链表中

1-3-2. 更新主键

需要更新主键的情况下,分为两个步骤

1. 将旧记录进行delete mark操作
   在delete mark前会记录一条delete 的undo日志
2. 插入一条新记录,需要重新定位插入的位置
   插入新记录时,会记录一条insert的undo日志
   所以更新主键的update操作会生成两条undo日志,先是delete的undo,然后是insert的undo

九、MySQL事务

1. 事务的特性

1-1. 原子性Atomicity

事务是最小单位，不可再分

1-2. 一致性Consistency

事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败

1-3. 隔离性Isolation

事务A和事务B之间具有隔离性

1-4. 持久性Durability

对数据库操作所修改的数据都会保存在磁盘上,不会丢失

2. 事务的状态

• 活动的 active
  事务对应的数据库操作正在执行中
• 部分提交的partially committed
  当事务中的最后一个操作执行完成,但由于操作都在内存中执行,所造成的影响并没有刷新到磁盘时,这个事务处于部分提交的状态
• 失败的 failed
  事务执行过程中或提交过程中遇到错误无法继续执行,或者人为的停止当前事务的执行,该事物就处于失败状态
• 中止的 aborted
  当事务处于失败状态时,要回滚事务对数据库的修改.也就是说数据库恢复到了执行事务之前的状态,该事物处于中止的状态.
• 提交的 commited
  事务将修改过的数据都同步到磁盘上之后,这个事务就属于提交状态

3. MySQL中事务的语法

3-1. 开启事务

1. begin;
2. start transaction [option,option];
   • option:
     • read only标识当前事务是一个只读事务
     • read write标识当前事务是一个读写事务(默认是这个)
     • with consistent snapshot启动一致性读

3-2. 提交事务

commit;

3-3. 手动中止事务

rollback;

3-4. 事务的自动提交

• 使用show variables like 'autocommit'查看自动提交是否启动,默认是on
• 如果关闭自动提交
  1. 设置系统变量autocommit为OFFset autocommit = off;
  2. 显示使用start transaction 或begin开启一个事务,这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能

3-5. 隐式提交

• 执行某些语句时会自动提交当前事务
  • 修改数据库,表,视图,存储过程之类的DDL语句会自动提交事务
  • 隐式使用或修改mysql数据库中的表
    • 如alter user,create user,drop user,grant等
  • 事务嵌套时会自动提交上一个事务
  • 等等

3-6. 保存点

• 添加保存点
  在事务中可以用savepoint 保存点名称;来指定一个保存点,
• 回滚到某个保存点
  当回滚时,可以指定回滚到某个保存点的位置rollback to [savepoint] 保存点名称
• 删除保存点
  release savepoint 保存点名称;

4. 支持事务的存储引擎

InnoDB可以支持事务,MyISAM不支持事务

5. 事务ID

5-1. 给事务分配ID的时机

• 只读事务分配事务ID的规则
  • 只读事务不可以对普通的表进行增删改操作,但可以对临时表做增删改操作.所以对于只读事务来说,只有它第一次对某个用户创建的临时表进行增删改操作时才会为这个事务分配一个事务ID,否则的话是不分配事务ID的
• 读写事务分配事务ID的规则
  • 当事务第一次对某个表执行增删改操作时才会为这个事务分配事务ID,否则的话也是不分配事务ID的

5-2. 事务ID是怎么生成的

• 系统内部维护了一个全局变量,当需要给某个事务分配事务ID时,会把该变量的值当做事务ID分配给它,然后该变量自增1
• 当这个变量的值为256的倍数的时候,会把它存入到表空间的Max Trx ID值
• 当系统重启时,会从表空间中读取原先存储的Max Trx ID,并把该值加上256之后赋值给前面提到的全局变量
• 这样做的目的是保证整个系统中分配的事务ID是一个递增的数字,先被分配id的事务的值是较小的.

十、事务隔离级别和MVCC

1. 事务隔离级别

1-1. 事务并发执行遇到的问题

• 脏写(Dirty Write)(任何隔离级别都不允许发生这个问题)
  一个事务修改了另一个未提交事务修改过的数据
• 脏读(Dirty Read)
  一个事务读到了另一个未提交事务修改过的数据
• 不可重复读(Non-Repeatable Read)
  一个事务能读到另一个已提交事务修改的数据.主要是同一个数据两次读 取的值不相同
• 幻读(Phantom)
  一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来,就发生了幻读.
  • 注意:记录被删除不属于幻读,幻读强调的是一个事务按照某个相同条件多次读取记录时,后读取时读到了之前没有读到的记录

1-2. SQL标准中的四种隔离级别

• 读未提交(read uncommitted)
• 读已提交(read committed)
• 可重复读(repeatable read)
• 序列化(serializable)

2. MVCC原理(Multi-Version-Concurrency-Control)

2-1. 版本链

每个记录中都有trx_id和roll_pointer
其中每次对某条聚簇索引记录进行改动时,都会把旧的版本写入到undo日志中,然后使用记录头中的roll_pointer指向这个undo日志.然后把该事务的事务ID赋值给trx_id列,通过这个roll_pointer就可以形成这个记录的版本链.版本链的头节点就是当前记录最新的值

2-2. ReadView

• 对于read uncommited隔离级别来说,直接读取记录的最新版本就可以了.
• 对于serializable隔离级别来说,使用加锁的方式访问记录
• 对于read committed和repeatable read来说,需要判断版本链中哪个版本是当前事务可见的,这个需要用到ReadView的概念
• ReadView的内容
  • m_ids表示在生成readview时当前系统中活跃的读写事务的事务id列表
  • min_trx_id表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值
  • max_trx_id表示生成ReadView时系统中应该分配给下一个事务的id值
  • creator_trx_id表示生成该readview的事务的事务ID
• 如何判断记录中某个版本是否可见
  • 如果被访问版本的trx_id属性值与readview中的creator_trx_id相同,说明是当前事务自己修改的记录,所以该版本可以被当前事务访问
  • 如果被访问版本的trx_id属性值小于ReadView中min_trx_id,表明生成该版本的事务在当前事务生成readview前已提交,所以该版本可以被当前事务访问.
  • 如果被访问版本的trx_id属性值大于readview中的max_trx_id,表明生成该版本的事务在当前事务生成readview后才开启,所以该版本不可以被当前事务访问
  • 如果被访问版本的trx_id属性值在readview的min_trx_id和max_trx_id之间,那就需要判断一下trx_id属性值是不是在m_ids列表中,如果在,说明创建readview时生成该版本的事务还是活跃的,该版本不可以被访问,如果不在,说明创建readview时生成该版本的事务已经被提交,该版本可以被访问
• 如果某个版本的数据对当前事务不可见的话,那就顺着版本链找下一个版本的数据,继续判断可见性.
• read committed和repeatable read的区别就是它们生成readview的时机不同.
  • read committed在每次读取数据前都生成一个readview
  • repeatable read只在第一次读取数据时生成一个readview

十一、MySQL锁相关

1. 当一个事务T1想对某个记录做改动时,首先会去看看内存中有没有与这个记录相关的锁结构,如果没有,就在内存中生成一个锁结构与之关联,锁结构中的trx信息就是当前事务的ID,因为之前没有别的事务为这个记录加锁,所以生成的锁结构中is_waiting属性就是false,表示加锁成功
2. 如果在T1提交之前,另一个事务T2也想对该记录做改动,那么它会先去看看有没有锁结构与这条记录相关联,发现已经有一个了,然后T2也会生成一个该记录的锁结构,不过此时新生成的锁结构的is_waiting属性为true,表示当前事务需要等待
3. 当事务T1提交之后,会把该事物生成的锁结构释放掉,然后看看还有没有别的事务在等待获取锁,发现T2还在等待获取锁,就把T2对应的锁结构的is_waiting属性设置为false,并把该事物对应的线程唤醒,此时T2就获取到锁了.

1. 一致性读

事务利用MVCC进行的读取操作称之为一致性读,或者快照读.一致性读并不会对任何数据加锁,其他事务可以对表中的记录做改动

2. 共享锁和独占锁

• 共享锁,Shared Locks,简称S锁,在事务要读取一条记录时,需要先获得该记录的S锁
• 独占锁,Exclusive Locks,简称X锁,在事务要改动一条记录时,需要先获得该记录的X锁
• 对读记录加S锁select ... lock in share mode;
• 对读记录加X锁select ... for update;

3. InnoDB中的锁

各个锁的兼容性

• IS锁 intention shared lock,当事务准备在某条记录上加S锁时,先在表级别加一个IS锁,这个锁通常是mysql自动加的,如果当前表有IS锁,则不能给表级别加X锁

• IX锁intention exclusive lock,当事务准备在某条记录上加X锁时,先在表级别上加一个IX锁.如果表有IX锁,则不能给表级别加S锁和X锁这两个锁的用处是,当需要给表级别加S锁或者X锁时,能够直接知道当前表中有没有记录被加了锁.

• S锁共享锁

• X锁排它锁

• gap锁给一条记录加了gap锁只是不允许其他事务往这条记录前面的间隙中插入新记录.repeatable read下解决幻读的另一种方式就是gap锁.gap锁仅仅是为了防止插入幻影记录而提出的

• next-key锁相当于给该记录加了行锁和gap锁

• 插入意向锁事务在插入一条记录时,要插入的地方被gap锁锁住,这时需要生成一个插入意向锁
  

• mysql如何给AUTO_INCREMENT的列赋值的,如何保证赋值时递增值是连续的
  系统实现auto_increment的列递增赋值的原理主要有两种:

1. 使用auto_inc锁
   在执行插入语句时就在表级别加一个auto_inc锁,然后为每条待插入记录的auto_increment列分配递增的值.在该语句执行结束后,再把auto_inc锁释放掉.这样一个事务在持有auto_inc锁的过程中,其他事务的插入语句都要被阻塞,可以保证一个语句中分配的递增值是连续的.
   如果我们的插入语句在执行前不能确定具体要插入多少条记录,一般使用auto_inc锁,比如INSERT INTO table1 SELECT * FROM table2;
2. 使用一个轻量级锁
   在为插入语句生成auto_increment列的值时获取一下这个轻量级锁,然后生成本次插入语句需要的auto_increment值,然后就把这个轻量级的锁释放掉,不需要等到整个插入语句执行完才释放锁
   一般用在知道具体要插入多少条记录的情况下.