《大話資料庫》-SQL語句執行時，底層究竟做了什麼小動作？ 前言 大家好，我是Taoye，試圖用玩世不恭過的態度對待生活的Coder。 現如今我們已然進入了大數據時代，無論是業內還是業外的朋友，相信都有聽說過資料庫這個名詞。數據是一個項目的精華，也扮演著為企業創造價值的重要角色，一個較為完善的公司一 ...
《大話資料庫》-SQL語句執行時，底層究竟做了什麼小動作？
前言
大家好，我是Taoye，試圖用玩世不恭過的態度對待生活的Coder。
現如今我們已然進入了大數據時代，無論是業內還是業外的朋友，相信都有聽說過資料庫這個名詞。數據是一個項目的精華，也扮演著為企業創造價值的重要角色，一個較為完善的公司一般都會有專門的DBA來管理資料庫，以便更好的為用戶服務。
互聯網的發展速度之快，以致大量的APP應用涌入用戶的視野，在大多數APP中都會有“推薦”這一板塊，而這個板塊功能的核心正是基於用戶以往的數據記錄而實現的。再如《死亡筆記》中L·Lawliet這一角色所提到的大數定律，在眾多繁雜的數據中必然存在著某種規律，偶然中必然包含著某種必然的發生。不管是我們提到的大數定律，還是最近火熱的大數據亦或其他領域都離不開大量數據的支持。
如上，我們可初步體會到數據的重要性，而要想更好的管理數據，則避免不了與資料庫打交道。對於資料庫而言，操作資料庫的用戶就相當於一位老闆，我們需要向資料庫發出命令，然後期望資料庫給我們返回想要的結果。
我們可以想象一下這麼一個場景，老闆給Taoye發佈了這麼一個任務：“Taoye啊，你作為一位專業的板磚工，我現在需要你在一小時的之內將工地的轉搬回來。”是的，老闆發佈任務只註重結果和效率，而不在意你板磚的過程。我們在執行SQL語句的時候也是如此，一般只關心執行的結果和效率是否滿足用戶的需求。
最近，Taoye重新把之前學習資料庫時候所記錄的筆記複習了一下，然後又系統性的拜讀了丁奇大大的《MySQL實戰45講》中的內容，所以想要把MySQL系列的知識內容單獨整理出來，在進行自我提高的同時，也希望能給予大家一點幫助。
MySQL的體繫結構
我們要想系統性的學習MySQL資料庫，首先不得不瞭解MySQL的體繫結構。在MySQL中，主要是由什麼功能模塊組成？每一個功能模塊在SQL語句執行的時候分別扮演了一個什麼樣的角色？MySQL的強大之處在哪，竟會受到如此之多的開發者的青睞？這些都是我們每一位學習MySQL的朋友必須瞭解甚至掌握的內容，以下便是MySQL資料庫的體繫結構及其執行流程：
從上圖，我們可以看出MySQL整體上主要分為了Server層和存儲引擎層兩個部分。
在Server層內部又包括連接器、緩存、分析器、優化器、執行器等功能部件，主要負責了MySQL的大多數核心服務功能，比如存儲過程、函數、觸發器、視圖等。
而存儲引擎層主要的是負責數據的存儲和提取，在MySQL中可支持MyISAM、InnoDB、Memory等多種存儲引擎，其中最常見的是InnoDB和MyISAM，這也是我們在學習存儲引擎時候的一個重點。在MySQL 5.5.5版本之前預設使用的是MyISAM，而在此版本之後預設採用的是InnoDB存儲引擎。我們在實際創建數據表的時候，也可以通過ENGINE來指定使用的存儲引擎，如下所示。我們可以對tb_comment數據表指定使用MyISAM存儲引擎：
1CREATE TABLE `tb_comment` (
2 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3 `content` varchar(255) DEFAULT NULL,
4 `user_name` varchar(255) DEFAULT NULL,
5 `openid` varchar(255) DEFAULT NULL,
6 `comment_time` int(11) DEFAULT NULL
7 PRIMARY KEY (`id`) USING BTREE
8) ENGINE=MyISAM AUTO_INCREMENT=175 DEFAULT CHARSET=utf8;
MyISAM和InnoDB這兩種存儲引擎最主要的區別是事務以及鎖機制：
- InnoDB支持事務，而MyISAM不支持事務
- InnoDB一般採用的是行鎖，鎖的粒度小，開銷大，鎖表慢，但是在高併發場景下性能更好。而MyISAM採用的是表鎖，特征與行鎖相反。
關於MySQL的事務和鎖機制，這裡只是簡單的提一下，具體的細節我們後面聊。下麵我們將MySQL的體繫結構中每一個功能模塊單獨的分離開來，依次看看每一個功能模塊所體現出的作用。
連接器
我們要想正常的操作資料庫，首先需要經過連接器這道大門。在正式引出連接器之前，各位看官不妨來看看下麵一個例子：
金主大大成天擔心自己財產的安全，必然會將自己的money存儲在銀行金庫中。某一天金主大大要想取出一部分的財產來維持公司的運營，而銀行為了保障金庫中money的安全性，金主必然要進行身份核驗以及一系列防盜系統的檢測。
在MySQL資料庫中，連接器的作用其實就類似於上面的身份校驗以及防盜系統，主要是負責與客戶端建立連接、許可權校驗、維持和管理連接。我們要想操作資料庫，首先需要通過賬號、密碼等信息來連接資料庫，假如我們想要以root賬戶、密碼為666666來連接192.168.31.100:3307的MySQL服務，則可以執行以下命令：
1mysql -h 192.168.31.100 -P 3307 -u root -p 666666
2
3# 如果是本地連接，則執行如下，密碼也可回車後輸入
4mysql -u root -p 666666
當數據匹配成功時，連接器就能允許用戶與資料庫建立連接。此外，連接器還需要驗證該用戶是否有許可權對數據表進行操作，這個時候連接器會去許可權表中查詢連接用戶的許可權，只有在具有操作許可權的前提下才能操作數據表。如果我們在與資料庫已經建立連接的前提下，但是不對資料庫進行任何操作，這個時候連接就會處於空閑狀態，我們可以通過show processlist命令來查看已經建立的連接數和處於空閑狀態的連接，其中command欄位為sleep表示連接空閑：
1mysql> show processlist;
2+----+------+-----------------+------+---------+------+----------+------------------+
3| Id | User | Host | db | Command | Time | State | Info |
4+----+------+-----------------+------+---------+------+----------+------------------+
5| 3 | root | localhost:53372 | NULL | Sleep | 28 | | NULL |
6| 4 | root | localhost:53378 | NULL | Query | 0 | starting | show processlist |
7+----+------+-----------------+------+---------+------+----------+------------------+
8
如果連接長期處於空閑狀態而不做任何操作，當超過一定時間時，就會自動斷開連接，而這個時間閾值主要是通過wait_timeout屬性來決定的，預設是28800，即8小時。show variables like '%wait_timeout%'"可查看時間閾值，set @@session.wait_timeout=xxx可修改當前會話下的時間閾值，具體操作如下：
1mysql> set @@session.wait_timeout=30000;
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> show variables like "%wait_timeout%";
5+--------------------------+----------+
6| Variable_name | Value |
7+--------------------------+----------+
8| innodb_lock_wait_timeout | 50 |
9| lock_wait_timeout | 31536000 |
10| wait_timeout | 30000 |
11+--------------------------+----------+
123 rows in set, 1 warning (0.00 sec)
緩存
緩存這個概念，學習過《電腦組成原理》或是其他相關課程的朋友應該並不陌生，緩存一般使用的是SRAM（靜態隨機存儲器）技術實現的，相較於DRAM（動態隨機存儲器）而言，它最主要的優勢在於速度快，能夠大大提高數據的查詢效率。
關於緩存，我們可以來做一個簡答的計算題：
假設查詢一次緩存需要1s，查詢一次記憶體需要10s，緩存命中的概率為90%，一位用戶想要查詢100次，則使用緩存和不使用緩存的平均查詢時間是多少？
可見，根據局部性原理，緩存的存在是可以大大提高數據的查詢效率的。
由上方的執行流程圖，我們也可以知道，客戶端通過連接器與MySQL伺服器建立連接之後，這個時候就會來到緩存中查詢用戶所需的數據。假設用戶向MySQL發出以下一條查詢語句：
1mysql> select * from tb_comment where id=1;
MySQL收到用戶發出的請求之後，就會先到緩存中看看之前是否有執行過相同的語句，而且之前執行的結果會以key-value鍵值對的形式直接進行存儲。假如之前有執行過這條命令，則直接從緩存中取出數據並反饋給用戶，如果沒有執行過，則會繼續走 分析器 -> 優化器 -> 執行器 -> 存儲引擎這條鏈路。所以說，緩存的命中可以省去後面一系列操作所消耗的時間，這也是緩存提高查詢效率的原因。
按道理來講，緩存的引入應該是相當不錯的，而且用戶查詢次數越多就越能體現緩存的強大，但為什麼在MySQL 8.0版本之後直接捨去了緩存部件呢？
在理想情況下，緩存引入確實是非常的完美，但是在資料庫中，我們除了查詢操作之外，還有更新操作（增、刪、改）。每當我們執行過一次更新操作的時候，資料庫中的數據就已然發生了改變，而當我們再次發出命令從緩存中取出的數據就不再是用戶所期望的數據了。所以對MySQL而言，每當用戶進行更新操作時，都會清空一次緩存，然後再次重新緩存新的數據，而這個過程給MySQL帶來的壓力是很大的，也大大削弱了SQL語句的執行效率。所以說，緩存對於一些查詢多，更新少的數據表比較有用，而對那些更新比較頻繁的數據表就會適得其反。
如果使用的是MySQL 8.0以下的版本，我們可以根據實際需求來確定是否開啟緩存，主要是通過my.cnf配置文件中的query_cache_type來決定的，0代表禁用緩存，1
