概述

MySQL 是我們平時(shí)開發(fā)中最常用的關(guān)系型數(shù)據(jù)庫，學(xué)習(xí) MySQL 的邏輯架構(gòu)相當(dāng)于從全局去理解 MySQL 的運(yùn)行機(jī)制，對于 MySQL 的學(xué)習(xí)和使用都會有較大的幫助。

邏輯架構(gòu)示意圖

下面就是 MySQL 的邏輯架構(gòu)示意圖：

從宏觀角度來看，MySQL 可以分為兩部分：

• Server 層：是客戶端與存儲引擎的中間層，提供了 MySQL 對外暴露的所有功能
• 存儲引擎層：負(fù)責(zé)數(shù)據(jù)的實(shí)際存儲和讀取

Server 層功能模塊

MySQL 的 Server 層提供了對外暴露的所有功能，例如請求連接、認(rèn)證、語法/詞法分析、執(zhí)行語句優(yōu)化、查詢緩存、內(nèi)置函數(shù)、語句執(zhí)行等功能。

這些功能分別由不同的功能模塊提供，模塊與模塊之間的分工非常明確；同時(shí)，也是由這些模塊的相互協(xié)作，最終給我們提供了可用的 MySQL 服務(wù)。

連接器

連接器是負(fù)責(zé)與客戶端建立連接，權(quán)限管理，以及管理連接的功能模塊。

連接器的功能職責(zé)比較清晰，但也有些細(xì)節(jié)需要關(guān)注：

建立連接時(shí)，如果認(rèn)證成功，連接器會把當(dāng)前時(shí)刻的用戶權(quán)限快照作為這個(gè)連接后續(xù)的權(quán)限判斷邏輯的依據(jù)，直到連接斷開

• 這也意味著，當(dāng)一個(gè)用戶成功建立了連接后，即使對這個(gè)用戶的權(quán)限進(jìn)行了修改，也不會影響已經(jīng)存在的連接權(quán)限判斷

連接建立成功后，連接的最大空閑時(shí)間（即什么操作都不執(zhí)行的時(shí)間）由 wait_timeout 參數(shù)控制，默認(rèn)為 8 小時(shí)

• 如果在空閑時(shí)間超時(shí)后，再發(fā)送操作請求，那么將會收到 MySQL 返回的錯誤消息：Lost connection to MySQL server during query
• 如果在空閑時(shí)間超時(shí)后，想要再次正常地發(fā)送請求，那么需要重新建立連接

查詢緩存

查詢緩存，主要用于將相同的查詢語句的結(jié)果給緩存起來。查詢緩存的工作原理如下所示：

• 執(zhí)行查詢語句之前，MySQL 會在內(nèi)存中查看之前是否執(zhí)行過相同的（一模一樣的）語句
• 如果有，那么直接將緩存中的結(jié)果集返回，并結(jié)束本次的查詢語句執(zhí)行流程
• 如果沒有，那么將正常走后面的邏輯，直到拿到結(jié)果集
• 查詢緩存拿到結(jié)果集后，將本條查詢語句與查詢得到的結(jié)果集以 K-V 的形式緩存到內(nèi)存中
• 將結(jié)果集返回，本次查詢語句執(zhí)行流程結(jié)束

既然有緩存，那么就需要考慮數(shù)據(jù)一致性的問題。MySQL 給出的解決方案就是當(dāng)一個(gè)表進(jìn)行了更新/新增操作后，這個(gè)表上的所有查詢緩存都會失效。

這樣一來，查詢緩存的功能就變得非常雞肋了。

具體的原因有：

• 查詢語句完全相同的概率可能并不高
• 執(zhí)行緩存操作本身也是需要耗費(fèi)時(shí)間和空間的
• 最主要的原因是清空緩存的觸發(fā)條件過于簡單，但是影響卻十分巨大（只有表上有一個(gè)更新/新增操作，那么這個(gè)表上的所有查詢緩存都會被清空）

綜合來說，即在大多數(shù)場景下，查詢緩存帶來的性能提升效果可能比不上執(zhí)行緩存操作本身帶來的性能消耗，即查詢緩存不值得使用。

當(dāng)然，在一些幾乎完全不會發(fā)生更新/新增操作的表上，這個(gè)查詢緩存還是可能會起到提升性能的作用的。

MySQL 8.0 之前可以通過將 query_cache_type 改為 DEMAND，并在查詢語句的查詢返回字段前增加 SQL_CACHE 關(guān)鍵字來顯示指定使用查詢緩存，例如：

select SQL_CACHE * from user where id = 1;

需要注意的是，MySQL 在 8.0 及之后的版本將查詢緩存功能徹底移除了。

分析器

在客戶端向服務(wù)端發(fā)送了一條 SQL 語句之后，MySQL 需要分析這條 SQL 語句是否合法；如果合法，那么這條 SQL 語句究竟是想要執(zhí)行什么操作。這就是分析器的職責(zé)。

分析的過程，主要分為詞法分析和語法分析

詞法分析：將輸入的 SQL 語句中的所有單詞（由空格隔開的字符串）識別為不同的含義

• 例如，把 select、update 給識別出來這是一個(gè)操作關(guān)鍵字，把輸入的 distinct 識別為一個(gè)去重的關(guān)鍵字

語法分析：根據(jù)詞法分析的結(jié)果以及當(dāng)前配置的 SQL 執(zhí)行模式（sql_mode 參數(shù)），判斷這條 SQL 語句是否合法

• 如果不合法，那么將會直接返回一個(gè)語法錯誤
• 如果合法，那么 MySQL 就會將 SQL 語句的執(zhí)行意圖給解析出來

優(yōu)化器

在經(jīng)過分析器的詞法分析和語法分析后，SQL 語句的執(zhí)行流程就來到了優(yōu)化器。

由上面的邏輯我們可以知道，到達(dá)優(yōu)化器的語句必定是一個(gè)合法的，且執(zhí)行意圖已知的 SQL 語句。

優(yōu)化器的作用，就是嘗試為 SQL 語句的執(zhí)行意圖，挑選出一種效率最高的執(zhí)行方案。

例如，在一個(gè)要執(zhí)行查詢語句的目標(biāo)數(shù)據(jù)表中，可能存在多個(gè)索引，優(yōu)化器將會根據(jù)這些索引的類型以及字段組合，結(jié)合查詢語句本身的條件，為其挑選一個(gè)最優(yōu)的索引，以便用于后續(xù)真正的數(shù)據(jù)查詢。

又或者，在一個(gè)有多表關(guān)聯(lián)的查詢語句中，根據(jù)表連接的字段以及各表的數(shù)據(jù)量，決定表與表之間的連接順序以及使用的算法。

優(yōu)化器的工作結(jié)束后，這條語句的執(zhí)行方案就確定下來了。值得一提的是，我們使用 explain 關(guān)鍵字用于分析一條 SQL 語句的執(zhí)行計(jì)劃時(shí)，返回的正是優(yōu)化器的一部分分析結(jié)果。

執(zhí)行器

MySQL 通過分析器已經(jīng)知道了 SQL 語句的執(zhí)行意圖，并且通過優(yōu)化器已經(jīng)為這條 SQL 語句挑選除了一種效率最高的執(zhí)行方案，那么 SQL 語句的執(zhí)行流程將會來到執(zhí)行器。

執(zhí)行器的主要工作為：

首先查看當(dāng)前用戶是否具有 SQL 語句中的目標(biāo)表的對應(yīng)操作權(quán)限

• 如果沒有，例如當(dāng)前用戶沒有對于目標(biāo)表的查詢權(quán)限，那么將會直接返回權(quán)限錯誤

如果有權(quán)限，那么將會調(diào)用當(dāng)前使用的存儲引擎的對應(yīng)操作接口，執(zhí)行這條 SQL 語句真正的執(zhí)行意圖

• 例如，當(dāng)前使用的存儲引擎是 InnoDB，當(dāng)前執(zhí)行的 SQL 語句是 select * from user where id=1，那么執(zhí)行器將會調(diào)用存儲引擎層的查詢接口執(zhí)行對于 user 表的具體查詢操作

存儲引擎層

存儲引擎層負(fù)責(zé)真正的數(shù)據(jù)存儲和提取，其結(jié)構(gòu)（對于 Server 層來說）是插件式的，封裝了具體的存儲引擎的操作邏輯。

存儲引擎的服務(wù)對象是表。意思就是說，不同的表可以使用不同的存儲引擎；同一個(gè)數(shù)據(jù)庫中的不同表也可能使用不同的存儲引擎。

下面將介紹常用的兩個(gè)存儲引擎：InnoDB 和 MyISAM。

InnoDB

InnoDB 是 MySQL 5.5 版本后的默認(rèn)存儲引擎，也是日常開發(fā)過程中使用的最多的存儲引擎。

使用 InnoDB 作為引擎來存儲的表， 會對應(yīng)磁盤上的兩個(gè)文件：

• *.ibd（索引及數(shù)據(jù)文件），存儲的是聚集索引（索引與數(shù)據(jù)在同一棵 B+ 樹中），以及非聚集索引
• *.frm（表結(jié)構(gòu)文件）存儲的是表結(jié)構(gòu)

InnoDB 主要特性

InnoDB 的主要特性如下所示：

• 支持事務(wù)
• 支持更細(xì)粒度的鎖（行鎖）
• 擁有崩潰后安全恢復(fù)（crash safe）能力
• 支持外鍵

InnoDB 引擎下的查詢過程

在使用了 InnoDB 引擎的表的單表查詢語句的執(zhí)行過程將會是這樣：

首先看查詢條件中是否使用了聚集索引

• 如果有，直接在聚集索引中進(jìn)行 B+Tree 查找直到找到數(shù)據(jù)，并將數(shù)據(jù)返回，時(shí)間復(fù)雜度為 O（logN）

如果不是聚集索引，則看查詢條件中是否命中的二級索引（非聚集索引）

• 如果可以命中二級索引，則首先在對應(yīng)的二級索引樹中查找，如果找到了，則取葉子節(jié)點(diǎn)上的聚集索引值，再回到聚集索引中（使用剛剛查找到的聚集索引值）進(jìn)行查詢（回表操作），并將數(shù)據(jù)返回，時(shí)間復(fù)雜度為 O（logN）

如果二級索引也不能命中，則直接在聚集索引樹中遍歷所有葉子節(jié)點(diǎn)，待全表掃描完后，再將中途查找到的符合條件的所有數(shù)據(jù)返回，時(shí)間復(fù)雜度為 O（N）

MyISAM

MyISAM 是 MySQL 最早出現(xiàn)的一批存儲引擎之一，但是現(xiàn)在在日常開發(fā)過程中已經(jīng)比較少用。 

MyISAM 也有很多優(yōu)點(diǎn)，但是有一個(gè)致命的缺點(diǎn)：不支持事務(wù)，沒有 crash safe 能力。

使用 MyISAM 作為引擎來存儲的表， 會對應(yīng)磁盤上的三個(gè)文件：

• *.myi（索引文件）存儲的是非聚集索引，葉子節(jié)點(diǎn)上存儲的是數(shù)據(jù)對應(yīng)的地址（.myd 文件中的位置）
• *.myd（數(shù)據(jù)文件），存儲的是實(shí)際的數(shù)據(jù)
• *.frm（表結(jié)構(gòu)文件），存儲的是表結(jié)構(gòu)

MyISAM 的主要特性

MyISAM 的主要特性如下所示：

• 只支持表鎖
• 內(nèi)置了一個(gè)計(jì)數(shù)器來存儲表的行數(shù)
• 延遲更新索引鍵：如果在創(chuàng)建表時(shí)指定了 DELAY_KEY_WRITE 參數(shù)，那么每次更新了（索引相關(guān)的）數(shù)據(jù)后，并不會立刻將修改的索引數(shù)據(jù)寫入磁盤中，而是采用了緩沖區(qū)+延時(shí)批量寫入的設(shè)計(jì)來延后地、批量地寫
• 入更新的索引數(shù)據(jù)。這樣可以極大地提升寫入的性能
• 設(shè)計(jì)簡單，數(shù)據(jù)以緊密的格式存儲：在更新較少的場景下性能表現(xiàn)很好

MyISAM 引擎下的查詢過程

在使用了 MyISAM 引擎的表的單表查詢語句的執(zhí)行過程將會是這樣：

首先看查詢條件中，是否有可以命中的索引

• 如果有，則在索引文件中進(jìn)行 B+Tree 查找直到找到數(shù)據(jù)的地址，然后再通過數(shù)據(jù)地址在數(shù)據(jù)文件中找到對應(yīng)的數(shù)據(jù)，時(shí)間復(fù)雜度為 O（logN）
• 如果沒有，則在數(shù)據(jù)文件中遍歷所有數(shù)據(jù)行，待全表掃描完成后，再將中途查找到的符合條件的所有數(shù)據(jù)返回，時(shí)間復(fù)雜度為 O（N）

InnoDB 和 MyISAM 的對比

InnoDB 和 MyISAM 的主要區(qū)別有：

• MyISAM 不支持事務(wù)，InnoDB 支持事務(wù)：兩個(gè)存儲引擎最大的兩個(gè)區(qū)別之一，MyISAM 不支持事務(wù)的特性導(dǎo)致了它在注重?cái)?shù)據(jù)一致性的場景下無法使用
• MyISAM 不支持崩潰后的安全恢復(fù)（crash safe），而 InnoDB 則支持：也是兩個(gè)存儲引擎最大的兩個(gè)區(qū)別之一，MyISAM 不支持 crash safe 導(dǎo)致了它在注重?cái)?shù)據(jù)安全的場景下無法使用
• MyISAM 只支持表鎖，而InnoDB 既支持表鎖也支持行級鎖：MyISAM 只支持表鎖的特性，在更新操作稍多的場景下，讀寫性能會大幅下降，這也導(dǎo)致了在這種場景下 MyISAM 的使用率將會比較低

對表的行數(shù)查詢的支持不同：

• MyISAM 內(nèi)置了一個(gè)計(jì)數(shù)器來存儲表的行數(shù)，在需要查詢表的行數(shù)時(shí)直接從計(jì)數(shù)器中拿出即可
• InnoDB 需要去統(tǒng)計(jì)所有的行數(shù)，在高版本的 MySQL 中，InnoDB 也會有一個(gè)存了行數(shù)的變量，但這只是個(gè)估計(jì)值，需要準(zhǔn)確的值時(shí)仍需要去實(shí)時(shí)統(tǒng)計(jì)

MyISAM 不支持外鍵，InnoDB 支持外鍵

delete from table 的處理方式不一樣：

• MyISAM直接重新建表
• InnoDB 會一行一行的刪除

文件存儲方式不同：

• MyISAM ：一個(gè)表在磁盤上對應(yīng)三個(gè)文件：*.myi（索引文件）、*.myd（數(shù)據(jù)文件）、 *.frm（表結(jié)構(gòu)文件）
• Innodb：一個(gè)表在磁盤上對應(yīng)兩個(gè)文件：*.ibd（數(shù)據(jù)及索引文件）、 *.frm（表結(jié)構(gòu)文件）

總的來說，在不考慮數(shù)據(jù)一致性以及數(shù)據(jù)安全性，且查詢操作遠(yuǎn)多于更新操作的場景下，可以考慮選擇 MyISAM 作為存儲引擎；否則都應(yīng)該選擇 Innodb 引擎

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。