MySQL

作者：曹理鵬
原文地址：https://icocos.github.io

• MyISAM是MySQL的默認數據庫引擎（5.5版之前）雕沉，由早期的ISAM（Indexed Sequential Access Method：有索引的順序訪問方法）所改良恭理。雖然性能極佳，但卻有一個缺點：不支持事務處理（transaction）丰涉。不過，在這幾年的發(fā)展下此再，MySQL也導入了InnoDB（另一種數據庫引擎），以強化參考完整性與并發(fā)違規(guī)處理機制玲销，后來就逐漸取代MyISAM输拇。

• InnoDB，是MySQL的數據庫引擎之一贤斜，為MySQL AB發(fā)布binary的標準之一策吠。InnoDB由Innobase Oy公司所開發(fā)，2006年五月時由甲骨文公司并購瘩绒。與傳統的ISAM與MyISAM相比猴抹，InnoDB的最大特色就是支持了ACID兼容的事務（Transaction）功能，類似于PostgreSQL锁荔。目前InnoDB采用雙軌制授權蟀给，一是GPL授權，另一是專有軟件授權阳堕。

MyISAM和InnoDB兩者之間有著明顯區(qū)別跋理，簡單梳理如下:

1) 事務支持

MyISAM不支持事務，而InnoDB支持恬总。InnoDB的AUTOCOMMIT默認是打開的前普，即每條SQL語句會默認被封裝成一個事務，自動提交壹堰，這樣會影響速度拭卿，所以最好是把多條SQL語句顯示放在begin和commit之間，組成一個事務去提交贱纠。

MyISAM是非事務安全型的峻厚，而InnoDB是事務安全型的，默認開啟自動提交谆焊，宜合并事務目木，一同提交，減小數據庫多次提交導致的開銷懊渡，大大提高性能刽射。

2) 存儲結構

MyISAM：每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始剃执，擴展名指出文件類型誓禁。.frm文件存儲表定義。數據文件的擴展名為.MYD (MYData)肾档。索引文件的擴展名是.MYI (MYIndex)摹恰。

InnoDB：所有的表都保存在同一個數據文件中（也可能是多個文件辫继，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統文件的大小俗慈，一般為2GB姑宽。

3) 存儲空間

MyISAM：可被壓縮，存儲空間較小闺阱。支持三種不同的存儲格式：靜態(tài)表(默認炮车，但是注意數據末尾不能有空格，會被去掉)酣溃、動態(tài)表瘦穆、壓縮表。

InnoDB：需要更多的內存和存儲赊豌，它會在主內存中建立其專用的緩沖池用于高速緩沖數據和索引扛或。

4) 可移植性、備份及恢復

MyISAM：數據是以文件的形式存儲碘饼，所以在跨平臺的數據轉移中會很方便熙兔。在備份和恢復時可單獨針對某個表進行操作。

InnoDB：免費的方案可以是拷貝數據文件艾恼、備份 binlog黔姜，或者用 mysqldump，在數據量達到幾十G的時候就相對痛苦了蒂萎。

5) 事務支持

MyISAM：強調的是性能秆吵，每次查詢具有原子性,其執(zhí)行數度比InnoDB類型更快，但是不提供事務支持五慈。

InnoDB：提供事務支持事務纳寂，外部鍵等高級數據庫功能。 具有事務(commit)泻拦、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表毙芜。

6) AUTO_INCREMENT

MyISAM：可以和其他字段一起建立聯合索引。引擎的自動增長列必須是索引争拐，如果是組合索引腋粥，自動增長可以不是第一列，他可以根據前面幾列進行排序后遞增架曹。

InnoDB：InnoDB中必須包含只有該字段的索引隘冲。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列绑雄。

7) 表鎖差異

MyISAM：只支持表級鎖展辞，用戶在操作myisam表時，select万牺，update罗珍，delete洽腺，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下覆旱，可以在表的尾部插入新的數據蘸朋。

InnoDB：支持事務和行級鎖，是innodb的最大特色扣唱。行鎖大幅度提高了多用戶并發(fā)操作的新能藕坯。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的画舌，非主鍵的WHERE都會鎖全表的堕担。

MyISAM鎖的粒度是表級已慢，而InnoDB支持行級鎖定曲聂。簡單來說就是, InnoDB支持數據行鎖定，而MyISAM不支持行鎖定佑惠，只支持鎖定整個表朋腋。即MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM并發(fā)讀寫時如果等待隊列中既有讀請求又有寫請求膜楷，默認寫請求的優(yōu)先級高旭咽，即使讀請求先到，所以MyISAM不適合于有大量查詢和修改并存的情況赌厅，那樣查詢進程會長時間阻塞穷绵。因為MyISAM是鎖表，所以某項讀操作比較耗時會使其他寫進程餓死特愿。

8) 全文索引

MyISAM：支持(FULLTEXT類型的)全文索引

InnoDB：不支持(FULLTEXT類型的)全文索引仲墨，但是innodb可以使用sphinx插件支持全文索引，并且效果更好揍障。

全文索引是指對char目养、varchar和text中的每個詞（停用詞除外）建立倒排序索引。MyISAM的全文索引其實沒啥用毒嫡，因為它不支持中文分詞癌蚁，必須由使用者分詞后加入空格再寫到數據表里，而且少于4個漢字的詞會和停用詞一樣被忽略掉兜畸。

另外努释，MyIsam索引和數據分離，InnoDB在一起咬摇，MyIsam天生非聚簇索引洽洁，最多有一個unique的性質，InnoDB的數據文件本身就是主鍵索引文件菲嘴，這樣的索引被稱為“聚簇索引”

9) 表主鍵

MyISAM：允許沒有任何索引和主鍵的表存在饿自，索引都是保存行的地址汰翠。

InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵(用戶不可見)昭雌，數據是主索引的一部分复唤，附加索引保存的是主索引的值。InnoDB的主鍵范圍更大烛卧，最大是MyISAM的2倍佛纫。

10) 表的具體行數

MyISAM：保存有表的總行數，如果select count(*) from table;會直接取出出該值总放。

InnoDB：沒有保存表的總行數(只能遍歷)呈宇，如果使用select count(*) from table；就會遍歷整個表局雄，消耗相當大甥啄，但是在加了wehre條件后，myisam和innodb處理的方式都一樣炬搭。

11) CURD操作

MyISAM：如果執(zhí)行大量的SELECT蜈漓，MyISAM是更好的選擇。

InnoDB：如果你的數據執(zhí)行大量的INSERT或UPDATE宫盔，出于性能方面的考慮融虽，應該使用InnoDB表。DELETE 從性能上InnoDB更優(yōu)灼芭，但DELETE FROM table時有额，InnoDB不會重新建立表，而是一行一行的刪除彼绷，在innodb上如果要清空保存有大量數據的表巍佑，最好使用truncate table這個命令。

12) 外鍵

MyISAM：不支持

InnoDB：支持

13) 查詢效率

沒有where的count()使用MyISAM要比InnoDB快得多苛预。因為MyISAM內置了一個計數器句狼，count()時它直接從計數器中讀，而InnoDB必須掃描全表热某。所以在InnoDB上執(zhí)行count()時一般要伴隨where腻菇，且where中要包含主鍵以外的索引列。為什么這里特別強調“主鍵以外”昔馋？因為InnoDB中primary index是和raw data存放在一起的筹吐，而secondary index則是單獨存放，然后有個指針指向primary key秘遏。所以只是count()的話使用secondary index掃描更快丘薛，而primary key則主要在掃描索引同時要返回raw data時的作用較大。MyISAM相對簡單邦危，所以在效率上要優(yōu)于InnoDB洋侨，小型應用可以考慮使用MyISAM舍扰。

通過上述的分析，基本上可以考慮使用InnoDB來替代MyISAM引擎了希坚，原因是InnoDB自身很多良好的特點边苹，比如事務支持、存儲 過程裁僧、視圖个束、行級鎖定等等，在并發(fā)很多的情況下聊疲，相信InnoDB的表現肯定要比MyISAM強很多茬底。另外，任何一種表都不是萬能的获洲，只用恰當的針對業(yè)務類型來選擇合適的表類型阱表，才能最大的發(fā)揮MySQL的性能優(yōu)勢。如果不是很復雜的Web應用昌妹，非關鍵應用捶枢，還是可以繼續(xù)考慮MyISAM的握截，這個具體情況可以自己斟酌飞崖。
MyISAM和InnoDB兩者的應用場景：

1. MyISAM管理非事務表。它提供高速存儲和檢索谨胞，以及全文搜索能力固歪。如果應用中需要執(zhí)行大量的SELECT查詢，那么MyISAM是更好的選擇胯努。

2. InnoDB用于事務處理應用程序牢裳，具有眾多特性，包括ACID事務支持叶沛。如果應用中需要執(zhí)行大量的INSERT或UPDATE操作蒲讯，則應該使用InnoDB，這樣可以提高多用戶并發(fā)操作的性能灰署。

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

• 數據庫是否有外鍵溉箕？
• 是否需要事務支持晦墙？
• 是否需要全文索引？
• 數據庫經常使用什么樣的查詢模式肴茄？在寫多讀少的應用中還是Innodb插入性能更穩(wěn)定晌畅，在并發(fā)情況下也能基本，如果是對讀取速度要求比較快的應用還是選MyISAM寡痰。
• 數據庫的數據有多大抗楔？ 大尺寸傾向于innodb棋凳，因為事務日志，故障恢復连躏。

其他

• MyBatis面試題集錦（精選）

• Redis面試題集錦（精選）

• RabbitMQ面試題集錦（精選）（另附思維導圖）

文末

歡迎關注個人微信公眾號：Coder編程
歡迎關注Coder編程公眾號贫橙，主要分享數據結構與算法、Java相關知識體系反粥、框架知識及原理卢肃、Spring全家桶、微服務項目實戰(zhàn)才顿、DevOps實踐之路莫湘、每日一篇互聯網大廠面試或筆試題以及PMP項目管理知識等。更多精彩內容正在路上~
也分享一些雜文~

文章收錄至
Github: https://github.com/CoderMerlin/coder-programming
Gitee: https://gitee.com/573059382/coder-programming
歡迎關注并star~

微信公眾號