MYSQL MYISAM引擎與INNODB引引擎的區別

標籤：系統   資料庫   

MYISAM和INNODB是MYSQL資料庫的兩個主要引擎，MYISAM是MYSQL5.5版本之前的預設的引擎，而INNODB是MYSQL 5.5版本預設引擎。

兩個引擎的區別如下:

1. 交易處理
   

   INNODB支援交易處理功能，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。

   原子性(Atomicity):事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。

   一致性（Consisten):在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性; 事務結束時，所有的內部資料結構（如B樹索引或雙向鏈表）也都必須是正確的。

   隔離性（ISOLATION）資料庫系統提供一定的隔離機制，保證事務在不受外部並行作業的影響的“獨立”環境執行。這意味著交易處理過程中的中間狀態對外部是不可見的，反之亦然。

   持久性：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障都保持。

   MYISAM不支援交易處理。

2. SELECT ,UPDATE,INSERT,DELETE操作

   MYISAM:如果大量的SELECT,MYISAM是更好的選擇

   INNODB:如果你的資料執行大量的INSERT或UPDATE,出於效能方面考慮,應該使用INNODB。

3. 鎖機制不同

   INNODB 為行級鎖，MYISAM為表級鎖。

4. 查詢表的行數不同

   MYISAM 記錄表的總行數，所以當 COUNT(*)相對很快。

   INNODB ：不儲存表的具體行數，也就是COUNT(*)時要對錶進行一次全表掃描。

5. MYISAM:每個MYISAM的表在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案類型。

   .frm檔案儲存體表定義。

   資料檔案的副檔名為.MYD (MYData)。

   索引檔案的副檔名是.MYI (MYIndex)

     INNODB ：分為兩種方式，一種為共用資料表空間方式，這種是所有表以及UNDO，索引都放在一個或多個資料表空間檔案裡，檔案大小最大為64TB。第二種為每個表為一個單獨的資料表空間。

6.MYISAM的表格儲存體方式是HOT表，堆表。

   INNODB表的儲存方式是IOT表，（索引組織表）

7.MyISAM 使用B+Tree 作為索引結構，葉子節點的data存放指標，也就是記錄的地址。對於主鍵索引和輔助索引都是一樣的。

InnoDB 也使用B+Tree作為索引結構，也別需要注意的是，對於主鍵索引，InnoDB 使用叢集索引，InnoDB的資料檔案本身就是就是索引檔案。而MyISAM，主鍵索引和資料檔案是分離的。

InnoDB資料檔案，要按主鍵叢集索引，這就要求InnoDB的表必須要有主鍵（MyISAM可以沒有）。如果沒有顯式指定主鍵，InnoDB會自動選擇一個可以唯一標識記錄的欄位作為主鍵，比如auto_increment的欄位，如果不存在這樣的列，InnoDB會自動產生一個隱含欄位作為主鍵，這個隱含欄位6個位元組，是長整形。

對於InnoDB的輔助索引，葉子節點的data存放的是主鍵的值。這就意味著，使用輔助索引定位記錄，需要使用兩次索引：首先使用輔助索引找到主鍵的值，根據主鍵的值，使用主鍵索引找到記錄。

InnoDB的輔助索引為什麼要這樣設計？
如果輔助索引data存放的行指標，當行移動或者資料頁分裂時，需要更新data域行指標的值，這就增加維護成本。data存在主鍵的值，就沒有這個問題。行移動和資料頁分裂，主鍵索引會自動更新。data關聯主鍵的值，不需要更新，相當於增加一個間接層。這個間接層對效能的影響也很小，因為通過主鍵定位記錄是非常快的。

瞭解了innoDB的索引實現，有幾個地方需要注意：
不要使用過長的欄位作為主鍵，因為輔助索引都要使用主鍵索引定位記錄，這個欄位過長，使用記憶體更大，影響效能。
使用單調的欄位作為主鍵，特別是insert的時候，如果是非單調的，B+Tree維護成本很高。

這就能很好解釋，隔離等級 repeatable-read, 不使用索引鎖住整個表，使用索引（主鍵索引或者輔助索引）只會鎖住對應的行。

在查詢執行計畫中，有一個欄位 type， eq_ref 表示使用主鍵索引，直接定位到記錄。而ref 表示先使用輔助索引，找到主鍵的值，再使用主鍵索引定位到記錄。

8.MYISAM不支援外鍵，INNODB支援外鍵

6mmy、my1， MyISAM ：每個MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案類型。

MYSQL MYISAM引擎與INNODB引引擎的區別