面試官：你知道select陳述句和update陳述句分別是怎么執行的嗎？-有解無憂

最近有粉絲面試互聯網公司被問到：你知道select陳述句和update陳述句分別是怎么執行的嗎？，要我寫一篇這兩者執行SQL陳述句的區別，這不就來了，

總的來說，select和update執行的邏輯大體一樣，但是具體的實作還是有區別的，

當然深入了解select和update的具體區別并不是只為了面試，當希望Mysql能夠高效的執行的時候，最好的辦法就是清楚的了解Mysql是如何執行查詢的，只有更加全面的了解SQL執行的每一個程序，才能更好的進行SQl的優化，

select陳述句

當執行一條查詢的SQl的時候大概發生了一下的步驟：

客戶端發送查詢陳述句給服務器，
服務器首先進行用戶名和密碼的驗證以及權限的校驗，
然后會檢查快取中是否存在該查詢，若存在，回傳快取中存在的結果，若是不存在就進行下一步，
接著進行語法和詞法的分析，對SQl的決議、語法檢測和預處理，再由優化器生成對應的執行計劃，
Mysql的執行器根據優化器生成的執行計劃執行，呼叫存盤引擎的介面進行查詢，
服務器將查詢的結果回傳客戶端，

Mysql的執行的流程

Mysql中陳述句的執行都是都是分層執行，每一層執行的任務都不同，直到最后拿到結果回傳，主要分為Service層和引擎層，在Service層中包含：連接器、分析器、優化器、執行器，引擎層以插件的形式可以兼容各種不同的存盤引擎，

Mysql的執行的流程圖如下圖所示：

這里以一個實體進行說明Mysql的的執行程序，新建一個User表，如下：

// 新建一個表
DROP TABLE IF EXISTS User;
CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT 0,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `dept` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

// 并初始化資料，如下
INSERT INTO User(name,age,address,phone,dept)VALUES('張三',24,'北京','13265543552',2);
INSERT INTO User(name,age,address,phone,dept)VALUES('張三三',20,'北京','13265543557',2);
INSERT INTO User(name,age,address,phone,dept)VALUES('李四',23,'上海','13265543553',2);
INSERT INTO User(name,age,address,phone,dept)VALUES('李四四',21,'上海','13265543556',2);
INSERT INTO User(name,age,address,phone,dept)VALUES('王五',27,'廣州','13265543558',3);
INSERT INTO User(name,age,address,phone,dept)VALUES('王五五',26,'廣州','13265543559',3);
INSERT INTO User(name,age,address,phone,dept)VALUES('趙六',25,'深圳','13265543550',3);
INSERT INTO User(name,age,address,phone,dept)VALUES('趙六六',28,'廣州','13265543561',3);
INSERT INTO User(name,age,address,phone,dept)VALUES('七七',29,'廣州','13265543562',4);
INSERT INTO User(name,age,address,phone,dept)VALUES('八八',23,'廣州','13265543563',4);
INSERT INTO User(name,age,address,phone,dept)VALUES('九九',24,'廣州','13265543564',4);

現在針對這個表發出一條SQl查詢：查詢每個部門中25歲以下的員工個數大于3的員工個數和部門編號，并按照人工個數降序排序和部門編號升序排序的前兩個部門，

SELECT dept,COUNT(phone) AS num FROM User WHERE age< 25 GROUP BY dept HAVING num >= 3 ORDER BY num DESC,dept ASC LIMIT 0,2;

連接器

開始執行這條sql時，首先會校驗你的用戶名和密碼是否正確，若是不正確會回傳錯誤資訊："Access denied for user"；

若是用戶名和密碼校驗通過，然后就會到權限表獲取當前用戶擁有的權限，會檢查該陳述句是否有權限，若是沒有權限就直接回傳錯誤資訊，有權限會進行下一步，校驗權限的這一步是在圖一的連接器進行的，對連接用戶權限的校驗，

注意：后續的一些列操作都是依賴于這個權限的范圍內的，

檢索快取

當建立連接，履行查詢陳述句的時候，會先行檢查在快取區域看看這個sql與否履行過，若是之前執行過，它的執行結果會以Key-Value的形式平緩適用記憶體中，Key是執行的sql，Value是結果集，

假如，快取中key遭擊中，便會直接將結果回傳給客戶端，假如沒命中，便會履行后續的操作，完工之后亦會將結果快取起來以便再次查詢獲取，當下一次進行查詢的時候也是如此的回圈操作，

注意：Mysql中的快取比較適合于那些靜態的表，更新不頻繁的表，因為只要當前表有資料更新，有關于該表的快取就會失效，若是表更新頻繁快取頻繁的實效，這樣維護快取的消耗的性能遠大于使用快取帶來的性能優化，這樣就會得不償失，嚴重影響Mysql的性能，所以在Mysql 8版本中的時候把快取這一塊給砍掉了，

在個人的觀點種對于快取這一塊的看法是，沒必要砍掉，可以設定成默認關閉快取，需要的時候再設定開啟，并且可以通過配置引數指定那別表使用快取，那些表不使用快取，這樣或許使用快取更有效，

分析器

分析器主要有兩步：（1）詞法分析（2）語法分析

詞法分析主要執行提煉關鍵性字，比如select，提交檢索的表，提交欄位名，提交檢索條件，確定該陳述句是select還是update或者是delete陳述句，

語法分析主要執行辨別你輸出的sql與否準確，是否合乎mysql的語法，若是不符合sql語法就會拋出：You have an error in your SQL syntax，

優化器

查詢優化器會將決議樹轉化成執行計劃，一條查詢可以有多種執行方法，最后都是回傳相同結果，優化器的作用就是找到這其中最好的執行計劃，

例如：在查詢陳述句中有多個索引的時候，優化器決定使用哪一個索引，或者有多表關聯的時候，決定表的連接順序等這些操作都是在優化器決定的，

生成執行計劃的程序會消耗較多的時間，特別是存在許多可選的執行計劃時，如果在一條SQL陳述句執行的程序中將該陳述句對應的最終執行計劃進行快取，

當相似的陳述句再次被輸入服務器時，就可以直接使用已快取的執行計劃，從而跳過SQL陳述句生成執行計劃的整個程序，進而可以提高陳述句的執行速度，

MySQL使用基于成本的查詢優化器，它會嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最少的一個，

執行器

優化器生成得執行計劃，交由執行器進行執行，執行器呼叫存盤引擎得讀介面，執行器中回圈的呼叫存盤引擎的讀介面，以此換取滿足條件的資料行，并把它放在一個結果集中，遍歷并獲取了所有滿足條件的資料行，最后將結果集回傳，結束整個查詢得程序，

update陳述句

上面我們說完了select陳述句，select陳述句的執行程序會經過連接器、分析器、優化器、執行器、存盤引擎，同樣的update陳述句也會同樣走一遍select陳述句的執行程序，

但是和select最大不同的是，update陳述句會涉及到兩個日志的操作redo log（重做日志） 和binlog （歸檔日志），對于這兩個日志的詳細介紹，我之前寫過一篇文章進行介紹，有興趣的可以看一看[]：

那么Mysql中又是怎么使用redo log和binlog？為什么要使用redo log和binlog呢？直接執行更新然后存庫不就行了嗎？還要放在redo log和binlog中，這不是多此一舉嗎？且聽我慢慢道來，這里面大有文章，

redo log

大家都是知道Mysql是關系型資料庫，用來存盤資料的，在訪問資料庫量大的時候，Mysql讀寫磁盤訪問的效率是非常低的，加上sql中的條件對資料的篩選過濾，那么效率就更低了，

這也是為什么引入非關系型資料庫作為作為資料快取原因，例如：Redis、MongoDB等，就是為了減少sql執行期間的資料庫io操作，

同樣的道理，若是每次執行update陳述句都要進行磁盤的io操作、以及資料的過濾篩選，小量的訪問和資料量資料庫還可以撐住，那么訪問量一大以及資料量一大，這樣資料庫肯定頂不住，

基于上面的問題于是出現了redo log日志，redo log日志也叫做WAL技術（Write- Ahead Logging），他是一種先寫日志，并更新記憶體，最后再更新磁盤的技術，并且更新磁盤往往是在Mysql比較閑的時候，這樣就大大減輕了Mysql的壓力，

redo log的特點就是：redo log是固定大小，是物理日志，屬于InnoDB引擎的，并且寫redo log是環狀寫日志的形式：

如上圖所示：若是四組的redo log檔案，一組為1G的大小，那么四組就是4G的大小，其中write pos是記錄當前的位置，有資料寫入當前位置，那么write pos就會邊寫入邊往后移，

而check point是擦除的位置，因為redo log是固定大小，所以當redo log滿的時候，也就是write pos追上check point的時候，需要清除redo log的部分資料，清除的資料會被持久化到磁盤中，然后將check point向前移動，

redo log日志實作了即使在資料庫出現例外宕機的時候，重啟后之前的記錄也不會丟失，這就是crash-safe能力，

binlog

binlog稱為歸檔日志，是邏輯上的日志，它屬于Mysql的Server層面的日志，記錄著sql的原始邏輯，主要有兩種模式，一個是statement格式記錄的是原始的sql，而row格式則是記錄行內容，

那么這樣看來redo log和binlog雖然記錄的形式、內容不同，但是這兩者日志都能通過自己記錄的內容恢復資料，那么為什么還要這兩個日志同時存在呢？只要其中一個不就行了嘛，兩個同時存在不就多此一舉了嘛，且聽我慢慢道來，這里面也大有文章，

因為剛開Mysql自帶的引擎MyISAM就沒有crash-safe功能的，并且在此之前Mysql還沒有InnoDB引擎，Mysql自帶的binlog日志只是用來歸檔日志的，所以InnoDB引擎也就通過自己redo log日志來實作crash-safe功能，

update執行程序

上面說了那么久兩種日志的作用和特點，那么這兩種日志究竟和update執行陳述句有什么關系呢？

先來看圖：

前提：當前的引擎是使用InnoDB，update陳述句與select陳述句區別主要是這兩日志的使用主要是在執行器和引擎之間進行互動時體現的區別，假如執行如下一條簡單的更新陳述句是：

update user set age=age+1 where id =2；

上面說過select陳述句走過的流程update陳述句也會走一遍，當來到執行器的時候：

執行器會呼叫引擎的讀介面，然后找到id=2的資料行，因為id是主鍵索引，索引按照樹的搜索找到這一行，若是資料行已經存在于記憶體的資料頁中就會立即將結果回傳，若是不在記憶體中，就會從磁盤中進行加載到記憶體中，然后將查詢的結果回傳，
然后，執行器將回傳的結果的age欄位+1，并呼叫引擎的寫介面寫入更新后的資料行，
引擎獲取到更新后的資料行更新到記憶體和redo log中，并告訴執行器可以隨時提交事務，此時的redo log處于prepare階段，
執行器收到引擎的告知后，生成binlog日志，并且呼叫引擎的介面提交事務，引擎將redo log的狀態修改為commit狀態，這樣這個更新操作算是完成，

與select陳述句相比，因為select沒有更新資料，只是將引擎查詢的資料回傳給執行器就算是完后，而update涉及資料的更新并且重新呼叫引擎介面寫會存盤引擎中的互動程序，

兩階段提交

上面詳細的說了update陳述句的執行流程，提到了redo log的prepare和commit兩個階段，這就是兩階段提交，兩階段提交的目的是為了保證redo log日志與binlog日志保持資料的一致性，

若是redo log寫成功binlog寫失敗，或者redo log寫失敗binlog寫成功，最后使用這兩者日志進行資料恢復得到的結果資料都是不一致性的，所以為了保證兩個日志邏輯上的一致，使用兩階段進行提交，

redo log與binlog的總結

最后來對比一下這兩種日志：redo是物理的，binlog是邏輯的，redo的大小固定，并且以環狀的形式寫入資料，資料滿的時候需要將redo日志中擦除資料，并且將擦除的資料持久化到磁盤中，

而binlog以追加日志的形式寫入，也就是當日志寫到一定大小后，就會切換到下一個，并不會覆寫以前寫的日志，

binlog是在Mysql的Server層中使用，因為binlog沒有crash-safe功能，所以InnoDB引擎自己實作了redo log日志的crash-safe的功能，為了保證這兩個日志邏輯上的一致使用兩階段提交，

在使用redo和binlog這兩種日志的時候，可以將引數innodb_flush_log_at_trx_commit和sync_binlog都設定為1，它表示每次事務提交的時候，都會將日志持久化到磁盤中，

好了，這里詳細的介紹了select和update執行陳述句的區別，這一期就到這里，我們下一期間，喜歡的歡迎轉發和分享，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/201679.html

標籤：其他

上一篇：Elasticsearch節點，集群，分片及副本詳解

下一篇：gin HTTP/2 服務器推送