mysql學習筆記（二）--查詢語句的執行過程
最近在學習mysql實戰45講，覺得裡面的內容很受用，做一些筆記記錄下：
首先是mysql的一個基礎架構的解釋，如下圖：
從上圖我們可以清晰的看到，mysql的基礎架構主要分為兩個部分，一個是server層（負責大多數核心服務功能的實現），一個是儲存引擎層（負責資料的儲存與提取）。
server層是跨儲存引擎的，也就是說，當客戶端執行一條查詢語句時，必須經過server層，不管當前查詢的表指定的儲存引擎是INNODB還是MYISAM。
server層主要包括以下幾個部分：
（1） 聯結器 ：管理與客戶端的連線，包括建立以及維持連線。可以用以下命令連線mysql資料庫：
mysql -h$ip -P$port -u$user -p
連線又分為長連線與短連線。其實長連線是相對於通常的短連線而說的，也就是長時間保持客戶端與服務端的連線狀態。通常的短連線操作步驟是：連線->資料傳輸->關閉連線；而長連線通常就是：連線->資料傳輸->保持連線->資料傳輸->保持連線->…………->關閉連線；這就要求長連線在沒有資料通訊時，定時傳送資料包，以維持連線狀態，短連線在沒有資料傳輸時直接關閉就行了。長連線主要用於在少數客戶端與服務端的頻繁通訊，因為這時候如果用短連線頻繁通訊常會發生Socket出錯，並且頻繁建立Socket連線也是對資源的浪費。但是對於服務端來說，長連線也會耗費一定的資源，這是因mysql在執行過程中臨時使用的記憶體是管理在連線物件裡面的，這些資源會在連線斷開的時候才釋放。所以如果長連線累積下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM），從現象看就是mysql異常重啟了。需要專門的執行緒（unix下可以用程序管理）來負責維護連線狀態。總之，長連線和短連線的選擇要視情況而定。
（2） 查詢快取 ：MySql查詢快取保留了查詢返回給客戶端的完整結果，當快取命中的時候，伺服器馬上返回儲存的結果(會先檢查許可權)，並跳過解析、優化和執行步驟。
當mysql接收到查詢請求時，會查詢快取是否命中，若命中，則直接返回查詢結果，這裡的key就是查詢語句，value就是查詢結果。在資料頻繁更新的資料庫中，查詢快取一般不建議使用，因為每當有表更新操作時，所有的快取都會失效。可以通過設定query_cache_type=demand按需使用查詢快取，如：
mysql> select SQL_CACHE * from ...
mysql8.0版本已將該功能去掉。
快取配置引數：
query_cache_limit: MySQL能夠快取的最大結果,如果超出,則增加 Qcache_not_cached的值,並刪除查詢結果
query_cache_min_res_unit: 分配記憶體塊時的最小單位大小
query_cache_size: 快取使用的總記憶體空間大小,單位是位元組,這個值必須是1024的整數倍,否則MySQL實際分配可能跟這個數值不同(感覺這個應該跟檔案系統的blcok大小有關)
query_cache_type: 是否開啟快取 OFF: 關閉 ON: 總是開啟
query_cache_wlock_invalidate: 如果某個資料表被鎖住,是否仍然從快取中返回資料,預設是OFF,表示仍然可以返回。
（3） 分析器 ：對使用者輸入的查詢語句進行詞法分析、語法分析、語義分析、構造執行樹。
a）首先是解析器將查詢分解成一個個標識，然後構造一顆“解析樹”，解析器保證查詢中的標識都是有效的，會檢查其中的基本錯誤，比如字串上面的引號沒有閉合等。
b）然後前處理器檢查解析器生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。最後，前處理器檢查許可權。
eg：
a）以下程式碼為當我們查詢一個不存在的列如k時，應該是在 前處理器階段 返回的錯誤；
select * from T where k=1
b）以下程式碼就是在 語法分析階段 ，mysql發現語句不對，報錯：You have an error in your SQL syntax；
mysql> elect * from t where ID=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1
ps：語法檢查階段的具體順序如下圖：
（4） 優化器 ：優化器把解析樹變成執行計劃。一個查詢通常可以有很多種執行方式，並且返回同樣的結果，優化器的任務就是找到最好的方式。
MySQL使用基於成本的優化器，它將嘗試預測一個查詢使用某種執行計劃的成本，並選擇其中成本最小的一個。最初，成本的最小單位是隨機讀取一個4K資料頁的成本，後來成本計算公式變得更加複雜，並且引入了一些“因子”來估算某些操作的代價，如當執行一次where條件比較的成本。可以通過查詢當前會話的last_query_cost的值來得知MySQL計算的當前查詢的成本。
有很多種原因會導致MySQL優化器選擇錯誤的執行計劃，比如：
1. 統計資訊不準確。
2. 執行計劃中的成本估算不等同於實際的執行計劃的成本。
3. MySQL的最優可能與你想的最優不一樣。
4. MySQL從不考慮其他併發的查詢，這可能會影響當前查詢的速度。
5. MySQL也不是任何時候都是基於成本的優化，有時候也會基於一些固定的規則。
6. MySQL不會考慮不受其控制的成本，例如執行儲存過程或者使用者自定義的函式的成本。
（5） 執行器 ：對優化器生成的執行計劃進行執行操作。
在執行之前，會檢查當前使用者是否有查詢許可權，如果沒有對應的許可權，會報錯如下：
mysql> select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
這裡有個疑問，為什麼丁奇老師講的是許可權檢查是在執行器階段進行，而網上的很多資料都表明在分析器階段進行許可權檢查？？？
丁奇老師給出的回答是：有些時候，SQL語句要操作的表不只是SQL字面上那些。比如如果有個觸發器，得在執行器階段（過程中）才能確定。優化器階段前是無能為力的。而通過實驗，如下程式碼：
mysql> select * from T where k=1 ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
可以發現，在建立了一個沒有select許可權的使用者之後，執行以下語句，報錯的確是沒有許可權，按照丁奇老師的說法，這個語句應該是通過了分析器（前處理器檢查每個列是否存在）階段，進入了執行器階段進行許可權檢查然後報錯的，但其實這個語句在分析器階段就會報錯了，與丁奇老師說的在執行器階段進行的許可權檢查其實是矛盾的，雖然丁奇老師給出的回答是：這個是一個安全方面的考慮。你想想一個使用者如果沒有檢視這個表的許可權，你是會告訴他欄位不對還是沒許可權？如果告訴他欄位不對，其實給的資訊太多了，因為沒許可權的意思還包含了：沒許可權知道欄位是否存在。但個人還是比較信服於在分析器階段其實就已經進行了許可權檢查了。
ps：結合丁奇老師以及網上的一些資料，會不會有可能進行了兩次許可權檢查呢？？？
最後就是呼叫儲存引擎的api介面獲取資料並返回給客戶端了。