MySQL(一)SQL执行流程与MySQL架构
目錄
MySQL語句的執行流程
1.通信協議
1.1通信協議
1.2 通信方式
2.查詢緩存
3.語法解析和預處理
3.1 詞法解析
3.2?語法解析
3.3 預處理器
4.查詢優化（Query Optimizer）與執行計劃
5.存儲引擎
6.?執行引擎，返回結果
MySQL體系結構
MySQL語句的執行流程
當向MySQL發送一條SQL請求的時候，MySQL到底進行了如下步驟:
- 客戶端與服務器建立連接，發送語句。
- 服務器先查詢緩存，如果命中了緩存，則立刻返回緩存中的結果。
- 如果沒有緩存，則服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。
- MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行。
- 將結果返回給客戶端。
具體過程如下
1.通信協議
首先，MySQL必須要運行一個服務，監聽默認的3306端口
1.1通信協議
MySQL是支持多種通信協議的，可以使用同步/異步的方式，支持長連接/短連接
通信類型：同步或者異步
同步通信的特點:
1.同步通信依賴于被調用方，受限于被調用方的性能。也就是說，應用操作數據庫，線程會阻塞，需要等待數據庫的返回。
2.一般只能做到一對一，很難做到一對多的通信。
異步跟同步相反：
1.異步可以避免應用阻塞等待，但是SQL執行的時間不能節省
2.如果異步存在并發，每一個SQL的執行都要單獨建立一個連接，避免數據混亂。
但是這樣會給服務端帶來巨大的壓力（一個連接就會創建一個線程，線程間切換會占用大量CPU資源）。另外異步通信還帶來了編碼的復雜度，所以一般不建議使用。如果要使用異步，必須使用連接池，排隊從連接池獲取連接而不是每次創建新連接。
一般來說我們連接數據庫都是使用的同步連接。
連接方式：長連接或者短連接
MySQL既支持短連接，也支持長連接。短連接就是操作完畢以后，馬上關掉連接，長連接可以保持打開，減少服務端創建和釋放連接的消耗，后面的程序訪問的時候還可以使用這個連接。一般我們會在連接池中使用長連接。
保持長連接會消耗內存。長時間不活動的連接，MySQL服務器會斷開
show global variables like 'wait_timeout'; -- 非交互式超時時間，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超時時間，如數據庫工具
一些常見的狀態: https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
MySQL以下兩種通信協議:
1.Unix Socket
比如我們在Linux服務器上，如果沒有指定-h參數，它就用socket方式登錄（省略了-S /var/lib/mysql/mysql.sock）。
不用通過網絡協議，也可以連接到MySQL的服務器，它需要用到服務器上的一個物理文件（/var/lib/mysql/mysql.sock）。
2.如果指定-h參數，就會用第二種方式，TCP/IP協議
mysql -h 192.168.56.10?-u root -p 123456
我們的編程語言的連接模塊都是用 TCP 協議連接到 MySQL 服務器的，比如mysql-connector-java-x.x.xx.jar。
1.2 通信方式
?mysql客戶端和服務器之間的通訊協議是“半雙工”的，這意味著，在任何一個時刻，要么由服務器向客戶端發送數據，要么由客戶端向服務器發送數據，這兩個動作不能同時發生。這種協議讓mysql通信簡單快速，但也限制了mysql。一個明顯的限制是，這意味著沒辦法進行流量限制。一旦一端開始發生消息，另一端要接收完整個消息才能響應。
如果SQL語句里的內容非常多，比方說一個批量的插入語句，這個時候我們就要調整MySQL服務器配置max_allowed_packet參數的值（默認是4M），把它調大，否則就會報錯。
另一方面，對于服務端來說，也是一次性發送所有的數據，不能因為你已經取到了想要的數據就中斷操作，這個時候會對網絡和內存產生大量消耗。
所以，我們一定要在程序里面避免不帶limit 的這種操作，比如一次把所有滿足條件的數據全部查出來，一定要先count一下。如果數據量大的話，可以分批查詢。
2.查詢緩存
MySQL內部自帶了一個緩存模塊。
MySQL的緩存默認是關閉的。因為MySQL的緩存是不推薦使用，主要是因為MySQL自帶的緩存的應用場景有限。
第一個是它要求SQL語句必須一模一樣，中間多一個空格，字母大小寫不同都被認為是不同的的SQL。
第二個是表里面任何一條數據發生變化的時候，這張表所有緩存都會失效，所以對于有大量數據更新的應用，也不適合
3.語法解析和預處理
這一步主要做的事情是對語句基于SQL語法進行詞法和語法分析和語義的解析。
3.1 詞法解析
詞法分析就是把一個完整的SQL語句打碎成一個個的單詞。
比如一個SQL:select name from user where id=1;會打碎成8個詞語，每個詞語從哪里開始到哪里結束
3.2?語法解析
語法分析會對SQL做一些語法檢查，比如單引號有沒有閉合，然后根據MySQL定義的語法規則，根據SQL語句生成一個數據結構-----解析樹
3.3 預處理器
如果執行一個詞法和語法都正確的SQL，但是表名或者字段不存在，預處理器會幫助我們判斷這些錯誤，直接返回
它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。
預處理之后得到一個新的解析樹
4.查詢優化（Query Optimizer）與執行計劃
查詢優化器會將解析樹轉化成執行計劃。一條查詢可以有多種執行方法，最后都是返回相同結果。優化器的作用就是找到這其中最好的執行計劃。
MySQL里面使用的是基于開銷（cost）的優化器，那種執行計劃開銷最小，就用哪種。
比方說:
當我們對多張表進行關聯查詢的時候，以哪個表的數據作為基準表;當有多個索引可以使用的時候，選擇哪個索引。
優化器最終會把解析樹變成一個查詢執行計劃，查詢執行計劃是一個數據結構。
當然，這個執行計劃也不一定是最優的執行計劃，因為MySQL也有可能覆蓋不到所有的執行計劃
我們在SQL語句前面加上EXPLAIN，就可以看到執行計劃的信息。
EXPLAIN select name from user where id=1;
注意Explain的結果也不一定最終執行的方式。
5.存儲引擎
在關系型數據庫里面，數據是放表Table里面的
我們可以把這個表理解成Excel電子表格的形式。所以我們的表在存儲數據的同時，還要組織數據的存儲結構，這個存儲結構就是由我們的存儲引擎決定的，所以我們也可以把存儲引擎叫做表類型
在MySQL里面，支持多種存儲引擎，他們是可以替換的，所以叫做插件式的存儲引擎
我們創建的每一張表都可以指定它的存儲引擎，存儲引擎的使用是以表為單位的。而且，創建表之后還可以修改存儲引擎
通過如下命令我們可以知道數據庫的數據是存儲在什么目錄下的
每個數據庫有一個自己文件夾，任何一個存儲引擎都有一個frm文件，這個是表結構定義文件。不同的存儲引擎存放數據的方式不一樣，產生的文件也不一樣，innodb 是 1 個，memory沒有，myisam是兩個。
MyISAM 和InnoDB 是我們用得最多的兩個存儲引擎，在 MySQL 5.5.5 版本之前，默認的存儲引擎是MyISAM，它是MySQL自帶的。5.5.5版本之后默認的存儲引擎改成了InnoDB，它是第三方公司為MySQL開發的。
修改默認的存儲引擎的最主要原因還是因為InnoDB 支持事務，支持行級別的鎖，對于業務一致性要求高的場景來說更適合
不同的存儲引擎的特性:https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
MyISAM（3 個文件）
Thesetableshaveasmallfootprint.Table-levellockinglimitstheperformanceinread/writeworkloads,soitisoftenused inread-onlyorread-mostlyworkloadsinWebanddatawarehousingconfigurations.?
特點:
支持表級別的鎖（插入和更新會鎖表）。不支持事務。
擁有較高的插入（insert）和查詢（select）速度。
存儲了表的行數（count速度更快）。
InnoDB（2 個文件）
特點:
支持事務，支持外鍵，因此數據的完整性、一致性更高。
支持行級別的鎖和表級別的鎖。
支持讀寫并發，寫不阻塞讀（MVCC）。
特殊的索引存放方式，可以減少IO，提升查詢效率。
適合：經常更新的表，存在并發讀寫或者有事務處理的業務系統。
Memory（1 個文件）
Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for hugedistributeddatasets.?
把數據放在內存里面，讀寫的速度很快，但是數據庫重啟或者崩潰，數據會全部消失。只適合做臨時表。
CSV（3 個文件）
Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
它的表實際上是帶有逗號分隔值的文本文件。因為csv 表沒有索引，所以通常在正常操作期間將數據保存在innodb表中，并且只在導入或導出階段使用csv表。
特點：
不允許空行，不支持索引。格式通用，可以直接編輯，適合在不同數據庫之間導入導出。
Archive（2 個文件）
These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived,orsecurityauditinformation.?
這些緊湊的未使用索引的表用于存儲和檢索大量很少引用的歷史、存檔或安全審計信息。
特點：不支持索引，不支持update delete
存儲引擎的選擇:
如果對數據一致性要求比較高，需要事務支持，可以選擇InnoDB。
如果數據查詢多更新少，對查詢性能要求比較高，可以選擇MyISAM。
如果需要一個用于查詢的臨時表，可以選擇Memory。
6.?執行引擎，返回結果
我們的執行引擎使用執行計劃去操作存儲引擎，它利用存儲引擎提供的相應API來完成操作。
不同功能的存儲引擎實現的API是相同的
最后把數據返回給客戶端，即使沒有結果也要返回。
MySQL體系結構
1.Connector：用來支持各種語言和SQL的交互，比如PHP，Python，Java的JDBC等；
2.Management Serveices & Utilities：系統管理和控制工具，包括備份恢復、MySQL復制、集群等；
3.Connection Pool：連接池，管理需要緩沖的資源，包括用戶密碼，權限，線程等等；
4.SQL Interface：用來接收用戶的SQL命令，返回用戶需要的查詢結果
5.Parser：用來解析SQL語句；
6.Optimizer：查詢優化器；
7.Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key緩存，權限緩存等等；
8.Pluggable Storage Engines：插件式存儲引擎，它提供API給服務層使用，跟具體的存儲文件打交道
總體上，我們可以把MySQL分成三層，跟客戶端對接的連接層，真正執行操作的服務層，和跟硬件打交道的存儲引擎層
連接層
客戶端要連接到MySQL服務器3306端口，必須要跟服務端建立連接，那么管理所有的連接，驗證客戶端的身份和權限，這些功能就在連接層完成。
服務層
連接層會把SQL語句交給服務層，這里面又包含一系列的流程：比如查詢緩存、根據SQL調用相應的接口，對我們的SQL語句進行詞法和語法的解析（比如關鍵字，別名怎么識別，語法有沒有錯誤等）。然后就是優化器，MySQL底層會根據一定的規則對我們的 SQL語句進行優化，最后再交給執行器去執行
存儲引擎
存儲引擎就是我們的數據真正存放的地方，在MySQL里面支持不同的存儲引擎。再往下就是內存或者磁盤。
?
總結
以上是生活随笔為你收集整理的MySQL(一)SQL执行流程与MySQL架构的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： kubeadm reset后安装遇到的错
- 下一篇： MySQL(三)MySQL索引原理