MySQL存储引擎原理拆解以及设计深度剖析

MySQL存储引擎原理拆解以及设计深度剖析

页结构

页头：记录页面的控制信息，共占56字节，包括页的左右兄弟页面指针（双向链表）、页面空间使用情况等。

最小最大虚记录：比页内最大小主键还大小确定一个范围，即一个页的空间，查看时，根据和最大最小虚记录的比较结果判定是否在这个空间内。

记录堆：行记录存储区，分为有效记录和已删除记录

自由空间链表：存储记录堆中，存储的过程中可定有被删除的记录，自由空间链表的作用就是吧删除的记录链起来，方便被删除的记录有什么，找到这些被删除的空间的目的是，想要利用这些空闲的空间

Slot区：

页内记录维护

页内维护记录
页内维护记录
B+tree

聚簇索引：索引和数据是在一起等

顺序保证

物理有序：
一开始放入数据，主键为10，之后放入的数据主键为9，为了保证物理有序，就要向后移动10，再插入9，同理如果插入是8，就要移动9、10，同样删除的过程也涉及到复制的过程这样的效率就会很低。但是查询的数独就很快，直接指针偏移，可以采用二分查找。

逻辑有序：（链表data域，指针域）
插入只需要新申请一个空间之后，修改指针域，这样插入的效率高，但是查询遍历的过程很慢。

MySQL innodb 显然使用的是逻辑有序，查询可以优化，显然物理实在在插入的过程很困难。

页内单项链表，页见双向链表
页内单项链表，页见双向链表
页面内单向链表，保证数据有序，页面间双向链表这样就把数据有序的链接起来了

根据这个原理，之前的聚簇索引就可以加入双向链表

双向链表页面维护记录
双向链表页面维护记录
​ 可用的空间有自由空间链表和未使用空间，首先要考虑把已删除的空间利用起来，但是也会产生碎片的，频繁的插入删除的表需要定期的做收缩

页内查询

逻辑连续 遍历

物理连续 二分查找，但是二分查找需要每个数据大小是等长的，通过偏移量找具体的位置。同时也可见一定是逻辑有序

页内查询
页内查询
最下方是Solt，分成若干个槽位，每个槽位又对应着一个链表，这样可以根据槽位进行二分查询，找到数据可能在的区间。再小范围的便利一下（类似于跳表）

MySQL InnoDB存储引擎内存管理

1预分配内存空间

内存空间
内存空间
2数据以页为单位加载
每次读取一页的数据到内存当中

为什么是一条：因为减少i/o操作，提高i/o性能，可能访问一页的多条数据

3数据内外存交换
内存满了，内外存交换，把内存中的变换的部分写回磁盘，不变的部分释放后加载新的页，读的就不需要写回磁盘

InnoDB技术点

内存池：预分配的内存

内存页面管理：

页面的映射：磁盘数据加载到内存，这个关系不是静态的关系是动态的过程，形成映射表，记录内存和磁盘的关系

页面管理：空闲页，数据页（clean page），脏页（加载后被修改的页，要刷回磁盘）

内存淘汰：是否刷盘

页面淘汰 LRU 热数据放到头，冷数据放到尾

首先访问P2页面把P2放到头部，当需要访问新的页面，而内存空间已经满了，只能将尾部的P1释放掉，将P7I/O到头部

思考：全表扫描堆内存的影响

eg：500g数据 50g内存（热）

select * from 表，整个内存都是表的数据，这样内存全部的热数据都会被淘汰掉，这样叫内存失效或者内存污染，MySQL做全表扫描很慢，这个慢是i/o慢，select执行完之后又会很快，这说明热数据并没有被淘汰

解决方案：避免热数据被淘汰

1.访问时间+频率

页面

A—— —— —— - —— ——

B- - - - – - - - – - - - - - - - - -

C- - - - - - - - - - -

此时按照传统LRU会淘汰A，但是显然我们需要淘汰的是C，Redis做到了按照频率淘汰，会按照频率增加，不访问又会下降

2.两个LRU表

一级放，热度到一定级别的时候加入，二级的LRU，可以认为是热数据表

InnoDB MySQL是怎么解决的

InnoDB MySQL 双LRU解决方案
InnoDB MySQL 双LRU解决方案
Buffer Pool ：预分配的内存

Page：Buffer Pool 的最小单位

Free list：空闲page组成的链表

Flush list： 脏页链表

Page hash 表：维护内存Page和文件page的映射关系

LRU：内存淘汰算法 链接起来clean page和dirty page进行LRU 但是是优化的

Mysql 内存管理—-LRU

MySQL 内存管理LRU

MySQL 内存管理LRU
页面装载：先从Free list找一个空闲空间free page，建立映射关系就可以写入数据，插入LRU-old当中，即插入冷表的头部

MySQL 内存管理LRU淘汰策略
MySQL 内存管理LRU淘汰策略
如果没有空闲位置LRU-old的尾部淘汰，如果刚好尾部数据正在使用，找一个dirty page进行Flush操作刷盘，释放内存

Free list 取>LRU-old 淘汰>LRU Flush

dirty page进行Flush操作刷盘，释放内存,讲dirty page变为clean page，是放在LRU尾部？还是直接放在Freelist。

5.2之前是放在尾部淘汰一次，目前使用的是直接放Free list

位置移动
1.从old到new，设想访问频率高的就放到new区，但是如果select*，加载一个页面会在短时间内多次访问一个页面，这样还是会把素有的new全部替换造成内存失效，内存污染。

所以移动的时机要考虑

innodb_old_blocks_time 大于这个时间有机会进入new区，大于时间还有人访问，这样select 很快访问完就不去访问了

2.从new到old 有页面从old到new 但是midpoint始终保证5:3自然而言new到尾部就进入到old

LRU_new到操作
链表有访问就放到头部？要考虑lock！MySQL设计思路：减少移动到次数

两个重要的参考：1.freed_page_clock:Buffer Pool淘汰页数。

​ 2.LRU_new长度的1/4

LRU_new中的A页面被访问，移动到head，此时的freed_page_clock为10000，上次移动到head的freed_page_clock是8000差值是2000用差值和LRU-new长度的1/4比较，如果大于就需要移动一次，这样可以保证数据不被误淘汰，也能减少移动的次数。

MySQL事物实现原理拆解以及设计深度剖析

MySQL事务的基本概念

事务特性

A 原子性 （atomicity）:强调事务的不可分割. 全部成功或者失败
C 一致性 （consistency）:事务的执行的前后数据的完整性保持一致. 通过AID保证
I 隔离性 （isolation）:一个事务执行的过程中,不应该受到其他事务的干扰 并行事物之间互不干扰
D 持久性（durability） :事务一旦结束,数据就持久到数据库 事物提交之后永久生效
事务并发问题（隔离）

赃读（Dirty Read）：读到未提交的数据。

不可重复读（Non-repeatable read）：两次读取结果不同

幻读（Phantom read）：select操作得到的结果所表征的状态无法支撑后续的业务操作（读到数据蒙圈，不知道干什么）

隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

eg：T1读区数据100，T2修改这个数据为200，T1再次读区数据为200，T2 rollback 100

Read Committed（读取提交内容）读已提交

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

eg：T1度数据100，T2 update 200，T1还会读到100，直到T2commit200，T1会读到数据200，可见T1点数据受到了干扰。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

eg：T1度数据100，T2 update 200，T1还会读到100，直到T2commit200，T1还会读到数据100，两次当前读不回读到不同数据

Serializable（可串行化）没人用

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务实现原理

MVCC
对版本并发控制

当前读 读存储引擎读数据

快照读 历史版本

一个uid会对应多个历史版本，历史版本由GB-TRX-ID记录，DB-ROLL-PTR记录上一条数据读存储位置，可以找到快照读位置，undolog中，存储引擎只存最新的一个数据。

解决读-写冲突

隐藏列

Read View
Read View
Read View

在执行select的同时，生成一个活跃事务列表eg【9，100，110，120】，此时可见性判断，小于9时已提交的，是可以看到的，大于9小于121是还未提交的事务，在事务链表中，要回退上一版本，不在事务链表，说明已经提交可以看到，大于121时创建快照之后创建的事务。想要回滚找历史版本。

undo log 回滚机制 历史版本都会存储在undolog
回滚日志

保证事务原子性

实现数多版本：mvcc的历史版本的实现原则

delete undo log：用于回滚，提交即清理

update undo log：用于回滚，同时实现快照读，不能随便删除，因为要快照读

思考：undolog如何清理：依据系统活跃的最小事务的id Read View，都能看见了就没有必要存储历史版本了。

​ 为什么innodb count （*）这么慢？

redo log
实现事务的持久性

记录修改

用于异常恢复

循环写文件

循环写文件
循环写文件
数据处理过程

数据处理过程
用户client发起请求到sever层转成SQL命令，修改数据命令，先记录undo log，历史版本用于回滚数据，更新内存数据，先把修改数据这件事写到redo log，更新完成，server执行一条提交一条，commit redo log刷盘，commit日志

刷盘时机

刷盘时机
redo——log刷盘时机
0: 写Buffer Pool，commit写内存Buffer，每秒聚合一次写文件，并刷盘，也就少受每秒刷盘最多丢1秒，性能极高，不安全

1：每次commit都刷盘，最严格，效率极低，最多丢1条。

2:每次commit提交写文件但是写的是OS Buffer每一秒刷一次盘，性能折中。

意义
体积小，日志的体积很小，页面的记录很多，记录页的修改，比写入页的代价低，小代价记录更新

末尾追加，随机写变顺序写入，发生改变的页固定不变

MySQL锁实现原理拆解以及深度剖析

锁粒度

行级锁

作用在索引上

聚簇索引&二级索引

eg ： delete from user where uid（pk）=134 ，锁索引和phone ，或者通过二级索引锁

前提是唯一索引，所以分析锁要有前提

RC： delete from user where uid（pk）=134 ，之后插入134/114，这时候会读到114这个就出现了幻读，不可重复读，RC隔离级别

RC 不可重复读

RC 不可重复读
RR： delete from user where uid（pk）=134，在红色三角出插入gap锁间隙锁

GAP锁锁住的锁

【131，140】-【134，130】

【134，130】-【134，150】 134 任何记录插不进去

【134，150】-【137，100】

RR可重复读 GAPj间隙锁

RR可重复读 GAPj间隙锁
间隙锁 两次当前读之间，其他的事务不回插入新的满足条件的记录

解决课重复读模式下的幻读问题，两次当前读读幻读问题

GAP锁不是加在记录上

GAP锁锁住的位置，是两条记录之间的GAP

保证两次当前读返回一致的记录

表级锁

线上锁表，只有全表扫面

select from user where phone = 134

phone没有建索引会全表扫描，每一个间隙建立GAP锁，所有记录加锁返回，然后有MySQL Server 进行行过滤。但是server会释放锁

全表扫描

全表扫描
类型

共享锁（s）

读锁 可以同时被多个事务获取，阻止其他事务对记录的修改

排他锁（x）

写锁 只能被一个事务获取，允许获得锁的事务修改数据
多有的当前读加排他锁，都有哪些是当前读 select for update，update，delete

InnoDB加锁过程

T1: update t user SET xx=xx where name = ‘f’

T2：select * from t_user where age>33 FOR UPDATE

死锁

死锁
加锁失败，事务1持有事务2的锁 ，事务2有事务2的锁，这样事务1加锁失败，事务2也加锁失败，死锁