1、什麼是存儲引擎
通俗地講就是存儲和管理數據的方式
形象例子:
某處地方的自行車管理員:李某,張某。每天都有很多人來存取自行車。李某的管理方式是: 車主自行存放,李某也不記錄對方存的是什麼車。取車時則要交管理費。也不檢查取的是否是對方自己的車。相反,張某,則在車主存放車時,記錄存放人的特征以及對應車的信息。當人來取車時,張某還要小心核對,人與車的特征是否對應。
從上述例子可知:
a、從效率的角度來看: 李某比張某快,因為其不用進行校對
b、從安全性的角度來看: 張某比李某要安全,因為其進行了校對。
總結: 實際上,mysql的存儲引擎也是這樣工作,每種存儲引擎都有自身的存取方式,因此在選擇上,應該根據實際情況來定。
2、查看mysql所有引擎
mysql>show engines;
之所以要區分這兩種存儲引擎,是因為它們是目前使用最頻繁的mysql引擎,因此有必要對它們進行區分。有利於在實際情況下進行靈活運用。
1、myisam存儲引擎
a、表構成
建立表mytest
create table mytest(id int unsigned,content char(5))engine=myisam;
表對應磁盤的文件
總結:
a1、後綴名為:frm為表結構文件,MYD為數據文件,MYI為索引文件
a2、建立myisam引擎的表,都會建立這三個文件,在進行mysql數據庫備份或轉移時,方便操作
b、批量插入數據
插入1000行數據
c、查詢表的條數
總結: 從截圖可以看到mysiam有對表的行數進行記錄,而不是需要一條條去記錄
d、是否支持事務
總結: myisam不支持事務,因此如果你在數據庫進行事務操作,但是事務無法成功,你就要看你的表引擎了,看這種引擎是否支持事務。
e、對auto_increment列的操作(修改表mytest的id列)
插入數據
刪除id為3的數據,然後在插入數據
這時發現,刪除id為3後,繼續添加的數據,id並不是從3開始,而是跳過3,從4開始算起。
處理方案:
刪除數據後,重置auto_increment
總結: myisam引擎的表會對表中auto_increment進行存放,存放在文件磁盤上。即使是重啟服務,也不會丟失。
2、innodb存儲引擎
a、表結構
建立表myinnodb表
create table myinnodb(id int unsigned,content char(5))engine=innodb;
其中,frm為表結構文件,ibd為數據和索引文件
b、批量插入數據 (插入1000行數據)
c、查詢表總條數
總結: innodb引擎的表,在計算總行數時,是一條條算
d、是否支持事務
總結: innodb引擎表支持事務操作
e、對auto_increment列的操作(修改表myinnodb的id列)
插入數據
刪除id為3的數據
繼續插入數據
發現這種情況跟myisam引擎的一致。
處理方案:
總結: innodb可以對auto_increment進行重置,但其對auto_increnment列僅被存儲在主內存中,而不是存在磁盤上
重啟服務
3、myisam與innodb的區別
myisam
innodb
文件構成
frm、MYD、MYI
frm、ibd
是否支持事務
MyISAM類型的表強調的是性能,其執行數度比InnoDB類型更快,但是不提供事務支持
InnoDB提供事務支持事務,外部鍵等高級數據庫功能
計算表行數
select count(*) from table,MyISAM只要簡單的讀出保存好的行數,注意的是,當count(*)語句包含 where條件時,兩種表的操作是一樣的
InnoDB 中不保存表的具體行數,也就是說,執行select count(*) from table時,InnoDB要掃描一遍整個表來計算有多少行
鎖的粒度
表鎖
行鎖
對AUTO_INCREMENT的操作
存放在磁盤上,可以通過alter進行重置
僅被存儲在主內存中,而不是存在磁盤上
4、表鎖與行鎖的區別
在執行數據庫寫入的操作(insert,update,delete)的時候,mysiam表會鎖表,而innodb表會鎖行。通俗點說,就是你執行了一個update語句,那麼mysiam表會將整個表都鎖住,其他的insert和delete、update都會被拒之門外,等到這個update語句執行完成後才會被依次執行。
而鎖行,就是說,你執行update語句是,只會將這一條記錄進行鎖定,只有針對這條記錄的其他寫入、更新操作會被阻塞並等待這條update語句執行完畢後再執行,針對其他記錄的寫入操作不會有影響。
5、myisam與innodb的選擇
a、業務需要支持事務,選擇innodb
b、業務查詢居多,選擇myisam
6、總結
MyISAM管理非事務表。它提供高速存儲和檢索,以及全文搜索能力。如果應用中需要執行大量的SELECT查詢,那麼MyISAM是更好的選擇。
InnoDB用於事務處理應用程序,具有眾多特性,包括ACID事務支持。如果應用中需要執行大量的INSERT或UPDATE操作,則應該使用InnoDB,這樣可以提高多用戶並發操作的性能。
補充:
1、對於innodb的數據結構,首先要先理解兩個概念性的問題:共享表空間以及獨占表空間。
什麼是共享表空間和獨占表空間
共享表空間以及獨占表空間都是針對數據的存儲方式而言的。
共享表空間: 某一個數據庫的所有表數據,索引文件全部放在一個文件中,默認這個共享表空間的文件路徑在data目錄下。默認的文件名為:ibdata1初始化為10M。
獨占表空間: 每一張表都將會生成以獨立的文件方式來進行存儲,每一張表都有一個.frm表描述文件,還有一個.ibd文件。其中這個文件包括了單獨一個表的數據內容以及索引內容,默認情況下它的存儲位置也是在表的位置之中。