面試官:你知道select語句和update語句分別是怎麼執行的嗎?

2021-02-07 程式設計師求職面試

來自：黎社編程（ID：  LDCldc123095）


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

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

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

select語句

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

然後會檢查緩存中是否存在該查詢，若存在，返回緩存中存在的結果。若是不存在就進行下一步。接著進行語法和詞法的分析，對SQl的解析、語法檢測和預處理，再由優化器生成對應的執行計劃。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執行語句的區別，這一期就到這裡。


分享程式設計師找工作經驗
程式設計師筆試、面試題

相關焦點

  • 面試官:你知道 select 語句和 update 語句分別是怎麼執行的嗎?
    ：你知道select語句和update語句分別是怎麼執行的嗎？，要我寫一篇這兩者執行SQL語句的區別，這不就來了。總的來說，select和update執行的邏輯大體一樣，但是具體的實現還是有區別的。當然深入了解select和update的具體區別並不是只為了面試，當希望Mysql能夠高效的執行的時候，最好的辦法就是清楚的了解Mysql是如何執行查詢的，只有更加全面的了解SQL執行的每一個過程，才能更好的進行SQl的優化。sele
  • 【Java】面試官靈魂拷問:if語句執行完else語句真的不會再執行嗎?
    ，這不，一名讀者面試時，被面試官問到了一個直擊靈魂的問題：if 語句執行完else語句真的不會再執行嗎？問題分析最近一名讀者留言說，自己出去面試被面試官的一道奇葩問題問倒了，這個問題就是：if語句執行完else語句真的不會再執行嗎？這名讀者確實不知道該如何回答這個問題。回去後，自己查閱了很多資料也沒弄明白這個問題！想必很多讀者朋友遇到這種奇葩面試題時，多多少少都會覺得鬧心吧！不過，鬧心歸鬧心，問題還是要解決的。今天，我們就一起來剖析下這個奇葩的面試題。
  • IT大叔詳談mysql中update語句和delete語句及應用
    學習是一件枯燥的事，你要犧牲自己的業餘時間，你要忍受孤獨，堅持下來了你就勝利了，學習是一個過程，只要循序漸進，每天進步一點點，只有這樣你才能提高進而成功。今天老韓來講一講mysql中的update語句和delete語句。一、update（更新）語句；update語句屬於DML語句，如果要更新資料庫中的數據時可以使用它。
  • SQL UPDATE 語句
    Update 語句Update 語句用於修改表中的數據。CityGatesBillXuanwumen 10BeijingWilsonFredZhongshan 23Nanjing請注意 :SQL UPDATE 語句中的
  • 5年老碼農,update語句誤用一個雙引號,生產數據全變0了
    二、過程由於開發需要在生產環節中修複數據，需要執行120條SQL語句，需要將數據進行更新，於是開發連上了生產資料庫，首先執行了第一條SQLupdate tablename set source_name = "bj1062-北京市朝陽區常營北辰福第"  where source_name =     "-北京市朝陽區常營北辰福第
  • SQL 查詢語句先執行 SELECT?兄弟你認真的麼?
    具體解釋：（註：下面「VT」表示 → 虛擬表 virtual ）from：select * from table_1, table_2; 與 select * from table_1 join table_2; 的結果一致，都是表示求笛卡爾積；用於直接計算兩個表笛卡爾積，得到虛擬表VT1，這是所有select語句最先執行的操作
  • 面試官靈魂一問: 為什麼 SQL 語句不要過多的 join?
    total 總內存used 已用內存free 空閒內存buff/cache 已使用的緩存面試官：那你知道怎麼清理已使用的緩存嗎(buff/cache)我：em...不知道面試官：sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了，你說說我在線上執行這條命令做好不好?
  • mysql修改語句UPDATE的使用
    今天介紹一下update的使用，這個關鍵詞就是修改資料庫語句。我們看看在哪些環境會用到修改語句，比如在編輯輸入資料的時候發現資料錄錯了，或者某個信息要修改，這個時候就需要用到update了。我們實現的是最後原理，不管是asp，jsp還是php實現最底層都是通過sql語句修改資料庫，實現達到的效果。通過程序篩選出數據，在指定去執行。我們看看demo我們先看看update的語法。
  • update語句set中也可以使用and關鍵字
    我們平常寫sql更新數據，常見都是下面這種吧update student set no = '1', name = '張xx' where row_id = 3;但你是否有注意到，下面這條語句也是可以更新數據的update
  • 不就是 SELECT COUNT 語句嗎,竟然能被面試官虐的體無完膚
    但是，就是這個常用的COUNT函數，卻暗藏著很多玄機，尤其是在面試的時候，一不小心就會被虐。不信的話請嘗試回答下以下問題：1、COUNT有幾種用法？2、COUNT(欄位名)和COUNT(*)的查詢結果有什麼不同？3、COUNT(1)和COUNT(*)之間有什麼不同？4、COUNT(1)和COUNT(*)之間的效率哪個更高？
  • 為什麼要避免使用「CREATE TABLE AS SELECT」語句
    在這篇博文中，我將解釋為什麼你應該避免使用CREATE TABLE AS SELECT語句。SQL語句「create table <table_name> as select ...」用於創建普通表或臨時表，並物化select的結果。某些應用程式使用這種結構來創建表的副本。一條語句完成所有工作，因此您無需創建表結構或使用其他語句來複製結構。
  • MySQL-SQL語句執行流程
    分析器:  沒有命中緩存的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要幹嘛，再檢查你的 SQL 語句語法是否正確。優化器：  按照 MySQL 認為最優的方案去執行。select 語句了。
  • SQL語句之Select
    既然Select語句執行的結果也是一張表，那麼它的語句中就應該指明產生這張表的欄位和記錄。Select關鍵詞後指明所需要的欄位，From關鍵詞指明數據的來源。例3：顯示學生表中所有男生的姓名和班級。語句：select 姓名,班級 from 學生 where 性別="男"
  • MySQL UPDATE語句一個「經典」的坑
    /起因最近好幾次有開發同學在釘釘上問我，比如下圖：問題歸納起來就是：在MySQL裡面update一條記錄，語法都正確的，但記錄並沒有被更新…結論小結：在一條UPDATE語句中，如果要更新多個欄位，欄位間不能使用「AND」，而應該用逗號分隔。
  • MySQL update語句一個「經典」的坑
    現象剛遇到這個問題的時候，我拿到這條語句直接在測試庫裡面執行了一把，發現確實有問題，但和開發描述的還是有區別，這裡我用測試數據來模擬下：有問題的SQL語句：update apps set owner_code='43212' and owner_name='李四' where owner_code='13245' and owner_name='
  • MySQL Update語句一個非常經典的「坑」
    點擊上方關注 「Java研究所」設為「星標」，和你一起掌握更多資料庫知識起因最近好幾次有開發同學在釘釘上問我，比如下圖：問題歸納起來就是：在MySQL裡面update一條記錄，語法都正確的，但記錄並沒有被更新…結論小結：在一條UPDATE語句中，如果要更新多個欄位，欄位間不能使用「AND」，而應該用逗號分隔。
  • SQL 查詢總是先執行SELECT語句嗎?你們都錯了!
    SQL 查詢的執行順序於是我研究了一下，發現順序大概是這樣的。SELECT 並不是最先執行的，而是在第五個。這張圖回答了以下這些問題這張圖與 SQL 查詢的語義有關，讓你知道一個查詢會返回什麼，並回答了以下這些問題：可以在 GRROUP BY 之後使用 WHERE 嗎？（不行，WHERE 是在 GROUP BY 之前！）可以對窗口函數返回的結果進行過濾嗎？
  • 判斷語句Select Case,比If-Else語句的整潔,容易看懂的他,你只需1分鐘就學會
    2、當執行的時候遇到Select Case，它會記下測試表達式的值。然後它前進到下面的第一個Case子句，如果這個表達式的值和測試表達式的值匹配的話，就會執行語句直到遇到另外一個Case子句並且跳到End Select語句。
  • 最強解讀MyBatis是如何執行SQL語句的?
    代碼中的if-else語句塊分別判斷四種不同的加載方式，可見package的優先級最高。parent是配置文件中mappers標籤中的信息，通過外層的循環一個一個讀取多個Mapper文件。這裡使用的方式是resource，所以會執行光標所在行的代碼塊，進入mapperParser.parse()方法。
  • 真刺激,用了Insert into select語句,差點被勸退!
    2、☞ 《Java面試手冊》.PDF    點擊查看作者：不一樣的科技宅https://juejin.im/post/5e670f0151882549274a65efInsert into select 請慎用，同事因為使用了 Insert into select 語句引發了重大生產事故，最後被開除。