詳解MyISAM與InnoDB兩者之間區別與應用場景
概述
MyISAM是MySQL的默認數據庫引擎(5.5版之前),由早期的ISAM(Indexed Sequential Access Method:有索引的順序訪問方法)所改良。雖然性能極佳,但卻有一個缺點:不支持事務處理(transaction)。不過,在這幾年的發展下,MySQL也導入了InnoDB(另一種數據庫引擎),以強化參考完整性與併發違規處理機制,後來就逐漸取代MyISAM。
InnoDB,是MySQL的數據庫引擎之一,為MySQL AB發佈binary的標準之一。InnoDB由Innobase Oy公司所開發,2006年五月時由甲骨文公司併購。與傳統的ISAM與MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事務(Transaction)功能,類似於PostgreSQL。下面簡單說下兩者之間的區別。
01
表鎖差異
1、MyISAM:
myisam只支持表級鎖,用戶在操作myisam表時,select,update,delete,insert語句都會給表自動加鎖,如果加鎖以後的表滿足insert併發的情況下,可以在表的尾部插入新的數據。也可以通過lock table命令來鎖表,這樣操作主要是可以模仿事務,但是消耗非常大,一般只在實驗演示中使用。
2、InnoDB :
Innodb支持事務和行級鎖,是innodb的最大特色。
事務的ACID屬性:atomicity,consistent,isolation,durable。
併發事務帶來的幾個問題:更新丟失,髒讀,不可重複讀,幻讀。
事務隔離級別:未提交讀(Read uncommitted),已提交讀(Read committed),可重複讀(Repeatable read),可序列化(Serializable)
四種隔離級別的比較
查看mysql的默認事務隔離級別“show global variables like 'tx_isolation'; ”
Innodb的行鎖模式有以下幾種:共享鎖,排他鎖,意向共享鎖(表鎖),意向排他鎖(表鎖),間隙鎖。
注意:當語句沒有使用索引,innodb不能確定操作的行,這個時候就使用的意向鎖,也就是表鎖.
02
數據庫文件差異
1、MyISAM
myisam屬於堆表
myisam在磁盤存儲上有三個文件,每個文件名以表名開頭,擴展名指出文件類型。
- .frm 用於存儲表的定義
- .MYD 用於存放數據
- .MYI 用於存放表索引
myisam表還支持三種不同的存儲格式:
- 靜態表(默認,但是注意數據末尾不能有空格,會被去掉)
- 動態表
- 壓縮表
2、InnoDB
innodb屬於索引組織表
innodb有兩種存儲方式,共享表空間存儲和多表空間存儲
兩種存儲方式的表結構和myisam一樣,以表名開頭,擴展名是.frm。
如果使用共享表空間,那麼所有表的數據文件和索引文件都保存在一個表空間裡,一個表空間可以有多個文件,通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字,一般共享表空間的名字叫ibdata1-n。
如果使用多表空間,那麼每個表都有一個表空間文件用於存儲每個表的數據和索引,文件名以表名開頭,以.ibd為擴展名。
03
索引差異
1、關於自動增長
myisam引擎的自動增長列必須是索引,如果是組合索引,自動增長可以不是第一列,它可以根據前面幾列進行排序後遞增。
innodb引擎的自動增長咧必須是索引,如果是組合索引也必須是組合索引的第一列。
2、關於主鍵
myisam允許沒有任何索引和主鍵的表存在,
myisam的索引都是保存行的地址。
innodb引擎如果沒有設定主鍵或者非空唯一索引,就會自動生成一個6字節的主鍵(用戶不可見)
innodb的數據是主索引的一部分,附加索引保存的是主索引的值。
3、關於count()函數
myisam保存有表的總行數,如果select count(*) from table;會直接取出出該值
innodb沒有保存表的總行數,如果使用select count(*) from table;就會遍歷整個表,消耗相當大,但是在加了wehre 條件後,myisam和innodb處理的方式都一樣。
4、全文索引
myisam支持 FULLTEXT類型的全文索引
innodb不支持FULLTEXT類型的全文索引,但是innodb可以使用sphinx插件支持全文索引,並且效果更好。(sphinx 是一個開源軟件,提供多種語言的API接口,可以優化mysql的各種查詢)
5、delete from table
使用這條命令時,innodb不會從新建立表,而是一條一條的刪除數據,在innodb上如果要清空保存有大量數據的表,最 好不要使用這個命令。(推薦使用truncate table,不過需要用戶有drop此表的權限)
6、索引保存位置
myisam的索引以表名+.MYI文件分別保存。
innodb的索引和數據一起保存在表空間裡。
04
MyISAM和InnoDB兩者的應用場景
1) MyISAM管理非事務表。它提供高速存儲和檢索,以及全文搜索能力。如果應用中需要執行大量的SELECT查詢,那麼MyISAM是更好的選擇。
2) InnoDB用於事務處理應用程序,具有眾多特性,包括ACID事務支持。如果應用中需要執行大量的INSERT或UPDATE操作,則應該使用InnoDB,這樣可以提高多用戶併發操作的性能。
但是實際場景中,針對具體問題需要具體分析,一般而言可以遵循以下幾個問題:
- 數據庫是否有外鍵?
- 是否需要事務支持?
- 是否需要全文索引?
- 數據庫經常使用什麼樣的查詢模式?在寫多讀少的應用中還是Innodb插入性能更穩定,在併發情況下也能基本,如果是對讀取速度要求比較快的應用還是選MyISAM。
- 數據庫的數據有多大? 大尺寸傾向於innodb,因為事務日誌,故障恢復。
總結
總結一下,在寫多讀少的應用中還是Innodb插入性能更穩定,在併發情況下也基本穩定,如果是對讀取速度要求比較快的應用可以考慮選MyISAM。
後面會分享更多devops和DBA方面的內容,感興趣的朋友可以關注一下~