Gea-Suan Lin's BLOG

標題所提到的兩個 engine 是在 MySQL 中最常用到的兩個 engine。其中 MyISAM 是在 MySQL 5.1 之前的 default engine，InnoDB 則是 MySQL 5.5 之後的 default engine。

這篇主要是講 MySQL 5.1 + InnoDB Plugin，或是 MySQL 5.5 後的情況。

MyISAM 是 Table-level lock，當有寫入時其他人無法讀取 (有少數例外，像是 bulk insert)。而 InnoDB 設計成 Row-level lock，在寫入時有很大機會還是可以讀取。

另外，InnoDB 支援 ACID transaction，對於學過資料庫理論的人 (像是科班出身) 會覺得比較「親切」，比起 MyISAM 有很多東西可以用。

大多數不換 InnoDB 有幾個原因：

InnoDB 佔用的空間比較大

最常見的原因是認為 InnoDB 相對 MyISAM 所佔用的空間會大不少 (看過三倍大的)：但在 InnoDB 自從推出壓縮功能後就接近很多 (看過只大 20% 的，甚至有可能比 MyISAM 小)。而資料量的問題可以參考 Mobile01 的 55GB (2012 年 10 月 uid=1 的蔣大在 Mobile01 上揭露的數字)。

當網站夠大時，記憶體與用 15KRPM SAS 硬碟加上 RAID1+0 的一次性成本其實不高。

InnoDB 不好備份

第二個常見的原因是認為 MyISAM 備份比較容易，直接 copy 就可以備份：這種備份方式其實有很高的風險造成 inconsistent backup (「備份的資料損毀」)。以交易的例子來說，有 auction 與 user 兩個表格，按照字母順序先複製了 auction 表格，再複製 user 表格，中間使用者用點數購買了某個物品 (扣 user 表格裡的點數，在 auction 裡建立一筆資料)。這份備份裡就會備份到「user 內的點數扣掉，但 auction 沒有資料」的情況。

比較好的方式是用 InnoDB 提供的 transaction 建立 consistent backup，備份時長時間讀取不會像 MyISAM 無法寫入。另外一種方式是建立 backup 專用的 slave，要備份時可以整台 slave 停掉備份 (還有像是 filesystem snapshot 之類的方式，這邊跳過)。

書上寫 MyISAM 效能比較好...

是的，不過這個前提是「如果你只有讀取」。

MyISAM 讀取效能比較好並不是指 MyISAM query 一次 <1ms 時 InnoDB query 一次就要 100ms 這種程度。兩個都還是在 <1ms，只是當你用到「效率不好的 query」時，MyISAM 在 Table scan 的效能會比 InnoDB 好。

但當網站變大遇到 MyISAM + table scan 時就完蛋了：一個 query 卡 1 秒就代表網站上寫入時遇到這個 query 時最少要卡一秒，InnoDB 反而能救你。(雖然這不是好的方向。好的方向應該是想辦法解決 Table scan 的問題，可能是改 query，也有可能是增加 index 或修改 index)

也因為以上的特性，當使用 InnoDB 時，可以這樣設計：

只對時間敏感 Query 加上 Index

以前用 MyISAM 時為了避免跑報表會造成其他線上服務的 query 卡住，如果用 InnoDB 因為不會卡，報表的 query 沒有 index 而 table scan 也是可以接受的。

有嚴格資料正確性需求的 Query 使用 Transaction

像是金流相關系統，一筆購買紀錄可能要修改好幾個表格。用 MyISAM 時必須對好幾個表格使用 TABLE LOCK (仍然有 atomic 問題)，現在可以用 transaction 解決。

資料庫正規化

有可能會因為 MyISAM 卡 query 的問題而設計出非正規化的 schema。用 InnoDB 可能可以正規化，用適當的設備成本 (像是適當使用 JOIN) 降低人力維護成本。