封面来源：由博主个人绘制，如需使用请联系博主。

1. 逻辑架构剖析

1.1 服务器处理客户端请求

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld。

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（SQL 语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？以查询请求为例展示：

具体展开来看就是：

Connectors：连接器，MySQL 服务器之外的客户端程序，与具体的语言相关；
Management Services & Utilities：基础服务组件；
SQL Interface：SQL 接口，用于接收 SQL 指令，返回查询结果；
Parser：解析器，用于语法解析、语义解析，生成语法树；
Optimizer：优化器，属于核心组件，对 SQL 进行优化；
Caches & Buffers：查询缓存，以 key-value 的方式缓存查询结果；
Pluggable Storage Engines：插件式存储引擎，与底层的文件系统进行交互；
File System：文件系统；
Files & Logs：日志文件。

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 16.11 Overview of MySQL Storage Engine Architecture

1.2 Connector

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 连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。

经过三次握手建立连接成功后，MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

用户名或密码不对，会收到一个 Access denied for user 错误，客户端程序结束执行.。
用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。

一个系统只会和 MySQL 服务器建立一个连接吗？只能有一个系统和 MySQL 服务器建立连接吗？

当然不是，多个系统都可以和 MySQL 服务器建立连接，每个系统建立的连接肯定不止一个。为了解决 TCP 无限创建与 TCP 频繁创建销毁带来的资源耗尽、性能下降问题，MySQL 服务器里有专门的 TCP 连接池限制连接数，采用长连接模式复用 TCP 连接解决上述问题。

TCP 连接收到请求后，必须要专门分配一个线程与客户端进行交互，所以还会有个线程池，完成后续流程。每一个连接从线程池中获取线程，省去了创建、销毁线程的开销。

这些内容都归纳到 MySQL 的连接管理组件中。

所以连接管理的职责是负责认证、管理连接、获取权限信息。

1.4 服务层

第二层架构主要完成大多数的核心服务功能，如 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 引擎层

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，同时开源的 MySQL 还允许开发人员设置自己的存储引擎。

这种模块化架构为那些希望专门针对特定应用程序需求（例如数据仓库、事务处理或高可用性情况）的人提供了巨大的好处，同时享受使用一组独立于任何接口和服务的优势存储引擎。

插件式存储引擎层（Storage Engines），真正负责了 MySQL 中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，可以根据自己的实际需要进行选取。

可以使用以下语句查询默认支持的存储引擎：

1	show engines;

MySQL 8.0.32 默认支持的存储引擎如下：

1.6 存储层

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

1.7 小结

可以对 MySQL 的架构图进行简化：

简化后有三层结构：

连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

2. SQL 执行流程

2.1 MySQL 中的 SQL 执行流程

查询缓存

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL 8.0 之后就抛弃了这个功能。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式被直接缓存在内存中，其中 key 是查询的语句，value 是查询的结果。如果查询请求能够直接在这个缓存中找到 key，那么对应的 value 就会被直接返回给客户端。如果语句不在查询缓存中，会继续后面的执行阶段。执行完成后，执行结果被存入查询缓存中。简单来说，如果查询命中缓存，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 2	# query_cache_type 有三个值，0 表示关闭查询缓存 OFF，1 表示开启 ON，2 表示按需使用 DEMAND query_cache_type=2

在 MySQL 5.7 中，可以使用以下语句查看查询缓存的开启情况：

1	SHOW GLOBAL VARIABLES LIKE "%query_cache_type%";

而在 MySQL 8.0 中，上述语句的执行结果只会返回 Empty set。

设置完成后，对于要使用查询缓存的语句可以使用 SQL_CACHE 指定：

1	SELECT SQL_CACHE * FROM test WHERE id = 1;

在 MySQL 5.7 中可以使用以下语句监控查询缓存的命中率：

1	SHOW STATUS LIKE "%Qcache%";

Qcache_free_blocks 表示查询缓存中还有多少剩余的 blocks，值越大说明查询缓存中的内存碎片越多，在一定的时间后会进行整理。

Qcache_free_memory 表示查询缓存的内存大小，通过这个参数可以很清晰地知道当前系统的查询内存是否够用，以便根据实际情况做出调整。

Qcache_hits 表示缓存命中次数。可以通过该值来验证查询缓存的效果，值越大，缓存效果越理想。

Qcache_inserts 表示缓存未命中并添加缓存的次数，与 Qcache_hits 相反，值越大，缓存效果越差。

Qcache_lowmem_prunes 该参数记录有多少条查询因为内存不足而从查询缓存中移除。

Qcache_not_cached 表示因为 query_cache_type 的设置而没有被缓存的查询数量。

Qcache_queries_in_cache 表示当前缓存中缓存的查询数量。

Qcache_total_blocks 表示当前缓存的 block 数量。

解析器

在解析器中可以对 SQL 语句进行语法分析、语义分析。

如果没有命中查询缓存，就需要执行真正的 SQL 语句。MySQL 需要知道用户要做什么，因此需要对 SQL 语句进行解析，SQL 语句的解析分为词法分析和语法分析。

解析器先做“词法分析”，用户输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么、代表什么。比如 MySQL 从用户输入的 SELECT 关键字识别出这是一个查询语句。

接着，要做“语法分析”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断用户输入的 SQL 语句是否满足 MySQL 语法。如果语法错误，就会收到如下错误提醒：

You have an error in your SQL syntax;

如果语法正确，则会生成语法树，以下述 SQL 语句为例：

1	select username, ismale from userinfo where age > 20 and level > 5 and 1 = 1;

MySQL 解析器分析步骤：

优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索。

经过解析器后，MySQL 知道了用户的目的。在开始执行前，还要经过优化器的处理。一条查询可以有很多种执行方式，而最后都返回相同的结果，优化器的作用就是找到这其中最好的执行计划。

比如：在表里有多个索引时，优化器决定使用哪个索引；或者在一个语句有多表关联 JOIN 的时候，决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。

以下述 SQL 语句为例：

1 2	SELECT * FROM test1 JOIN test2 USING(id) WHERE test1.name = 'zhangwei' AND test2.name = 'mysql高级课程';

针对这条 SQL 语句有两种执行方式：

先从 test1 表里面取出 name 列等于 zhangwei 的记录的 id 值，再根据 id 值关联到 test2 表，然后判断 test2 里面 name 值是否等于“mysql高级课程”；
还可以从 test2 表里面取出 name 列等于“mysql高级课程”的记录的 id 值，再根据 id 值关联到 test1 表，然后判断 test1 里面 name 的值是否等于 zhangwei。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，确定语句的执行方案，然后进入执行器阶段。

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

逻辑查询优化 是通过改变 SQL 语句的内容使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

物理查询优化 是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

执行器

目前仅仅是产出了执行计划，要到执行器阶段才会真正地去读写真实表。

在执行之前需要 判断该用户是否具备权限。如果没有，会返回权限错误，反之执行 SQL 查询并返回结果。在 MySQL 8.0 以下的版本，如果设置了查询缓存，还会将查询结果进行缓存。

如果有权限，打开表继续执行。打开表时，执行器就会根据表的引擎定义，调用存储引擎 API 对表进行读写。存储引擎 API 只是抽象接口，下面还有个存储引擎层，具体实现由表选择的存储引擎决定。

以下述 SQL 语句为例：

1	SELECT * FROM test WHERE id = 1;

如果 test 表中的 id 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取该表的第一行，判断 id 列的值是否是 1，如果不是则跳过，反之则将这行存在结果集中，重复相同的逻辑，直到表的最后一行，执行器最后将结果集返回给客户端。

总结下 SQL 语句在 MySQL 中的执行流程：

flowchart LR
    sql(SQL 语句) --> cache(查询缓存) --> parser(解析器) --语法树--> optimizer(优化器) --查询计划--> executor(执行器)

2.2 MySQL 8.0 中 SQL 执行原理

不同的 DBMS 的 SQL 的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

既然一条 SQL 语句会经历不同的模块，那在不同的模块中，SQL 执行所使用的资源（时间）是怎样的呢？如何在 MySQL 中对一条 SQL 语句的执行时间进行分析呢？

确认 profiling 是否开启

执行下述语句查看是否开启计划，这可以让 MySQL 收集在 SQL 执行时所使用的资源情况：

1	SELECT @@profiling;

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

或者：

1	SHOW VARIABLES LIKE 'profiling';

mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

默认 @@profiling 的值为 0 或者 profiling 的值为 OFF 代表关闭，需要把 profiling 打开，即设置为 1：

1	SET profiling=1;

多次执行相同的 SQL 查询

执行任意一个 SQL 查询，比如：

1	SELECT * FROM employees;

查询出 employees 表中的所有数据，然后可以再执行一次一模一样的查询。

查看 profiles

SHOW PROFILES 和 SHOW PROFILE 语句可以展示当前会话（退出 session 后，profiling 重置为 0）中执行语句的资源使用情况。

显示当前会话所产生的所有 profiles：

1	SHOW PROFILES;

mysql> SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00109400 | show databases          |
|        2 | 0.00024500 | SELECT DATABASE()       |
|        3 | 0.00081550 | show databases          |
|        4 | 0.00105000 | show tables             |
|        5 | 0.00059025 | SELECT * FROM employees |
|        6 | 0.00053675 | SELECT * FROM employees |
+----------+------------+-------------------------+

查看 profile

也可以详细查看最近一次执行语句的资源使用情况：

1	SHOW PROFILE;

mysql> SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000095 |
| Executing hook on transaction  | 0.000006 |
| starting                       | 0.000011 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000046 |
| init                           | 0.000007 |
| System lock                    | 0.000012 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000022 |
| preparing                      | 0.000023 |
| executing                      | 0.000235 |
| end                            | 0.000006 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000012 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000018 |
| cleaning up                    | 0.000016 |
+--------------------------------+----------+

各个 Status 的含义说明：

checking permissions：权限检查
Opening tables：打开表
init：初始化
System lock：锁系统
optimizing：查询优化
preparing：准备
executing：执行

还可以查询指定的 Query_ID：

1	SHOW PROFILE FOR QUERY 6;

结果与 SHOW PROFILE 返回的一致，因为 Query_ID 为 6 分析结果就是最后一次。

还可以查询更多内容，比如：

1	SHOW PROFILE cpu,block io FOR QUERY 6;

结果在上述基础上会多出 CPU_user、CPU_system、Block_ops_in 和 Block_ops_out 列。

除此之外，还可以查询下列参数的利用情况：

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 -- 显示交换次数相关的开销信息
}

上述两次 SHOW PROFILE 的结果一致，说明没有缓存一说。在 MySQL 8.0 之后，不再支持缓存的查询。一旦数据表有更新，缓存都将清空，因此只有数据表是静态时，或者数据表很少发生变化时，使用缓存查询才有价值，否则经常更新的数据表，反而增加了 SQL 的查询时间。

2.3 MySQL 5.7 中 SQL 执行原理

MySQL 5.7 是支持查询缓存的，先在 /etc/my.cnf 中新增一行：

1	query_cache_type=1

然后重启 MySQL 服务：

1	systemctl restart mysqld

紧接着像【2.2 MySQL 8.0 中 SQL 执行原理】中的操作一样，先开启 profiling，然后连续执行两次 完全相同 的查询，观察着两次的资源使用情况，可以发现第二次的信息相比第一次少了许多，这是因为第二次查询结果是直接从缓存中获取的。

注意事项：

两次的查询必须是完全一样的，无论是 SQL 语句还是查询结果，否则无法命中缓存；
如果在 MySQL 8.0 的 my.cnf 文件中追加 query_cache_type 信息，后续重启 MySQL 服务时会报错，因为 MySQL 8.0 已经移除了查询缓存。

2.4 SQL 语法顺序

随着 MySQL 版本的更新换代，其优化器也在不断地升级，优化器会分析不同执行顺序产生的性能消耗而动态调整执行顺序。

现在需要查询部门中年龄高于 20 岁、总数量不少于 2 人的人数最多的部门信息，下面是经常出现的查询顺序：

2.5 Oracle 中的 SQL 执行流程

Oracle 中采用了 共享池 来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤可以知道应该采用硬解析还是软解析。

SQL 在 Oracle 中的执行流程：

SQL 语句在 Oracle 中经历了以下的几个步骤：

语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误；
语义检查：检查 SQL 中的访问对象是否存在。比如在写 SELECT 语句的时候，列名写错了，系统会提示错误，语法检查和语义检查的保证 SQL 语句没有错误；
权限检查：看用户是否具备访问该数据的权限；
共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行 软解析，还是 硬解析；
优化器：在优化器中进行硬解析，决定要怎么做，比如创建解析树，生成执行计划；
执行器：当有了解析树和执行计划后，就知道 SQL 该怎么被执行，就可以在执行器中执行语句了。

那什么是软解析、什么是硬解析呢？

在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接执行，进入“执行器”的环节，这就是 软解析。

如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”步骤，这就是 硬解析。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。上面已经讲到了库缓存区，它主要
缓存 SQL 语句和执行计划，而 数据字典缓冲区 存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

库缓存 这一个步骤，决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率，应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树、生成执行计划是很消耗资源的。

如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量 是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

以下面的查询语句为例：

1	SELECT * FROM player WHERE player_id = 10001;

使用绑定变量后变成：

1	SELECT * FROM player WHERE player_id = :player_id;

这两个查询语句的效率在 Oracle 中是完全不同的。如果在查询 player_id = 10001 之后，还会查询 10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析；而第二种方式使用了绑定变量，在第一次查询后，共享池中已经存在这类查询的执行计划，后续会使用软解析进行查询。

因此，可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量，但这种方式也有缺点，使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

Oracle 的架构图：

Oracle 的架构简图：

Oracle 和 MySQL 在进行 SQL 的查询上面有软件实现层面的差异。Oracle 提出了共享池的概念，通过共享池来判断是进行软解析，还是硬解析。

3. 数据库缓冲池

InnoDB 存储引擎是以页为单位来管理存储空间的，MySQL 中进行的增删改查操作本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。磁盘 I/O 需要消耗的时间很多，如果在内存中进行操作，效率则会高很多。为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool（即缓冲池）之后才可以访问。

这样做可以让磁盘活动最小化，减少与磁盘直接进行 I/O 的时间。

3.1 缓冲池 VS 查询缓存

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

缓冲池

在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，如下图所示：

InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

缓冲池的重要性：

对于使用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是 InnoDB 对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。磁盘的访问速度较慢，与 CPU 之间存在巨大的鸿沟，而缓冲池的存在就可以消除之间的鸿沟。

所以 InnoDB 存储引擎在处理客户端的请求，当需要访问某个页的数据时，就会把完整的页数据全部加载到内存中，也就是说即使只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在之后也不会立即把该页对应的内存空间释放掉，而是将其缓存起来，如果将来有请求再次访问该页面时，就可以省去磁盘的 I/O 开销。

缓存原则：

“位置 $\times$ 频次”这个原则，可以帮我们对 I/O 访问效率进行优化。

首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。

其次，频次决定优先级顺序。因为缓冲池的大小是有限的，假设磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序， 会优先对使用频次高的热数据进行加载。

缓冲池的预读：

缓冲池还有一个重要的特性：预读。

缓冲池是为了提升 I/O 效率，而进行读取数据的时候存在一个“局部性原理”，也就是说在使用了一些数据后，大概率还会使用它周围的一些数据，因此采用“预读’机制提前加载附近页，以便减少未来可能的磁盘 I/O 开销。

查询缓存

查询缓存是提前把查询结果缓存起来，下次无需执行可以直接拿到结果。MySQL 中的查询缓存不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低，在 MySQL 8.0 中已经移除了查询缓存。

缓冲池服务于数据库整体的 I/O 操作，它们的共同点都是通过缓存的机制来提升效率。

3.2 缓冲池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

缓存池在数据库中的结构如下图所示：

如果执行 SQL 语句的时候更新了缓存池中的数据，那这些数据会马上同步到磁盘上吗？

当对数据库中的记录进行修改时，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。并不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据回写到磁盘上，这样做提升了数据库的整体性能。

比如，当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用 checkpoint ，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。这里脏页（dirty page）指的是缓冲池中被修改过，与磁盘上的数据页不一致的页。

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

如果使用的是 MyISAM 存储引擎，它只缓存索引，不会缓存数据，对应的键缓存参数为 key_buffer_size。

如果使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小：

1	show variables like 'innodb_buffer_pool_size';

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

能看到此时 InnoDB 的缓冲池大小只有 $134217728 \ \div 1024 \ \div 1024=128MB$ ，可以尝试修改缓冲池大小，比如
改为 256MB：

1	set global innodb_buffer_pool_size = 268435456;

或者修改配置文件：

1 2	[server] innodb_buffer_pool_size = 268435456

3.4 多个 Buffer Pool 实例

Buffer Pool 本质是 InnoDB 向操作系统申请的一块连续的内存空间，在多线程环境下，访问 Buffer Pool 中的数据都需要加锁处理。在 Buffer Pool 特别大、多线程并发访问特别高的情况下，单一的 Buffer Pool 可能会影响请求的处理速度。所以在 Buffer Pool 特别大的时候，可以把它们拆分成若干个小的 Buffer Pool，每个 Buffer Pool 都称为一个实例，它们各自独立地去申请内存空间、管理各种链表。所以在多线程并发访问时不会相互影响，从而提高并发处理能力。

可以在服务器启动时通过设置 innodb_buffer_pool_instances 的值来修改 Buffer Pool 实例的个数：

1 2	[server] innodb_buffer_pool_instances = 2

这表明要创建 2 个 Buffer Pool 实例。

可以使用以下命令查看缓冲池的个数：

1	show variables like 'innodb_buffer_pool_instances';

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

那每个 Buffer Pool 实例实际占多少内存空间呢？可以使用以下公式算出来：

$innodb\_buffer\_pool\_size \ \div innodb\_buffer\_pool\_instances$

也就是缓冲池的总大小除以实例的个数。

也不是说 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 中，会先数据加载进来，然后修改内存的数据，被修改过的数据会在之后统一刷入磁盘。