超詳細的MySQL工作原理體系結構

piyanat 2020-08-16

展開全文

了解MySQL（超詳細的MySQL工作原理體系結構）

1.MySQL體系結構
2.MySQL內存結構
3.MySQL文件結構
4.innodb體系結構一、了解MySQL前你需要知道的引擎是什么:MySQL中的數據用各種不同的技術存儲在文件(或者內存)中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。當我們理解了引擎這個概念，自然而然就知道引擎層的作用就提供各種不同引擎給你選擇，然后用你選出來的引擎去處理sql語句二、MySQL體系結構MySQL 最重要、最與眾不同的特性是它的存儲引擎架構，這種架構的設計將查詢處理（Query Processing）及其他系統任務（Server Task）和數據的存儲/提取相分離。這種處理和存儲分離的設計可以在使用時根據性能、特性，以及其他需求來選擇數據存儲的方式。

由圖，可以看出MySQL最上層是連接組件。下面服務器是由連接池、管理工具和服務、SQL接口、解析器、優化器、緩存、存儲引擎、文件系統組成。

用戶：進行數據庫連接的人。
支持接口：是第三方語言提供和數據庫連接的接口，常見的有jdbc,odbc,c的標準api函數等等。
管理工具和服務：系統管理和控制工具，例如備份恢復、Mysql復制、集群等（見圖）
連接層：提供與用戶的連接服務，用于驗證登錄服務。

—> 連接池：由于每次建立建立需要消耗很多時間，連接池的作用就是將這些連接緩存下來，下次可以直接用已經建立好的連接，提升服務器性能。

服務層：完成大多數的核心服務功能。有sql接口，解析器parser,優化器optimizer,查詢緩存 cache/buffer 。

? —>SQL接口：接受用戶的SQL命令，并且返回用戶需要查詢的結果。比如select * from就是調用SQL Interface

? —>解析器: SQL命令傳遞到解析器的時候會被解析器驗證和解析。解析器是由Lex和YACC實現的，是一個很長的腳本。其功能是：

a.將SQL語句分解成數據結構，并將這個結構傳遞到后續步驟，以后SQL語句的傳遞和處理就是基于這個結構的。

? b.如果在分解構成中遇到錯誤，那么就說明這個sql語句是不合理的。

? —>優化器：查詢優化器，SQL語句在查詢之前會使用查詢優化器對查詢進行優化。他使用的是“選取-投影-聯接”策略進行查詢。舉一個例子：

select * from users where uname='admin';

1.這個select查詢先根據where語句進行選取，而不是先將表全部查詢出來以后再進行uname過濾。（選取）

? 2.這個select查詢先根據*進行屬性投影，而不是將屬性全部取出以后再進行過濾。（投影）

? 3.將這兩個查詢條件聯接起來生成最終查詢結果。(聯接)

? —>緩存器： 查詢緩存，如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。通過LRU算法將數據的冷端溢出，未來得及時刷新到磁盤的數據頁，叫臟頁。這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權限緩存等

? 簡而言之，服務層執行過程：sql語句通過sql接口，服務器如果緩存cache有命中查詢結果，直接讀取數據。如果沒有命中查詢結果，由解析器進行sql語句的解析，預處理，經過優化器進行優化后提交給引擎層。通俗地說—>服務層告訴引擎層要做什么。

引擎層: 提供各種存儲引擎，真正的負責MySQL中數據的存儲和提取。常見有innodb myisam 。innodb支持全文索引，事務（高并發），行鎖，myisam 性能優先。 mysql中查詢引擎語句:show engines.通俗地說—>引擎層會轉發服務層解析出來的sql語句告訴存儲層要做什么(增刪改查)并且告訴存儲層要以何種方式做(innodb myisam等等)。
存儲層：數據存儲層，主要是將數據存儲在運行于裸設備的文件系統之上，并完成與存儲引擎的交互。SQL的執行流程：數據庫通常不會被單獨使用，而是由其它編程語言通過SQL支持接口調用MySQL。由MySQL處理并返回執行結果。首先，其它編程語言通過SQL支持接口調用MySQL，MySQL收到請求后，會將該請求暫時放在連接池，并由管理服務與工具進行管理。當該請求從等待隊列進入到處理隊列時，管理器會將該請求傳給SQL接口，SQL接口接收到請求后，它會將請求進行hash處理并與緩存中的數據進行對比，如果匹配則通過緩存直接返回處理結果；否則，去文件系統查詢：由SQL接口傳給后面的解析器，解析器會判斷SQL語句是否正確，若正確則將其轉化為數據結構。解析器處理完畢后，便將處理后的請求傳給優化器控制器，它會產生多種執行計劃，最終數據庫會選擇最優的方案去執行。確定最優執行計劃后，SQL語句交由存儲引擎處理，存儲引擎將會到文件系統中取得相應的數據，并原路返回。在我們專業老師的pdf資料里面每一層也講得很細見下

創建新表時如果不指定存儲引擎，那么系統就會使用默認存儲引擎，MySQL5.5 之前的默認存儲引擎是 MyISAM，5.5 之后改為了 InnoDB。 MySQL 中同一個數據庫，不同的表格可以選擇不同的存儲引擎。

MyISAM 不支持事務、也不支持外鍵，其優勢是訪問的速度快，對事務完整性沒有要求或者以 SELECT、INSERT 為主的應用。每個 MyISAM 在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm 文件存儲表定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)
InnoDB 存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比 MyISAM 的存儲引擎，InnoDB 寫的處理效率差一些，并且會占用更多的磁盤空間以保存數據和索引。InnoDB：所有的表都保存在同一個數據文件中，InnoDB 表的大小只受限于操作系統文件的大小限制。Myisam 只緩存索引，不緩存真實數據；Innodb 不僅緩存索引還要緩存真實數據，對內存要求較高，而且內存大小對性能有決定性的影響。
MEMORY 存儲引擎使用存在于內存中的內容來創建表。MEMORY 類型的表訪問非常的快，因為它的數據是放在內存中的，并且默認使用 HASH 索引，但是一旦服務關閉，表中的數據就會丟失。主要用于那些內容變化不頻繁的代碼表或者作為統計操作的中間結果表。三、MySQL內存結構MySQL中內存大致分為：全局內存（Global buffer）、線程內存（Thread buffer） 兩大部分。

全局內存：緩沖池里面有數據緩存、索引緩存、鎖信息、插入緩存等等。此外還有重做日志緩存、額外的內存池。線程內存：Master Thread、IO Thread、Purage Thread、Page Cleaner Thread。

CheckPoint技術：緩沖池的設計目的為了協調CPU速度與磁盤速度的鴻溝。因此跟新或者刪除的時候直接操作的是內存的數據，先寫入重做日志，然后再修改內存池里面的數據，最后定時刷新到磁盤上。

四、MySQL文件結構

參數文件：啟動MySQL實例的時候，指定一些初始化參數，比如：緩沖池大小、數據庫文件路徑、用戶名密碼等。my.cnf讀取優先級是從左自右的順序,但是當默認讀取路徑都有配置文件時,最后讀取的參數的值，會覆蓋前面讀取的參數的值。/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf~/.my.cnf
日志文件：比如：錯誤日志、二進制日志、慢查詢日志、查詢日志等等。例如：通過show variables like 'error_log'來查看錯誤日志存放內容。
socket文件：當用UNIX域套接字方式進行連接的時候需要的文件。
pid文件：MySQL實例的進程ID文件。
表結構文件：用來存放MySQL表結構定義文件。.frm后綴命名的文件都是表結構文件，和存儲引擎類型無關。所有的表都會生成一個.frm文件；
存儲引擎文件：存儲引擎正在存儲了記錄和索引等數據。

表空間可以在邏輯上管理多個數據文件，而這些數據文件又可以分布在不同磁盤中這就使得一個表的數據、索引等信息可以被記錄在多個磁盤中。

（1）共享表空間：共享表空間文件以.ibdata*來命名；共享表空間下，innodb所有數據保存在一個單獨的表空間里面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。共享表空間主要存放double write、undo log（undo log沒有獨立的表空間，需要存放在共享表空間）

（2）獨立表空間：每個表擁有自己獨立的表空間用來存儲數據和索引。

（3）查看數據庫是否啟用獨立表空間：show variables like ‘innodb_file_per_table’;查看，innodb_file_per_table=ON,表示啟用了獨立表空間；

（4）使用獨立表空間的優點：a.如果使用軟鏈接將大表分配到不同的分區上，易于管理數據文件b.易于監控解決IO資源使用的問題；c.易于修復和恢復損壞的數據；d.相互獨立的，不會影響其他innodb表；e.導出導入只針對單個表，而不是整個共享表空間；f.解決單個文件大小的限制；g.對于大量的delete操作，更易于回收磁盤空間；h.碎片較少，易于整理optimize table；i.易于安全審計；j.易于備份如果在innodb表已創建后設置innodb_file_per_table，那么數據將不會遷移到單獨的表空間上，而是續集使用之前的共享表空間。只有新創建的表才會分離到自己的表空間文件。

（5）共享表空間的數據文件配置：innodb_data_file_path參數：設置innoDB共享表空間數據文件的名字和大小，例如innodb_data_file_path=ibdata1:12M:autoextend（初始大小12M，不足自增）innodb_data_home_dir參數：innodb引擎的共享表空間數據文件的存放目錄目前主要是使用獨立表空間，但是共享表空間也是需要的，共享表空間主要存放double write、undo log等。