【MySQL實戰45講基礎篇】(task1)基礎架構：SQL查詢語句如何執行

學習總結

（1）以一條查詢語句為栗子過一遍執行流程，初步學習MySQL的邏輯架構。

（2）可以從一個有趣的栗子入手：

1）連接配接器：門衛，想進請出示準入憑證（工牌、邀請證明一類）。“你好，你是普通員工，隻能進入辦公大廳，不能到高管區域”此為權限查詢。

2）分析器：“您需要在公司裡面找一張頭發是黑色的桌子？桌子沒有頭發啊！臣妾做不到”。

3）優化器：“要我在A B兩個辦公室找張三和李四啊？那我應該先去B辦公室找李四，然後請李四幫我去A辦公室找張三，因為B辦公室比較近且李四知道張三具體工位在哪”。

4）執行器：“好了，找人的計劃方案定了，開始行動吧，走你！糟糕，剛門衛大哥說了，我沒有權限進B辦公室”。

文章目錄

• ​​學習總結​​
• ​​一、MySQL邏輯架構圖​​

• ​​1.1 Server 層​​
• ​​1.2 存儲引擎層​​

• ​​二、Server層五大部分​​

• ​​2.1 連接配接器​​

• ​​（1）連接配接指令​​
• ​​（2）連接配接後​​
• ​​（3）長連接配接過多導緻記憶體爆炸​​

• ​​2.2 查詢緩存​​

• ​​（1）預設語句使用查詢緩存​​
• ​​（2）确定需要查詢緩存的語句​​

• ​​2.3 分析器​​
• ​​2.4 優化器​​
• ​​2.5 執行器​​

• ​​（1）沒有索引的執行流程​​
• ​​（2）有索引的執行流程​​

• ​​三、作業​​
• ​​四、課後答疑​​
• ​​附基礎篇的大綱​​
• ​​Reference​​
• ​​附：MySQL的語句執行順序​​

• ​​一、sql執行順序​​
• ​​二、mysql的執行順序​​

• ​​1、SELECT語句定義​​
• ​​2、SELECT語句執行順序​​

一、MySQL邏輯架構圖

現在有一個隻有一個ID字段的表，當執行查詢語句

mysql> select * from T where ID=10；      

其中的執行過程是本task要學的。

MySQL 可以分為 Server 層和存儲引擎層兩部分。

1.1 Server 層

Server 層包括連接配接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的内置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實作，比如存儲過程、觸發器、視圖等。

1.2 存儲引擎層

而存儲引擎層負責資料的存儲和提取。其架構模式是插件式的，支援 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了預設存儲引擎。

也就是說，你執行 create table 建表的時候，如果不指定引擎類型，預設使用的就是 InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇别的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用記憶體引擎建立表。不同存儲引擎的表資料存取方式不同，支援的功能也不同。

從上圖中看出，不同的存儲引擎共用一個 Server 層，也就是從連接配接器到執行器的部分。

二、Server層五大部分

2.1 連接配接器

（1）連接配接指令

首先要連接配接資料庫，是以連接配接器的作用：和用戶端建立連接配接、擷取權限、維持和管理連接配接。

連接配接指令的一般寫法如下。然後就是輸入密碼，密碼也可以直接跟在​​

​-p​

​後面（即寫在指令行裡），如果是生産伺服器就别這麼做（不安全）。

mysql -h$ip -P$port -u$user -p      

賬号密碼正确後，連接配接器會到權限表裡面查出你擁有的權限。之後，這個連接配接裡面的權限判斷邏輯，都将依賴于此時讀到的權限（就是說即使你用管理者賬号對這個使用者的權限做了修改，也不會影響已經存在連接配接的權限。修改完成後，隻有再建立的連接配接才會使用新的權限設定）。

（2）連接配接後

連接配接完成後，如果你沒有後續的動作，這個連接配接就處于空閑狀态，​

​show processlist​

​​ 指令可以看到該連接配接（如下圖），第一行的Command處是​

​Sleep​

​就是代表現在系統有一個空閑連接配接。

用戶端如果太長時間沒動靜，連接配接器就會自動将它斷開。這個時間是由參數 ​

​wait_timeout​

​​ 控制的，預設值是 8 小時，超過8h再次發起請求則會報錯​

​Lost connection to MySQL server during query​

​，隻能重連咯。

TIPS：

（1）最好用長連接配接

資料庫裡面，長連接配接是指連接配接成功後，如果用戶端持續有請求，則一直使用同一個連接配接。短連接配接則是指每次執行完很少的幾次查詢就斷開連接配接，下次查詢再重建立立一個。

（2）如果長連接配接累積下來，可能導緻記憶體占用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重新開機了。

（3）長連接配接過多導緻記憶體爆炸

如果全部使用長連接配接後，你可能會發現，有些時候 MySQL 占用記憶體漲得特别快，這是因為 MySQL 在執行過程中臨時使用的記憶體是管理在連接配接對象裡面的。這些資源會在連接配接斷開的時候才釋放。解決方案有：

• 定期斷開長連接配接。使用一段時間，或者程式裡面判斷執行過一個占用記憶體的大查詢後，斷開連接配接，之後要查詢再重連。
• 如果用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行​

  ​mysql_reset_connection​

  ​ 來重新初始化連接配接資源。這個過程不需要重連和重新做權限驗證，但是會将連接配接恢複到剛剛建立完時的狀态。

2.2 查詢緩存

如果查詢命中緩存MySQL不需要執行後面的複雜操作，就可以直接傳回結果，這個效率會很高，但是大多數情況下不建議使用查詢緩存，因為：

1、查詢緩存的失效非常頻繁，隻要有一個表更新，這個表上所有的查詢緩存都被清空

2、對于更新壓力大的資料庫來說，查詢緩存的命中率會非常低，

3、除非你的業務就是有一張靜态表，很長時間才會更新一次(比如一個系統配置表)

（1）預設語句使用查詢緩存

​

​query_cache_type​

​ 設定成 DEMAND

（2）确定需要查詢緩存的語句

用​

​SQL_CACHE​

​ 顯式指定。

mysql> select SQL_CACHE * from T where ID=10；      

注意：MySQL 8.0 版本直接将查詢緩存的整塊功能删掉了，8.0 開始徹底沒有這個功能了。

2.3 分析器

如果沒有命中緩存，則要正真開始執行語句，分析器對SQL語句做解析。首先是《詞法分析》，即識别裡面的字元串分别是啥，并且代表啥。然後是《文法分析》判斷該SQL語句是否合法。如果語句不對則會如下報錯（下面SQL語句是select少了開頭的s字母）。

mysql> elect * from t where ID=1;

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 'elect * from t where ID=1' at line 1      

注意：一般文法錯誤會提示第一個出現錯誤的位置，是以你要關注的是緊接"use naar"的内容。

2.4 優化器

作用：

1、在表裡面有多個索引的時候，決定使用哪個索引

2、多表關聯(join)的時候，決定各個表的連結順序

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;      

• 既可以先從表 t1 裡面取出 c=10 的記錄的 ID 值，再根據 ID 值關聯到表 t2，再判斷 t2 裡面 d 的值是否等于20
• 也可以先從表 t2 裡面取出 d=20 的記錄的 ID 值，再根據 ID 值關聯到 t1，再判斷 t1 裡面 c 的值是否等于10

這兩種執行方法的邏輯結果時一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇哪一個方案

2.5 執行器

開始執行時會判斷使用者是否對該表T有執行查詢的權限（如果沒有權限就傳回如下error；在工程實作上，如果命中查詢緩存，會在查詢緩存傳回結果的時候，做權限驗證。查詢也會在優化器之前調用precheck驗證權限）。

如果有權限，就打開表繼續執行；

打開表時，執行器會根據表的引擎定義，使用該引擎提供的接口。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'      

（1）沒有索引的執行流程

1、調用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是1，如果不是則跳過，如果是則将這行存在結果集中

2、調用引擎接口取"下一行"，重複相同的判斷邏輯，直到取到這個表的最後一行

3、執行器将上述遍布過程中所有滿足條件的行組成的記錄集作為結果集傳回給用戶端。

（2）有索引的執行流程

第一調用的是"取滿足條件的第一行"這個接口，

之後循環取"滿足條件的下一行"這個引擎中的接口。

PS：引擎掃描行數跟​​

​rows_examined​

​并不是完全相同的。

三、作業

如果表 T 中沒有字段 k，而你執行了這個語句 select * from T where k=1, 那肯定是會報“不存在這個列”的錯誤： “​

​Unknown column ‘k’ in ‘where clause​

​’”。你覺得這個錯誤是在我們上面提到的哪個階段報出來的呢？

【答】分析器。

《高性能mysql》裡提到解析器和預處理器。

解析器處理文法和解析查詢, 生成一課對應的解析樹。

預處理器進一步檢查解析樹的合法。比如: 資料表和資料列是否存在, 别名是否有歧義等。如果通過則生成新的解析樹，再送出給優化器。

四、課後答疑

（1）為什麼對權限的檢查不在優化器之前做？

【答】有些時候，SQL語句要操作的表不隻是SQL字面上那些。比如如果有個觸發器，得在執行器階段（過程中）才能确定。優化器階段前是無能為力的。

（2）我建立了一個沒有select權限的使用者，執行select * from T where k=1，報錯“select command denied”，并沒有報錯“unknown column”，是不是可以說明是在打開表之後才判斷讀取的列不存在？

【答】這個是一個安全方面的考慮。你想想一個使用者如果沒有檢視這個表的權限，你是會告訴他字段不對還是沒權限？如果告訴他字段不對，其實給的資訊太多了，因為沒權限的意思還包含了：沒權限知道字段是否存在。

（3）Trace 日志是個好東西：

源碼安裝完MySQL之後,使用Debug模式啟動

mysqld --debug --console &後，

mysql> create database wxb;

Query OK, 1 row affected (0.01 sec)

mysql> use wxb;

Database changed

mysql> create table t(a int);

Query OK, 0 rows affected (0.01 sec)

mysql> select * from t where k=1;

ERROR 1054 (42S22): Unknown column ‘k’ in ‘where clause’

T@4: | | | | | | | | | error: error: 1054 message: ‘Unknown column ‘k’ in ‘where clause’’

Complete optimizer trace:

答案就很清楚了。

附基礎篇的大綱

Reference

（1）《MySQL實戰45講》 （2）

附：MySQL的語句執行順序

PS：這部分轉自java知路公衆号。

今天遇到一個問題就是mysql中insert into 和update以及delete語句中能使用as别名嗎？目前還在檢視，但是在查閱資料時發現了一些有益的知識，給大家分享一下，就是關于sql以及MySQL語句執行順序：

sql和mysql執行順序，發現内部機制是一樣的。最大差別是在别名的引用上。

一、sql執行順序

from 
join 
on 
where 
group by(開始使用select中的别名，後面的語句中都可以使用)
 avg,sum.... 
having 
select 
distinct 
order by
limit      

從這個順序中我們不難發現，所有的 查詢語句都是從from開始執行的，在執行過程中，每個步驟都會為下一個步驟生成一個虛拟表，這個虛拟表将作為下一個執行步驟的輸入。

第一步：首先對​

​from​

​子句中的前兩個表執行一個笛卡爾乘積，此時生成虛拟表 vt1（選擇相對小的表做基礎表）。

第二步：接下來便是應用​

​on​

​篩選器，on 中的邏輯表達式将應用到 vt1 中的各個行，篩選出滿足on邏輯表達式的行，生成虛拟表 vt2 。

第三步：如果是​

​outer join​

​ 那麼這一步就将添加外部行，left outer jion 就把左表在第二步中過濾的添加進來，如果是right outer join 那麼就将右表在第二步中過濾掉的行添加進來，這樣生成虛拟表 vt3 。

第四步：如果 ​

​from​

​ 子句中的表數目多餘兩個表，那麼就将vt3和第三個表連接配接進而計算笛卡爾乘積，生成虛拟表，該過程就是一個重複1-3的步驟，最終得到一個新的虛拟表 vt3。

第五步：應用​

​where​

​篩選器，對上一步生産的虛拟表引用where篩選器，生成虛拟表vt4，在這有個比較重要的細節不得不說一下，對于包含outer join子句的查詢，就有一個讓人感到困惑的問題，到底在on篩選器還是用where篩選器指定邏輯表達式呢？on和where的最大差別在于，如果在on應用邏輯表達式那麼在第三步outer join中還可以把移除的行再次添加回來，而where的移除的最終的。舉個簡單的例子，有一個學生表（班級,姓名）和一個成績表(姓名,成績)，我現在需要傳回一個x班級的全體同學的成績，但是這個班級有幾個學生缺考，也就是說在成績表中沒有記錄。為了得到我們預期的結果我們就需要在on子句指定學生和成績表的關系（學生.姓名=成績.姓名）那麼我們是否發現在執行第二步的時候，對于沒有參加考試的學生記錄就不會出現在vt2中，因為他們被on的邏輯表達式過濾掉了,但是我們用left outer join就可以把左表（學生）中沒有參加考試的學生找回來，因為我們想傳回的是x班級的所有學生，如果在on中應用學生.班級='x’的話，left outer join會把x班級的所有學生記錄找回（感謝網友康欽謀__康欽苗的指正），是以隻能在where篩選器中應用學生.班級=‘x’ 因為它的過濾是最終的。

第六步：​

​group by​

​ 子句将中的唯一的值組合成為一組，得到虛拟表vt5。如果應用了group by，那麼後面的所有步驟都隻能得到的vt5的列或者是聚合函數（count、sum、avg等）。原因在于最終的結果集中隻為每個組包含一行。這一點請牢記。

第七步：應用​

​cube​

​​或者​

​rollup​

​選項，為vt5生成超組，生成vt6.

第八步：應用​

​having​

​篩選器，生成vt7。having篩選器是第一個也是為唯一一個應用到已分組資料的篩選器。

第九步：處理​

​select​

​子句。将vt7中的在select中出現的列篩選出來。生成vt8.

第十步：應用​

​distinct​

​子句，vt8中移除相同的行，生成vt9。事實上如果應用了group by子句那麼distinct是多餘的，原因同樣在于，分組的時候是将列中唯一的值分成一組，同時隻為每一組傳回一行記錄，那麼是以的記錄都将是不相同的。

第十一步：應用​

​order by​

​子句。按照order_by_condition排序vt9，此時傳回的一個遊标，而不是虛拟表。sql是基于集合的理論的，集合不會預先對他的行排序，它隻是成員的邏輯集合，成員的順序是無關緊要的。對表進行排序的查詢可以傳回一個對象，這個對象包含特定的實體順序的邏輯組織。這個對象就叫遊标。正因為傳回值是遊标，那麼使用order by 子句查詢不能應用于表表達式。排序是很需要成本的，除非你必須要排序，否則最好不要指定order by，最後，在這一步中是第一個也是唯一一個可以使用select清單中别名的步驟。

第十二步：應用top選項。此時才傳回結果給請求者即使用者。

二、mysql的執行順序

1、SELECT語句定義

一個完成的SELECT語句包含可選的幾個子句。SELECT語句的定義如下：

SQL代碼

<SELECT clause> [<FROM clause>] [<WHERE clause>] 
[<GROUP BY clause>] [<HAVING clause>] 
[<ORDER BY clause>] [<LIMIT clause>]      

SELECT子句是必選的，其它子句如WHERE子句、GROUP BY子句等是可選的。

一個SELECT語句中，子句的順序是固定的。例如GROUP BY子句不會位于WHERE子句的前面。

2、SELECT語句執行順序

SELECT語句中子句的執行順序與SELECT語句中子句的輸入順序是不一樣的，是以并不是從SELECT子句開始執行的，而是按照下面的順序執行：

開始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最終結果

每個子句執行後都會産生一個中間結果，供接下來的子句使用，如果不存在某個子句，就跳過

對比了一下，mysql和sql執行順序基本是一樣的, 标準順序的 SQL 語句為:

select 考生姓名, max(總成績) as max總成績 
from tb_Grade 
where 考生姓名 is not null 
group by 考生姓名 
having max(總成績) > 600 
order by      

在上面的示例中 SQL 語句的執行順序如下:

(1). 首先執行 FROM 子句, 從 tb_Grade 表組裝資料源的資料

(2). 執行 WHERE 子句, 篩選 tb_Grade 表中所有資料不為 NULL 的資料