Mysql 体系结构、innodb、redolog、事务、双写等

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

执行引擎

ibdata文件

innodb

binlog

       .....

1.读取数据

redo log

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

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

lock info锁信息

Buffer POOL

判断索引是否在buffer Pool

物理磁盘

7.写入binglog文件与位置

Buffer  Pool

flush 链表（刷脏叶）

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

生成执行计划、索引选择

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

1.read uncommit

磁盘

read_thread线程

读数据

错误日志

3.repeated read

慢查询日志

1.mysql 架构

redo log buffer

对查询语法进行分析

每秒调用fsync()刷盘

持久性

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

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

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。这样牺牲一定的一致性，可以获得更高的并发和性能。

system tablespace

非⼦页

DML

innodb存储引擎

mysql service层

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

解析器

隔离性

insert buffer 段

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

.ibd文件 

写数据

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

Os Buffer

index 段

*.IDB

操作引擎，返回结果

1.加载数据缓存到

3.更新内存数据

index page索引页

1.redolog + binlog

4.写redoLog日志

连接器

redo  logbuffer

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

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

RedoUndo

1.事务

double write段

memery

设置为 2

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

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

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

3.InnoDB存储架构

2.mysql执行顺序

1.读未提交会发生脏读

insert buffer 的 bitmap 页、

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

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

binlog日志文件

3. log日志

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

write_thread线程4个线程

Mysql执行器

data page clean

Buffer pool

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

redo log 1

data page数据页

4.MYSQL数据写入过程

undolog日志文件

4.serlize

索引文件

执行器

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 会根据其内部运⾏的相关状态在各循环间进⾏切换

insert Buffer page

全日志等.

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

main线程

CAP+RRRow+RC

redo_log_thread线程

data page dirty

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

8. 脏页数据刷盘

客户端

innodb三大特性之一：dubbo write

LRU 链表（最少活跃淘汰）

分隔

数据完整性

事务特性

WAL

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

rollback 段

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

原子性

IO线程

叶⼦页

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

main线程刷脏页到磁盘

优化器

redo Log buffer

查询缓存

1.磁盘文件

commit

2.read commit

物理文件系统

myisam

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

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

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

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

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中的事务号，就无法恢复。

....

redo/undo log

1.要么修改，要么没改

hash index自适应hash索引

insert_buffer_thread线程

数据字典段

共享表空间

隔离级别

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

设置为 0

page_cleaner_thread线程

一致性

Buffer Pool

insert Buffer cache

2.写undolog日志变与回滚

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

sync_binlog

user tablespace

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

内存

设置为 1

数据落盘

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

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

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

memory

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

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

2.变更数据

cache全局缓存innodb Buffer Pool

undo段

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.

每次提交都仅写入到os buffer

7.数据恢复时

数据文件

dubbo write

各种存储引擎

脏数据

redo log 2

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

锁

mysql 事务与锁

innodb三大特性之一：insert Buffer cache

undo