技術(shù)交流QQ群:1027579432,歡迎你的加入!
歡迎關(guān)注我的微信公眾號(hào):CurryCoder的程序人生
一.InnoDB和MylSAM存儲(chǔ)引擎對比
- MyISAM是MySQL的默認(rèn)數(shù)據(jù)庫引擎(5.5版之前)床三,由早期的ISAM(Indexed Sequential Access Method:有索引的順序訪問方法)所改良。雖然性能極佳杨幼,但卻有一個(gè)缺點(diǎn):不支持事務(wù)處理(transaction)撇簿。不過,在這幾年的發(fā)展下差购,MySQL也導(dǎo)入了InnoDB(另一種數(shù)據(jù)庫引擎)四瘫,以強(qiáng)化參考完整性與并發(fā)違規(guī)處理機(jī)制,后來就逐漸取代MyISAM欲逃。
- InnoDB找蜜,是MySQL的數(shù)據(jù)庫引擎之一,為MySQL AB發(fā)布binary的標(biāo)準(zhǔn)之一稳析。InnoDB由Innobase Oy公司所開發(fā)洗做,2006年五月時(shí)由甲骨文公司并購。與傳統(tǒng)的ISAM與MyISAM相比彰居,InnoDB的最大特色就是支持了ACID兼容的事務(wù)(Transaction)功能诚纸,類似于PostgreSQL。目前InnoDB采用雙軌制授權(quán)陈惰,一是GPL授權(quán)畦徘,另一是專有軟件授權(quán)。
- MyISAM和InnoDB兩者之間有著明顯區(qū)別:
- a.事務(wù)支持:MyISAM不支持事務(wù)抬闯,而InnoDB支持井辆。InnoDB的AUTOCOMMIT默認(rèn)是打開的,即每條SQL語句會(huì)默認(rèn)被封裝成一個(gè)事務(wù)溶握,自動(dòng)提交掘剪,這樣會(huì)影響速度,所以最好是把多條SQL語句顯示放在begin和commit之間奈虾,組成一個(gè)事務(wù)去提交夺谁;MyISAM是非事務(wù)安全型的廉赔,而InnoDB是事務(wù)安全型的,默認(rèn)開啟自動(dòng)提交匾鸥,宜合并事務(wù)蜡塌,一同提交,減小數(shù)據(jù)庫多次提交導(dǎo)致的開銷勿负,大大提高性能馏艾。
- b.存儲(chǔ)結(jié)構(gòu):MyISAM:每個(gè)MyISAM在磁盤上存儲(chǔ)成三個(gè)文件。第一個(gè)文件的名字以表的名字開始奴愉,擴(kuò)展名指出文件類型琅摩。.frm文件存儲(chǔ)表定義。數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)锭硼。索引文件的擴(kuò)展名是.MYI (MYIndex)房资;InnoDB:所有的表都保存在同一個(gè)數(shù)據(jù)文件中(也可能是多個(gè)文件,或者是獨(dú)立的表空間文件)檀头,InnoDB表的大小只受限于操作系統(tǒng)文件的大小轰异,一般為2GB。
- c.存儲(chǔ)空間:MyISAM:可被壓縮暑始,存儲(chǔ)空間較小搭独。支持三種不同的存儲(chǔ)格式:靜態(tài)表(默認(rèn),但是注意數(shù)據(jù)末尾不能有空格廊镜,會(huì)被去掉)牙肝、動(dòng)態(tài)表、壓縮表嗤朴;InnoDB:需要更多的內(nèi)存和存儲(chǔ)配椭,它會(huì)在主內(nèi)存中建立其專用的緩沖池用于高速緩沖數(shù)據(jù)和索引。
- d.可移植性播赁、備份及恢復(fù):MyISAM:數(shù)據(jù)是以文件的形式存儲(chǔ)颂郎,所以在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中會(huì)很方便。在備份和恢復(fù)時(shí)可單獨(dú)針對某個(gè)表進(jìn)行操作容为;InnoDB:免費(fèi)的方案可以是拷貝數(shù)據(jù)文件乓序、備份 binlog,或者用mysqldump坎背,在數(shù)據(jù)量達(dá)到幾十G的時(shí)候就相對痛苦了替劈。
- e.事務(wù)支持:MyISAM:強(qiáng)調(diào)的是性能,每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快得滤,但是不提供事務(wù)支持陨献;InnoDB:提供事務(wù)支持事務(wù),外部鍵等高級(jí)數(shù)據(jù)庫功能懂更。具有事務(wù)(commit)眨业、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表急膀。
- f.AUTO_INCREMENT:MyISAM:可以和其他字段一起建立聯(lián)合索引。引擎的自動(dòng)增長列必須是索引龄捡,如果是組合索引卓嫂,自動(dòng)增長可以不是第一列,他可以根據(jù)前面幾列進(jìn)行排序后遞增聘殖;InnoDB:InnoDB中必須包含只有該字段的索引晨雳。引擎的自動(dòng)增長列必須是索引,如果是組合索引也必須是組合索引的第一列奸腺。
- g.表鎖差異:MyISAM:只支持表級(jí)鎖餐禁,用戶在操作myisam表時(shí),select突照,update帮非,delete,insert語句都會(huì)給表自動(dòng)加鎖绷旗,如果加鎖以后的表滿足insert并發(fā)的情況下喜鼓,可以在表的尾部插入新的數(shù)據(jù)副砍;InnoDB:支持事務(wù)和行級(jí)鎖衔肢,是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能豁翎。但是InnoDB的行鎖角骤,只是在WHERE的主鍵是有效的,非主鍵的WHERE都會(huì)鎖全表的心剥。MyISAM鎖的粒度是表級(jí)邦尊,而InnoDB支持行級(jí)鎖定。簡單來說就是, InnoDB支持?jǐn)?shù)據(jù)行鎖定优烧,而MyISAM不支持行鎖定蝉揍,只支持鎖定整個(gè)表。即MyISAM同一個(gè)表上的讀鎖和寫鎖是互斥的畦娄,MyISAM并發(fā)讀寫時(shí)如果等待隊(duì)列中既有讀請求又有寫請求又沾,默認(rèn)寫請求的優(yōu)先級(jí)高,即使讀請求先到熙卡,所以MyISAM不適合于有大量查詢和修改并存的情況杖刷,那樣查詢進(jìn)程會(huì)長時(shí)間阻塞。因?yàn)镸yISAM是鎖表驳癌,所以某項(xiàng)讀操作比較耗時(shí)會(huì)使其他寫進(jìn)程餓死滑燃。
- h.全文索引:MyISAM:支持(FULLTEXT類型的)全文索引;InnoDB:不支持(FULLTEXT類型的)全文索引颓鲜,但是innodb可以使用sphinx插件支持全文索引表窘,并且效果更好典予。全文索引是指對char、varchar和text中的每個(gè)詞(停用詞除外)建立倒排序索引乐严。MyISAM的全文索引其實(shí)沒啥用熙参,因?yàn)樗恢С种形姆衷~,必須由使用者分詞后加入空格再寫到數(shù)據(jù)表里麦备,而且少于4個(gè)漢字的詞會(huì)和停用詞一樣被忽略掉孽椰。另外,MyIsam索引和數(shù)據(jù)分離凛篙,InnoDB在一起黍匾,MyIsam天生非聚簇索引,最多有一個(gè)unique的性質(zhì)呛梆,InnoDB的數(shù)據(jù)文件本身就是主鍵索引文件锐涯,這樣的索引被稱為“聚簇索引”。
- i.表主鍵:MyISAM:允許沒有任何索引和主鍵的表存在填物,索引都是保存行的地址纹腌;InnoDB:如果沒有設(shè)定主鍵或者非空唯一索引,就會(huì)自動(dòng)生成一個(gè)6字節(jié)的主鍵(用戶不可見)滞磺,數(shù)據(jù)是主索引的一部分升薯,附加索引保存的是主索引的值。InnoDB的主鍵范圍更大击困,最大是MyISAM的2倍涎劈。
- j.表的具體行數(shù):MyISAM:保存有表的總行數(shù),如果select count(*) from table;會(huì)直接取出出該值阅茶;InnoDB:沒有保存表的總行數(shù)(只能遍歷)蛛枚,如果使用select count(*) from table;就會(huì)遍歷整個(gè)表脸哀,消耗相當(dāng)大蹦浦,但是在加了where條件后,myisam和innodb處理的方式都一樣撞蜂。
- k.CURD操作:MyISAM:如果執(zhí)行大量的SELECT盲镶,MyISAM是更好的選擇;InnoDB:如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE谅摄,出于性能方面的考慮徒河,應(yīng)該使用InnoDB表。DELETE 從性能上InnoDB更優(yōu)送漠,但DELETE FROM table時(shí)顽照,InnoDB不會(huì)重新建立表,而是一行一行的刪除,在innodb上如果要清空保存有大量數(shù)據(jù)的表代兵,最好使用truncate table這個(gè)命令尼酿。
- l.外鍵:MyISAM:不支持;InnoDB:支持
- m.查詢效率:沒有where的count(*)使用MyISAM要比InnoDB快得多植影。因?yàn)镸yISAM內(nèi)置了一個(gè)計(jì)數(shù)器裳擎,count(*)時(shí)它直接從計(jì)數(shù)器中讀,而InnoDB必須掃描全表思币。所以在InnoDB上執(zhí)行count(*)時(shí)一般要伴隨where鹿响,且where中要包含主鍵以外的索引列。為什么這里特別強(qiáng)調(diào)“主鍵以外”?因?yàn)镮nnoDB中primary index是和raw data存放在一起的谷饿,而secondary index則是單獨(dú)存放惶我,然后有個(gè)指針指向primary key。所以只是count(*)的話使用secondary index掃描更快博投,而primary key則主要在掃描索引同時(shí)要返回raw data時(shí)的作用較大绸贡。MyISAM相對簡單,所以在效率上要優(yōu)于InnoDB毅哗,小型應(yīng)用可以考慮使用MyISAM听怕。
- 總結(jié):通過上述的分析,基本上可以考慮使用InnoDB來替代MyISAM引擎了虑绵,原因是InnoDB自身很多良好的特點(diǎn)尿瞭,比如事務(wù)支持、存儲(chǔ) 過程蒸殿、視圖筷厘、行級(jí)鎖定等等鸣峭,在并發(fā)很多的情況下宏所,相信InnoDB的表現(xiàn)肯定要比MyISAM強(qiáng)很多。另外摊溶,任何一種表都不是萬能的爬骤,只用恰當(dāng)?shù)尼槍I(yè)務(wù)類型來選擇合適的表類型,才能最大的發(fā)揮MySQL的性能優(yōu)勢莫换。如果不是很復(fù)雜的Web應(yīng)用霞玄,非關(guān)鍵應(yīng)用虹蒋,還是可以繼續(xù)考慮MyISAM的房午,這個(gè)具體情況可以自己斟酌汰聋。
二.MyISAM和InnoDB兩者的應(yīng)用場景
- a.MyISAM管理非事務(wù)表帘营。它提供高速存儲(chǔ)和檢索扛吞,以及全文搜索能力试伙。如果應(yīng)用中需要執(zhí)行大量的SELECT查詢议薪,那么MyISAM是更好的選擇罐孝。
- b.InnoDB用于事務(wù)處理應(yīng)用程序,具有眾多特性狞尔,包括ACID事務(wù)支持丛版。如果應(yīng)用中需要執(zhí)行大量的INSERT或UPDATE操作,則應(yīng)該使用InnoDB偏序,這樣可以提高多用戶并發(fā)操作的性能页畦。