MySql实战原理思维导图模板_ProcessOn思维导图、流程图

5.Linux存储系统

VFS层

文件系统

NFS文件系统

Ext2文件系统

Ext3文件系统

Page Cache

通用Block层

IO调度层

Block设备驱动层

Block设备层

6.MVCC多版本并发控制机制

unon log多版本链条

txr_id(事务id)

roll_pointer(指向本次修改之前的undo log)

readView机制

核心字段

m_ids

此时有那些还没提交的事务id

min_trx_id

m_ids里最小的值

max_trx_id

mysql下一个要生成的事务id，就是最大事务id

creator_trx_id

当前的事务id

RC隔离级别

每次查询都会创建一个readView

运行期间m_ids中已提交的事务会被移出m_ids列表，提交的数据可以被查询到

RR隔离级别(默认)

trx_id大于max_trx_id的事务数据不能被查询到

readView一但被创建m_ids列表就不会被更改

读取数据默认开启mvcc机制，不需加锁

7.事务并发

问题

脏写

多个事务修改同一条数据，某一事务回滚导致值被覆盖的问题

脏读

一个事务读取其他事务未提交的数据，其他事务回滚了，下次就读不到了

不可重复读

同一事务中多次读取同一条数据，别的事务老是修改数据值，多次读取的值不同

幻读

同一事务中进行范围查询，每次查到的数据不同，别的事务插入新数据，会读到更多的数据

隔离级别

read uncommitted(读未提交)

避免脏写问题

read commit(读已提交)

不会发生脏写和脏读问题

repeatable read(可重复读)

不会发生脏写、脏读、不可重复读问题

mysql的RR级别解决幻读问题

serializable串行化)

强制事务只能串行化执行

8.锁\索引

锁

行锁

独占锁(写锁) X锁（Exclude锁）

trx_id、等待状态(false\true)

X锁与X锁之间需要等待锁释放

select * from xxxx for update 加的是独占锁，表明查出来的数据要update

共享锁(读锁) S锁

S锁与S锁不会互斥

S锁和X锁是互斥的

表锁

意向独占锁

意向共享锁

主键索引

索引页

页目录

数据页最小主键

页号

行实际物理位置

有序单向链表

B+树存储

同一层级索引页双向链表

索引页放满也会造成分裂，再形成更上层的索引页

聚簇索引

如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引

数据页

双向链表

页分裂

如果数据不是自增的话，会有一个数据挪动的过程，保证下一页的数据主键会大于上一页的数据主键

数据挪动

数据行(记录)

行类型

开始（2）

普通数据（0）

结束（3）

单向链表

按照数据大小有序排列

二级索引

重新弄一颗B+树

数据页

主键

多个索引字段

有序单向链表

回表

通过索引找到主键数据后，还需要进行回表查询，查聚簇索引把其他字段查出来

索引匹配规则

等值匹配规则

=

最左侧列匹配

最左前缀匹配原则

like A%

范围查找规则

Order by排序使用索引

order by字段在联合索引中，可以走索引进行排序

Group by分组使用索引

group by字段在联合索引中，可以走索引进行组合

mysql的一些值得思考的问题

spring的事务传播及其底层原理

REQUIRED：如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。Spring的默认事务传播类型

SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行

MANDATORY：使用当前的事务，如果当前没有事务，就抛出异常

REQUIRES_NEW：新建事务，如果当前存在事务，把当前事务挂起（暂停）

NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起

NEVER：以非事务方式执行，如果当前存在事务，则抛出异常

NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作

mysql的隔离级别使用

mysql为何默认设置为RR：可重复读级别

主从复制问题：binlog日志顺序错乱

正常互联网公司设置为RC：读已提交

1.首先不可能使用读未提交或串行化

2.到底使用读已提交还是可重复读

两个疑问

事务不生效的一些原因

mysql存储引擎设置必须为inodb

@Transactional必须加在接口的实现类上，不能加在接口上

类的内部方法调用不生效

必须抛出异常才可以进行回滚操作，如果dao方法中catch了异常，则不会回滚

1.存储引擎

innoDB

版本

老版本 InnoDB

支持ACID、行锁设计、MVCC

InnoDB 1.0.x

继承了上述版本所有功能，增加了compress和dynamic页格式

InnoDB 1.1.x

继承了上述版本所有功能，增加了Linux AIO，多回滚段

InnoDB 1.2.x

继承了上述版本所有功能，增加了全文索引支持、在线索引添加

SQL执行组件

SQL接口

解析器

优化器

执行器

InnoDB关键特性

插入缓冲（Insert Buffer）

优点

然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能

InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入

若不在，则先放入到一个Insert Buffer对象中好，似欺骗数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置

使用条件

索引是辅助索引（secondary index）

索引不是唯一（unique）的

两次写（Double Write）

自适应哈希索引（Adaptive Hash Index）

异步IO（Async IO）

刷新邻接页（Flush Neighbor Page）

执行步骤

1.从磁盘文件加载缓存数据到BufferPool中

2.将旧数据的值写入undo日志中，以便回滚

3.执行器更新Buffer Pool的缓存数据

4.写redo日志到Redo Log Buffer中

5.准备提交事务，将redo日志写入磁盘

6.准备提交事务，将binlog日志写入磁盘

7.将写入的binlog文件于位置更新到redo日志中，写入commit标记

8.后台IO线程随机将内存更新后的脏数据回刷磁盘

组件

缓冲池（Buffer Pool）

缓存页

数据缓存哈希表

free链表

flush链表

lru链表

undo日志

redo Log buffer

redo日志文件

binlog日志文件

后台线程

Master Thread

Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等

IO Thead

在InnoDB存储引擎中大量使用了AIO（Async IO）来处理写IO请求，这样可以极大提高数据库的性能

而IO Thread的工作主要是负责这些IO请求的回调（callback）处理

InnoDB 1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO thread

从InnoDB 1.0.x版本开始，read thread和write thread分别增大到了4个

根据参数innodb_read_io_threads及innodb_write_io_threads来设置的读写线程，并且读线程的ID总是小于写线程

Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页

而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能

在InnoDB 1.1版本中，即使将innodb_purge_threads设为大于1，InnoDB存储引擎启动时也会将其设为1

从InnoDB 1.2版本开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收

同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能

Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。

需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中

倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除

并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024

2.执行计划分析

type

const

本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效

ref

本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效

range

本质都是基于索引查询，只要你索引查出来的数据量不是特别大，一般性能都极为高效

index

扫描二级索引的意思，就是说不通过索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，大家尽量还是别出现这种情况

all

直接全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描

多个索引树，如果不全都是等值匹配的时候，会取数据少的索引树进行遍历，然后回表查询字段

如果是多个索引树是等值匹配的时候，会将结果都查出来之后取交集，再进行回表查询字段

3.核心组件

innoDB buffer_pool(缓冲池)

多个Buffer Poll实例

从InnoDB 1.0.x版本开始，允许有多个缓冲池实例

每个页根据哈希值平均分配到不同缓冲池实例中

SHOW ENGINE INNODB STATUS

默认大小128MB,按机器Buffer Pool可以配置2GB大小

数据页类型

索引页

数据页

undo页

插入缓冲(insert buffer)

自适应哈希索引(adaptive hash index)

InnoDB存储的锁信息

lock info

数据字典信息(data dictionary)等

chunk

默认大小128MB

innodb_buffer_pool_chunk_size = 128MB

多个chunk组成一个完整的Buffer Pool

多个chunk共享一套lru、free、flush链表结构

数据描述块

数据页

每页默认16kb

一行数据

每次加载一页或者多页

缓存页

更新缓存页的数据，由于跟磁盘数据不一致，导致缓存页变成脏页

淘汰缓存页

把缓存页进行刷盘操作，释放空闲缓存页到free链表

预读机制

1.如果访问一个区里的数据页超过一定的阈值会触发预读机制，把下一个相邻区里的所有数据页都加载到缓存里

innodb_read_ahead_threshold

2.如果Buffer Pool缓存了一个区里13个相邻的数据页，而且这些数据页被频繁访问的时候，也会触发预读机制，将这个区里剩下的数据页加载到缓存里

innodb_random_read_ahead

数据缓存哈希表

key-value数据结构

&lt;表空间号+数据页号,缓存页地址&gt;

使用数据页的时候先读哈希表，如果不存在就加载数据页

内置链表数据结构

free链表

双向链表

记录空闲的缓存页，使用一个缓存页就会从free链表中移除

当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。

count基础节点

引用链表的头节点、尾节点

lru链表(核心数据结构)

midpoint位置

新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置

在默认配置下，该位置在LRU列表长度的5/8处

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。

挂载逻辑

每次从磁盘加载一个数据页到缓存页的时候，缓存页的数据描述块会放到lLRU链表的表头位置

每次查询、修改数据的时候，都会放到LRU链表的表头位置

没有空间的缓存页时，会将尾部节点的缓存页进行刷盘，释放free链表、flush链表

冷热数据

冷数据默认占比3:7

innodb_old_blocks_pct

参数innodb_old_blocks_pct默认值为37，表示新读取的页插入到LRU列表尾端的37%的位置（差不多3/8的位置）

热数据区

对冷数据再次进行访问的时候，会将数据放去热数据区的头部

热数据区域的后3/4部分的缓存页被访问，才会移动到链表头部去

冷数据区

第一次加载数据的时候会加载到冷数据链表的头部

1s秒之后再次访问缓存页，会将这个缓存页放到热数据区的链表头部去

预读机制加载的数据页会放在冷数据区的头部位置

缓存页不够的时候，直接释放冷数据区域的尾部缓存页，刷入磁盘

flush链表

双向链表

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。

需要注意的是，脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响

count基础节点

引用链表的头节点、尾节点

undo log日志

回滚事务日志

刷盘机制

后台线程定时将lru冷数据区尾部的一些缓存页刷入磁盘

刷盘会释放flush链表，重新加入free链表，从lru链表中移除

redo log_buffer(重做日志缓冲)

该值可由配置参数innodb_log_buffer_size控制，默认为8MB

组件

redo log block

512字节

多条redo log数据

redo log

用户顺序追加写入redo log日志

缓冲

InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件

刷盘时机

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

每个事务提交的时候，必须把他那些redo log所在的redo log block都刷入磁盘里去

Master Thread每一秒将重做日志缓冲刷新到重做日志文件

重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可

后台线程定时刷新，有一个后台线程每隔1秒就把redo log buffer 里的redo log block刷到磁盘里

Mysql关闭的时候，redo log block都会刷入到磁盘里

checkpoint机制

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页

时机

Master Thread中发生的Checkpoint，差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞

倘若没有足够可用的空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint

Async/Sync Flush Checkpoint指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的

innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint，刷新一部分的脏页到磁盘

解决问题

缩短数据库的恢复时间

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。

缓冲池不够用时，将脏页刷新到磁盘

此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘

重做日志不可用时，刷新脏页

重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，这从成本及管理上都是比较困难的

innodb_additional_mem_pool_size额外内存池)

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap）的方式进行的

在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请

例如，分配了缓冲池（innodb_buffer_pool），但是每个缓冲池中的帧缓冲（framebuffer）还有对应的缓冲控制对象（buffer control block），这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。

4.生产经验

机器配置

16核32GB内存

SDS固态硬盘

压测工具

sysbench

观察网卡流量

dstat -n

观察磁盘IO

dstat -d

dstat -r

性能提升

Buffer Pool

配置多个Buffer Pool提升并发能力

innodb_buffer_pool_size = 8GB(配置的时候用字节值)

innodb_buffer_pool_instances = 4

动态扩容Buffer Pool

增加多个chunk

可以配置机器总内存的60%作为Buffer Pool的大小

show engine innodb status 查看状态