首页  流程图  详情

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

2021-02-09 16:17:20   12  举报





在原有模板中增加了mvcc的流程

mysql

innodb

mvcc

dubbo write

作者其他创作

大纲/内容

执行引擎

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