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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• MyISAM 不支持事務(wù)、也不支持外鍵，其優(yōu)勢(shì)是訪問的速度快，對(duì)事務(wù)完整性 沒有要求或者以 SELECT、INSERT 為主的應(yīng)用。每個(gè) MyISAM 在磁盤上存儲(chǔ)成 三個(gè)文件。第一個(gè)文件的名字以表的名字開始，擴(kuò)展名指出文件類型。.frm 文件存儲(chǔ)表定義。數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)。索引文件的擴(kuò)展名是.MYI (MYIndex)
• InnoDB 存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比 MyISAM 的存儲(chǔ)引擎，InnoDB 寫的處理效率差一些，并且會(huì)占用更多的磁盤空 間以保存數(shù)據(jù)和索引。InnoDB：所有的表都保存在同一個(gè)數(shù)據(jù)文件中，InnoDB 表的大小只受限于操作系統(tǒng)文件的大小限制。Myisam 只緩存索引，不緩存真實(shí)數(shù)據(jù)；Innodb 不僅緩存索引還要緩存真實(shí)數(shù)據(jù)，對(duì)內(nèi)存要求較高，而且內(nèi)存大小對(duì)性能有決定性的影響。
• MEMORY 存儲(chǔ)引擎使用存在于內(nèi)存中的內(nèi)容來創(chuàng)建表。MEMORY 類型的表訪問非常的快，因?yàn)樗臄?shù)據(jù)是放在內(nèi)存中的，并且默認(rèn)使用 HASH 索引，但是 一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失。主要用于那些內(nèi)容變化不頻繁的代碼表或者作為統(tǒng)計(jì)操作的中間結(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è)計(jì)目的為了協(xié)調(diào)CPU速度與磁盤速度的鴻溝。因此跟新或者刪除的時(shí)候直接操作的是內(nèi)存的數(shù)據(jù)，先寫入重做日志，然后再修改內(nèi)存池里面的數(shù)據(jù)，最后定時(shí)刷新到磁盤上。

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

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

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

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

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

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

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

（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表存儲(chǔ)結(jié)構(gòu)

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

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

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

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

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

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