ucbug軟件站:安全、綠色、放心的專業下載站!首頁|最近更新|專題集合|標簽云|站內導航|加入收藏

Mysql數據中MyISAM與InnoDB兩者之間都有哪些區別與選擇

時間:2019-06-16 22:45:39人氣:0

1、MyISAM:默認表類型,它是基于傳統的ISAM類型,ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫,它是存儲...

1、MyISAM:默認表類型,它是基于傳統的ISAM類型,ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫,它是存儲記錄和文件的標準方法。不是事務安全的,而且不支持外鍵,如果執行大量的select,insert MyISAM比較適合。

2、InnoDB:支持事務安全的引擎,支持外鍵、行鎖、事務是他的最大特點。如果有大量的update和insert,建議使用InnoDB,特別是針對多個并發和QPS較高的情況。

一、表鎖差異

MyISAM:

myisam只支持表級鎖,用戶在操作myisam表時,select,update,delete,insert語句都會給表自動加鎖,如果加鎖以后的表滿足insert并發的情況下,可以在表的尾部插入新的數據。也可以通過lock table命令來鎖表,這樣操作主要是可以模仿事務,但是消耗非常大,一般只在實驗演示中使用。

InnoDB :

Innodb支持事務和行級鎖,是innodb的最大特色。

事務的ACID屬性:atomicity,consistent,isolation,durable。

并發事務帶來的幾個問題:更新丟失,臟讀,不可重復讀,幻讀。

事務隔離級別:未提交讀(Read uncommitted),已提交讀(Read committed),可重復讀(Repeatable read),可序列化(Serializable)

四種隔離級別的比較

讀數據一致性及并發副作用

 

隔離級別

讀數據一致性

臟讀

不可重復讀

幻讀

為提交讀(read uncommitted)

最低級別,不讀物理上順壞的數據

已提交讀(read committed)

語句級

可重復讀(Repeatable red)

事務級

可序列化(Serializable)

最高級別,事務級

查看mysql的默認事務隔離級別“show global variables like ‘tx_isolation’; ”

Innodb的行鎖模式有以下幾種:共享鎖,排他鎖,意向共享鎖(表鎖),意向排他鎖(表鎖),間隙鎖。

注意:當語句沒有使用索引,innodb不能確定操作的行,這個時候就使用的意向鎖,也就是表鎖

關于死鎖:

什么是死鎖?當兩個事務都需要獲得對方持有的排他鎖才能完成事務,這樣就導致了循環鎖等待,也就是常見的死鎖類型。

解決死鎖的方法:

1、 數據庫參數

2、 應用中盡量約定程序讀取表的順序一樣

3、 應用中處理一個表時,盡量對處理的順序排序

4、 調整事務隔離級別(避免兩個事務同時操作一行不存在的數據,容易發生死鎖)

二、數據庫文件差異

MyISAM :

myisam屬于堆表

myisam在磁盤存儲上有三個文件,每個文件名以表名開頭,擴展名指出文件類型。

.frm 用于存儲表的定義

.MYD 用于存放數據

.MYI 用于存放表索引

myisam表還支持三種不同的存儲格式:

靜態表(默認,但是注意數據末尾不能有空格,會被去掉)

動態表

壓縮表

InnoDB :

innodb屬于索引組織表

innodb有兩種存儲方式,共享表空間存儲和多表空間存儲

兩種存儲方式的表結構和myisam一樣,以表名開頭,擴展名是.frm。

如果使用共享表空間,那么所有表的數據文件和索引文件都保存在一個表空間里,一個表空間可以有多個文件,通過innodb_data_file_path和innodb_data_home_dir參數設置共享表空間的位置和名字,一般共享表空間的名字叫ibdata1-n。

如果使用多表空間,那么每個表都有一個表空間文件用于存儲每個表的數據和索引,文件名以表名開頭,以.ibd為擴展名。

三、索引差異

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的索引和數據一起保存在表空間里。

四、開發的注意事項

1、可以用 show create table tablename 命令看表的引擎類型。

2、對不支持事務的表做start/commit操作沒有任何效果,在執行commit前已經提交。

3、可以執行以下命令來切換非事務表到事務(數據不會丟失),innodb表比myisam表更安全:alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;

4、默認innodb是開啟自動提交的,如果你按照myisam的使用方法來編寫代碼頁不會存在錯誤,只是性能會很低。如何在編寫代碼時候提高數據庫性能呢?

a、盡量將多個語句綁到一個事務中,進行提交,避免多次提交導致的數據庫開銷。

b、在一個事務獲得排他鎖或者意向排他鎖以后,如果后面還有需要處理的sql語句,在這兩條或者多條sql語句之間程序應盡量少的進行邏輯運算和處理,減少鎖的時間。

c、盡量避免死鎖

d、sql語句如果有where子句一定要使用索引,盡量避免獲取意向排他鎖。

f、針對我們自己的數據庫環境,日志系統是直插入,不修改的,所以我們使用混合引擎方式,ZION_LOG_DB照舊使用myisam存儲引擎,只有ZION_GAME_DB,ZION_LOGIN_DB,DAUM_BILLING使用Innodb引擎。

五、究竟該怎么選擇

下面先讓我們回答一些問題:

◆你的數據庫有外鍵嗎?

◆你需要事務支持嗎?

◆你需要全文索引嗎?

◆你經常使用什么樣的查詢模式?

◆你的數據有多大?

myisam只有索引緩存

innodb不分索引文件數據文件 innodb buffer

myisam只能管理索引,在索引數據大于分配的資源時,會由操作系統來cache;數據文件依賴于操作系統的cache。innodb不管是索引還是數據,都是自己來管理

思考上面這些問題可以讓你找到合適的方向,但那并不是絕對的。如果你需要事務處理或是外鍵,那么InnoDB 可能是比較好的方式。如果你需要全文索引,那么通常來說 MyISAM是好的選擇,因為這是系統內建的,然而,我們其實并不會經常地去測試兩百萬行記錄。所以,就算是慢一點,我們可以通過使用Sphinx從InnoDB中獲得全文索引。

數據的大小,是一個影響你選擇什么樣存儲引擎的重要因素,大尺寸的數據集趨向于選擇InnoDB方式,因為其支持事務處理和故障恢復。數據庫的在小決定了故障恢復的時間長短,InnoDB可以利用事務日志進行數據恢復,這會比較快。而MyISAM可能會需要幾個小時甚至幾天來干這些事,InnoDB只需要幾分鐘。

操作數據庫表的習慣可能也會是一個對性能影響很大的因素。比如: COUNT() 在 MyISAM 表中會非常快,而在InnoDB 表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當的快,但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts 語句在 MyISAM下會快一些,但是updates 在InnoDB下會更快一些——尤其在并發量大的時候。

所以,到底你檢使用哪一個呢?根據經驗來看,如果是一些小型的應用或項目,那么MyISAM 也許會更適合。當然,在大型的環境下使用 MyISAM 也會有很大成功的時候,但卻不總是這樣的。如果你正在計劃使用一個超大數據量的項目,而且需要事務處理或外鍵支持,那么你真的應該直接使用 InnoDB方式。但需要記住InnoDB 的表需要更多的內存和存儲,轉換100GB 的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。

對于支持事務的InnoDB類型的表,影響速度的主要原因是AUTOCOMMIT默認設置是打開的,而且程序沒有顯式調用BEGIN 開始事務,導致每插入一條都自動Commit,嚴重影響了速度。可以在執行sql前調用begin,多條sql形成一個事務(即使autocommit打開也可以),將大大提高性能。

InnoDB

InnoDB 給 MySQL 提供了具有事務(commit)、回滾(rollback)和崩潰修復能力 (crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。 InnoDB 提供了行鎖(locking on row level),提供與 Oracle 類型一致的不加鎖讀取(non- locking read in SELECTs)。這些特性均提高了多用戶并發操作的性能表現。在InnoDB表中不需要擴大鎖定 (lock escalation),因為 InnoDB 的列鎖定(row level locks)適宜非常小的空間。 InnoDB 是 MySQL 上第一個提供外鍵約束(FOREIGN KEY constraints)的表引擎。

InnoDB 的設計目標是處理大容量數據庫系統,它的 CPU 利用率是其它基于磁盤的關系數據庫引擎所不能比的。在技術上,InnoDB 是一套放在 MySQL 后臺的完整數據庫系統,InnoDB 在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。 InnoDB 把數據和索引存放在表空間里,可能包含多個文件,這與其它的不一樣,舉例來說,在 MyISAM 中,表被存放在單獨的文件中。InnoDB 表的大小只受限于操作系統的文件大小,一般為 2 GB。

InnoDB所有的表都保存在同一個數據文件 ibdata1 中(也可能是多個文件,或者是獨立的表空間文件),相對來說比較不好備份,免費的方案可以是拷貝數據文件、備份 binlog,或者用 mysqldump。

MyISAM

MyISAM 是MySQL缺省存貯引擎 .

每張MyISAM 表被存放在三個文件 。frm 文件存放表格定義。 數據文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。

因為MyISAM相對簡單所以在效率上要優于InnoDB..小型應用使用MyISAM是不錯的選擇.

MyISAM表是保存成文件的形式,在跨平臺的數據轉移中使用MyISAM存儲會省去不少的麻煩

以下是一些細節和具體實現的差別:

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的性能優勢。

六、重復地總結一遍

1、MyISAM不支持事務,InnoDB是事務類型的存儲引擎,當我們的表需要用到事務支持的時候,那肯定是不能選擇MyISAM了。

2、MyISAM只支持表級鎖,BDB支持頁級鎖和表級鎖默認為頁級鎖,而InnoDB支持行級鎖和表級鎖默認為行級鎖

表級鎖:直接鎖定整張表,在鎖定期間,其他進程無法對該表進行寫操作,如果設置的是寫鎖,那么其他進程讀也不允許

MyISAM是表級鎖定的存儲引擎,它不會出現死鎖問題

對于write,表鎖定原理如下:

如果表上沒有鎖,在其上面放置一個寫鎖,否則,把鎖定請求放在寫鎖隊列中。

對于read,表鎖定原理如下 :

如果表上沒有寫鎖定,那么把一個讀鎖放在其上面,否則把鎖請求放在讀鎖定隊列中

當一個鎖定被釋放時,表可被寫鎖定隊列中的線程得到,然后才是讀鎖定隊列中的線程。這意味著,如果你在一個表上有許多更新,那么你的SELECT語句將等到所有的寫鎖定線程執行完。

行級鎖:只對指定的行進行鎖定,其他進程還是可以對表中的其他行進行操作的。

行級鎖是Mysql粒度最小的一種鎖,它能大大的減少數據庫操作的沖突,但是粒度越小實現成本也越大。

行級鎖可能會導致“死鎖”,那到底是怎么導致的呢,分析原因:Mysql行級鎖并不是直接鎖記錄,而是鎖索引。索引分為主鍵索引和非主鍵索引兩種,如果一條sql語句操作了主鍵索引,那么Mysql就會鎖定這個主鍵索引,如果sql語句操作的是非主鍵索引,那么Mysql會先鎖定這個非主鍵索引,再去鎖定主鍵索引。

在UPDATE 和 DELETE操作時Mysql不僅會鎖定所有WHERE 條件掃描過得索引,還會鎖定相鄰的鍵值。

“死鎖”舉例分析:

表Test:(ID,STATE,TIME) 主鍵索引:ID 非主鍵索引:STATE

當執行"UPDATE STATE =1011 WHERE STATE=1000" 語句的時候會鎖定STATE索引,由于STATE 是非主鍵索引,所以Mysql還會去請求鎖定ID索引

當另一個SQL語句與語句1幾乎同時執行時:“UPDATE STATE=1010 WHERE ID=1” 對于語句2 Mysql會先鎖定ID索引,由于語句2操作了STATE字段,所以Mysql還會請求鎖定STATE索引。這時。彼此鎖定著對方需要的索引,又都在等待對方釋放鎖定。所以出現了"死鎖"的情況。

行級鎖的優點:

有許多線程訪問不同的行時,只存在少量的沖突。

回滾時只有少量的更改

可以長時間鎖定單一的行

行級鎖缺點:

相對于頁級鎖和表級鎖來說占用了更多的內存

當表的大部分行在使用時,比頁級鎖和表級鎖慢,因為你必須獲得更多的鎖

當在大部分數據上經常使用GROUP BY操作,肯定會比表級鎖和頁級鎖慢。

頁級鎖:表級鎖速度快,但是沖突多;行級鎖速度慢,但沖突少;頁級鎖就是他倆折中的,一次鎖定相鄰的一組記錄。

3、MyISAM引擎不支持外鍵,InnoDB支持外鍵

4、MyISAM引擎的表在大量高并發的讀寫下會經常出現表損壞的情況

我們以前做的項目就遇到這個問題,表的INSERT 和 UPDATE操作很頻繁,原來用的MyISAM引擎,導致表隔三差五就損壞,后來更換成了InnoDB引擎。

其他容易導致表損壞原因:

服務器突然斷電導致數據文件損壞,強制關機(mysqld未關閉情況下)導致表損壞

mysqld進程在寫入操作的時候被殺掉

磁盤故障

表損壞常見癥狀:

查詢表不能返回數據或返回部分數據

打開表失敗: Can’t open file: ‘×××.MYI’ (errno: 145) 。

Error: Table 'p' is marked as crashed and should be repaired 。

Incorrect key file for table: '...'. Try to repair it

Mysql表的恢復:

對于MyISAM表的恢復:

可以使用Mysql自帶的myisamchk工具: myisamchk -r tablename 或者 myisamchk -o tablename(比前面的更保險) 對表進行修復

5、對于count()查詢來說MyISAM更有優勢

因為MyISAM存儲了表中的行數記錄,執行SELECT COUNT() 的時候可以直接獲取到結果,而InnoDB需要掃描全部數據后得到結果。

但是注意一點:對于帶有WHERE 條件的 SELECT COUNT()語句兩種引擎的表執行過程是一樣的,都需要掃描全部數據后得到結果

6、 InnoDB是為處理巨大數據量時的最大性能設計,它的CPU效率可能是任何其它基于磁盤的關系數據庫引擎所不能匹敵的。

7、MyISAM支持全文索引(FULLTEXT),InnoDB不支持

8、MyISAM引擎的表的查詢、更新、插入的效率要比InnoDB高

網上截取了前輩們測試結論:

測試方法:連續提交10個query, 表記錄總數:38萬 , 時間單位 s

引擎類型 MyISAM InnoDB 性能相差

count 0.0008357 3.0163 3609

查詢主鍵 0.005708 0.1574 27.57

查詢非主鍵 24.01 80.37 3.348

更新主鍵 0.008124 0.8183 100.7

更新非主鍵 0.004141 0.02625 6.338

插入 0.004188 0.3694 88.21

(1)加了索引以后,對于MyISAM查詢可以加快:4 206.09733倍,對InnoDB查詢加快510.72921倍,同時對MyISAM更新速度減慢為原來的1/2,InnoDB的更

新速度減慢為原來的1/30。要看情況決定是否要加索引,比如不查詢的log表,不要做任何的索引。

(2)如果你的數據量是百萬級別的,并且沒有任何的事務處理,那么用MyISAM是性能最好的選擇。

(3)InnoDB表的大小更加的大,用MyISAM可省很多的硬盤空間。

在我們測試的這個38w的表中,表占用空間的情況如下:

引擎類型 MyISAM InnoDB

數據 53,924 KB 58,976 KB

索引 13,640 KB 21,072 KB

占用總空間 67,564 KB 80,048 KB

另外一個176W萬記錄的表, 表占用空間的情況如下:

引擎類型 MyIsam InnorDB

數據 56,166 KB 90,736 KB

索引 67,103 KB 88,848 KB

占用總空間 123,269 KB 179,584 KB

七、性能對比

測試的版本是mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686),使用的是Innodb plugin 1.0.8(官方稱比built-in版本性能更好)和默認的MyISAM。

測試機器是筆記本,配置如下:Intel 酷睿2雙核 P8600,2G*2 DDR3 1066內存,320G硬盤5400轉。

測試一:數據插入性能測試,這里我分別對innodb_flush_log_at_trx_commit參數打開和關閉都測了了一下,每次測試都是運行40s,表中數字都是實際插入條數。

MyISAM Innodb (打開) Innodb (關閉)

單線程,逐個插入 120000 60000 60000

4線程,逐個插入 40000*4 20000*4 40000*4

單線程,批量100條/次插入 3600*100 800*100 3000*100

單線程,批量200條/次插入 1800*200 400*200 1600*200

可以發現批量插入的性能遠高于單條插入,但是一次批量的大小對性能影響不大。每條記錄是否都刷新日志的參數對innodb性能的影響巨大。總體上來說,MyISAM性能更優一點。這里有一點需要注意,在插入測試過程中,我對系統資源進行了監控,發現MyISAM對系統資源占用很低,但是Innodb對磁盤占用卻很高,應該是對事務控制多了很多需要記錄的日志。

測試二:數據讀取性能測試。每次隨機讀取1000條記錄,反復進行讀取。

MyISAM Innodb

單線程,200次讀取 5.7s 16.7s

4線程,200次讀取 12s 40.8s

可以看出MyISAM的讀取性能非常恐怖,性能差距在3倍的樣子。

以上兩個測試發現MyISAM在無事務的需求下幾乎完勝,但是要知道它是表鎖,Innodb是行鎖,那么在并發讀寫同時存在的情況下,那結果會是怎么樣呢?!

測試三:兩個線程并發寫入,2個線程并發讀取。

MyISAM Innodb

逐個插入 寫入40s:10000*2 讀取200次*2:14s 寫入40s:60000*2 讀取200次*2:50s

批量100條/次插入 寫入40s:1000*100*2 讀取200次*2:10s 寫入40s:1500*100*2 讀取200次*2:50s

這下立刻顯示出Innodb在并發情況下強勁的性能,幾乎沒有什么性能衰減。而MyISAM單條插入速度變得非常慢,批量插入也下降了40%性能。

總結一下,在寫多讀少的應用中還是Innodb插入性能更穩定,在并發情況下也能基本,如果是對讀取速度要求比較快的應用還是選MyISAM。

三分彩是什么