MySQL存儲引擎是什么及作用

本文主要給大家簡單講講MySQL存儲引擎是什么及作用，相關專業(yè)術語大家可以上網(wǎng)查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL存儲引擎是什么及作用這篇文章可以給大家?guī)硪恍嶋H幫助。

一、mysql存儲引擎介紹：

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

但是從MySQL5.1 開始，MySQL AB 對其結構體系做了較大的改造，并引入了一個新的概念：插件式存儲引擎體系結構。MySQL AB 在架構改造的時候，讓存儲引擎層和sql layer各自更為獨立，耦合更小，甚至可以做到在線加載新的存儲引擎，也就是完全可以將一個新的存儲引擎加載到一個正在運行的MySQL 中，而不影響MySQL 的正常運行。插件式存儲引擎的架構，為存儲引擎的加載和移出更為靈活方便，也使自行開發(fā)存儲引擎更為方便簡單。

MySQL的插件式存儲引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，F(xiàn)alcon，Memory，Archive等，其中最著名而且使用最為廣泛的MyISAM 和Innodb兩種存儲引擎。MyISAM 是MySQL 最早的ISAM 存儲引擎的升級版本，也是MySQL 默認的存儲引擎。而Innodb 實際上并不是MySQ 公司的，而是第三方軟件公司Innobase（在2005 年被Oracle 公司所收購）所開發(fā)，其最大的特點是提供了事務控制等特性， 所以使用者也非常廣泛。

其他的一些存儲引擎相對來說使用場景要稍微少一些，都是應用于某些特定的場景，如NDB Cluster雖然也支持事務，但是主要是用于分布式高可用集群環(huán)境。Maria是MySQL最新開發(fā)的對MyISAM的升級版存儲引擎，F(xiàn)alcon 是MySQL 公司自行研發(fā)的為了替代當前的Innodb 存儲引擎的一款帶有事務等高級特性的數(shù)據(jù)庫存儲引擎。Memory 存儲引擎所有數(shù)據(jù)和索引均存儲于內存中，僅保存.frm文件在硬盤，所以主要是用于一些臨時表，或者對性能要求極高，但是允許在Crash 的時候丟失數(shù)據(jù)的特定場景下，會消耗內存較大。Archive 是一個數(shù)據(jù)經過高比例壓縮存放的存儲引擎，僅支持insert，select，不支持update和delete，主要用于存放過期而且很少訪問的歷史信息，不支持索引。

MyISAM 存儲引擎簡介：

1、mysql5.1之前默認存儲引擎。

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

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

B-Tree 索引

B-Tree 索引，就是所有的索引節(jié)點都按照balance tree 的數(shù)據(jù)結構來存儲，所有的索引數(shù)據(jù)節(jié)點都在葉節(jié)點。

R-Tree 索引

R-Tree 索引的存儲方式和b-tree 索引有一些區(qū)別，主要設計用于為存儲空間和多維數(shù)據(jù)的字段做索引，所以目前的MySQL 版本來說，也僅支持geometry 類型的字段作索引。

Full-text 索引

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

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

3、不支持事務

4、只有表鎖

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

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

主機宕機（Crash）

磁盤硬件故障

MyISAM存儲引擎的bug

6、雖然每一個MyISAM的表都是存放在一個相同后綴名的.MYD 文件中，但是每個文件的存放格式實際上可能并不是完全一樣的，因為MyISAM 的數(shù)據(jù)存放格式是分為靜態(tài)（FIXED）固定長度、動態(tài)（DYNAMIC）可變長度以及壓縮（COMPRESSED）這三種格式。當然三種格式中是否壓縮是完全可以任由自己選擇的，可以在創(chuàng)建表的時候通過ROW_FORMAT 來指定{COMPRESSED | DEFAULT}，也可以通過myisampack 工具來進行壓縮，默認是不壓縮的。而在非壓縮的情況下，是靜態(tài)還是動態(tài)，就和我們表中個字段的定義相關了。只要表中有可變長度類型的字段存在，那么該表就肯定是DYNAMIC 格式的，如果沒有任何可變長度的字段，則為FIXED 格式，當然，你也可以通過alter table 命令，強行將一個帶有VARCHAR 類型字段的DYNAMIC 的表轉換為FIXED，但是所帶來的結果是原VARCHAR 字段類型會被自動轉換成CHAR 類型。相反如果將FIXED 轉換為DYNAMIC，也會將CHAR 類型字段轉換為VARCHAR 類型，

知識點擴展：如何根據(jù)表的記錄數(shù)量估算占用的磁盤空間

首先先算一個表中一行有多少個字節(jié)。

然后根據(jù)數(shù)據(jù)庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤空間，這樣就可根據(jù)數(shù)據(jù)量估算規(guī)劃多大的空間。

例如在數(shù)據(jù)庫test中創(chuàng)建一張tb1表

執(zhí)行mysql>desc test1.tb1查看tb1的表結構

10個字節(jié)+20個字節(jié)+2個字節(jié)+20字節(jié)+8字節(jié)+8字節(jié)+100個字節(jié)=168字節(jié)

Tb1表的一個行有118個字節(jié)

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

這樣就可以根據(jù)每天增加的記錄數(shù)，合理規(guī)劃好磁盤空間了

MyISAM 存儲引擎的某個表文件出錯之后，僅影響到該表，而不會影響到其他表，更不會影響到其他的數(shù)據(jù)庫。如果我們的數(shù)據(jù)庫正在運行過程中發(fā)現(xiàn)某個MyISAM 表出現(xiàn)問題了，則可以在線通過check table 命令來嘗試校驗他，并可以通過repair table 命令來嘗試修復。在數(shù)據(jù)庫關閉狀態(tài)下，我們也可以通過myisamchk 工具來對數(shù)據(jù)庫中某個（或某些）表進行檢測或者修復。不過強烈建議不到萬不得已不要輕易對表進行修復操作，修復之前盡量做好可能的備份工作，以免帶來不必要的后果。

Innodb 存儲引擎簡介：

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

1、支持事務

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

2、鎖定機制的改進

Innodb 改變了MyISAM 的鎖機制，實現(xiàn)了行鎖。

3、實現(xiàn)外鍵

Innodb 實現(xiàn)了外鍵引用這一數(shù)據(jù)庫的重要特性。

4、Innodb 存儲引擎也和MyISAM 不太一樣，雖然也有.frm 文件來存放表結構定義相關的元數(shù)據(jù)，但是表數(shù)據(jù)和索引數(shù)據(jù)是存放在一起的。至于是每個表單獨存放還是所有表存放在一起，完全由用戶來決定。

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

1、數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）

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

雖然我們可以自行設定使用共享表空間還是獨享表空間來存放我們的表，但是共享表空間都是必須存在的，因為Innodb 的undo 信息和其他一些元數(shù)據(jù)信息都是存放在共享表空間里面的。共享表空間的數(shù)據(jù)文件是可以設置為固定大小和可自動擴展大小兩種形式的。

當我們的文件表空間快要用完的時候，我們必須要為其增加數(shù)據(jù)文件，當然，只有共享表空間有此操作。共享表空間增加數(shù)據(jù)文件的操作比較簡單， 只需要在innodb_data_file_path 參數(shù)后面按照標準格式設置好文件路徑和相關屬性即可，不過這里有一點需要注意的，就是Innodb 在創(chuàng)建新數(shù)據(jù)文件的時候是不會創(chuàng)建目錄的，如果指定目錄不存在，則會報錯并無法啟動。

2、日志文件

Innodb 的日志文件和Oracle 的redo 日志比較類似，同樣可以設置多個日志組（最少2

個），同樣采用輪循策略來順序的寫入。

由于Innodb 是事務的存儲引擎，所以系統(tǒng)Crash（宕機）對他來說并不能造成非常嚴重的損失，由于有redo日志（即事物日志）的存在，有checkpoint 機制的保護，Innodb 完全可以通過redo 日志將數(shù)據(jù)庫Crash 時刻已經完成但還沒有來得及將數(shù)據(jù)寫入磁盤的事務恢復，也能夠將所有部分完成并已經寫入磁盤的未完成事務回滾并將數(shù)據(jù)還原。

Innodb 不僅在功能特性方面和MyISAM 存儲引擎有較大區(qū)別，在配置上面也是單獨處理的。在MySQL 啟動參數(shù)文件（/etc/my.cnf）設置中，Innodb 的所有參數(shù)基本上都帶有前綴“innodb_”，不論是innodb 數(shù)據(jù)和日志相關，還是其他一些性能，事務等等相關的參數(shù)都是一樣。和所有Innodb 相關的系統(tǒng)變量一樣，所有的Innodb 相關的系統(tǒng)狀態(tài)值也同樣全部以“Innodb_”前綴。

MyISAM和InnoDB的區(qū)別

1、 MyISAM不支持事務，而InnoDB支持。InnoDB的AUTOCOMMIT默認是打開的，即每條SQL語句會默認被封裝成一個事務，自動提交，這樣會影響速度，所以最好是把多條SQL語句顯示放在begin和commit之間，組成一個事務去提交。

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支持數(shù)據(jù)行鎖定，MyISAM不支持行鎖定，只支持鎖定整個表。即MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM并發(fā)讀寫時如果等待隊列中既有讀請求又有寫請求，默認寫請求的優(yōu)先級高，即使讀請求先到，所以MyISAM不適合于有大量查詢和修改并存的情況，那樣查詢進程會長時間阻塞。因為MyISAM是鎖表。

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

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

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

mysqlslap是mysql自帶的基準測試工具,優(yōu)點:查詢數(shù)據(jù),語法簡單,靈活容易使用.該工具可以模擬多個客戶端同時并發(fā)的向云服務器發(fā)出查詢更新,給出了性能測試數(shù)據(jù)而且提供了多種引擎的性能比較.msqlslap為mysql性能優(yōu)化前后提供了直觀的驗證依據(jù),建議系統(tǒng)運維和DBA人員應該掌握一些常見的壓力測試工具,才能準確的掌握線上數(shù)據(jù)庫支撐的用戶流量上限及其抗壓性等問題。

現(xiàn)在看一下這個壓力測試工具mysqlslap，關于他的選項手冊上以及--help介紹的很詳細。  
這里解釋一下一些常用的選項。    
--concurrency代表并發(fā)數(shù)量，多個可以用逗號隔開。例如：concurrency=50,100,200    
--engines代表要測試的引擎，可以有多個，用分隔符隔開。    
--iterations代表要運行這些測試多少次，即運行多少次后，得到結果。    
--auto-generate-sql 代表用系統(tǒng)自己生成的SQL腳本來測試。    
--auto-generate-sql-load-type 代表要測試的是讀還是寫還是兩者混合的（read,write,update,mixed）    
--number-of-queries 代表總共要運行多少次查詢。每個客戶運行的查詢數(shù)量可以用查詢總數(shù)/并發(fā)數(shù)來計算。比如倒數(shù)第二個結果2=200/100。    
--debug-info 代表要額外輸出CPU以及內存的相關信息（注：只有在MySQL用--with-debug編譯時可）。    
--number-int-cols 代表測試表中的INTEGER類型的屬性有幾個。    
--number-char-cols代表測試表的char類型字段的數(shù)量。    
--create-schema 代表自己定義的模式（在MySQL中也就是庫即創(chuàng)建測試的數(shù)據(jù)庫）。    
--query 代表自己的SQL腳本。    
--only-print 如果只想打印看看SQL語句是什么，可以用這個選項。

--csv=name 生產CSV格式數(shù)據(jù)文件

查看Mysql數(shù)據(jù)庫默認最大連接數(shù)

可以看到mysql5.7.13默認是151，注：不同版本默認最大連接數(shù)不差別。一般生產環(huán)境是不夠的，在my.cnf [mysqld]下添加  max_connections=1024 增加到1024，重啟Mysql。

修改my.cnf文件并重起mysqld服務

查看修改后的最大連接數(shù)

查看Mysql默認使用存儲引擎，如下查看：

mysql> show engines;

現(xiàn)在我們來看一下具體測試的例子

[root@localhost ~]# 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

顯示結果：

測試說明：模擬測試兩次讀寫并發(fā)，第一次100，第二次200，自動生成SQL腳本，測試表包含20個init字段，30個char字段，每次執(zhí)行2000查詢請求。測試引擎分別是myisam，innodb。

測試結果說明：

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

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

由此可見MyISAM存儲引擎處理性能是最好的，也是最常用的，但不支持事務。InonDB存儲引擎提供了事務型數(shù)據(jù)引擎（ACID），在事務型引擎里使用最多的。具有事務回滾，系統(tǒng)修復等特點。

Mysqlslap測試工具生產CSV格式數(shù)據(jù)文件并轉換成圖表形式：

[root@localhost ~]# 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

執(zhí)行結果：

將a.csv拷貝到windows主機上，打開并生成圖表

用我們自己定義的SQL 腳本或語句來測試

首先準備好要測試的數(shù)據(jù)庫表，這里我們編寫一個生成表的腳本去完成

腳本內容如下：

[root@localhost ~]# cat /root/mysql3.sh

#!/bin/bash

HOSTNAME="localhost"

PORT="3306"

USERNAME="root"

PASSWORD="123.abc"

DBNAME="test1"

TABLENAME="tb1"

#create database

mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"

create_db_sql="create database ifnot exists ${DBNAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#create table

create_table_sql="create table ifnot exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) notnull,stusex char(1) not null,cardid varchar(20) not null,birthdaydatetime,entertime datetime,address varchar(100) default null)"

mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"

#insert data to table

i=1

while [ $i -le 20000 ]

do

insert_sql="insert into${TABLENAME} values($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguobeijingshi changpinqu')"

mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"

let i++

done

#select data

select_sql="select count(*) from${TABLENAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

授權腳本x執(zhí)行權限

[root@localhost ~]# chmod +x/root/mysql3.sh

執(zhí)行腳本mysql3.sh生成mysqlslap工具需要的測試表

[root@localhost ~]# /root/mysql3.sh

執(zhí)行mysqlslap工具進行測試

[root@localhost ~]#  mysqlslap --defaults-file=/etc/my.cnf--concurrency=10,20 --iterations=1 --create-schema='test1' --query='select *from test1.tb1' --engine=myisam,innodb --number-of-queries=2000 -uroot -p123456–verbose

顯示結果：

mysqlslap: [Warning] Using a password onthe command line interface can be insecure.

Benchmark

         Runningfor engine myisam

         Averagenumber of seconds to run all queries: 14.128 seconds

         Minimumnumber of seconds to run all queries: 14.128 seconds

         Maximumnumber of seconds to run all queries: 14.128 seconds

         Numberof clients running queries: 10

         Averagenumber of queries per client: 200

Benchmark

         Runningfor engine myisam

         Averagenumber of seconds to run all queries: 13.646 seconds

         Minimumnumber of seconds to run all queries: 13.646 seconds

         Maximumnumber of seconds to run all queries: 13.646 seconds

         Numberof clients running queries: 20

         Averagenumber of queries per client: 100

Benchmark

         Runningfor engine innodb

         Averagenumber of seconds to run all queries: 13.837 seconds

         Minimumnumber of seconds to run all queries: 13.837 seconds

         Maximumnumber of seconds to run all queries: 13.837 seconds

         Numberof clients running queries: 10

         Averagenumber of queries per client: 200

Benchmark

         Runningfor engine innodb

         Averagenumber of seconds to run all queries: 13.768 seconds

         Minimumnumber of seconds to run all queries: 13.768 seconds

         Maximumnumber of seconds to run all queries: 13.768 seconds

         Numberof clients running queries: 20

         Averagenumber of queries per client: 100

注：通過mysqlslap工具對mysql server進行壓力測試，可以通過--concurrency、--number-of-queries等選項的值查看每次測試的結果，通過反復測試、優(yōu)化得出mysql server的最大并發(fā)數(shù)。

如果mysqlslap工具輸出結果為Segmentation fault (core dumped)基本表示走超出mysql server的負載。

MySQL存儲引擎是什么及作用就先給大家講到這里，對于其它相關問題大家想要了解的可以持續(xù)關注我們的行業(yè)資訊。我們的板塊內容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。