一個查詢語句的例子
在執行下面這個查詢語句時的執行的流程是怎么樣的？
MySQL> select * from t where id=1；
執行器的執行流程是這樣的：
1.調用 InnoDB 引擎接口取這個表的第一行，判斷 id 值是不是 1，如果不是則跳過，如果是則將這行存在結果集中；
2.調用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。
3.執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。
一個 SQL 的執行過程為：
1.連接 2.查詢緩存 3.詞法分析 4.語法分析 5.語義分析 6.構造執行樹 7.生成執行計劃 8.執行器執行計劃 9.返回執行結果
一個更新語句的例子
更新語句：
mysql> update table set c = c+1 where id = 2;
執行流程：
1.執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數據頁本來就在內存中，就直接返回給執行器；否則，需要先從磁盤讀入內存，然后再返回。 2.執行器拿到引擎給的行數據，把這個值加上1，比如原來是N，現在就是N+1，得到新的一行數據，再調用引擎接口寫入這行新數據。 3.引擎將這行新數據更新到內存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態。然后告知執行器執行完成了，隨時可以提交事務。 4.執行器生成這個操作的 binlog，并把 binlog 寫入磁盤。 5.執行器調用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態，更新完成。
query和update執行流程不一樣的在于 update涉及了日志模塊，binlog (歸檔日志)和 redo log (重做日志)。
兩階段提交
redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"。
為什么必須有“兩階段提交”呢？這是為了讓兩份日志之間的邏輯一致。
由于 redo log 和 binlog 是兩個獨立的邏輯，如果不用兩階段提交，要么就是先寫完 redo log 再寫 binlog，或者采用反過來的順序。我們看看這兩種方式會有什么問題。
仍然用前面的 update 語句來做例子。假設當前 ID=2 的行，字段 c 的值是 0，再假設執行 update語句過程中在寫完第一個日志后，第二個日志還沒有寫完期間發生了 crash，會出現什么情況呢？
情況 1 ：先寫 redo log 后寫 binlog。
假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟。由于我們前面說過的，redo log 寫完之后，系統即使崩潰，仍然能夠把數據恢復回來，所以恢復后這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時候 binlog 里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條語句。然后你會發現，如果需要用這個 binlog 來恢復臨時庫的話，由于這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同。
情況 2：先寫 binlog 后寫 redo log。
如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復以后這個事務無效，所以這一行 c 的值是 0。但是 binlog 里面已經記錄了“把 c 從 0 改成 1”這個日志。所以，在之后用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同。
可以看到，如果不使用“兩階段提交”，那么數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致。
MySQL邏輯架構
MySQL是一個開放源代碼的關系數據庫管理系統。原開發者為瑞典的MySQL AB公司，最早是在2001年MySQL3.23進入到管理員的視野并在之后獲得廣泛的應用。
當MySQL啟動（MySQL服務器就是一個進程），等待客戶端連接，每一個客戶端連接請求，服務器都會新建一個線程處理（如果是線程池的話，則是分配一個空的線程），每個線程獨立，擁有各自的內存處理空間。
MySQL總體上可分為Server層和存儲引擎層。
Server層包括連接器、查詢器、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。
存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。
如果能在頭腦中構建一幅MySQL各組件之間如何協同工作的架構圖，有助于深入理解MySQL服務器。下圖展示了MySQL的邏輯架構圖。
MySQL 整體上可以分為 Server 層和存儲引擎層兩部分。詳細的分層如下：
1.客戶端層：連接處理、授權認證、安全等功能均在這一層處理。包含本地sock通信和大多數基于客戶端/服務端工具實現的類似于tcp/ip的通信。主要完成一些類似于連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基于SSL的安全鏈接。服務器也會為安全接入的每個客戶端驗證它所具有的操作權限。
2.核心服務層：查詢解析、分析、優化、緩存、內置函數(比如：時間、數學、加密等函數)等。該層架構主要完成核心服務功能，如SQL接口，并完成緩存的查詢，SQL的分析和優化及部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如過程、函數等。在該層，服務器會解析查詢并創建相應的內部解析樹，并對其完成相應的優化如確定查詢表的順序，是否利用索引等，最后生成相應的執行操作。如果是select語句，服務器還會查詢內部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
3.存儲引擎層：存儲過程、觸發器、視圖等。存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
4.數據存儲層，主要是將數據存儲在運行于裸設備的文件系統之上，并完成與存儲引擎的交互。
最下層為存儲引擎，其負責MySQL中的數據存儲和提取。和linux下的文件系統類似，每種存儲引擎都有其優勢和劣勢。中間的服務層通過API與存儲引擎通信，這些API接口屏蔽了不同存儲引擎間的差異。
MySQL查詢過程
我們總是希望MySQL能夠獲得更高的查詢性能，最好的辦法是弄清楚MySQL是如何優化和執行查詢的。一旦理解了這一點，就會發現：很多的查詢優化工作實際上就是遵循一些原則讓MySQL的優化器能夠按照預想的合理方式運行而已。
當向MySQL發送一個請求的時候，MySQL到底做了些什么呢？
客戶端/服務端通信協議
MySQL客戶端/服務端通信協議是“半雙工”的：在任一時刻，要么是服務器向客戶端發送數據，要么是客戶端向服務器發送數據，這兩個動作不能同時發生。一旦一端開始發送消息，另一端要接收完整個消息才能響應它，所以我們無法也無須將一個消息切成小塊獨立發送，也沒有辦法進行流量控制。
客戶端用一個單獨的數據包將查詢請求發送給服務器，所以當查詢語句很長的時候，需要設置max_allowed_packet參數。但是需要注意的是，如果查詢實在是太大，服務端會拒絕接收更多數據并拋出異常。
與之相反的是，服務器響應給用戶的數據通常會很多，由多個數據包組成。但是當服務器響應客戶端請求時，客戶端必須完整的接收整個返回結果，而不能簡單的只取前面幾條結果，然后讓服務器停止發送。因而在實際開發中，盡量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。
查詢緩存
在解析一個查詢語句前，如果查詢緩存是打開的，那么MySQL會檢查這個查詢語句是否命中查詢緩存中的數據。如果當前查詢恰好命中查詢緩存，在檢查一次用戶權限后直接返回緩存中的結果。這種情況下，查詢不會被解析，也不會生成執行計劃，更不會執行。
MySQL將緩存存放在一個引用表（不要理解成table，可以認為是類似于HashMap的數據結構），通過一個哈希值索引，這個哈希值通過查詢本身、當前要查詢的數據庫、客戶端協議版本號等一些可能影響結果的信息計算得來。所以兩個查詢在任何字符上的不同（例如：空格、注釋），都會導致緩存不會命中。
如果查詢中包含任何用戶自定義函數、存儲函數、用戶變量、臨時表、MySQL庫中的系統表，其查詢結果都不會被緩存。比如函數NOW()或者CURRENT_DATE()會因為不同的查詢時間，返回不同的查詢結果，再比如包含CURRENT_USER或者CONNECION_ID()的查詢語句會因為不同的用戶而返回不同的結果，將這樣的查詢結果緩存起來沒有任何的意義。
既然是緩存，就會失效，那查詢緩存何時失效呢？MySQL的查詢緩存系統會跟蹤查詢中涉及的每個表，如果這些表（數據或結構）發生變化，那么和這張表相關的所有緩存數據都將失效。正因為如此，在任何的寫操作時，MySQL必須將對應表的所有緩存都設置為失效。如果查詢緩存非常大或者碎片很多，這個操作就可能帶來很大的系統消耗，甚至導致系統僵死一會兒。而且查詢緩存對系統的額外消耗也不僅僅在寫操作，讀操作也不例外：
- 任何的查詢語句在開始之前都必須經過檢查，即使這條SQL語句永遠不會命中緩存
- 如果查詢結果可以被緩存，那么執行完成后，會將結果存入緩存，也會帶來額外的系統消耗
基于此，我們要知道并不是什么情況下查詢緩存都會提高系統性能，緩存和失效都會帶來額外消耗，只有當緩存帶來的資源節約大于其本身消耗的資源時，才會給系統帶來性能提升。但要如何評估打開緩存是否能夠帶來性能提升是一件非常困難的事情，也不在本文討論的范疇內。如果系統確實存在一些性能問題，可以嘗試打開查詢緩存，并在數據庫設計上做一些優化，比如：
- 用多個小表代替一個大表，注意不要過度設計
- 批量插入代替循環單條插入
- 合理控制緩存空間大小，一般來說其大小設置為幾十兆比較合適
- 可以通過SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進行緩存
最后的忠告是不要輕易打開查詢緩存，特別是寫密集型應用。如果你實在是忍不住，可以將query_cache_type設置為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被緩存。
當然查詢緩存系統本身是非常復雜的，這里討論的也只是很小的一部分，其他更深入的話題，比如：緩存是如何使用內存的？如何控制內存的碎片化？事務對查詢緩存有何影響等等，讀者可以自行閱讀相關資料，這里權當拋磚引玉吧。
語法解析和預處理
MySQL通過關鍵字將SQL語句進行解析，并生成一顆對應的解析樹。
這個過程解析器主要通過語法規則來驗證和解析。比如SQL中是否使用了錯誤的關鍵字或者關鍵字的順序是否正確等等。預處理則會根據MySQL規則進一步檢查解析樹是否合法。比如檢查要查詢的數據表和數據列是否存在等。
SQL總體執行流程圖
查詢優化
經過前面的步驟生成的語法樹被認為是合法的了，并且由優化器將其轉化成查詢計劃。多數情況下，一條查詢可以有很多種執行方式，最后都返回相應的結果。優化器的作用就是找到這其中最好的執行計劃。
MySQL使用基于成本的優化器，它嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。在MySQL可以通過查詢當前會話的last_query_cost的值來得到其計算當前查詢的成本。
數據庫事務的概念及其實現原理
數據庫事務（Database Transaction）概述
什么是事務？
- 轉賬的例子
- 1.轉賬操作的第一步執行成功,A賬戶上的錢減少了100元,但是第二步執行失敗或者未執行便發生系統崩潰,導致B賬戶并沒有相應增加100元。
- 2.轉賬操作剛完成就發生系統崩潰,系統重啟恢復時丟失了崩潰前的轉賬記錄。
- 3.同時又另一個用戶轉賬給B賬戶,由于同時對B賬戶進行操作,導致B賬戶金額出現異常。
- 1.將A賬戶的金額減少100元
- 2.將B賬戶的金額增加100元。
- 從A賬戶轉賬100元到B賬號。站在用戶角度而言,這是一個邏輯上的單一操作,然而在數據庫系統中,至少會分成兩個步驟來完成:
- 在這個過程中可能會出現以下問題:
- 為了便于解決這些問題,需要引入數據庫事務的概念。
- 定義
- 數據庫管理系統執行過程中的一個邏輯單位，由一個有限的數據庫操作序列構成。
- 必須滿足ACID屬性
- 例子
- 一個典型的數據庫事務如下所示
BEGIN TRANSACTION //事務開始 SQL1 SQL2 COMMIT/ROLLBACK //事務提交或回滾
為什么要有事務？
- 為數據庫操作序列提供了一個從失敗中恢復到正常狀態的方法，同時提供了數據庫即使在異常狀態下仍能保持一致性的方法。
- 當多個應用程序在并發訪問數據庫時，可以在這些應用程序之間提供一個隔離方法，以防止彼此的操作互相干擾。
ACID 特性
- 原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對數據庫的操作要么全部被執行，要么都不執行。
- 一致性（Consistency）：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態，一致狀態的含義是數據庫中的數據應滿足完整性約束。
- 隔離性（Isolation）：多個事務并發執行時，一個事務的執行不應影響其他事務的執行。
- 持久性（Durability）：已被提交的事務對數據庫的修改應該永久保存在數據庫中。
隔離級別
- 四個級別
- Read Uncommitted 讀未提交：就是一個事務可以讀取另一個未提交事務的數據。
- Read Committed 讀提交：就是一個事務要等另一個事務提交后才能讀取數據。若有事務對數據進行更新（UPDATE）操作時，讀操作事務要等待這個更新操作事務提交后才能讀取數據，可以解決臟讀問題。
- Repeatable Read 重復讀：就是在開始讀取數據（事務開啟）時，不再允許修改操作。重復讀可以解決不可重復讀問題。寫到這里，應該明白的一點就是，不可重復讀對應的是修改，即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作，而不是UPDATE操作。
- Serializable 順序讀:是最高的事務隔離級別，在該級別下，事務串行化順序執行，可以避免臟讀、不可重復讀與幻讀。但是這種事務隔離級別效率低下，比較耗數據庫性能，一般不使用。
- mysql 對應的InnoDB默認隔離級別是 重復讀
- 所有事務隔離級別都不允許出現臟寫,而串行化可以避免所有可能出現的并發異常,但是會極大的降低系統的并發處理能力。
- 共享鎖與排它鎖
- 排它鎖
- 共享鎖
- 讀
- 寫
- 隔離怎么實現？
- 共享鎖（S鎖） ：（插入/修改/刪除）資源獲取S鎖之后，能加S鎖，不能加X鎖
- 排它鎖（X鎖） ：資源加上X鎖之后，不能加S鎖，也不能加X鎖
- 鎖
- InnoDB存在兩種鎖
數據庫事務實現原理剖析
事務的實現原理
- 事務的執行過程
- 系統會為每個事務開辟一個私有工作區
- 事務讀操作將從磁盤中拷貝數據項到工作區中,在執行寫操作前所有的更新都作用于工作區中的拷貝.
- 事務的寫操作將把數據輸出到內存的緩沖區中,再由緩沖區管理器將數據寫入到磁盤。
- 分特性
- 鎖
- MVCC 多版本并發控制
- 人們一般把基于鎖的并發控制機制稱成為悲觀機制，而把MVCC機制稱為樂觀機制。這是因為鎖機制是一種預防性的，讀會阻塞寫，寫也會阻塞讀，當鎖定粒度較大，時間較長時并發性能就不會太好；而MVCC是一種后驗性的，讀不阻塞寫，寫也不阻塞讀，等到提交的時候才檢驗是否有沖突，由于沒有鎖，所以讀寫不會相互阻塞，從而大大提升了并發性能。
- 通過增加系統版本號，每次事務操作，會比較系統版本號
- InnoDB為每行記錄添加了一個版本號（系統版本號），每當修改數據時，版本號加一。在讀取事務開始時，系統會給事務一個當前版本號，事務會讀取版本號<=當前版本號的數據，這時就算另一個事務插入一個數據，并立馬提交，新插入這條數據的版本號會比讀取事務的版本號高，因此讀取事務讀的數據還是不會變。
- MVCC 是什么？
- 基于CAS（Compare-and-swap）
- 有條件更新（Conditional Update）
- 通過undo log 來實現
- 原子性是事務的基本特性，保證了事務中的操作是不可拆分的整體，那么原子性是如何實現的呢？事務的原子性表現的兩個方面：
- 通過redo log 來實現
- 事務提交失敗，那么事務中的操作都失敗，這個是通過數據庫的撤銷操作日志來保證的，也稱之為undo log。
- 事務提交成功保證事務中的操作都會完成。1、是正確執行完事務，沒有出現任何問題；2、是事務提交成功但是出異常，數據庫恢復之后，提交完成的事務會保證數據庫完成該事物的操作。對于第一種正常情況不予討論，因為不存在 異常情況，那么第2種實際上是和上文說的持久性是相關聯的，而這個是基于重做日志（redo log）來保證提交完成的事務在異常情況下保證數據操作能夠進行：
- 事務提交成功時，那么事務中的操作總會完成
- 事務提交失敗，那么事務中的操作都失敗
- 原子性
- 一致性
- 隔離性
- Undo原理 與 Redo原理
- 和Undo Log相反，Redo Log記錄的是新數據的備份。在事務提交前，只要將Redo Log持久化即可，不需要將數據持久化。當系統崩潰時，雖然數據沒有持久化，但是Redo Log已經持久化。系統可以根據Redo Log的內容，將所有數據恢復到最新的狀態。
- 在操作任何數據之前，首先將數據備份到一個地方（這個存儲數據備份的地方稱為Undo Log）。然后進行數據的修改。如果出現了錯誤或者用戶執行了ROLLBACK語句，系統可以利用Undo Log中的備份將數據恢復到事務開始之前的狀態。
- Undo原理：（備份舊數據）
- Redo原理：（保存最新數據）
