SQL語句執行的底層實現

SQL語句執行的底層實現

本文包含一條select語句或update語句的底層實現原理、事務并發、事務的隔離級別

一、一條查詢語句的執行流程

Server層包含MySQL的大多數核心服務，和所有內置函數，所有跨存儲引擎功能的實現

存儲引擎層負責數據的存儲和提取，包括InnoDB、MyISAM、Memory等多個存儲引擎，InnoDB從MySQL5.5.5版本開始成為了默認存儲引擎，可以自由選擇存儲引擎：engine=InnoDB

create table `t` { }engine=InnoDB

連接器負責跟客戶端建立連接、獲取權限、維持和管理連接

mysql -h 主機名 -P 端口號 -u 用戶名 -p密碼

例如：mysql 【-h localhost P 3306】 -u root -p564445 ，若是登錄本地的，【】內的可以不寫。-p和密碼中間不能加空格，也可以-p之后直接回車再輸入密碼，這時候密碼是隱藏的，安全性更高

MySQL拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以key-value對的形式，被直接緩存在內存中。key是查詢的語句，value是查詢的結果。如果查詢能夠直接在這個緩存中找到key，那么這個value就會被直接返回給客戶端。如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中。如果查詢命中緩存，MySQL不需要執行后面的復雜操作，就可以直接返回結果，效率更高。

但查詢緩存更適合業務上有一張靜態表的情況，很長時間才會更新一次，如果更新太頻繁，表上的查詢緩存會被清空，導致的使用率不高。MySQL8.0版本直接將查詢緩存的整塊功能刪掉了，從8.0版本開始徹底沒有這個功能了。

分析器會對語句做“詞法分析”，識別里面的各種字符代表什么，語法規則是否正確等等，一般語法錯誤會提示第一個出現錯誤的位置

優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。不同的執行順序會導致效率的高低，通常會選擇效率更高的情況，但是也會出現選錯的情況

先判斷對表T有沒有執行權限，沒有權限會報錯

然后使用引擎提供的接口，開始執行邏輯

然后將結果返回給客戶端

二、一條更新語句的執行流程

建表

create table `T`( ID int primary key, c int )engine=InnoDB;

將ID=2的值加1

update T set c=c+1 where ID=2;

執行流程

連接數據庫

因為有更新，所以會清空緩存數據，導致命中率不高，這也是為什么前面建議不適用查詢緩存的原因

分析器通過詞法和語法解析知道這是一條更新語句

優化器決定使用ID這個索引

執行器負責執行邏輯，找到這一行然后更新

更新流程和查詢流程大體上的執行順序相似，但是更新流程還涉及到兩個重要的日志模塊：redo log（重做日志）和bin log（歸檔日志）

先分析問題，如果每一次的更新操作都需要寫進磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個過程IO成本、查找成本都很高

這時候就需要redo log了，當有一條記錄需要更新的時候，InnoDB引擎就會先把記錄寫到redo log里，并更新內存，這個時候更新就算完成了。InnoDB引擎會在空閑的時候，將這個操作記錄批量更新到磁盤里面，減少對磁盤的IO操作

這其實就是MySQL里經常說到的WAL技術，WAL的全稱是Write-Ahead-Logging（預寫式日志），它的關鍵點就是先寫日志，再寫磁盤

InnoDB的redo log大小固定，比如可以配置一組4個文件，每個文件1GB，總共4GB，從頭到尾循環寫入，如下圖

write pos是當前正在記錄的位置，從0號文件到3號文件，然后循環回0號文件，checkpoint是將當前數據更新到磁盤。

有了redo log，InnoDB可以保證即使數據庫發生異常重啟，之前提交的記錄都不會丟失，稱為crash-safe

上面的redo log是InnoDB存儲引擎層特有的日志，而binlog則屬于Server層。因為最開始MySQL里并沒有InnoDB引擎。MySQL自帶的引擎是MyISAM，但是MyISAM沒有crash-safe的能力，binlog日志只能用于歸檔。而InnoDB是另一個公司以插件形式引入MySQL的，既然只依靠binlog是沒有crash-safe能力的，所以InnoDB使用另外一套日志系統，也就是redo log來實現crash-safe能力

redo log和binlog的三個不同點：

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用

redo log是物理日志，記錄的是“在某個數據頁上做了什么修改”；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c字段加1”

redo log是循環寫的，會把之前的覆蓋掉，空間固定會用完；binlog是可以追加寫入的?！白芳訉憽笔侵竍inlog文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志

現在再來看看加上和兩個日志模塊后，update語句在執行器的執行流程

執行器先找引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=2這一行所在的數據頁本來就在內存中，就直接返回給執行器；否則，需要先從磁盤讀入內存，然后再返回

執行器拿到引擎給的行數據，把這個值加上1，比如原來是c，現在就是c+1，得到新的一行數據，再調用引擎接口寫入這行新數據

引擎將這行新數據更新到內存中，同時將這個更新操作記錄到redo log里面，此時redo log處于prepare狀態。然后告知執行器執行完成了，隨時可以提交事務

執行器生成這個操作的binlog，并把binlog寫入磁盤

執行器調用引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交（commit）狀態，更新完成

上述最后三步中，將redo log的寫入拆成了兩個步驟：prepare和commit，即兩階段提交，兩階段提交的目的是：為了讓兩份日志之間的邏輯一致

先來看看數據恢復過程。binlog會記錄所有的邏輯操作，如果設定的是可以恢復一個月，那么備份系統中一定會保存最近一個月的所有binlog，前面講到binlog可以追加寫入，并不會覆蓋以前的日志，當要找回數據時：

找到最近一次的備份恢復到臨時庫

從備份的時間點開始，將備份的binlog依次取出來，重放到誤刪表之前的時刻

按需恢復到線上庫

為什么一定是兩階段提交呢？

可以使用binlog替代redo log進行數據恢復嗎？不可以，Innodb利用WAL技術進行數據恢復，write ahead logging技術依賴于物理日志進行數據恢復，binlog不是物理日志是邏輯日志，因此無法使用

可以只使用redo log而不使用binlog嗎？不可以，redo log是循環寫，寫到末尾要回到開頭繼續寫，這樣的日志無法保留歷史記錄，會被覆蓋掉，無法進行數據復制

由于redo log和binlog是兩個獨立的邏輯，如果不用兩階段提交，要么就是先寫完redolog再寫binlog，或者采用反過來的順序。若在寫完第一個日志后，第二個日志還沒有寫完期間發生了crash，數據庫的狀態可能和用日志恢復出來的數據庫的狀態不一致，下面是不采用兩階段提交的情況：

先寫redo log后寫binlog。 假設在redo log寫完，binlog還沒有寫完的時候，MySQL進程異常重啟。由于前面說過，redo log寫完之后，系統即使崩潰，仍然能夠把數據恢復回來，所以恢復后這一行c的值是1。但是由于binlog沒寫完就crash了，這時候binlog里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的binlog里面就沒有這條語句。然后會發現，如果需要用這個binlog來恢復臨時庫的話，由于這個語句的binlog丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行c的值就是0，與原庫的值不同

先寫binlog后寫redo log。 如果在binlog寫完之后crash，由于redo log還沒寫，崩潰恢復以后這個事務無效，所以這一行c的值是0。但是binlog里面已經記錄了“把c從0改成1”這個日志。所以，在之后用binlog來恢復的時候就多了一個事務出來，恢復出來的這一行c的值就是1，與原庫的值不同

由上述分析可見，如果不采用兩階段提交，數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致

采用兩階段提交后：

redo log好沒寫入之前崩潰，這時binlog也還沒寫入，恢復數據不受影響

redo log寫好了，binlog還沒寫崩潰時，這時redo log處于prepare狀態，還沒有提交，恢復時事務會回滾，binlog也還沒有記錄，所以不會影響

redo log已經有了commit標識，則直接提交事務，同時因為binlog有記錄，則恢復數據也不受影響

三、事務隔離

在MySQL中，事務是在引擎層實現的，MySQl的原生引擎MyISAM不支持事務，而InnoDB支持。

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、持久性）

原子性（Atomicity）：事務開始后的所有操作，要么全部做完，要么全部不做，不可能停滯在中間環節。事務執行過程中出錯，會回滾到事務開始前的狀態，所有的操作就像沒有發生一樣。也就是說事務是一個不可分割的整體，就像化學中學過的原子，是物質構成的基本單位

一致性（Consistency）：事務開始前和結束后，數據庫的完整性約束沒有被破壞 。比如A向B轉賬，不可能A扣了錢，B卻沒收到

隔離性（Isolation）：同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬

持久性（Durability）：事務完成后，事務對數據庫的所有更新將被保存到數據庫，不能回滾

為了解決事務并發問題，就有了“隔離級別”的概念，隔離得越嚴實，即隔離等級越高，效率越低。Oracle數據庫的默認隔離級別是讀已提交，MySQL數據庫的默認隔離級別是可重復讀，可以參考這篇文章：MySQL數據庫的默認隔離級別為什么是可重復讀？

舉個例子

若隔離級別是“讀未提交”，則V1的值就是2。這時候事務B雖然還沒有提交，但是結果已經被A看到了。因此，V2、V3也都是2

若隔離級別是“讀提交”，則V1是1，V2的值是2。事務B的更新在提交后才能被A看到。所以，V3的值也是2

若隔離級別是“可重復讀”，則V1、V2是1，V3是2。之所以V2還是1，遵循的就是這個要求：事務在執行期間看到的數據前后必須是一致的

若隔離級別是“串行化”，則在事務B執行“將1改成2”的時候，會被鎖住。直到事務A提交后，事務B才可以繼續執行。所以從A的角度看，V1、V2值是1，V3的值是2

以可重復讀為例，在MySQL中，每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。假設一個值從1按順序改成了2、3、4，在回滾日志里面就會有類似下面的記錄。

當前值是4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的read-view。如圖中看到的，在視圖A、B、C里面，這一個記錄的值分別是1、2、4，同一條記錄在系統中可以存在多個版本，這就是數據庫的多版本并發控制 MVCC（Multi-Version Concurrency Control），對于read-view A，要得到1，就必須將當前值依次執行圖中所有的回滾操作得到，即使現在有另外一個事務正在將4改成5，這個事務跟read-view A、B、C對應的事務是不會沖突的

長事務意味著系統里面會存在很老的事務視圖。由于這些事務隨時可能訪問數據庫里面的任何數據，所以這個事務提交之前，數據庫里面它可能用到的回滾記錄都必須保留，這就會導致占用大量存儲空間，這是一個不容忽視的問題。

MySQL的事務啟動方式有以下幾種

顯式啟動事務語句，begin或start transaction。配套的提交語句是commit，回滾語句是rollback

set autocommit=0，這個命令會將這個線程的自動提交關掉。意味著如果你只執行一個select語句，這個事務就啟動了，而且并不會自動提交。這個事務持續存在直到你主動執行commit或rollback語句，或者斷開連接

有些客戶端連接框架會默認連接成功后先執行一個set autocommit=0的命令。這就導致接下來的查詢都在事務中，如果是長連接，就導致了意外的長事務。因此，建議使用set autocommit=1，通過顯式語句的方式來啟動事務，在autocommit為1的情況下，用begin顯式啟動的事務，如果執行commit則提交事務，可以避免長事務。

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。