Mysql中MyISAM和InnoDB的區(qū)別及說明
MyISAM和InnoDB的區(qū)別
1. 定義
InnoDB:
InnoDB:MySQL默認的事務型引擎,也是最重要和使用最廣泛的存儲引擎。
它被設計成為大量的短期事務,短期事務大部分情況下是正常提交的,很少被回滾。InnoDB的性能與自動崩潰恢復的特性,使得它在非事務存儲需求中也很流行。除非有非常特別的原因需要使用其他的存儲引擎,否則應該優(yōu)先考慮InnoDB引擎。
MyISAM:
MyISAM:在MySQL 5.5 及之前的版本,MyISAM是默認引擎。
MyISAM提供的大量的特性,包括全文索引、壓縮、空間函數(shù)(GIS)等,但MyISAM并不支持事務以及行級鎖,而且一個毫無疑問的缺陷是崩潰后無法安全恢復。正是由于MyISAM引擎的緣故,即使MySQL支持事務已經(jīng)很長時間了,在很多人的概念中MySQL還是非事務型數(shù)據(jù)庫。盡管這樣,它并不是一無是處的。對于只讀的數(shù)據(jù),或者表比較小,可以忍受修復操作,則依然可以使用MyISAM(但請不要默認使用MyISAM,而是應該默認使用InnoDB)
2. 區(qū)別
InnoDB 支持事務,MyISAM 不支持事務。這是 MySQL 將默認存儲引擎從 MyISAM 變成 InnoDB 的重要原因之一;
InnoDB 支持外鍵,而 MyISAM 不支持。對一個包含外鍵的 InnoDB 表轉為 MYISAM 會失??;
InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的文件存放在主鍵索引的葉子節(jié)點上,因此 InnoDB 必須要有主鍵,通過主鍵索引效率很高。但是輔助索引需要兩次查詢,先查詢到主鍵,然后再通過主鍵查詢到數(shù)據(jù)。因此,主鍵不應該過大,因為主鍵太大,其他索引也都會很大。而 MyISAM 是非聚集索引,數(shù)據(jù)文件是分離的,索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的。
InnoDB 不保存表的具體行數(shù),執(zhí)行 select count(*) from table 時需要全表掃描。而MyISAM 用一個變量保存了整個表的行數(shù),執(zhí)行上述語句時只需要讀出該變量即可,速度很快;
InnoDB 最小的鎖粒度是行鎖,MyISAM 最小的鎖粒度是表鎖。一個更新語句會鎖住整張表,導致其他查詢和更新都會被阻塞,因此并發(fā)訪問受限。
這也是 MySQL 將默認存儲引擎從 MyISAM 變成 InnoDB 的重要原因之一;
3. 使用
是否要支持事務,如果要請選擇 InnoDB,如果不需要可以考慮 MyISAM;
如果表中絕大多數(shù)都只是讀查詢,可以考慮 MyISAM,如果既有讀寫也挺頻繁,請使用InnoDB。
系統(tǒng)奔潰后,MyISAM恢復起來更困難,能否接受,不能接受就選 InnoDB;
MySQL5.5版本開始Innodb已經(jīng)成為Mysql的默認引擎(之前是MyISAM),說明其優(yōu)勢是有目共睹的。如果你不知道用什么存儲引擎,那就用InnoDB,至少不會差。
MyISAM和InnoDB索引結構分析
存儲引擎作用于什么對象
存儲引擎是作用在表上的,而不是數(shù)據(jù)庫。
MyISAM和InnoDB對索引和數(shù)據(jù)的存儲在磁盤上是如何體現(xiàn)的
先來看下面創(chuàng)建的兩張表信息,role表使用的存儲引擎是MyISAM,而user使用的是InnoDB:
再來看下兩張表在磁盤中的索引文件和數(shù)據(jù)文件:
1. role表有三個文件,對應如下:
role.frm
:表結構文件role.MYD
:數(shù)據(jù)文件(MyISAM Data)role.MYI
:索引文件(MyISAM Index)
2. user表有兩個文件,對應如下:
user.frm
:表結構文件user.ibd
:索引和數(shù)據(jù)文件(InnoDB Data)
也由于兩種引擎對索引和數(shù)據(jù)的存儲方式的不同,我們也稱MyISAM的索引為非聚集索引,InnoDB的索引為聚集索引。
MyISAM主鍵索引與輔助索引的結構
我們先列舉一部分數(shù)據(jù)出來分析,如下:
上面已經(jīng)說明了MyISAM引擎的索引文件和數(shù)據(jù)文件是分離的,我們接著看一下下面兩種索引結構異同。
1. 主鍵索引:
上一篇文章已經(jīng)介紹過數(shù)據(jù)庫索引是采用B+Tree存儲,并且只在葉子節(jié)點存儲數(shù)據(jù),在MyISAM引擎中葉子結點存儲的數(shù)據(jù)其實是索引和數(shù)據(jù)的文件指針兩類。
如下圖中我們以Col1列作為主鍵建立索引,對應的葉子結點儲存形式可以看一下表格。
通過索引查找數(shù)據(jù)的流程:先從索引文件中查找到索引節(jié)點,從中拿到數(shù)據(jù)的文件指針,再到數(shù)據(jù)文件中通過文件指針定位了具體的數(shù)據(jù)。
2. 輔助(非主鍵)索引:
以Col2列建立索引,得到的輔助索引結構跟上面的主鍵索引的結構是相同的。
InnoDB主鍵索引與輔助索引的結構
1. 主鍵索引:
我們已經(jīng)知道InnoDB索引是聚集索引,它的索引和數(shù)據(jù)是存入同一個.idb文件中的,因此它的索引結構是在同一個樹節(jié)點中同時存放索引和數(shù)據(jù),如下圖中最底層的葉子節(jié)點有三行數(shù)據(jù),對應于數(shù)據(jù)表中的Col1、Col2、Col3數(shù)據(jù)項。
2. 輔助(非主鍵)索引:
這次我們以數(shù)據(jù)表中的Col3列的字符串數(shù)據(jù)建立輔助索引,它的索引結構跟主鍵索引的結構有很大差別,我們來看下面的圖:
在最底層的葉子結點有兩行數(shù)據(jù),第一行的字符串是輔助索引,按照ASCII碼進行排序,第二行的整數(shù)是主鍵的值。
InnoDB索引結構需要注意的點
- 1. 數(shù)據(jù)文件本身就是索引文件
- 2. 表數(shù)據(jù)文件本身就是按B+Tree組織的一個索引結構文件
- 3. 聚集索引中葉節(jié)點包含了完整的數(shù)據(jù)記錄
- 4. InnoDB表必須要有主鍵,并且推薦使用整型自增主鍵
正如我們上面介紹InnoDB存儲結構,索引與數(shù)據(jù)是共同存儲的,不管是主鍵索引還是輔助索引,在查找時都是通過先查找到索引節(jié)點才能拿到相對應的數(shù)據(jù),如果我們在設計表結構時沒有顯式指定索引列的話,MySQL會從表中選擇數(shù)據(jù)不重復的列建立索引,如果沒有符合的列,則MySQL自動為InnoDB表生成一個隱含字段作為主鍵,并且這個字段長度為6個字節(jié),類型為整型。
那為什么推薦使用整型自增主鍵而不是選擇UUID?
UUID是字符串,比整型消耗更多的存儲空間;
在B+樹中進行查找時需要跟經(jīng)過的節(jié)點值比較大小,整型數(shù)據(jù)的比較運算比字符串更快速;
自增的整型索引在磁盤中會連續(xù)存儲,在讀取一頁數(shù)據(jù)時也是連續(xù);UUID是隨機產(chǎn)生的,讀取的上下兩行數(shù)據(jù)存儲是分散的,不適合執(zhí)行where id > 5 && id < 20的條件查詢語句。
在插入或刪除數(shù)據(jù)時,整型自增主鍵會在葉子結點的末尾建立新的葉子節(jié)點,不會破壞左側子樹的結構;UUID主鍵很容易出現(xiàn)這樣的情況,B+樹為了維持自身的特性,有可能會進行結構的重構,消耗更多的時間。
為什么非主鍵索引結構葉子節(jié)點存儲的是主鍵值?
保證數(shù)據(jù)一致性和節(jié)省存儲空間,可以這么理解:商城系統(tǒng)訂單表會存儲一個用戶ID作為關聯(lián)外鍵,而不推薦存儲完整的用戶信息,因為當我們用戶表中的信息(真實名稱、手機號、收貨地址···)修改后,不需要再次維護訂單表的用戶數(shù)據(jù),同時也節(jié)省了存儲空間。
總結
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
本文給大家分享的是作者在使用mysql進行千萬級數(shù)據(jù)量分頁查詢的時候進行性能優(yōu)化的方法,非常不錯的一篇文章,對我們學習mysql性能優(yōu)化非常有幫助2017-11-11解決Navicat Premium 連接 MySQL 8.0 報錯"1251"的問題分析
這篇文章主要介紹了解決Navicat Premium 連接 MySQL 8.0 報錯"1251"的問題分析,本文通過圖文并茂的形式給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-11-11mysql一次將多條不同sql查詢結果并封裝到一個結果集的實現(xiàn)方法
這篇文章主要給大家介紹了關于mysql一次將多條不同sql查詢結果并封裝到一個結果集的實現(xiàn)方法,文中通過實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2023-03-03