参考b站up 戌米的论文笔记 https://www.bilibili.com/video/BV1Tv4y1o7tA/
书籍《mysql是怎样运行的》
极客时间《mysql实战45讲》
一丶Mysql整体架构
MySQL 可以分为 Server 层和存储引擎层两部分
1.Server 层
Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
-
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接
-
查询缓存
对于查询语句,mysql server层会将查询语句和对应的结果,使用key - value的缓存结构进行缓存,但是一旦发生更新,那么查询缓存就需要失效。因此查询缓存在高版本的mysql中已经被移除
-
分析器
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。
然后做“语法分析”。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。
-
优化器
优化器是在表里面有多个索引的时候,决定使用哪个索引。或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。
-
执行器
MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。
2.存储引擎层
存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。下面我们对比两个常用的存储引擎
MyISAM | InnoDB | |
---|---|---|
存储结构 | Myisam 创建表后生成的文件有三个,分别为: frm:创建表的语句 MYD:表里面的数据文件(myisam data) MYI:表里面的索引文件(myisam index) | Innodb 创建表后生成的文件有两个,分别为: frm:创建表的语句 idb:表里面的数据+索引文件 |
索引 | 非聚集索引 ,MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 |
聚集索引 ,聚集索引的文件存放在主键索引的叶子节点上 |
事务支持 | 不提供事务支持 |
提供事务支持 |
锁的粒度 | 只支持表级锁 |
支持行级锁 。 |
存储表的具体行数 | 保存表的总行数,如果select count() from table;会直接取出出该值。 | 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。 |
以下的分析针对 update t set a='1' where 主键 = 1这条语句
二丶开启事务
在mysql中,无论用户是否手动开启一个事务,sql都是在一个事务中进行的。我们可以使用start transaction
开启一个事务,commit
提交事务,rollback
回滚事务。
默认情况下,mysql存在自动提交(autocommit=1
),这时候即使我们没有显式开启事务,直接执行update语句,那么mysql会隐式的开启一个事务,并在这条update执行结束后自动提交
如果set autocommit = 0
或者显式开启了一个事务,那么update执行结束后不会自动提交,而是需要手动发起commit 或者rollback
无论式显式事务,还是隐式事务,mysql都会在事务内部第一次执行增删改操作的时候,给事务分配一个事务号
三丶Sql解析,查询计划生成
mysql服务器层会从连接中读取sql语句,然后进行词法解析,语法解析,查询优化(为什么update语句需要查询优化?不查出来怎么知道修改哪些行数据昵)最终生成一个AST树,这便是物理执行计划,执行器会根据执行计划,调用存储引擎的接口,
四丶查询需要修改的数据
mysql innodb存储引擎对磁盘的读取是以页为单位的,为了避免每次都从磁盘读取数据,innodb存在buffer pool使用LRU链表维护最近访问到的页,为了更快的从buffer pool中查找到目标页,innodb 还使用表空间号和页号作为key,页作为value,形成Hash表。如果我们目标页已经在buffer pool中那么直接返回目标页,如果不在那么需要进行磁盘io加载目标页到内存,然后缓存到buffer pool中
1.buffer pool是如何维护页在内存中的
buffer poo中存在三个关键的链表结构
- Free List 空闲链表,链表中将空闲缓冲页的控制块(控制块中记录了缓冲页的位置)进行串联,用于管理未被使用的缓冲池空间
- LRU List,最近最少使用链表,利用LRU算法在buffer pool满后淘汰冷门数据页(innodb 为了应对预读,全表扫描,对应LRU链表进行了改进,分成young区,和old区,
解决预读:innodb规定当磁盘某个页面在初次加载到buffer pool中某个缓冲页时,该缓冲页对应的控制块会放在old区域的头部,这样预读到的且后续如果不进行后续访问的页面会逐渐从old区移除,而不影响young区使用频率高的缓冲页。
,解决全表扫描:nnodb规定对于某个处于old区的缓冲页第一次访问时,就在其控制块中记录下访问时间,如果后续访问的时间和第一次访问的时间,在某个时间访问间隔内(innodb_old_blocks_time可以进行设置)那么页面不会从old区移动到young区,反之移动到young区中。这个时间间隔默认时1000ms,基本上多次访问同一个页面中的多个记录的时间不会超过1s
,解决热门数据经常需要移动到LRU链头部的问题: innodb规定只有被访问的缓冲页位于young区的前1/4范围外,才会进行移动,所以前1/4的高热度的数据,不会频繁移动
) - Flush List,脏链,维护在buffer pool中进行了修改,后续需要刷新到磁盘的缓冲页信息,innodb修改后的页不会立即刷盘,而是使用Flush list记录,后台存在线程定时进行刷脏
2.怎么从16k的页中找到目标数据
结合B+树索引结构,执行引擎根据页号找到根节点,然后根据根节点中的索引数据进行比较,找到子节点,重复此过程直至找到叶子节点所在的页。
到了叶子节点所在的页后,根据叶子节点页中的Page Dictionary中的槽找到目标记录所在的组,然后遍历这一组中的记录,找到目标记录。如果是范围查询,还需要根据B+树叶子节点间的双向指针继续查找,直到找到不符合要求的记录位置。(为了避免我们在遍历B+树的时候,其他线程修改了B+树的结构,此过程还需要对B+树进行加闩锁)(详细可看 Mysql索引(究极无敌细节版中的InnoDB索引方案一节)
五丶检验锁和加锁
1.Mysql中的锁
-
元数据锁MDL,mysql服务器层的MDL主要是避免操作数据的同时存在另外线程修改表结构,实现二者的互斥
-
innodb表锁
-
表级S锁,X锁
使用
Lock Tables t Read
,innodb存储引擎会对表t加共享锁使用
Lock tables t write
,innodb存储引擎会对表t加独占锁 -
表级意向锁:
innodb存储引擎中,当对表中某些记录加S锁之前,会在表上加上一个IS锁,同样加X锁之前会加表级IX锁,这里的I表示意向锁,
S or X
表示共享还是互斥,表级意向锁存在的目的是后续对表加S锁,X锁的时候,可以快速判断表中是否存在加锁的记录,避免遍历每一个记录查看是否被加锁。
-
-
innodb 行锁
- Record Lock
官方名称
Lock_REC_NOT_GAP
记录锁有S锁和X锁,S型记录锁之间可以共享,X型记录锁和S型记录锁,X型记录锁互斥
- GAP Lock
innodb的可重复读级别,使用词锁解决幻读问题,前面我们说过,其难点在于,加锁的时候幻影记录还未出现。官方使用
Lock_GAP
实现如下操作此处的gap锁可以反之其他事务在number为8记录前面的间隙插入新的记录,在区间(3,8)内无法进行插入操作,当另外一个事务要插入number为4的记录时,首先需要定位到该条记录的下一条记录,也就是number为8的记录,此时number为8的记录具备gap锁,所以将阻塞插入操作,直到gap锁被释放,其他事务才能进行插入。
gap锁出现的目的,就是为了防止插入幻影记录,如果对记录上gap锁,并不会限制其他事务对记录加记录锁
。innodb有两个虚拟的记录
Infimum(虚拟最小),Supermun(虚拟最大)
当我们想在(xx,正无穷)范围锁住幻影记录时就可以对Supermun加gap锁。- Next-Key Lock
Next-Key Lock = 记录锁 + gap锁,既锁住记录,也锁住记录之前的间隙
-
Insert Intention Lock
插入意向锁,表示事务想在某个间隙插入新的记录,但是当前处于等待状态。
比如事务A持有(4,8)范围内的gap锁,事务B和C,想插入(4,8)范围内的记录,就会在内存中生成事务B,C对应的插入意向锁,当前事务A释放gap锁的时候,将唤醒事务B和C,事务B和C可以同时获取插入意向锁,然后进行插入。插入意向锁并不会阻止对记录继续上锁。
- 隐式锁
为事务生成内存中的锁结构并不是一个0成本的事情,为了节省这个成本,提出
隐式锁
的概念。当一个事务插入语一条记录A,其他事务
select xxx Lock in share mode
读取记录A(获取记录A的S锁),或者使用select xxx for update
(获取记录A的X锁)- 立即修改记录A(获取x锁)
对于聚簇索引来说,有一个隐藏列
trx_id
此列存储着最后更改记录的事务id,在当前事务A插入记录后,便是存储着当前事务A的id,其他事务B企图获取x锁,s锁的时候,就需要下先看一下,trx_id隐藏列对应的事务是否存活,如果不是那么正常获取,反之需要为当前事务A创建一个x锁内存结构,并标记is_waiting为false,然后事务B将为自己创建一个锁结构,is_waiting 为true然后事务B进入等待状态对于二级索引来说,其不具备隐藏列
trx_id
但是在二级索引页面的page header中的page_maxt_trx_id
属性,记录了改动页面最大的事务id,如果其属性值小于当前最小的活跃事务id,那么说明对页面的改动事务已经提交,否则需要定位到二级索引记录,然后回表对聚簇索引进行上述聚簇索引的操作。一个事务对新插入的记录不需要显示的加锁,由于事务id的存在相当于加了一个隐式锁,别的事务需要加S锁或者X锁的时候,先帮之前的事务生成锁结构,然后为自己生成锁结构,再进入阻塞状态。隐式锁起到了延迟加锁的作用,也许别的事务不会获取于隐式锁冲突的锁,这时候可以减少内存中生成锁结构。
2.一条Update语句涉及的锁
2.1加共享元数据锁
为了避免当前事务操作的时候,存在另外的用户对当前表进行DDL操作,mysql首先会为当前操作的表加共享元数据锁。这个过程可能存在阻塞的可能,如果当前事务企图加共享元数据锁的时候,存在另外一个事务正在对表进行DDL操作,这时候另外一个事务上了互斥元数据锁,这时候会出现当前事务阻塞的情况
2.2 加表级意向互斥锁
此阶段也可能存在阻塞,但是由于innodb支持行锁,基本上很少有人给表上锁。如果执行当前事务之前存在另外一个事务给表上了表记共享锁,表记互斥锁,那么当前操作也会被阻塞。
加表记意向锁的好处在于,若没有意向锁,那么其他事务对表加锁的时候,需要遍历表中所有记录确保当前行中的记录没有被上锁
2.3 行锁
innodb中的行锁,其实是在内存中,为当前行生成一个锁结构,记录事务id,索引信息,锁信息,锁类型等.如果当前事务加锁的时候,记录并没有加锁,那么会生成一个锁结构存储于内存中。如果锁已经被占用那么会挂起当前事务,直到锁被释放后唤醒当前事务。
六丶修改数据和生成日志
在成功上锁之后,就可以放心的更新数据了,innodb将写三部分内容
1.写缓冲页
- 如果修改前后这行数据的大小完全没有发生改变,每一个字段所占用的大小和之前一样,那么进行
就地更新
- 但凡存在任何一个字段的大小发生了改变,那么删除旧记录,将旧纪录放入页的垃圾链表中,插入新的记录
不进行需要修改sql中指定的字段,还需要更新trx_id=当前事务的id
,roll_pointer = 指向undo log
buffer pool中脏页的刷盘依赖于后台定时任务线程定时进行刷新,如果修改到此为止将存在数据丢失的问题,为此innodb存储引擎还需要写入以下两种日志
2.写undo log
undo log是为了记录行数据修改前的结果,用于回滚和mvcc。undo log 可以分为两种——记录insert undo log,和 update/delete undo log,生成的undo log会写入到undo log buffer。
-
insert undo log 如何帮助回滚刚insert的一行数据
insert undo log实际上记录了插入行数据的主键,回滚是只需要根据主键进行删除即可
-
update/delete undo log怎么回滚update/delete的一行数据
update/delete操作的回滚需要记录操作前数据的完整信息
update/delete undo log
中的trx_id
,roll_pointer
是为了支持mvcc,并且还需要记录修改删除前后的列信息,便于回滚恢复记录
2.1.mvcc
如图多个版本的数据,在undo log中进行了记录,并且使用roll_pointer,进行串联,形成版本链。快照读查询语句执行前,或者使用start transaction with consistent snapshot(立即生成read view)
会生成一个read view(一致性视图,如下)
read view包含如下几个字段
- m_ids:在生成read view时,当前系统中活跃的读写事务id列表
- min_trx_id:生成read view时,当前系统中活跃的读写事务中最小事务id,也就是m_ids中的最小值
- max_trx_id:生成read view时,系统应该分配给下一个事务的事务id值
- creator_trx_id:生成该read view的事务的事务id
2.2如何利用一致性视图判断数据是否可见
- 如果被访问版本的
trx_id
和creator_trx_id
相同,意味着当前事务在访问自己修改的记录,自然可见 - 如果访问版本的
trx_id
属性值小于read view中的min_trx_id
表明此版本是生成read view之前已经提交的事务,那么自然可见 - 如果访问版本的
trx_id
,大于等于read view中的max_trx_id
说明,当前版本数据是生成read view后开启事务产生的,那么自然不可见 - 如果访问版本的
trx_id
介于min_trx_id
和max_trx_id
之间,需要判断trx_id
是否位于m_ids
列表中,如果在说明创建read view时生成该版本的事务还是活跃的,那么该版本,不可被访问,如果不在说明创建read view 时生成该版本的事务已经提交,可以被访问到
2.3Read Committed和 Repeatable Read的不同
-
Read Committed——每次读取数据前都生成一个Read View
这样可以保证生成Read view 中的m_ids是实时活跃事务id集合,也许第一次读取的时候事务A没提交,其id位于m_ids中,但是第二次读取的时候事务A提交了,事务A将不位于m_ids中,这样在第二次读取的时候,通过m_ids判断事务A是否提交的时候,可以得到事务A已经提交了,然后让事务A版本产生的数据可见(见2.2.4中的内容)。
-
Repeatable Read——如果使用begin开启事务那么在第一次查询的时候生成Read view,如果使用
start transaction with consistent snapshot
那么执行的时候就会生成read view这样可以保证当前事务从头到尾都是read view中记录的内容是一致的,第一次读取的时候事务A没有提交,那么不可见,但是第二次读取的时候事务A提交了,但是read view的
m_ids
和max_trx_id
可以判断事务A不可见,比如事务A事务id小于max_trx_id意味着生成read view是事务A启动但是没提交,即使第二次读事务A提交了,但是m_ids
中还是包含事务A,那么不可见。如果事务A事务id大于max_trx_id,那么自然第二次还是大于max_trx_id,也是不可见的,从而实现了可重复读。
3.写redo log
redo log 记录事务修改了哪个表空间(space id属性),哪个页(page number 属性),修改后的值(data属性)
即使是非常简单的一条变更sql,往往涉及到多出的改动,比如需改sql数据的字符数发生了变更,需要先删除,后插入。并且需要对上一条行记录的next_record 属性进行修改,页中行数据的修改,往往同样需要修改page header,page dictionary等内容,并且可能伴随着B+树节点分裂和合并。为了解决存在多种不同修改的问题,innodb存在多种类型的redo log。
3.1 mini-transaction
innodb 把一次变更分为多个mini-transaction(MTR)一个MTR包含一组redo log,这一组redo log以一个特殊类型的redo log作为类型,恢复的时候,这一组redo log具备原子性,只有检测到特殊类型的redo log才任何一组redo log是完整的才会进行恢复(B+树叶子节点的分裂,不能说分裂一半)
3.2 log buffer
生成redo log,会写入到log buffer,log buffer是一块连续的内存空间,由一个个大小为512B的log block组成,默认16mb大小。生成的redo log会找最小的一个redo log block 顺序写入
-
buf_next_to_write 标记redo log已经落盘的位置
-
buf_free 是标记buffer pool 剩下的空闲空间
3.3 redo log 刷盘的时机:
- 事务提交
- log buffer 空间低于50%
- 后台线程周期性刷盘
- mysql服务正常关闭
- 做checkpoint
3.4 redo log 进行崩溃恢复
从checkpoint_lsn位置开始读取redo log,来恢复脏页和undo log,然后通过undo log把所有未提交的事务的脏页进行回滚
七丶本地提交
提交阶段 innodb存储引擎需要落盘redo log,mysql服务器层需要落盘binlog
1.binlog
二进制逻辑日志,在逻辑备份和主备复制中发挥重要作用,具备三种格式
-
statement
每一条会修改数据的 SQL 都会记录在 binlog 中。
Statement 模式只记录执行的 SQL,不需要记录每一行数据的变化,因此极大的减少了 binlog 的日志量,避免了大量的 IO 操作,提升了系统的性能。
但是,正是由于 Statement 模式只记录 SQL,而如果一些 SQL 中包含了函数,那么可能会出现执行结果不一致的情况。比如说
uuid()
函数,每次执行的时候都会生成一个随机字符串,在 master 中记录了 uuid,当同步到 slave 之后,再次执行,就获取到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。
-
row
Row 格式不记录 SQL 语句上下文相关信息,仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节,这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。
不过 Row 格式也有一个很大的问题,那就是日志量太大了,特别是批量 update、整表 delete、alter 表等操作,由于要记录每一行数据的变化,此时会产生大量的日志,大量的日志也会带来 IO 性能问题
-
mixed
Row 格式不记录 SQL 语句上下文相关信息,仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节,这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。
不过 Row 格式也有一个很大的问题,那就是日志量太大了,特别是批量 update、整表 delete、alter 表等操作,由于要记录每一行数据的变化,此时会产生大量的日志,大量的日志也会带来 IO 性能问题
2.怎么保证binlog 和redo log状态一致
mysql采用了内部XA事务的机制保证binlog,和redo log的状态顺序一致,通过两阶段提交的方式实现,两阶段提交存在一个协调者和多个参与者,在mysql中binlog是协调者,redo log是参与者
2.1mysql的两阶段提交
- prepare阶段
- innodb刷redo log到磁盘,redo log刷盘完成后,修改事务状态为TRX_PREPARED
- prepare如果失败,那么事务会回滚,而prepare成功那么进入commit阶段
- commit阶段
- mysql服务器层写入binlog,写入完成后,修改事务状态为TRX_NOT_STARTED,表示事务已经成功提交
2.2宕机的处理
- 事务转换若为TRX_ACTIVE那么回滚事务
- 事务状态为TRX_NOT_STARTED 那么说明redo log 和binlog都成功落盘,这时候任务事务已经提交
- 恢复的时候如果发现事务状态为TRX_PREPARED,根据binlog的状态判断是提交还是回滚。
- 若binlog 写入失败,那么回滚
- 若binlog写入成功那么提交并修改事务为TRX_NOT_STARTED
3.redo log 和binlog 物理落盘策略
3.1 innodb_flush_log_at_trx_commit空置redo log的落盘
- 0表示每秒进行一次刷新
- 1表示每次事务提交时落盘
- 2表示每次事务提交都只写redolog缓冲写道操作系统缓存中,由操作系统决定何时刷盘
3.2 sync_binlog控制binlog的落盘
- 0 表示当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
- n表示当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
- 1表示每次事务提交都刷盘
八丶主备复制
主库写入binlog之后,备库的io线程会读取主库的binlog,并转存为本地的中继日志relay log,备库上的sql线程读取relay log并在本地执行
1.主备复制的策略
- 异步复制:主库写完binlog后即可返回提交成功,无需等待备库响应
- 半同步复制:主库接受指定数量的备机转储relay log成功的ACK后可返回提交成功(还支持超时时间,超时没有返回那么主库返回成功)
- 同步复制:主库等备库回放relay log执行完,事务之后才能返回提交成功
不同的策略,其性能和一致性要求不同,也影响到主库能否返回
九丶返回提交成功
至此mysql会给客户端返回成功
十丶脏页刷盘
innodb后台有专门的线程负责将buffer pool中的脏页刷新到磁盘
-
从LRU链表中的冷数据刷新一部分页面到磁盘
后台线程定时从LRU链表尾部扫描一些页面,扫描的页面数量可以通过
innodb_lru_scan_depth
指定,如果在LRU中发现脏页,那么刷新到磁盘 -
从flush链表刷新一部分页面到磁盘
后台线程也会定时从flush链表中刷新一部分页面到磁盘,刷新速率取决于系统是否繁忙
如果后台线程刷新的很慢,且有新的页面需要进行缓存,这时候会从LRU链表尾部看看是否有可以直接释放的非脏页,如果不存在那么需要刷盘然后缓存新的页。
标签:事务,log,记录,Update,InnoDB,MySQL,innodb,redo,id From: https://www.cnblogs.com/cuzzz/p/16990502.html