mysql查詢流程解析及重要知識總結

時光荏苒啊！在過兩個月我就工作滿三年了，大學畢業的情景還歷歷在目，而我已經默默的向油膩中年大叔進發了。作為一名苦逼的后端工程師，我搞過一段時間python，現在靠java糊口，但后來才發現，始終不棄我的是數據庫啊。從大學到工作，她始終陪伴左右，從sql service到mysql，她一直在身邊，而今是時候深入了解總結下數據庫的一些基礎知識了。

（一）mysql邏輯架構

1.數據庫與數據庫實例

在介紹mysql邏輯架構前，首先需要理解兩個概念：數據庫和數據庫實例。

數據庫： 數據庫是文件的集合，是依照某種數據模型組織起里并存放于二級存儲器中的數據集合。
數據庫實例： 數據庫實例是程序，是位于用戶和操作系統之間的一層數據管理軟件，用來管理數據庫數據。
在日常開發中，我們經常將兩者的概念混淆，但實際兩者概念完全不同。mysql有點類似我們的java應用，是一個單進程多線程架構的數據庫，mysql數據庫實例在系統上的表現就是一個進程。

2.mysql邏輯結構及分層

接下來我們看下《高性能mysql》中的一張簡單的mysql服務器邏輯架構圖，我們可以在邏輯上將mysql服務器分為三層：客戶端層、服務器層、存儲引擎層。這個分層并不是官方定義的，是基于使用經驗和習慣意義上的分層。
在這里插入圖片描述
上面的圖比較粗糙，主要用來讓大家對邏輯架構和分層有個大致的了解，接下來我們通過另一張圖來介紹各層的職責功能。

客戶端層：負責連接處理、授權認證、安全服務等。
服務器層：這一層包含了mysql的所有核心服務功能，包括查詢、解析、分析、優化、緩存及所有內置函數。所有跨存儲引擎的功能都在這一層實現，例如存儲過程、觸發器及我們常常提到的binlog日志。
存儲引擎層：負責mysql中數據的存儲和提取。

插件式的表存儲引擎是mysql數據庫的特色，我們可以在不同的應用場景下可以使用不同的存儲引擎，當然最常用的還是Innodb存儲引擎，除此之外還有MyISAM、NDB、Memory等存儲引擎。有心的童鞋能夠看到存儲引擎層的解釋被我加了斜體，我們都知道mysql的大部分數據是存儲在磁盤中的，所以這個解釋還帶有一層含義：存儲引擎層負責和磁盤打交道，存儲引擎讀取數據的方式很大程度上決定了sql執行的快慢。實際上這也是我介紹mysql邏輯架構圖的一個重要用意，我想讓大家清楚：服務器層的很多操作是基于內存的，而存儲引擎層主要和磁盤打交道，通過合適的sql語句優化減少存儲引擎訪問的數據量是mysql語句優化的一個基本思路。

通過上面的圖，我們可以大致了解MySQL查詢的執行過程，可以分為5個步驟：

客戶端向mysql服務器發出請求
（若mysql開啟了查詢緩存）服務器檢查緩存，若存在緩存直接返回，否則進入下個階段。
服務器解析sql語句，進行預處理，并由優化器生成相應的執行計劃。
mysql根據執行計劃，調用存儲引擎API來執行查詢。
返回查詢結果給客戶端，（若mysql開啟了查詢緩存）并緩存查詢結果。

這里有兩點值得注意：
首先是： 客戶端/服務器端的通信協議問題：mysql服務器和客戶端間的通信是半雙工的，不能雙向同步數據，任意時刻只能由一方傳輸，一旦一端開始發送消息，另一端需要接收完消息才能響應。在mysql服務端返回查詢數據時，需要等所有的數據都發送給客戶端才能夠釋放這條查詢所占用的資源。當我們需要進行大量的數據查詢時，例如需要查詢幾萬條或幾十萬條運單的商戶信息，請只返回我們真正需要的字段，盡量避免select *，這樣能夠減少數據傳輸的開銷，減輕服務器和客戶端的壓力。
其次是： mysql的緩存問題，緩存并不是什么場景都是好的，需要衡量緩存使用的開銷和它能夠給我們帶來的收益。目前我們的mysql服務器緩存是默認關閉的，因為在緩存的設置，刪除以及更新都需要比較多的系統開銷，綜合收益并不大，另外在客戶端層mybatis的一級、二級緩存提供了非常相似的緩存功能，我個人感覺還是在客戶端層進行緩存會更好些。

3.Innodb引擎索引實現

索引是用于快速查找記錄的一種數據結構，在mysql中也別稱為建(key)。在mysql中索引是由存儲引擎層而不是服務器層實現的，不同存儲引擎實現的索引工作方式也并不一樣，這里我們只介紹Innodb的索引實現。在Innodb存儲引擎中，主鍵索引 (或稱聚簇索引)和輔助索引(或稱二級索引)都是通過B+樹實現的，B+樹是一種平衡多叉樹（注意是多叉樹而不是二叉樹）。
在這里插入圖片描述
在Innodb存儲引擎中數據存儲結構被分為：表、段、區、頁，頁是Innodb磁盤管理的最小單位。頁的默認大小是16k，我們無法通過B+樹索引直接找到給定鍵值的具體行，B+樹索引能找到的只是數據行所在的頁，然后通過把頁讀取到內存中，在內存中查找并讀取相應的行數據。無論是主鍵索引還是輔助索引，他們的數據存儲和讀取都是基于頁的，所以也經常將葉子節點稱為數據頁，將非葉子節點稱為節點頁。
主鍵索引和輔助索引的區別： 在主鍵索引的B+樹中：非葉子節點存儲的是索引列（即主鍵列）和相關指針，而葉子節點存儲了行的全部數據。在輔助索引的B+樹中：非葉子節點存儲的同樣是索引列（組成索引的所有列）和相關指針，而葉子節點除了存儲索引列外，還存儲了主鍵列。
B+樹索引的本質是B+樹在數據庫中的實現，B+樹索引在數據庫中的一個特點就是高扇出性，在數據庫中B+樹的高度一般都是2-4層，也就是說查找某一主鍵鍵值對應的行記錄僅需要2-4次IO，這也就保證了數據庫數據查詢的速度。B+樹深度不大卻能夠保存數以億計的數據，同時擁有自平衡的功能，能夠確保數據查詢時間的均衡性，我認為這兩點就是數據庫或其他直接存取輔助設備使用B+樹保存數據的根本原因。
通過上面的小圖，其實我們就可以簡單的了解到數據庫的查詢方式了：通過輔助索引查詢時，首先需要在輔助索引中定位到記錄的主鍵值，再利用主鍵值在主鍵索引中查詢到相應的數據頁，通過主鍵值去主鍵索引中查詢完整行數據的過程通常被我們稱為“回表”。如果我們的查詢只發生在輔助索引中，而不需要回表的話，那么毋庸置疑查詢效率會很高，這種通過輔助索引就可以獲取到所需數據而無需回表的查詢方式被稱為“覆蓋索引”。熟練的使用“覆蓋索引”是一名優秀的后端工程師應該具備的基本技能。

（二）sql查詢過程及where條件的提取與應用

在上一節中我們已經介紹了索引結構及簡單的查詢過程，接下來我們通過一個復雜些的sql，來介紹下詳細的查詢過程及where條件語句的提取應用流程，同時也會介紹些在mysql5.6版本中使用的新技術。這里講的知識很大程度上借鑒參考了何登成大神的文章和思想，借花獻佛是最快樂的事了，哈哈哈，有興趣的童鞋可以到大神的網站上學習：mysql大神的github 。
首先我們創建一張測試表，將字段a作為主鍵，同時建立字段b、c、d的聯合索引idx_b_c_d，然后插入幾條記錄，具體語句如下：

create table test (a int primary key,b int,c int,d int,e varchar(20)
) ENGINE = InnoDB;alter table test add index idx_b_c_d (b,c,d);
insert into test values (4,3,1,1,'d');
insert into test values (1,1,1,1,'a');
insert into test values (8,8,8,8,'h');
insert into test values (2,2,2,2,'b');
insert into test values (5,2,3,5,'e');
insert into test values (3,3,2,2,'c');
insert into test values (7,4,5,5,'g');
insert into test values (6,6,4,4,'f');

生成的大致索引結構圖如下（意思意思即可，不要太糾結細節）：
在這里插入圖片描述
接下來我們考慮以下sql的執行過程（我們假設語句走idx_b_c_d索引，實際上記錄這么少估計就直接全表掃描了）

select * from test where b >= 2 and b < 8 and c > 1 and d != 4 and e != 'a';

在看到這條語句后，我們可以思考幾個問題：
1.此SQL能夠覆蓋索引idx_b_c_d上的哪些記錄？
起始范圍：記錄[2,2,2]是第一個需要檢查的索引項，索引起始查找范圍由b >= 2，c > 1決定。
終止范圍：記錄[8,8,8]是第一個不需要檢查的記錄，而之前的記錄均需要判斷。索引的終止查找范圍由b < 8決定；
2.在確定了查詢的起始、終止范圍之后，SQL中還有哪些條件可以使用索引idx_b_c_d過濾？
固定了索引的查詢范圍[(2,2,2),(8,8,8))之后，此索引范圍中并不是每條記錄都是滿足where查詢條件的。例如：(3,1,1)不滿足c > 1的約束；(6,4,4)不滿足d != 4的約束。而c，d列均可在索引idx_b_c_d中過濾掉不滿足條件的索引記錄的。因此，SQL中還可以使用c > 1 and d != 4條件來進行索引記錄的過濾。
3.在確定了索引中最終能夠過濾掉的條件之后，還有哪些條件是索引無法過濾的？
顯而易見，e != 'a’這個查詢條件，無法在索引idx_b_c_d上進行過濾，因為索引并未包含e列。為了過濾此查詢條件，必須將已經滿足索引查詢條件的記錄回表，取出表中的e列，然后使用e列的查詢條件e != ‘a’進行最終的過濾。

在理解以上的問題解答的基礎上，做一個抽象，可總結出一套放置于所有SQL語句而皆準的where查詢條件的提取規則：
可歸納為3大類：Index Key (First Key & Last Key)，Index Filter，Table Filter。
（1）Index Key：
用于確定SQL查詢在索引中的連續范圍的查詢條件，被稱之為Index Key。一個范圍包含一個起始與一個終止，因此Index Key也被拆分為Index First Key和Index Last Key，分別用于定位索引查找的起始，以及索引查詢的終止條件。
Index First Key
提取規則：從索引的第一個鍵值開始，檢查其在where條件中是否存在，若存在并且條件是=、>=，則將對應的條件加入Index First Key之中，繼續讀取索引的下一個鍵值，使用同樣的提取規則；若存在并且條件是>，則將對應的條件加入Index First Key中，然后終止Index First Key的提取。針對上面的SQL，應用這個提取規則，提取出來的Index First Key為(b >= 2, c > 1)。由于c的條件為 >，提取結束，不包括d。
Index Last Key
與Index First Key正好相反，用于確定索引查詢的終止范圍。提取規則：從索引的第一個鍵值開始，檢查其在where條件中是否存在，若存在并且條件是=、<=，則將對應條件加入到Index Last Key中，繼續提取索引的下一個鍵值，使用同樣的提取規則；若存在并且條件是 < ，則將條件加入到Index Last Key中，同時終止提取；若不存在，同樣終止Index Last Key的提取。針對上面的SQL，應用這個提取規則，提取出來的Index Last Key為(b < 8)，由于是 < 符號，因此提取b之后結束。
（2）Index Filter：
在Index Key的提取之后固定了索引的查詢范圍，但是此范圍中的項，并不都是滿足查詢條件的項。在上面的SQL用例中，(3,1,1)，(6,4,4)均屬于范圍中，但是均不滿足SQL的查詢條件。Index Filter的提取規則：同樣從索引列的第一列開始，檢查其在where條件中是否存在：
1 若存在并且where條件僅為 =，則跳過第一列繼續檢查索引下一列，下一索引列采取與索引第一列同樣的提取規則；
2 若where條件為 >=、>、<、<= 其中的幾種，則跳過索引第一列，將其余where條件中索引相關列全部加入到Index Filter之中；
3 若索引第一列的where條件包含 =、>=、>、<、<= 之外的條件，則將此條件以及其余where條件中索引相關列全部加入到Index Filter之中；
4 若第一列不包含查詢條件，則將所有索引相關條件均加入到Index Filter之中。針對上面的用例SQL，索引第一列只包含 >=、< 兩個條件，因此第一列可跳過，將余下的c、d兩列加入到Index Filter中。因此獲得的Index Filter為 c > 1 and d != 4 。
（3）Table Filter：
Table Filter是最簡單，也是提取最為方便的。提取規則：所有不屬于索引列的查詢條件，均歸為Table Filter之中。針對上面的用例SQL，Table Filter就為 e != ‘a’。
在這里插入圖片描述
有了上面的“where條件的提取應用”流程后，對于數據庫的查詢邏輯我們就清楚很多了，我們這里以上面提到的sql語句為例，來講解下具體的查詢流程：
（1） 首先需要在輔助索引中定位起始索引記錄，在索引第一次Search Path(沿著索引B+樹的根節點一直遍歷，到索引正確的葉節點位置)時使用，一次判斷即可。
（2） 之后遍歷起始索引記錄后的每一條索引記錄，判斷是否為終止索引記錄，是的話就結束查詢，同時利用輔助索引中的條件列過濾掉不滿足查詢條件的索引記錄。
講到這里時，一些比較有經驗的程序員可能會說：查詢語句雖然使用了idx_b_c_d索引，但其中索引字段b和c進行的都是范圍查詢（b >= 2 and b < 8 and c > 1），這會導致其余的索引字段（字段d）無法使用的，很多人將這種情況稱為“索引截斷”。在《高性能mysql》中確實也明確的提過：“如果查詢中有某個列進行范圍查詢，則其右邊所有的列都無法使用索引進行優化查詢”。我們的《高性能mysql》涵蓋版本主要是mysql5.5及以前，在mysql5.5及以前確實是這樣的，這主要是由于存儲引擎API不支持使用過濾條件，服務器層沒辦法把過濾條件傳到存儲引擎層，而在5.6版本mysql推出了index condition pushdown(ICP)索引條件下推技術解決了這個問題。有興趣的童鞋可以了解下：ICP技術。當使用了ICP時，在Explain執行計劃的Extra列中會出現Using index condition的提示。
（3） 將查詢得到的索引記錄暫存在緩存中，然后根據主鍵鍵值進行排序，之后利用主鍵鍵值順序地訪問主鍵索引中存儲的數據文件。 這個流程其實是mysql5.6推出的Multi-Range Read(MRR)技術，主要用于減少磁盤的隨機IO。其具體內容可見于：MRR技術。
（4） 根據主鍵鍵值在主鍵索引中查詢到完整的數據記錄，并將數據記錄返回給【服務器層】。
（5）【服務器層】根據where條件中非索引列進行過濾，并將過濾后的數據記錄返回給【客戶端層】。

這里有兩點值得注意：
第一點： 在主鍵索引中不會進行條件過濾。我們知道字段e并不是索引idx_b_c_d包含的字段，所以在輔助索引idx_b_c_d中無法進行條件過濾，但當我們根據主鍵鍵值在主鍵索引中查找數據記錄時仍不能進行過濾，只能當數據返回到服務器層后，在服務器層通過 e != 'a’進行條件過濾。之前已經提過存儲引擎層是負責和磁盤交互的，當數據記錄到達服務器層時，實際已經完成了從磁盤中讀數據的操作，也就是說實際上我們從磁盤中讀取了我們并不需要的數據，這無疑會增加數據庫的查詢時間，當出現這種情況時，我們會在Explain執行計劃的Extra列中看到 Using where的提示。當然這種情況其實很常見，也很難避免，只要在輔助索引中能夠有效的過濾掉大部分無效數據，即使出現using where也不會有很大的性能影響。
第二點： mysql結果集返回客戶端是一個增量、逐步返回的過程。當開始生成第一條結果時。mysql就開始向客戶端開始返回數據了，存儲引擎層和服務器層也是一樣的，查詢到一條數據就會返回一條數據給客戶端，這樣做的好處主要是：可以減少mysql服務器存儲的數據，也就不會因為要返回太多結果而消耗太多內存。

（三）EXPLAIN執行計劃信息

上面講了很多東西，但其實我最想講的是explain，對的，就是大家都很熟悉的mysql explain命令。上面講的一大堆其實都是為此鋪墊，explain真的很難，需要你對mysql的基礎有比較深入的了解，才能讀懂，這也是我認為mysql值得吐槽的一點，explain的提供信息真的是非常不人性化，而且很容易誤導人。在講解explain命令的相關信息前，我們首先要明確一點：explain給出的很多統計數據都是近似值，并不是精確值。在這里插入圖片描述
我們繼續使用上文中的sql語句進行分析（這里只考慮單表的，暫時不考慮多表聯接的情況）。通過上圖可以看到explain結果中有很多列，接下來我們介紹下explain結果中每一列的意義，其中我認為比較重要的列會放在后面講解。在mysql官方文檔中也有相關解釋：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows。在這里插入圖片描述
上面已經將explain中比較簡單的列信息描述好了，接下來我們開始重頭戲，也就是我們剩下的key_len列、type列、及Extra列。
（1）key_len列：
key_len其實是和key結合在一起使用的。在key確定后，也就是優化器選定好索引后，我們就可以通過key_len的值來查看我們的sql語句能夠使用的索引長度，也就可以確定我們的sql語句能使用到索引的哪幾個字段。但這里有一點需要重點說明的是：key_len無法統計ICP優化后輔助索引使用的列。也就是說雖然我們在5.6版本引入了ICP技術使得所有的索引列都能參與索引記錄過濾，但key_len的統計邏輯沒有做相應的優化，在聯合索引中靠前的列出現范圍查詢時key_len的統計邏輯仍會認為發生了索引截斷，認為這個列后的其余索引列無法使用。這其實就是上面的圖中為什么key_len的值為10（字段a和b都為int類型，占用4個字節，同時因為列可為NULL，a和b都需要增加1個字節標識），key_len統計邏輯認為后面的索引字段d無法被使用，而實際上字段d能夠被使用。下面列了簡單的驗證圖

開啟ICP： 在這里插入圖片描述
關閉ICP：

select * from test force index(idx_b_c_d) where b >= 2 and b < 8 能查詢出的記錄為5條，
select * from test force index(idx_b_c_d) where b >= 2 and b < 8 and c > 1 and d != 4語句能查詢出的記錄為4條，
而開啟ICP時實際只回表讀了4條記錄，說明輔助索引中確實使用了d字段。
在這里插入圖片描述
其實通過簡單的測試我們能夠發現，其實在關閉ICP時，字段c也不能過濾索引記錄，但是key_len的長度卻包含了c字段，這有點讓我費解。

（2）type列：
type列在mysql用戶手冊上被稱為“關聯類型”，其實更準確的說法應該是“訪問類型”，即“mysql查找表中行的方式”，下面我們按照從最差到最優的順序列出最重要的幾種訪問方式。
在這里插入圖片描述
（3）Extra列：
這一列包含的是不適合在其他列中顯示的額外信息，同時也是最復雜和比較容易讓人產生誤解的一列。接下來我們通過上面的test表及相關語句來演示下，各種extra值出現的場景，以便于大家理解（不含using temporary）。

using index
在這里插入圖片描述我們可以看到當查詢的字段剛好是輔助索引包含的字段，且查詢條件都是輔助索引包含的字段，并且是等值查詢時，Extra的值為using index。

using where；using index
在這里插入圖片描述
我們可以看到當查詢的字段剛好是輔助索引包含的字段，且查詢條件都是輔助索引包含的字段，但查詢為范圍查詢時，Extra的值為using where; using index。

using index condition
在這里插入圖片描述
我們可以看到當查詢條件都是輔助索引包含的字段，且查詢為范圍查詢時，但查詢的數據包含非索引字段時，Extra的值為using index condition。

using index condition；using where
在這里插入圖片描述
我們可以看到當增加過濾條件e != 'a’后，多了一個using where，上面我們其實已經講過了，因為二級索引中不包含字段e，索引需要先通過b >= 2 and b < 8 and c > 1 and d != 4過濾出數據記錄，然后將數據記錄返回給服務器層再次利用e != 'a’進行過濾，所以會多出一個using where。

Using index condition; Using where; Using filesort
在這里插入圖片描述
我們可以看到當使用order by e時出現了Using filesort，我們應該盡量避免Using filesort的出現，它可能會影響系統的性能。

（4）using filesort排序原理及可能的影響

上面我們已經將幾種常見的Extra值出現的情況進行了列舉，到這里整篇文章其實差不多要結束了。在最后我想說下using filesort的問題，這個問題大家平時關注的比較少，但它可能帶來的影響確可能很大。當我們的查詢中不能使用索引生成查詢結果的時候，mysql需要自己進行排序，如果數據量小則在內存中排序，否則需要使用磁盤，不過在Extra中都只是展示using filesort。每個數據庫connection在需要進行排序時mysql都會根據參數sort_buffer_size來為其分配內存（默認1m），當數據量小于sort_buffer_size時，mysql將所有數據放入內存中進行快速排序。當數據量大于sort_buffer_size時，mysql會先將數據分塊，對每塊分別排序并將結果存放在磁盤中，最后將排序好的塊進行合并（其實就是歸并排序算法）。這里其實存在一些隱患：首先用于排序的內存分配使用的是mmap()函數而不是malloc()，分配內存的效率比較低，其次當排序的并發connection非常多，可能占用很多內存資源，給系統帶來不小的壓力。
在mysql中還有一個參數max_length_for_sort_data（默認1k），這個參數決定了mysql使用的排序算法。當需要查詢的所有列的總長度不超過max_length_for_sort_data時mysql使用單次傳輸排序，否者使用兩次傳輸排序。
兩次傳輸排序： 第一次只讀取主鍵值和需要排序的字段，對其排序，然后在根據排序結果第二次讀取數據。這種方式占用比較少的內存空間，但是會造成大量的隨機IO。
上面說了很多，其實總結一下就一句話：使用order by時應注意利用索引的有序性，盡量避免出現using filesort或者盡可能避免對大量數據進行排序。