MYSQL 思维导图模板_ProcessOn思维导图、流程图

三大范式

第一范式

第二范式

第三范式

主从

分支主题

索引

优化

ICP优化

在某些情况下可以有效减少回表次数，比如在回表的时候直接判断数据是否符合，不符合就放弃该记录

FIC优化

如果没有开启该优化，每次创建或删除索引都要创建一张新表，重新定义表的索引结构，然后将该新表改名为旧表，并将旧表替换为这张表

Online DDL

在进行FIC的过程中，会阻塞除读操作以外的DML语句，性能较低
为了解决这个问题，可以将其间所有的写操作都记录在缓存中，等索引创建完成后再执行对应的语句
为了保证数据的一致性，不能操作正在创建索引的列上的数据

MRR优化

主要是减少离散读，将查出来的索引id排序好再去回表
在没有该优化如果查出来索引数量大于一定值（20%）则不会使用索引直接进行全表扫描

数据结构

B树

为什么不使用B树？

因为B树的非叶子结点也是要存放数据的，这就造成了非叶子结点能存放的数据量会少，三层B树只能存少量数据
另外如果要做范围查询还需要大量的中序遍历

B+树

非叶子结点

非叶子结点存储的内容主要为数据的 ID 以及对应的页号
因此一个非叶子结点（数据页）可以存放更多的指针

叶子结点

叶子结点存放的是具体数据
并且叶子结点之间是已双向链表的形式连接的

聚簇索引

存放的是数据的所有内容

非聚簇索引

存放的是数据的ID以及索引列的值

回表

回表指的是索引列不包含查询的列的时候，要取出ID然后去聚簇索引上再进行一次查询，取出所有所需要的数据

覆盖索引

覆盖索引指的是索引上包含了所有要查询的列，因此可以直接取出这些数据不需要再进行回表查询

跳表

为什么不使用跳表？

跳表的查询复杂度为O(logn)，如果要查找的页全部不在磁盘上，则需要做大量的磁盘IO
而相比于B+树来说，千万数据B+树最多只需要做3次磁盘 IO

hash表

为什么不使用hash表？

无法做模糊查询。比如 % 无法计算hash值
范围查询很麻烦

最左匹配原则

指的是查询时的顺序应该按照索引的顺序进行查询
否则不符合该原则后面的所有查询都不会走索引

索引失效

某些情况下使用select *

索引列上使用了运算/函数

字段类型不同

隐式转换，底层调用了函数

like 中 % 出现在左边

列对比

比如一张表有列id 和列age，这时候where id = age 会导致索引失效

使用or

如果or有任何一个字段没用到索引，都会导致索引失效

Order by

非主键且不加limit、不用覆盖索引

is not null、not exixt、not...等

记忆方式：LOL+-*/not null nomethod

索引设计

1. 读多写少
2. 根据索引的区分度来设计，越随机越好
可以通过show index命令查看Cardinality值，这个值越大则表示该索引离散度越好

事务

事务的四大特性

Atomicity 原子性

事务要么全部成功，要么全部失败

Consistency 一致性

指事务在执行过程中逻辑保持一致，由MVCC以及锁机制来保证

Isolation 隔离性

读未提交

会出现脏读和幻读和更新丢失

读提交

会出现幻读和更新丢失

可重复读

会出现更新丢失，可能会出现幻读，但innodb引用了间隙锁来解决了幻读
出现幻读的情况：先使用快照读，然后另一个事物提交，这时候使用当前读

串行化

没有任何问题

Durability 持久性

指的是对数据库的修改不会丢失，由日志系统来保证

MVCC机制

read_view

读未提交

没有read_view机制

读提交

每次查询都会新创建一个read_view

可重复读

普通查询会使用上一次的read_view
在select上加锁，select之前对数据库进行了写等操作会新创建一个read_view

串行化

不需要read_view

undolog

purge

一个后台线程，由它来决定删除哪些不需要的undolog记录

隐藏列

trx_id

事务id

roll_pointer

回滚指针，指向上一个版本的数据行

row_id

锁

锁粒度

行锁

锁粒度小，并发较好，开销较大，如果行锁数量过多，会退化为表锁（锁升级）

三种行锁

单记录锁

只锁住一个行

间隙锁

锁住多个行，前提是要有索引，从该索引开始，一直到不符合条件为止，中间的记录全部被锁住。
如果该索引是唯一索引，会退化为单记录锁

next-key-lock

上面两个锁的合体，一般是左闭右开

表锁

锁住一张表，多线程下并发较差

锁类型

共享锁

读锁

排他锁

写锁

意向锁

获取锁时先获取对应锁的意向锁
作用时当一张表需要做全表时不用一行一行去判断是否加锁，而去判断能否取得该意向锁

锁升级

指的是行锁升级为表锁

日志

binlog

日志格式

statement

row

作用

1. 选择一个时间点进行数据恢复
2. 用于主从同步

为什么binlog不具备数据恢复的功能？

因为binlog记录的是类似于SQL语句的东西，无法判断某条数据是否提交
因此不能用binlog来做宕机后的数据恢复

写入时机

根据两阶段提交协议，先写redo log。
当redo log写完以后，处于commit状态
这时候再写入到binlog里去

发生宕机

binlog不完整

事务回滚

binlog完整，切redo log处于prepare状态

提交事务

没发生宕机

redolog变为commit状态，事务提交

先写入binlog cache

刷盘策略

三种策略

每次只写不刷盘

每次提交事务都会刷盘

积累到N个事务后再刷盘

最多会丢失N-1个事务

redo log

日志格式

记录了对数据页的修改逻辑

作用

数据库宕机是对内存页的恢复

写入时机

当sql语句执行完，就将它记录在redo log buffer里面

两阶段提交协议

数据提交时先写到redolog，此时redolog处于prepare状态

然后将数据写入到binlog

最后将redolog和binlog里的数据对应，redolog处于commit状态

刷盘策略

当事务commit时，就将buffer里面的内容刷新到磁盘

三种提交策略

每一次commit都会进行一次刷盘

每秒钟一次刷盘

每次都只写到page cache中，不刷盘

group commit机制

checkpoint技术

解决问题

1. 加快宕机后的恢复
2. 保证缓冲池的可用性
3. 保证redolog的可用性

可能发生checkpoint的情况

1. 后台线程每隔一段时间进行一次刷脏页，控制脏页比率
2. 缓冲池不够用
3. redolog记录的内容超过了总大小的90%
4. 脏页太多了

刷脏页的时候还有连坐机制

慢查询日志

系统出现了慢查询

偶尔出现慢查询

innodb可能在刷脏页

经常出现慢查询

等待时间长

调整服务器参数

查询时间长

优化sql语句，优化索引

如何定位

设置long_query_time参数定义“慢”的阈值，如果 SQL 执行时间超过了long_query_time，则会认为是慢查询。
开启慢查询日志
使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志

架构

存储引擎

MyIsam

不支持事务

只有表锁

非聚集索引

不支持外键

可以没有唯一索引

记录了所有数据总数

Innodb

支持事务

支持表锁和行锁

聚集索引

支持外键

必须要有唯一索引

不记录数据总数，因为支持mvcc

内存

buffer pool

内部有两个LRU，一个是new lru，另一个是old lru
淘汰内存页时先淘汰old lru，然后再淘汰new lru
当一个old lru的内存页存活时间足够长，则会将它移到new lru
3/8 old区，每次有新数据页先把数据页放在lru的 3/8位置

change buffer

如果对数据页的修改时，该数据页不在buffer pool的时候，不会从硬盘上查询，而是直接将这些记录记录在change buffer中
等到该数据页被加载到内存的时候再将这些写操作写入到对应的数据页
前提是不是唯一索引，因为唯一索引要判判断是否正确，则需要直接将其加载到内存，这时直接修改内存页就行了

自增ID锁

为了保证自增id唯一，内部维护一个锁，当使用insert的时候会上锁，然后并不需要等到commit，等insert完成后（或者用完这个自增id后）就直接释放锁
优化：当有批量插入时，mysql会预先申请多个自增id，以避免重复加锁
前提是批量插入可预测，否则像insert ... from select 还是会使用原始策略

后台线程

Master Thread

IO Thread

mysql内部使用的AIO，因此会大量涉及到线程回调
包括select、insert、update、delete等

purge Thread

服务器与客户端之间的交互

sql语句执行流程

查询语句

连接器：判断是否有权限，是否为长连接/短连接
如果长连接太长，超过一定时间没有动静会断开，默认是8h

长连接过多容易OOM

定期断开长连接

5.7以后可以重新初始化资源

查询缓存

查询缓存以k-v的形式对结果进行存储

大部分情况下不建议使用缓存
因为数据库表一更逊缓存就会全部失效
除非很久都不更新的表才使用缓存

可以通过配置按需使用

分析器：对sql语句进行解析，判断是否有出错是否合法，然后生成语法树

优化器：MYSQL计算出一个代价值，决定使用哪一个索引

执行器：执行这条语句

如果用到辅助索引，则判断是否需要回表
不需要回表则直接返回数据
需要回表则从主键索引查找对应的数据页
看buffer pool中是否存在数据页，有则返回，无则从磁盘中读取

更新语句

一样要经过连接器、查询缓存、分析器、优化器、执行器

语句分类

更新语句

更新的是否是唯一索引

是：从内存中读数据页然后更新

否：先将更新写到change buffer
等到数据页换到内存中再将change buffer里面的内容写到数据页

赠删语句

更新到对应到内存页

写undolog，事务提交后再写入到redolog和undolog

数据行

结构

可变长字段的大小

NULL值列表，bitmap存储

next_record

记录头信息

delete_flag

n_owned：页面中组的大哥

heap_on：在页面的相对位置

真实数据

溢出列

对于占用存储空间非常多的列，在记录的真实数据只会记录页的一部分数据，然后把其他数据存储在其他页中
只存储前768个字节，然后用20字节存储分散的页的地址

临界点：一个页面最少存储两条数据，因此计算得出如果一个列长度长于8099，则会被分裂出去

数据页

大小

默认是16kb，必须要是系统页大小（4k）的整数倍
如果分配太小会导致数据库要不断从磁盘加载数据页到内存，并且一页能存的数据量也会更少
如果分配太大容易产生内存碎片
16k是统计出来的结果，并且很好的预读到后面的内容

结构

页与页之间是以双向链表的形式组织的
页里的数据是一个单向链表，并且有结构记录最大记录，因此页里的数据可以以二分的形式去索引出来

结构

文件头

校验和、lsn、页号等

页头

行记录

数据行和当前页最小id和当前页的最大id

空闲空间

数据页还没被使用的空间

页目录

当前页的索引

槽：一个槽为一个分组
分组中最后一行对应的地址偏移
分组内的行记录以单向链表的形式连接

文件尾

校验和、lsn。用于验证该数据页是否完整

查找

数据页查找

B+树

行记录查找

通过槽来二分查找

插入

找主键值比待插入记录的主键大且差值最小的槽

当一个槽记录超过8时，再插入一个记录则会拆成两个组

表空间

物理结构

组

一组大小为256M，一共256个区

第一个组的前三个页面是固定的
FSP_HDR：记录了整个表空间的整体属性以及这个组的所有的区的属性
IBUF_BItMAP：这个组的change buffer
INODE Entry数据结构

其他每个组的前两个页面是固定的
XDES：本组所有区的属性
IBUF_BITMAP：change buffer

区

一个区大小1M，一共64个页

因为B+树的每一层都是一个双向链表，如果两个页的距离太大，对传统的机械磁盘来说要重新定位磁头的位置，开销较大
因此为了尽量让数据页连续，引入了区的概念。区里面的数据页都是连续的

数据结构：XDES Entry

Segment ID：表示属于哪个段

ListNode：由XEDS Entry组成的链表

State：区的状态（空闲、有无剩余空间的碎片区、属于某个段的区等）

Page State Bitmap：区里面的数据页的bitmap，第一位表示这个页有没有被使用

页

表空间中最小的单位，16k

逻辑结构

段

因为b+树只会扫描叶子结点的记录，因此如果不区分这些区，要全部扫描一遍
因此innodb把存储叶子结点的区和非叶子结点的区放在一个集合，称为一个段
每个索引都有两个数据段

数据结构：INODE Entry

Segment ID：段的编号

NOT_FULL_N_USED：NOT_FULL链表中已经使用了多少个页面

3个链表，分别由FREE链表，NOT_FULL链表、FULL链表

Magin Number：表示这个段是否被初始化

Fragment Array Entry：零散页面

b+树的根节点保存着这颗b+树的段信息

碎片区

如果某些表记录太小，直接分配一个区会造成空间浪费，因此提出了碎片区的概念

碎片区里的页面有的属于段A，有的数据段B
当一个段占用了32个碎片区页面后，就开始以区为单位来分配空间

Buffer Pool

结构

向系统申请一块连续的内存

控制快...
控制块...

碎片

缓冲页...
缓冲页...

free链表

以链表的形式链着空闲页面对应的控制块

缓冲页hash处理

key为表空间号+页号
value为对应的控制块

flush链表

以链表的形式链着脏页对应的控制块

LRU链表

传统LRU链表的问题

预读

线性预读

如果顺序访问一个区的页面超过56，就会异步把下一个区的所有页面都读入内存中

随机预读

某个区连续13个页面都被加载到buffer pool中会异步读取这个区所有的页面到内存，默认关闭

全表扫描

把一张表所有的页面都读取进来

问题：如果其中有的页面不使用或者只使用1次，会让lru头部的页面淘汰掉，降低了buffer pool的命中率

划分区域的LRU

前5/8的页面为young链表，为经常使用到的
后3/8的页面为old链表，表示为不经常使用

针对上面两个问题的优化

预读：当预读的页面加载到内存后，如果不被访问到，就马上又被淘汰掉

全表扫描：由于加载到内存的页面可能会继续被访问到，因此规定两次访问页面之间间隔不超过1000ms才会被放到lru的头部

刷新脏页

从lru链表尾部刷新

从flush链表刷新

如果只从flush刷新，可能会导致把经常访问的页刷掉
如果只从lru刷新，可能lru尾部没有脏页，导致刷不回硬盘
后台线程繁忙时，会由用户线程帮忙刷脏页

多个buffer pool实例

由于不能直接修改buffer pool的大小，因此可以以chunk为单位进行buffer pool的增加和减少

redo log

结构：type｜space ID｜page number｜data

组

对一次底层页面的原子访问为一个组
一个组有多条redo log

因为一条更新语句可能涉及多个页的修改，比如对聚簇索引树的修改，二级索引树的修改等
比如对聚簇索引树的修改，可能会涉及到多个页面。对它的一次修改必须是原子的，否则可能生成一颗错误的B+树
在数据恢复时，从一条语句进行扫描，当扫描到表示结束的标志时，证明扫描完一组redolog，就对其进行恢复，否则放弃前面解析到的redo log。

不同事务产生的不同的组可以交替存储

如果一个组出错，意味着这个时候程序崩溃，导致后面的组的redolog会没记录

日志缓冲区

服务启动时向操作系统申请的一块连续的内存，单位为block，大小为512B

日志文件组

一个日志文件组有多个日志文件，每个日志文件的前4个block用来记录信息，其他的结构跟日志缓冲区基本一致

LSN

lsn表示当前总共已经写入的日志量

flushed_to_disk_lsn/buf_next_to_write 表示已经刷新到磁盘的日志量

flush链表的lsn

flush链表里面的控制块有两个值

oldest_modification：第一次修改这个缓冲页时开始的lsn

newest_modification：每修改一次页面之后的lsn值

checkpoint

核心是把buffer pool的页刷新到磁盘，刷新后redolog中被刷新的页的位置就可以被覆盖了

刷新时，计算可以被覆盖的redolog日志对应的lsn值最大为多少
-->把页从flush链表中刷新后，找到链表尾部的页的o_m值，将其赋值给checkpoint_lsn
然后更新日志文件的管理信息

崩溃恢复

hash表恢复

undo log

事务id

只有当事务对表进行增删改操作时，才会对这个事务分配一个事务id

事务id是一个全局变量，存储在系统表空间中页号为5的页面中
每当事务id为256的倍数时，就将该事务id刷新到系统表空间中
当系统重启时，读出这个变量的值，然后加上256

操作

insert操作

当insert操作时，就往undo log页面中插入一条记录，里面记录了主键的信息
insert操作除了向聚簇索引插入数据外，还会往辅助索引插数据。但是回滚时，只需要根据这一条undo log的记录就可以
找到对应聚簇索引的id及其对应的辅助索引

delete操作

阶段1:事务还没提交，会将对应的行记录的delete_flag标记标记为1
不直接删除的原因是，由于有mvcc的机制存在，可能还会被其他事务访问

阶段2:当事务提交后，并且已经没有其他事务引用这条记录，则由purge线程将其加入到垃圾链表

update操作

不更新主键

就地更新

如果更新后的结果和原来的结果长度相同，则可以直接更新

非就地更新

先删除旧记录，再插入新纪录
这里的删除是真正的删除，也就是说直接把该记录加入到垃圾链表

更新主键

先对旧记录进行delete mark操作，然后再增加一条insert 操作
也就是说更新主键要插入两条undolog

undolog页面

分为两类

insert相关的undolog

update相关的undolog

因为insert操作的undolog在事务提交可以直接删掉，但是其他类型的undolog要为mvcc服务，所以要区别对待

每个事务都有自己的insert undo和update undo链表

每一个undo log页面链表对应着一个段

回滚段

在系统表空间第5号，存着128个回滚段
每个回滚段有1024个slot，每个slot都对应着一个undolog链表（一个slot就是一个段）

一个回滚段由表空间+页号组成

0号回滚段必须在表空间
1-32号回滚段必须在临时表空间
33-127号回滚段既可以在系统表空间也可以在自己配置的undolog表空间

事务分配回滚页面的过程

1.先去系统表空间5号页面分配一个回滚段，采用的是循环分配（0-33-34-....-0-33-...）

2.在对应的回滚段的cache里看有没有可用的slot，如果有，直接使用这个slot

3.如果没有，就去对应的回滚段找一个slot给这个事务

4.如果不是在cache中获取的slot，还要重新分配一个段，然后将这个段的第一个页号填入slot中

崩溃恢复的过程

遍历所有不为空的slot，从第一个页面中找到对应的事务的状态，如果是active，表示是活跃事务
然后去找到这个undo链表最后一个页面的undolog header属性，找到对应的事务id，表示这个事务id的事务为未提交
然后将这个事务的更改全部回滚

mvcc

ReadView

每个事务在进行普通查询时，会生成一个ReadView

m_ids

表示生成readview时系统中的活跃事务id列表

min_trx_id

表示m_ids里最小的事务id

max_trx_id

表示应该分配给下一个事务的id

如何判断可见？

如果被访问的版本与creator_trx_id相同，表示是自己创建的，可见

如果被访问的版本小于min_trx_id，表示生成这个readview时这个版本已经提交，可以被访问

如果被访问的版本大于等于max_trx_id，表示该版本是创建这个视图之后才提交的，不可见

如果被访问的版本介于min_trx_id和max_trx_id之间，看看该版本是否存在m_ids。
如果在，表示这个版本还没提交（事务还处于活跃状态），不可见
如果不在，表示创建这个视图的时候事务已经提交，可见

二级索引

由于只有聚簇索引才会有id隐藏列，因此二级索引要多加一些判断

当对二级索引做增删改操作时，如果该事务id大于页头的事务id，就会把这个事务id改成操作的事务id
当有其他事务进行查询的时候，先看一下他自己的min_trx_id是否大于这个页头的事务id，如果是，则表示这个页面所有的内容都可见
如果不是的话一定是有其他事务对这个页面进行了修改

否则，需要根据主键值进行回表操作，根据roll_point指针找到第一个可见的版本，然后再判断该版本二级索引列的值是否和该二级索引查询时的值相同

purge

一个事务产生的一组undolog都有一个undo log header部分，里面有一个history链表
当一个事务提交后，就会把这个事务所产生的undolog日志插入到history链表的头部

当不在有事务需要访问这些undo log以及标记了delete mark的数据后，就由purge线程来对其进行回收

锁

读写情况

写-写

当对记录进行修改时，会创建一个锁结构

trx信息：表示是哪个事务

is_waiting：表示是否等待

当记录上没有锁结构时，is_waiting为false；否则为true

当释放锁时，检测一下有没有相关的锁结构，有的话则唤醒

读-写或写-读

方案1: mvcc。可以保证在readview里读写不会冲突

方案2: 加锁。对于某些场合不允许读数据的旧版本，因此可以加锁

多粒度锁

意向锁：当往一个数据加锁时，需要先获得这张表相对应的意向锁，并加在这张表上

意向锁作用：当需要对整张表整体加锁时，不需要对这张表进行遍历，直接查看是否有意向锁即可

Innodb里的锁

表级锁

S、X锁

主要用于在对整个表结构操作时才会加这个锁

IS、IX锁

同上

AUTO_INC锁

对自增值的加锁，自增完会立即释放

行级锁

Record Lock

记录锁

Gap Lock

给这个记录的前面的间隙加锁

如果要加后面间隙的锁，可以用到隐藏记录Supremum，给这个隐藏的最大值记录加锁

Next-Key Lock

Record Lock + Gap Lock

隐式锁

由于生成锁结构需要成本，因此出现了隐式锁，并且只能用在insert的记录上

如何判断是否有隐式锁？事务id

锁的内存结构

锁的事务信息

索引信息

表锁/行锁信息

Space iD

Page Number

n_bit：一个bit对应这个这个页的一条数据

type_mode

锁的信息，比如读/写锁？类型？等

其他信息

bit位

对应的在表中的位置（heap_no）