給隔壁的妹子講『一個SQL語句是如何執行的？』

胖胖胖胖虎阿發表於2020-07-24
SQL

前言

SQL作為Web開發是永遠離開不的一個話題，天天寫SQL，可是你知道一個SQL是如何執行的嗎？

select name from user where id = 1;

上面是一個簡單的查詢語句，交給資料庫去執行，然後返回name。看起來很簡單，可是內部的執行過程卻很多人都不知道。

今天就把MySQL拆開看看，看一下它究竟是怎麼工作的。

SQL基本架構

1595328824023

從上圖可以看出，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 這個欄位。

img

另外可以用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等等..

1595356537614

但是我們常用的基本是InnoDB和MylSAM。

InnoDB在5.5.5版本之後為預設的儲存引擎

InnoDB

InnoDB是一個事務型的儲存引擎，有行級鎖定和外來鍵約束,提供了具有提交，回滾和崩潰恢復的事務安全，但是對比MyLSAM引擎，寫的效率會比差一些，並且會佔用更多的磁碟空間以保持資料和索引。

特點:

  1. 更新多的表，適合處理多重併發的更新請求。

  2. 支援事務。

  3. 可以從災難中恢復（通過bin-log日誌等）。

  4. 外來鍵約束。只有他支援外來鍵。

  5. 支援自動增加列屬性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執行流程的簡單介紹。感謝大家的閱讀！

文末福利

肝了全網，43份Java思維導圖，需要自取！！!

《Java面試手冊》V1.0版本，高清PDF免費獲取

相關文章