文章目錄
- 概念
- innodb引擎的4大特性
- 索引結構
- InnoDB
- MyISAM
- 區別
- 表級鎖和行級鎖
概念
MyISAM 是 MySQL 的默認數據庫引擎（5.5版之前），但因為不支持事務處理而被 InnoDB 替代。
然而事物都是有兩面性的，InnoDB 支持事務處理也會帶來一些問題：
-
當操作完全兼容
ACID時，InnoDB會自動合并多個連接，但每次有事務產生時，仍至少須寫入硬盤一次，因此對于某些硬盤或磁盤陣列，會造成每秒200次的事務處理上限。 -
若希望達到更高的性能且保持事務的完整性，就必使用磁盤緩存與電池備援。當然
InnoDB也提供數種對性能沖擊較低的模式，但相對的也會降低事務的完整性。
innodb引擎的4大特性
插入緩沖(insert buffer)：
為解決非聚集索引的寫性能問題（插入或更新）而生。
對非聚集索引的插入或更新操作，不是每一次都直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩沖池中：
- 若在則直接插入；
- 不在則先放入到一個
Insert Buffer對象中。
此時看似數據庫這個非聚集索引已經插入到了葉子節點，然而實際上只是存放在另一個位置。然后再以一定的頻率進行 Insert Buffer 與輔助索引葉子節點的 merge 操作，此時通常能將多個插入合并到一個操作中，這就大大提高了非聚集索引插入的性能。
二次寫(double write)：
數據庫宕機可能會引起部分寫失效（partial page write），解決方法有兩種：
- 數據庫宕機，物理文件完好無損，是可以通過
redo log進行崩潰恢復（ACID中的持久性）。 - 數據庫宕機，物理文件由于宕機發生損壞，這時就無法通過
redo log進行數據恢復了, 而只能通過double write解決問題。
二次寫分為三步：
- 對緩沖池臟頁進行刷新時，先將臟頁復制到內存的
double write buffer； - 第一次寫： 將
double write buffer分批次寫入到共享表空間中，因為共享表空間是連續存儲所以很快。 - 第二次寫： 將
double write buffer中數據寫入到各個表空間文件中，而此時的寫入則是離散的，因為各個表空間可能在磁盤的不同位置，因此寫入速度較慢。
PS：參數 skip_innodb_doublewrite 可以禁用 double write 功能，但不推薦這樣做。對于需要提供數據高可靠性的主服務器，任何時候都應該確保開啟 double write 功能。
自適應哈希索引(ahi)：
InnoDB存儲引擎 會監控對表上各個索引頁的查詢，如果它觀察到建立哈希索引可以帶來速度提升，則會自行建立哈希索引，這也就是 自適應哈希索引。 即會自動根據 訪問頻率和模式 來為 熱點數據 建立哈希索引。
哈希索引是數據庫自身自動創建并使用的，人工無法對其進行干預。
預讀(read ahead)：
為了提高磁盤操作性能，當前的數據庫系統都采用 異步IO 的方式來處理磁盤操作，InnoDB 也是如此。
同步阻塞： 每進行一次IO操作，需要等待此次操作結束才能繼續接下來的操作。
但是如果一條 SQL語句 需要掃描多個索引頁，也就是需要進行多次 IO操作(掃描一個頁就是一次IO請求) 。同步阻塞會掃描一個頁并等待其完成再進行下一個頁掃描，也就是等待一個IO請求完成后再發送下一個IO請求，效率很低。
異步非阻塞（AIO）： 用戶可以在發出一個 IO請求 后不必等待其完成，而是可以立即發出下一個 IO請求 。
索引結構
我們在MySQL 索引 ：哈希索引、B+樹索引、全文索引中介紹過聚集索引和非聚集索引，現在結合兩個存儲引擎深入研究一下。
兩者都使用 B+樹 作為索引結構，但實現方式卻截然不同：
- 主鍵索引：
InnoDB的數據文件本身就是索引文件；MyISAM主鍵索引的葉節點存的是數據地址。 - 輔助索引：
InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址；MyISAM輔助索引的葉節點存的還是數據地址。
InnoDB
InnoDB 是聚集索引：
- 數據文件和主鍵索引綁在一起（表數據文件本身就是按
B+Tree組織的一個索引結構），必須要有主鍵，通過主鍵索引效率很高。主鍵索引的葉節點保存了完整的數據記錄。 - 但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數據。主鍵不應該過大，因為主鍵太大，其他索引也都會很大。輔助索引的葉子節點是主鍵的值。
圖源博客
MyISAM
MyISAM 是 非聚集索引：
- 索引和數據文件是分離的，主鍵索引和輔助索引的葉子節點都是數據文件的地址指針。主鍵索引要求每個葉子節點的內容是唯一的，而輔助索引每個葉子節點的內容可以重復。
- 主鍵索引和輔助索引是獨立的。因此不同于
InnoDB需要先在輔助索引中查找到主鍵，再通過主鍵在主鍵索引中查找到具對應數據。MyISAM可通過輔助索引快速找到所有的數據，而不需要再遍歷一邊主鍵索引，所以適用于OLAP。
區別
| myisam | innodb | |
|---|---|---|
| 索引類型 | 支持 B-tree、FullTex、R-tree 索引類型 | 支持 hash、B-tree 索引類型，可以通過使用插件 Sphinx 從 InnoDB 中獲得全文索引，會慢一點。 |
| 索引 | Myisam 可以不用主鍵 | InnoDB 表必須有唯一索引（如主鍵）（用戶沒有指定的話會自己找/生產一個隱藏列 Row_id 來充當默認主鍵） |
| 存儲結構 | 每張表存放三個文件：frm：格式定義； MYD(MYData)：數據文件； MYI(MYIndex)：索引文件。 | Innodb 存儲文件有 frm：格式定義；ibd ：數據文件。表的大小只受限于操作系統文件的大小，一般為 2GB 。 |
| 存儲空間 | MyISAM 提供 壓縮 與 簡潔行（terse row formats），存儲空間較小。 | InnoDB 的表需要更多的內存和存儲，在主內存中建立專用的緩沖池用于高速緩沖數據和索引，對硬盤和高速緩存的使用量較大。 |
| 讀寫緩存 | MyISAM 沒有緩存管理機制，必須依靠操作系統來管理讀取與寫入的緩存。 | InnoDB 有讀寫緩存管理機制，不會將被修改的數據頁立即交給操作系統。通常 InnoDB 的數據訪問會比 MyISAM 更有效率。 |
| 可移植性、備份 | 由于 MyISAM 的數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。 | 免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十G的時候就相對痛苦了 |
| 恢復 | MyISAM 遇到錯誤，必須完整掃描全表后才能重建索引，或修正未寫入硬盤的錯誤。修復時間與數據量的多少成正比。 | InnoDB 可借由回滾機制來恢復程序崩潰或非預期結束所造成的數據錯誤 ，修復時間基本都是固定的。 |
| 事務 | 不支持，每次查詢具有原子性。 | 支持，具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全型表 |
| AUTO_INCREMENT | MyISAM 表可以和其他字段一起建立聯合索引 | InnoDB 中必須包含只有該字段的索引 |
| 鎖 | 只支持表級鎖，select、update、delete、insert 語句都會給表自動加鎖。 | 支持表鎖、（默認）行鎖，行鎖大幅度提高了多用戶并發操作的新能。但是 InnoDB 的行鎖，只是在 WHERE 的主鍵是有效的，非主鍵的 WHERE 都會鎖全表的。 |
| 外鍵 | 不支持 | 支持，對一個包含外鍵的 InnoDB表 轉為 MYISAM 會失敗。 |
| 快速讀取行數 | 用一個變量保存了整個表的行數，執行 select count(*) from table 語句時只需要讀出該變量即可，速度很快（注意不能加有任何 WHERE 條件）。 | 保存表的具體行數，執行 select count(*) from table 時需要全表掃描。 |
表級鎖和行級鎖
表級鎖
- 優點：開銷小，加鎖快；
- 缺點：粒度大，發生沖突概率高，高容納并發能力低，適合查詢為主的業務。
行級鎖
- 優點：粒度小，發生鎖沖突的概率小，適用于高并發的頻繁表修改，因此
InnoDB高并發性能優于MyISAM。 - 缺點：加鎖慢，系統消耗較大。索引不僅緩存自身，也緩存數據，因此
InnoDB相比MyISAM需要更大的內存。
