引擎介紹

MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件（或者內(nèi)存）中含末。這些技術(shù)中的每一種技術(shù)都使用不同的存儲機制、索引技巧胸懈、鎖定水平并且最終提供廣泛的不同的功能和能力垄开。通過選擇不同的技術(shù)，你能夠獲得額外的速度或者功能聋涨，從而改善你的應用的整體功能晾浴。

這些不同的技術(shù)以及配套的相關(guān)功能在MySQL中被稱作存儲引擎(也稱作表類型)。MySQL默認配置了許多不同的存儲引擎牍白，可以預先設置或者在MySQL服務器中啟用脊凰。你可以選擇適用于服務器、數(shù)據(jù)庫和表格的存儲引擎茂腥，以便在選擇如何存儲你的信息狸涌、如何檢索這些信息以及你需要你的數(shù)據(jù)結(jié)合什么性能和功能的時候為你提供最大的靈活性。

引擎定義

數(shù)據(jù)庫引擎是用于存儲最岗、處理和保護數(shù)據(jù)的核心服務帕胆。利用數(shù)據(jù)庫引擎可控制訪問權(quán)限并快速處理事務，從而滿足企業(yè)內(nèi)大多數(shù)需要處理大量數(shù)據(jù)的應用程序的要求般渡。 使用數(shù)據(jù)庫引擎創(chuàng)建用于聯(lián)機事務處理或聯(lián)機分析處理數(shù)據(jù)的關(guān)系數(shù)據(jù)庫懒豹。這包括創(chuàng)建用于存儲數(shù)據(jù)的表和用于查看、管理和保護數(shù)據(jù)安全的數(shù)據(jù)庫對象（如索引驯用、視圖和存儲過程）脸秽。

引擎作用

1. 設計并創(chuàng)建數(shù)據(jù)庫以保存系統(tǒng)所需的關(guān)系或XML文檔。

2. 現(xiàn)系統(tǒng)以訪問和更改數(shù)據(jù)庫中存儲的數(shù)據(jù)蝴乔。包括實現(xiàn)網(wǎng)站或使用數(shù)據(jù)的應用程序记餐，還包括生成使用SQL Server工具和實用工具以使用數(shù)據(jù)的過程。

3. 為單位或客戶部署實現(xiàn)的系統(tǒng)淘这。

4. 提供日常管理支持以優(yōu)化數(shù)據(jù)庫的性能剥扣。

引擎種類

存儲引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam。在實際工作中用的比較多的是MyIsam 和InnoDB铝穷，而InnoDB更因為各方面的優(yōu)越性更是獲得了更多的青睞钠怯。尤其是MySQL 8.0 版本發(fā)布后，其中具有重大意義的是官方廢棄了MyISAM存儲引擎曙聂。

參考：https://www.cnblogs.com/sunsky303/p/8274586.html

查看當前數(shù)據(jù)庫支持的引擎可以用一下語句：

show ENGINES;

image.png

修改數(shù)據(jù)庫引擎

方式一 修改配置文件my.ini：

在[mysqld]后面添加default-storage-engine=InnoDB晦炊，重啟服務，數(shù)據(jù)庫默認的引擎修改為InnoDB。

方式二 在建表的時候指定

create table mytbl(   

    id int primary key,   

    name varchar(50)   

)ENGINE=MyISAM;

方式三 建表后更改

alter table person2 ENGINE =InnoDB;

查看當前數(shù)據(jù)庫引擎

方式一

show table status from table_name;

方式二

show create table table_name

方式三

使用第三方數(shù)據(jù)庫管理工具断国。

InnoDB

定義

InnoDB是一個事務型的存儲引擎贤姆，有行級鎖定和外鍵約束。

Innodb引擎提供了對數(shù)據(jù)庫ACID事務（原子性Atomicity稳衬、一致性Consistency霞捡、隔離性Isolation、持久性Durability）的支持薄疚，并且實現(xiàn)了SQL標準的四種隔離級別碧信，關(guān)于數(shù)據(jù)庫事務與其隔離級別的內(nèi)容請見數(shù)據(jù)庫事務與其隔離級別這類型的文章。該引擎還提供了行級鎖和外鍵約束街夭，它的設計目標是處理大容量數(shù)據(jù)庫系統(tǒng)砰碴，它本身其實就是基于MySQL后臺的完整數(shù)據(jù)庫系統(tǒng)，MySQL運行時Innodb會在內(nèi)存中建立緩沖池板丽，用于緩沖數(shù)據(jù)和索引呈枉。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數(shù)埃碱，當SELECT COUNT(*) FROM TABLE時需要掃描全表猖辫。當需要使用數(shù)據(jù)庫事務時，該引擎當然是首選砚殿。由于鎖的粒度更小住册，寫操作不會鎖定全表，所以在并發(fā)較高時瓮具，使用Innodb引擎會提升效率。但是使用行級鎖也不是絕對的凡人，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍名党，InnoDB表同樣會鎖全表。

-- 這個就是select鎖表的一種挠轴，不明確主鍵传睹。增刪改查都可能會導致鎖全表。

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

適用場景

1. 經(jīng)常更新的表岸晦，適合處理多重并發(fā)的更新請求欧啤。
2. 支持事務。
3. 可以從災難中恢復（通過bin-log日志等）启上。
4. 外鍵約束邢隧。只有他支持外鍵。
5. 支持自動增加列屬性auto_increment冈在。

官方對InnoDB的講解

1. InnoDB給MySQL提供了具有提交倒慧、回滾和崩潰恢復能力的事務安全（ACID兼容）存儲引擎。
2. InnoDB鎖定在行級并且也在SELECT語句提供一個Oracle風格一致的非鎖定讀，這些特色增加了多用戶部署和性能纫谅。沒有在InnoDB中擴大鎖定的需要炫贤，因為在InnoDB中行級鎖定適合非常小的空間。
3. InnoDB也支持FOREIGN KEY強制付秕。在SQL查詢中兰珍，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合询吴。
4. InnoDB是為處理巨大數(shù)據(jù)量時的最大性能設計掠河，它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的。
5. InnoDB被用來在眾多需要高性能的大型數(shù)據(jù)庫站點上產(chǎn)生汰寓。

MyIsam

定義

MyIASM沒有提供對數(shù)據(jù)庫事務的支持口柳，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數(shù)據(jù)時即寫操作需要鎖定整個表有滑，效率便會低一些跃闹。

MyIsam 存儲引擎獨立于操作系統(tǒng)，也就是可以在windows上使用毛好，也可以比較簡單的將數(shù)據(jù)轉(zhuǎn)移到linux操作系統(tǒng)上去望艺。

意味著：引擎在創(chuàng)建表的時候，會創(chuàng)建三個文件肌访，一個是.frm文件用于存儲表的定義找默，一個是.MYD文件用于存儲表的數(shù)據(jù)，另一個是.MYI文件吼驶，存儲的是索引惩激。操作系統(tǒng)對大文件的操作是比較慢的，這樣將表分為三個文件蟹演，那么.MYD這個文件單獨來存放數(shù)據(jù)自然可以優(yōu)化數(shù)據(jù)庫的查詢等操作风钻。有索引管理和字段管理。MyISAM還使用一種表格鎖定的機制酒请，來優(yōu)化多個并發(fā)的讀寫操作骡技，其代價是你需要經(jīng)常運行OPTIMIZE TABLE命令，來恢復被更新機制所浪費的空間羞反。

適用場景

1. 不支持事務的設計布朦，但是并不代表著有事務操作的項目不能用MyIsam存儲引擎，可以在service層進行根據(jù)自己的業(yè)務需求進行相應的控制昼窗。
2. 不支持外鍵的表設計是趴。
3. 查詢速度很快，如果數(shù)據(jù)庫insert和update的操作比較多的話比較適用膏秫。
4. 整天 對表進行加鎖的場景右遭。
5. MyISAM極度強調(diào)快速讀取操作做盅。
6. MyIASM中存儲了表的行數(shù)，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經(jīng)保存好的值而不需要進行全表掃描窘哈。如果表的讀操作遠遠多于寫操作且不需要數(shù)據(jù)庫事務的支持吹榴，那么MyIASM也是很好的選擇。

特性

ISAM執(zhí)行讀取操作的速度很快滚婉，而且不占用大量的內(nèi)存和存儲資源图筹。

在設計之初就預想數(shù)據(jù)組織成有固定長度的記錄，按順序存儲的让腹。ISAM是一種靜態(tài)索引結(jié)構(gòu)远剩。

缺點

1. 它不支持事務處理

2. 也不能夠容錯。如果你的硬盤崩潰了骇窍，那么數(shù)據(jù)文件就無法恢復了瓜晤。如果你正在把ISAM用在關(guān)鍵任務應用程序里，那就必須經(jīng)常備份你所有的實 時數(shù)據(jù)腹纳，通過其復制特性痢掠，MYSQL能夠支持這樣的備份應用程序。

Memory（也叫HEAP）堆內(nèi)存

定義

使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表嘲恍。每個MEMORY表只實際對應一個磁盤文件足画。MEMORY類型的表訪問非常得快，因為它的數(shù)據(jù)是放在內(nèi)存中的佃牛，并且默認使用HASH索引淹辞。

但是一旦服務關(guān)閉，表中的數(shù)據(jù)就會丟失掉俘侠。 HEAP允許只駐留在內(nèi)存里的臨時表格象缀。駐留在內(nèi)存里讓HEAP要比ISAM和MYISAM都快，但是它所管理的數(shù)據(jù)是不穩(wěn)定的爷速，而且如果在關(guān)機之前沒有進行保存攻冷，那么所有的數(shù)據(jù)都會丟失。在數(shù)據(jù)行被刪除的時候遍希，HEAP也不會浪費大量的空間。HEAP表格在你需要使用SELECT表達式來選擇和操控數(shù)據(jù)的時候非常有用里烦。

適用場景

1. 那些內(nèi)容變化不頻繁的代碼表凿蒜，或者作為統(tǒng)計操作的中間結(jié)果表，便于高效地堆中間結(jié)果進行分析并得到最終的統(tǒng)計結(jié)果胁黑。
2. 目標數(shù)據(jù)比較小废封，而且非常頻繁的進行訪問，在內(nèi)存中存放數(shù)據(jù)丧蘸，如果太大的數(shù)據(jù)會造成內(nèi)存溢出漂洋。可以通過參數(shù)max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小刽漂。
3. 數(shù)據(jù)是臨時的演训，而且必須立即可用得到，那么就可以放在內(nèi)存中贝咙。
4. 存儲在Memory表中的數(shù)據(jù)如果突然間丟失的話也沒有太大的關(guān)系样悟。

注意： Memory同時支持散列索引和B樹索引，B樹索引可以使用部分查詢和通配查詢庭猩，也可以使用<,>和>=等操作符方便數(shù)據(jù)挖掘窟她，散列索引相等的比較快但是對于范圍的比較慢很多。

特性要求

1. 要求存儲的數(shù)據(jù)是數(shù)據(jù)長度不變的格式蔼水，比如震糖，Blob和Text類型的數(shù)據(jù)不可用（長度不固定的）。
2. 要記住趴腋，在用完表格之后就刪除表格吊说。

Mrg_Myisam（分表的一種方式–水平分表）

定義

是一個相同的可以被當作一個來用的MyISAM表的集合∮谘“相同”意味著所有表同樣的列和索引信息疏叨。

也就是說，他將MyIsam引擎的多個表聚合起來穿剖，但是他的內(nèi)部沒有數(shù)據(jù)蚤蔓，真正的數(shù)據(jù)依然是MyIsam引擎的表中，但是可以直接進行查詢糊余、刪除更新等操作秀又。

比如：我們可能會遇到這樣的問題，同一種類的數(shù)據(jù)會根據(jù)數(shù)據(jù)的時間分為多個表贬芥，如果這時候進行查詢的話吐辙，就會比較麻煩，Merge可以直接將多個表聚合成一個表統(tǒng)一查詢蘸劈，然后再刪除Merge表（刪除的是定義）昏苏，原來的數(shù)據(jù)不會影響。

Blackhole（黑洞引擎）

定義

任何寫入到此引擎的數(shù)據(jù)均會被丟棄掉威沫， 不做實際存儲贤惯；Select語句的內(nèi)容永遠是空。他會丟棄所有的插入的數(shù)據(jù)，服務器會記錄下Blackhole表的日志，所以可以用于復制數(shù)據(jù)到備份數(shù)據(jù)庫瘤旨。

使用場景

1. 驗證dump file語法的正確性

2. 以使用blackhole引擎來檢測binlog功能所需要的額外負載

CREATE TABLE `Blackhole` (

  `id` bigint(20) unsigned NOT NULL,

  `fname` varchar(100) NOT NULL,

  `lname` varchar(100) NOT NULL,

  `age` tinyint(3) unsigned NOT NULL,

  `sex` tinyint(1) unsigned NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=Blackhole DEFAULT CHARSET=utf8

Innodb對比myIsam

事務

MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持颈墅，提供事務支持已經(jīng)外部鍵等高級數(shù)據(jù)庫功能蜡镶。

InnoDB表的行鎖也不是絕對的，假如在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍恤筛，InnoDB表同樣會鎖全表官还，例如updatetable set num=1 where name like “a%”，就是說在不確定的范圍時叹俏，InnoDB還是會鎖表的妻枕。

性能

以前版本中MyISAM類型的表強調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快粘驰。但現(xiàn)在InnoDB在多方面的性能已經(jīng)趕上活超過了MyIsam屡谐。

行數(shù)保存

InnoDB 中不保存表的具體行數(shù)，也就是說蝌数，執(zhí)行select count() fromtable時愕掏，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可顶伞。注意的是饵撑，當count()語句包含where條件時，兩種表的操作是一樣的唆貌。

索引存儲

對于AUTO_INCREMENT類型的字段滑潘，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中锨咙，可以和其他字段一起建立聯(lián)合索引语卤。

MyISAM支持全文索引（FULLTEXT）、壓縮索引酪刀，InnoDB不支持

MyISAM的索引和數(shù)據(jù)是分開的粹舵，并且索引是有壓縮的，內(nèi)存使用率就對應提高了不少骂倘。能加載更多索引眼滤，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小历涝。

InnoDB存儲引擎被完全與MySQL服務器整合诅需，InnoDB存儲引擎為在主內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池。InnoDB存儲它的表＆索引在一個表空間中荧库，表空間可以包含數(shù)個文件（或原始磁盤分區(qū)）诱担。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中电爹。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上料睛。

服務器數(shù)據(jù)備份

InnoDB必須導出SQL來備份丐箩，LOAD TABLE FROM MASTER操作對InnoDB是不起作用的摇邦，解決方法是首先把InnoDB表改成MyISAM表，導入數(shù)據(jù)后再改成InnoDB表屎勘，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用施籍。

而且MyISAM應對錯誤編碼導致的數(shù)據(jù)恢復速度快。MyISAM的數(shù)據(jù)是以文件的形式存儲概漱，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便丑慎。在備份和恢復時可單獨針對某個表進行操作。

InnoDB是拷貝數(shù)據(jù)文件瓤摧、備份 binlog竿裂，或者用 mysqldump，在數(shù)據(jù)量達到幾十G的時候就相對痛苦了照弥。

鎖的支持

MyISAM只支持表鎖腻异。InnoDB支持表鎖、行鎖 行鎖大幅度提高了多用戶并發(fā)操作的新能这揣。但是InnoDB的行鎖悔常，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的给赞。

使用建議

以下兩點必須使用 InnoDB：

1. 可靠性高或者要求事務處理机打，則使用InnoDB。這個是必須的片迅。

2. 表更新和查詢都相當?shù)念l繁残邀，并且表鎖定的機會比較大的情況指定InnoDB數(shù)據(jù)引擎的創(chuàng)建。

對比之下障涯，MyISAM的使用場景：

1. 做很多count的計算的罐旗。如一些日志，調(diào)查的業(yè)務表唯蝶。

2. 插入修改不頻繁九秀，查詢非常頻繁的。

MySQL能夠允許你在表這一層應用數(shù)據(jù)庫引擎粘我，所以你可以只對需要事務處理的表格來進行性能優(yōu)化鼓蜒，而把不需要事務處理的表格交給更加輕便的MyISAM引擎。對于 MySQL而言征字，靈活性才是關(guān)鍵都弹。