Mysql innodb、redolog、事务、双写、mvcc，mysql执行流程

执行引擎

ibdata文件

binlog

       .....

1.读取数据

binlog：二进制日志文件，不管使用的是哪一种存储引擎，都会产生binlog。binlog都是基于SQL语句级别的。应用这种格式进行数据恢复时如果SQL带有rand或者uui等等函数可能导致与原数据不一致。binlog format有statement和row两个可选参数，row是记录的行的更改情况，可以避免数据不一致的问题。

lock info锁信息

Buffer POOL

判断索引是否在buffer Pool

可见性判断创建快照这一刻，已经提交的事务才能看到不然根据Read View里面3个属性判断是否可读等

物理磁盘

Buffer  Pool

数据⼀致性： 是⼀个综合性的规定， 或者说是⼀个把握全局的规定。 因为它是由原⼦性、 持久性、 隔离性共同保证的结果， ⽽不是单单依赖于某⼀种技术

flush 链表（刷脏叶）

生成执行计划、索引选择

1.read uncommit

读数据

错误日志

3.repeated read

1.mysql 架构

对查询语法进行分析

持久性

system tablespace

非⼦页

mysql service层

表级锁

只解决了当前读幻读问题

.ibd文件 

DB_ROLL_PTR回滚ID

MYSQL锁的实现

更新非唯一二级索引时，先将数据写入 buffer

可重复读(默认隔离级别)RR(repeat read)隔离可能幻读

*.IDB

操作引擎，返回结果

3.更新内存数据

index page索引页

DB_Row_Id

undolog：回滚日志文件。当事务进行回滚时，需要依赖undo日志进行回滚操作。undo log存放在ibdata1共享表空间中，可以通过innodb_undo_directory参数指定单独存undo表空间的目录。在并发写入型负载中，我们可以把undo文件部署到单独的高速存储设备上

事务特性

表行数据增加2字段

redo  logbuffer

1.约束一致性2.数据一致性

double write段

memery

默认不开启，8版本以后删除了，建议缓存用本地缓存或者redis等。

共享锁(S)读锁

5.数据写到 data write buffer此步骤为顺序写

整个流程：①通过客户端/服务器通信协议与 MySQL 建⽴连接。②查询缓存， 这是 MySQL 的⼀个可优化查询的地⽅， 如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句， 则将查询结果直接返回给客户端； 如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进⾏语法语义解析， 并⽣成解析树。③预处理器⽣成新的解析树。④查询优化器⽣成执⾏计划。⑤查询执⾏引擎执⾏ SQL 语句， 此时查询执⾏引擎会根据 SQL 语句中表的存储引擎类型， 以及对应的API 接⼜与底层存储引擎缓存或者物理⽂件的交互情况， 得到查询结果， 由MySQL Server 过滤后将查询结果缓存并返回给客户端。 若开启了 Query Cache， 这时也会将SQL 语句和结果完整地保存到 Query Cache 中， 以后若有相同的 SQL 语句执⾏则直接返回结果。

DML

3.InnoDB存储架构

I（隔离性）

insert buffer 的 bitmap 页、

1.管理连接，权限验证等mysql5.6之前采用BIO，1.规范，2.数据库连接数量有限切单socket连接读写频繁。优化：连接池数量不是越多越好，设置为(2*CPU数+磁盘个数即可)mysql 5.7以后变成了Pool-Threads 模式，使用EPOLL，实现高并发连接

3. log日志

Service & utilities管理服务&⼯具集， 包括备份恢复、 安全管理、 集群管理服务和⼯具

Mysql执行器

data page clean

Buffer pool

D（持久性）

4.MYSQL数据写入过程

data page数据页

undolog日志文件

索引文件

锁类型

insert Buffer page

2.读已提交在同一事务中，读取一条数据，会出现不一样的结果，出现不可重复读在mysql中使用MVCC实现，此隔离级别，每次都会读最新的可用的read view，导致不同时刻数据不一致

所有需要全部加载到内存

锁粒度

间隙锁

客户端

解决可重复度模式下的幻读问题锁的是两次当前读之前的GAP

innodb三大特性之一：dubbo write

分隔

是:不可见

读已提交Read Committed不能重复度

MVCC：即多版本并发控制。当innoDB存储引擎通过MVCC多版本控制的方式来读取当前执行时间数据库中的行数据，如果读取的行正在执行delete或者update操作，这时读取操作不会因此等待行上锁的释放；相反，innoDB会去读取行的一个快照数据（undo log），从历史快照（undo log链）中获取旧版本数据来保证数据一致性。由于历史版本数据存放在undo log页当中，对数据修改所加的锁对于undo页没有影响，因此不会影响用户对历史数据的读，从而达到非一致性锁定读，提高并发性能。

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

SQL interface接收客户端发送的各种 SQL 语句， ⽐如 DML、 DDL 和存储过程等

原子性

每次事务提交都会写入OS buffer，并调用fsync()刷盘

optimizer

回滚对应undo位置

QUERY CACHE

物理文件系统

不可重复读(二次读取结果不同)

Redo buffer ⾥存储数据修改所产⽣的 Redo log

MVCC

InnoDB 的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。而计算机硬件和操作系统，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。MySQL 可以根据redolog 进行恢复，而mysql在恢复的过程中是检查page的checksum，checksum就是pgae的最后事务号，发生partial page write 问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

update ... (更新操作)delete ... (删除操作)insert ... (插入操作)select ... lock in share mode (共享读锁)select ... for update (写锁)

redo/undo log

Read View机制活跃事务列表列表中最小事务ID列表最大事务ID你的事务ID:

1.要么修改，要么没改

hash index自适应hash索引

并发问题

隔离级别

C（一致性）

executor

串行化(一般不用)性能太差

DB_TRX_Id当前事务ID

一致性

Buffer Pool

读取未提交Read uncommitted存在脏读

2.写undolog日志便于事务回滚

Record_TRX_ID在活跃列表

sync_binlog

对于非聚集索引的插入或者更新操作：1.先判断插入的非聚集索引页是否在缓冲池中，2.如果在，则直接插入，3.如果不在，则先放入一个insert buffer中，然后再以一定的频率进行Insert buffer和辅助索引叶子节点合并操作。目的：这种时候，经常能将多条记录的插入合并到一个操作中，这样就大大提高了非聚集索引离散插入的性能。条件：非聚簇索引，非唯一索引

可被多个事务获取，但阻止事务对行数据修改

内存

redo log

数据落盘

1.redolog 刷盘机制可以进行设置，通过innodb_flush_log_at_trx_commit参数进行设置，默认是1

5.准备提交事务，redoLog日志刷盘

当前事务ID

4.可串⾏化单版本的状态， 因为它所有的实现都是通过锁来实现的。

memory

⽤户表空间:是指以 .ibd 为后缀的⽂件， ⽂件中包含 insert buffer 的 bitmap 页、 叶⼦页（这⾥存储真正的⽤户数据） 、 ⾮叶⼦页。 InnoDB 表是索引组织表， 采⽤ B+ 树组织存储， 数据都存储在叶⼦节点中， 分⽀节点（即⾮叶⼦页） 存储索引分⽀查找的数据值。

A（原子性）

2.变更数据

cache全局缓存innodb Buffer Pool

Archive

当设置为1的时候：事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。当设置为0的时候：事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，最多会丢失1秒钟的数据。当设置为2的时候：每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。设置为0和2可以大幅度提升插入性能，但是在故障的时候可能会丢失1秒钟数据，这1秒钟很可能有大量的数据，所以默认值和一般企业这里的值都设置的是1.

幻读指的就是你一个事务用一样的SQL多次查询，结果每次查询都会发现查到了一些之前没看到过的数据

7.数据恢复时

数据文件

dubbo write

各种存储引擎

是不可见

myisam

double write buffer 是 double write 所需的 buffer， 主要解决由于宕机引起的物理写⼊操作中断， 数据页不完整的问题。

只能被一个事务获得，允许获得锁的事务修改数据

mysql 事务与锁

undo

共享表空间

Innodb_flush_log_at_trx_commit

Parser对 SQL 语句进⾏语法解析⽣成解析树

当前读

innodb

MVCC读取流程

client  connectors（JDBC/.NET等）负责处理客户端的连接请求， 与客户端创建连接

MySQL 是通过 WAL（Write Ahead Log） 技术来实现原子性

7.写入binglog文件与位置

每秒写入os buffer并调用fsync()刷盘

磁盘

read_thread线程

锁的类型(都是作用在索引上)聚簇索引&二级索引

慢查询日志

redo log buffer

每秒调用fsync()刷盘

在 MySQL 事务中， 锁的实现与隔离级别有关系， 在 RR（Repeatable Read） 隔离级别下， MySQL 为了解决幻读的问题， 以牺牲并⾏度为代价， 通过 Gap 锁来防⽌数据的写⼊， ⽽这种锁， 因为其并⾏度不够， 冲突很多， 经常会引起死锁。 现在流⾏的 Row 模式可以避免很多冲突甚⾄死锁问题， 所以推荐默认使⽤ Row +RC（Read Committed） 模式的隔离级别， 可以很⼤程度上提⾼数据库的读写并⾏度。

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统控制它的缓存的刷新。性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

data dictionary（页与数据的元数据）

innodb存储引擎

Optimizer1.⽣成执⾏计划， 并选择合适的索引， 2.按照执⾏计划执⾏SQL 语⾔并与各个存储引擎交互。

否

Parser

隔离性

insert buffer 段

      为什么不能是唯一索引？    之所以不支持唯一索引，是因为如果辅助索引是唯一索引，那么在插入时需要校验唯一性，校验唯一性的时候就会发生离散读取，从而又增加了开销，那么insert buffer得不偿失。

可见

行锁

写数据

Record_TRX_ID>最大ID

Os Buffer

index 段

1.加载数据缓存到

1.redolog + binlog

4.写redoLog日志

CONNECTOR

5.redo_log_thread负责把⽇志缓冲中的内容刷新到 Redo log ⽂件中

RedoUndo

1.事务

设置为 2

2.mysql执行顺序

1.读未提交会发生脏读

幻读(读取到别的事务未提交的新数据)

事务的四大特性：ACID1.原子性2.持久性3.隔离性4.一致性

binlog日志文件

write_thread线程4个线程

3.page_cleaner_thread：负责将 buffer pool 中的脏页刷新到磁盘

redo log 1

4.serlize

加入MVCC后为啥还不能重复读关键点在于每次查询都生成新的ReadView所导致的

执行器

1.由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了 adaptive hash index功能，innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引。

redo log日志文件

2. InnoDB 架构

4. 写日志

redo log 3

1.master线程：主循环（loop） 、 后台循环（background loop） 、 刷新循环（flush loop） 、 暂停循环（suspend loop） 。 Master Thread 会根据其内部运⾏的相关状态在各循环间进⾏切换

全日志等.

Connection Poll连接管理、用户身份认证与鉴权

main线程

快照读

CAP+RRRow+RC

redo_log_thread线程

data page dirty

脏读(读取未提交的数据)

8. 脏页数据刷盘

LRU 链表（最少活跃淘汰）

删除基于最小活跃事务ID

数据完整性

排他锁(X)写锁

WAL

rollback 段

回滚指针找到上个版本

IO线程

InnoDB select count(*) from table 为什么慢

叶⼦页

使用了MVCC无法计数数量

main线程刷脏页到磁盘

redo Log buffer

1.磁盘文件

commit

2.read commit

基于Undolog

3.可重复读在同一事物中，读取一条数据，不会出现数据不一致，解决了不可重复读。在mysql中使用MVCC实现，每次读的read view 都是第一次读取的版本，读取的数据就会一致。但是会出现幻读的情况，幻读是查询多条数据的时候，另一个事务新增数据后，会出现新增的数据。

6.将double write buffer写入各表空间文件，这时是离散写入

4. write_thread 负责将数据页从缓冲区写⼊磁盘， 也可以通过参数设置线程数量， page_cleaner线程发起刷脏页操作后 write_thread 就开始⼯作了。

Record_TRX_ID<最小ID

....

redolog：重做日志文件。用于记录InnoDB引擎下事务的日志，他记录每页更改的物理情况。将对页面的修改操作写入到这个专门的文件中，并在数据库启动时从此文件进行恢复操作。

insert_buffer_thread线程

数据字典段

在数据写入并落盘过程中产生了几个问题：1.insert Buffer page 加快写入速度。2.dubbo write 确保数据落盘准确。3.redolog落盘方式可选，redolog落盘方式与binglog落盘方式搭配，保证数据不丢失设置。

设置为 0

是

page_cleaner_thread线程

insert Buffer cache

1.唯一索引2.外键约束

user tablespace

设置为 1

2.read_thread 处理⽤户的读请求， 并负责将数据页从磁盘上读取出来， 可以通过参数设置线程数量。

undo段

每次提交都仅写入到os buffer

脏数据

redo log 2

锁

innodb三大特性之一：insert Buffer cache