[Mysql数据库 数据存储和查询流程] Mysql的数据存储和查询流程.......

本文也是学习别人的笔记,加自己整理

创建表

CREATE TABLE student ( id int(11) NOT NULL AUTO_INCREMENT
COMMENT ‘学号’, name varchar(10) NOT NULL COMMENT ‘学生姓名’, age
int(11) NOT NULL COMMENT ‘学生年龄’, PRIMARY KEY (id), KEY
idx_name (name) ) ENGINE=InnoDB;

---

插入SQL语句
insert into student (name, age) value(‘a’, 10); insert into
student (name, age) value(‘c’, 12); insert into student (name,
age) value(‘b’, 9); insert into student (name, age) value(‘d’,
15); insert into student (name, age) value(‘h’, 17); insert into
student (name, age) value(‘l’, 13); insert into student (name,
age) value(‘k’, 12); insert into student (name, age) value(‘x’,
9);

数据格式

数据分析

数据最终会持久化到文件中，那么这些数据在文件中是如何组织的？

「数据其实是存到页中的，一页的大小为16k，一个表由很多页组成，这些页组成了B+树」

组织如下所示:

页5代表的是 创建的新的一个 页目录 这里a1 分别代表的是挂着的 其他页的 主键和页号

SQL执行流程

详细结构如下:

提问?

「当我们想更新某条数据的时候，难道是从磁盘中加载出来这条数据，更新后再持久化到磁盘中吗？」

• 如果这样搞的话，那一条sql的执行过程可太慢了，因为对一个大磁盘文件的读写操作是要耗费几百万毫秒的

  真实的执行过程是，当我们想更新或者读取某条数据的时候，会把对应的页加载到内存中的Buffer
  Pool缓冲池中（默认为128m，当然为了提高系统的并发度，你可以把这个值设大一点）
  

• 之所以加载页到Buffer Pool中，是考虑到当你使用这个页的数据时，这个页的其他数据使用到的概率页很大，随机IO的耗时很长，所以多加载一点数据到Buffer Pool
  

• 当更新数据的时候，如果对应的页在Buffer Pool中，则直接更新Buffer Pool中的页即可，对应的页不在Buffer Pool中时，才会从磁盘加载对应的页到Buffer Pool，然后再更新，「此时Buffer Pool中的页和磁盘中的页数据是不一致的，被称为脏页」。这些脏页是要被刷回到磁盘中的

当发生脏页如何处理

「这些脏页是多会刷回到磁盘中的？」 有如下几个时机?

• Buffer Pool不够用了，要给新加载的页腾位置了，所以会利用改进的后的LRU算法，将一些脏页刷回磁盘
• 后台线程会在MySQL不繁忙的时候，将脏页刷到磁盘中 redolog写满时（redolog的作用后面会提到)
• 数据库关闭时会将所有脏页刷回到磁盘

脏页如何实现刷新?

• 最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能。所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。
• 但是如果不立即同步到磁盘的话，那之后再同步的时候我们怎么知道Buffer
  Pool中哪些页是脏页，哪些页从来没被修改过呢？我们需要创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫Flush链表，链表的构造和Free链表差不多，这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入Flush链表，如果这个页被再次修改就不会再放到Flush链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，而Flush链表中的脏页记录只是通过指针指向LRU链表中的脏页。

当需要更新的数据所在的页已经在Buffer Pool中时，只需要操作内存即可，效率不是一般的高

提问?

「看到这小伙伴们可能会有一个疑问？如果对应的脏页还没有被刷到磁盘中，数据库就宕机了，那我们的更改不就丢失了？」

这里就要提高数据库的三大日志: undolog，rodolog，binlog 针对上面的提问,主要提供解决方法的日志是 rodolog

Undolog：如何让更新的数据可以回滚？

• 以上面的student表为例，当我们想把id=1的name从a变为abc时，会把原来的值id=1，name=a写入到undolog中。当这条更新语句在事务中执行，当事务回滚时，就可以通过undolog将数据恢复为原来的模样。
  

Rodolog：系统宕机了，如何避免数据丢失？

接着我们上面的问题，如果对应的脏页还没有被刷到磁盘中，数据库就宕机了，那我们的更改不久丢失了？

• 为了解决这个问题，我们需要把内存所做的修改写入到 redo log buffer中，这是内存里的一个缓冲区，用来存在redo日志。
• rodo log记录了你对数据所做的修改，如“将id=1这条数据的name从a变为abc”，物理日志哈，后面会再提一下。「redolog是顺序写所以比随机写效率高」

「InnoDB的redo log是固定大小的」，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总大小为4GB。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

• write pos是当前要写的位置，checkpoint是要擦除的位置，擦除前要把对应的脏页刷回到磁盘中。writepos和checkpoint中间的位置是可以写的位置。
• 当我们的系统能支持的并发比较低时，可以看看对应的redo log是不是设置的太小了。太小的话会导致频繁的刷脏页，影响并发，可以通过工具监控redo log的大小

redolog的大小=innodb_log_file_size*innodb_log_files_in_group（默认为2）

「接下来我们详细聊聊，redolog是如何避免数据丢失的」

1. 事务未提交，MySQL宕机，这种情况Buffer Pool中的数据丢失，并且redo log buffer中的日志也会丢失，不会影响数据

   提交事务成功，redo log buffer中的数据没有刷到磁盘，此时会导致事务提交的数据丢失。

「鉴于这种情况，我们可以通过设置innodb_flush_log_at_trx_commit来决定redo log的刷盘策略

查看innodb_flush_log_at_trx_commit的配置

SHOW GLOBAL VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’

innodb_flush_log_at_trx_commit 值 作用:

• 0 提交事务时，不会将redo log buffer中的数据写入os buffer，而是每秒写入os buffer并刷到磁盘
• 1 提交事务时，必须把redo log从内存刷入到磁盘文件中
• 2 提交事务时，将rodo log写入os buffer中，默认每隔1s将os buffer中的数据刷入磁盘

值为0和2都可能会造成事务更新丢失

Binlog：主从库之间如何同步数据？

当我们把mysql主库的数据同步到从库，或者其他数据源时，如es，bi库时，只需要订阅主库的binlog即可。

MySQL刚开始用binlog实现归档的功能，但是binlog没有crash-safe的能力，所以后来InnoDB引擎加了redo log来实现crash-safe。假如MySQL中只有一个InnoDB引擎，说不定就能用redo log来实现归档了，此时就可以将redo log和 binlog合并到一块了

这两种日志的区别如下：

• redo log是InnoDB存储引擎特有，binglog是MySQL的server层实现的，所有引擎都可以使用
• redo log是物理日志，记录的是数据页上的修改。binlog是逻辑日志，记录的是语句的原始逻辑，如给 id=2的这一行的c字段加1
• redo log是固定空间，循环写。binlog是追加写，当binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

「我们可以通过设置sync_binlog来决定binlog的刷盘策略」

sync_binlog值 作用:

• 0 不立即刷盘，将binlog写入os buffer，由操作系统决定何时刷盘 ，有可能会丢失多个事务的数据
• 1 将binlog写入os buffer，每n个事务提交后，将os buffer的数据刷盘

一般情况下将sync_binlog的值设为1即可

---

二阶段提交:

接着我们来看一下将id=2的行c字段加1的执行流程。

• 引擎将新数据更新到内存中，将操作记录到redo log中，此时redo log处于prepare状态，然后告知执行器执行完成了，可以提交事务
• 执行器生成操作的binlog，并把binlog写入磁盘
• 引擎将写入的redo log改为提交状态，更新完成

「为什么要把relog的写入拆成2个步骤？即prepare和commit，两阶段提交」

1. 因为不管你先写redolog还是binlog，奔溃发生后，最终其实都有可能会造成原库和用日志恢复出来的库不一致

「而两阶段提交可以避免这个问题」:

1. redolog和binlog具有关联行，在恢复数据时，redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。每个阶段的log操作都是记录在磁盘的，在恢复数据时，redolog
   状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。

入坑经验:

「1. 数据库支持的并发度不高」

在一些并发要求高的系统中，可以调高Buffer Pool和redo log，这样可以避免频繁的刷脏页，提高并发

「2. 事务提交很慢」

原来我负责的一个系统跑的挺正常的，直到上游系统每天2点疯狂调我接口，然后我这边都是事务方法，事务提交很慢。监控到Buffer Pool和redo log的设置都很合理，并没有太小，所以问题出在哪了？我也不知道

「后来dba排查到原因，把复制方式从半同步复制改为异步复制解决了这个问题」

「异步复制」：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间

「全同步复制」：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响

「3. 在一个方法中，我先插入了一条数据，然后过一会再查一遍，结果插入成功，却没有查出来」

这个比较容易排查，如果系统中采用了数据库的读写分离时，写插入的是主库，读的却是从库，binlog同步比较慢时，就会出现这种情况，此时只需要让这个方法强制走主库即可

后台线程

Master Thread
这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲等。

IO Thread
在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调处理。

Purge Thread

• 事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。

完成整体功能介绍以后，我们开始聊聊数据如何插入到InnoDB引擎上的:

假设场景如下:

首先我们创建一张表T，主键为Id，辅助索引为a

create table T(id int primary key, a int not null, name
varchar(16),index (a))engine=InnoDB;

接下来插入一条数据，
insert into t(id,a,name) values(id1,a1,‘哈哈’),(id2,a2,‘哈哈哈’);
我们介绍过MySQL读取数据的流程，Server层我们还是会经过连接器、解析器、优化器、执行器这些东西，这些我们就不介绍了，我们主要介绍剩下的操作：

插入数据时候可能有两种场景:

第一种场景:假设Id1这条数据在内存池中,

直接更新Buffer Pool中的Index Page和Data Page;
写入redo log中，处于预提交状态；
写入binlog中，
提交事务，处于commit状态，两阶段提交；
后台线程写入到数据文件的索引段和数据段中;

第二种场景假设id2这条数据不再内存池中,

数据写入到内存池中，非聚集索引写入到Insert Buffer，其他数据写入Data Page中;
后续的动作保持和上面剩下的步骤一样。

---

第二个版本的Sql 执行流程

Server层
连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接；
查询缓存:服务的查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集；
解析器:解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确;
优化器:解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划;
执行器:执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据;

InnoDB
后台线程:负责刷新内存池中的数据，保证缓存池中的内存缓存是最近的数据，将已修改的数据刷新到磁盘文件，同时保证数据库发生异常的情况能恢复到正常情况；
内存池:内存池也可以叫做缓存池，主要为弥补磁盘的速度较慢对数据库产生的影响，查询的时候，首先将磁盘读到的页的数据放在内存池中，下次读取的时候直接从内存池中读取数据，修改数据的时候，首先修改内存池中的数据，然后后台线程按照一定的频率刷新到磁盘上。
文件:主要是指表空间文件，而外还有一些日志文件；
以上大致的介绍一下MySQL的整体架构，其中内存池、文件、后台线程等一些跟细节的东西没有介绍，后面我们介绍,下图 是5.6详细流程

存储文件也就是表数据的存储，整体的存储结构如下图:

表空间主要分为两类文件，一类是共享表空间，一类是每张表单独的表空间，单独的表空间存放的是表中的数据、索引等信息，共享的表空间主要是存储事务信息、回滚信息等数据；表空间由段(Segment)、区(Extend)、页(Page)、行(Row)组成，接下来简单介绍一下这4种结构:
1:段(Segment)
常见的Segment有数据段、索引段、回滚段等， 数据段为B+树的叶子节点(Leaf node segment)、索引段为B+树的非叶子节点(Non-leaf node segment)。如下图：

每创建索引就会创建一个索引段，索引段的叶子节点指向数据段，通过这样的组合来完成我们查询数据时候需要，因此创建索引越多，会导致需要构建的索引段就越多，导致插入数据时间就会增加。
2:区(Extend)
区是构成段的基本元素，一个段由若干个区构成，一个区是物理上连续分配的一段空间，每一个段至少会有一个区，在创建一个段时会创建一个默认的区。如果存储数据时，一个区已经不足以放下更多的数据，此时需要从这个段中分配一个新的区来存放新的数据。一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是区。每个区大小固定为1MB，区由页组成，为保证区中Page的连续性通常InnoDB会一次从磁盘中申请4-5个区。在默认Page的大小为16KB的情况下，一个区则由64个连续的Page组成。
3:页(Page):
页是构成区的基本单位，是InnoDB磁盘管理的最小单位。在逻辑上（页面号都是从小到大连续的）及物理上都是连续的。在向表中插入数据时，如果一个页面已经被写完，系统会从当前区中分配一个新的空闲页面处理使用，如果当前区中的64个页面都被分配完，系统会从当前页面所在段中分配一个新的区，然后再从这个区中分配一个新的页面来使用。
4:行(Row):
InnoDB按照行进行存放数据，每个页存放的数据有硬性规定，最多存放16KB，当数据大于16KB的时候会发生行溢出，会存储到而外的页(Uncompressed BLOB Page)当中。

学习地址