一、基礎架構
MySQL 可以分為 Server 層和儲存引擎層兩部分。
Server 層包括聯結器、查詢快取、分析器、優化器、執行器等，所有跨儲存引擎
的功能都在這一層實現，比如儲存過程、觸發器、檢視等。
而儲存引擎層負責資料的儲存和提取。支援 InnoDB、MyISAM等多個儲存引擎。最常用的儲存引擎是 InnoDB，從 MySQL 5.5.5 版本開始成為了預設儲存引擎。
1.1 聯結器
首先，聯結器負責跟客戶端建立連線、獲取許可權、維持和管理連線。連線命令一般是
mysql -h 11.28.19.14 -P 3306 -u xiaoming -p
在完成經典的 TCP 握手後，聯結器就要開始認證你的身份，這個時候用的就是你輸入的使用者名稱和密碼。連線完成後，如果你沒有後續的動作，這個連線就處於空閒狀態，你可以在 show processlist 命令中看到它
資料庫裡面，長連線是指連線成功後，如果客戶端持續有請求，則一直使用同一個連線。短
連線則是指每次執行完很少的幾次查詢就斷開連線，下次查詢再重新建立一個。
建立連線的過程通常是比較複雜的，建議使用中要儘量減少建立連線的動作，也就是儘量使用長連線。
但全部使用長連線後，可能會發現，有些時候 MySQL 佔用記憶體漲得特別快，是因為 MySQL 在執行過程中臨時使用的記憶體是管理在連線物件裡面的。這些資源會在連線斷開的時候才釋放。所以如果長連線累積下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM），從現象看就是 MySQL 異常重啟了。
Java中運用的資料庫連線池就是這麼個辦法原理或者及時關閉。還有就是MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行mysql_reset_connection 來重新初始化連線資源。會將連線恢復到剛剛建立完時的狀態。
1.2 查詢快取
連線建立後，就可以執行語句了。
MySQL 拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以 key-value 對的形式，被直接快取在記憶體中。key 是查詢的語句，value 是查詢的結果。如果你的查詢能夠直接在這個快取中找到 key，那麼這個value 就會被直接返回給客戶端。
但是大多數情況下建議不要使用查詢快取，因為查詢快取往往弊大於利。
詢快取的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢快取都會被清空。因此可能費勁地把結果存起來，還沒使用就被一個更新全清空了。對於更新大的資料庫來說，查詢快取的命中率會非常低。除非是有一張靜態表，很長時間更新一次。比如，一個系統配置表
引數 query_cache_type 設定成 DEMAND，這樣對於預設的 SQL 語句都不使用查詢快取。
對於確定要使用查詢快取的語句，可以用 SQL_CACHE 顯式指定select SQL_CACHE * from T where ID=10；
MySQL 8.0 版本直接將查詢快取的整塊功能刪掉了，也就是說 8.0 開始徹底沒有這個功能了
1.3 分析器
如果沒有命中查詢快取，就要開始真正執行語句了。
分析器先會做“詞法分析”。你輸入的是由多個字串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字串分別是什麼，代表什麼。
MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字串“T”識別成“表名 T”，把字串“ID”識別成“列 ID”。
做完了這些識別以後，就要做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足MySQL 語法。
1.4 優化器
優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連線順序。比如：
mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
既可以先從表 t1 裡面取出 c=10 的記錄的 ID 值，再根據 ID 值關聯到表 t2，再判斷 t2裡面 d 的值是否等於 20。
也可以先從表 t2 裡面取出 d=20 的記錄的 ID 值，再根據 ID 值關聯到 t1，再判斷 t1 裡面 c 的值是否等於 10。
兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案
具體優化後面講，優化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段。
1.5 執行器
開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤，如下所示 (在工程實現上，如果命中查詢快取，會在查詢快取返回結果的時候，做許可權驗證。查詢也會在優化器之前呼叫 precheck 驗證許可權)。
select * from T where ID=10;
這個例子中的表 T 中，ID 欄位沒有索引，那麼執行器的執行流程是這樣的：
1.呼叫 InnoDB 引擎介面取這個表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結果集中；
2.呼叫引擎介面取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行。
3.執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端
對於有索引的表，執行的邏輯也差不多。第一次呼叫的是“取滿足條件的第一行”這個介面，之後迴圈取“滿足條件的下一行”這個介面，這些介面都是引擎中已經定義好的。
資料庫的慢查詢日誌中看到一個 rows_examined 的欄位，表示這個語句執行過程中掃描了多少行。這個值就是在執行器每次呼叫引擎獲取資料行的時候累加的
有些場景下，執行器呼叫一次，在引擎內部則掃描了多行，因此引擎掃描行數跟rows_examined 並不是完全相同的。後面會專門講儲存引擎的內部機制，會有詳細的說明。
二、日誌系統
前面介紹過 SQL 語句基本的執行鏈路。查詢語句的那一套流程，更新語句也是同樣會走一遍。在一個表上有更新的時候，跟這個表有關的查詢快取會失效，所以這條語句就會把表 T 上所有快取結果都清空。這也就是我們一般不建議使用查詢快取的原因。
更新與查詢流程不一樣的是，更新流程還涉及兩個重要的日誌模組，主角：redo log（重做日誌）和 binlog（歸檔日誌）。
2.1 redo log
Redo log不是記錄資料頁“更新之後的狀態”，而是記錄這個頁 “做了什麼改動”
2.1.1 原因
MySQL 裡有一個問題，如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程 IO 成本、查詢成本都很高。
2.1.2 解決思路
為了解決這個問題，MySQL 的設計者就用了WAL的思路來提升更新效率，WAL 的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日誌，再寫磁碟，也就是先寫到日誌，等系統比較空閒的時候再寫磁碟。
2.1.3 具體解決方案
InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個檔案，每個檔案的大小是 1GB，那麼這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭迴圈寫，如下面這個圖所示：
2.1.3.1 write pos
write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號檔案末尾後就回到 0 號檔案開頭。checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案。
2.1.3.2 checkpoint
write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把 checkpoint 推進一下。
2.1.3.3 crash-safe
有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe
2.3 redo log和bin log區別與由來
2.3.1 原因
最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是MyISAM 沒有 crash-safe 的能力，binlog 日誌只能用於歸檔。而 InnoDB 是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB使用另外一套日誌系統——也就是 redo log 來實現 crash-safe 能力。
2.3.2 區別
1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。
2.redo log 是物理日誌，記錄的是“在某個資料頁上做了什麼修改”；binlog 是邏輯日誌，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”。
3.redo log 是迴圈寫的，空間固定會用完；binlog 是可以追加寫入的。“追加寫”是指binlog 檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌
3.2 binlog
binlog 會記錄所有的邏輯操作，並且是採用“追加寫”的形式。
有兩種模式，statement 格式的話是記sql語句， row格式會記錄行的內容，記兩條，更新前和更新後都有
如果你的 DBA 承諾說半個月內可以恢復，那麼備份系統中一定會儲存最近半個月的所有binlog，同時系統會定期做整庫備份。這裡的“定期”取決於系統的重要性，可以是一天一備，也可以是一週一備
3.2.1 update 語句執行流程
mysql> update T set c=c+1 where ID=1;
最後三步看上去有點“繞”，將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"
3.2.2 兩階段提交
由於 redo log 和 binlog 是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完 redolog 再寫 binlog，或者採用反過來的順序。我們看看這兩種方式會有什麼問題。
1.先寫 redo log 後寫 binlog
假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 程式異常重啟。由於我們前面說過的，redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行 c 的值是 1
但是由於 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。因此，
之後備份日誌的時候，存起來的 binlog 裡面就沒有這條語句
2.先寫 binlog 後寫 redo log
如果在 binlog 寫完之後 crash，由於 redo log 還沒寫，崩潰恢復以後這個事務無效，所以這一行 c 的值是 0。
但是 binlog 裡面已經記錄了“把c 從 0 改成 1”這個日誌。所以，在之後用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同
3.總結
當你需要擴容的時候，也就是需要再多搭建一些備庫來增加系統的讀能力的時候，現在常見的做法也是用全量備份加上應用binlog 來實現的，這個“不一致”就會導致你的線上出現主從資料庫不一致的情況。
簡單說，redo log 和 binlog 都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致
3.2.3 資料庫恢復
當需要恢復到指定的某一秒時，比如某天下午兩點發現中午十二點有一次誤刪表，需要找回
資料，那你可以這麼做：
1.首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上的一個備份，從這個備份恢復到臨時庫；
2.然後，從備份的時間點開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個時刻
3.這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢復到線上庫去。