# 前言
數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據。不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎，還可以獲得特定的功能。現在許多不同的數據庫管理系統都支持多種不同的數據引擎。**MySQL的核心就是存儲引擎。**
開發中常用的數據引擎包括MyISMA、InnoDB、MEMORY、MERGE.
1.MyISMA為mysql默認的插件式存儲引擎，如果應用是讀取跟插入為主，只有很少的更新和刪除，并且對事物的完整性、并發性要求不高。
2.InnoDB應用于事務處理應用支持外鍵，如果應用對完整性要求比較高，并發情況下要求數據的一致性，數據的操作包括CRUD，該引擎就非常適合。InnoDB存儲除了有效減低由刪除和更新導致的鎖定，還保證了事務的完整提交和回滾。
3.MEMORY將所有的數據保存在內存中，在需要定位和其他類似的操作中可提高極快的訪問速度；缺陷是對表的大小有限制，表太大無法緩存到內存，其實要保證表的可恢復性。
4.MERGE用于將一系列MyISMA引擎的表以結構相同的方式組合在一起，并可以對其進行引用。優點是在于突破單個MyISMA表大小的限制。
# 存儲引擎的概述
## 為什么要合理選擇數據庫存儲引擎：
MySQL中的數據用各種不同的技術存儲在文件（或者內存）中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。
這些不同的技術以及配套的相關功能在MySQL中被稱作存儲引擎(也稱作表類型)。MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。你可以選擇適用于服務器、數據庫和表格的存儲引擎，以便在選擇如何存儲你的信息、如何檢索這些信息以及你需要你的數據結合什么性能和功能的時候為你提供最大的靈活性。
## 定義
數據庫引擎是用于存儲、處理和保護數據的核心服務。利用數據庫引擎可控制訪問權限并快速處理事務，從而滿足企業內大多數需要處理大量數據的應用程序的要求。 使用數據庫引擎創建用于聯機事務處理或聯機分析處理數據的關系數據庫。這包括創建用于存儲數據的表和用于查看、管理和保護數據安全的數據庫對象（如索引、視圖和存儲過程）。
## 存儲引擎作用
1）設計并創建數據庫以保存系統所需的關系或XML文檔。
2）實現系統以訪問和更改數據庫中存儲的數據。包括實現網站或使用數據的應用程序，還包括生成使用SQL Server工具和實用工具以使用數據的過程。
3）為單位或客戶部署實現的系統。
4）提供日常管理支持以優化數據庫的性能。
## 如何修改數據庫引擎
方式一：
修改配置文件my.ini
將mysql.ini另存為my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重啟服務，數據庫默認的引擎修改為InnoDB
方式二：
在建表的時候指定
```
create table mytbl(
id int primary key,
name varchar(50)
)type=MyISAM;
```
方式三：
建表后更改
```
alter table table_name engine = InnoDB;
```
## 怎么查看修改成功？
方式一：
```
show table status from database_name;
```
方式二：
```
show create table table_name
```
方式三：
使用數據庫管理工具
# 分析使用MyIsam 和InnoDB
定義：（默認的存儲引擎）
InnoDB是一個事務型的存儲引擎，有行級鎖定和外鍵約束。
Innodb引擎提供了對數據庫ACID事務的支持，并且實現了SQL標準的四種隔離級別，關于數據庫事務與其隔離級別的內容請見數據庫事務與其隔離級別這類型的文章。該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量數據庫系統，它本身其實就是基于MySQL后臺的完整數據庫系統，MySQL運行時Innodb會在內存中建立緩沖池，用于緩沖數據和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數，當SELECT COUNT(*) FROM TABLE時需要掃描全表。當需要使用數據庫事務時，該引擎當然是首選。由于鎖的粒度更小，寫操作不會鎖定全表，所以在并發較高時，使用Innodb引擎會提升效率。但是使用行級鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
```
//這個就是select鎖表的一種，不明確主鍵。增刪改查都可能會導致鎖全表，在以后我們會詳細列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
```
適用場景：
1）經常更新的表，適合處理多重并發的更新請求。
2）支持事務。
3）可以從災難中恢復（通過bin-log日志等）。
4）外鍵約束。只有他支持外鍵。
5）支持自動增加列屬性auto_increment。
MySQL官方對InnoDB的講解：
1）InnoDB給MySQL提供了具有提交、回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。
2）InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風格一致的非鎖定讀，這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。
3）InnoDB也支持FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。
4）InnoDB是為處理巨大數據量時的最大性能設計，它的CPU效率可能是任何其它基于磁盤的關系數據庫引擎所不能匹敵的。
5） InnoDB被用來在眾多需要高性能的大型數據庫站點上產生。
補充：什么叫事務？簡稱ACID
A 事務的原子性(Atomicity)：指一個事務要么全部執行,要么不執行.也就是說一個事務不可能只執行了一半就停止了.比如你從取款機取錢,這個事務可以分成兩個步驟:1劃卡,2出錢.不可能劃了卡,而錢卻沒出來.這兩步必須同時完成.要么就不完成.
C 事務的一致性(Consistency)：指事務的運行并不改變數據庫中數據的一致性.例如,完整性約束了a+b=10,一個事務改變了a,那么b也應該隨之改變.
I 獨立性(Isolation）:事務的獨立性也有稱作隔離性,是指兩個以上的事務不會出現交錯執行的狀態.因為這樣可能會導致數據不一致.
D 持久性(Durability）:事務的持久性是指事務執行成功以后,該事務所對數據庫所作的更改便是持久的保存在數據庫之中，不會無緣無故的回滾.
# MyIsam
定義：
MyIASM是MySQL默認的引擎，但是它沒有提供對數據庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數據時即寫操作需要鎖定整個表，效率便會低一些。
MyIsam 存儲引擎獨立于操作系統，也就是可以在windows上使用，也可以比較簡單的將數據轉移到linux操作系統上去。
意味著：引擎在創建表的時候，會創建三個文件，一個是.frm文件用于存儲表的定義，一個是.MYD文件用于存儲表的數據，另一個是.MYI文件，存儲的是索引。操作系統對大文件的操作是比較慢的，這樣將表分為三個文件，那么.MYD這個文件單獨來存放數據自然可以優化數據庫的查詢等操作。有索引管理和字段管理。MyISAM還使用一種表格鎖定的機制，來優化多個并發的讀寫操作，其代價是你需要經常運行OPTIMIZE TABLE命令，來恢復被更新機制所浪費的空間。
適用場景：
1）不支持事務的設計，但是并不代表著有事務操作的項目不能用MyIsam存儲引擎，可以在service層進行根據自己的業務需求進行相應的控制。
2）不支持外鍵的表設計。
3）查詢速度很快，如果數據庫insert和update的操作比較多的話比較適用。
4）整天 對表進行加鎖的場景。
5）MyISAM極度強調快速讀取操作。
6）MyIASM中存儲了表的行數，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多于寫操作且不需要數據庫事務的支持，那么MyIASM也是很好的選擇。
缺點：
就是不能在表損壞后恢復數據。（是不能主動恢復）
補充：ISAM索引方法–索引順序存取方法
定義：
是一個定義明確且歷經時間考驗的數據表格管理方法，它在設計之時就考慮到 數據庫被查詢的次數要遠大于更新的次數。
特性：
ISAM執行讀取操作的速度很快，而且不占用大量的內存和存儲資源。
在設計之初就預想數據組織成有固定長度的記錄，按順序存儲的。—ISAM是一種靜態索引結構。
缺點：
1.它不 支持事務處理
2.也不能夠容錯。如果你的硬盤崩潰了，那么數據文件就無法恢復了。如果你正在把ISAM用在關鍵任務應用程序里，那就必須經常備份你所有的實 時數據，通過其復制特性，MYSQL能夠支持這樣的備份應用程序。
# InnoDB和MyIsam使用及其原理對比
## 在一個普通數據庫中創建兩張分別以MyIsam和InnoDB作為存儲引擎的表。
```
create table testMyIsam(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=myisam;
create table testInnoDB(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=innodb;
```
## 對比插入效率（百萬級插入）：（雖然速度上MyISAM快，但是增刪改是涉及事務安全的，所以用InnoDB相對好很多）
```
//創建存儲過程
delimiter $$
drop procedure if exists ptestmyisam;
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testmyisam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存儲過程：
call ptestmyisam();
```
```
//創建存儲過程（盡量把Innodb的數量級壓低，不然，，卡在那里半天也不奇怪）
delimiter $$
drop procedure if exists ptestInndb;
create procedure ptestInndb()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testinnodb(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存儲過程：
call ptestInndb();
```
當然innodb默認是開啟事務的，如果我們把事務給停了，會快很多。
```
//停掉事務
set autocommit = 0;
//調用存儲過程
call ptestInndb;
//重啟事務
set autocommit = 1;
```
## 對比更新：（雖然速度上MyISAM快，但是增刪改是涉及事務安全的，所以InnoDB相對好很多）
```
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
update testmyisam set name = 'fuzhu' where id>0 and id<13525;
```
## 查詢對比：
1）查詢總數目
```
select count(*) from testInnoDB;
select count(*) from testMyIsam;
```
2）查詢無索引的列：
```
select * from testMyIsam where name > "fuzhu100" ;
select * from testInnoDB where name > "fuzhu100" ;
```
3）查詢有索引的列：
```
select * from testMyIsam where id > 10 ;
select * from testinnodb where id > 10 ;
```
4）存儲大小
# 效果對比總述：
1）事務。MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持，提供事務支持已經外部鍵等高級數據庫功能。
InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如updatetable set num=1 where name like “a%”
就是說在不確定的范圍時，InnoDB還是會鎖表的。
2）性能主題。MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快。
3）行數保存。InnoDB 中不保存表的具體行數，也就是說，執行select count() fromtable時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count()語句包含where條件時，兩種表的操作是一樣的。
4）索引存儲。對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引。
MyISAM支持全文索引（FULLTEXT）、壓縮索引，InnoDB不支持
MyISAM的索引和數據是分開的，并且索引是有壓縮的，內存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。
InnoDB存儲引擎被完全與MySQL服務器整合，InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩沖池。InnoDB存儲它的表＆索引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。
5）服務器數據備份。InnoDB必須導出SQL來備份，LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用。
而且MyISAM應對錯誤編碼導致的數據恢復速度快。MyISAM的數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。
InnoDB是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十G的時候就相對痛苦了。
**6）鎖的支持。**MyISAM只支持表鎖。InnoDB支持表鎖、行鎖 行鎖大幅度提高了多用戶并發操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的
# 使用建議：
以下兩點必須使用 InnoDB：
1）可靠性高或者要求事務處理，則使用InnoDB。這個是必須的。
2）表更新和查詢都相當的頻繁，并且表鎖定的機會比較大的情況指定InnoDB數據引擎的創建。
對比之下，MyISAM的使用場景：
1）做很多count的計算的。如一些日志，調查的業務表。
2）插入修改不頻繁，查詢非常頻繁的。
MySQL能夠允許你在表這一層應用數據庫引擎，所以你可以只對需要事務處理的表格來進行性能優化，而把不需要事務處理的表格交給更加輕便的MyISAM引擎。對于 MySQL而言，靈活性才是關鍵。
