本篇把MySQL最常用的存儲(chǔ)引擎給大家做一個(gè)介紹，然后通過(guò)插入、修改和并發(fā)實(shí)驗(yàn)來(lái)了解和驗(yàn)證一下它們之間的一些差異。

?

一、MySQL存儲(chǔ)引擎簡(jiǎn)介

存儲(chǔ)引擎在MySQL結(jié)構(gòu)里占據(jù)核心的位置，是上層抽象接口和存儲(chǔ)的物理文件之間的橋梁。每一種storage engine 架構(gòu)組件都是根據(jù)特定的場(chǎng)合來(lái)設(shè)計(jì)的，負(fù)責(zé)數(shù)據(jù)的 I/O 操作，并啟用一些特性的支持。

MySQL存儲(chǔ)引擎被設(shè)計(jì)為插件式結(jié)構(gòu)，每種存儲(chǔ)引擎可從運(yùn)行的mysql里動(dòng)態(tài)加載或卸載。我們可以在客戶(hù)端連接后用show plugins;查看當(dāng)前加載的插件，用install plugin xxx;或者 uninstall plugin xxx;來(lái)安裝或卸載。

?

查看服務(wù)器當(dāng)前支持的引擎命令：

mysql> show engines;

?

主要的幾種引擎介紹如下：

InnoDB：支持事務(wù)操作，支持行級(jí)鎖，支持外鍵。獨(dú)立表結(jié)構(gòu)的花每個(gè)表單文件存儲(chǔ)，是MySQL5.5之后的默認(rèn)引擎。

MyISAM：使用廣泛，數(shù)據(jù)量不是特別大時(shí)性能很高，是5.5之前的默認(rèn)引擎。

Memory：數(shù)據(jù)直接放在內(nèi)存，極快的訪問(wèn)速度，但是空間很受限。

MRG_MYISAM：可以把MyISAM表分組管理。

Federated：可以把不同物理服務(wù)器連接成一個(gè)邏輯服務(wù)器，適合分布式管理。

CSV：導(dǎo)入導(dǎo)出成CSV格式，便于和其他軟件數(shù)據(jù)交換。

?

我們可以配置php.ini文件或者在server啟動(dòng)時(shí)，可以通過(guò)--default-storage-engine參數(shù)來(lái)指定默認(rèn)的存儲(chǔ)引擎。也可以在mysql運(yùn)行狀態(tài)下動(dòng)態(tài)改變默認(rèn)引擎：

show variables like 'default_storage_engine';

SET default_storage_engine=MYISAM;

?

數(shù)據(jù)庫(kù)的每個(gè)表可以使用不同的引擎：

create table t_a(uid int,uname varchar(50)) engine=innodb;

也可以動(dòng)態(tài)修改表的引擎：

alter table t_a engine=MyISAM;

?

二、MySQL存儲(chǔ)引擎的文件組成與備份

MySQL主要的動(dòng)態(tài)文件有日志文件、配置文件和存儲(chǔ)引擎的數(shù)據(jù)文件

1、日志文件

種類(lèi)非常多，我們也可以在這些變量里找到innodb的特殊日志文件：

show variables like '%log%';

2、配置和連接文件

my.cnf是數(shù)據(jù)庫(kù)的主要配置文件，如果我們做了主從配置，則還有master.info等配置信息文件。

linux下支持tcp和socket連接，可以通過(guò)配置my.cnf或者連接時(shí)增加參數(shù)來(lái)確定mysql --protocol=tcp，如果是socket方式則一般會(huì)通過(guò)socket文件來(lái)連接/tmp/mysql.sock。

3、數(shù)據(jù)文件

每一種存儲(chǔ)引擎都有.frm 表元數(shù)據(jù)文件。然后每種引擎都有自己的一些特有特有格式的文件：

.myd (MyData)是MyISAM數(shù)據(jù)文件，.myi (MyIndex)是MyISAM索引文件(b-tree、full-text等)。

innodb的共享表空間存在ibdata文件里，如果配置成獨(dú)享表空間的話（mysql默認(rèn)）每個(gè)表還會(huì)有對(duì)應(yīng).ibb文件。我們可以通過(guò)變量查詢(xún)和設(shè)置這些配置：

show variables like ‘%innodb%’;? 其中innodb_file_per_table設(shè)置是否是獨(dú)享表空間，innodb_data_file_path 和innodb_data_home_dir用來(lái)指定表的存放位置。

?

備份：

1、邏輯備份

邏輯備份是不停機(jī)的情況下比較好的備份方式，通過(guò)mysqldump或者其他方式來(lái)導(dǎo)出sql語(yǔ)句。

2、物理備份

物理備份在某些情況是更加直接和快速的方式。myisam引擎因?yàn)槭欠鞘聞?wù)沒(méi)有獨(dú)立日志，一般備份3個(gè)文件即可，也可以通過(guò)mysqlhotcopy來(lái)進(jìn)行物理備份。

innodb 因?yàn)槭挛镄枰腥罩疚募绻谶\(yùn)行狀態(tài)則不能手工來(lái)備份，需要一些商業(yè)化的工具比如ibbackup來(lái)支持物理備份。

3、主從物理備份

因?yàn)槲锢韨浞菀话阈枰i庫(kù)，在線上數(shù)據(jù)庫(kù)上我們?nèi)绻O(shè)置了主從服務(wù)器并且有多臺(tái)從庫(kù)的話，可以暫停一臺(tái)從庫(kù)，然后實(shí)行物理備份。

?

三、插入和更新數(shù)據(jù)

我們先創(chuàng)建3個(gè)引擎的數(shù)據(jù)表user_myisam、user_innodb、user_memory，表的結(jié)構(gòu)是一樣的：

      create table user_myisam (



       uid int auto_increment,



       uname varchar(50) not null default '',



       type tinyint not null default 0,



       ctime timestamp not null default current_timestamp,



       primary key (uid)



) engine=myisam, charset=utf8;



 


    

我們?cè)谏蓴?shù)據(jù)時(shí)，可以使用一條條數(shù)據(jù)插入、導(dǎo)入sql文件、或者批量插入的方式進(jìn)行。

導(dǎo)入sql文件是有大小限制的，我們可以通過(guò)max_allowed_packet變量來(lái)查看，一般默認(rèn)為1M，所以導(dǎo)入大量數(shù)據(jù)時(shí)需要增大這個(gè)變量：

show variables like 'max_allowed_packet';

顯然，數(shù)據(jù)量很大時(shí)，批量插入的方式是效率最高的:

insert into tbl values(),(),()...

經(jīng)過(guò)對(duì)比，雖然memory引擎插入和查詢(xún)修改的速度都極快，單只支持幾萬(wàn)行數(shù)據(jù)，即使調(diào)大了內(nèi)存參數(shù)也只能支持10多萬(wàn)行。所以memory一般用在一些數(shù)據(jù)量比較小的特殊場(chǎng)合，比如在線用戶(hù)表、或者緩存一些配置信息等。

我們用批量插入的方式把myisam和innodb的表各插入了1千萬(wàn)行數(shù)據(jù)（每次插入1萬(wàn)行或更多），myisam的速度要稍快些，沒(méi)有調(diào)優(yōu)的情況下幾分鐘時(shí)間就可以了。

?

更新和查詢(xún)的數(shù)據(jù)對(duì)比：

在一個(gè)進(jìn)程操作的情況下，myisam的更新和查詢(xún)速度都會(huì)稍快于innodb。

特別注意的一點(diǎn)是，innodb查詢(xún)表的行數(shù)需要全表掃描，速度會(huì)非常慢，查詢(xún)1千萬(wàn)行數(shù)據(jù)的表最多時(shí)要6、7s，所以在項(xiàng)目里一定要控制innodb表的總數(shù)查詢(xún)，一定要緩存。而myisam因?yàn)楸４媪丝傂袛?shù)是極快的。

?

四、innodb的事務(wù)支持和鎖

innodb的事物支持4種隔離級(jí)別：

read uncommitted：臟讀，在自己的事務(wù)里能看到別的事務(wù)修改但未提交的數(shù)據(jù)。

read committed：不可重復(fù)讀，雖然別的事務(wù)未提交的數(shù)據(jù)看不到，但是提交后就可以了，所以不能多次讀取，數(shù)據(jù)可能不一致。

repeatable read：可重復(fù)讀，事務(wù)做了隔離，但還是可以并發(fā)的。

serializable：串行，最嚴(yán)格的方式，事務(wù)單行處理，不會(huì)并行。

查看當(dāng)前和全局的事務(wù)隔離級(jí)別：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

可以通過(guò)以下命令來(lái)改變?cè)O(shè)置：

set global transaction isolation level read uncommitted;

我們可以通過(guò)2個(gè)session然后設(shè)置set autocommit=0來(lái)進(jìn)行測(cè)試和驗(yàn)證這4種事務(wù)隔離級(jí)別的差別，在自己的項(xiàng)目里也可以根據(jù)情況來(lái)改變。越高的隔離級(jí)別對(duì)性能影響越大，innodb默認(rèn)是repeatable read方式。

?

mysql有3種鎖：

1、表級(jí)鎖：myisam的默認(rèn)形式，開(kāi)銷(xiāo)小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。可以查看表鎖的一些情況：

show status like 'table%';
2、行級(jí)鎖：innodb的默認(rèn)形式，開(kāi)銷(xiāo)大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

需要注意的是，innodb只有在能利用索引的操作時(shí)才執(zhí)行行級(jí)鎖，如果查詢(xún)或更新操作不能利用索引還是會(huì)使用表級(jí)鎖的。查看行鎖狀態(tài)：

show status like 'innodb_row_lock%';
3、頁(yè)面鎖：開(kāi)銷(xiāo)和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

?

五、并發(fā)測(cè)試與參數(shù)調(diào)優(yōu)總結(jié)

雖然在上面單進(jìn)程的情況下，myisam在插入查詢(xún)和更新等操作中性能都比較高，但是在我們模擬高并發(fā)的情況下，可以看出innodb的銷(xiāo)量明顯占優(yōu)了。

我們用apache的ab工具來(lái)測(cè)試3000次30個(gè)并發(fā)的請(qǐng)求，每個(gè)請(qǐng)求在1千萬(wàn)數(shù)據(jù)里隨機(jī)找5行數(shù)據(jù)進(jìn)行修改和查詢(xún)（用到索引），測(cè)試結(jié)果如下：

myisam的測(cè)試數(shù)據(jù)：

innodb的測(cè)試結(jié)果：

myisam的一些參數(shù)優(yōu)化：

read_buffer_size緩存大小

設(shè)置concurrent_insert為2，在尾部插入數(shù)據(jù)，不影響select

打開(kāi)delay_key_write

?

innodb的一些參數(shù)：

設(shè)置事務(wù)提交后數(shù)據(jù)保存方式：

innodb_flush_log_at_trx_commit

0 每秒保存 1 每事務(wù)保存 2 系統(tǒng)決定

?

innodb_buffer_pool緩存大小：

show status like 'innodb_buffer_pool%';

?

可以用show engine innodb status\G查看innodb的一些情況：

innodb_read_io_threads讀寫(xiě)進(jìn)程數(shù)

innodb_write_io_threads

innodb_io_capacity合并寫(xiě)入數(shù)量

innodb_io_capacity=5000;

set global innodb_stats_on_metadata=0;關(guān)閉元數(shù)據(jù)更新

?

?

經(jīng)過(guò)我們的一些操作對(duì)比，可以看出：

Memory雖然是高效的引擎，但是由于是臨時(shí)數(shù)據(jù)而且有數(shù)據(jù)量的限制，適合與性能要求高數(shù)據(jù)量小的地方，和緩存的效果類(lèi)似。

MyISAM適合數(shù)據(jù)量不是特別大并發(fā)不太高的大部分場(chǎng)合，性能都占優(yōu)，并且也支持全文檢索。如果不需要事務(wù)支持的話MyISAM絕對(duì)是最優(yōu)的方式。

而InnoDB 則更適合與大并發(fā)大數(shù)據(jù)量的場(chǎng)合，除了支持事務(wù)，在高并發(fā)時(shí)行級(jí)鎖的優(yōu)勢(shì)就會(huì)發(fā)揮出來(lái)。當(dāng)然我們需要在代碼和設(shè)計(jì)里去規(guī)避innodb本身的一些的問(wèn)題，例如盡可能使用到索引，緩存表的行數(shù)等。

?

?

MySQL存儲(chǔ)引擎差異化實(shí)驗(yàn)