與其他數據庫相比，MySQL并不是完美的，卻是足夠靈活，能夠適應高要求的環境。在開發中，MySQL是勾勾在項目中的首選數據庫。為了更好的應用MySQL，充分發揮它的性能，就必須要理解它的設計。從這篇文章開始，我們就開始學習MySQL數據庫的基礎知識。

如果想要更深入的理解MySQL服務器，那么首當其沖應該掌握的是MySQL的邏輯架構，了解其各個模塊之間是如何協同工作的。

下圖是官網的邏輯架構圖：

我們把上面的圖簡化一下，就有了如下所示的MySQL簡易的邏輯架構，稍后我們會詳細分析每一個組件。

MySQL從整體上可以分為Server層和存儲引擎層。

Server層

大多數的MySQL的核心服務功能都是在Server層，它包括連接器、查詢緩存、解析器、優化器、執行器。

Server層涵蓋了MySQL的大部分功能，包括查詢解析、分析、優化、緩存以及所有的內置函數(例如：日期、時間、數學和加密函數)，所有跨存儲引擎的功能都在這一層實現：存儲過程、觸發器、視圖等。

連接器

每個客戶端連接都會在服務器進程中擁有一個線程，這個連接的查詢只會在這個單獨的線程中執行。

當客戶端應用連接到MySQL服務器時，首先接待它的就是連接器。連接器負責跟客戶端建立連接、獲取權限、維持和管理連接。

連接MySQL服務器的命令：

mysql -h$ip -P$port -u$user -p 

輸完命令之后，會提示我們輸入密碼，也可以將密碼寫在-p后面，但是這樣會存在密碼泄漏的風險。

如果根據我們輸入的用戶名和密碼無法連接到服務器，我們能看到如下的報錯：

[root@codegirl ~]# mysql -hlocalhost -P3306 -uroot -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 

 這個報錯信息就是連接器返回的。

所以當我們通過客戶端命令mysql與服務器建立連接時，連接器做了兩件事情：

• 認證用戶名和密碼，如果認證失敗，我們就收到了上述1045的異常，客戶端程序就結束了執行。如果認證成功，客戶端就與服務器建立了連接。
• 連接成功之后，連接器會繼續驗證用戶的權限，比如我們有哪些表的查詢權限，哪些表的修改權限，或者是授權權限。之后這個連接中的權限判斷邏輯，都是基于此時讀到的權限。所以如果修改了權限，一定要記得重新連接!

連接器的連接又分為長連接和短連接。

長連接：連接成功后，如果客戶端持續有請求，則一直使用通過一個連接。

短連接：每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個連接。

建立連接的過程比較復雜，現在絕大部分的服務都是使用的長連接。

如果建立連接之后，客戶端一直沒有請求，這個時候連接就會斷開。這個時間由參數wait_timeout控制，默認為8小時。

查看MySQL的連接時間設置：

mysql> show variables like 'wait_timeout%'; 

超時時間的設置單位為秒，28800/60/60 = 8h;

查詢緩存

建立連接之后，我們就可以執行sql語句了。

select查詢語句：

mysql> select * from user where id = 1; 

它不是直接去查詢表里的數據，而是先查詢緩存，如果緩存中存在則直接返回緩存中的數據，緩存中不存在再去表里查詢數據，然后將查詢到的結果添加到緩存里。

這個邏輯就像是我們為了減輕數據庫的壓力加了Redis緩存一樣。如果緩存存在，就不需要后面的解析和執行步驟，效率會大大提高。

MySQL緩存的數據是以key-value的形式存在的，key就是我們的查詢sql語句，value就是這個sql語句對應的查詢結果。

那這個時候我們不禁會想，數據庫的數據如果經常變更是不是緩存需要及時失效，這樣在下次查詢的時候我們就可以獲取到最新的數據了。

是的，MySQL只要表的數據或者表結構有變化，這張表的所有緩存都會失效。所以如果是一張經常涉及到增刪改的表，緩存并沒有太多實際的意義，可能剛加了緩存接下來就更新了，費了老大勁加的緩存又失效了。但是如果我們的表是系統配置這類的靜態表，緩存就能起到作用。

在開發中，如果我們測試某個sql的執行時間，首先要確定緩存是否可用。查詢緩存是否可用的命令：

mysql> show variables like '%have_query_cache%'; 

緩存是默認可用的：

修改緩存的配置，我們可以修改MySQL的配置文件：/etc/my.cnf，添加配置：query_cache_type=0;

其中可選項為：0、1、2;0代表不使用緩存，1代表使用緩存，2代表根據需要使用。

也可以使用命令：

mysql> set global query_cache_type = 0; 

查看緩存是否開啟：

mysql> select @@query_cache_type; 

如果關閉緩存之后，某些sql語句我們希望能使用緩存，我們可以通過SQL_CACHE顯式的指定sql使用緩存。

mysql> select SQL_CACHE * from user; 

MySQL8.0版本已經完全把緩存刪除了，對于緩存這一組件我們只需了解。在使用不同版本的MySQL時需要注意緩存對性能的影響。

解析器

開始真正執行sql語句時，解析器會先分析我們輸入的sql語句，MySQL解析器將sql語句解析成內部數據結構(解析樹)，然后優化器就可以對其優化。

我們給解析器的sql語句是字符串和空格組成的，解析器第一步是解析出來字符串，識別出里面的每個字符串代表的意思。

解析器會識別sql關鍵字，從而知道我們是在查詢還是更新。解析器將字符串'user'識別為表名字，把字符串'id'識別為列。解析器識別了字符串之后，就開始校驗我們給的字符串是否符合語法規范。

解析器會驗證語法，還會根據解析到的表和列驗證表和列是否存在。

如果表或者列不存在，或者語法有問題，我們可以收到錯誤信息。

mysql> select * from aa where id =1; 
ERROR 1146 (42S02): Table 'test.aa' doesn't exist 

mysql> select * fromuser 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 'fromuser where id=1' at line 1 

 優化器

經過解析器處理，得到了解析樹。這個時候MySQL已經明確知道自己要做什么了，但是在開始執行之前還會對sql進行優化。

優化器對sql語句的優化包括：重寫查詢、決定表的讀寫順序、選擇合適的索引等。

優化器涉及的內容比較多，我們先對它有個初步印象，后續我們再詳細了解它。

經過優化器之后，sql語句的執行方案就已經確定了，解析來就進入執行器開始執行了。

執行器

執行器執行sql語句的時候，會先驗證是否有對這個表的權限，如果沒有權限就會返回沒有權限的錯誤信息。如果有權限，則會打開表繼續執行。打開表的時候，執行器就會根據表的執行引擎，去使用執行引擎提供的接口。

存儲引擎

存儲引擎層負責數據的存儲和提取。存儲引擎是插件式的，支持InnoDB、MyISAM、Memory等多種存儲引擎，MySQL也提供了一些第三方的存儲引擎，這種插件式的結構設計，使得不同的公司可以根據自己的需求選擇不同的引擎。

現在最常用的存儲引擎是InnoDB，它是MySQL5.5.5版本之后默認的存儲引擎，如果我們在建表時不指定存儲引擎類型，默認使用的就是InnoDB。

不同的存儲引擎是公用Server層的，區分Server層和引擎層的功能對于后面我們學習鎖和事務比較重要。

不同的引擎保存數據和索引的方式是不相同的，但是表的定義是MySQL服務層負責的，這個是一致的。

今天我們只分析兩種常見的存儲引擎InnoDB和MyISAM，其他的引擎小伙伴感興趣可以查看相關文檔。

InnoDB

我們先看一下'user'表的信息 ，它的存儲引擎是InnoDB。

mysql> show table status like 'user' \G 
*************************** 1. row *************************** 
           Name: user  #表名 
         Engine: InnoDB #存儲引擎類型 
        Version: 10  
     Row_format: Dynamic #行的格式，如果表中包含了可變長度的字段比如Varchar，那么就是Dynamic 
           Rows: 0  #行數，對于InnoDB引擎來說，這是預估值 
 Avg_row_length: 0 #平均每行包含的字節數 
    Data_length: 16384 #表數據的大小（字節） 
Max_data_length: 0 #表數據的最大容量，和引擎有關 
   Index_length: 0 #所以的大小（字節） 
      Data_free: 0  
 Auto_increment: NULL #下一個自增長的值 
    Create_time: 2021-02-16 14:24:46  
    Update_time: NULL 
     Check_time: NULL 
      Collation: utf8_general_ci #默認字符集 
       Checksum: NULL 
 Create_options:  
        Comment:  
1 row in set (0.00 sec) 

 InnoDB的數據存儲在表空間中，它將每個表的數據和索引存放在單獨的文件中。‘user’表在磁盤上有兩個數據文件：

.frm文件：表示表的定義，由MySQL的server層定義。

.ibd文件：數據和索引文件。

InnoDB采用的是MVCC多版本控制來支持高并發。并且它實現了四個標準的事務隔離級別，其默認的隔離級別是可重復讀。它支持行鎖，并且通過間隙鎖策略防止幻讀的出現。

InnoDB是基于聚簇索引建立的，對基于主鍵的查詢有很高的性能。

MyISAM

我們先看一下'user_isam'表的信息 ，它的存儲引擎為MyISAM。

mysql> show table status like 'user_isam' \G 
*************************** 1. row *************************** 
           Name: user_isam 
         Engine: MyISAM 
        Version: 10 
     Row_format: Dynamic 
           Rows: 0 
 Avg_row_length: 0 
    Data_length: 0 
Max_data_length: 281474976710655 
   Index_length: 1024 
      Data_free: 0 
 Auto_increment: NULL 
    Create_time: 2021-02-16 16:36:25 
    Update_time: 2021-02-16 16:36:25 
     Check_time: NULL 
      Collation: utf8_general_ci 
       Checksum: NULL 
 Create_options:  
        Comment:  
1 row in set (0.00 sec) 

 MyISAM會將表存儲在兩個文件中：數據文件和索引文件。

.frm文件：表示表的定義，由MySQL的server層定義。

.MYD文件：表示數據文件。

.MYI文件：表示索引文件。

MyISAM提供了很多特性，但是它不支持事務和行鎖，它是對整張表加鎖，而且崩潰后無法安全恢復，這也是它被InnoDB取代的原因。

總結

MySQL邏輯架構分為Server層和存儲引擎層。

Server層負責連接、緩存、解析 、優化、執行。存儲引擎負責數據的存儲和提取。

存儲是插件式的，可以根據每個表的需求選擇不同的存儲引擎，但是不建議這么做。除非需要用到InnoDB不具備的特性，并且無法解決只能選擇其他引擎的情況下才需要修改，否則都按照默認的InnoDB引擎。而且除非萬不得已，不建議混合使用多種引擎。