第04章_逻辑架构.pdf

1. 逻辑架构剖析

1.1 服务器处理客户端请求

首先MySQL是典型的C/S架构，服务器端程序使用的是mysqld。不论客户端进程和服务端进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程想服务器进程发送一段文本（SQL语句），服务器进程处理后再将客户端进程发送一段文本（处理结果）。
那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：
image.png
下面具体展开看一下：
image.png

1.2 Connectors

Connectors，指的就是不同语言中与SQL的交互，MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议，所以要使用MySQL，我们可以编写代码，跟MySQL Server 建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK，比如Native C API、JDBC、PHP等各种语言MySQL Connector，或者ODBC。但通过SDK来方位MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。
接下来的MySQL Server 结构可以分为如下的三层：

1.3 第1层：连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。 经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

  • 接下来我们思考一个问题，一个系统只会和MySQL服务建立一个连接吗？只能有一个系统和MySQL服务器建立连接吗？

当然不是，多个系统都可以和MySQL服务器建立连接，每个系统建立的连接肯定不止一个，所以为了解决TCP无线创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数。采用长连接模式 复用TCP连接，来解决上述问题。
image.png
TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。这些内容我们都归纳到MySQL 的连接管理组件中。所以连接管理组件 的职责是负责认证、管理连接、获取权限等。

1.4 第2层：服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有的跨存储引擎的功能也在这一层实现，如过程和函数等。在该层，服务器会解析查询 并创建相应的内部 解析树 ，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是SELECT 语句，服务器还会查询内部的缓存 ，如果缓存空间足够大，这样在解决大量读操作的环境中能很好的提升系统的性能。

  • SQL Interface: SQL接口
    • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
    • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
  • Parser: 解析器
    • 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
    • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树 ，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

  • Optimizer: 查询优化器

    • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划 。
    • 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
    • 它使用“选取-投影-连接 ”策略进行查询。例如：
      1. SELECT id,name FROM student WHERE gender = '女';
      这个SELECT查询先根据WHERE语句进行选取 ，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过滤，将这两个查询条件 连接起来生成最终查询结果。

  • Caches & Buffers： 查询缓存组件

    • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
    • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
    • 这个查询缓存可以在不同客户端之间共享 。
    • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。

      1.5 第3层：引擎层

      和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的MySQL还允许开发人员设置自己的存储引擎。这种高效的模块化架构为那些希望专门针对特定应用程序需求（例如数据仓库，事务处理或高可用情况）的人提供了巨大的好处，同时享受使用一组独立于任何借口和服务的优势存储引擎。
      插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。MySQL 8.0.25默认支持的存储引擎如下：
      image.png

      1.6 存储层

      所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

      1.7 小结

      以上简化如下：
      image.png
      简化为三层结构：
  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
  2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

    2. SQL执行流程

2.1 MySQL 中的 SQL执行流程

image.png
MySQL的查询流程：

1.查询缓存

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
image.png
大多数情况查询缓存就是个鸡肋，为什么呢？

  1. SELECT employee_id,last_name FROM employees WHERE employee_id = 101;

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MySQL不需要后面的复杂操作，就可以直接返回结果，这个效率会很高。
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大降低 ，只有 相同的查询操作才会命中查询缓存 。两个查询请求在任何字符上的不同（例如：空格、注释、 大小写），都会导致缓存不会命中。因此 MySQL 的 查询缓存命中率不高 。 同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数 举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW ，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！
此外，既然是缓存，那就有它 缓存失效的时候 。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER
TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于 更新压力大的数据库 来说，查询缓存的命中率会非常低。
总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。
一般，建议大家在静态表使用查询缓存，什么是静态表 ？就是一般极少更新的表，比如，一个系统配置表、字典表，这张表上的查询才使用使用查询缓存。好在MySQL 也提供 按需使用 的方式，你可以将my.cnf 参数 query_cache_type设置成DEMAND，代表SQL语句中有SQL_CACHE 关键词时才使用缓存。比如：

  1. # query_cache_type 有3个值，0表示关闭查询缓存 1表示开启 2（DEMAND）
  2. quert_cache_type=2

这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE 显示指定，如下：

  1. select SQL_CACHE * from test where id = 6;

查看当前mysql实例是否开启缓存机制：

  1. mysql> show global variables like '%query_cache_type%';
  2. +------------------+-------+
  3. | Variable_name    | Value |
  4. +------------------+-------+
  5. | query_cache_type | OFF   |
  6. +------------------+-------+
  7. 1 row in set (0.01 sec)

但是在mysql8中，并没有quert_cache_type 参数；我们需要查询参数如下：

  1. mysql> show status like '%Qcache%';

image.png

  • Qcache_free_blocks：表示查询缓存中还有多少剩余的blocks。如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
  • Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用， 是多了，还是不够用，DBA可以根据实际情况做出调整。
  • Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
  • Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理， 执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
  • Qcache.lowmem-prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适 当的调整缓存大小。
  • Qcache.not.cached：表示因为query_cache_type的设置而没有被缓存的查询数量。
  • Qcache.queries_in_cache：当前缓存中缓存的查询数量。
  • Qcache-total.blocks：当前缓存的block数量。

2. 解析器

在解析器中对 SQL 语句进行语法分析、语义分析。
image.png
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQl需要知道你要做什么，因此需要对SQL语句做解析。SQL语句的分析分为词法分析与语法分析

  • 分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
  • 接着，要做“ 语法分析 ”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否 满足 MySQL 语法 。

如果你的语句不对，就会收到 “ You have an error in your SQL syntax “的错误提醒，比如下面这个语句 from 写成了 fro

  1. # 语句
  2. select * fro user where id = 1;
  4. #错误
  5. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro user where id = 1' at line 1

如果SQL语句正确，则会生成一个这样的语法树：
image.png
下面是SQL词法分析的过程步骤：
image.png
至此我们解析器的工作任务也基本圆满了。接下来进入到优化器。

3. 优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索 等。经过了解析器，MySQL就知道你要做什么了。在开始执行前，还要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划
image.png
比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。
举例：如下语句是执行两个表的 join： 

  1. select * from test1 join test2 using(ID) where test1.name='zhangwei' and test2.name='mysql高级课程';
  • 方案1：可以先从表 test1 里面取出 name=’zhangwei’的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 ‘mysql高级课程’。
  • 方案2：可以先从表 test2 里面取出 name=’mysql高级课程’ 的记录的 ID 值，再根据 ID 值关联到 test1， 再判断 test1 里面 name的值是否等于 zhangwei。这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。 如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。

在查询优化器中，可以分为 逻辑查询 优化阶段和 物理查询 优化阶段。

  • 逻辑查询优化就是通过改变SQL 语句的内容来使得查询SQL 更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行 等价交换，对查询进行 重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写，条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
  • 物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的的作为执行计划，在这个阶段里，对于单表和多表的连接的操作，需要高效得 使用索引，提升查询效率。

4. 执行器

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。在执行之前需要判断该用户是否 具备权限 。如果没有，就会返回权限错误。如果具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

  1. select * from test where id=1;

如果有权限，就打开表继续执行，打开表的时候，执行器就会根据表的引擎定义，调用存储过程 API 对表进行读写。存储引擎 API 只是抽象接口，下面还有个 存储引擎层 ，具体实现还是要看表选择的存储引擎。
image.png
比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

  • 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。 SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器 。
image.png

2.2 MySQL 8.0 中SQL执行原理

前面的结构很复杂，我们需要抓取最核心的部分：SQL的执行原理。不同的DBMS的SQl的执行原理是相通的，只是在不同的软件中，各有各的实现路径。既然一条SQL语句会经历不同的模块，那我们就来看下，在不同的模块中，SQL执行所使用的资源（时间）是怎么样的。如何在MySQL中对一条SQL语句的执行时间进行分析。

1. 确认 profiling 是否开启

了解查询语句的层执行的过程： select @@profiling; 或者 show variables like ‘profiling’; 查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下：

  1. mysql> select @@profiling; 
  3. mysql> show variables like 'profiling';

image.pngimage.png
profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1： 

  1. mysql> set profiling=1;

Profiling功能由MySQL会话变量profiling控制。默认是OFF（关闭状态）

2. 多次执行相同SQL查询

然后我们执行一个 SQL 查询（你可以执行任何一个 SQL 查询）

  1. mysql> select * from employees;

3. 查看profiles

查看当前会话所产生的所有 profiles

  1. mysql> show profiles; # 显示最近的几次查询

image.png

4. 查看profile

显示执行计划，查看程序的执行步骤

  1. mysql> show profile;

image.png
当然你也可以查询指定的 Query ID，比如

  1. mysql> show profile for query 7;

查询 SQL 的执行时间结果和上面是一样的。 此外，还可以查询更丰富的内容：

  1. mysql> show profile cpu,block io for query 6;

image.png
继续：

  1. mysql> show profile cpu,block io for query 7;

image.png

  1. 除了查看cpu、io阻塞等参数情况，还可以查询下列参数的利用情况 ```sql SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]

type: ALL — 显示所有参数的开销信息 | BLOCK IO — 显示IO的相关开销 | CONTEXT SWITCHES — 上下文切换相关开销 | CPU — 显示CPU相关开销信息 | IPC — 显示发送和接收相关开销信息 | MEMORY — 显示内存相关开销信息 | PAGE FAULTS — 显示页面错误相关开销信息 | SOURCE — 显示Source_function,Source_file,Source_line 相关的开销信息 | SWAPS — 显示交换次数相关的开销信息

  2. 2. 发现两次查询当前情况都一直，说明没有缓存之说
  4. 在 8.0 版本之后，MySQL不再支持缓存的查询。一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了SQL的查询时间。
  5. <a name="i7dW8"></a>
  6. ## 2.3 MySQL 5.7 中SQL执行原理
  7. 上述操作在MySQL5.7中测试，发现前后两次相同的sql语句，执行的查询过程仍然是相同的。不是会使用缓存吗？这里我们需要 显式开启查询缓存模式 。在MySQL5.7中如下设置
  8. <a name="mDVZc"></a>
  9. ### 1. 配置文件中开启查询缓存 
  10. 在 /etc/my.cnf 中新增一行：
  11. ```properties
  12. query_cache_type=1

2. 重启mysql服务

  1. systemctl restart mysqld

3. 开启查询执行计划

由于重启过服务，需要重新执行如下指令，开启profiling。 

  1. mysql> set profiling=1;

4. 执行语句两次

  1. mysql> select * from locations; 
  3. mysql> select * from locations;

5. 查看profiles

image.png

6. 查看profile

显示执行计划，查看程序的执行步骤：

  1. mysql> show profile for query 1;

image.png

  1. mysql> show profile for query 2;

image.png
结论不言而喻。执行编号2时，比执行编号1时少了很多信息，从截图中可以看出查询语句直接从缓存中获取数据

  • 注意1

SQL必须是一直的，否则无法命中缓存
例如

  1. ## 虽然查询结果一直，但并没有命中缓存
  2. select *from mydb.mytb1 where id = 2
  4. select *from mydb.mytb1 where id > 1 and id < 3
  • 注意2

同样的开启缓存的配置信息如果在MySQL 8 中添加。重启服务时会报错：

  1. [root@01 ~]# vim /tc/my.cnf
  2. [root@01 ~]# systemctl restart mysqld;
  3. Job for mysqld.service failed because the control process exited with error code.See 
  4. "systemctl status mysqld.service" and "journalctl -xe " for details

分别在MySQL 5.7 和 MySQL 8 中执行如下命令：

  1. mysql> show variables like "%query_cache%"
  • MySQL 5.7中显示

image.png

  • MySQL 5.7中显示

image.png

3. 数据库缓冲池(buffer pool)

InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请 占用内存来作为数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。这样做的好处是可以让磁盘活动最小化，从而 减少与磁盘直接进行 I/O 的时间 。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3.1 缓冲池 vs 查询缓存

缓冲池和查询缓存是一个东西吗？不是。

1. 缓冲池（Buffer Pool）

首先我们需要了解在 InnoDB 存储引擎中，缓冲池都包括了哪些。在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，如下图所示：
image.png
从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

缓存池的重要性：
对于使用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以 页 的形式存放在 表空间 中的，而所谓的 表空间 只不过是InnoDB 对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。但是各位也都知道，磁盘的速度慢的跟乌龟一样，怎么能配得上“快如风，疾如电”的 CPU 呢？所以 InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其 缓存 起来，这样将来有请求再次访问该页面时，就可以省去磁盘 IO 的开销了。

缓存原则：
“ 位置 * 频次 ”这个原则，可以帮我们对 I/O 访问效率进行优化。首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。 其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会 优先对使用频次高的热数据进行加载 。

缓冲池的预读特性：
了解了缓冲池的作用之后，我们还需要了解缓冲池的另一个特性：预读。缓冲池的作用就是提高 I/O 效率，而我们进行读取数据的时候存在一个 “局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用 “预读”的机制提前加载，可以减少未来可能的磁盘 I/O 操作。

2. 查询缓存

查询缓存是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表 发生变化，查询缓存就会失效，因此命中率低。
缓冲池服务于数据库整体的 I/O 操作，它们的共同点都是通过缓存的机制来提升效率的。

3.2 缓冲池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
image.png
如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？
实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会 以一定的频率 刷到磁盘上，注意并不是每次发生更新的操作都会立即进行磁盘回刷。缓冲池会采用一种叫做 checkpoint 机制 将数据回刷到磁盘上，这样做的好处就是提升了数据库的整体性能。比如，当 缓冲池不够用 时，需要释放掉一些不常用的页，此时就可以强行采用 checkpoint 的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉，这里的脏页（dirty page）指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

3.3 查看/设置缓冲池的大小

  • 如果你使用的是MyISAM存储引擎，它只缓存索引，不缓存数据，对应的键缓存参数为key_buffer_size，你可以用它进行查看
  • 如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小。命令如下：
    1. show variables like 'innodb_buffer_pool_size';
    image.png
    你能看到此时 InnoDB 的缓冲池大小只有 134217728/1024/1024=128MB（默认）。我们可以修改缓冲池大小，比如改为256MB，方法如下：
    1. set global innodb_buffer_pool_size = 268435456;
    image.png
    或者：
    1. [server] 
    2. innodb_buffer_pool_size = 268435456
    然后再来看下修改后的缓冲池大小，此时已成功修改成了 256 MB：
    image.png

    3.4 多个Buffer Pool实例

    Buffer Pool 本质是InnoDB 向操作系统申请的一块 连续的内存空间，在多线程环境下，访问 Buffer Pool 中的数据都需要 加锁 处理，在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool 可能会影响请求的处理速度。所以在Buffer Pool 特别大的时候，我们可以把他们 拆分成若干个小个 Buffer Pool，每个Buffer Pool 都成为一个实例，他们都是独立的，独立的去申请内存空间，独立的管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。我们可以在服务器启动的时候通过设置 innodb_buffer_pool_instance 的值来修改Buffer Pool实例的个数，如下
    1. [server] 
    2. innodb_buffer_pool_instances = 2
    这样就表明我们要创建2个 Buffer Pool 实例。我们看下如何查看缓冲池的个数，使用命令：
    1. show variables like 'innodb_buffer_pool_instances';
    image.png
    那每个 Buffer Pool 实例实际占多少内存空间呢？其实使用这个公式算出来的：
    innodb_buffer_pool_size/innodb_buffer_pool_instances
    也就是总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。
    不过也不是说 Buffer Pool 的实例创建的越多越好，分别 管理各个Buffer Pool也是需要性能开销的，InnoDB规定：当innodb_buffer_pool_size 的值小于1G 的时候设置多个实例是无效的。InnoDB 会默认将innodb_buffer_pool_instances 设置为1。而我们鼓励在 Buffer Pool 大于等于 1G 的时候设置多个Buffer Pool 实例。

    3.5 引申问题

    Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。黑盒下的更新数据流程
    当我们查询数据的时候，会先去Buffer Pool中查询。如果Buffer Pool中不存在，存储引擎会先将数据从磁盘加载到Buffer Pool中，然后将数据返回给客户端；同理，当我们更新某个数据的时候，如果这个数据不存在于Buffer Pool，同样会先将数据加载进来，然后修改内存的数据。被修改过的数据会在之后统一刷入磁盘
    image.png
    这个过程看似没有问题，实则是有问题的。假设我们修改 Buffer Pool 中的数据成功，但是还没来得及将数据刷入磁盘，MySQL 就挂了怎么办? 按照上图的逻辑，此时更新之后的数据只存在于Buffer Pool 中，如果此时MySQL宕机，这部分数据将会永久丢失。
    再者，我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？ 连数据持久化的保证、事务回滚都做不到还谈什么崩溃恢复？
    答案：Redo Log & Undo Log