#索引#日志#MVCC

0004

地址5

buffer  pool

普通索引树结构

页

111

控制块2

1

P

user space

页头

data

update set name = 'venki.chen' where id = 10;

undo_001

0010

flush链表

记录到binlog内存中

磁盘 log files

脏页

792

如果sync_binlog = 0或者1，那么就要有一个从os cache写入磁盘binlog file的机制。可以通过binlog_max_flush_queue_time决定，默认值为0表示不等待直接进入sync，设置该变量为一个大于0的值的好处是group中的事务多了，性能会好一些，但是这样会导致事务的响应时间变慢，所以建议不要修改该变量的值，除非事务量非常多并且不断的在写入和更新。这里涉及到单个commit 和 group commit 

（font color=\"#ff0000\

data7

322

控制块5

0009

next指针

磁盘

commit                                               每次提交时，写入OS buffer，并调用fsync()刷到磁盘

1. undo log是什么？- 是逻辑日志；- 主要用于实现 MVCC，从而实现 MySQL 的 ”读已提交“、”可重复读“ 隔离级别。在每个行记录后面有两个隐藏列，\"trx_id\"、\"roll_pointer\"，分别表示上一次修改的事务id，以及 \"上一次修改之前保存在 undo log中的记录位置 \"。在对一行记录进行修改或删除操作前，会先将该记录拷贝一份到 undo log 中，然后再进行修改，并将修改事务 id，拷贝的记录在 undo log 中的位置写入 \"trx_id\"、\"roll_pointer\"。2. undo log有什么作用？- 提供回滚；- 多个行版本控制(MVCC)。3. undo log什么时候生成？- MySQL启动后。- DML操作时变化产生undo log。4. undo log需要注意什么？- 当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。- 当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

buffer pool

MVCC1. MVCC 最核心的就是 版本链 和通过版本链生成的 Read View。2. 版本链：通过 \"roll_pointer\" 字段指向的上一次修改的值，使每行记录变化前后形成了一条版本链。3. Read View：Read View 表示可见视图，用于限制当前事务查询数据的，通过与版本链的配合可以实现对数据的 “快照读” 。Read View 内部主要有四个部分组成，第一个是创建当前 Read View 的事务 id creator_trx_id，第二个是创建 Read View 时还未提交的事务 id 集合trx_ids，第三个是未提交事务 id 集合中的最大值up_limit_id，第四个是未提交事务 id 集合中的最小值low_limit_id。当执行查询操作时会先找磁盘上的数据，然后根据 Read View 里的各个值进行判断，    - 如果该数据的 trx_id 等于 creator_trx_id，那么就说明这条数据是创建 Read View的事务修改的，那么就直接返回；        - 如果 trx_id 大于等于 up_limit_id，说明是新事务修改的，那么会根据 roll_pointer 找到上一个版本的数据重新比较；    - 如果 trx_id 小于 low_limit_id，那么说明是之前的事务修改的数据，那么就直接返回；    - 如果 trx_id 是在 low_limit_id 与 up_limit_id 中间，那么需要去 trx_ids 中对各个元素逐个判断，如果存在相同值的元素，就根据 roll_pointer 找到上一个版本的数据，然后再重复判断；如果不存在就说明该数据是创建当前 Read View 时就已经修改好的了，可以返回。读已提交和可重复读之所以不同就是它们 Read View 生成机制不同，读已提交是每次 select 都会重新生成一次，而可重复读是一次事务只会创建一次且在第一次查询时创建 Read View。事务启动命令begin/start transaction不会创建Read View，但是通过 start transaction with consistent snapshot 开启事务就会在开始时就创建一次 Read View。4. mvcc只在RC和RR两个隔离级别下工作。其他两个隔离级别和mvcc不兼容，因为read uncommited总是读取最新的数据行，而不是符合当前事务版本的数据行。而serializable则会对所有读取的行加锁。5. trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id.6. rool_pointer:每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo log中。这个roll_pointer就是存储了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。（注意插入操作的undo log中没有这个属性，因为它没有老版本）。

页目录

启动事务的方式是通过 start transaction with consistent 。首先创建事务1，假设此时事务1 id 是60，事务1先修改 name 为poet.chao，那么就会在修改前将之前的记录写入 undo log，同时在修改时将生成的undo log 行数据地址写入 roll_pointer，然后暂不提交事务1。开一个事务2，事务 id 为 65，进行查询操作，此时生成的 Read View 的trx_ids是[60]，creator_trx_id 为 65，对应的数据状态就是下图，首先先得到磁盘数据的 trx_id ，为60，然后判断，不等于 creator_trx_id，然后检查，最大值和最小值都是 60，也就是属于上面 2）的情况，所以通过 roll_pointer 从 undo log 中找到 “venki.chen” 那条数据，再次判断，发现 50 是小于 60的，满足上面 3）的情况，所以返回数据。

name

311

900

用户数据区域

id

磁盘4地址：xb0002156444

将这行数据的name字段更改为'venki.chen'

undo log存储的数据

空置页

地址7

ib_16384_0.dblwr

7

......

age

地址6

磁盘8地址：xb0003156444

累计N个事务后，fsync（）刷到磁盘

2

log buffer 写入到os buffer1. 当 innodb_flush_log_at_trx_commit 设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。2. 当 innodb_flush_log_at_trx_commit 设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。

否

内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。不仅仅是日志需要刷盘，脏数据页也一样需要刷盘。在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。innodb存储引擎中checkpoint分为两种：1. sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。2. fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点。    2.1 master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。    2.2 flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。    2.3 async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘    2.4 dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。

4

613

WAL

undo log buffer

控制块3

DML

13

roll_pointer

基节点：存储控制块信息

先取出t1表的第一个数据页

0002

data4

sync_binlog =1

5

1. WAL写入。2. DML变化写入buffer pool和写入log buffer 可以理解为几乎是同时的。

地址2

data8

将redo log写入redo log buffer

控制块4

0012

innodb存储引擎为什么强烈建议设置主键，并且是整型自增？

每次提交事务都 write至操作系统缓存

29

1. 此链表为free链表，专门用于存储buffer pool中空置页信息；2. 由基结点和控制块结点组成；3. 基结点用于存储结点信息（首尾结点指针地址等）；4. 控制块用于管理空白页。5. 从磁盘中取到数据后，通过free链表，找到空白页，然后将数据复制过去，之后移除free链表此控制块；

0001

data6

prev指针

5/8热数据区域

0006

redo log

1. WAL，全称是Write-Ahead Logging， 预写日志系统。指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。2. 二进制日志（binlog）是MySQL的上层日志，先于存储引擎的事务日志（redo log、undo log）被写入。

控制块1

3/8冷数据区域

8

将数据页从磁盘中读取到内存

commit

776

1. select除外

612

venki.chen

1. 走索引+7次回表；1. 走索引+1次回表

磁盘5地址：xb0002156446

455

3

事务id-50

取下一页

222

0008

OS buffer

上一个版本地址

数据第4页

doublewrite buffer files

磁盘binlog files

binlog

0007

Os cache

事务id-60

sleep.shi

11

free链表

644

将binlog写入磁盘

每秒调用fsync()刷到磁盘

数据第5页

1. buffer pool中的页是从磁盘中的页复制而来的；2. 每页数据占据空间为16kb，buffer pool默认空间大小为128M，大概可以存放8192页；

磁盘3地址：xb0002156442

innodb_flush_log_at_trx_commit=1

commit                                        每次提交时写入OS buffer

redo log buffer

内存

磁盘1地址：xb0002156441

902

ib_logfile1

控制块6

793

磁盘9地址：xb0002136444

数据第2页

235

786

主键索引树结构

结束

内存Os buffer

怎么理解binlog、undo log以及redo log日志？

DML/DDL操作

ib_16384_1.dblwr

os buffer

数据落盘

磁盘6地址：xb0002156434

lru链表

binlog中内容的形式，通过binlog_format决定。1. Row-记录操作语句对具体行的操作以及操作前的整行信息。缺点是占空间大。优点是能保证数据安全，不会发生遗漏。2. Statement-记录修改的 sql。缺点是在 mysql 集群时可能会导致操作不一致从而使得数据不一致（比如在操作中加入了Now()函数，主从数据库操作的时间不同结果也不同）。优点是占空间小，执行快。3. Mixed-会针对于操作的 sql 选择使用Row 还是 Statement。相比于 row 更省空间，但还是可能发生主从不一致的情况。

事务id-100

0011

896

sync_binlog=0

innodb_flush_log_at_trx_commit=0

data2

888

os buffer 写入到磁盘redo log当innodb_flush_log_at_trx_commit 设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。 

log buffer

内存buffer pool

trx_id

重中之重：read view的生成只有在select的时候！！！

log_bin=on

6

内存binlog cache

ib_logfile0

开始

地址4

9

将改过之后的数据行写入内存

开启binlog日志

数据第1页

数据在内存的存在形式

system tablespace(ibdata1)

1. 损失部分插入性能换取查询性能；2. show variables like '%innodb_page_size%';3. 6字节的row_id作为主键；一个int占据4个字节；4. 

undo log

地址3

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

on-disk structures

磁盘2地址：xb0002156445

数据第6页

数据池

3. sync_binlog = 1，每次提交事务，直接将binlog，调用fync()刷到磁盘中.

要取出t1表的id=10这一行数据

poet.chao

131

sync_binlog = N

1. 当执行数据更新等操作，引起buffer pool中的页数据变化后，此时的数据页可以理解为脏页（数据并不是立马写入磁盘的，性能不够好），而记录这些脏页的信息就是flush链表的意义；2. 可以理解为：MySQL后台线程会定时的寻找脏页，然后添加到链表；3. 脏页最后是需要持久化到磁盘的；4. 脏页写入磁盘的机制和redo log的设置有关。

15

数据页是否存在内存中

innodb_flush_log_at_trx_commit=2

内存log buffer

data1

0003

891

data3

232

0005

数据在磁盘的存在形式

然后提交事务2，再开一个事务3，将name改成sleep.shi，假设此时的事务3 id 是100，那么在修改前又会将 trx_id 为 60 拷贝进 undo log，同时修改时将 trx_id 改为100，然后事务3暂不提交，此时事务1再进行select。如果隔离级别是读已提交，那么就会重新生成 Read View，trx_ids是[100]，creator_trx_id 为65，判断过程和上面相似，最终返回的是poet.chao那条数据；而如果是可重复读，那么还是一开始的 Read View，trx_ids 还是[60]，creator_trx_id 还是 65，那么还是从sleep.shi 的 trx_id 进行判断，发现不等于 65，且大于60，为情况 2），跳到 poet.chao ，对 trx_id判断，等于，还是情况 2），跳转到 “venki.chen” 那条数据，判断 trx_id <  low_mimit_id，为情况 3），所以返回 \"venki.chen\"。下面是这个例子最终的示意图

控制块8

DML操作

or

磁盘7地址：xb0001156444

控制块7

kernel space

1. redo log 什么时候生成？- mysql启动后，会生成redo log。分配的磁盘空间是连续的，所以写入是顺序写。2. redo log 存储的是什么？- 存储的是物理日志，记录的是数据页的物理修改。3. redo log 什么时候写入磁盘？- 当 innodb_flush_log_at_trx_commit 设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。- 当 innodb_flush_log_at_trx_commit 设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。- 当innodb_flush_log_at_trx_commit 设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。以上规则，默认情况下事务每次提交的时候都会刷事务日志到磁盘中，这是因为变量 innodb_flush_log_at_trx_commit 的值为1。但是innodb不仅仅只会在有commit动作后才会刷日志到磁盘，这只是innodb存储引擎刷日志的规则之一。刷日志到磁盘有以下几种规则：- 发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制；- 每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关；- 当log buffer中已经使用的内存超过一半时；- 当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置；4. redo log 有什么作用？- 用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。- 保证事务的持久性。5. 数据页刷盘的规则及checkpoint内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。innodb存储引擎中checkpoint分为两种：- sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。- fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：    - master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。        - flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。    - async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘。    - dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。

1.发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。2.每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。3.当log buffer中已经使用的内存超过一半时。4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。

1. binlog是什么？- binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。 当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里。2. binlog怎么用？- 需要开启log_bin = on。- 配置需要结合实际应用。- binlog里面记录什么，怎么记录取决于配置项：binlog_format（Row，Statement，Mixed）3. binlog有什么作用？- 主从复制。- 数据恢复。4. binlog流程机制是什么？- flush阶段。    - 向内存中写入每个事务的二进制日志，即当有符合记录binlog日志的操作时。- sync阶段。   - 将内存中的二进制日志刷盘。若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的刷盘操作。      - 至于什么时候刷盘和配置有关（sync_binlog）。   - leader根据顺序调用存储引擎层事务的提交，由于innodb本就支持group commit，所以解决了因为锁 prepare_commit_mutex 而导致的group commit失效问题。在flush阶段写入二进制日志到内存中，但是不是写完就进入sync阶段的，而是要等待一定的时间，多积累几个事务的binlog一起进入sync阶段，等待时间由变量 binlog_max_flush_queue_time 决定，默认值为0表示不等待直接进入sync，设置该变量为一个大于0的值的好处是group中的事务多了，性能会好一些，但是这样会导致事务的响应时间变慢，所以建议不要修改该变量的值，除非事务量非常多并且不断的在写入和更新。进入到sync阶段，会将binlog从内存中刷入到磁盘，刷入的数量和单独的二进制日志刷盘一样，由变量 sync_binlog 控制。    - 当有一组事务在进行commit阶段时，其他新事务可以进行flush阶段，它们本就不会相互阻塞，所以group commit会不断生效。当然，group commit的性能和队列中的事务数量有关，如果每次队列中只有1个事务，那么group commit和单独的commit没什么区别，当队列中事务越来越多时，即提交事务越多越快时，group commit的效果越明显。- commit阶段。    - 提交事务。其中：sync阶段和commit阶段可以同时进行，并不会相互阻塞。

data5

内存中的binlog什么时候进入到操作系统的缓存中，取决于sync_binlog的值。1. sync_binlog = 0，每次提交事务，会将binlog buffer 写入os cache。2. sync_binlog = 2，每次提交事务，会将binlog buffer写入os cache。

数据落盘后，相应删除redo log

等待一定的时间后binlog_max_flush_queue_time 可配置，调用fsync()刷到磁盘

数据第3页

数据第7页

select、desc、show等操作除外

磁盘 redo logfiles

change buffer

磁盘 binlog files

1. 当buffer pool所有数据页全部沾满之后，就应该存在一种机制进行淘汰；2. buffer pool中最近使用过的数据页，会被放置链表的头部，最少使用的放置链表尾部，所以最先淘汰的是链表尾部的控制块。3. 当buffer pool没有占满的时候，存在一些热数据经常被访问到的，但是偶尔出现一次全表扫描，那么将会清掉buffer pool中全部数据页（换血），包括热数据，那么问题就是：全盘扫描不是经常出现的，而那些热数据则是经常被访问，显然此时性能就下降了（因为还要再去磁盘获取数据到buffer pool中）。4. 为解决3带来的问题，那么lru链表被分为两部分，如图所示。数据从磁盘获取后，先将数据加到冷数据区域的头部，当冷数据区域控制块对应的数据页连续两次访问时间大于1s时，就会将冷数据区域对应的数据页挪移到热数据区域的头部。5. 为什么是大于1s，举例说明：对于全盘扫描而言，某一数据页重复访问的时间远远小于1s。因为一个数据页存在多条数据记录，逐条扫描的时间很短。

三个日志的比较（undo、redo、bin）1、undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。2、undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。3、redo log 大小有限，超过后会循环写；另外两个大小不会。4、undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。5、单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 \"三步提交\"，就可以让主从库的数据也能保证一致性。6、redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在当前存储引擎下使用，而 binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。7. redo log不是二进制日志。虽然二进制日志中也记录了innodb表的很多操作，**也能实现重做的功能，但是它们之间有很大区别。8. 二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录。具体的见后文group commit小结。9. 二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。10. 二进制日志只在每次事务提交的时候一次性写入缓存中的日志\"文件\

当前数据页中是否存在需要的数据行