MySQL的存儲引擎MyISAM和InnoDB

MySQL的存儲引擎

• 資料庫引擎
• mysql的兩種存儲引擎的比較
• • InnoDB特點介紹及适用場景
• MyISAM和InnoDB兩者的應用場景
• MyISAM和InnoDB存儲引擎使用的鎖
• 資料庫中的鎖
• MySQL的存儲引擎MyISAM與InnoDB如何選擇

♠ \color{pink}{\spadesuit} ♠

不知道你們有沒有留意到資料庫表裡經常看到這樣的一句ENGINES=InnoDB DEFAULT，有沒有好奇是啥意思？那就一起學習吧~

資料庫引擎

資料庫引擎是用于存儲、處理和保護資料的核心服務。

當你通路資料庫時，不管是手工通路，還是程式通路，都不是直接讀寫資料庫檔案，而是通過資料庫引擎去通路資料庫檔案

mysql的兩種存儲引擎的比較

MySQL5.5 之前預設使用MyISAM ，5.6 以後預設為 InnoDB

InnoDB是事務型資料庫的首選引擎，支援事務安全表（ACID），支援行鎖定和外鍵，其為MySQL資料庫5.6及以後版本預設存儲引擎。

InnoDB特點介紹及适用場景

MyISAM 和 InnoDB 是許多人在使用MySQL時最常用的兩個存儲引擎，這兩個各有優劣，視具體應用而定。

基本的差别為：

• MyISAM隻支援表級鎖，使用者在操作MyISAM表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以後的表滿足insert并發的情況下，可以在表的尾部插入新的資料。不支援外鍵。
• InnoDB支援事務和行級鎖，行鎖大幅度提高了多使用者并發操作的新能。支援外鍵；對一個包含外鍵的InnoDB表轉為MYISAM會失敗；
• MyISAM 不支援事務處理等進階處理，強調的是性能，其執行速度比 InnoDB 更快，但是不提供事務支援，而 InnoDB 提供事務支援、外部鍵等進階資料庫功能。

InnoDB支援事務和行級鎖，是InnoDB的最大特色。但是InnoDB的行鎖，隻是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。如果資料表主要用來插入和查詢記錄，則 MyISAM 能提供較高的處理效率；如果要提供送出、復原、崩潰恢複能力的事務安全（ACID相容）能力，并要求實作并發控制，InnoDB 是一個好的選擇。

不知道ACID是什麼的童鞋可要去了解哦，面試時比較常問到的。可以看下這篇資料庫事務的四大特性及事物的隔離級别

MyISAM和InnoDB兩者的應用場景

1. MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜尋能力。如果應用中需要執行大量的SELECT查詢，那麼MyISAM是更好的選擇。
2. InnoDB用于事務處理應用程式，具有衆多特性，包括ACID事務支援。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多使用者并發操作的性能。

但是實際場景中，針對具體問題需要具體分析，一般而言可以遵循以下幾個問題：

• 資料庫是否有外鍵？
• 是否需要事務支援？
• 是否需要全文索引？
• 資料庫經常使用什麼樣的查詢模式？在寫多讀少的應用中還是Innodb插入性能更穩定，在并發情況下也能基本，如果是對讀取速度要求比較快的應用還是選MyISAM。
• 資料庫的資料有多大？ 大尺寸傾向于innodb，因為事務日志，故障恢複。

摘自MySQL兩種存儲引擎: MyISAM和InnoDB 簡單總結

MyISAM和InnoDB存儲引擎使用的鎖

MyISAM 采用表級鎖(table-level locking)。

InnoDB 支援行級鎖(row-level locking)和表級鎖,預設為行級鎖

資料庫中的鎖

頁級:引擎 BDB

表級:引擎 MyISAM

行級:引擎 INNODB

♢ \color{pink}{\diamondsuit} ♢ 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低；

♢ \color{pink}{\diamondsuit} ♢ 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高；

♢ \color{pink}{\diamondsuit} ♢ 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

适用：從鎖的角度來說，表級鎖更适合于以查詢為主，隻有少量按索引條件更新資料的應用，如Web應用；而行級鎖則更适合于有大量按索引條件并發更新少量不同資料，同時又有并發查詢的應用，如一些線上事務處理（OLTP）系統。

檢視資料庫引擎指令

檢視MySQL提供的所有存儲引擎

show engines
           

從上圖我們可以檢視出 MySQL 目前預設的存儲引擎是InnoDB，并且在5.7版本所有的存儲引擎中隻有 InnoDB 是事務性存儲引擎，也就是說隻有 InnoDB 支援事務。

檢視MySQL目前預設的存儲引擎

我們也可以通過下面的指令檢視預設的存儲引擎。

show databases; # 先檢視資料庫名
use databse_name; # 進入資料庫
show tables_name; # 檢視資料庫中的表

show table status like "table_name";
           

MySQL的存儲引擎MyISAM與InnoDB如何選擇

MySQL有多種存儲引擎，每種存儲引擎有各自的優缺點，可以擇優選擇使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

雖然MySQL裡的存儲引擎不隻是MyISAM與InnoDB這兩個，但常用的就是兩個。

關于MySQL資料庫提供的兩種存儲引擎，MyISAM與InnoDB選擇使用：

1.InnoDB會支援一些關系資料庫的進階功能，如事務功能和行級鎖，MyISAM不支援。

2.MyISAM的性能更優，占用的存儲空間少，是以，選擇何種存儲引擎，視具體應用而定。

如果你的應用程式一定要使用事務，毫無疑問你要選擇InnoDB引擎。但要注意，InnoDB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如DELETE FROM mytable這樣的删除語句。

如果你的應用程式對查詢性能要求較高，就要使用MyISAM了。MyISAM索引和資料是分開的，而且其索引是壓縮的，可以更好地利用記憶體。是以它的查詢性能明顯優于InnoDB。壓縮後的索引也能節約一些磁盤空間。MyISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。

有人說MyISAM隻能用于小型應用，其實這隻是一種偏見。如果資料量比較大，這是需要通過更新架構來解決，比如分表分庫，而不是單純地依賴存儲引擎。

現在一般都是選用InnoDB了，主要是MyISAM的全表鎖，讀寫串行問題，并發效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。

MEMORY存儲引擎

MEMORY是MySQL中一類特殊的存儲引擎。它使用存儲在記憶體中的内容來建立表，而且資料全部放在記憶體中。這些特性與前面的兩個很不同。

每個基于MEMORY存儲引擎的表實際對應一個磁盤檔案。該檔案的檔案名與表名相同，類型為frm類型。該檔案中隻存儲表的結構。而其資料檔案，都是存儲在記憶體中，這樣有利于資料的快速處理，提高整個表的效率。值得注意的是，伺服器需要有足夠的記憶體來維持MEMORY存儲引擎的表的使用。如果不需要了，可以釋放記憶體，甚至删除不需要的表。

MEMORY預設使用哈希索引。速度比使用B型樹索引快。當然如果你想用B型樹索引，可以在建立索引時指定。

注意，MEMORY用到的很少，因為它是把資料存到記憶體中，如果記憶體出現異常就會影響資料。如果重新開機或者關機，所有資料都會消失。是以，基于MEMORY的表的生命周期很短，一般是一次性的。

簡單了解完資料庫引擎後你還需要去了解資料庫索引，資料庫事務的四大特性以及事務隔離級别。一方面可以拓寬自己的知識廣度，另一方面也能學的更加系統~，面試常問的考點。