mysql如何執(zhí)行流程
MySQL
架構(gòu)與SQL執(zhí)行流程
MySQL主要而分為server層和存儲引擎層兩部分
- Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等,涵蓋 MySQL 的大多數(shù)核心服務(wù)功能,以及所有的內(nèi)置函數(shù)(如日期、時間、數(shù)學(xué)和加密函數(shù)等),所有跨存儲引擎的功能都在這一層實現(xiàn),比如存儲過程、觸發(fā)器、視圖等。
- 存儲引擎層負(fù)責(zé)數(shù)據(jù)的存儲和提取。其架構(gòu)模式是插件式的,支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現(xiàn)在最常用的存儲引擎是 InnoDB,它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲引擎。(在 create table 語句中使用 engine=memory, 來指定使用內(nèi)存引擎創(chuàng)建表。)
SQL查詢語句執(zhí)行流程
select * from T where ID=10;
- 連接器(負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接)
連接命令
mysql -h$ip -P$port -u$user -p
連接命令中的 mysql 是客戶端工具,用來跟服務(wù)端建立連接。
在完成經(jīng)典的 TCP 握手后,連接器就要開始認(rèn)證你的身份,這個時候用的就是你輸入的用戶名和密碼。
如果用戶名或密碼不對,你就會收到一個"Access denied for user"的錯誤,然后客戶端程序結(jié)束執(zhí)行。
如果用戶名密碼認(rèn)證通過,連接器會到權(quán)限表里面查出你擁有的權(quán)限。之后,這個連接里面的權(quán)限判斷邏輯,都將依賴于此時讀到的權(quán)限。
一個用戶成功建立連接后,即使你用管理員賬號對這個用戶的權(quán)限做了修改,也不會影響已經(jīng)存在連接的權(quán)限。修改完成后,只有再新建的連接才會使用新的權(quán)限設(shè)置。
連接完成后,如果你沒有后續(xù)的動作,這個連接就處于空閑狀態(tài),你可以在 show processlist 命令中看到它。
客戶端如果太長時間沒動靜,連接器就會自動將它斷開。這個時間是由參數(shù) wait_timeout 控制的,默認(rèn)值是 8 小時。
全部使用長連接后,有些時候 MySQL 占用內(nèi)存漲得特別快,這是因為 MySQL 在執(zhí)行過程中臨時使用的內(nèi)存是管理在連接對象里面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來,可能導(dǎo)致內(nèi)存占用太大,被系統(tǒng)強(qiáng)行殺掉(OOM),從現(xiàn)象看就是 MySQL 異常重啟了。
解決這個問題,以下兩種方案
定期斷開長連接。使用一段時間,或者程序里面判斷執(zhí)行過一個占用內(nèi)存的大查詢后,斷開連接,之后要查詢再重連。
如果你用的是 MySQL 5.7 或更新版本,可以在每次執(zhí)行一個比較大的操作后,通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權(quán)限驗證,但是會將連接恢復(fù)到剛剛創(chuàng)建完時的狀態(tài)。
- 查詢緩存(MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了)
查詢緩存的失效非常頻繁,只要有對一個表的更新,這個表上所有的查詢緩存都會被清空
對于更新壓力大的數(shù)據(jù)庫來說,查詢緩存的命中率會非常低。除非你的業(yè)務(wù)就是有一張靜態(tài)表,很長時間才會更新一次。比如,一個系統(tǒng)配置表,那這張表上的查詢才適合使用查詢緩存。
- 分析器
對 SQL 語句做解析
分析器先會做“詞法分析”。你輸入的是由多個字符串和空格組成的一條 SQL 語句,MySQL 需要識別出里面的字符串分別是什么,代表什么。
MySQL 從你輸入的"select"這個關(guān)鍵字識別出來,這是一個查詢語句。它也要把字符串“T”識別成“表名 T”,把字符串“ID”識別成“列 ID”。
做完了這些識別以后,就要做“語法分析”。根據(jù)詞法分析的結(jié)果,語法分析器會根據(jù)語法規(guī)則,判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。
如果你的語句不對,就會收到“You have an error in your SQL syntax”的錯誤提醒,比如下面這個語句 select 少打了開頭的字母“s”。
一般語法錯誤會提示第一個出現(xiàn)錯誤的位置,所以你要關(guān)注的是緊接“use near”的內(nèi)容。
- 優(yōu)化器
優(yōu)化器是在表里面有多個索引的時候,決定使用哪個索引;或者在一個語句有多表關(guān)聯(lián)(join)的時候,決定各個表的連接順序
- 執(zhí)行器
開始執(zhí)行的時候,要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權(quán)限,如果沒有,就會返回沒有權(quán)限的錯誤,如下所示 (在工程實現(xiàn)上,如果命中查詢緩存,會在查詢緩存返回結(jié)果的時候,做權(quán)限驗證。查詢也會在優(yōu)化器之前調(diào)用 precheck 驗證權(quán)限)。
如果有權(quán)限,就打開表繼續(xù)執(zhí)行。打開表的時候,執(zhí)行器就會根據(jù)表的引擎定義,去使用這個引擎提供的接口。
select * from T where ID=10
ID 字段沒有索引,那么執(zhí)行器的執(zhí)行流程是這樣的:
1、調(diào)用 InnoDB 引擎接口取這個表的第一行,判斷 ID 值是不是 10,如果不是則跳過,如果是則將這行存在結(jié)果集中;
2、調(diào)用引擎接口取“下一行”,重復(fù)相同的判斷邏輯,直到取到這個表的最后一行。
3、執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端。
對于有索引的表,執(zhí)行的邏輯也差不多。第一次調(diào)用的是“取滿足條件的第一行”這個接口,之后循環(huán)取“滿足條件的下一行”這個接口,這些接口都是引擎中已經(jīng)定義好的。
你會在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€ rows_examined 的字段,表示這個語句執(zhí)行過程中掃描了多少行。這個值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時候累加的。
在有些場景下,執(zhí)行器調(diào)用一次,在引擎內(nèi)部則掃描了多行,因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的
SQL更新語句執(zhí)行
- 與查詢流程不一樣的是,更新流程還涉及兩個重要的日志模塊;redo log(重做日志)和 binlog(歸檔日志)
- 物理日志 redo log(InnoDB 引擎特有日志)
當(dāng)有一條記錄需要更新的時候,InnoDB 引擎就會先把記錄寫到 redo log 里面,并更新內(nèi)存,這個時候更新就算完成了。同時,InnoDB 引擎會在適當(dāng)?shù)臅r候,將這個操作記錄更新到磁盤里面,而這個更新往往是在系統(tǒng)比較空閑的時候做
記錄這個頁做了什么改動
redo log 用于保證 crash-safe 能力。innodb_flush_log_at_trx_commit 這個參數(shù)設(shè)置成 1 的時候,表示每次事務(wù)的 redo log 都直接持久化到磁盤。這個參數(shù)我建議你設(shè)置成 1,這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失。
- 邏輯日志 binlog(Server 層日志)
Binlog有兩種模式,
statement 格式的話是記sql語句,
row格式會記錄行的內(nèi)容,記兩條,更新前和更新后都有。
- 日志區(qū)別
1、redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實現(xiàn)的,所有引擎都可以使用。
2、redo log 是物理日志,記錄的是“在某個數(shù)據(jù)頁上做了什么修改”;binlog 是邏輯日志,記錄的是這個語句的原始邏輯,比如“給 ID=2 這一行的 c 字段加 1 ”。
3、redo log 是循環(huán)寫的,空間固定會用完;binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個,并不會覆蓋以前的日志。
- update 語句時的內(nèi)部流程
update T set c=c+1 where ID=2;
1、執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵,引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中,就直接返回給執(zhí)行器;否則,需要先從磁盤讀入內(nèi)存,然后再返回。
2、執(zhí)行器拿到引擎給的行數(shù)據(jù),把這個值加上 1,比如原來是 N,現(xiàn)在就是 N+1,得到新的一行數(shù)據(jù),再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
3、引擎將這行新數(shù)據(jù)更新到內(nèi)存中,同時將這個更新操作記錄到 redo log 里面,此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了,隨時可以提交事務(wù)。
4、執(zhí)行器生成這個操作的 binlog,并把 binlog 寫入磁盤。
5、執(zhí)行器調(diào)用引擎的提交事務(wù)接口,引擎把剛剛寫入的 redo log 改成提交(commit)狀態(tài),更新完成。
兩階段提交(跨系統(tǒng)維持?jǐn)?shù)據(jù)邏輯一致性時常用的一個方案)
數(shù)據(jù)庫恢復(fù)
binlog 會記錄所有的邏輯操作,并且是采用“追加寫”的形式
需要恢復(fù)到指定的某一秒時
- 找到最近的一次全量備份,從這個備份恢復(fù)到臨時庫;
- 從備份的時間點開始,將備份的 binlog 依次取出來,重放到指定時刻。
- 由于 redo log 和 binlog 是兩個獨立的邏輯,如果不使用“兩階段提交”,那么數(shù)據(jù)庫的狀態(tài)就有可能和用它的日志恢復(fù)出來的庫的狀態(tài)不一致。
先寫 redo log 后寫 binlog。
假設(shè)在 redo log 寫完,binlog 還沒有寫完的時候,MySQL 進(jìn)程異常重啟。由于我們前面說過的,redo log 寫完之后,系統(tǒng)即使崩潰,仍然能夠把數(shù)據(jù)恢復(fù)回來,所以恢復(fù)后這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了,這時候 binlog 里面就沒有記錄這個語句。因此,之后備份日志的時候,存起來的 binlog 里面就沒有這條語句。
然后你會發(fā)現(xiàn),如果需要用這個 binlog 來恢復(fù)臨時庫的話,由于這個語句的 binlog 丟失,這個臨時庫就會少了這一次更新,恢復(fù)出來的這一行 c 的值就是 0,與原庫的值不同。
先寫 binlog 后寫 redo log。
如果在 binlog 寫完之后 crash,由于 redo log 還沒寫,崩潰恢復(fù)以后這個事務(wù)無效,所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個日志。所以,在之后用 binlog 來恢復(fù)的時候就多了一個事務(wù)出來,恢復(fù)出來的這一行 c 的值就是 1,與原庫的值不同。
InnoDB
- 數(shù)據(jù)存儲:在InnoDB存儲引擎,數(shù)據(jù)被邏輯地存放到表空間中,表空間(tablespace)是存儲引擎中最高到的存儲邏輯單位,在表空間的下面包括段(segment)、區(qū)(extent)、頁(page)
- 存儲表:表的定義信息存儲在 .frm 文件中;數(shù)據(jù)索引存儲在 .ibd 文件中;
- 存儲記錄:InnoDB 使用頁作為磁盤管理的最小單位,數(shù)據(jù)在 InnoDB 中按行存儲,每個 16KB 大小的頁中可以存放 2-200 行的記錄
- B+Tree 在查找對應(yīng)的記錄時,并不會直接從樹中找出對應(yīng)的行記錄,它只能記錄獲取記錄所在頁,將整個頁加載到內(nèi)存中,再通過 Page Directory 中存儲的稀疏索引和 n_owned、next_record 屬性取出對應(yīng)的記錄,這部分是在內(nèi)存中進(jìn)行的,通常忽略這部分查找耗時;
索引:索引優(yōu)化是對查詢性能優(yōu)化的最有效手段;
B+Tree(平衡樹,查找任意節(jié)點耗時相同,比較次數(shù)就是樹高) 索引可以分為聚集索引和輔助索引
- 聚集索引:就是按照表中主鍵的順序構(gòu)建的一顆 B+Tree ,并在葉子節(jié)點總存放表中的行記錄數(shù)據(jù);使用聚集索引對表中的數(shù)據(jù)進(jìn)行檢索時,可以直接獲得聚集索引對應(yīng)的整條行記錄數(shù)據(jù)所在的葉,不需要進(jìn)行第二次操作。
- 輔助索引:也是通過 B+Tree 實現(xiàn),但是葉子節(jié)點并不包含行記錄的全部數(shù)據(jù),僅包含索引中的所有鍵和一個用于查找對應(yīng)行記錄的【書簽】,InnoDB 中這個書簽就是當(dāng)前記錄的主鍵
鎖
- InnoDB 鎖種類
- 共享鎖(Shared Lock)和互斥鎖(Exclusive Lock)屬于行級鎖
- 意向鎖(Intention Lock)一種表級鎖
- 意向共享鎖:事務(wù)想要在獲取表中某些記錄的共享鎖,需要在表上先加意向共享鎖
- 意向互斥鎖:事務(wù)想要獲取表中某些記錄的互斥鎖,需要在表上先加意向互斥鎖
意向鎖不會阻塞全表掃描之外的任何請求,主要目的是為了表示是否有人請求鎖定標(biāo)中某一行數(shù)據(jù)
- 意向共享鎖:事務(wù)想要在獲取表中某些記錄的共享鎖,需要在表上先加意向共享鎖
- 意向互斥鎖:事務(wù)想要獲取表中某些記錄的互斥鎖,需要在表上先加意向互斥鎖
意向鎖不會阻塞全表掃描之外的任何請求,主要目的是為了表示是否有人請求鎖定標(biāo)中某一行數(shù)據(jù)
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
淺談Mysql、SqlServer、Oracle三大數(shù)據(jù)庫的區(qū)別
這篇文章主要介紹了Mysql、SqlServer、Oracle三大數(shù)據(jù)庫的區(qū)別,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04MySQL的存儲函數(shù)與存儲過程相關(guān)概念與具體實例詳解
MySQL存儲函數(shù)(自定義函數(shù)),函數(shù)一般用于計算和返回一個值,可以將經(jīng)常需要使用的計算或功能寫成一個函數(shù),存儲函數(shù)和存儲過程一樣,都是在數(shù)據(jù)庫中定義一些SQL語句的集合2023-03-03MySQL 文本文件的導(dǎo)入導(dǎo)出數(shù)據(jù)的方法
但有時為了更快速地插入大批量數(shù)據(jù)或交換數(shù)據(jù),需要從文本中導(dǎo)入數(shù)據(jù)或?qū)С鰯?shù)據(jù)到文本。下面的具體的方法大家可以參考下。多測試。2009-11-11Mysql錯誤Every derived table must have its own alias解決方法
這篇文章主要介紹了Mysql錯誤Every derived table must have its own alias解決方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-08-08MySQL數(shù)據(jù)庫主從復(fù)制原理及作用分析
這篇文章主要介紹了MySQL數(shù)據(jù)庫主從復(fù)制原理并分析了主從復(fù)制的作用和使用方法,有需要的的朋友可以借鑒參考下,希望可以有所幫助,感謝閱讀2021-09-09