MySQL数据库底层思维导图模板_ProcessOn思维导图、流程图

引擎特性

支持事务

支持行锁

支持外键

引擎特点

插入缓冲 (提升性能)

Insert Buffer

注:插入缓冲是物理页

特性

对于非聚集索引的插入或者更新操作,不是每一次都直接插入索引页中 而是先判断插入的非聚集索引是否在缓冲池中,如果在直接插入 若不在,则放入一个Insert Buffer对象中

缺点

当在大量插入时宕机,数据库恢复需要很长时间

插入缓冲实现

数据结构

放在共享空间中的全局B+树

插入过程

当一个辅助索引要插入到页时,如果该页不在缓冲池中,首先会构建一个search key  接下来查询Insert Buffer这颗B+树,然后将记录插入到叶子节点中.

合并插入缓冲

Insert Buffer Bitmap会追踪辅助索引页可用空间

发生情况

1.辅助索引页被读取到缓冲池中时

2.Insert Buffer Bitmap页追踪到该辅助索引页以及没有可用空间时

3.Master Thread的循环

具体分类

Insert Buffer

Change Buffer

Delete Buffer

Purge Buffer

满足条件

索引是辅助索引

聚集索引在插入时效率很高,不需要像辅助索引一样离散插入

索引不是唯一的

如果时唯一的,那么在插入时需要去查找,会浪费大量时间

二次写 (可靠性)

特性

在应用重做日志进行还原之前需要一个页的副本,该副本就是double write

组成

doublewrite buffer

2MB

物理磁盘上共享空间中连续的128个页

2MB

原理

在对缓冲池中脏页进行刷新时,并不直接写磁盘

先将脏页辅助到内存中单doublewrite buffer中

通过doublewrite buffer分两次每次1MB顺序写入共享表空间的磁盘上,然后马上调用fsync函数同步磁盘

流程图

自适应哈希索引

优点

访问速度极快

缺点

不能顺序访问

构造条件

1.以一个同样的查询条件查询了100次

2.页通过该模式访问了N次,N=页中记录*1/16

异步IO

IO Merge

可以将多个IO合并为一次IO

异步方式读取

刷新邻接页

当刷新一个脏页时,引擎会刷新所在区的所有页,如果时脏页则一起刷新,即可以通过AIO进行IO Merge

可以通过参数关闭开启

预读

体系结构

后台线程

Master Thread

核心线程

主循环

每秒操作

日志缓冲刷新到磁盘(总是)

合并插入缓冲(可能)

至多刷新100个缓冲池中的脏页到磁盘(可能)

如果没有用户活动,切换到后台循环(可能)

每十秒操作

刷新100个脏页到磁盘

合并至多五个插入缓冲

将日志文件刷新到磁盘

删除无用的undo页

刷新100个或10个脏页到磁盘

后台循环

删除无用的undo页

合并20个插入缓冲

跳回主循环

刷新循环

将缓冲区数据刷新异步刷新到磁盘

脏页刷新

合并插入缓冲

undo页的回收

IO Thread

使用AIO处理IO请求

Purge Thread

回收已经使用并分配的undo页

在1.1版本之前,在Master Thread中换成该操作

1.1版本之后purge操作独立到Purge Thread中完成

Page Clean Thread

在1.2版本以后,将主线程中脏页刷新的操作完成

内存

缓冲池

数据页

索引页

插入缓冲

锁信息

自适应哈希索引

数据字典信息

淘汰算法

LRU算法

将新加入缓冲池中的页加入距离尾端3/8位置,在一定时间后,将数据前置到热点位置

加入LRU List位置可以自定义

数据成为热点的时间可以自定义

3/8处之前的数据被成为new list,之后的数据被称为old list

内存分配(在1.0版本之后支持压缩页)

假定分配4KB内存

1.检查4KB的unzip_LRU列表.查看是否有可用的空闲页

2.有则直接使用

3.没有则检查8KB的unzip_LRU列表

4.若有则将8KB的页分为两个4KB,存放到4KB的unzip_LRU列表中

5.若没有则申请16KB的内存页,分为两个4KB,一个8KB

注:页默认大小为16KB,所以无需再放大一次

Free List

当数据库刚启动时,LRU List中的数据为空,此时页数据存放在Free List中,当需要从缓冲池中分页时,先去Free List中找,找到则将数据置于LRU List中,并从Free List删除

Flush List

在LRU中的数据页被修改过后,被称之为脏页,此时数据库会通过Check point机制将脏页刷新回磁盘

重做日志缓冲

一般情况下数据库每一秒将重做日志缓冲刷新到日志文件中

当每个事务提交时将重做日志缓冲刷新到日志文件

当重做日志缓冲池剩余空间小于1/2时,重做日志缓冲刷新到日志文件

额外的内存池

Checkpoint技术

解决问题

1.缩短数据库回复时间

2.缓冲池不够用时,将脏页刷新到磁盘

3.重做日志不可用是,刷新脏页

4.LRU溢出数据为脏页时,刷新到磁盘

种类

Sharp Checkpoint

在数据库关闭时,将所有脏页刷新到磁盘

Fuzzy Checkpoint

Master Thread Checkp

Flush_LRU_List_Checkpoint

在1.1版本之前,需要检查LRU有100个空闲页可用

Asnyc Flush Checkpoint

重做日志缓冲不可用时,需要强制将页刷新道磁盘

Dirty Page Too Much Checkpoint

脏页数量过多

索引

B+树索引

聚集索引

按照主键构建,一张表只有一个聚集索引

对主键的排序,范围查找迅速

非聚集索引

别名

稀疏索引

辅助索引

用到非聚集索引时,数据库会先查找非聚集索引得到数据主键,再通过主键查询聚集索引获取数据

Cardinality

表中不重复数据数量的预估值

统计方式

采样统计

具体流程

1.获取B+树索引中叶子节点的数量,记为A

2.随机取得B+树索引中的8个叶子节点,统计每个页不同记录的个数,记为P1--P8

3.Cardinality的预估值为Cardinality = (P1+ P2 +....+P8)*A/8

更新策略

1.表中1/16的数据发生了改变

2.表中变化数据大于2 000 000 000次

索引优化

MRR

Multi-Range Read

在查询完辅助索引后对查询的主键进行排序,然后使用顺序读获取数据,从而减少IO次数

对查询条件进行拆分(范围查询拆分为键值对),再通过拆分出的查询条件进行查询,从而过滤不符合条件的数据

ICP

Index Condition PushDown(索引下推)

在引擎层面对Where条件进行过滤

哈希算法

InnoDB使用哈希算法对字典进行查找,冲突机制为链表机制,哈希函数为除法散列

对于除法散列M的值,为大于缓冲池页数量2倍的质数

锁

锁的类型

共享锁

允许事务读一行数据

排它锁

允许事务删除或者更新一行数据

意向共享锁

事务想要获得一张表中某几行的共享锁

意向排它锁

事务想要获得一张表中某几行的排它锁

总结

写写不兼容

非双意向读写不兼容

一致性非锁定读

如果读取正在执行delete和update的数据时,不会等待锁的释放,而是会通过多版本控制机制读取行的一个快照版本

实现

通过undo段实现读取历史数据

undo段用于事务的数据回滚,所以没有本身的开销

条件

在Read Committed隔离级别下使用

读取锁定行最新的快照

在Repeatable Read隔离级别下使用

读取事务开始时的快照

一致性锁定读

Select ... for update

对读取行记录加X锁

Select ... lock in share mode

对读取行记录加S锁

自增长与锁

对于每个含有自增长的表都有一个计数器,每次插入数据时会获取计数器的值,并加锁插入,在插入完成后立即释放,不需要等待事务的完成

在InnoDB中自增长的列必须是索引,而且是索引的第一个列,不然会报错

锁的算法

行锁的三种算法

Record Lock

单个行上的锁

Gap Lock

间隙锁,锁定一个范围,不包括记录本身

Next-Key Lock

Gap + Record

技术:Next-Key Locking

用于解决幻读问题

在查询一个范围时,会对查询范围加Next-Key Lock,保证不会有新数据插入从而避免幻读

在唯一索引的情况下降级为Record Lock

锁的问题

脏读

读到别的事务中没有提交的数据

幻读

在一次事务中两次读数据,第二次读到第一次中不存在的数据或者不一样的数据

丢失更新

一个事务的更新操作会被另一个事务覆盖

解决方法

使用Select ... for update(即加X锁)

事务

ACID

原子性

数据库事务是不可分割的工作单位

一致性

事务将数据库从一种状态转变为下一种一样的状态,在事务开始到结束之后,数据库的完整型约束没有被破坏

隔离性

事务的隔离性要求事务在提交前对另外的事务都不可见

持久性

事务一旦提交,其结果就是永久的

事务分类

扁平事务

带有保存点的扁平事务

变种

链事务

只能回滚到最近一个版本

嵌套事务

分布式事务

事务的实现

redo(物理)

重做日志缓冲

重做日志文件

Force Log at Commit机制

当事务提交时,必须先将事务的重做日志写入重做日志文件进行持久化

重做日志

redo log

保证事务的持久性

顺序写,不需要对redo进行读取

undo log

事务回滚以及MVCC版本控制

需要对undo log进行随机读写

log block(重做日志块)

512字节

与扇区大小一致,不需要doublewrite技术保证原子性

log group(重做日志组)

逻辑概念

每个log group中的日志文件大小相同

重做日志文件总大小小于512G(InnoDB 1.2版本)

undo(逻辑)

完成的是与语句相反的工作(例如:Insert对应delete)

undo log产生的同时会伴随这redo log,需要持久性保护

purge

在使用delete或者update时,数据并没有被真正的删除,而是将delete flag置为1,甚至没有产生undo log日志 真正的删除操作在purge中完成