出處：https://www.cnblogs.com/wtzbk/p/14410608.html

一個查詢語句經過哪些步驟

這次我們從MySQL的整體架構來講SQL的執行過程，如下圖:

在整體分為兩部分Server和引擎層，這裡引擎層我使用InnoDB去代替，引擎層的設計是外掛形式的，可以任意替代，接下來我們開始介紹每個元件的作用:

Server層

聯結器:聯結器負責跟客戶端建立連線、獲取許可權、維持和管理連線；

查詢快取:服務的查詢快取，如果能找到對應的查詢，則不必進行查詢解析，最佳化，執行等過程，直接返回快取中的結果集；

解析器:解析器會根據查詢語句，構造出一個解析樹，主要用於根據語法規則來驗證語句是否正確，比如SQL的關鍵字是否正確，關鍵字的順序是否正確;

最佳化器:解析樹轉化為查詢計劃，一般情況下，一條查詢可以有很多種執行方式，最終返回相同的結果，最佳化器就是根據成本找到這其中最優的執行計劃;

執行器:執行計劃呼叫查詢執行引擎，而查詢引擎透過一系列API介面查詢到資料;

InnoDB

後臺執行緒:負責重新整理記憶體池中的資料，保證快取池中的記憶體快取是最近的資料，將已修改的資料重新整理到磁碟檔案，同時保證資料庫發生異常的情況能恢復到正常情況；

記憶體池:記憶體池也可以叫做快取池，主要為彌補磁碟的速度較慢對資料庫產生的影響，查詢的時候，首先將磁碟讀到的頁的資料放在記憶體池中，下次讀取的時候直接從記憶體池中讀取資料，修改資料的時候，首先修改記憶體池中的資料，然後後臺執行緒按照一定的頻率重新整理到磁碟上。

檔案:主要是指表空間檔案，而外還有一些日誌檔案；

以上大致的介紹一下MySQL的整體架構，其中記憶體池、檔案、後臺執行緒等一些跟細節的東西沒有介紹，後面我們介紹其他時候在帶出來其中的詳細的部分，另外在附上一張MySQL5.6整體架構圖：

InnoDB如何儲存資料

這部分內容是建立在上部分的基礎上，需要對記憶體池、檔案、後臺執行緒深入到細節去了解組成，接下我們還是分三部分開始講解:

檔案

檔案分為日誌檔案和儲存檔案，分為兩部分講起:

儲存檔案

儲存檔案也就是表資料的儲存，整體的儲存結構如下圖:

表空間主要分為兩類檔案，一類是共享表空間，一類是每張表單獨的表空間，單獨的表空間存放的是表中的資料、索引等資訊，共享的表空間主要是儲存事務資訊、回滾資訊等資料；表空間由段(Segment)、區(Extend)、頁(Page)、行(Row)組成，接下來簡單介紹一下這4種結構:

段(Segment)常見的Segment有資料段、索引段、回滾段等， 資料段為B+樹的葉子節點(Leaf node segment)、索引段為B+樹的非葉子節點(Non-leaf node segment)。如下圖：每建立索引就會建立一個索引段，索引段的葉子節點指向資料段，透過這樣的組合來完成我們查詢資料時候需要，因此建立索引越多，會導致需要構建的索引段就越多，導致插入資料時間就會增加。區(Extend)區是構成段的基本元素，一個段由若干個區構成，一個區是物理上連續分配的一段空間，每一個段至少會有一個區，在建立一個段時會建立一個預設的區。如果儲存資料時，一個區已經不足以放下更多的資料，此時需要從這個段中分配一個新的區來存放新的資料。一個段所管理的空間大小是無限的，可以一直擴充套件下去，但是擴充套件的最小單位就是區。每個區大小固定為1MB，區由頁組成，為保證區中Page的連續性通常InnoDB會一次從磁碟中申請4-5個區。在預設Page的大小為16KB的情況下，一個區則由64個連續的Page組成。頁(Page):頁是構成區的基本單位，是InnoDB磁碟管理的最小單位。在邏輯上（頁面號都是從小到大連續的）及物理上都是連續的。在向表中插入資料時，如果一個頁面已經被寫完，系統會從當前區中分配一個新的空閒頁面處理使用，如果當前區中的64個頁面都被分配完，系統會從當前頁面所在段中分配一個新的區，然後再從這個區中分配一個新的頁面來使用。行(Row):InnoDB按照行進行存放資料，每個頁存放的資料有硬性規定，最多存放16KB，當資料大於16KB的時候會發生行溢位，會儲存到而外的頁(Uncompressed BLOB Page)當中。日誌檔案

關於日誌檔案這裡主要介紹三種日誌檔案，分別為binlog、redo log、redo log:

binlog

binlog用於記錄資料庫執行的寫入性操作(不包括查詢)資訊，以二進位制的形式儲存在磁碟中。binlog是mysql的邏輯日誌，並且由Server層進行記錄，使用任何儲存引擎的mysql資料庫都會記錄binlog日誌。binlog是透過追加的方式進行寫入的，可以透過max_binlog_size引數設定每個binlog檔案的大小，當檔案大小達到給定值之後，會生成新的檔案來儲存日誌。

binlog日誌格式ROW基於行的複製，不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了。優點:不會出現某些特定情況下的儲存過程、或function、或trigger的呼叫和觸發無法被正確複製的問題；缺點:因為每行都要記錄日誌，會照成日誌量暴漲；STATMENT基於SQL語句的複製，每一條會修改資料的sql語句會記錄到binlog中。優點:不需要記錄每一行的變化，減少了binlog日誌量，節約了IO, 從而提高了效能；缺點:在某些情況下會導致主從資料不一致，比如執行sysdate()等函式的時候。MIXED基於STATMENT和ROW兩種模式的混合複製，一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog使用場景

binlog的主要使用場景有兩個，分別是主從複製和資料恢復;

主從複製在Master端開啟binlog，然後將binlog傳送到各個Slave端，Slave端重放binlog從而達到主從資料一致。資料恢復恢復到某一時刻的日誌，透過使用mysqlbinlog工具來恢復資料；刷盤時機

對於InnoDB儲存引擎而言，只有在事務提交時才會記錄biglog，此時記錄還在記憶體中，Mysql透過sync_binlog引數控制biglog的刷盤時機，取值範圍是0-N，

N代表多少條以後開始進行刷盤，當設定為0的時候由系統自行判斷何時寫入磁碟，當設定為1的時候，相當於每次Commit就進行刷盤一次，但是這個時候要注意與redo log日誌可能存在不一致的情況，這個時候需要設定innodb_support_xa引數也為1，這樣就能保證兩個兩份日誌是同步的。

redo log

redo log包括兩部分:redo log buffer和redo log file,redo log buffer是在記憶體中，redo log file是在磁碟上，當MySQL執行DML語句的時候，首先寫入redo log buffer，然後按照一定條件順序寫入redo log file，什麼時候會觸發buffer內容寫入到file當中呢？

InnoDB後臺執行緒中的主執行緒，每秒會進行一次將buffer中的資料刷入到磁碟當中；透過設定innodb_flush_log_at_trx_commit引數，來控制重新整理的時機，當設定為 1 的時候，事務每次提交都會將 log buffer 中的日誌寫入 os buffer 並呼叫 fsync()刷到 log file on disk中。這種方式即使系統崩潰也不會丟失任何資料，但是因為每次提交都寫入磁碟，IO 的效能較差。當設定為 0 的時候，事務提交時不會將 log buffer 中日誌寫入到 os buffer，而是每秒寫入 os buffer 並呼叫fsync()寫入到 log file on disk 中。也就是說設定為 0 時是(大約)每秒重新整理寫入到磁碟中的，當系統崩潰，會丟失 1 秒鐘的資料。當設定為 2 的時候，每次提交都僅寫入到 os buffer，然後是每秒呼叫 fsync() 將 os buffer 中的日誌寫入到 log file on disk。redo log日誌格式

redo log記錄資料頁的變更，在設計上redo log採用了大小固定，迴圈寫入的方式，當寫到結尾時，會回到開頭迴圈寫日誌，本質上就是一個環狀。

rdo log刷盤完成以後，其實資料最終還沒重新整理到真正資料磁碟上，因此還需要重新整理到真正的資料磁碟上，本質上redo log的設計就是為了降低對資料頁刷盤的要求，接下來我們結合上圖來聊聊是如何重新整理到資料檔案檔案上的,也就是checkpoint機制:

首先看下環，環上有4個ib_logfile_*的檔案，該檔案就是儲存redo log日誌的檔案，可以透過控制innodb_log_files_in_group的數量來控制檔案的個數，透過innodb_log_file_size來控制檔案的大小，不介意將檔案的設定的太大，如果設定的太大會導致奔潰恢復的時候過於緩慢，也不能設定的太小，這樣可能導致一次事務需要切換多次日誌檔案，此外還會照成頻繁寫入磁碟檔案，照成效能抖動；

接下來我們看兩個端點write pos和check point,write pos到check point之間的部分是redo log空著的部分，用於記錄新的記錄；check point到write pos之間是redo log待落盤的資料頁更改記錄。當write pos追上check point時，會先推動check point向前移動，空出位置再記錄新的日誌。

InnoDB在啟動的時候，不管上次資料庫是否正常關閉，都會嘗試進行恢復操作，分為兩種情況:

checkpoint表示已經完整刷到磁碟上data page上的LSN，因此恢復時僅需要恢復從checkpoint開始的日誌部分，LSN表示寫入日誌的位元組的總量，例如，當資料庫在上一次checkpoint的LSN為10000時宕機，且事務是已經提交過的狀態。啟動資料庫時會檢查磁碟中資料頁的LSN，如果資料頁的LSN小於日誌中的LSN，則會從檢查點開始恢復。在宕機前正處於checkpoint的刷盤過程，且資料頁的刷盤進度超過了日誌頁的刷盤進度。這時候一宕機，資料頁中記錄的LSN就會大於日誌頁中的LSN，在重啟的恢復過程中會檢查到這一情況，這時超出日誌進度的部分將不會重做，因為這本身就表示已經做過的事情，無需再重做。

在恢復的過程中因為redo log記錄的是資料頁的物理變化，因此恢復的時候速度比邏輯日誌(如binlog)要快很多；

使用的場景

MySQL用來確保事務的永續性。redo log記錄事務執行後的狀態，用來恢復未寫入data file的已成功事務更新的資料。防止在發生故障的時間點，尚有髒頁未寫入磁碟，在重啟mysql服務的時候，根據redo log進行重做，從而達到事務的永續性這一特性。

undo log

undo log記錄資料的邏輯變化，使用者事務的回滾操作和MVCC, undo log 存放在共享表空間中，以段(rollback segment)的形式存在。

undo log日誌格式

邏輯格式的日誌，在事務進行回滾的時候，可以將資料從邏輯上恢復至事務之前的狀態。

使用的場景

保證資料的原子性，儲存了事務發生之前的資料的一個版本，可以用於回滾，同時可以提供多版本併發控制下的讀（MVCC），也即非鎖定讀。

記憶體池

InnoDB 儲存引擎是基於磁碟儲存的,也就是說資料都是儲存在磁碟上的,由於 CPU 速度和磁碟速度之間的鴻溝, InnoDB 引擎使用緩衝池技術來提高資料庫的整體效能。記憶體池簡單來說就是一塊記憶體區域.在資料庫中進行讀取頁的操作,首先將從磁碟讀到的頁存放在記憶體池中,下一次讀取相同的頁時,首先判斷該頁是不是在記憶體池中,若在,稱該頁在記憶體池中被命中,直接讀取該頁。否則,讀取磁碟上的頁。對於資料庫中頁的修改操作,首先修改在記憶體池中頁,然後再以一定的頻率重新整理到磁碟,並不是每次頁發生改變就重新整理回磁碟。

記憶體池中快取的資訊主要有:index page、data page、insert buffer、自適應雜湊索引、 lock info、資料字典資訊等。索引頁和資料頁佔緩衝池的很大一部分。在InnoDB中，記憶體池中的頁大小預設為16KB，和磁碟的頁的大小預設一樣。我們已經介紹過資料檔案的儲存結構相信大家對快取結構的內容也會有一定理解，我們就不單獨介紹了，後面只會重點強調一下insert buffer和自適應雜湊索引這兩塊內容，以及擴充套件下記憶體池的設計原理。

Insert Buffer

Insert Buffer的設計，對於非聚集索引的插入和更新操作，不是每一次直接插入到索引頁中，而是先判斷插入非聚集索引頁是否在緩衝池中，若存在，則直接插入，不存在，則先放入一個Insert Buffer物件中。資料庫這個非聚集的索引已經插到葉子節點，而實際並沒有，只是存放在另一個位置。然後再以一定的頻率和情況進行Insert Buffer和輔助索引頁子節點的merge（合併）操作，這時通常能將多，這就大大提高了對於非聚集索引插入的效能。這個時候可能會照成一種情況，當MySQL資料庫發生宕機的時候有有大量的Insert Buffer沒有被合併到非聚集索引的頁當中的時候，這個時候MySQL恢復需要很長的時間。

需要滿足的條件:

索引是非聚集索引，索引不是唯一的；

對於具體的實現我們下次再聊；

自適應雜湊索引

InnoDB儲存引擎會監控對錶上各索引頁的查詢。如果觀察到建立雜湊索引可以提升速度，這簡歷雜湊索引，稱之為自適應雜湊索引。AHI是透過緩衝池的B+樹頁構造而來的。因此建立的速度非常快，且不要對整張表構建雜湊索引。InnoDB儲存引擎會自動根據訪問的頻率和模式來自動的為某些熱點頁建立雜湊索引。

後臺執行緒Master Thread

這是最核心的一個執行緒,主要負責將緩衝池中的資料非同步重新整理到磁碟,保證資料的一致性,包括贓頁的重新整理、合併插入緩衝等。

IO Thread

在 InnoDB 儲存引擎中大量使用了非同步 IO 來處理寫 IO 請求, IO Thread 的工作主要是負責這些 IO 請求的回撥處理。

Purge Thread

事務被提交之後, undo log 可能不再需要,因此需要 Purge Thread 來回收已經使用並分配的 undo頁. InnoDB 支援多個 Purge Thread, 這樣做可以加快 undo 頁的回收。

完成整體功能介紹以後，我們開始聊聊資料如何插入到InnoDB引擎上的:

假設場景如下:

首先我們建立一張表T，主鍵為Id，輔助索引為a

create table T(id int primary key, a int not null, name varchar(16),index (a))engine=InnoDB;

接下來插入一條資料，

insert into t(id,a,name) values(id1,a1,'哈哈'),(id2,a2,'哈哈哈');

我們介紹過MySQL讀取資料的流程，Server層我們還是會經過聯結器、解析器、最佳化器、執行器這些東西，這些我們就不介紹了，我們主要介紹剩下的操作：

插入資料時候可能有兩種場景:

第一種場景:假設Id1這條資料在記憶體池中,

直接更新Buffer Pool中的Index Page和Data Page;寫入redo log中，處於預提交狀態；寫入binlog中，提交事務，處於commit狀態，兩階段提交；後臺執行緒寫入到資料檔案的索引段和資料段中;

第二種場景假設id2這條資料不再記憶體池中,

資料寫入到記憶體池中，非聚集索引寫入到Insert Buffer，其他資料寫入Data Page中;後續的動作保持和上面剩下的步驟一樣。擴充套件閱讀

我們來聊聊記憶體池(Buffer Pool)執行原理，可以從以下3個方面來看:

如何管理快取的頁？InnoDB為每一個快取頁都建立了一些控制資訊，這些控制資訊包括該頁所屬的表空間編號、頁號、頁在Buffer Pool中的地址、LSN等資訊，每個快取頁對應的控制資訊佔用的記憶體大小是相同的，我們就把每個頁對應的控制資訊佔用的一塊記憶體稱為一個控制塊吧，控制塊和快取頁是一一對應的，它們都被存放到 Buffer Pool 中，其中控制塊被存放到 Buffer Pool 的前邊，快取頁被存放到 Buffer Pool 後邊，所以整個Buffer Pool對應的記憶體空間看起來就是這樣的：碎片就是空間不夠分配的快取頁。當我們最初啟動MySQL伺服器的時候，需要完成對Buffer Pool的初始化過程，就是分配Buffer Pool的記憶體空間，把它劃分成若干對控制塊和快取頁。但是此時並沒有真實的磁碟頁被快取到Buffer Pool中，之後隨著程式的執行，會不斷的有磁碟上的頁被快取到Buffer Pool中，接下來會有一個問題就是怎麼區分Buffer Pool中哪些快取頁是空閒的，哪些已經被使用？我們最好在某個地方記錄一下哪些頁是可用的，我們可以把所有空閒的頁包裝成一個節點組成一個連結串列，這個連結串列也可以被稱作Free連結串列。因為剛剛完成初始化的Buffer Pool中所有的快取頁都是空閒的，所以每一個快取頁都會被加入到Free連結串列中，整體設計如下圖：從圖中可以看出，Free連結串列包含著連結串列的頭節點地址，尾節點地址，以及當前連結串列中節點的數量等資訊。每個Free連結串列的節點中都記錄了某個快取頁控制塊的地址，而每個快取頁控制塊都記錄著對應的快取頁地址，所以相當於每個Free連結串列節點都對應一個空閒的快取頁。每當需要從磁碟中載入一個頁到Buffer Pool中時，就從Free連結串列中取一個空閒的快取頁，並且把該快取頁對應的控制塊的資訊填上，然後把該快取頁對應的Free連結串列節點從連結串列中移除，表示該快取頁已經被使用了。快取的淘汰?機器的記憶體大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同樣是有限的，如果需要快取的頁佔用的記憶體大小超過了Buffer Pool大小,InnoDB Buffer Pool採用經典的LRU演算法來進行頁面淘汰，以提高快取命中率。當Buffer Pool中不再有空閒的快取頁時，就需要淘汰掉部分最近很少使用的快取頁。當我們需要訪問某個頁時，可以這樣處理LRU連結串列：1.如果該頁不在Buffer Pool中，在把該頁從磁碟載入到Buffer Pool中的快取頁時，就把該快取頁包裝成節點塞到連結串列的頭部。2.如果該頁在Buffer Pool中，則直接把該頁對應的LRU連結串列節點移動到連結串列的頭部。但是這樣做會有一些效能上的問題，比如你的一次全表掃描或一次邏輯備份就把熱資料給衝完了，就會導致導致緩衝池汙染問題！Buffer Pool中的所有資料頁都被換了一次血，其他查詢語句在執行時又得執行一次從磁碟載入到Buffer Pool的操作，而這種全表掃描的語句執行的頻率也不高，每次執行都要把Buffer Pool中的快取頁重新整理一次，這嚴重的影響到其他查詢對 Buffer Pool 的使用，降低了快取命中率。針對這種場景InnoDB儲存引擎對傳統的LRU演算法做了一些最佳化，在InnoDB中加入了midpoint。新讀到的頁，雖然是最新訪問的頁，但並不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。這個演算法稱之為midpoint insertion stategy。預設配置插入到列表長度的5/8處。midpoint由引數innodb_old_blocks_pct控制。midpoint之前的列表稱之為new列表，之後的列表稱之為old列表。可以簡單的將new列表中的頁理解為最為活躍的熱點資料。髒頁如何實現重新整理?更新是在快取池中先進行的，那它就和磁碟上的頁不一致了，這樣的快取頁也被稱為髒頁。所以需要考慮這些被修改的頁面什麼時候重新整理到磁碟？當然，最簡單的做法就是每發生一次修改就立即同步到磁碟上對應的頁上，但是頻繁的往磁碟中寫資料會嚴重的影響程式的效能。所以每次修改快取頁後，我們並不著急立即把修改同步到磁碟上，而是在未來的某個時間點進行同步，由後臺重新整理執行緒依次重新整理到磁碟，實現修改落地到磁碟。但是如果不立即同步到磁碟的話，那之後再同步的時候我們怎麼知道Buffer Pool中哪些頁是髒頁，哪些頁從來沒被修改過呢？我們需要建立一個儲存髒頁的連結串列，凡是在LRU連結串列中被修改過的頁都需要加入這個連結串列中，因為這個連結串列中的頁都是需要被重新整理到磁碟上的，所以也叫Flush連結串列，連結串列的構造和Free連結串列差不多，這裡的髒頁修改指的此頁被載入進Buffer Pool後第一次被修改，只有第一次被修改時才需要加入Flush連結串列，如果這個頁被再次修改就不會再放到Flush連結串列了，因為已經存在。需要注意的是，髒頁資料實際還在LRU連結串列中，而Flush連結串列中的髒頁記錄只是透過指標指向LRU連結串列中的髒頁。

作者：大魔王先生

出處：https://www.cnblogs.com/wtzbk/p/14410608.html