InnoDBvsMyISAM.txt ---------- 20200114 項目 MyISAM InnoDB ---------------- ------------------------------------------------- ---------------------------------------- 版本 Default before v 5.1 Default after v 5.5 Lock Table-level lock Row-Level Lock ACID transaction 不支援 支援 佔用空間 小 大, 未壓縮前可能差3倍 備份 易. 直接 copy 備份 難. 讀取效能 較好 較差 ---------------- ------------------------------------------------- ---------------------------------------- 構成 每一表存3個檔案: 檔名為table名稱, .frm存定義, 資料檔與日誌檔受限作業系統檔案大小(2GB) .MYD(MYDATA)存資料, .MYI(MYIndex)存索引. ---------------- ------------------------------------------------- ---------------------------------------- MySQL有多種儲存引擎，每種儲存引擎有各自的優缺點，可以擇優選擇使用： MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。 MySQL支援數個儲存引擎作為對不同表的型別的處理器。MySQL儲存引擎包括處理事務安全表的引擎和處理非事務安全表的引擎： · MyISAM管理非事務表。它提供高速儲存和檢索，以及全文搜尋能力。MyISAM在所有MySQL配置裡被支援，它是預設的儲存引擎，除非你配置MySQL預設使用另外一個引擎。 · MEMORY儲存引擎提供“記憶體中”表。MERGE儲存引擎允許集合將被處理同樣的MyISAM表作為一個單獨的表。就像MyISAM一樣，MEMORY和MERGE儲存引擎處理非事務表，這兩個引擎也都被預設包含在MySQL中。 註釋：MEMORY儲存引擎正式地被確定為HEAP引擎。 · InnoDB和BDB儲存引擎提供事務安全表。BDB被包含在為支援它的作業系統釋出的MySQL-Max二進位制分發版裡。InnoDB也預設被包括在所 有MySQL 5.1二進位制分發版裡，你可以按照喜好通過配置MySQL來允許或禁止任一引擎。 · EXAMPLE儲存引擎是一個“存根”引擎，它不做什麼。你可以用這個引擎建立表，但沒有資料被儲存於其中或從其中檢索。這個引擎的目的是服務，在 MySQL原始碼中的一個例子，它演示說明如何開始編寫新儲存引擎。同樣，它的主要興趣是對開發者。 · NDB Cluster是被MySQL Cluster用來實現分割到多臺計算機上的表的儲存引擎。它在MySQL-Max 5.1二進位制分發版裡提供。這個儲存引擎當前只被Linux, Solaris, 和Mac OS X 支援。在未來的MySQL分發版中，我們想要新增其它平臺對這個引擎的支援，包括Windows。 · ARCHIVE儲存引擎被用來無索引地，非常小地覆蓋儲存的大量資料。 · CSV儲存引擎把資料以逗號分隔的格式儲存在文字檔案中。 · BLACKHOLE儲存引擎接受但不儲存資料，並且檢索總是返回一個空集。 · FEDERATED儲存引擎把資料存在遠端資料庫中。在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未來的分發版中，我們想要讓它使用其它驅動器或客戶端連線方法連線到另外的資料來源。 比較常用的是MyISAM和InnoBD 另附：MySQL儲存引擎MyISAM與InnoDB如何選擇？ 雖然MySQL裡的儲存引擎不只是MyISAM與InnoDB這兩個，但常用的就是它倆了。可能有站長並未注意過MySQL的儲存引擎，其實儲存引擎也是資料庫設計裡的一大重要點，那麼部落格系統應該使用哪種儲存引擎呢？ 下面我們分別來看兩種儲存引擎的區別。 一、InnoDB支援事務，MyISAM不支援，這一點是非常之重要。事務是一種高階的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。 二、MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用 三、InnoDB支援外來鍵，MyISAM不支援 四、MyISAM是預設引擎，InnoDB需要指定 五、InnoDB不支援FULLTEXT型別的索引 六、InnoDB中不儲存表的行數，如select count(*) from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含where條件時MyISAM也需要掃描整個表 七、對於自增長的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中可以和其他欄位一起建立聯合索引 八、清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表 九、InnoDB支援行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like ‘%lee%’ 通過以上九點區別，結合個人部落格的特點，推薦個人部落格系統使用MyISAM，因為在部落格裡主要操作是讀取和寫入，很少有鏈式操作。所以選擇MyISAM引擎使你部落格開啟也頁面的效率要高於InnoDB引擎的部落格，當然只是個人的建議，大多數部落格還是根據實際情況下謹慎選擇。 一些關於MyISAM與InnoDB選擇使用： MYISAM和INNODB是Mysql資料庫提供的兩種儲存引擎。兩者的優劣可謂是各有千秋。INNODB會支援一些關聯式資料庫的高階功能，如事務功能和行級鎖，MYISAM不支援。MYISAM的效能更優，佔用的儲存空間少。所以，選擇何種儲存引擎，視具體應用而定。 如果你的應用程式一定要使用事務，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如DELETE FROM mytable這樣的刪除語句。 如果你的應用程式對查詢效能要求較高，就要使用MYISAM了。MYISAM索引和資料是分開的，而且其索引是壓縮的，可以更好地利用記憶體。所以它的查詢效能明顯優於INNODB。壓縮後的索引也能節約一些磁碟空間。MYISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。 有人說MYISAM只能用於小型應用，其實這只是一種偏見。如果資料量比較大，這是需要通過升級架構來解決，比如分表分庫，而不是單純地依賴儲存引擎。 其他一些說法： 現在一般都是選用innodb了，主要是myisam的全表鎖，讀寫序列問題，併發效率鎖表，效率低myisam對於讀寫密集型應用一般是不會去選用的。 關於Mysql資料庫預設的儲存引擎： MyISAM和InnoDB是MySQL的兩種儲存引擎。如果是預設安裝，那就應該是InnoDB，你可以在my.ini檔案中找到default-storage-engine=INNODB；當然你可以在建表時指定相應的儲存引擎。通過show create table xx 可以看見相應資訊。 另附：Mysql中InnoDB和MyISAM的比較 MyISAM： 每個MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案型別。.frm檔案儲存表定義。資料檔案的副檔名為.MYD (MYData)。 MyISAM表格可以被壓縮，而且它們支援全文搜尋。不支援事務，而且也不支援外來鍵。如果事物回滾將造成不完全回滾，不具有原子性。在進行updata時進行表鎖，併發量相對較小。如果執行大量的SELECT，MyISAM是更好的選擇。 MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小 MyISAM快取在記憶體的是索引，不是資料。而InnoDB快取在記憶體的是資料，相對來說，伺服器記憶體越大，InnoDB發揮的優勢越大。 優點：查詢資料相對較快，適合大量的select，可以全文索引。 缺點：不支援事務，不支援外來鍵，併發量較小，不適合大量update InnoDB： 這種型別是事務安全的。.它與BDB型別具有相同的特性,它們還支援外來鍵。InnoDB表格速度很快。具有比BDB還豐富的特性,因此如果需要一個事務安全的儲存引擎，建議使用它。在update時表進行行鎖，併發量相對較大。如果你的資料執行大量的INSERT或UPDATE，出於效能方面的考慮，應該使用InnoDB表。 優點：支援事務，支援外來鍵，併發量較大，適合大量update 缺點：查詢資料相對較快，不適合大量的select 對於支援事物的InnoDB型別的表，影響速度的主要原因是AUTOCOMMIT預設設定是開啟的，而且程式沒有顯式呼叫BEGIN 開始事務，導致每插入一條都自動Commit，嚴重影響了速度。可以在執行sql前呼叫begin，多條sql形成一個事物（即使autocommit開啟也可以），將大大提高效能。 基本的差別為：MyISAM型別不支援事務處理等高階處理，而InnoDB型別支援。 MyISAM型別的表強調的是效能，其執行數度比InnoDB型別更快，但是不提供事務支援，而InnoDB提供事務支援已經外部鍵等高階資料庫功能。 其他比較： MyIASM是IASM表的新版本，有如下擴充套件： 二進位制層次的可移植性。 NULL列索引。 對變長行比ISAM表有更少的碎片。 支援大檔案。 更好的索引壓縮。 更好的鍵嗎統計分佈。 更好和更快的auto_increment處理。 以下是一些細節和具體實現的差別： 1.InnoDB不支援FULLTEXT型別的索引。 2.InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數 即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。 3.對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。 4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。 5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的 InnoDB特性（例如外來鍵）的表不適用。 另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%” 任何一種表都不是萬能的，只用恰當的針對業務型別來選擇合適的表型別，才能最大的發揮MySQL的效能優勢。 innodb和myisam更新比較： innodb的資料組織就是按照主鍵建成的一個B 樹，如果沒有顯示的定義主鍵，那麼innodb會選區一個not null unique key，作為主鍵，如果還是沒有，那麼innodb會建立一個 6位元組的主鍵，主鍵索引到頁不是具體的行位置 不是遞增的主鍵會使得插入的速度很慢，例如使用手機號或身份證號做為主鍵，所以善用AUTO_INCREMENT 表大不可怕，可怕的是count或者高偏移limit,可以將大的limit big換成 limit max_id, xxxxx Limit 0 1000 | limit 1001 1000 | limit 2001 1000 Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000 對於InnoDB來說，按照某列分表，想在單臺伺服器上提高效能是沒有意義的 插入的速度和查詢的速度有時候是不可調和的矛盾 說InnoDB不適合做count是不對的，MyISAM也是一樣的慢，只不過MyISAM將正表的行數快取起來，所以count整表很快，如果有查詢條件，並且不是主鍵查詢，那就沒有什麼區別，主鍵count慢的原因是innodb是按照主鍵組織的，按照主鍵count的時候，會載入資料 InnoDB的頁式儲存會使得InnoDB更容易做整表快取和熱備份 如果表索引很多，那麼InnoDB的更新速度要大於MyISAM，因為InnoDB的輔助索引關聯的是表的主鍵，是一個邏輯的值，而MyISAM的所有索引關聯的是資料的物理位置，更新時有可能資料的物理位置發生變化，如果發生變化，那麼所有的索引都要做更新 InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。