MYSQL 逻辑架构/执行流程MYSQL 中 SQL 的执行流程 1. MYSQL 的查询流程 1.2 查询缓存 MYS

MYSQL 中 SQL 的执行流程

MysqlProcedure.drawio.xml

1. MYSQL 的查询流程

1.2 查询缓存

MYSQL 服务器如果在查询缓存中发现了相同的 SQL 语句，就会直接将结果返回给客户端，提高了查询的效率。
反之，就进入解析器阶段。

⚠但是查询缓存的效率并不是很高，所以在 MYSQL8.0 之后就被移除了。

1.2.1 为什么移除查询缓存呢？

查询缓存的原理是提前将查询的结果缓存起来，这样下次执行就可以直接拿到结果。不是缓存查询计划，而是查询对应的结果。

只有相同的查询语句才会命中缓存，要求两次查询语句要完全相同，任何字符上的不同（空格、注释、大小写），都不会命中缓存。
```
SELECT * FROM DUAL;
SELECT *  FROM DUAL;  // 无法命中缓存
```
即使字符都相同，如果查询语句中使用了某些函数，也可能无法命中缓存。
1. 比如函数 NOW ，每次调用都会获取新的系统时间，即使查询文本完全相同，但是时间不同也不会命中缓存。
此外，既然是缓存，那么就有存在缓存失效。在 MYSQL 的缓存系统会监控每一张表，只要该表的结构或者数据发生变化，如INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER、TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那么对于更新压力大的数据库来说，查询缓存的命中率会非常低。

1.2 解析器

解析器对 SQL 语句进行语法分析、语义分析。

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

select username, ismale
from userInfo 
where age > 20 and level > 5

语法树.drawio.xml

⚠请注意，实际的语法树可能会更加复杂，并且取决于具体的数据库管理系统（DBMS）和它内部的解析器实现。不同DBMS的解析器可能会有不同的语法树结构和节点命名约定。这个示例仅仅是为了说明概念，并不是从任何特定DBMS中直接生成的。

1.3 优化器

优化器会确定 SQL 语句的执行路径，比如是根据全表检索还是索引检索等。举个例子：执行两个表的 JOIN 操作

select * from test1 
join test2 using(ID)
where 
  test1.name='zhangwei' and 
  test2.name='mysql高级课程';

以下是优化器可能采取的一些具体步骤：

选择连接顺序：
1. 如果表test1和test2的大小不同，优化器可能会决定先对较小的表执行连接操作，以减少中间结果的大小。
选择连接类型：
1. 对于JOIN ... USING操作，优化器可能会选择Nested Loop Join、Hash Join或Merge Join，具体取决于表的统计信息和索引。
应用 WHERE 条件：
1. 优化器会决定何时应用 WHERE 条件。它可能会先过滤掉不符合条件的行，然后再执行连接操作，以减少需要处理的数据量。

1.4 执行器

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

在执行之前需要判断用户是否具备权限
1. 如果没有，就会返回权限错误。
2. 如果具备权限，就执行 SQL 查询并返回结果。
执行查询语句

select * from test where id=1;

如果 id 字段存在索引，就会进行索引检索。
如果没有，就会进行全表检索。

至此，这个一个 SQL 语句就执行完成了。SQL 语句在 MYSQL 的流程是： SQL 语句 - 查询缓存(MYSQL8 以下版本) - 解析器 - 优化器 - 执行器

1.5 存储引擎

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

mysql > show engines;

// Output
Engine            |Support|Comment                                                       |Transactions|XA |Savepoints|
------------------+-------+--------------------------------------------------------------+------------+---+----------+
MEMORY            |YES    |Hash based, stored in memory, useful for temporary tables     |NO          |NO |NO        |
MRG_MYISAM        |YES    |Collection of identical MyISAM tables                         |NO          |NO |NO        |
CSV               |YES    |CSV storage engine                                            |NO          |NO |NO        |
FEDERATED         |NO     |Federated MySQL storage engine                                |            |   |          |
PERFORMANCE_SCHEMA|YES    |Performance Schema                                            |NO          |NO |NO        |
MyISAM            |YES    |MyISAM storage engine                                         |NO          |NO |NO        |
InnoDB            |DEFAULT|Supports transactions, row-level locking, and foreign keys    |YES         |YES|YES       |
ndbinfo           |NO     |MySQL Cluster system information storage engine               |            |   |          |
BLACKHOLE         |YES    |/dev/null storage engine (anything you write to it disappears)|NO          |NO |NO        |
ARCHIVE           |YES    |Archive storage engine                                        |NO          |NO |NO        |
ndbcluster        |NO     |Clustered, fault-tolerant tables                              |            |   |          |

2. MYSQL8 中 SQL 的执行原理

2.1 确认开启 profiling

select @@profiling;

-- Output
@@profiling|
-----------+
          0|
// 或者

show variables like 'profiling';

-- Output
Variable_name|Value|
-------------+-----+
profiling    |OFF  |

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

set profiling=1;

-- Output
Variable_name|Value|
-------------+-----+
profiling    |ON   |

2.2 多次执行相同的 SQL 查询

SELECT * FROM test_table tt;

2.3 查看 profiles

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

--  显示最近的几次查询
show profiles;

-- Output
Query_ID|Duration  |Query                                                                                                      |
--------+----------+-----------------------------------------------------------------------------------------------------------+
      15|  0.000178|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      16| 0.0002515|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      17|0.00015675|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      18|  0.000251|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      19|0.00015725|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      20|  0.000279|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      21|  0.000148|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      22|  0.000339|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      23|0.00015625|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      24|0.00033675|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      25|   0.00014|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      26|0.00044425|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT¶ *¶FROM¶ test_table tt¶¶LIMIT 0, 200|
      27|  0.000163|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |
      28|  0.000103|SHOW WARNINGS                                                                                              |
      29| 0.0001565|/* ApplicationName=DBeaver 24.1.1 - SQLEditor <Script-1.sql> */ SELECT DATABASE()                          |

2.4 查看profile

现实执行计划，查看程序的执行步骤：

show profile;

-- Output
Status              |Duration|
--------------------+--------+
starting            |0.000072|
checking permissions|0.000004|   -- 权限检查
Opening tables      |0.000012|   -- 打开表
init                |0.000004|   -- 初始化
optimizing          |0.000007|   -- 优化
executing           |0.000006|   -- 执行
end                 |0.000002|
query end           |0.000005|
closing tables      |0.000002|
freeing items       |0.000023|
cleaning up         |0.000007|

当然，你也可以根据 QUERY ID 查询执行 SQL 语句的执行流程。

show profile for query 7;

此外，还可以查询更丰富的内容：

show profile cpu,block io for query 29;

-- Output
Status              |Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|
--------------------+--------+--------+----------+------------+-------------+
starting            |0.000072|0.000000|  0.000000|            |             |
checking permissions|0.000004|0.000000|  0.000000|            |             |
Opening tables      |0.000012|0.000000|  0.000000|            |             |
init                |0.000004|0.000000|  0.000000|            |             |
optimizing          |0.000008|0.000000|  0.000000|            |             |
executing           |0.000006|0.000000|  0.000000|            |             |
end                 |0.000002|0.000000|  0.000000|            |             |
query end           |0.000005|0.000000|  0.000000|            |             |
closing tables      |0.000002|0.000000|  0.000000|            |             |
freeing items       |0.000037|0.000000|  0.000000|            |             |
cleaning up         |0.000007|0.000000|  0.000000|            |             |

3. SQL 的语法顺序

SELECT - 指定要返回的列。
FROM - 指定要从中检索数据的表。
JOIN - 指定要连接的表以及连接条件。
WHERE - 指定行的过滤条件。
GROUP BY - 指定分组列。
HAVING - 为分组指定过滤条件。
SELECT (在GROUP BY和HAVING之后，可以指定额外的选择，如聚合函数)
ORDER BY - 指定结果排序的顺序。
LIMIT / OFFSET - 指定返回结果的数量和起始位置。

4. 数据库缓冲池

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

4.1 缓冲池（Buffer Pool）

在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据得缓存。缓冲池.drawio.xml 缓存池得重要性：

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

缓存池得预读性：

缓存池得作用就是提高 I/O 效率，MYSQL 在读取数据得时候存在一个“局部性原理”。 MYSQL 认为在读取了某一块数据页后，认为用户大概率还会使用周围得数据页，因此采用“预读”得机制提前加载到缓存池，可以减少未来 I/O 操作。

4.2 缓存池如何读取数据

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。缓冲池.drawio.xml 如果 SQL 语句更新了缓存池的数据，那么会立马同步到磁盘上吗？

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

缓冲池-1.drawio.xml

当缓存池空间不足时，会释放掉一些不常用的页面。

使用checkpoint 的机制将数据回写到磁盘上
然后释放缓存池中的页面数据。

4.3 查看/设置缓冲池大小

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

show variables like 'innodb_buffer_pool_size';

-- Output
Variable_name          |Value    |
-----------------------+---------+
innodb_buffer_pool_size|134217728|

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

set global innodb_buffer_pool_size = 268435456;

MYSQL 逻辑架构/执行流程