MySQL數據庫引擎和索引

一、MySQL 數據庫引擎：

1. Innodb引擎：
Innodb引擎提供了對數據庫ACID事務的支持，并且實現了SQL標準的四種隔離級別。

在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改，哪些是在事務內和事務間可見的，哪些是不可見的。較低級別的隔離通常可以執行更高的并發，系統的開銷也更低。

四種隔離級別：
未提交讀（Read uncommitted）：在未提交讀級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。事務可以讀取未提交的數據，這也被稱為臟讀（Dirty Read）。
這個級別會導致很多問題，從性能上來說，未提交讀不會比其他的級別好太多，但是缺乏其他級別的很多好處，在實際應用中一般很少使用。
提交讀（Read committed）：大多數數據庫系統的默認隔離級別都是提交讀（但Mysql不是）。提交讀滿足前面提到的隔離性的簡單定義：一個事務開始時，只能“看見”已經提交的事務所做的修改。
換句話說，一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的。這個級別有時候也叫做不可重復讀（nonrepeatable read），因為兩次執行同樣的查詢，可能會得到不一樣的結果。
可重復讀（Repeatable read）：可重復讀解決了臟讀的問題。該級別保證了在同一個事務中多次讀取同樣記錄的結果是一致的。但是理論上，可重復讀隔離級別還是無法解決另外一個幻讀（Phantom read）問題。
所謂幻讀，指的是當某個事務在讀取某個范圍內的記錄時，另外一個事務中又在該范圍插入了新的記錄，當之前的事務再次讀取該范圍的記錄時，會產生幻行（Phantom row）。可重復讀是MySQL的默認事務隔離級別。
可串行化（Serializable）：可串行化是最高的隔離級別。它通過強制事務串行執行，避免了前面所說的幻讀問題。簡單來說，可串行化會在讀取的每一行數據上都加上鎖，所以可能導致大量的超時和鎖爭用問題。
實際應用中也很少用到這個隔離級別，只有在非常需要確保數據的一致性而且可以接受沒有并發的情況下，才考慮用該級別。

該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量數據庫系統，它本身其實就是基于MySQL后臺的完整數據庫系統，MySQL運行時Innodb會在內存中建立緩沖池，用于緩沖數據和索引。
但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數，當SELECT COUNT(*) FROM TABLE時需要掃描全表。
當需要使用數據庫事務時，該引擎當然是首選。
由于鎖的粒度更小，寫操作不會鎖定全表，所以在并發較高時，使用Innodb引擎會提升效率。
但是使用行級鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。

2. MyIASM引擎：

MyIASM是MySQL默認的引擎，但是它沒有提供對數據庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數據時即寫操作需要鎖定整個表，效率便會低一些。
不過和Innodb不同，MyIASM中存儲了表的行數，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。
如果表的讀操作遠遠多于寫操作且不需要數據庫事務的支持，那么MyIASM也是很好的選擇。

3.兩種引擎的選擇：

大尺寸的數據集趨向于選擇InnoDB引擎，因為它支持事務處理和故障恢復。
數據庫的大小決定了故障恢復的時間長短，InnoDB可以利用事務日志進行數據恢復，這會比較快。主鍵查詢在InnoDB引擎下也會相當快，不過需要注意的是如果主鍵太長也會導致性能問題。
大批的INSERT語句(在每個INSERT語句中寫入多行，批量插入)在MyISAM下會快一些，但是UPDATE語句在InnoDB下則會更快一些，尤其是在并發量大的時候。