給公司妹子講了好久,頭都大了,一個SQL語句是如何執行的?

Java資料搬運工發表於2020-10-08

前言

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

select name from user where id = 1;

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

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

SQL基本架構

給公司妹子講了好久,頭都大了,一個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 這個欄位。

給公司妹子講了好久,頭都大了,一個SQL語句是如何執行的?

 

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

給公司妹子講了好久,頭都大了,一個SQL語句是如何執行的?

 

但是我們常用的基本是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執行流程的簡單介紹。感謝大家的閱讀!

相關文章