給隔壁的妹子講『一個SQL語句是如何執行的？』
- 2020 年 7 月 24 日
- 筆記
前言
SQL作為Web開發是永遠離開不的一個話題，天天寫SQL，可是你知道一個SQL是如何執行的嗎？
select name from user where id = 1;
上面是一個簡單的查詢語句，交給資料庫去執行，然後返回name。看起來很簡單，可是內部的執行過程卻很多人都不知道。
今天就把MySQL拆開看看，看一下它究竟是怎麼工作的。
SQL基本架構
從上圖可以看出，MySQL分為Server層和存儲引擎層
Server層
連接器
連接器主要是與客戶端建立連接， 包含本地socket和大多數基於客戶端/服務端工具實現的類似於tcp/ip的通訊。 連接成功之後會同時校驗用戶的許可權，等相關安全方案。如我們常用的建立連接方式
mysql -h ip -P 3306 -u root -p
連接是可以在-p後面輸入密碼，但是考慮到安全問題 不建議這樣操作，-P 後是埠號，-p 是密碼。注意大小寫。
登錄成功之後，會校驗當然登錄帳號的許可權。後續所有的資料庫操作都被當前許可權所限制。因此，管理員修改用戶許可權時，不會立即生效，需要重新連接才會生效。
MySQL默認情況下，當一個鏈接空閑超過8（60 * 60 * 8）小時之後會自動斷開連接。但是連接池則以為該被斷開的連接依然有效。 這個時候如果客戶端程式碼發送請求時，連接池會把已經失效的程式碼返回至客戶端。這樣就會導致程式碼異常。
通過show global variables like '%timeout%'可進行查看， 預設情況下是使用wait_timeout 這個欄位。
另外可以用show processlist; 用來顯示用戶正在運行的執行緒。
注意：除了 root 用戶能看到所有正在運行的執行緒外，其他用戶都只能看到自己正在運行的執行緒，看不到其它用戶正在運行的執行緒。除非單獨個這個用戶賦予了PROCESS 許可權。
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 461 | Waiting on empty queue | NULL |
| 13 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
查詢快取
當我們建立連接之後，執行SQL語句時，會先進行快取查詢（如果開啟了快取查詢）。如果之前執行了相同的SQL語句，則會從快取中直接返回結果。 這個過程可以理解為SQL文本和查詢結果的映射。
但是查詢快取真的能提升效率嗎？ 理論上，不建議開啟查詢快取
因為快取和失效都會有額外的資源消耗，數據發生改變或者表結構發生改變時，都會導致快取失效。最差的情況就是你剛建立了一份快取，另外一邊又有人修改數據。這樣導致快取失效，重新建立了一份新的快取。
**有這些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE會導致快取數據失效。所以查詢快取適合有大量相同查詢的應用，不適合有大量數據更新的應用 **
在MySQL8.0的版本刪除了查詢快取的功能。
如果你是8.0之前的版本，可以通過以下方法關閉查詢快取：
1、臨時關閉，直接執行命令行
set global query_cache_size=0
set global query_cache_type=0
2、永久關閉，修改配置文件my.cnf ,添加下面的配置即可。
query_cache_type=0
query_cache_size=0
分析器
當在查詢快取中沒有找到對象的查詢結果時，這時候就需要分析器對SQL進行解析。比如解析出響應的關鍵詞。如Select（查詢）、Delete（刪除）等等，同時也會把相應的表明、欄位名都分析出來。如果SQL語法錯誤，會告訴我們You have an error in your SQL syntax
優化器
SQL實際的執行順序不一定就是我們寫的順序。在通過分析器的解析，資料庫知道了我們要做什麼。然後會按照一定的規則重寫SQL。當有多個索引的時候，優化器也會決定去使用哪個索引；當多表關聯查詢的時候，也會去決定各個表的鏈接順序。總之，優化器會通過一系列的演算法規則去給出一個最優的執行策略。
執行器
SQL通過分析器知道要做什麼，通過優化器知道該怎麼做。最後通過執行器就進入了執行階段。
首先會根據連接的帳號查看是否有操作該表的許可權。如果沒有，則返回許可權錯誤。如果有許可權，則繼續執行。
打開表的時候，執行器會根據表的引擎 去使用該引擎提供的介面。
存儲引擎層
存儲引擎層負責數據的存儲和提取。
可通過show engines查看MySQL的存儲引擎。存儲引擎有InnoDB、MylSAM、MEMORY、MERGE等等..
但是我們常用的基本是InnoDB和MylSAM。
InnoDB在5.5.5版本之後為默認的存儲引擎
InnoDB
InnoDB是一個事務型的存儲引擎，有行級鎖定和外鍵約束,提供了具有提交，回滾和崩潰恢復的事務安全，但是對比MyLSAM引擎，寫的效率會比差一些，並且會佔用更多的磁碟空間以保持數據和索引。
特點:
-
更新多的表，適合處理多重並發的更新請求。
-
支援事務。
-
可以從災難中恢復（通過bin-log日誌等）。
-
外鍵約束。只有他支援外鍵。
-
支援自動增加列屬性auto_increment。
MylSAM
Mylsam 存儲引擎獨立於作業系統，簡單說就是可用在windows上使用，也可用將數據轉移到Lunex作業系統上。系統兼容性很好！！！。這種存儲引擎在建表的時候，它會創建3個文件。分別是（.frm, .MYD, .MYI），簡單說明一下：.frm 存儲表的定義（也就是表結構啦），.MYD 就是表裡面的數據，.MYD存儲索引。這樣的劃分作業系統對大文件的操作是比較慢的，這樣將表分為三個文件，那麼.MYD這個文件單獨來存放數據自然可以優化資料庫的查詢等操作。
特點:
1、不支援事務
2、不支援外鍵
3、查詢速度很快。如果資料庫insert和update的操作比較多的話採用表鎖效率低（建議使用innodb）。
4、對錶進行加鎖
總結
Server層涵蓋了MySQL執行的大多數的核心功能，以及各種各樣的內置函數，比如時間、日期等，不管使用的是什麼存儲引擎，它的Server層是一樣的。以上就是對一個SQL執行流程的簡單介紹。感謝大家的閱讀！
