MySQL 高级 - 第五章 | 逻辑架构

---

上篇：第四章、配置文件与系统变量
下篇：第六章、存储引擎
本文内容紧张源于：bilibili-尚硅谷-MySQL高级篇
第五章 逻辑架构

---

5.1 逻辑架构的剖析

---

5.1.1 服务器处置惩罚客户端请求

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器步调利用的是 mysqld。
岂论客户端进程和服务器进程是采用哪种方式进行通讯，末了实现的效果都是：客户端进程向服务器进程发送一段文本（SQL）语句，服务器进程处置惩罚后再向客户端进程发送一段文本（处置惩罚结果）。
那服务器进程对客户端进程发送的请求做了什么处置惩罚，才能产生末了的处置惩罚结果呢？这里以查询请求为例展示：

具体流程（针对于 MySQL-5.7）：

在 MySQL-8.0 版本中 缓存 被移除了

---

5.1.2 客户端连接器

客户端连接器（Connectors）指的是不同语言中与 SQL 的交互，MySQL 起首是一个网络步调，在 TCP 之上界说了本身的应用层协议，所以要利用 MySQL，可以编写代码跟 MySQL 创建 TCP 连接，之后按照其界说好的协议进行交互或者比力方便的办法是调用 SDK，比如 Native C API、JDBC、PHP 等各个语言 MySQL Connector，或者通过 ODBC，但通过 SDK 来访问 MySQL 本质上照旧在 TCP 连接上通过 MySQL 协议跟 MySQL 进行交互。

---

5.1.3 MySQL Server 的三层架构

简化为三层布局：

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

---

5.1.3.1 第一层：连接层

系统（客户端）访问 MySQL 服务器前做的第一件事就是创建 TCP 连接
经过三次握手创建连接乐成后，MySQL 服务器对 TCP 传输过来的账号暗码做身份认证、权限获取

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

一个系统只会和 MySQL 服务器创建一个连接吗？只能有一个系统和 MySQL 服务器创建连接吗？
当然不是，多个系统都可以和 MySQL 服务器创建连接，每个系统创建的连接肯定不止一个，所以为相识决 TCP 无穷创建与 TCP 频仍创建销毁带来的资源耗尽、性能降落问题。MySQL 服务器有专门的 TCP 连接池 限制连接数。
采用 长连接模式复用 TCP 连接来办理上述问题。

TCP 连吸收到请求后，必须要分配给一个线程专门与这个客户端的交互，所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。
所以 连接受理 的职责就是负责管理连接、账号认证和获取权限信息。

---

5.1.3.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 语句的执行路径，生成一个 执行计划
    
  • 这个执行计划表明应该 利用哪些索引 进行查询（全表检索照旧利用索引检索），表之间的连接顺序怎样，末了会按照执行计划中的步调调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户
    
  • 它利用 选取-投影-连接 策略进行查询，例如：SELECT id,name FROM student WHERE gender = '女';，这个 SELECT 查询会根据 WHERE 语句进行 选取，而不是将表全部查询出来以后再进行 gender 过滤，根据 id 和 name 进行属性 投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件 连接 起来生成终极查询布局
    
  
  
• Cache & Buffers：查询缓存组件
  
  
  
  • MySQL 内部维持着一些 Cache 和 Buffer，比如 Query Cache 用来缓存一条 SELECT 语句的执行结果，如果可以或许在其中找到对应的查询结果，那么就不必再进行查询剖析、优化和执行的整个过程了，直接将结果反馈给客户端
    
  • 这个缓存机制是由一系列小缓存构成的，比如表缓存、记录缓存、key 缓存、权限缓存等
    
  • 这个查询缓存可以在 不同客户端之间共享
    
  • 从 MySQL-5.7.20 开始，不推荐利用查询缓存，并在 MySQL-8.0 中删除
    
  
  

---

5.1.3.3 第三层：存储引擎层

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，紧张体如今存储引擎的架构上，插件式的存储引擎 架构将查询处置惩罚和其它的系统使命以及数据的存储提取相分离，这种架构可以根据业务的需求和现实必要选择合适的存储引擎。同时开源的 MySQL 还允许 开发职员设置本身的存储引擎。
这种高效的模块化架构为那些希望专门针对特定应用步调需求（例如数据仓库、变乱处置惩罚或可用性情况）的人提供了巨大的好处，同时享受利用一组独立于任何接口和服务的优势存储引擎。
插件式存储引擎层（Storage Engines）真正的负责了 MySQL 中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过 API 与存储引擎进行通讯。不同的存储引擎具有的功能不同，这样可以根据本身的现实必要进行选取。
可以用以下命令检察 MySQL 所支持的存储引擎

1. SHOW ENGINES;

复制代码

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

1. mysql> SHOW ENGINES;
2. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

复制代码

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

---

5.2 SQL 执行流程

---

5.2.1 MySQL 中的 SQL 执行流程

MySQL 的查询流程：

• ① 查询缓存
  
• ② 剖析器
  
• ③ 优化器
  
• ④ 执行器
  

1. 查询缓存：Server如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到剖析器阶段。必要分析的是，由于查询缓存往往服从不高，所以在 MySQL 8.0 之后就扬弃了这个功能。

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句，之前执行过的语句及其结果可能会以 key-value 键值对的情势被直接缓存在内存中，key 是查询语句，value 是查询的结果。如果你的查询可以或许直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中，所以，如果查询命中缓存，MySQL 不必要执行后面的复杂操作，就可以直接返回结果，这个服从会很高。
但是在大多数情况下查询缓存就是个鸡肋，由于查询缓存是提前把查询结果缓存起来，这样下次不必要执行就可以直接拿到结果，必要分析的是，在 MySQL 中的查询缓存，不说缓存查询计划，而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大低落，只有 相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写等），都会导致缓存不会命中，因此 MySQL 查询缓存命中率不高。
比如：

1. SELECT id,name FROM t_user where id = 1;
3. # 即使该 SQL 只比上一条 SQL 多一个空格，也会未命中缓存
4. SELECT id, name FROM t_user where id = 1;

复制代码

同时，如果查询请求中包罗某些系统函数、用户自界说变量和函数、一些系统表、如 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 表示关闭查询缓存 OFF，1 代表开启，2 表示 DEMAND
2. query_cache_type=2

复制代码

这样对于默认的 SQL 语句都不利用查询缓存，而对于你确定要利用查询缓存的语句，可以用 SQL_CACHE 表现指定，像下面这个语句一样：

1. SELECT SQL_CACHE * FROM table_name WHERE ...;

复制代码

不利用查询缓存也可以写作

1. SELECT SQL_NO_CACHE * FROM table_name WHERE ...;

复制代码

检察当前 MySQL 实例是否开启缓存机制

1. # MySQL-5.7 中，在默认情况下 MySQL 也是关闭的
2. mysql>SHOW GLOBAL VARIABLES LIKE '%query_cache_type%';
3. +---------------------------+---------+
4. | Variable_name             | Vaule   |
5. +---------------------------+---------+
6. | Query_cache_type          | OFF     |
7. +--------------------+----------------+
8. 1 rows in set (0.00 sec)
10. # MySQL-8.0 中不存在该参数
11. mysql> SHOW GLOBAL VARIABLES LIKE '%query_cache_type%';
12. Empty set (0.00 sec)

复制代码

监控查询缓存命中率：

1. mysql> SHOW STATUS LIKE '%Qcache%';
2. +---------------------------+---------+
3. | Variable_name             | Vaule   |
4. +---------------------------+---------+
5. | Qcahce_free_blocks        | 1       |
6. | Qcahce_free_memory        | 1031832 |
7. | Qcahce_hits               | 0       |
8. | Qcahce_inserts            | 0       |
9. | Qcahce_lowmem_prunes      | 0       |
10. | Qcahce_not_cached         | 1280    |
11. | Qcahce_queries_incahce    | 0       |
12. | Qcahce_total_blocks       | 1       |
13. +--------------------+----------------+
14. 9 rows in set (0.00 sec)

复制代码

运行结果分析：

• Qcahce_free_blocks：表示查询缓存中还有多少个剩余的 blocks，如果该值表现较大，则分析查询缓存中的 内存碎片 过多了，可能在肯定的时间进行整理
  
• Qcahce_free_memory：查询缓存的内存大小，通过这个参数可以很清楚的知道当前系统的查询内存是否够用，是多了，照旧不敷用，DBA 可以根据现实情况做出调整
  
• Qcahce_hits：表示有 多少次命中缓存，紧张可以通过该值来验证查询缓存的效果，数字越大，缓存效果越理想
  
• Qcahce_inserts：表示 多少次未命中然后参加，意思是新来的 SQL 请求在缓存中未找到，不得不执行查询处置惩罚，执行查询处置惩罚后把结果 insert 到查询缓存中，这样的情况的次数越多，表示查询缓存应用到的比力少，效果也就不理想，当然系统刚启动后，查询缓存是空的，也很正常
  
• Qcahce_lowmem_prunes：该参数记录有 多少条查询由于内存不足而被移除 出查询缓存，通过这个值，用户可以得当的调整缓存大小
  
• Qcahce_not_cached：表示由于 query_cache_type 的设置而没有被缓存的查询数量
  
• Qcahce_queries_incahce：当前缓存中 缓存的查询数量
  
• Qcahce_total_blocks：当前缓存的 block 数量
  

2. 剖析器： 在剖析器中对 SQL 语句进行语法分析、语义分析。

如果没有命中查询缓存，就要开始真正执行语句了，起首，MySQL 必要知道你要做什么，因此必要对 SQL 语句进行剖析，SQL 语句的分析分为 词法分析 和 语法分析。
分析器先做 词法分析，你输入的是由多个字符串和空格构成的一条 SQL 语句，MySQL 必要识别出里面的字符串分别是什么，代表什么。
MySQL 从你输入的 select 这个关键字就能判断这是一个查询语句，并把所必要查询的表、列都识别出来
接着要做 语法分析，根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否 满足 MySQL 语法。
如果你的语句不对，就会提示 You have an error in your SQL syntax 的错误提示，比如下面这个语句将 from 写成 fro

1. mysql> select * fro t_user where id = 1;
2. 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 t_user where id = 1' at line 1

复制代码

如果 SQL 语句精确，则会生成一个这样的语法树：

下图是 SQL 语法分析的过程步调：

至此剖析器的工作使命也算根本圆满了，接下来进入到优化器

3. 优化器： 在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索，照旧根据 索引检索 等
经过相识析器，MySQL 就知道你要做什么，在开始执行之前，还要先经过优化器的处置惩罚，一条查询可以有很多种执行方式，末了都返回相同的结果，优化器的作用就是找到这其中最好的执行计划。

比如：优化器是在表里面有多个索引的时间，决定利用哪个索引，或者在一个语句有多表关联（join）的时间，决定各个表的连接顺序，还有表达式简化，子查询转为连接，外连接转为内连接等
举例：如下语句是执行两个表的 join：

1. select * from test1 join test2 using(ID)
2. where test1.name = 'duojiala' and test2.name = '泰酷啦';

复制代码

• 方案一：可以先从表 test1 里面取出 name = 'duojiala' 的记录 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name 的值是否等于 泰酷啦
  
• 方案二：可以先从表 test2 里面取出 name = '泰酷啦' 的记录 ID 值，再根据 ID 值关联到表 test1，再判断 test1 里面 name 的值是否等于 duojiala
  

这两种执行方式的逻辑结果是一样的，但是执行的服从会有不同，而优化器的作用就是决定选择利用哪一个方案，优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
在查询优化器种，可以分为 逻辑查询 优化阶段和 物理查询 优化阶段
逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行 等价变更，对查询进行 重写，而查询重写的数学底子就是关系代数，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行外连接消除、嵌套连接消除等
物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此必要计算各种物理路径的代价，从中选择代价最小的作为执行计划，在这个阶段里，对于单表和多表连接的操作，必要高效地 利用索引，提升查询服从。

4. 执行器：
截止到如今，还没有真正去读写真实的表，仅仅只是产出了一个执行计划，于是就进入了 执行器阶段

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

1. select * from test where id = 1;

复制代码

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

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

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

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

---

5.2.2 SQL 执行原理

---

5.2.2.1 查询资源利用情况

不同的 DBMS 其 SQL 的执行原理是相通的，只是在不同的软件中，各有各的实现路径。
一条 SQL 语句会履历不同的模块，可以用 show profile 命令检察在不同模块中 SQL 执行所利用的资源（时间）。
MySQL 默认情况下是不会开启 profiling 的，可以通过以下命令确认是否开启计划：

1. select @@profiling;
3. show variables like '%profiling%';

复制代码

示例：

1. mysql> select @@profiling;
2. +-------------+
3. | @@profiling |
4. +-------------+
5. |           0 |
6. +-------------+
7. 1 row in set, 1 warning (0.00 sec)
9. mysql> show variables like '%profiling%';
10. +------------------------+-------+
11. | Variable_name          | Value |
12. +------------------------+-------+
13. | have_profiling         | YES   |
14. | profiling              | OFF   |
15. | profiling_history_size | 15    |
16. +------------------------+-------+
17. 3 rows in set (0.00 sec)

复制代码

profiling 为 0 或者 OFF，表示关闭
如果想让 MySQL 收集在 SQL 执行时所利用的资源情况，则必要把 profiling 打开，即设置为 1；

1. mysql> SET profiling = 1;
2. Query OK, 0 rows affected, 1 warning (0.00 sec)

复制代码

show profile 根本语法：

1. show profile [type [, type] ...] [for Query_ID] [LIMIT row_count [OFFSET offset]]

复制代码

type 可设置的值有：

• ALL：表现所有参数的开销信息
  
• BLOCK IO：表现 IO 的相干开销
  
• CONTEXT SWITCHES：上下文切换相干开销
  
• CPU：表现 CPU 相干开销信息
  
• IPC：表现发送和吸收相干开销信息
  
• MEMORY：表现内存相干开销信息
  
• PAGE FALUTS：表现页面错误相干开销信息
  
• SOURCE：表现 Source_function，Source_file，Source_line 相干的开销信息
  
• SWAPS：表现互换次数相干的开销信息
  

展示所有 SQL 语句的简要耗时情况：

1. show profiles;

复制代码

示例：

1. mysql> show profiles;
2. +----------+------------+----------------------------+| Query_ID | Duration   | Query                      |+----------+------------+----------------------------+|        1 | 0.00030500 | select * from t_access_log ||        2 | 0.00027300 | select * from t_access_log |+----------+------------+----------------------------+2 rows in set, 1 warning (0.00 sec)

复制代码

查询最近一条 SQL 的详细耗时信息

1. show profile;

复制代码

示例：

1. mysql> show profile;
2. +--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000066 || Executing hook on transaction  | 0.000004 || starting                       | 0.000008 || checking permissions           | 0.000006 || Opening tables                 | 0.000032 || init                           | 0.000006 || System lock                    | 0.000009 || optimizing                     | 0.000005 || statistics                     | 0.000013 || preparing                      | 0.000015 || executing                      | 0.000068 || end                            | 0.000003 || query end                      | 0.000003 || waiting for handler commit     | 0.000007 || closing tables                 | 0.000007 || freeing items                  | 0.000012 || cleaning up                    | 0.000010 |+--------------------------------+----------+17 rows in set, 1 warning (0.00 sec)

复制代码

这里默认查询的就是 Query_ID = 2 的这条 SQL

当然也可以查询指定 SQL 的执行过程，语法如下：

1. show profile for query Query_ID;

复制代码

示例：

1. mysql> show profile for query 1;
2. +--------------------------------+----------+
3. | Status                         | Duration |
4. +--------------------------------+----------+
5. | starting                       | 0.000064 |
6. | Executing hook on transaction  | 0.000006 |
7. | starting                       | 0.000008 |
8. | checking permissions           | 0.000006 |
9. | Opening tables                 | 0.000031 |
10. | init                           | 0.000005 |
11. | System lock                    | 0.000010 |
12. | optimizing                     | 0.000004 |
13. | statistics                     | 0.000014 |
14. | preparing                      | 0.000015 |
15. | executing                      | 0.000070 |
16. | end                            | 0.000016 |
17. | query end                      | 0.000004 |
18. | waiting for handler commit     | 0.000008 |
19. | closing tables                 | 0.000007 |
20. | freeing items                  | 0.000020 |
21. | cleaning up                    | 0.000020 |
22. +--------------------------------+----------+
23. 17 rows in set, 1 warning (0.00 sec)

复制代码

如果想要查询 CPU、io 阻塞等参数情况，可以写作：

1. # 查看指定 SQL 的 CPU、io 阻塞等参数情况
2. mysql> show profile cpu,block io for query 1;
3. +--------------------------------+----------+----------+------------+--------------+---------------+
4. | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
5. +--------------------------------+----------+----------+------------+--------------+---------------+
6. | starting                       | 0.000064 | 0.000062 |   0.000000 |            0 |             0 |
7. | Executing hook on transaction  | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
8. | starting                       | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
9. | checking permissions           | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
10. | Opening tables                 | 0.000031 | 0.000021 |   0.000009 |            0 |             0 |
11. | init                           | 0.000005 | 0.000003 |   0.000002 |            0 |             0 |
12. | System lock                    | 0.000010 | 0.000006 |   0.000004 |            0 |             0 |
13. | optimizing                     | 0.000004 | 0.000003 |   0.000001 |            0 |             0 |
14. | statistics                     | 0.000014 | 0.000009 |   0.000005 |            0 |             0 |
15. | preparing                      | 0.000015 | 0.000010 |   0.000005 |            0 |             0 |
16. | executing                      | 0.000070 | 0.000045 |   0.000024 |            0 |             0 |
17. | end                            | 0.000016 | 0.000007 |   0.000004 |            0 |             0 |
18. | query end                      | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
19. | waiting for handler commit     | 0.000008 | 0.000005 |   0.000003 |            0 |             0 |
20. | closing tables                 | 0.000007 | 0.000005 |   0.000002 |            0 |             0 |
21. | freeing items                  | 0.000020 | 0.000013 |   0.000007 |            0 |             0 |
22. | cleaning up                    | 0.000020 | 0.000013 |   0.000007 |            0 |             0 |
23. +--------------------------------+----------+----------+------------+--------------+---------------+
24. 17 rows in set, 1 warning (0.00 sec)

复制代码

---

5.2.2.2 MySQL-5.7 中的 SQL 执行原理

由于 MySQL-5.7 在默认情况下是不利用缓存的

1. # MySQL-5.7 中，在默认情况下 MySQL 也是关闭的
2. mysql>SHOW GLOBAL VARIABLES LIKE '%query_cache_type%';
3. +---------------------------+---------+
4. | Variable_name             | Vaule   |
5. +---------------------------+---------+
6. | Query_cache_type          | OFF     |
7. +--------------------+----------------+
8. 1 rows in set (0.00 sec)

复制代码

所有必要开启查询缓存的设置，在配置文件 my.cnf 中添加以下设置：

1. [mysqld]# query_cache_type 有 3 个值：0 表示关闭查询缓存 OFF，1 代表开启，2 表示 DEMAND
2. query_cache_type=2

复制代码

再重启 MySQL 服务器
这里我 mike_inner 库下有一个 t_access_log 的表，我对于该表进行两次查询操作
示例：

1. mysql> use mike_inner;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> SET profiling = 1;
2. Query OK, 0 rows affected, 1 warning (0.00 sec)
3. mysql> select * from t_access_log;+----+------------+-------------+-----------------+---------------------+-------+| id | login_name | access_path | access_ip       | create_time         | state |+----+------------+-------------+-----------------+---------------------+-------+|  1 | duojiala   | /auth/login | 192.168.110.193 | 2024-04-18 09:15:28 |     0 ||  2 | caodali    | /auth/login | 192.168.110.193 | 2024-04-18 09:16:27 |     1 |+----+------------+-------------+-----------------+---------------------+-------+2 rows in set (0.00 sec)mysql> select * from t_access_log;+----+------------+-------------+-----------------+---------------------+-------+| id | login_name | access_path | access_ip       | create_time         | state |+----+------------+-------------+-----------------+---------------------+-------+|  1 | duojiala   | /auth/login | 192.168.110.193 | 2024-04-18 09:15:28 |     0 ||  2 | caodali    | /auth/login | 192.168.110.193 | 2024-04-18 09:16:27 |     1 |+----+------------+-------------+-----------------+---------------------+-------+2 rows in set (0.00 sec)mysql> show profiles;
4. +----------+------------+----------------------------+| Query_ID | Duration   | Query                      |+----------+------------+----------------------------+|        1 | 0.00030500 | select * from t_access_log ||        2 | 0.00027300 | select * from t_access_log |+----------+------------+----------------------------+2 rows in set, 1 warning (0.00 sec)mysql> show profile for query 1;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000064 || Waiting for query cache lock   | 0.000006 || starting                       | 0.000008 || Checking query cahce for query | 0.000051 || checking permissions           | 0.000006 || Opening tables                 | 0.000031 || init                           | 0.000005 || System lock                    | 0.000010 || Waiting for query cache lock   | 0.000006 || System lock                    | 0.000010 || optimizing                     | 0.000004 || statistics                     | 0.000014 || preparing                      | 0.000015 || executing                      | 0.000070 || Sending data                   | 0.000070 || end                            | 0.000016 || query end                      | 0.000004 || closing tables                 | 0.000004 || freeing items                  | 0.000004 || Waiting for query cache lock   | 0.000006 || freeing items                  | 0.000004 || Waiting for query cache lock   | 0.000006 || freeing items                  | 0.000004 || storing result in query cache  | 0.000020 || cleaning up                    | 0.000020 |+--------------------------------+----------+25 rows in set, 1 warning (0.00 sec)mysql> show profile for query 2;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000066 || Waiting for query cache lock   | 0.000006 || starting                       | 0.000008 || Checking query cahce for query | 0.000051 || Checking privileges on cached  | 0.000051 || checking permissions           | 0.000006 || sending cached result to clien | 0.000006 || cleaning up                    | 0.000010 |+--------------------------------+----------+17 rows in set, 1 warning (0.00 sec)

复制代码

可以看到同样的 SQL，第二次执行会去读缓存，如果缓存中有，就不会再执行之后的流程了

---

5.2.2.3 MySQL-8.0 中的 SQL 执行原理

由于 MySQL-8.0 已经没有缓存了，所有不必要去设置 query_cache_type
这里我 mike_inner 库下有一个 t_access_log 的表，我对于该表进行两次查询操作
示例：

1. mysql> use mike_inner;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> SET profiling = 1;
2. Query OK, 0 rows affected, 1 warning (0.00 sec)
3. mysql> select * from t_access_log;+----+------------+-------------+-----------------+---------------------+-------+| id | login_name | access_path | access_ip       | create_time         | state |+----+------------+-------------+-----------------+---------------------+-------+|  1 | duojiala   | /auth/login | 192.168.110.193 | 2024-04-18 09:15:28 |     0 ||  2 | caodali    | /auth/login | 192.168.110.193 | 2024-04-18 09:16:27 |     1 |+----+------------+-------------+-----------------+---------------------+-------+2 rows in set (0.00 sec)mysql> select * from t_access_log;+----+------------+-------------+-----------------+---------------------+-------+| id | login_name | access_path | access_ip       | create_time         | state |+----+------------+-------------+-----------------+---------------------+-------+|  1 | duojiala   | /auth/login | 192.168.110.193 | 2024-04-18 09:15:28 |     0 ||  2 | caodali    | /auth/login | 192.168.110.193 | 2024-04-18 09:16:27 |     1 |+----+------------+-------------+-----------------+---------------------+-------+2 rows in set (0.00 sec)mysql> show profiles;
4. +----------+------------+----------------------------+| Query_ID | Duration   | Query                      |+----------+------------+----------------------------+|        1 | 0.00030500 | select * from t_access_log ||        2 | 0.00027300 | select * from t_access_log |+----------+------------+----------------------------+2 rows in set, 1 warning (0.00 sec)mysql> show profile for query 1;
5. +--------------------------------+----------+
6. | Status                         | Duration |
7. +--------------------------------+----------+
8. | starting                       | 0.000064 |
9. | Executing hook on transaction  | 0.000006 |
10. | starting                       | 0.000008 |
11. | checking permissions           | 0.000006 |
12. | Opening tables                 | 0.000031 |
13. | init                           | 0.000005 |
14. | System lock                    | 0.000010 |
15. | optimizing                     | 0.000004 |
16. | statistics                     | 0.000014 |
17. | preparing                      | 0.000015 |
18. | executing                      | 0.000070 |
19. | end                            | 0.000016 |
20. | query end                      | 0.000004 |
21. | waiting for handler commit     | 0.000008 |
22. | closing tables                 | 0.000007 |
23. | freeing items                  | 0.000020 |
24. | cleaning up                    | 0.000020 |
25. +--------------------------------+----------+
26. 17 rows in set, 1 warning (0.00 sec)
27. mysql> show profile for query 2;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000066 || Executing hook on transaction  | 0.000004 || starting                       | 0.000008 || checking permissions           | 0.000006 || Opening tables                 | 0.000032 || init                           | 0.000006 || System lock                    | 0.000009 || optimizing                     | 0.000005 || statistics                     | 0.000013 || preparing                      | 0.000015 || executing                      | 0.000068 || end                            | 0.000003 || query end                      | 0.000003 || waiting for handler commit     | 0.000007 || closing tables                 | 0.000007 || freeing items                  | 0.000012 || cleaning up                    | 0.000010 |+--------------------------------+----------+17 rows in set, 1 warning (0.00 sec)

复制代码

可以看出，不同于 MySQL-5.7，同样的 SQL 会走同样的流程，不存在缓存

---

5.2.2.4 SQL 语法顺序

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

• 语法顺序：
  

1. SELECT DISTINCT
2.     <select_list>
3. FROM
4.     <left_table> <join_type>
5. JOIN <right_table> ON <join_condition>
6. WHERE
7.     <where_condition>
8. GROUP BY
9.     <group_by_list>
10. HAVING
11.     <having_condition>
12. ORDER BY
13.     <order_by_condition>
14. LIMIT <limit_number>

复制代码

• 剖析顺序：
  

1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>  -- 这一步和上一步，会循环执行
4. WHERE <where_condition> -- 这一步会循环执行，多个条件从左往右
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT   -- 分组之后才执行SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number> -- 这一步是MySQL独有的语法，前面都是SQL92标准

复制代码

---

5.2.2.5 Oracle 中的 SQL 执行流程

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

从上图可知，SQL 语句在 Oracle 中履历了以下几个步调：
① 语法查抄：查抄 SQL 拼写是否精确，如果不精确，Oracle 会报语法错误
② 语义查抄：查抄 SQL 中的访问对象是否存在，比如我们在写 SELECT 语句的时间，列名写错了，系统就会提示错误。语法查抄和语义查抄的作用是包管 SQL 语句没有错误
③ 权限查抄：看用户是否具备访问该数据的权限
④ 共享池查抄：共享池（Shared Pool）是一块内存池，最紧张的作用是缓存 SQL 语句和该语句的执行计划
   Oracle 通过查抄共享池是否存在 SQL 语句的执行计划，来判断进行软剖析照旧硬剖析。


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


如果没有找到 SQL 语句和执行计划，Oracle 就必要创建剖析树进行剖析，生成执行计划，进入优化器这个步调，这就是 硬剖析。
  ⑤ 优化器：优化器中就是要进行硬剖析，也就是决定怎么做，比如创建剖析树，生成执行计划
   共享池是 Oracle 中的术语，包括了库缓存、数据字典缓冲区等。库缓存 紧张缓存 SQL 语句和执行计划，而 数据字典缓冲区 存储的是 Oracle 中的对象界说，比如表、视图、索引等对象，当对 SQL 语句进行剖析的时间，如果必要相干的数据，会从数据字典缓冲区中提取


库缓存 这一步调决定了 SQL 语句是否必要进行硬剖析，为了提示 SQL 的执行服从，我们应该只管制止硬剖析，由于在 SQL 的执行过程中，创建剖析树、生成执行计划是很消耗资源的


因此，可以通过利用绑定变量来减少硬剖析，减少 Oracle 的剖析工作量，但是这种方式也有缺点，利用动态 SQL 的方式，由于参数不同，会导致 SQL 的执行服从不同，同时 SQL 优化也比力困难
  Oracle 和 MySQL 在进行 SQL 的查询上面有软件实现层面的差异，Oracle 提出了共享池的概念，通过共享池来判断是进行软剖析照旧硬剖析。

---

5.3 数据库缓冲池

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

---

5.3.1 缓冲池 & 查询缓存

• 缓冲池
  

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

从图中，可以看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自顺应 Hash 和数据字典信息等。
缓存池的紧张性：
对于利用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引（包罗聚簇索引和二级索引），照旧各种系统数据，都是以 页 的情势存放在 表空间 中的，而所谓的表空间只不外是 InnoDB 对文件系统上一个或几个现实文件的抽象，也就是说我们的数听说到底照旧存储在磁盘上的。但是磁盘的速率比力满，和 CPU 的高性能是匹配的，但是缓冲池可以消除 CPU 和磁盘之间的鸿沟。所以 InnoDB 存储引擎在处置惩罚客户端的请求时，当必要访问某个页的数据时，就会把 完整的数据全部加载到内存 中，存中后就可以进行读写访问了，在进行读写访问之后并不着急把该页对应的内存空间开释掉，而是将其 缓存 起来，这样将来有请求再次访问该页面时，就可以 省去磁盘 I/O 的开销了。
缓存的原则：
位置 * 频次 这个原则，可以帮我们对 I/O 访问服从进行优化。
起首，位置决定服从，提供缓冲池就是为了在内存中可以直接访问数据，其次，频次决定优先级顺序，由于缓冲池的大小有限，比如磁盘有 200G，但内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先利用频次高的热数据进行加载。
缓冲池的预读特性：
缓冲池的另一个特性就是 预读。缓冲池的作用就是提升 I/O 服从，而我们进行读取数据的时间存在一个局部性原理，也就是说我们利用一些数据，大概率还会利用它周围的一些数据，因此采用预读的机制提前记载，可以减少未来可能的磁盘 I/O 操作。

• 查询缓冲
  

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

---

5.3.2 缓冲池怎样读取数据

缓冲池管理器会只管将经常利用的数据保存起来，在数据库进行页面读操作的时间，起首会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
缓存在数据库中的布局和作用如下图所示：

如果我们执行 SQL 语句的时间更新了缓存池中的数据，那么这些数据会立即同步到磁盘上吗？
现实上，当我们对数据库中的记录进行修改的时间，起首会修改缓冲池中页里面的记录信息，然后数据库会 以肯定的频率革新 到磁盘上。注意并不是每次发生更新操作都会立即进行磁盘回写。缓冲池采用一种叫做 checkpoint 的机制 将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。
比如，当 缓冲池不敷用 时，必要开释掉一些不常用的页，此时就可以强行采用 checkpoint 的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页开释掉。这里脏页（dirty page）指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

---

5.3.3 检察设置缓冲池的大小

如果你利用的是 MySQL MyISAM 存储引擎，它只缓存索引，不缓存数据，对应的健缓存参数为 key_buffer_size，可以用它进行检察。
如果你利用的是 InnoDB 存储引擎，可以通过检察 innodb_buffer_pool_size 变量来检察缓冲池的大小，命令如下：

1. show variables like 'innodb_buffer_pool_size';

复制代码

示例：

1. mysql> show variables like 'innodb_buffer_pool_size';
2. +-------------------------+-----------+| Variable_name           | Value     |+-------------------------+-----------+| innodb_buffer_pool_size | 134217728 |+-------------------------+-----------+1 row in set (0.00 sec)

复制代码

你能看到此时 InnoDB 缓冲池大小有 134217728/1024/1024 = 128MB。我们可以修改缓冲池大小，比如改为：256MB，方法如下：

1. set global innodb_buffer_pool_size = 268435456;

复制代码

或者修改配置文件 my.cnf，添加配置如下：

1. [mysqld]
2. innodb_buffer_pool_size = 268435456

复制代码

改完必要重启数据库才能生效

---

5.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. [mysqld]
2. innodb_buffer_pool_instances = 2

复制代码

这样就表明我们要创建 2 个 Buffer Pool 实例
我们看下怎样检察缓冲池的个数，利用命令：

1. show variables like 'innodb_buffer_pool_instances';

复制代码

示例：

1. mysql> show variables like 'innodb_buffer_pool_instances';
2. +------------------------------+-------+| Variable_name                | Value |+------------------------------+-------+| innodb_buffer_pool_instances | 1     |+------------------------------+-------+1 row in set (0.01 sec)

复制代码

那每个 Buffer pool 实例现实占用多少内存空间呢？实在利用这个公司算出来的：

1. innodb_buffer_pool_size / innodb_buffer_pool_instances

复制代码

也就是总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。
不外也不是说 Buffer Pool 实例创建得越多越好，分别 管理各个 Buffer Pool 也是必要性能开销 的，InnoDB 规定：当 innodb_buffer_pool_size 的值小于 IG 的时间设置多个实例是无效的，InnoDB 会默认把 innodb_buffer_pool_instances 的值修改为 1，而我们鼓励在 Buffer Pool 大于或等于 IG 的时间设置多个 Buffer Pool 实例。

---

5.3.5 引申问题

Buffer Pool 是 MySQL 内存布局中是否核心的一个构成，你可以把它想象成一个黑盒子。
黑河下的更新数据流程
当我们查询数据的时间，会先去 Buffer Pool 中查询。如果 Buffer Pool 中不存在，存储引擎会先将数据从磁盘加载到 Buffer Pool 中，然后将数据返回给客户端；同理，当我们更新某个数据的时间，如果这个数据不存在于 Buffer Pool，同样会先数据加载进来，然后修改修改内存的数据。被修改过的数据会在之后统一刷入磁盘。

这个过程看似没有啥问题，实则是有问题的。假设我们修改 Buffer Pool 中的数据乐成，但是还没来得及将数据刷入磁盘 MySQL 就挂了怎么办？按照上图的逻辑，此时更新之后的数据只存在于 Buffer Pool 中，如果此时 MySQL 宕机了，这部门数据将会永世地丢失。
再者，我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据恒久化的包管、变乱回滚都做不到还谈什么崩溃规复？

---

上篇：第四章、配置文件与系统变量
下篇：第六章、存储引擎

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。