了解MySQL（超詳細(xì)的MySQL工作原理 體系結(jié)構(gòu)）

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

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

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

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

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

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

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

a.將SQL語句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個結(jié)構(gòu)傳遞到后續(xù)步驟，以后SQL語句的傳遞和處理就是基于這個結(jié)構(gòu)的。

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

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

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

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

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

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

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

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

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

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

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

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

四、MySQL文件結(jié)構(gòu)

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

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

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

（2）獨(dú)立表空間：每個表擁有自己獨(dú)立的表空間用來存儲數(shù)據(jù)和索引。

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

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

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

五、InnoDB表存儲結(jié)構(gòu)

表空間∶表空間可看做是InnoDB存儲引擎邏輯結(jié)構(gòu)的最高層。

段 ：表空間由各個段組成，常見的段有數(shù)據(jù)段、索引段、回滾段等。

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

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

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

b.區(qū)=64個連續(xù)的頁=64*16Kb=1MB