本篇把MySQL最常用的存儲引擎給大家做一個介紹，然后通過插入、修改和并發實驗來了解和驗證一下它們之間的一些差異。

?

一、MySQL存儲引擎簡介

存儲引擎在MySQL結構里占據核心的位置，是上層抽象接口和存儲的物理文件之間的橋梁。每一種storage engine 架構組件都是根據特定的場合來設計的，負責數據的 I/O 操作，并啟用一些特性的支持。

MySQL存儲引擎被設計為插件式結構，每種存儲引擎可從運行的mysql里動態加載或卸載。我們可以在客戶端連接后用show plugins;查看當前加載的插件，用install plugin xxx;或者 uninstall plugin xxx;來安裝或卸載。

?

查看服務器當前支持的引擎命令：

mysql> show engines;

?

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

InnoDB：支持事務操作，支持行級鎖，支持外鍵。獨立表結構的花每個表單文件存儲，是MySQL5.5之后的默認引擎。

MyISAM：使用廣泛，數據量不是特別大時性能很高，是5.5之前的默認引擎。

Memory：數據直接放在內存，極快的訪問速度，但是空間很受限。

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

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

CSV：導入導出成CSV格式，便于和其他軟件數據交換。

?

我們可以配置php.ini文件或者在server啟動時，可以通過--default-storage-engine參數來指定默認的存儲引擎。也可以在mysql運行狀態下動態改變默認引擎：

show variables like 'default_storage_engine';

SET default_storage_engine=MYISAM;

?

數據庫的每個表可以使用不同的引擎：

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

也可以動態修改表的引擎：

alter table t_a engine=MyISAM;

?

二、MySQL存儲引擎的文件組成與備份

MySQL主要的動態文件有日志文件、配置文件和存儲引擎的數據文件

1、日志文件

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

show variables like '%log%';

2、配置和連接文件

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

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

3、數據文件

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

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

innodb的共享表空間存在ibdata文件里，如果配置成獨享表空間的話（mysql默認）每個表還會有對應.ibb文件。我們可以通過變量查詢和設置這些配置：

show variables like ‘%innodb%’;? 其中innodb_file_per_table設置是否是獨享表空間，innodb_data_file_path 和innodb_data_home_dir用來指定表的存放位置。

?

備份：

1、邏輯備份

邏輯備份是不停機的情況下比較好的備份方式，通過mysqldump或者其他方式來導出sql語句。

2、物理備份

物理備份在某些情況是更加直接和快速的方式。myisam引擎因為是非事務沒有獨立日志，一般備份3個文件即可，也可以通過mysqlhotcopy來進行物理備份。

innodb 因為事物需要有日志文件，如果在運行狀態則不能手工來備份，需要一些商業化的工具比如ibbackup來支持物理備份。

3、主從物理備份

因為物理備份一般需要鎖庫，在線上數據庫上我們如果設置了主從服務器并且有多臺從庫的話，可以暫停一臺從庫，然后實行物理備份。

?

三、插入和更新數據

我們先創建3個引擎的數據表user_myisam、user_innodb、user_memory，表的結構是一樣的：

      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;



 


    

我們在生成數據時，可以使用一條條數據插入、導入sql文件、或者批量插入的方式進行。

導入sql文件是有大小限制的，我們可以通過max_allowed_packet變量來查看，一般默認為1M，所以導入大量數據時需要增大這個變量：

show variables like 'max_allowed_packet';

顯然，數據量很大時，批量插入的方式是效率最高的:

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

經過對比，雖然memory引擎插入和查詢修改的速度都極快，單只支持幾萬行數據，即使調大了內存參數也只能支持10多萬行。所以memory一般用在一些數據量比較小的特殊場合，比如在線用戶表、或者緩存一些配置信息等。

我們用批量插入的方式把myisam和innodb的表各插入了1千萬行數據（每次插入1萬行或更多），myisam的速度要稍快些，沒有調優的情況下幾分鐘時間就可以了。

?

更新和查詢的數據對比：

在一個進程操作的情況下，myisam的更新和查詢速度都會稍快于innodb。

特別注意的一點是，innodb查詢表的行數需要全表掃描，速度會非常慢，查詢1千萬行數據的表最多時要6、7s，所以在項目里一定要控制innodb表的總數查詢，一定要緩存。而myisam因為保存了總行數是極快的。

?

四、innodb的事務支持和鎖

innodb的事物支持4種隔離級別：

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

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

repeatable read：可重復讀，事務做了隔離，但還是可以并發的。

serializable：串行，最嚴格的方式，事務單行處理，不會并行。

查看當前和全局的事務隔離級別：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

可以通過以下命令來改變設置：

set global transaction isolation level read uncommitted;

我們可以通過2個session然后設置set autocommit=0來進行測試和驗證這4種事務隔離級別的差別，在自己的項目里也可以根據情況來改變。越高的隔離級別對性能影響越大，innodb默認是repeatable read方式。

?

mysql有3種鎖：

1、表級鎖：myisam的默認形式，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低?？梢圆榭幢礞i的一些情況：

show status like 'table%';
2、行級鎖：innodb的默認形式，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。

需要注意的是，innodb只有在能利用索引的操作時才執行行級鎖，如果查詢或更新操作不能利用索引還是會使用表級鎖的。查看行鎖狀態：

show status like 'innodb_row_lock%';
3、頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

?

五、并發測試與參數調優總結

雖然在上面單進程的情況下，myisam在插入查詢和更新等操作中性能都比較高，但是在我們模擬高并發的情況下，可以看出innodb的銷量明顯占優了。

我們用apache的ab工具來測試3000次30個并發的請求，每個請求在1千萬數據里隨機找5行數據進行修改和查詢（用到索引），測試結果如下：

myisam的測試數據：

innodb的測試結果：

myisam的一些參數優化：

read_buffer_size緩存大小

設置concurrent_insert為2，在尾部插入數據，不影響select

打開delay_key_write

?

innodb的一些參數：

設置事務提交后數據保存方式：

innodb_flush_log_at_trx_commit

0 每秒保存 1 每事務保存 2 系統決定

?

innodb_buffer_pool緩存大?。?

show status like 'innodb_buffer_pool%';

?

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

innodb_read_io_threads讀寫進程數

innodb_write_io_threads

innodb_io_capacity合并寫入數量

innodb_io_capacity=5000;

set global innodb_stats_on_metadata=0;關閉元數據更新

?

?

經過我們的一些操作對比，可以看出：

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

MyISAM適合數據量不是特別大并發不太高的大部分場合，性能都占優，并且也支持全文檢索。如果不需要事務支持的話MyISAM絕對是最優的方式。

而InnoDB 則更適合與大并發大數據量的場合，除了支持事務，在高并發時行級鎖的優勢就會發揮出來。當然我們需要在代碼和設計里去規避innodb本身的一些的問題，例如盡可能使用到索引，緩存表的行數等。

?

?

MySQL存儲引擎差異化實驗