InnoDB和MyISAM的差別
| InnoDB | MyISAM |
|---|---|
| 支援事務 | 不支援事務 |
| 支援外鍵 | 不支援外鍵 |
| 支援行鎖 | 不支援行鎖，支援表鎖 |
| 寫操作效率高 | 讀操作效率高 |
| 支援全文索引 | 支援全文索引 |
| 表占用空間較小 | 表占用空間較大 |
| 聚集索引 | 非聚集索引 |
Innodb:通過輔助索引找到主鍵,在通過主鍵索引來找到記錄。
myisam通過索引找到實體位址,再通過實體位址找到對應的記錄。
應用場景：
-
MyISAM 管理非事務表，它提供高速存儲和檢索，以及全文搜尋能力。如果應用中需要執行大量的
SELECT查詢，那麼MyISAM是更好的選擇。
-
InnoDB用于事務處理應用程式，具有衆多特性，包括ACID事務支援。如果應用中需要執行大量的
INSERT或UPDATE操作，則應該使用InnoDB,這樣可以提高多使用者并發操作的性能
MyISAM索引實作
索引由B+樹構成，執行查詢操作的時候會先搜尋B+樹，如果找到對應葉子結點會，根據葉子節點的值（位址），拿出整行資料。
InnoDB索引實作（5.5後預設引擎）
上圖是InnoDB主索引（同時也是資料檔案）的示意圖，可以看到葉節點包含了完整的資料記錄。這種索引叫做聚集索引。因為InnoDB的資料檔案本身要按主鍵聚集，是以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一辨別資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隐含字段作為主鍵，這個字段長度為6個位元組，類型為長整形。（InnoDB如果沒有主鍵，會用第一個非空且唯一的列，會建立隐藏列-6位元組。）
這裡以英文字元的ASCII碼作為比較準則。聚集索引這種實作方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。
innodb buffer pool
- innodb buffer pool一次從磁盤讀一個或幾個page頁，大小為16K
- io_write_thread什麼時候進行與commit無關，當IO壓力大時少寫入，IO壓力小就多寫入
- innodb buffer pool以LRU（lastest recent used）作為其存儲結構：若不做優化，select * 冷端表會把熱端頂掉，再次查詢熱端表時就會出現大量磁盤IO（LRU污染）。
MySQL學習筆記（二）-存儲引擎 熱塊競争解決：
設定 set global innodb buffer pool_size = XXX(一般占整個資料庫的70%)
innodb buffer pool_instance(這個參數會根據COU數量把IBP分為幾塊)
innodb對LRU優化：在LRU中設定midpiont（預設第一次插入LRU清單5/8處，第二次插入頭部）
undo裡的值是修改前的值
undo裡的資料在commit後就沒用了
undo的作用：
- 沒有送出和復原時，保證事務的一緻性，做一緻性讀取
- 當復原時，支援事務的復原
日志先行
commit做了一件事，将log buffer日志永久保留在磁盤中
資料庫當機時，進行redo操作
沒有commit時，直接rollback回去
log buffer原則：當達到二分之一滿，1M，commit時任意一個條件時，就會把資料刷到磁盤中。
關于redo的commit：
Innodb_flush_log_at_trx_commit參數
0 ：每秒将日志緩沖區寫入log file，并同時flush到磁盤。跟事務送出無關。在機器crash并重新開機後，會丢失一秒的事務日志資料（并不一定是1s，也許會有延遲，跟作業系統排程有關）。
1：每次事務送出将日志緩沖區寫入log file，并同時flush到磁盤。（crash不會丢失事務日志）
2：每次事務送出将日志緩沖區寫入log file，每秒flush一次到磁盤。（crash有可能丢失資料）
MRR（multi range read）：而MRR的優化在于，并不是每次通過輔助索引就回表去取記錄，而是将其rowid給緩存起來，然後對rowid進行排序後，再去通路記錄，這樣就能将随機I/O轉化為順序I/O，進而大幅地提升性能。