一 . 前言
總是看到有說MySQL某個資料引擎哪裡哪裡有問題的 , 讓我一度懷疑我是不是Mysql 用的不對 , 隻會用InnoDB 建表 . 看 MyISAM 的表也沒超過10次.
是以我非常好奇 , 資料庫的存儲引擎 , 大家真的在用嗎?
MySQL 官方文檔:: MySQL 5.7 Reference Manual :: 15 Alternative Storage Engines
二. 涉及的引擎
2.1 不同存儲引擎的特性
修改引擎的相關指令
// 檢視 MySQL 版本 (不同版本對于存儲引擎的支援不一樣)
SELECT VERSION();
// 檢視支援的引擎
show engines;
// 修改引擎類型
alter table table_name engine=innodb; 三. 引擎的選擇
了解存儲引擎的差別隻是一方面 , 更重要的是在何種場景下應該選擇哪些存儲引擎. 存儲引擎使用最多的應該是四種 : InnoDB , MyISAM , Archive , Memory .
但是實際上真正可能用的多的 , 隻會是 InnoDB 和 MyISAM
3.1 引擎的選擇
MySQL 各個存儲引擎的使用空間可以說越來越小了, 比如 Memory , 鎖是表鎖 , 又不能持久化資料. 也許業務場景中唯一的作用就是節約成本 , 不用搭一套Redis .
但是都上雲了 , MySQL 伺服器記憶體的開銷 , 相對價格也不會便宜到哪去.
而針對于 csv 引擎 , 其實就是存儲CSV檔案, 那麼搞一套OSS系統存這些可能更便宜.
而更有價值的可能就是各種圖引擎或者特殊功能的引擎 , 但是往往都會有更好的替代品.
對于這些引擎總結起來就一句話 : 除非需要用到某些InnoDB 不具備的特性，并且沒有其他辦 怯可以替代，否則都應該優先選擇Inno DB 引擎 <高性能MySQL>
那麼剩下的 , 就是 InnoDB 和 MylSAM 了~~~
3.2 InnoDB 和 MylSAM 的選擇
MySQL 的引擎太多了, 用的最多的應該就是 InnoDB 和 MylSAM, 這也是涉及篇幅最大的兩個資料引擎.
兩個存儲引擎的差別很多 , 但是正在的核心集中在以下幾個方面 :
是否支援事務
MyISAM 本身是不支援事務的 ,
事務就很重要了 , 如果業務線比較長 ,而且強制要求資料完整一緻. InnoDB.
如果本身就是單次操作或者可以通過 TCC 的方式進行復原 , 那麼選擇 MyISAM 也不會對業務産生什麼影響.
MVCC
MyISAM 是不支援 MVCC 的 , MVCC 是 多版本并發控制 , 如果對事務接觸比較深 , 就會了解到四種隔離級别. 而 MySQL 的事務隔離級别就是基于 MVCC 進行實作.
外鍵
外鍵通常用來保證資料的完整性 ,A表的字段是B表的主鍵，就可以為該字段設定外鍵限制。 InnoDB 支援外鍵而 MyISAM 不支援.
外鍵其實很讨厭 , 删除資料時如果有外鍵關聯 , 會導緻資料删除不掉,處理起來也會非常複雜. 同時由于強調多表關聯 , 也會導緻性能較差
但是外鍵好處也很強大 , 保證了資料的一緻和完整. 再也不怕使用者未删除 ,而屬性被删除的問題.
不過個人接觸的幾個系統裡面就一個使用了外鍵 ,是以這個特性不能影響到引擎的選擇~~
鎖粒度
鎖粒度通常是指行鎖和表鎖. InnoDB 鎖粒度為行鎖 ,而 MyISAM 為表鎖.
行鎖和表鎖不能一概而論 :
- 表鎖面積更大 , 但是申請鎖隻需要進行一次 ,單次鎖的處理時間更短
- 行鎖面積小 , 但是需要申請多次鎖 , 單次處理的時間更長
四 . 引擎的對比
了解理論還是不夠 , 是以期望能從資料上看一看 , 兩個存儲引擎到底差了多少 . 實際業務中有沒有必要使用特殊的引擎建表
4.1 InnoDB 與 MylSAM 的性能差距
MyISAM 更快的原因
// 索引不同 ：
- MyISAM 隻有三個檔案：索引檔案，表結構檔案，資料檔案
- InnerDB 基于 BTree 的索引方法
> 宏觀 : MyISAM 直接加載索引檔案到記憶體中，由于索引檔案的資料量更少，相同時間加載的索引資料就更多
> 微觀 ：InnerDB 尋址需要查詢到塊,再在塊中找到對應的行,但是MyISAM查詢資料很快，直接辨別的OFFSET位址
// 事務 ：
- InnerDB 處理時需要維護事務,越複雜的場景這種損耗就會越高
// 緩存機制的不同 ：
- MyISAM 使用 KeyCache ，将頻繁通路的索引塊直接放到記憶體中
- InnerDB 使用 BufferPool ，會緩存資料和索引
> 相對而言MyISAM能夠緩存的資料更多，實際操作也能發現，第二次查詢InnerDB需要 10 秒，而MyISAM隻用 1 秒 4.2 了解事務
上面列舉了一堆事務和性能什麼什麼的,但是不論用什麼引擎，都需要深入了解性能和事務的直接聯系。
首先事務的原理是什麼 ， 從單機事務的角度來說 ，事務主要是基于MySQL來實作 ，簡單來說就是從 START TRANSACTION 來開啟事務 ，到 COMMIT 結束事務 。
而看似簡單的兩個步驟 ，整個在InnerDB中的執行過程是很長的。
以 InnerDB 為例 ， 該引擎為事務提供了四種隔離級别 ： 讀未送出 ， 讀已送出 ， 可重複讀 ， 可串行化。 為了實作這四種隔離級别 ，InnerDB 對資料進行了加鎖 ， MVCC ， lock in share mode 等操作 。
就光加鎖這一項,就限制了并發的能力。而MVCC機制帶來的快照，同樣會對性能有所影響。其他的引擎同理
// PS ： 性能名額是絕對的嗎？ （100萬資料）
- READ_UNCOMMITTED ： 讀未送出 >> 58 秒
- READ_COMMITTED ： 讀已送出 >> 55 秒
- REPEATABLE_READ ： 可重複讀 >> 35 秒 很奇怪不是 ， 為什麼我們看到的越嚴格的隔離級别反而少了時間 :
- 因為性能和鎖挂鈎 ，而鎖往往隻會在複雜的業務場景中才會有沖突，才會存在并發的問題，我這裡就是單純的寫，本身并不會出現什麼鎖的競争，也就沒什麼事務的問題。
4.3 了解外鍵
InnerDB 的外鍵這東西,用好了妥妥的可靠性小助手,再也不怕某個資料不存在而作一堆查詢和判斷。資料的完備性大大的提升。
但是這功能沒用好，簡直能把人惡心死。外鍵的設計，是需要對系統有很深的了解的，哪些資料應該綁定，哪些資料不應該建立耦合關系很重要。
但是現在的系統設計思路，會考慮邏輯删除資料，這個時候外鍵的作用就很小了。
而外鍵同樣是會對性能帶來損耗的 ：
外鍵的目的在于綁定多張表之間的關系，外鍵在進行子表的寫入操作時，是會對主表加共享鎖，而一旦加了鎖，在并發的場景下，就會有性能的問題。
綜合考慮下 ： 外鍵的功能僅适用于特殊場景下的完整性限制，而在并發的需求下，能不用就不用吧
總結
本着學習的目的了解了這些 , 雖然MySQL 本身還支援很多種其他的引擎 , 但是最好還是不要嘗鮮. 用的時候一時爽 , 回頭上雲或者切庫的時候 , 那怕是後悔的不得了.
一般業務裡面其實基本上都會選用 InnoDB , 極個别場景會選用 MyISAM, 其實其他的引擎都可以用外部元件來解決. Redis ,MongoDB , ES 就是用來解決這些問題的 ， 是以已經沒有什麼理由非要用 MyISAM 了，而其他的引擎，基本上不需要在生産上實踐
而伴随着分庫分表的流行 , 加上很多公司會選擇把大資料的計算交給大資料部門來處理, 對 MySQL 的壓力是在逐漸變小的.
另外需要注意，性能這東西并不是絕對的，這篇文章不好實踐，也不好追代碼，都是看看别人的部落格，然後了解吸收後用自己的話表述出來，也不知道是不是真的~~~
附錄 : 各種存儲引擎
InnoDB :
- InnoDB 的資料存儲在表空間（ tablespace ）中
- InnoDB 采用MVCC 來支援高井發，并且實作了四個标準的隔離級别
- InnoDB 表是基于聚簇索引建立的
MylSAM
- MyISAM 提供了全文索引、壓縮、空間函數（ GIS ） 等，但My ISAM 不支援事務和行級
- MyISAM 會将表存儲在兩個檔案中： 資料檔案和索引檔案，分别以.MYD 和. MYI 為擴充名
- MyISAM 表可以包含動态或者靜态（長度固定）行。
- 可以使用myisa 1叩a c k 對My ISAM 表進行壓縮（也叫打包pack ）, 壓縮表是不能進行修 改的
Archive 引擎
- Archive 存儲引擎隻支援INSERT 和SELECT 操作 , Archive 引擎會緩存所有的寫并利用zlib 對插入的行進行壓縮 , 磁盤 IO 更少
- 表适合日志和資料采集類應用
- 支援行級鎖和專用的緩沖區，是以可以實作高并發的插入
Blackhole 引擎 (問題太多 , 不推薦)
- 會丢棄所有插入的資料，不做任何儲存。但是伺服器會記錄Blackhole 表的日志，是以可以用于複制資料到備庫，或者隻是簡單地記錄到日志
- 在一些特殊的複制架構和日志稽核時發揮作用。
csv 引擎
- 将普通的csv 檔案（逗号分割值的檔案）作為MySQL 的表來處理
- 不支援索引。csv 引擎可以在資料庫運作時拷入或者拷出檔案
- 将 Excel 等電子表格軟體中的資料存儲為csv 檔案，然後複制到MySQL 資料目錄下
Federated 引擎
- 代理引擎 : 會建立一個到遠端MySQL 伺服器的用戶端連接配接，井将查詢傳輸到遠端伺服器執行，然後提取或者發送需要的資料。
Memory 引擎
- 如果需要快速地通路資料，并且這些資料不會被修改，重新開機以後丢失也沒有關系，那麼使用Memory 表
- Memory 表至少比MyISAM 表要快一個數量級
- Memroy 表是表級鎖，是以并發寫入的性能較差
- 每行長度固定 , 部分記憶體的搜費
Merge 引擎
- Merge 表是由多個My ISAM 表合并而來的虛拟表
- 可以用于将MySQL 用于日志或者資料倉庫類應用
文章來源：https://juejin.cn/post/7173296499726483470