MySQL 架構組成--儲存引擎

標籤：blank   border   target   

http://hongge.blog.51cto.com/

MySQL Server 系統架構

650) this.width=650;" title="clip_image002" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M01/86/CD/wKioL1fLlsXDPmOWAADCwqpKIro977.jpg" "449" height="290" />

在前一節中我們學習了mysql的邏輯模組組成，接下來我們來學習mysql儲存引擎。

一、mysql儲存引擎介紹：

MySQL在5.1（不包括）之前的版本中，儲存引擎是需要在MySQL 安裝的時候就必須和MySQL 一起被編譯並同時被安裝的。

但是從MySQL5.1 開始，MySQL AB 對其結構體系做了較大的改造，並引入了一個新的概念：外掛程式式儲存引擎體繫結構。MySQL AB 在架構改造的時候，讓儲存引擎層和sql layer各自更為獨立，耦合更小，甚至可以做到線上載入新的儲存引擎，也就是完全可以將一個新的儲存引擎載入到一個正在啟動並執行MySQL 中，而不影響MySQL 的正常運行。外掛程式式儲存引擎的

架構，為儲存引擎的載入和移出更為靈活方便，也使自行開發儲存體引擎更為方便簡單。

MySQL的外掛程式式儲存引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，Falcon，Memory，Archive等，其中最著名而且使用最為廣泛的MyISAM 和Innodb兩種儲存引擎。MyISAM 是MySQL 最早的ISAM 儲存引擎的升級版本，也是MySQL 預設的儲存引擎。而Innodb 實際上並不是MySQ 公司的，而是第三方軟體公司Innobase（在2005 年被Oracle 公司所收購）所開發，其最大的特點是提供了事務控制等特性， 所以使用者也非常廣泛。

其他的一些儲存引擎相對來說使用情境要稍微少一些，都是應用於某些特定的情境，如NDB Cluster雖然也支援事務，但是主要是用於分布式高可用叢集環境。Maria是MySQL最新開發的對MyISAM的升級版儲存引擎，Falcon 是MySQL 公司自行研發的為了替代當前的Innodb 儲存引擎的一款帶有事務等進階特性的資料庫儲存引擎。Memory 儲存引擎所有資料和索引均儲存於記憶體中，僅儲存.frm檔案在硬碟，所以主要是用於一些暫存資料表，或者對效能要求極高，但是允許在Crash 的時候遺失資料的特定情境下，會消耗記憶體較大。Archive 是一個資料經過高比例壓縮存放的儲存引擎，僅支援insert，select，不支援update和delete，主要用於存放到期而且很少訪問的曆史資訊，不支援索引。

MyISAM 儲存引擎簡介：

1、mysql5.1之前預設儲存引擎。

2、MyISAM 儲存引擎的表在資料庫中，每一個表都被存放為三個以表名命名的物理檔案。首先肯定會有任何儲存引擎都不可缺少的存放表結構定義資訊的.frm 檔案，另外還有.MYD和.MYI 檔案，分別存放了表的資料（.MYD）和索引資料（.MYI）。每個表都有且僅有這樣三個檔案做為MyISAM 儲存類型的表的儲存，也就是說不管這個表有多少個索引，都是存放在同一個.MYI 檔案中。

3、MyISAM 支援以下三種類型的索引：

B-Tree 索引

B-Tree 索引，就是所有的索引節點都按照balance tree 的資料結構來儲存，所有的索引資料節點都在分葉節點。

R-Tree 索引

R-Tree 索引的儲存方式和b-tree 索引有一些區別，主要設計用於為儲存空間和多維資料的欄位做索引，所以目前的MySQL 版本來說，也僅支援geometry 類型的欄位作索引。

Full-text 索引

Full-text 索引就是全文索引，他的儲存結構也是b-tree。主要是為瞭解決在我們需要用like 查詢的低效問題。

MyISAM 上面三種索引類型中，最經常使用的就是B-Tree 索引了，偶爾會使用到Fulltext，但是R-Tree 索引一般系統中都是很少用到的。另外MyISAM 的B-Tree 索引有一個較大的限制，那就是參與一個索引的所有欄位的長度之和不能超過1000 位元組。

3、不支援事務

4、只有表鎖

5、如下情況會造成表損壞：

Mysqld正在寫入該表時，被kill掉

主機宕機（Crash）

磁碟硬體故障

MyISAM儲存引擎的bug

6、雖然每一個MyISAM的表都是存放在一個相同尾碼名的.MYD 檔案中，但是每個檔案的存放格式實際上可能並不是完全一樣的，因為MyISAM 的資料存放格式是分為靜態（FIXED）固定長度、動態（DYNAMIC）可變長度以及壓縮（COMPRESSED）這三種格式。當然三種格式中是否壓縮是完全可以任由自己選擇的，可以在建立表的時候通過ROW_FORMAT 來指定{COMPRESSED | DEFAULT}，也可以通過myisampack 工具來進行壓縮，預設是不壓縮的。而在非壓縮的情況下，是靜態還是動態，就和我們表中個欄位的定義相關了。只要表中有可變長度類型的欄位存在，那麼該表就肯定是DYNAMIC 格式的，如果沒有任何可變長度的欄位，則為FIXED 格式，當然，你也可以通過alter table 命令，強行將一個帶有VARCHAR 類型欄位的DYNAMIC 的錶轉換為FIXED，但是所帶來的結果是原VARCHAR 欄位類型會被自動轉換成CHAR 類型。相反如果將FIXED 轉換為DYNAMIC，也會將CHAR 類型欄位轉換為VARCHAR 類型，

知識點擴充：如何根據表的記錄數量估算佔用的磁碟空間

首先先算一個表中一行有多少個位元組。

然後根據資料庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬碟空間，這樣就可根據資料量估算規劃多大的空間。

例如在資料庫test中建立一張tb1表

650) this.width=650;" title="clip_image003" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M00/86/CD/wKioL1fLlsbwZLIgAACCSvX43Ys960.png" "396" height="178" />

執行mysql>desc test1.tb1查看tb1的表結構

650) this.width=650;" title="clip_image005" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M02/86/CE/wKiom1fLlsfDQifsAABek4lW6Kk937.jpg" "400" height="154" />

10個位元組+20個位元組+2個位元組+20位元組+8位元組+8位元組+100個位元組=168位元組

Tb1表的一個行有118個位元組

如果每天增加10000條記錄，大約需要10000x168/1024/1024=1.6MB

這樣就可以根據每天增加的記錄數，合理規劃好磁碟空間了

MyISAM 儲存引擎的某個表檔案出錯之後，僅影響到該表，而不會影響到其他表，更不會影響到其他的資料庫。如果我們的資料庫正在運行過程中發現某個MyISAM 表出現問題了，則可以線上通過check table 命令來嘗試校正他，並可以通過repair table 命令來嘗試修複。在資料庫關閉狀態下，我們也可以通過myisamchk 工具來對資料庫中某個（或某些）表進行檢測或者修複。不過強烈建議不到萬不得已不要輕易對錶進行修複操作，修複之前盡量做好可能的備份工作，以免帶來不必要的後果。

Innodb 儲存引擎簡介：

Innodb 之所以能如此受寵，主要是在於其功能方面的較多特點：

1、支援事務

Innodb 在功能方面最重要的一點就是對事務的支援，這無疑是讓Innodb 成為MySQL最為流行的儲存引擎之一的一個非常重要原因。

2、鎖定機制的改進

Innodb 改變了MyISAM 的鎖機制，實現了行鎖。

3、實現外鍵

Innodb 實現了外鍵引用這一資料庫的重要特性。

4、Innodb 儲存引擎也和MyISAM 不太一樣，雖然也有.frm 檔案來存放表結構定義相關的中繼資料，但是表資料和索引資料是存放在一起的。至於是每個表單獨存放還是所有表存放在一起，完全由使用者來決定。

Innodb 的物理結構分為兩大部分：

1、資料檔案（表資料和索引資料）

存放資料表中的資料和所有的索引資料，包括主鍵和其他普通索引。在Innodb 中，存在了資料表空間（tablespace）這樣一個概念，但是他和Oracle 的資料表空間又有較大的不同。首先，Innodb 的資料表空間分為兩種形式。一種是共用資料表空間，也就是所有表和索引資料被存放在同一個資料表空間（一個或多個資料檔案）中，通過innodb_data_file_path 來指定，增加資料檔案需要停機重啟。另外一種是獨享資料表空間，也就是每個表的資料和索引被存放在一個單獨的.ibd 檔案中。

雖然我們可以自行設定使用共用資料表空間還是獨享資料表空間來存放我們的表，但是共用資料表空間都是必須存在的，因為Innodb 的undo 資訊和其他一些中繼資料資訊都是存放在共用資料表空間裡面的。共用資料表空間的資料檔案是可以設定為固定大小和可自動擴充大小兩種形式的。

當我們的檔案資料表空間快要用完的時候，我們必須要為其增加資料檔案，當然，只有共用資料表空間有此操作。共用資料表空間增加資料檔案的操作比較簡單， 只需要在innodb_data_file_path 參數後面按照標準格式設定好檔案路徑和相關屬性即可，不過這裡有一點需要注意的，就是Innodb 在建立新資料檔案的時候是不會建立目錄的，如果指定目錄不存在，則會報錯並無法啟動。

2、記錄檔

Innodb 的記錄檔和Oracle 的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之間，組成一個事務去提交。

650) this.width=650;" title="clip_image006" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M02/86/CD/wKioL1fLlsfT1aVdAABLe9RzwWY738.png" "427" height="139" />

mysql> use test_db;

mysql> show tables;

mysql> desc tb1;

mysql> begin;

mysql> insert into tb1 values(‘lisi‘,1);

mysql> insert into tb1 values(‘zhangsan‘,2);

mysql> commit;

2、InnoDB支援資料行鎖定，MyISAM不支援行鎖定，只支援鎖定整個表。即MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM並發讀寫時如果等待隊列中既有讀請求又有寫請求，預設寫請求的優先順序高，即使讀請求先到，所以MyISAM不適合於有大量查詢和修改並存的情況，那樣查詢進程會長時間阻塞。因為MyISAM是鎖表。

3、InnoDB支援外鍵，MyISAM不支援。

4、InnoDB不支援全文索引，而MyISAM支援。

二、Mysqlslap效能測試MySQL二種儲存引擎

mysqlslap是mysql內建的基準測試載入器,優點:查詢資料,文法簡單,靈活容易使用.該工具可以類比多個用戶端同時並發的向伺服器發出查詢更新,給出了效能測試資料而且提供了多種引擎的效能比較.msqlslap為mysql效能最佳化前後提供了直觀的驗證依據,建議系統營運和DBA人員應該掌握一些常見的壓力測試工具,才能準確的掌握線上資料庫支撐的使用者資料傳輸量上限及其抗壓性等問題。

現在看一下這個壓力測試工具mysqlslap，關於他的選項手冊上以及--help介紹的很詳細。
這裡解釋一下一些常用的選項。
--concurrency代表並發數量，多個可以用逗號隔開。例如：concurrency=50,100,200
--engines代表要測試的引擎，可以有多個，用分隔字元隔開。
--iterations代表要運行這些測試多少次，即運行多少次後，得到結果。
--auto-generate-sql 代表用系統自己產生的SQL指令碼來測試。
--auto-generate-sql-load-type 代表要測試的是讀還是寫還是兩者混合的（read,write,update,mixed）
--number-of-queries 代表總共要運行多少次查詢。每個客戶啟動並執行查詢數量可以用查詢總數/並發數來計算。比如倒數第二個結果2=200/100。
--debug-info 代表要額外輸出CPU以及記憶體的相關資訊（註：只有在MySQL用--with-debug編譯時間可）。
--number-int-cols 代表測試表中的INTEGER類型的屬性有幾個。
--number-char-cols代表測試表的char類型欄位的數量。
--create-schema 代表自己定義的模式（在MySQL中也就是庫即建立測試的資料庫）。
--query 代表自己的SQL指令碼。
--only-print 如果只想列印看看SQL語句是什麼，可以用這個選項。

--csv=name 生產CSV格式資料檔案

查看Mysql資料庫預設最大串連數

650) this.width=650;" title="clip_image008" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image008" src="http://s3.51cto.com/wyfs02/M00/86/CD/wKioL1fLlsjgzZHoAAAscuk9ZSs881.jpg" "414" height="71" />

可以看到mysql5.7.13預設是151，註：不同版本預設最大串連數不差別。一般生產環境是不夠的，在my.cnf [mysqld]下添加  max_connections=1024 增加到1024，重啟Mysql。

修改my.cnf檔案並重起mysqld服務

650) this.width=650;" title="clip_image009" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M01/86/CE/wKiom1fLlsjyphoZAAAc9dvguX0357.png" "414" height="55" />

查看修改後的最大串連數

650) this.width=650;" title="clip_image011" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image011" src="http://s3.51cto.com/wyfs02/M01/86/CD/wKioL1fLlsnz-jYLAAAq6GQIXRU209.jpg" "416" height="67" />

查看Mysql預設使用儲存引擎，如下查看：

mysql> show engines;

650) this.width=650;" title="clip_image013" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image013" src="http://s3.51cto.com/wyfs02/M00/86/CD/wKioL1fLlsmQb37_AAA9VZkQKd0201.jpg" "415" height="96" />

現在我們來看一下具體測試的例子

[[email protected] ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -p123456 –verbose

顯示結果：

650) this.width=650;" title="clip_image015" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image015" src="http://s3.51cto.com/wyfs02/M02/86/CE/wKiom1fLlsrzGJ7xAAC9HlXqMPE926.jpg" "431" height="322" />

測試說明：類比測試兩次讀寫並發，第一次100，第二次200，自動產生SQL指令碼，測試表包含20個init欄位，30個char欄位，每次執行2000查詢請求。測試引擎分別是myisam，innodb。

測試結果說明：

Myisam第一次100用戶端同時發起增查用0.413/s,第二次200用戶端同時發起增查用0.509/s

Innodb第一次100用戶端同時發起增查用0.692/s,第二次200用戶端同時發起增查用0.617/s

由此可見MyISAM儲存引擎處理效能是最好的，也是最常用的，但不支援事務。InonDB儲存引擎提供了事務型資料引擎（ACID），在事務型引擎裡使用最多的。具有交易回復，系統修複等特點。

Mysqlslap測試載入器生產CSV格式資料檔案並轉換成圖表形式：

[[email protected] ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -p123456 --csv=/root/a.csv

執行結果：

650) this.width=650;" title="clip_image017" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image017" src="http://s3.51cto.com/wyfs02/M01/86/CD/wKioL1fLlsvQbCZrAAAmFOIduvY623.jpg" "444" height="43" />

將a.csv拷貝到windows主機上，開啟並組建圖表

650) this.width=650;" title="clip_image019" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; padding-right: 0px" border="0" alt="clip_image019" src="http://s3.51cto.com/wyfs02/M00/86/CE/wKiom1fLlszCyOK0AAC1u1xFSbA562.jpg" "442" height="303" />

http://hongge.blog.51cto.com/

MySQL 架構組成--儲存引擎