與其他數(shù)據(jù)庫(kù)相比，MySQL并不是完美的，卻是足夠靈活，能夠適應(yīng)高要求的環(huán)境。在開(kāi)發(fā)中，MySQL是勾勾在項(xiàng)目中的首選數(shù)據(jù)庫(kù)。為了更好的應(yīng)用MySQL，充分發(fā)揮它的性能，就必須要理解它的設(shè)計(jì)。從這篇文章開(kāi)始，我們就開(kāi)始學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)。

如果想要更深入的理解MySQL服務(wù)器，那么首當(dāng)其沖應(yīng)該掌握的是MySQL的邏輯架構(gòu)，了解其各個(gè)模塊之間是如何協(xié)同工作的。

下圖是官網(wǎng)的邏輯架構(gòu)圖：

我們把上面的圖簡(jiǎn)化一下，就有了如下所示的MySQL簡(jiǎn)易的邏輯架構(gòu)，稍后我們會(huì)詳細(xì)分析每一個(gè)組件。

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

Server層

大多數(shù)的MySQL的核心服務(wù)功能都是在Server層，它包括連接器、查詢(xún)緩存、解析器、優(yōu)化器、執(zhí)行器。

Server層涵蓋了MySQL的大部分功能，包括查詢(xún)解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)(例如：日期、時(shí)間、數(shù)學(xué)和加密函數(shù))，所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

連接器

每個(gè)客戶(hù)端連接都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程，這個(gè)連接的查詢(xún)只會(huì)在這個(gè)單獨(dú)的線程中執(zhí)行。

當(dāng)客戶(hù)端應(yīng)用連接到MySQL服務(wù)器時(shí)，首先接待它的就是連接器。連接器負(fù)責(zé)跟客戶(hù)端建立連接、獲取權(quán)限、維持和管理連接。

連接MySQL服務(wù)器的命令：

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

輸完命令之后，會(huì)提示我們輸入密碼，也可以將密碼寫(xiě)在-p后面，但是這樣會(huì)存在密碼泄漏的風(fēng)險(xiǎn)。

如果根據(jù)我們輸入的用戶(hù)名和密碼無(wú)法連接到服務(wù)器，我們能看到如下的報(bào)錯(cuò)：

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

 這個(gè)報(bào)錯(cuò)信息就是連接器返回的。

所以當(dāng)我們通過(guò)客戶(hù)端命令mysql與服務(wù)器建立連接時(shí)，連接器做了兩件事情：

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

連接器的連接又分為長(zhǎng)連接和短連接。

長(zhǎng)連接：連接成功后，如果客戶(hù)端持續(xù)有請(qǐng)求，則一直使用通過(guò)一個(gè)連接。

短連接：每次執(zhí)行完很少的幾次查詢(xún)就斷開(kāi)連接，下次查詢(xún)?cè)僦匦陆⒁粋€(gè)連接。

建立連接的過(guò)程比較復(fù)雜，現(xiàn)在絕大部分的服務(wù)都是使用的長(zhǎng)連接。

如果建立連接之后，客戶(hù)端一直沒(méi)有請(qǐng)求，這個(gè)時(shí)候連接就會(huì)斷開(kāi)。這個(gè)時(shí)間由參數(shù)wait_timeout控制，默認(rèn)為8小時(shí)。

查看MySQL的連接時(shí)間設(shè)置：

mysql> show variables like 'wait_timeout%'; 

超時(shí)時(shí)間的設(shè)置單位為秒，28800/60/60 = 8h;

查詢(xún)緩存

建立連接之后，我們就可以執(zhí)行sql語(yǔ)句了。

select查詢(xún)語(yǔ)句：

mysql> select * from user where id = 1; 

它不是直接去查詢(xún)表里的數(shù)據(jù)，而是先查詢(xún)緩存，如果緩存中存在則直接返回緩存中的數(shù)據(jù)，緩存中不存在再去表里查詢(xún)數(shù)據(jù)，然后將查詢(xún)到的結(jié)果添加到緩存里。

這個(gè)邏輯就像是我們?yōu)榱藴p輕數(shù)據(jù)庫(kù)的壓力加了Redis緩存一樣。如果緩存存在，就不需要后面的解析和執(zhí)行步驟，效率會(huì)大大提高。

MySQL緩存的數(shù)據(jù)是以key-value的形式存在的，key就是我們的查詢(xún)sql語(yǔ)句，value就是這個(gè)sql語(yǔ)句對(duì)應(yīng)的查詢(xún)結(jié)果。

那這個(gè)時(shí)候我們不禁會(huì)想，數(shù)據(jù)庫(kù)的數(shù)據(jù)如果經(jīng)常變更是不是緩存需要及時(shí)失效，這樣在下次查詢(xún)的時(shí)候我們就可以獲取到最新的數(shù)據(jù)了。

是的，MySQL只要表的數(shù)據(jù)或者表結(jié)構(gòu)有變化，這張表的所有緩存都會(huì)失效。所以如果是一張經(jīng)常涉及到增刪改的表，緩存并沒(méi)有太多實(shí)際的意義，可能剛加了緩存接下來(lái)就更新了，費(fèi)了老大勁加的緩存又失效了。但是如果我們的表是系統(tǒng)配置這類(lèi)的靜態(tài)表，緩存就能起到作用。

在開(kāi)發(fā)中，如果我們測(cè)試某個(gè)sql的執(zhí)行時(shí)間，首先要確定緩存是否可用。查詢(xún)緩存是否可用的命令：

mysql> show variables like '%have_query_cache%'; 

緩存是默認(rèn)可用的：

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

其中可選項(xiàng)為：0、1、2;0代表不使用緩存，1代表使用緩存，2代表根據(jù)需要使用。

也可以使用命令：

mysql> set global query_cache_type = 0; 

查看緩存是否開(kāi)啟：

mysql> select @@query_cache_type; 

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

mysql> select SQL_CACHE * from user; 

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

解析器

開(kāi)始真正執(zhí)行sql語(yǔ)句時(shí)，解析器會(huì)先分析我們輸入的sql語(yǔ)句，MySQL解析器將sql語(yǔ)句解析成內(nèi)部數(shù)據(jù)結(jié)構(gòu)(解析樹(shù))，然后優(yōu)化器就可以對(duì)其優(yōu)化。

我們給解析器的sql語(yǔ)句是字符串和空格組成的，解析器第一步是解析出來(lái)字符串，識(shí)別出里面的每個(gè)字符串代表的意思。

解析器會(huì)識(shí)別sql關(guān)鍵字，從而知道我們是在查詢(xún)還是更新。解析器將字符串'user'識(shí)別為表名字，把字符串'id'識(shí)別為列。解析器識(shí)別了字符串之后，就開(kāi)始校驗(yàn)我們給的字符串是否符合語(yǔ)法規(guī)范。

解析器會(huì)驗(yàn)證語(yǔ)法，還會(huì)根據(jù)解析到的表和列驗(yàn)證表和列是否存在。

如果表或者列不存在，或者語(yǔ)法有問(wèn)題，我們可以收到錯(cuò)誤信息。

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 

 優(yōu)化器

經(jīng)過(guò)解析器處理，得到了解析樹(shù)。這個(gè)時(shí)候MySQL已經(jīng)明確知道自己要做什么了，但是在開(kāi)始執(zhí)行之前還會(huì)對(duì)sql進(jìn)行優(yōu)化。

優(yōu)化器對(duì)sql語(yǔ)句的優(yōu)化包括：重寫(xiě)查詢(xún)、決定表的讀寫(xiě)順序、選擇合適的索引等。

優(yōu)化器涉及的內(nèi)容比較多，我們先對(duì)它有個(gè)初步印象，后續(xù)我們?cè)僭敿?xì)了解它。

經(jīng)過(guò)優(yōu)化器之后，sql語(yǔ)句的執(zhí)行方案就已經(jīng)確定了，解析來(lái)就進(jìn)入執(zhí)行器開(kāi)始執(zhí)行了。

執(zhí)行器

執(zhí)行器執(zhí)行sql語(yǔ)句的時(shí)候，會(huì)先驗(yàn)證是否有對(duì)這個(gè)表的權(quán)限，如果沒(méi)有權(quán)限就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤信息。如果有權(quán)限，則會(huì)打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的執(zhí)行引擎，去使用執(zhí)行引擎提供的接口。

存儲(chǔ)引擎

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。存儲(chǔ)引擎是插件式的，支持InnoDB、MyISAM、Memory等多種存儲(chǔ)引擎，MySQL也提供了一些第三方的存儲(chǔ)引擎，這種插件式的結(jié)構(gòu)設(shè)計(jì)，使得不同的公司可以根據(jù)自己的需求選擇不同的引擎。

現(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它是MySQL5.5.5版本之后默認(rèn)的存儲(chǔ)引擎，如果我們?cè)诮ū頃r(shí)不指定存儲(chǔ)引擎類(lèi)型，默認(rèn)使用的就是InnoDB。

不同的存儲(chǔ)引擎是公用Server層的，區(qū)分Server層和引擎層的功能對(duì)于后面我們學(xué)習(xí)鎖和事務(wù)比較重要。

不同的引擎保存數(shù)據(jù)和索引的方式是不相同的，但是表的定義是MySQL服務(wù)層負(fù)責(zé)的，這個(gè)是一致的。

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

InnoDB

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

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

 InnoDB的數(shù)據(jù)存儲(chǔ)在表空間中，它將每個(gè)表的數(shù)據(jù)和索引存放在單獨(dú)的文件中。‘user’表在磁盤(pán)上有兩個(gè)數(shù)據(jù)文件：

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

.ibd文件：數(shù)據(jù)和索引文件。

InnoDB采用的是MVCC多版本控制來(lái)支持高并發(fā)。并且它實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別，其默認(rèn)的隔離級(jí)別是可重復(fù)讀。它支持行鎖，并且通過(guò)間隙鎖策略防止幻讀的出現(xiàn)。

InnoDB是基于聚簇索引建立的，對(duì)基于主鍵的查詢(xún)有很高的性能。

MyISAM

我們先看一下'user_isam'表的信息 ，它的存儲(chǔ)引擎為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會(huì)將表存儲(chǔ)在兩個(gè)文件中：數(shù)據(jù)文件和索引文件。

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

.MYD文件：表示數(shù)據(jù)文件。

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

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

總結(jié)

MySQL邏輯架構(gòu)分為Server層和存儲(chǔ)引擎層。

Server層負(fù)責(zé)連接、緩存、解析 、優(yōu)化、執(zhí)行。存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。

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