MySQL系列10—执行过程与缓冲池

1. MySQL查询过程

当你希望MySQL能够以更高的性能运行查询时，最好的办法是弄清楚MySQL是如何优化和执行查询。

1.1 衡量查询开销的三个指标

1. 响应时间
   1. 服务时间（处理语句真正花的时间）
   2. 排队时间（等待资源，例如IO，行锁等）
2. 扫描的行数
3. 返回的行数

下面来看一看从客户端发起查询语句到服务端返回结果之间的执行过程：

1.2 MySQL客户端与服务器端的通信特点

客户端与服务器之间是半双工通信,意味着服务器与客户端之间的传递数据不可以同时发生。

1. 客户端使用一个单独的数据包将查询传给服务器。当语句过长时，可能受到服务器端max_allowed_packet的限制。
2. 服务器响应给用户的数据通常会很多，由多个数据包组成。（客户端不断接受服务器推送的数据，客户端没有办法让服务器停下来。客户端只能被动的接受）。

1.3 查询的执行流程

客户端与服务器之间是半双工通信,意味着服务器与客户端之间的传递数据不可以同时发生。

1. 客户端使用一个单独的数据包将查询传给服务器。当语句过长时，可能受到服务器端max_allowed_packet的限制。
2. 服务器响应给用户的数据通常会很多，由多个数据包组成。（客户端不断接受服务器推送的数据，客户端没有办法让服务器停下来。客户端只能被动的接受）。

1.3.1 mysql客户端和服务器通讯（tcp/ip或socket）

• 客户端发起一条Query请求，服务器端的连接管理模块接收请求。
• 将请求转发到连接进/线程模块。
• 调用用户模块来进行授权检查。
• 通过检查后，连接进/线程模块从线程连接池中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求。

1.3.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，然后在返回查询结果前还要检查一次用户权限。（此时缓冲中存放了当前查询需要访问的表信息）

• 如果命中且权限通过，则返回结果（无需解析并生成执行计划）
• 如果命中但权限不通过，直接返回
• 如果不命中，则解析语句，生成执行计划，并执行

###1.3.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。包括：解析SQL、预处理、优化SQL执行计划。

语法解析器

首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。

预处理

预处理器则根据一些mysql规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

优化执行计划

当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

有非常多的原因会导致MySQL选择错误的执行计划，而MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短），因此我们可以指定某个执行计划。

执行过程和函数就是提前编译好，经过了语法解析器和预处理，然后存储在数据库中，当调用存储过程时就可以直接进入执行计划优化阶段了

1.3.4 查询执行引擎

在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

1.3.5 返回结果

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么mysql在这个阶段也会将结果放到查询缓存中。

mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对mysql的封包进行缓存然后批量传输。

2. Mysql缓存机制

上面提到，MySQL会将结果存放到查询缓存中，而这个缓存在Mysql中是怎么实现的呢？

为了提升数据库的操作性能，MySQL同样引入了缓存（缓冲池）机制，以避免每次查询数据都进行磁盘IO。此外，还可以通过异步落盘实现查询性能的提升。

此外我们知道，缓存必然存在淘汰机制，在MySQL中一般通过LRU算法实现数据淘汰。

2.1 缓冲池

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中读取页的操作，首先将从磁盘读取的页存放在缓冲池中，这个过程称为将页 “FIX” 在缓冲池中。下一次再读取相同的页时，首先判断该页是否在缓冲中。若在则命中，否则读取磁盘上的页。

对于数据库中的页的修改，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上，页冲缓冲池刷新回磁盘的操作并不是在每次页发送更新时触发，而是通过一种称为 Checkpoint 的机制刷新回磁盘。这是为了提高数据库的整体性能。

Buffer Pool的大小

Buffer Pool 是 InnoDB 中的一块内存区域，他一定是有自己的大小的，且大小默认是 128M，不过这个容量似乎有点小了，大家的自己的生产环境可以根据实际的内存大小进行调整，参数为：innodb_buffer_pool_size=2147483648 单位是字节，

Buffer Pool的大小

MySQL在执行增删改首先会定位到这条数据所在数据页，然后会将数据所在的数据页加载到 Buffer Pool 中。

当数据页被加载到缓冲池中后，Buffer Pool 中也有叫缓存页的概念与其一一对应，大小同样是 16KB，但是 MySQL还为每个缓存也开辟额外的一些空间，用来描述对应的缓存页的一些信息，例如：数据页所属的表空间，数据页号，这些描述数据块的大小大概是缓存页的15%左右（约800KB）。

2.2 Free List

上面是说了每个数据页会被加载到一个缓存页中，但是加载的时候 MySQL是如何知道那个缓存页有数据，那个缓存页没有数据呢？换句话说， MySQL是怎么区分哪些缓存页是空闲的状态，是可以用来存放数据页的。

为了解决这个问题， MySQL 为 Buffer Pool 设计了一个双向链表— free链表，这个 free 链表的作用就是用来保存空闲缓存页的描述块，另外 free 链表还会有一个基础节点，他会引用该链表的头结点和尾结点，还会记录节点的个数（也就是可用的空闲的缓存页的个数）。

当加载数据页到缓存池中的时候， MySQL会从 free 链表中获取一个描述数据的信息，根据描述节点的信息拿到其对应的缓存页，然后将数据页信息放到该缓存页中，同时将链表中的该描述数据的节点移除。这就是数据页被读取 Buffer Pool 中的缓存页的过程。

但 MySQL是怎么知道哪些数据页已经被缓存了，哪些没有被缓存呢。实际上数据库中还有后一个哈希表结构，他的作用是用来存储表空间号 + 数据页号作为数据页的key，缓存页对应的地址作为其value，这样数据在加载的时候就会通过哈希表中的key来确定数据页是否被缓存了。

2.3 Flush List

MySql 在执行增删改的时候会一直将数据以数据页的形式加载到 Buffer Pool 的缓存页中，增删改的操作都是在内存中执行的，然后会有一个后台的线程数将脏数据刷新到磁盘中，但是后台的线程肯定是需要知道应该刷新哪些啊。

针对这个问题，MySQL设计出了 Flush 链表，他的作用就是记录被修改过的脏数据所在的缓存页对应的描述数据。如果内存中的数据和数据库和数据库中的数据不一样，那这些数据我们就称之为脏数据，脏数据之所以叫脏数据，本质上就是被缓存到缓存池中的数据被修改了，但是还没有刷新到磁盘中。

另外，当某个脏数据页页被刷新到磁盘后，其空间就腾出来了，然后又会跑到 Free 链表中了。

2.4 LRU List

如果 redis 的内存不够使用了，是不是自己还有一定的淘汰策略？最基本的准则就是淘汰掉不经常使用到的key。Buffer Pool 也类似，它也会有内存不够使用的情况，它是通过 LRU 链表来维护的。LRU 即 Least Recently Uesd（最近最少使用）。

MySql 会把最近使用最少的缓存页数据刷入到磁盘去，那 MySql 如何判断出 LRU 数据的呢？为此 MySql 专门设计了 LRU 链表，还引入了另一个概念：缓存命中率

说到底，每次查询数据的时候如果数据已经在缓存页中，那么就会将该缓存页对应的描述信息放到LRU链表的头部，如果不在缓存页中，就去磁盘中查找，如果查找到了，就将其加载到缓存中，并将该数据对应的缓存页的描述信息插入到LRU链表的头部。也就是说最近使用的缓存页都会排在前面，而排在后面的说明是不经常被使用到的。

最后，如果 Buffer Pool 不够使用了，那么 MySQL就会将 LRU 链表中的尾节点刷入到磁盘中，以及 Buffer Pool 腾出内存空间。来个整体的流程图给大家看下

##2.5 基于冷热数据分离的LRU链表

预读机制带来的问题

MySQL 在从磁盘加载数据的的时候，会将数据页的相邻的其他的数据页也加载到缓存中。但也会出现响应的问题：

• 数据页的相邻也被加载到LRU链表中，但是相邻的命中率并没有其他数据项的命令率高
• 全表扫描，会直接加载表中的所有数据，并挤出其他的数据

冷热分离

所谓的冷热分离，就是将 LRU 链表分成两部分，一部分是经常被使用到的热数据，另一部分是被加载进来但是很少使用的冷数据。

数据在从磁盘被加载到缓存池的时候，首先是会被放在冷数据区的头部，然后在一定时间之后，如果再次访问了这个数据，那么这个数据所在的缓存页对应描述数据就会被放转移到热数据区链表的头部。

缓冲池中的页大小默认为 16KB。但是 InnoDB 存储引擎对传统的 LRU 算法做了一些优化。 LRU 列表中加入了 midpoint 位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到 LRU 列表的首部，而是插入到 LRU 列表的 midpoint 位置。在默认配置下，该位置在 LRU 列表长度的 5/8 处。在 InnoDB 存储引擎中，把 midpoint之后的列表称为 old列表，之前的列表称为 new 列表。可以简单地理解为 new 列表中的页都是最活跃的热点数据。

**通常数据库中的缓冲池通过 LRU（最近最少使用）算法来进行管理的。**最频繁使用的页在 LRU 列表的前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表中尾端的页。

2.6 Buffer Pool的并发性能

Buffer Pool 一次只能允许一个线程来操作，一次只有一个线程来执行这一系列的操作，因为MySQL 为了保证数据的一致性，操作的时候必须缓存池加锁，一次只能有一个线程获取到锁。

但是话又说回来，串行执行再怎么快也是串行，虽然不是性能瓶颈，这还有更好的优化办法吗？那肯定的 MySQL早就设计好了这些规则。那就是 Buffer Pool 是可以有多个的，可以通过 MySQL的配置文件来配置，参数分别是：

#  Buffer Pool  的总大小
innodb_buffer_pool_size=8589934592
#  Buffer Pool  的实例数（个数）
innodb_buffer_pool_instance=4

2.7 Checkpoing

缓冲池的设计目的是为了协调 CPU 速度与磁盘速度的鸿沟，因此页的操作首先都是在缓冲池中完成。倘若每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常大的。若热点数据集中的某几个页中，那么数据库的性能将变得非常差。同时，如果在从缓存池将页的新版本刷新到磁盘时发生宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用 Write Ahead Log 策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失，通过重做日志来完成数据的恢复。

Checkpoint技术目的是解决以下几个问题：

• 缩短数据库的恢复时间
• 缓冲池不够用时，将脏页刷新到磁盘
• 重做日志不可用时，刷新脏页

当数据库发生宕机时，数据库不需要重做所有的日志，因为 Checkpoint 之前的页都已经刷新回磁盘。故数据库只需对 Checkpoint 后的重做日志进行恢复。这样就大大缩短了恢复的时间。

当前事务数据库系统对重做日志的设计都是循环使用的，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。

在 InnoDB 存储引擎内部，有两种 Checkpoint， 分别为：

1. Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘
2. Fuzzy Checkpoint 数据库在运行时只刷新一部分脏页，而不是刷新所有的脏页回磁盘

2.8 buffer poll 和 redo log

其实每个人都担心一个事，就是你在数据库的内存里执行了一堆增删改的操作，内存数据是更新了，但是这个时候如果数据库突然崩溃了，那么内存里更新好的数据不是都没了吗？

MySQL就怕这个问题，所以引入了一个redo log机制，你在对内存里的数据进行增删改的时候，他同时会把增删改对应的日志写入redo log中，如下图。

万一你的数据库突然崩溃了，没关系，只要从redo log日志文件里读取出来你之前做过哪些增删改操作，瞬间就可以重新把这些增删改操作在你的内存里执行一遍，这就可以恢复出来你之前做过哪些增删改操作了。 当然对于数据更新的过程，他是有一套严密的步骤的，还涉及到undo log、binlog、提交事务、buffer pool脏数据刷回磁盘，等等。

2.9 缓冲池问题

预读失效

为了充分利用缓冲池功能，一般在数据库启动时会采取预读机制，将可能要访问的页加入到缓冲池。那么当预读的页并没有用到，会发生什么呢？ 此时无效数据会占用大量空间，在热点数据加载进来后还要需要将无效数据清理，怎么办呢？

有人想到可以让预读失败的页在缓冲池中停留的时间尽可能短，这就是上面的midPoint机制。

midPoint将LRU List分为两个部分：新生代<—老生代

• 新页（预读页）加入缓冲池时，只加入到老生代头部。
• 而只有老年代的新页真正被读取时，才会加入到新生代的头部

因此没有被读取的新页会被新生代的热数据页更早地淘汰出缓冲池。

缓冲池污染

当某一个 SQL 语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL 性能急剧下降，这种情况叫缓冲池污染。

为此，MySQL 缓冲池加入了一个 “老生代停留时间窗口” 的机制：**只有满足 “被访问” 并且 “在老生代停留时间” 大于 T，才会被放入新生代头部。**这就避免了大量热点数据被立即替换。

3. MySQL日志

3.1 bin log

bin log是一个二进制格式的命令文件，用于记录用户对数据库更新的SQL语句信息（除了select和show），当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里。

当使用mysqldump进行全量备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志，也就是增量备份。还有主从复制。

为什么需要bin log

主要是为了故障恢复，在没有备份的情况下，可以根据bin log中的sql语句恢复部分数据。此外还用于主从模式下主服务器向从服务器同步数据

3.2 redo log

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页。redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。

因为直接写磁盘，系统只给当前的数据分配磁盘空间，而下一次写磁盘时，又分配另一块空间，因此磁头需要频繁移动，写磁盘就很慢，这就是随机读写。而先写redo log，然后系统根据redo log再申请大块磁盘空间（连续的），磁盘在写数据时不要频繁移动，速度也就很快了。

redo log可以用来实现事务的持久化。

为什么需要redo log

我们知道数据在写入的时候，都是先写入redo log，为什么不直接写入磁盘呢？

因为直接写磁盘，系统只给当前的数据分配磁盘空间，而下一次写磁盘时，又分配另一块空间，因此磁头需要频繁移动，写磁盘就很慢，这就是随机读写。而先写redo log，然后系统根据redo log再申请大块磁盘空间（连续的），磁盘在写数据时不要频繁移动，速度也就很快了。

3.3 undo log

undo log一般是数据日志，根据每行记录进行记录，具体内容就是将copy事务前的数据库内容（行）写到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。

为什么需要undo log

引入undo log是为了实现并发下数据版本控制，以及事务的回滚。

rollback segment：

在Innodb中，undo log被划分为多个段，具体某行的undo log就保存在某个段中，称为回滚段。可以认为undo log和回滚段是同一意思。

4. 数据持久化

4.1 全量备份

备份数据库的全部数据

# 导出整个库 [-d]表示只导出表结构，否则导出结构和数据
mysqldump -uroot -p [-d] databasename > name.sql
# 导出库下某几个表
mysqldump -uroot -p databasename test1 test2 test3 > createtab.sql

4.2 增量备份

备份上一次全量备份/增量备份后的数据，没有重复数据，备份数据量不大，所需时间短，备份速度快，恢复也快。

# 1. 修改配置文件，开启增量备份
[mysqld] 
server_id
log_bin=/MySQL/my3306/log/binlog/binlog  
binlog_format = row 

# 2. 进行日志备份
mysqladmin -uroot -p flush-logs  	'进行增量备份'