概述

MySQL 是我們平時開發(fā)中最常用的關系型數據庫，學習 MySQL 的邏輯架構相當于從全局去理解 MySQL 的運行機制，對于 MySQL 的學習和使用都會有較大的幫助。

邏輯架構示意圖

下面就是 MySQL 的邏輯架構示意圖：

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

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

Server 層功能模塊

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

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

連接器

連接器是負責與客戶端建立連接，權限管理，以及管理連接的功能模塊。

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

建立連接時，如果認證成功，連接器會把當前時刻的用戶權限快照作為這個連接后續(xù)的權限判斷邏輯的依據，直到連接斷開

• 這也意味著，當一個用戶成功建立了連接后，即使對這個用戶的權限進行了修改，也不會影響已經存在的連接權限判斷

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

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

查詢緩存

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

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

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

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

具體的原因有：

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

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

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

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

select SQL_CACHE * from user where id = 1;

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

分析器

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

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

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

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

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

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

優(yōu)化器

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

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

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

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

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

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

執(zhí)行器

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

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

首先查看當前用戶是否具有 SQL 語句中的目標表的對應操作權限

• 如果沒有，例如當前用戶沒有對于目標表的查詢權限，那么將會直接返回權限錯誤

如果有權限，那么將會調用當前使用的存儲引擎的對應操作接口，執(zhí)行這條 SQL 語句真正的執(zhí)行意圖

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

存儲引擎層

存儲引擎層負責真正的數據存儲和提取，其結構（對于 Server 層來說）是插件式的，封裝了具體的存儲引擎的操作邏輯。

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

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

InnoDB

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

使用 InnoDB 作為引擎來存儲的表， 會對應磁盤上的兩個文件：

• *.ibd（索引及數據文件），存儲的是聚集索引（索引與數據在同一棵 B+ 樹中），以及非聚集索引
• *.frm（表結構文件）存儲的是表結構

InnoDB 主要特性

InnoDB 的主要特性如下所示：

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

InnoDB 引擎下的查詢過程

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

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

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

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

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

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

MyISAM

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

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

使用 MyISAM 作為引擎來存儲的表， 會對應磁盤上的三個文件：

• *.myi（索引文件）存儲的是非聚集索引，葉子節(jié)點上存儲的是數據對應的地址（.myd 文件中的位置）
• *.myd（數據文件），存儲的是實際的數據
• *.frm（表結構文件），存儲的是表結構

MyISAM 的主要特性

MyISAM 的主要特性如下所示：

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

MyISAM 引擎下的查詢過程

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

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

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

InnoDB 和 MyISAM 的對比

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

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

對表的行數查詢的支持不同：

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

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

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

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

文件存儲方式不同：

• MyISAM ：一個表在磁盤上對應三個文件：*.myi（索引文件）、*.myd（數據文件）、 *.frm（表結構文件）
• Innodb：一個表在磁盤上對應兩個文件：*.ibd（數據及索引文件）、 *.frm（表結構文件）

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

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論