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

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

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

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

—> 連接池：由于每次建立建立需要消耗很多時(shí)間，連接池的作用就是將這些連接緩存下來(lái)，下次可以直接用已經(jīng)建立好的連接，提升服務(wù)器性能。

• 服務(wù)層：完成大多數的核心服務(wù)功能。有sql接口，解析器parser,優(yōu)化器optimizer,查詢(xún)緩存 cache/buffer 。

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

? —>解析器: SQL命令傳遞到解析器的時(shí)候會(huì )被解析器驗證和解析。解析器是由Lex和YACC實(shí)現的，是一個(gè)很長(cháng)的腳本。其功能是：

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

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

? —>優(yōu)化器：查詢(xún)優(yōu)化器，SQL語(yǔ)句在查詢(xún)之前會(huì )使用查詢(xún)優(yōu)化器對查詢(xún)進(jìn)行優(yōu)化。他使用的是“選取-投影-聯(lián)接”策略進(jìn)行查詢(xún)。舉一個(gè)例子：

1.這個(gè)select查詢(xún)先根據where語(yǔ)句進(jìn)行選取，而不是先將表全部查詢(xún)出來(lái)以后再進(jìn)行uname過(guò)濾。（選取）

? 2.這個(gè)select查詢(xún)先根據*進(jìn)行屬性投影，而不是將屬性全部取出以后再進(jìn)行過(guò)濾。（投影）

? 3.將這兩個(gè)查詢(xún)條件聯(lián)接起來(lái)生成最終查詢(xún)結果。(聯(lián)接)

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

? 簡(jiǎn)而言之， 服務(wù)層執行過(guò)程：sql語(yǔ)句通過(guò)sql接口，服務(wù)器如果緩存cache有命中查詢(xún)結果，直接讀取數據。如果沒(méi)有命中查詢(xún)結果，由解析器進(jìn)行sql語(yǔ)句的解析，預處理，經(jīng)過(guò)優(yōu)化器進(jìn)行優(yōu)化后提交給引擎層。通俗地說(shuō)—>服務(wù)層告訴引擎層要做什么。

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

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

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

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

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

四、MySQL文件結構

1. 參數文件：?jiǎn)?dòng)MySQL實(shí)例的時(shí)候，指定一些初始化參數，比如：緩沖池大小、數據庫文件路徑、用戶(hù)名密碼等。my.cnf讀取優(yōu)先級是從左自右的順序,但是當默認讀取路徑都有配置文件時(shí),最后讀取的參數的值，會(huì )覆蓋前面讀取的參數的值。/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf~/.my.cnf
2. 日志文件：比如：錯誤日志、二進(jìn)制日志、慢查詢(xún)日志、查詢(xún)日志等等。例如：通過(guò)show variables like 'error_log'來(lái)查看錯誤日志存放內容。
3. socket文件：當用UNIX域套接字方式進(jìn)行連接的時(shí)候需要的文件。
4. pid文件：MySQL實(shí)例的進(jìn)程ID文件。
5. 表結構文件：用來(lái)存放MySQL表結構定義文件。.frm后綴命名的文件都是表結構文件，和存儲引擎類(lèi)型無(wú)關(guān)。所有的表都會(huì )生成一個(gè).frm文件；
6. 存儲引擎文件：存儲引擎正在存儲了記錄和索引等數據。

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

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

（2）獨立表空間：每個(gè)表?yè)碛凶约邯毩⒌谋砜臻g用來(lái)存儲數據和索引。

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

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

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

五、InnoDB表存儲結構

表空間∶表空間可看做是InnoDB存儲引擎邏輯結構的最高層。

段 ：表空間由各個(gè)段組成，常見(jiàn)的段有數據段、索引段、回滾段等。

區 ：由64個(gè)連續的頁(yè)組成，每個(gè)頁(yè)大小為16kb，即每個(gè)區大小為1MB。頁(yè):每頁(yè)16kb，且不能更改。常見(jiàn)的頁(yè)類(lèi)型有∶數據頁(yè)、Undo頁(yè)、系統頁(yè)、事務(wù)數據頁(yè)、插入緩沖位圖頁(yè)、插入緩沖空閑列表頁(yè)、未壓縮的二進(jìn)制大對象頁(yè)、壓縮的二進(jìn)制大對象頁(yè)。

行 ：InnoDB存儲引擎是面向行的(row-oriented)，每頁(yè)最多允許存放7992行數據。

a.每頁(yè)=16Kb（頁(yè)類(lèi)型：數據頁(yè)、undo頁(yè)、系統頁(yè)、事務(wù)數據頁(yè)、插入緩沖位圖頁(yè)、插入緩沖空閑列表頁(yè)、未壓縮的二進(jìn)制大對象頁(yè)、壓縮的二進(jìn)制大對象頁(yè)）

b.區=64個(gè)連續的頁(yè)=64*16Kb=1MB