1、MyISAM：默認表類型，它是基于傳統的ISAM類型，ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫，它是存儲記錄和文件的標準方法。不是事務安全的，而且不支持外鍵，如果執行大量的select，insert MyISAM比較適合。
2、InnoDB：支持事務安全的引擎，支持外鍵、行鎖、事務是他的最大特點。如果有大量的update和insert，建議使用InnoDB，特別是針對多個并發和QPS較高的情況。
?
一、表鎖差異
MyISAM:
myisam只支持表級鎖，用戶在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發的情況下，可以在表的尾部插入新的數據。也可以通過lock table命令來鎖表，這樣操作主要是可以模仿事務，但是消耗非常大，一般只在實驗演示中使用。
InnoDB ：
Innodb支持事務和行級鎖，是innodb的最大特色。
事務的ACID屬性：atomicity,consistent,isolation,durable。
并發事務帶來的幾個問題：更新丟失，臟讀，不可重復讀，幻讀。
事務隔離級別：未提交讀(Read uncommitted)，已提交讀(Read committed)，可重復讀(Repeatable read)，可序列化(Serializable)
四種隔離級別的比較
| 讀數據一致性及并發副作用 ? 隔離級別 | 讀數據一致性 | 臟讀 | 不可重復讀 | 幻讀 |
| 為提交讀(read uncommitted) | 最低級別，不讀物理上順壞的數據 | 是 | 是 | 是 |
| 已提交讀(read committed) | 語句級 | 否 | 是 | 是 |
| 可重復讀(Repeatable red) | 事務級 | 否 | 否 | 是 |
| 可序列化(Serializable) | 最高級別，事務級 | 否 | 否 | 否 |
?
查看mysql的默認事務隔離級別“show global variables like ‘tx_isolation’; ”
Innodb的行鎖模式有以下幾種：共享鎖，排他鎖，意向共享鎖(表鎖)，意向排他鎖(表鎖)，間隙鎖。
注意：當語句沒有使用索引，innodb不能確定操作的行，這個時候就使用的意向鎖，也就是表鎖
關于死鎖：
什么是死鎖？當兩個事務都需要獲得對方持有的排他鎖才能完成事務，這樣就導致了循環鎖等待，也就是常見的死鎖類型。
解決死鎖的方法：
1、? 數據庫參數
2、? 應用中盡量約定程序讀取表的順序一樣
3、? 應用中處理一個表時，盡量對處理的順序排序
4、? 調整事務隔離級別（避免兩個事務同時操作一行不存在的數據，容易發生死鎖）
?
二、數據庫文件差異
MyISAM ：
myisam屬于堆表
myisam在磁盤存儲上有三個文件，每個文件名以表名開頭，擴展名指出文件類型。
.frm 用于存儲表的定義
.MYD 用于存放數據
.MYI 用于存放表索引
myisam表還支持三種不同的存儲格式：
靜態表(默認，但是注意數據末尾不能有空格，會被去掉)
動態表
壓縮表
InnoDB ：
innodb屬于索引組織表
innodb有兩種存儲方式，共享表空間存儲和多表空間存儲
兩種存儲方式的表結構和myisam一樣，以表名開頭，擴展名是.frm。
如果使用共享表空間，那么所有表的數據文件和索引文件都保存在一個表空間里，一個表空間可以有多個文件，通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字，一般共享表空間的名字叫ibdata1-n。
如果使用多表空間，那么每個表都有一個表空間文件用于存儲每個表的數據和索引，文件名以表名開頭，以.ibd為擴展名。
?
三、索引差異
1、關于自動增長
myisam引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序后遞增。
innodb引擎的自動增長咧必須是索引，如果是組合索引也必須是組合索引的第一列。
2、關于主鍵
myisam允許沒有任何索引和主鍵的表存在，
myisam的索引都是保存行的地址。
innodb引擎如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節的主鍵(用戶不可見)
innodb的數據是主索引的一部分，附加索引保存的是主索引的值。
3、關于count()函數
myisam保存有表的總行數，如果select count(*) from table;會直接取出出該值
innodb沒有保存表的總行數，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了wehre?????? 條件后，myisam和innodb處理的方式都一樣。
4、全文索引
myisam支持 FULLTEXT類型的全文索引
innodb不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx?? 是一個開源軟件，提供多種語言的API接口，可以優化mysql的各種查詢）
5、delete from table
使用這條命令時，innodb不會從新建立表，而是一條一條的刪除數據，在innodb上如果要清空保存有大量數據的表，最?????? 好不要使用這個命令。(推薦使用truncate table，不過需要用戶有drop此表的權限)
6、索引保存位置
myisam的索引以表名+.MYI文件分別保存。
innodb的索引和數據一起保存在表空間里。
?
四、開發的注意事項
1、可以用 show create table tablename 命令看表的引擎類型。
2、對不支持事務的表做start/commit操作沒有任何效果，在執行commit前已經提交。
3、可以執行以下命令來切換非事務表到事務（數據不會丟失），innodb表比myisam表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;
4、默認innodb是開啟自動提交的，如果你按照myisam的使用方法來編寫代碼頁不會存在錯誤，只是性能會很低。如何在編寫代碼時候提高數據庫性能呢？
a、盡量將多個語句綁到一個事務中，進行提交，避免多次提交導致的數據庫開銷。
b、在一個事務獲得排他鎖或者意向排他鎖以后，如果后面還有需要處理的sql語句，在這兩條或者多條sql語句之間程序應盡量少的進行邏輯運算和處理，減少鎖的時間。
c、盡量避免死鎖
d、sql語句如果有where子句一定要使用索引，盡量避免獲取意向排他鎖。
f、針對我們自己的數據庫環境，日志系統是直插入，不修改的，所以我們使用混合引擎方式，ZION_LOG_DB照舊使用myisam存儲引擎，只有ZION_GAME_DB，ZION_LOGIN_DB，DAUM_BILLING使用Innodb引擎。
?
五、究竟該怎么選擇
下面先讓我們回答一些問題：??
◆你的數據庫有外鍵嗎？??
◆你需要事務支持嗎？??
◆你需要全文索引嗎？??
◆你經常使用什么樣的查詢模式？??
◆你的數據有多大？??
?
myisam只有索引緩存??
innodb不分索引文件數據文件 innodb buffer??
myisam只能管理索引，在索引數據大于分配的資源時，會由操作系統來cache；數據文件依賴于操作系統的cache。innodb不管是索引還是數據，都是自己來管理??
?
思考上面這些問題可以讓你找到合適的方向，但那并不是絕對的。如果你需要事務處理或是外鍵，那么InnoDB 可能是比較好的方式。如果你需要全文索引，那么通常來說 MyISAM是好的選擇，因為這是系統內建的，然而，我們其實并不會經常地去測試兩百萬行記錄。所以，就算是慢一點，我們可以通過使用Sphinx從InnoDB中獲得全文索引。??
?
數據的大小，是一個影響你選擇什么樣存儲引擎的重要因素，大尺寸的數據集趨向于選擇InnoDB方式，因為其支持事務處理和故障恢復。數據庫的在小決定了故障恢復的時間長短，InnoDB可以利用事務日志進行數據恢復，這會比較快。而MyISAM可能會需要幾個小時甚至幾天來干這些事，InnoDB只需要幾分鐘。??
?
操作數據庫表的習慣可能也會是一個對性能影響很大的因素。比如： COUNT() 在 MyISAM 表中會非常快，而在InnoDB 表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當的快，但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts 語句在 MyISAM下會快一些，但是updates 在InnoDB下會更快一些——尤其在并發量大的時候。??
?
所以，到底你檢使用哪一個呢？根據經驗來看，如果是一些小型的應用或項目，那么MyISAM 也許會更適合。當然，在大型的環境下使用 MyISAM 也會有很大成功的時候，但卻不總是這樣的。如果你正在計劃使用一個超大數據量的項目，而且需要事務處理或外鍵支持，那么你真的應該直接使用 InnoDB方式。但需要記住InnoDB 的表需要更多的內存和存儲，轉換100GB 的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。??
?
對于支持事務的InnoDB類型的表，影響速度的主要原因是AUTOCOMMIT默認設置是打開的，而且程序沒有顯式調用BEGIN 開始事務，導致每插入一條都自動Commit，嚴重影響了速度。可以在執行sql前調用begin，多條sql形成一個事務（即使autocommit打開也可以），將大大提高性能。??
?
InnoDB
InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力 (crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。 InnoDB 提供了行鎖(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non- locking read in SELECTs)。這些特性均提高了多用戶并發操作的性能表現。在InnoDB表中不需要擴大鎖定 (lock escalation)，因為 InnoDB 的列鎖定(row level locks)適宜非常小的空間。 InnoDB 是 MySQL 上第一個提供外鍵約束(FOREIGN KEY constraints)的表引擎。??
InnoDB 的設計目標是處理大容量數據庫系統，它的 CPU 利用率是其它基于磁盤的關系數據庫引擎所不能比的。在技術上，InnoDB 是一套放在 MySQL 后臺的完整數據庫系統，InnoDB 在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。 InnoDB 把數據和索引存放在表空間里，可能包含多個文件，這與其它的不一樣，舉例來說，在 MyISAM 中，表被存放在單獨的文件中。InnoDB 表的大小只受限于操作系統的文件大小，一般為 2 GB。??
InnoDB所有的表都保存在同一個數據文件 ibdata1 中（也可能是多個文件，或者是獨立的表空間文件）,相對來說比較不好備份，免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump。??
MyISAM??
MyISAM 是MySQL缺省存貯引擎 .??
每張MyISAM 表被存放在三個文件 。frm 文件存放表格定義。 數據文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。??
因為MyISAM相對簡單所以在效率上要優于InnoDB..小型應用使用MyISAM是不錯的選擇.??
MyISAM表是保存成文件的形式,在跨平臺的數據轉移中使用MyISAM存儲會省去不少的麻煩??
?
以下是一些細節和具體實現的差別：??
?
1.InnoDB不支持FULLTEXT類型的索引。??
2.InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。??
3.對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引。??
4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。??
5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。??
另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如 update table set num=1 where name like “%aaa%”??
任何一種表都不是萬能的，只用恰當的針對業務類型來選擇合適的表類型，才能最大的發揮MySQL的性能優勢。??
?
六、重復地總結一遍
1、MyISAM不支持事務，InnoDB是事務類型的存儲引擎，當我們的表需要用到事務支持的時候，那肯定是不能選擇MyISAM了。
2、MyISAM只支持表級鎖，BDB支持頁級鎖和表級鎖默認為頁級鎖，而InnoDB支持行級鎖和表級鎖默認為行級鎖
?
表級鎖：直接鎖定整張表，在鎖定期間，其他進程無法對該表進行寫操作，如果設置的是寫鎖，那么其他進程讀也不允許
?
MyISAM是表級鎖定的存儲引擎，它不會出現死鎖問題
?
對于write，表鎖定原理如下：
?
如果表上沒有鎖，在其上面放置一個寫鎖，否則，把鎖定請求放在寫鎖隊列中。
?
對于read，表鎖定原理如下 ：
?
如果表上沒有寫鎖定，那么把一個讀鎖放在其上面，否則把鎖請求放在讀鎖定隊列中
?
當一個鎖定被釋放時，表可被寫鎖定隊列中的線程得到，然后才是讀鎖定隊列中的線程。這意味著，如果你在一個表上有許多更新，那么你的SELECT語句將等到所有的寫鎖定線程執行完。
行級鎖：只對指定的行進行鎖定，其他進程還是可以對表中的其他行進行操作的。
?
行級鎖是Mysql粒度最小的一種鎖，它能大大的減少數據庫操作的沖突，但是粒度越小實現成本也越大。
?
行級鎖可能會導致“死鎖”，那到底是怎么導致的呢，分析原因：Mysql行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，那么Mysql就會鎖定這個主鍵索引，如果sql語句操作的是非主鍵索引，那么Mysql會先鎖定這個非主鍵索引，再去鎖定主鍵索引。
?
在UPDATE 和 DELETE操作時Mysql不僅會鎖定所有WHERE 條件掃描過得索引，還會鎖定相鄰的鍵值。
?
“死鎖”舉例分析：
?
表Test：（ID,STATE,TIME）? 主鍵索引：ID? 非主鍵索引：STATE
?
當執行"UPDATE? STATE =1011 WHERE STATE=1000"? 語句的時候會鎖定STATE索引，由于STATE 是非主鍵索引,所以Mysql還會去請求鎖定ID索引
?
當另一個SQL語句與語句1幾乎同時執行時：“UPDATE STATE=1010 WHERE ID=1”? 對于語句2 Mysql會先鎖定ID索引，由于語句2操作了STATE字段，所以Mysql還會請求鎖定STATE索引。這時。彼此鎖定著對方需要的索引，又都在等待對方釋放鎖定。所以出現了"死鎖"的情況。
?
行級鎖的優點：
?
有許多線程訪問不同的行時，只存在少量的沖突。
?
回滾時只有少量的更改
?
可以長時間鎖定單一的行
?
行級鎖缺點：
?
相對于頁級鎖和表級鎖來說占用了更多的內存
?
當表的大部分行在使用時，比頁級鎖和表級鎖慢，因為你必須獲得更多的鎖
?
當在大部分數據上經常使用GROUP BY操作，肯定會比表級鎖和頁級鎖慢。
?
頁級鎖：表級鎖速度快，但是沖突多；行級鎖速度慢，但沖突少；頁級鎖就是他倆折中的，一次鎖定相鄰的一組記錄。
3、MyISAM引擎不支持外鍵，InnoDB支持外鍵
4、MyISAM引擎的表在大量高并發的讀寫下會經常出現表損壞的情況
?
我們以前做的項目就遇到這個問題，表的INSERT 和 UPDATE操作很頻繁，原來用的MyISAM引擎，導致表隔三差五就損壞，后來更換成了InnoDB引擎。
?
其他容易導致表損壞原因：
?
服務器突然斷電導致數據文件損壞，強制關機（mysqld未關閉情況下）導致表損壞
?
mysqld進程在寫入操作的時候被殺掉
?
磁盤故障
?
表損壞常見癥狀：
?
查詢表不能返回數據或返回部分數據
?
打開表失敗： Can’t open file: ‘×××.MYI’ (errno: 145) 。
?
Error: Table 'p' is marked as crashed and should be repaired 。
Incorrect key file for table: '...'. Try to repair it
?
Mysql表的恢復：
對于MyISAM表的恢復：
可以使用Mysql自帶的myisamchk工具： myisamchk -r tablename? 或者 myisamchk -o tablename（比前面的更保險） 對表進行修復
5、對于count()查詢來說MyISAM更有優勢
因為MyISAM存儲了表中的行數記錄，執行SELECT COUNT() 的時候可以直接獲取到結果，而InnoDB需要掃描全部數據后得到結果。
但是注意一點：對于帶有WHERE 條件的 SELECT COUNT()語句兩種引擎的表執行過程是一樣的，都需要掃描全部數據后得到結果
6、 InnoDB是為處理巨大數據量時的最大性能設計，它的CPU效率可能是任何其它基于磁盤的關系數據庫引擎所不能匹敵的。
7、MyISAM支持全文索引（FULLTEXT），InnoDB不支持
8、MyISAM引擎的表的查詢、更新、插入的效率要比InnoDB高
網上截取了前輩們測試結論：
測試方法：連續提交10個query， 表記錄總數：38萬 ， 時間單位 s
?
? ? ? ? 引擎類型? ? ? ? ? ? ? ? ? ? MyISAM? ? ? ? ? ? ? ? InnoDB? ? ? ? ? ? ? 性能相差
?
? ? ? ? count? ? ? ? ? ? ? ? ? ? ? 0.0008357? ? ? ? ? ? 3.0163? ? ? ? ? ? ? ? 3609
?
? ? ? ? 查詢主鍵? ? ? ? ? ? ? ? ? 0.005708? ? ? ? ? ? ? 0.1574? ? ? ? ? ? ? ? 27.57
?
? ? ? ? 查詢非主鍵? ? ? ? ? ? ? ? ? 24.01? ? ? ? ? ? ? ? ? 80.37? ? ? ? ? ? ? ? 3.348
?
? ? ? ? 更新主鍵? ? ? ? ? ? ? ? ? 0.008124? ? ? ? ? ? 0.8183? ? ? ? ? ? ? ? 100.7
?
? ? ? ? 更新非主鍵? ? ? ? ? ? ? ? 0.004141? ? ? ? ? ? 0.02625? ? ? ? ? ? ? 6.338
?
? ? ? ? 插入? ? ? ? ? ? ? ? ? ? ? ? 0.004188? ? ? ? ? ? 0.3694? ? ? ? ? ? ? ? 88.21
?
? ? （1）加了索引以后，對于MyISAM查詢可以加快：4 206.09733倍，對InnoDB查詢加快510.72921倍，同時對MyISAM更新速度減慢為原來的1/2，InnoDB的更
? 新速度減慢為原來的1/30。要看情況決定是否要加索引，比如不查詢的log表，不要做任何的索引。
?
? ? （2）如果你的數據量是百萬級別的，并且沒有任何的事務處理，那么用MyISAM是性能最好的選擇。
?
? ? （3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盤空間。
?
? ? ? ? 在我們測試的這個38w的表中，表占用空間的情況如下：
? ? ? ? ? ? 引擎類型? ? ? ? ? ? ? ? ? ? MyISAM? ? ? ? ? ? ? InnoDB
? ? ? ? ? ? 數據? ? ? ? ? ? ? ? ? ? ? 53,924 KB? ? ? ? ? 58,976 KB
? ? ? ? ? ? 索引? ? ? ? ? ? ? ? ? ? ? 13,640 KB? ? ? ? ? 21,072 KB
? ? ? ? ? ? 占用總空間? ? ? ? ? ? ? 67,564 KB? ? ? ? ? 80,048 KB
?
? ? ? ? 另外一個176W萬記錄的表， 表占用空間的情況如下：
?
? ? ? ? ? ? 引擎類型? ? ? ? ? ? ? ? MyIsam? ? ? ? ? ? ? InnorDB
? ? ? ? ? ? 數據? ? ? ? ? ? ? ? ? 56,166 KB? ? ? ? ? 90,736 KB
? ? ? ? ? ? 索引? ? ? ? ? ? ? ? ? 67,103 KB? ? ? ? ? 88,848 KB
? ? ? ? ? ? 占用總空間? ? ? ? 123,269 KB? ? ? ? 179,584 KB
?
七、性能對比
?
測試的版本是mysql ?Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8（官方稱比built-in版本性能更好）和默認的MyISAM。
測試機器是筆記本，配置如下：Intel 酷睿2雙核 P8600，2G*2 DDR3 1066內存，320G硬盤5400轉。
測試一：數據插入性能測試，這里我分別對innodb_flush_log_at_trx_commit參數打開和關閉都測了了一下，每次測試都是運行40s，表中數字都是實際插入條數。
? ? ? ? ? ? ? ? ? ? ? ?MyISAM ? ? ? ? ? ? ? ??Innodb (打開) ? ? ?Innodb (關閉)
單線程，逐個插入 ? ? ? ? 120000 ? ? ? ? ? ? ? ??60000 ? ? ? ? ? ? ?60000
4線程，逐個插入 ? ? ? ? ?40000*4 ? ? ? ? ? ? ? ?20000*4 ? ? ? ? ? ?40000*4
單線程，批量100條/次插入 ?3600*100 ? ? ? ? ? ? ??800*100 ? ? ? ? ? ?3000*100
單線程，批量200條/次插入 ?1800*200 ? ? ? ? ? ? ??400*200 ? ? ? ? ? ?1600*200
可以發現批量插入的性能遠高于單條插入，但是一次批量的大小對性能影響不大。每條記錄是否都刷新日志的參數對innodb性能的影響巨大。總體上來說，MyISAM性能更優一點。這里有一點需要注意，在插入測試過程中，我對系統資源進行了監控，發現MyISAM對系統資源占用很低，但是Innodb對磁盤占用卻很高，應該是對事務控制多了很多需要記錄的日志。
測試二：數據讀取性能測試。每次隨機讀取1000條記錄，反復進行讀取。
? ? ? ? ? ? ? ? ? ? ? ? MyISAM ? ? ? ?Innodb
單線程，200次讀取 ? ? ? ??5.7s ? ? ? ? ?16.7s
4線程，200次讀取 ? ? ? ? ?12s ? ? ? ? ? 40.8s
可以看出MyISAM的讀取性能非常恐怖，性能差距在3倍的樣子。
以上兩個測試發現MyISAM在無事務的需求下幾乎完勝，但是要知道它是表鎖，Innodb是行鎖，那么在并發讀寫同時存在的情況下，那結果會是怎么樣呢？！
測試三：兩個線程并發寫入，2個線程并發讀取。
? ? ? ? ? ? ? ? ? ? ? ?MyISAM ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??Innodb
逐個插入 ? ? ? ? ? ? ? ?寫入40s：10000*2 讀取200次*2：14s ? ? ? ?寫入40s：60000*2 讀取200次*2：50s
批量100條/次插入 ? ? ? ?寫入40s：1000*100*2 讀取200次*2：10s ? ? ?寫入40s：1500*100*2 讀取200次*2：50s
這下立刻顯示出Innodb在并發情況下強勁的性能，幾乎沒有什么性能衰減。而MyISAM單條插入速度變得非常慢，批量插入也下降了40%性能。
總結一下，在寫多讀少的應用中還是Innodb插入性能更穩定，在并發情況下也能基本，如果是對讀取速度要求比較快的應用還是選MyISAM。
?
)
