搜尋

首頁  >  文章  >  資料庫  >  深入解析MySQL中SQL的執行流程（圖文結合）

深入解析MySQL中SQL的執行流程（圖文結合）

青灯夜游
青灯夜游轉載
2021-10-14 18:44:513081瀏覽

這篇文章帶大家了解MySQL中SQL的執行流程，看看MySQL 是如何執行一則查詢語句的呢？希望對大家有幫助！

深入解析MySQL中SQL的執行流程（圖文結合）

對於一個開發工程師來說，了解 MySQL 是如何執行一條查詢語句的，我想是非常必要的。 【相關推薦：mysql影片教學】

首先我們要了解MYSQL的體系架構是什麼樣子的？然後再來聊聊一條查詢語句的執行流程是如何？

MYSQL體系結構

先看一張架構圖，如下：

深入解析MySQL中SQL的執行流程（圖文結合）

##模組詳解

  • Connector：用來支援各種語言和SQL 的交互，例如PHP，Python，Java 的JDBC；

  • Management Serveices & Utilities：系統管理與控制工具，包括備份復原、MySQL 複製、叢集等；

  • Connection Pool：連線池，管理需要緩衝的資源，包括使用者密碼權限執行緒等等；

  • SQL Interface：用來接收使用者的SQL 指令，傳回使用者所需的查詢結果；

  • Parser：用來解析SQL 語句；

  • Optimizer：查詢最佳化器；

  • Cache and Buffer：查詢緩存，除了行記錄的快取之外，還有表格緩存，Key 緩存，權限緩存等等；

  • Pluggable Storage Engines：外掛程式儲存引擎，它提供API 給服務層使用，跟特定的檔案打交道。

架構分層

把MySQL 分成三層，跟著客戶端對接的連接層，真正執行操作的服務層，和跟硬體打交道的儲存引擎層。

深入解析MySQL中SQL的執行流程（圖文結合）

連接層

#我們的客戶端要連接到MySQL 伺服器3306 端口，必須跟服務端建立連接，那麼管理所有的連接，驗證客戶端的身份和權限，這些功能就在連接層完成。

服務層

連線層會把SQL 語句交給服務層，這裡面又包含一連串的流程：

例如查詢快取的判斷、根據SQL 呼叫對應的接口，對我們的SQL 語句進行詞法和語法的解析（例如關鍵字怎麼識別，別名怎麼識別，語法有沒有錯誤等等）。

然後就是優化器，MySQL 底層會根據一定的規則對我們的 SQL 語句進行最佳化，最後再交給執行器去執行。

儲存引擎

儲存引擎就是我們的資料真正存放的地方，在 MySQL 裡面支援不同的儲存引擎。再往下就是記憶體或者磁碟。

SQL的執行流程

以一條查詢語句為例，我們來看下 MySQL 的工作流程是什麼樣的。 

select name from user where id=1 and age>20;

首先咱們先來看一張圖，接下來的過程都是基於這張圖來講的：

深入解析MySQL中SQL的執行流程（圖文結合）##連結

程式或工具要操作資料庫，第一步要跟資料庫建立連線。

在資料庫中有兩種連接:

短連線：短連線就是操作完畢以後，馬上 close 掉。
  • 長連線：長連線可以保持打開，減少服務端建立和釋放連線的消耗，後面的程式存取的時候還可以使用這個連線。
  • 建立連線是比較麻煩的，首先要發送請求，發送了請求要去驗證帳號密碼，驗證完了要去看你所擁有的權限，所以在使用過程中，盡量使用長連接。

保持長連線會消耗記憶體。長時間不活動的連接，MySQL 伺服器會斷開。可以使用sql語句查看預設時間：

show global variables like 'wait_timeout';

這個時間是由 wait_timeout 來控制的，預設都是 28800 秒，8 小時。

查詢快取

MySQL 內部自帶了一個快取模組。執行相同的查詢之後我們發現快取沒有生效，為什麼？ MySQL 的快取預設是關閉的。 

show variables like 'query_cache%';

預設關閉的意思就是不建議使用，為什麼 MySQL 不建議使用它自帶的快取呢？

主要是因為MySQL 自帶的快取的應用場景有限：

第一個是它要求SQL 語句必須一模一樣，中間多一個空格，字母大小寫不同都被認為是不同的的SQL。

第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

所以缓存还是交给 ORM 框架（比如 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比如 Redis 来处理更合适。

在 MySQL 8.0 中，查询缓存已经被移除了。

语法解析和预处理

为什么一条 SQL 语句能够被识别呢？假如随便执行一个字符串 hello，服务器报了一个 1064 的错：

[Err] 1064 - 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 'hello' at line 1

这个就是 MySQL 的解析器和预处理模块。

这一步主要做的事情是对语句基于 SQL 语法进行词法和语法分析和语义的解析。

词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。

比如一个简单的 SQL 语句：select name from user where id = 1 and age >20;

深入解析MySQL中SQL的執行流程（圖文結合）

它会将 select 识别出来，这是一个查询语句，接下来会将 user 也识别出来，你是想要在这个表中做查询，然后将 where 后面的条件也识别出来，原来我需要去查找这些内容。

语法分析

语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）。

就比如英语里面的语法 “我用 is ， 你用 are ”这种，如果不对肯定是不可以的，语法分析之后发现你的 SQL 语句不符合规则，就会收到 You hava an error in your SQL syntax 的错误提示。

预处理器

如果写了一个词法和语法都正确的 SQL，但是表名或者字段不存在，会在哪里报错？ 是在数据库的执行层还是解析器？比如：select * from hello;

还是在解析的时候报错，解析 SQL 的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名， 保证没有歧义。预处理之后得到一个新的解析树。

查询优化器

一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的 SQL？

这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是 MySQL 的查询优化器的模块（Optimizer）。 查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选 择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

可以使用这个命令查看查询的开销：

show status like 'Last_query_cost';

MySQL 的优化器能处理哪些优化类型呢？

举两个简单的例子：

1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。

2、有多个索引可以使用的时候，选择哪个索引。

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。但是优化器也不是万能的，并不是再垃圾的 SQL 语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写 SQL 语句的时候还是要注意。

执行计划

优化器最终会把解析树变成一个执行计划（execution_plans），执行计划是一个数据结构。当然，这个执行计划不一定是最优的执行计划，因为 MySQL 也有可能覆盖不到所有的执行计划。

我们怎么查看 MySQL 的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？

MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

EXPLAIN select name from user where id=1;

存储引擎

在介绍存储引擎先来问两个问题：

1、从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面？

2、执行计划在哪里执行？是谁去执行？

存储引擎基本介绍

在关系型数据库里面，数据是放在表 Table 里面的。我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。

在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。为什么要支持这么多存储引擎呢？一种还不够用吗？

在 MySQL 里面，每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。

如何选择存储引擎？

  • 如果对数据一致性要求比较高，需要事务支持，可以选择 InnoDB。

  • 如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。

  • 如果需要一个用于查询的临时表，可以选择 Memory。

  • 如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用 C 语言开发一个存储引擎。（https://dev.mysql.com/doc/internals/en/custom-engine.html%EF%BC%89 ）

执行引擎

谁使用执行计划去操作存储引擎呢？这就是执行引擎（执行器），它利用存储引擎提供的相应的 API 来完成操作。

为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的 API 是相同的。

最后把数据返回给客户端，即使没有结果也要返回。

栗子

还是以上面的sql语句为例，再来梳理一下整个sql执行流程。

select name from user where id = 1 and age >20;

  • 通过连接器查询当前执行者的角色是否有权限，进行查询。如果有的话，就继续往下走，如果没有的话，就会被拒绝掉，同时报出 Access denied for user 的错误信息；

  • 接下来就是去查询缓存，首先看缓存里面有没有，如果有呢，那就没有必要向下走，直接返回给客户端结果就可以了；如果缓存中没有的话，那就去执行语法解析器和预处理模块。（ MySQL 8.0 版本直接将查询缓存的整块功能都给删掉了）

  • 语法解析器和预处理主要是分析sql语句的词法和语法是否正确，没啥问题就会进行下一步，来到查询优化器；

  • 查询优化器就会对sql语句进行一些优化，看哪种方式是最节省开销，就会执行哪种sql语句，上面的sql有两种优化方案：

    • 先查询表 user 中 id 为 1 的人的姓名，然后再从里面找年龄大于 20 岁的。
    • 先查询表 user 中年龄大于 20 岁的所有人，然后再从里面找 id 为 1 的。

  • 优化器决定选择哪个方案之后，执行引擎就去执行了。然后返回给客户端结果。

更多编程相关知识，请访问：编程视频！！

以上是深入解析MySQL中SQL的執行流程（圖文結合）的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文轉載於：juejin.cn。如有侵權，請聯絡admin@php.cn刪除
上一篇：深入解析MySQL中的LIMIT語句下一篇：深入解析MySQL中的LIMIT語句

相關文章

看更多