MySQL 架构组成—存储引擎
博主QQ：819594300
博客地址：http://zpf666.blog.51cto.com/
有什么疑問的朋友可以聯系博主，博主會幫你們解答，謝謝支持！
MySQL Server 系統架構
上圖的大概簡易圖如下：
邏輯模塊組成:
MySQL邏輯結構可以看成是二層架構：
第一層我們通常叫做SQL Layer（SQL層，這一層是mysql的邏輯組件），在MySQL 數據庫系統處理底層數據之前的所有工作都是在這一層完成的，包括權限判斷，sql解析，執行計劃優化，query cache 的處理等等；
第二層就是存儲引擎層，我們通常叫做StorageEngine Layer，也就是底層數據存取操作實現部分，由多種存儲引擎共同組成。
所以，可以用如下一張最簡單的架構示意圖來表示MySQL 的基本架構，如圖所示
說明：從上圖看起來MySQL邏輯架構非常的簡單，但實際上每一層中都含有各自的很多小模塊，尤其是第一層SQLLayer，結構相當復雜的。
?
下面介紹mysql存儲引擎
一、?? mysql存儲引擎介紹：
Mysql5.1版本之前的版本，存儲引擎是需要mysql安裝時必須和mysql一起唄編譯并同時被安裝的。
Mysql5.1版本之后的版本，AB公司引入一個新的概念“插件式存儲引擎體系結構”，AB公司在架構改造的時候，讓存儲引擎和sql層各自獨立，耦合更小，可以做到在線加載新的存儲引擎（即可以將一個新的存儲引擎加載到一個正在運行的mysql中，而不影響mysql的正常運行）。
Mysql插件式存儲引擎主要包括：
MyISAM，Innodb，NDB Cluster，Maria，Falcon，Memory，Archive等。
其中最著名而且使用最為廣泛的MyISAM和Innodb兩種存儲引擎。
?
MyISAM是MySQL 最早的ISAM 存儲引擎的升級版本，也是MySQL 默認的存儲引擎。
Innodb實際上并不是MySQ公司的，而是第三方軟件公司Innobase（在2005 年被Oracle 公司所收購）所開發，其最大的特點是提供了事務控制等特性，所以使用者也非常廣泛。
其他的一些存儲引擎相對來說使用場景要稍微少一些，都是應用于某些特定的場景：
NDBCluster雖然也支持事務，但是主要是用于分布式高可用集群環境。
Maria是MySQL最新開發的對MyISAM的升級版存儲引擎。
Falcon是MySQL 公司自行研發的為了替代當前的Innodb存儲引擎的一款帶有事務等高級特性的數據庫存儲引擎。
Memory存儲引擎所有數據和索引均存儲于內存中，僅保存.frm文件在硬盤，所以主要是用于一些臨時表，或者對性能要求極高，但是允許在Crash （崩潰、癱瘓）的時候丟失數據的特定場景下，會消耗內存較大。
Archive是一個數據經過高比例壓縮存放的存儲引擎，僅支持insert，select，不支持update和delete，主要用于存放過期而且很少訪問的歷史信息，不支持索引。
?
MyISAM存儲引擎簡介：
是否為默認：mysql5.1之前為默認存儲引擎，之后就不是了。
文件組成：MyISAM存儲引擎的表在數據庫中，每一個表都被存放為三個以表名命名的物理文件。
?????????三個文件：①首先肯定會有任何存儲引擎都不可缺少的存放表結構定義信息的.frm文件
??????????????????? ②.MYD文件，存放該表的存儲數據
??????????????????? ③.MYI文件，存放該表的索引數據
??????????說明：每個表都有且僅有這樣三個文件做為MyISAM存儲類型的表的存儲，也就是說不管這個表有多少個索引，都是存放在同一個.MYI 文件中。
支持以下三種類型的索引：
??????? ???B-Tree 索引：所有的索引節點都按照balance tree （平衡樹）的數據結構來存儲，所有的索引數據節點都在葉節點。
??????????R-Tree 索引：存儲方式和B-Tree 索引有一些區別，主要設計用于為存儲空間和多維數據的字段做索引，所以目前的MySQL版本來說，也僅支持geometry（幾何） 類型的字段作索引。
?? ????????Full-text 索引：就是全文索引，他的存儲結構也是B-Tree。主要是為了解決在我們需要用like 查詢的低效問題。
??????????說明：MyISAM引擎上面的三種索引類型中，最常使用的就是B-Tree索引，偶爾會用到Full-text索引，但是R-Tree索引一般在系統中很少用得到。另外MyISAM的B-Tree索引又一個較大的限制，那就是參與一個索引的所有字段的長度紙盒不能超過1000字節。
是否支持事務：不支持事務
支持什么鎖：只支持表鎖，不支持行鎖
什么情況會造成表損壞：①mysql正在往表內寫入數據，突然被kill掉
????????????????????? ②主機宕機(crash）
????????????????????? ③磁盤硬件損壞
數據存放格式分為三種：①靜態（FIXED）固定長度
????????????????????? ②動態（DYNAMIC）可變長度
????????????????????? ③壓縮（COMPRESSED）
? ? ? ? ? ? ? ? ? ? ? ??說明：①當然三種格式中是否壓縮是完全可以任由自己選擇的，可以在創建表的時候通過row_format 來指定{COMPRESSED | DEFAULT}，也可以通過myisampack工具來進行壓縮，默認是不壓縮的。
?????????????????? ??????②而在非壓縮的情況下，是靜態還是動態，就和我們表中的字段的定義相關了。只要表中有可變長度類型的字段存在，那么該表就肯定是DYNAMIC 格式的，如果沒有任何可變長度的字段，則為FIXED 格式，當然，你也可以通過alter table 命令，強行將一個帶有VARCHAR 類型字段的DYNAMIC 的表轉換為FIXED，但是所帶來的結果是原VARCHAR 字段類型會被自動轉換成CHAR 類型。相反如果將FIXED 轉換為DYNAMIC，也會將CHAR類型字段轉換為VARCHAR 類型。
???????????????舉例說明一下說明②中的說明意思：
????????????????“name?? char(30)”,則用戶lisi就占30位（即字節）；
????????????????“name ??varchar(30)”,則用戶lisi就占4位（即字節）。
?
知識點擴展：如何根據表的記錄數量估算占用的磁盤空間
大概思路：首先先算一個表中一行有多少個字節。
然后根據數據庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤空間，這樣就可根據數據量估算規劃多大的空間。
舉例來說明怎么操作：
1）在數據庫bdqn中創建一個表tb
2）執行mysql>desc? bdqn.tb查看tb的表結構
10個字節+20個字節+2個字節+20字節+8字節+8字節+100個字節=168字節
即tb表一行數據最大有168個字節
3）假設每天增加1萬條記錄
大約需要10000×168÷1024÷1024=1.6MB，這樣就可以根據每天增加的記錄數，合理規劃好磁盤空間。
總結說明：MyISAM存儲引擎的某個表文件出錯之后，僅影響到該表，而不會影響到其他表，更不會影響到其他的數據庫。如果我們的數據庫正在運行過程中發現某個MyISAM表出現問題了，則可以在線通過check table 命令來嘗試校驗他，并可以通過repair table 命令來嘗試修復。在數據庫關閉狀態下，我們也可以通過myisamchk工具來對數據庫中某個（或某些）表進行檢測或者修復。不過強烈建議不到萬不得已不要輕易對表進行修復操作，修復之前盡量做好可能的備份工作，以免帶來不必要的后果。
?
Innodb 存儲引擎簡介：
特點：
1、支持事務：一個事務內的語句要么全部成功執行，要么全部不成功，事務會回滾到原始狀態。而myisam引擎不支持事務。
2、支持行鎖：而myisam只支持表鎖。
3、支持外鍵：而myisam不支持外鍵。外鍵的含義就是確保引用的完整性。比如A表引用B表的數據，就是引用外鍵。
4、它也有.frm文件，用來存放表結構定義相關的元數據，但和myisam不同的是innodb的表數據和索引數據是存放在一起的（即.ibd文件）。至于是獨表空間還是共享表空間，就是由用戶自己決定。
Innodb 的物理結構分為兩大部分：
1、數據文件（包含表數據和索引數據）
存放數據表中的數據和所有的索引數據，包括主鍵和其他普通索引。Innodb里面也有表空間概念，但是和oracle的表空間不同。Innnodb的表空間有兩種形式，一種是共享表空間，所有的表數據和所有的索引數據都在一個表空間里面，可以通過innodb_data_file_path來指定，但是增加數據文件需要重啟服務；另外一個就是獨享表空間，表數據和索引數據一起存放在.ibd文件里面。
雖然用戶可自定義是使用獨享還是共享表空間，但是共享表空間必須存在，因為innodb的undo信息和其他一些元數據信息都是存放在共享表空間的，共享表空間的數據文件可以設置固定大小或者自動擴展大小兩種形式。
當表空間不夠了，只有共享表空間才可以增加數據文件，具體的就是在innodb_data_file_path參數后面按照標準格式設置好數據文件的路徑和相關屬性即可。但是有一點需要注意，innodb在創建新數據文件的時候不會自動創建目錄的，如果指定的目錄不存在，重啟服務會報錯并無法啟動。
2、日志文件
Innodb的日志文件和oracle的redo日志（即重做日志文件，mysql里的innodb的redo日志叫事務日志）比較類似，同樣可以設置多個日志組（至少2個），同樣采用輪詢策略來順序的寫入。
由于Innodb 是事務的存儲引擎，所以系統Crash（宕機）對他來說并不能造成非常嚴重的損失，由于有redo日志（即事物日志）的存在，有checkpoint 機制的保護，Innodb 完全可以通過redo 日志將數據庫Crash 時刻已經完成但還沒有來得及將數據寫入磁盤的事務恢復，也能夠將所有部分完成并已經寫入磁盤的未完成事務回滾并將數據還原。
Innodb 不僅在功能特性方面和MyISAM 存儲引擎有較大區別，在配置上面也是單獨處理的。在MySQL 啟動參數文件（/etc/my.cnf）設置中，Innodb 的所有參數基本上都帶有前綴“innodb_”，不論是innodb 數據和日志相關，還是其他一些性能，事務等等相關的參數都是一樣。和所有Innodb 相關的系統變量一樣，所有的Innodb 相關的系統狀態值也同樣全部以“Innodb_”前綴。
?
?
Myisam和innodb的區別：
1、myisam不支持事務，innodb支持事務，innodb的autocommit是默認打開的，即每條sql語句會默認被封裝成一個事務，自動提交，但是這樣會影響速度，最好的辦法是把很多條SQL語句放在begin和commit之間，組成一個事務再提交過去，這樣的話很多條SQL語句就算是一個事務。
2、innodb支持行鎖，而myisam只支持表鎖。Myisam上同一個表讀鎖和寫鎖是互相排斥的，等待隊列中既有讀鎖又有寫鎖，則寫鎖優先級高，即使讀鎖先到，寫鎖后到，也是寫鎖優先執行。所以myisam不適合用于有大量查詢和修改并存的環境中，就是因為myisam只支持表鎖。
3、innodb支持外鍵，而myisam不支持外鍵。
4、innodb不支持全文索引，但是myisam支持全文索引
5、innodb的表數據和索引數據是在一個表空間內，myisam的表數據和索引數據是分開存放的。
?
?
Mysql自帶工具使用介紹
Mysql數據庫不僅提供了數據庫的服務端應用程序，同時還提供了大量的客戶端工具程序，
比如：mysql、mysqladmin、mysqldump
1、mysql命令
作用：為用戶提供一個命令行接口來操作管理mysql服務器。
語法結構：mysql? [options] ?[database]???????? //mysql[選項] [數據庫]
Mysql主要選項：
?????????①“-e”? //單詞execute（執行）的縮寫
??????????作用:告訴MySQL，我要執行“-e”后面的命令，而不是通過mysql連接登錄到mysql的交互式界面。
??????????應用場合：運維工程師要寫mysql檢查和監控腳本中要用到。
??????????下面是例子：
舉例1：
舉例2：
通過腳本創建數據庫、表以及對表進行增、改、刪、查操作
1）腳本內容如下：（此腳本在該博文附件有）
2）創建授予zhengpengfei用戶可以在限定的客戶端登錄
3）測試zhengpengfei用戶連接mysql服務器（前提是mysql服務器端要在防火墻開啟3306/tcp端口例外）
4）授予腳本可執行權限，并執行腳本
②“-E” //單詞vertical（垂直）縮寫
????????? 作用：使用了“-E”之后，登錄之后的所有查詢結果都將以縱列顯示，效果等同于我們在SQL語句后面加上“\G“結尾一樣。
????????? 舉例說明：
1）先創建一個數據庫和一個表，并添加數據
2）把“-E”加進去查詢數據
③“--prompt” ?//單詞提示的英語單詞
???????????????作用：定制自己的mysql提示符的顯示內容。一般情況下，登錄mysql，是“mysql>”界面，沒有其他附加信息。
???????????????建議：將登錄主機名、登錄用戶名、所在的數據庫名、時間這四項加入到提示內容，方便運維人員和DBA管理mysql數據庫。
????????????????個人強烈建議定義的提示符：\\u@\\h:\\d\\R:\\m:\\s>
???????????????顯示結果如下：
提示符解釋如下：
\u：表示用戶名
\h：表示主機名
\d“表示當前數據庫
\r：小時（12小時制）
\m：分鐘
\s：秒
\R：小時（24小時制）
④“--tee”??
作用：用來告訴mysql，要講所有輸入和輸出的內容都記錄進指定的文件中，方便日后檢查。
方式一：
方式二：
方式三：
2、mysqladmin
作用：提供的功能都是與mysql管理相關的各種功能
例如：mysql服務器的狀態檢查、各種統計信息的flush、創建/刪除數據庫、關閉mysql服務器等等
語法：mysqladmin? [options]? command??command??? //mysqladmin? [選項]? 命令? 命令
說明：mysqladmin所有能做的事，大部分都能通過mysql連接登錄mysql的交互式模式來完成，但是大部分還是通過mysqladmin來完成操作更加的簡單方便快捷。
下面介紹幾個常用的功能：
1、用ping命令可以很容易的檢測mysql服務器是都還能正常提供服務（alive表示是正常狀態）
說明：如果是測試本機的mysql服務，即把截圖中的192.168.1.6換成localhost即可，如果是從客戶端來測試mysql服務器的mysql服務是否正常，就寫mysql服務器的IP地址。同時還要注意的是：mysql服務器的防火墻要開啟3306/tcp端口例外。提前需要在創建好授權用戶（比如我截圖中的用戶“zhengpengfei”）。
2、用status命令可以獲取當前的mysql服務器的基本基本的狀態值
上圖中綠色部分的字段解釋如下：
說明：其中的slow queries是統計慢查詢SQL語句的數目的，前提是要開啟慢查詢功能，默認是關閉狀態。
3、用processlist獲取當前數據庫的連接線程信息：
說明一下：processlist經常與status聯合使用：
下面編寫一個腳本舉例說明三個參數命令在mysql監控腳本中的作用：($NF表示分隔字段的最后一個字段)（此腳本在該博文附件也有）
附加知識點（一）
附加知識點（二）
3、mysqldump
這個工具其功能就是將MySQL 服務器中的數據以SQL 語句的形式從數據庫中dump （轉儲）成文本 文件。mysqldump 是做為 MySQL 的一種邏輯備份工具。
4、mysqlbinlog
mysqlbinlog 程序的主要功能就是分析 MySQL Server 所產生的二進制日志（也就是 binlog）。 通過 mysqlbinlog，我們可以解析出 binlog 中指定時間段或者指定日志起始和結束位置的內容解析成 SQL 語句。
?
Mysqlslap性能測試MySQL二種存儲引擎
mysqlslap是mysql自帶的基準測試工具，可以模擬多個客戶端同時并發的向服務器發出查詢更新,給出了性能測試數據而且提供了多種引擎的性能比較，即它就是mysql的一個壓力測試工具。
關于mysqlslap的一些常用的選項：
說明：mixed是讀寫混合模式；csv是電子表格格式；“--number-of-queries”÷ “--concurrency”=總請求數÷并發數=每個客戶端的并發數量
查看 Mysql 數據庫默認最大連接數
可以看到 mysql5.7.13 默認是 151，注：不同版本默認最大連接數不差別。一般生產環境是不夠的，在 my.cnf[mysqld]下添加?max_connections=1024 增加到 1024，重啟 Mysql。 修改 my.cnf 文件并重起 mysqld 服務。
查看修改后的最大連接數
查看 Mysql 默認使用存儲引擎，如下查看：
現在我們來看一下具體測試的例子
用自帶的 SQL 腳本來測試:：
顯示結果如下所示：
測試說明：
模擬測試兩次讀寫并發，第一次 100，第二次 200，自動生成 SQL 腳本，測試表 包含 20 個 init 字段，30 個 char 字段，每次執行 2000 查詢請求。測試引擎分別是 myisam， innodb。
測試結果說明：
Myisam 第一次 100 客戶端同時發起增查用 1.019/s,第二次200客戶端同時發起
增查用 1.079
Innodb 第一次 100 客戶端同時發起增查用 1.671/s，第二次200客戶端同時發起
增查用 1.669
總結：MyISAM 存儲引擎處理性能是最好的（innodb性能不如myisam），也是最常用的，但不支持事務。InonDB 存儲引擎提供了事務型數據引擎（ACID），在事務型引擎里使用最多的。具有事務回滾，系統修復等特點。?
將 a.csv 拷貝到 windows 主機上，打開并生成圖表
總結
以上是生活随笔為你收集整理的MySQL 架构组成—存储引擎的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Redis数据持久化
- 下一篇： php代码优化 -- array_wal