MySql執(zhí)行流程與生命周期詳解
我們經(jīng)常會在一起討論怎樣優(yōu)化 SQL、怎樣優(yōu)化數(shù)據(jù)庫,那么請問你知道執(zhí)行一條 SQL 的過程中都經(jīng)過了哪些動作嗎?如果不知道的話,又怎么去優(yōu)化 SQL、優(yōu)化數(shù)據(jù)庫呢?為了更好地優(yōu)化 MySQL 數(shù)據(jù)庫,接下來我們就來討論一下執(zhí)行一條 SQL 都經(jīng)過哪些步驟。
為了方便理解,在介紹 SQL 執(zhí)行流程之前,我們先通過一張圖,來詳細闡述一下 SQL 執(zhí)行流程。
由上圖,可以得出:在 MySQL 中,我們大致可以分為三個部分,分別是MySQL 客戶端、MySQL 服務端和存儲層。其中,存儲層主要是硬件層面,不在今天討論的范圍內(nèi),所以這里我們主要討論前兩者。
一、MySQL 客戶端
我們應該都聽說過 MySQL 數(shù)據(jù)庫支持很多編程語言的 API 接口,其實這句話底層的含義是很多編程語言是內(nèi)置 MySQL 客戶端。除此之外,MySQL 還有一個常用的客戶端就是 MySQL 數(shù)據(jù)庫自帶的一個 mysql 命令,這個命令使用如下:
$ mysql -u$username -p$password -h$host -P$port
備注:
- username 是用戶名
- password 是密碼
- host 是 MySQL 服務端地址
- port 是 MySQL 服務端端口
復制代碼
一條 SQL 執(zhí)行的第一步是由 MySQL 客戶端發(fā)送到 MySQL 服務端。在這個步驟中直接影響數(shù)據(jù)速度的是網(wǎng)絡,所以,數(shù)據(jù)庫服務端和客戶端之間最好要有良好的網(wǎng)絡環(huán)境。不過,網(wǎng)絡不屬于本次討論的重點,這里不做過多的討論。我們重點討論一下 MySQL 中的各個模塊對于數(shù)據(jù)庫速度的影響。
嚴格來說,MySQL 連接池屬于 MySQL 服務端的模塊,因其跟 MySQL 客戶端關系密切,我將其拿到 MySQL 客戶端中來介紹。
MySQL 客戶端成功連接 MySQL 服務端之后,MySQL 服務端的連接池會對客戶端的連接進行權限驗證,當權限驗證通過之后,MySQL 服務端會將客戶端的鏈接記錄在服務端的連接池中,之后的各種操作將不再進行權限認證。
這里你肯定會有疑問:為什么 MySQL 要增加一個連接池模塊呢?直接連接 MySQL 服務端不是更簡單且高效嗎?下面我們就來分析一下。
每一次操作數(shù)據(jù)庫,都需要有一個 MySQL 客戶端和服務端之間的鏈接,創(chuàng)建一個鏈接就會有一定的時間消耗;當在高并發(fā)的情況下,每一次訪問數(shù)據(jù)都會創(chuàng)建一個鏈接,這樣就會持續(xù)創(chuàng)建很多重復的數(shù)據(jù)庫鏈接,很沒有必要而且大量的創(chuàng)建鏈接可能會導致數(shù)據(jù)庫內(nèi)存溢出等問題。
為了解決這個問題,MySQL 數(shù)據(jù)庫就提供了一個鏈接池模塊,主要用來保存和安全校驗連接過來的客戶端,當一個客戶端連接成功之后,并不會立即釋放該連接,而是會將該鏈接保存在連接池中,這樣下一次同一個客戶端連接過來就不需要再重復創(chuàng)建連接和安全驗證了,可以在很大程度上降低數(shù)據(jù)庫的資源消耗并且減少連接數(shù)據(jù)的延時。
二、MySQL 服務端
在 MySQL 服務端中,SQL 執(zhí)行過程中是需要經(jīng)過很多模塊的,其中比較重要的模塊是緩存、SQL 解析器、查詢優(yōu)化器以及 SQL 執(zhí)行器等模塊。下面我來詳細介紹一下。
1. 查詢緩存
在 MySQL 數(shù)據(jù)庫中,如果開啟了緩存查詢,每一次的查詢都會在緩存器中以 KV 形式緩存一份。一條 SQL 在執(zhí)行過程中,如果命中了緩存,就會跳過 SQL 解析器、查詢優(yōu)化器以及 SQL 執(zhí)行器,并且立即返回數(shù)據(jù),這樣做的目的主要是提高數(shù)據(jù)庫的性能。
其實 MySQL 數(shù)據(jù)庫是將緩存以哈希的形式保存在內(nèi)存中的一個引用表中,并且把本次查詢的 SQL、數(shù)據(jù)庫名稱以及協(xié)議的 hash 值作為 key,這樣做的主要目的是下一次同一個查詢過來之后可以直接命中查詢。
不過緩存并不是永恒不變的,也會失效,我總結了以下幾個緩存失效的情況:
- 該條緩存對應的數(shù)據(jù)、數(shù)據(jù)表發(fā)生變化時,緩存就會自動失效;
- 查詢過程中有變化的數(shù)據(jù)時,是不會創(chuàng)建緩存的,例如 now()。
所以,在使用 MySQL 數(shù)據(jù)庫查詢的時候,要盡量避免更改數(shù)據(jù)和使用有變化的數(shù)據(jù)。
那么,既然緩存能夠提高 MySQL 數(shù)據(jù)的性能,應該怎么設置開啟緩存呢?首先,我們可以使用SHOW VARIABLES LIKE '%query_cache%';
來查詢緩存的配置項:
-- 查詢緩存配置項 mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.01 sec)
其中,query_cache_type有三個選項,分別是:
- OFF(0):關閉緩存。
- ON(1):開啟緩存。
- DEMAND(2):按需開啟緩存,加上 SQL_CACHE 關鍵字才會緩存。
所以,要開啟查詢緩存可以在 MySQL 數(shù)據(jù)庫的配置文件 my.cnf 中添加query_cache_type = 1
即可。如果需要使用 DEMAND 的話,就需要配置成query_cache_type = 2
。如果需要緩存的話,就需要增加SQL_CACHE
關鍵字,具體操作如下:
mysql> select SQL_CACHE * from info; +----+--------------+ | id | name | +----+--------------+ | 1 | 小仙女 | | 2 | 小帥哥 | | 3 | 鐵錘妹妹 | +----+--------------+ 3 rows in set, 1 warning (0.00 sec)
2. SQL 解析器
當沒有命中緩存時,這個時候 MySQL 數(shù)據(jù)庫就得去查詢數(shù)據(jù)了。在查詢之前必須解析客戶端發(fā)送過來的以一系列字符串和空格組成的 SQL,此時就必須用到 MySQL 數(shù)據(jù)庫中的另一個模塊:SQL 解析器。
SQL 解析器的主要功能是解析客戶端發(fā)送過來的 SQL,就比如匹配到 SQL 中是以 select 開頭的,那就可以認定其為查詢語句;以 insert 開頭的 SQL,就可以認定其為插入語句。但如果在判斷的過程中,出現(xiàn)了匹配不到的這種情況,就會報出ERROR 1064 (42000): You have an error in your SQL syntax;
的錯誤。具體如下:
mysql> elect * from info; 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 info' at line 1
一般情況下,具體的錯誤信息都是在最后邊的單引號內(nèi),具體問題具體對待。
3. 查詢優(yōu)化器
查詢優(yōu)化器,顧名思義就是優(yōu)化 SQL 的執(zhí)行效率,也就是說查詢優(yōu)化器的具體功能是為了找到 SQL 的最佳執(zhí)行方案。
在 MySQL 數(shù)據(jù)庫中的查詢優(yōu)化器優(yōu)化 SQL 具體有兩個方面,分別是邏輯層面和物理層面。
物理層面主要是跟硬件有關,很難通過邏輯去優(yōu)化,所以這里我們從邏輯層面說明一下。
邏輯層面的優(yōu)化主要有命中索引優(yōu)化、順序優(yōu)化、排序優(yōu)化等。例如連表查詢,具體如下:
mysql> select * from province inner join city on city.fid = province.id where province.id = 1; +----+-----------+----+--------------+------+ | id | name | id | name | fid | +----+-----------+----+--------------+------+ | 1 | 上海市 | 1 | 徐匯區(qū) | 1 | | 1 | 上海市 | 2 | 浦東新區(qū) | 1 | +----+-----------+----+--------------+------+ 2 rows in set (0.00 sec)
上面的 SQL 連接 province 和 city 兩個數(shù)據(jù)表,在內(nèi)存中,有如下兩種情況。
- 第一種情況:首先查詢 province 表中 id 為 1 的數(shù)據(jù),然后再查詢 city 表中 fid 為 1 的數(shù)據(jù)。
- 第二種情況:首先查詢 city 表中的所有數(shù)據(jù),然后再判斷 city 表中的 fid 是否等于 1。
這兩種情況的結果是一樣的,這個時候查詢優(yōu)化器內(nèi)部通過算法的方式判斷哪個方案的效率更高,進而選擇哪個方案。
4. SQL 執(zhí)行器
當分析完 SQL 并且選擇合適的方案之后,就開始執(zhí)行 SQL 了,執(zhí)行 SQL 就需要使用 MySQL 數(shù)據(jù)庫提供的 SQL 執(zhí)行器模塊。
SQL 執(zhí)行模塊首先會判斷當前用戶是否對該表有相關的操作權限(如果命中了緩存,將會在返回緩存數(shù)據(jù)之前進行權限認證)。權限判斷通過之后才會調用存儲引擎去操作對應的數(shù)據(jù)表,然后將操作的結果返回。
總結
在這篇文章中,我們按照 SQL 的執(zhí)行順序介紹了一條 SQL 從客戶端到返回數(shù)據(jù)期間經(jīng)過的各個模塊。這里我簡單將各個要點匯總一下。
- MySQL 客戶端主要是用來將 SQL 發(fā)送至服務端的一個模塊。
- 連接池主要是用來保存成功連接 MySQL 服務端的鏈接的,這樣做的好處是可以防止數(shù)據(jù)庫連接短時間內(nèi)不斷重復創(chuàng)建,減少了資源浪費,提高了數(shù)據(jù)庫的訪問速度。
- 數(shù)據(jù)庫緩存模塊是將之前訪問過的數(shù)據(jù)保存在內(nèi)存中,這樣做的好處是避免相同的一個任務重復執(zhí)行的問題,可以提高數(shù)據(jù)庫速度,并且也可以降低數(shù)據(jù)庫資源消耗。
- SQL 解析器主要是用于解析 SQL 的,搞清楚這條 SQL 的具體目的——查詢還是更新數(shù)據(jù)。
- SQL 優(yōu)化器是通過內(nèi)部的算法選擇執(zhí)行這條 SQL 效率最高的方案。
- 搞清了 SQL 的具體操作,也選擇了最優(yōu)的執(zhí)行方案,最后就開始執(zhí)行 SQL,SQL 執(zhí)行器的主要作用除了調用存儲引擎接口獲取數(shù)據(jù)之外,還有權限認證的作用。
至此,一條 SQL 的生命周期就結束了。
在實際應用中,我建議 SQL 緩存最好設置成按需開啟(默認是關閉狀態(tài)) ,原因是 MySQL 中的某一個表更新會導致與這個表相關的所有緩存全部失效,這樣更新數(shù)據(jù)庫時的開銷就會額外增加很多。
到此這篇關于MySql執(zhí)行流程與生命周期詳解的文章就介紹到這了,更多相關MySql生命周期內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
mysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例
下面小編就為大家?guī)硪黄猰ysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2017-04-04MySQL實戰(zhàn)教程之Join語句執(zhí)行流程
這篇文章主要介紹了MySQL Join語句執(zhí)行流程,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-03-03MySQL 索引的優(yōu)缺點以及創(chuàng)建索引的準則
這篇文章主要介紹了MySQL 索引的優(yōu)劣以及創(chuàng)建索引的準則,幫助大家更好的理解和使用MySQL 索引,感興趣的朋友可以了解下2020-09-09MySQL 配置免密碼登錄的問題記錄(mysql_config_editor Configurati
這篇文章主要介紹了MySQL 配置免密碼登錄的問題記錄(mysql_config_editor Configuration),本文給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-08-08