mysql存儲引擎

存儲引擎的基本介紹

在關系型資料庫裡面，資料是放在什麼結構裡面的？（放在表Table裡面的）

我們可以把這個表了解成Excel電子表格的形式。是以我們的表在存儲資料的同時，還要組織資料的存儲結構，這個存儲結構就是由我們的存儲引擎決定的，是以我們也可以把存儲引擎叫做表類型。

檢視存儲引擎

show table status from `xxxtable`
           

或者通過DDL建表語句來檢視

在Mysql裡面，我們建立的每一張表都可以指定它的存儲引擎，而不是一個資料庫隻能使用一個存儲引擎。存儲引擎的時候是以表為機關的。而且，建立表之後還可以修改存儲引擎。

我們說一張表使用的存儲引擎決定我們存儲資料的結構，那在伺服器上它們是怎麼存儲的呢？我們先要找到資料庫存放資料的路徑：

show variables like 'datadir';
           

預設情況下，每個資料庫有一個自己的檔案夾

不同的存儲引擎存放資料的方式不一樣，産生的檔案也不一樣，innodb是一個，memory沒有，myisam是兩個。

存儲引擎比較

常見的存儲引擎

MyISAM和InnoDB是我們用得最多的兩個存儲引擎，在Mysql5.5版本之前，預設的存儲引擎是MyISAM,它是MySQL自帶的。我們建立表的時候不指定存儲引擎，他就會使用MyISAM作為存儲引擎。

MYISAM的前身是ISAM（Indexed Sequential Access Method：利用索引，順序存取資料的方法）。

5.5版本之後預設的存儲引擎改成了InnoDB，它是第三方公司為MySQL開發的。為什麼要改呢？最主要的原因還是InnoDB支援事務，支援行級别的鎖，對于業務一緻性要求高的場景來說更适合。

這裡有個小故事可以聽下：

這個裡面又有 Oracle 和 MySQL 公司的一段恩怨情仇。 InnoDB 本來是 InnobaseOy 公司開發的，它和 MySQL AB 公司合作開源了 InnoDB 的代碼。但是沒想到 MySQL 的競争對手 Oracle 把 InnobaseOy 收購了。 後來 08 年 Sun 公司（開發 Java 語言的 Sun）收購了 MySQL AB，09 年 Sun 公司 又被 Oracle 收購了，是以 MySQL，InnoDB 又是一家了。有人覺得 MySQL 越來越像 Oracle，其實也是這個原因。

那麼除了這兩個我們最熟悉的存儲引擎，資料庫還支援其他

資料庫支援的存儲引擎

show engines ;  檢視資料庫支援的索引
           

其中有存儲引擎的描述和對事物，XA協定和Savepoints的支援。

XA協定用來實作分布式事物（分為本地資料總管，事物管理器）。

Savepoints用來實作子事物（嵌套事物）。建立一個Savepoints之後，事物就可以復原到這個點。不會影響建立Savepoints之前的操作。

MyISAM

These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
           

應用範圍比較小。表級鎖定限制了讀/寫的性能，是以在web和資料倉庫配置中，它通常用于隻讀或以讀為主的工作。

特點：

支援表級别的鎖（插入和更新會鎖表）。不支援事務。

擁有較高的插入（insert）和查詢（select）速度。

存儲了表的行數（count速度較快）

（怎麼快速向資料庫插入100萬條資料？我們有一種先用MyISAM插入資料，然後修改存儲引擎為InnoDB的操作）

适合：隻讀之類的資料分析的項目。

InnoDB

The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints
           

mysql5.7中預設存儲引擎。InnoDB是一個事務安全（與ACID相容）的MySQL存儲引擎，它具有送出、復原和崩潰恢複功能來保護使用者資料。InnoDB行級鎖（不更新為更粗粒度的鎖）和Oracle風格的一緻非鎖讀提高了多使用者并發性和性能。InnoDB将使用者資料存儲在聚集索引中，以減少基于主鍵的常見查詢的I/O。為了保持資料完整性。InnoDB還支援外鍵引用完整性限制。

特點：

支援事務、支援外鍵，是以資料完整性，一緻性更高。

支援行級别的鎖和表級别的鎖。

支援讀寫并發，寫不阻塞（MVCC）

特殊的索引存放方式，可以減少IO，提升查詢效率。

适合：經常更新的表，存在并發讀寫或者有事務處理的業務系統。

Memory

Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
           

将所有資料存儲在RAM中，以便在需要快速查找非關鍵資料的環境中快速通路。這個引擎以前被稱為堆引擎。其使用案例正在減少；InnoDB及其緩存池記憶體區域提供了一種通用、持久的方法來将大部分或所有資料儲存在記憶體中，而ndbcluster為大型分布式資料提供了快速的鍵值查找。

特點：

把資料放在記憶體裡面，讀寫的速度很快，但是資料庫重新開機或者崩潰，資料會全部消失，隻适合做臨時表。

将表中資料存儲到記憶體中。

CSV

Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
           

它的表實際上是帶有逗号分隔值的文本檔案。csv表允許以csv格式導入或轉儲資料，以便與讀寫相同格式的腳本和應用程式交換資料。因為CSV表沒有索引，是以通常在正常操作期間将資料儲存在innodb表中，并且隻在導入和到處階段使用csv表。

特點：不允許空行，不支援索引。格式通用，可以直接編輯，适合在不同資料庫之間導入和導出。

如何選擇存儲引擎

如果對資料一緻性要求比較高，需要事務支援，可以選擇InnoDB。

如果資料查詢多更新少，對查詢性能要求比較高，可以選擇MyISAM。

如果需要一個用于查詢的臨時表，可以選擇Memory。

執行引擎 （Query Execution Engine）

存儲引擎分析完了，它是我們存儲資料的形式，繼續第二個問題，是誰使用執行計劃去操作存儲引擎呢？

這就是我們的執行引擎，它利用存儲引擎提供的相應API來完成操作。

為什麼我們修改了表的存儲引擎，操作方式不需要任何改變？因為不同功能的存儲引擎實作的API是相同的。

最後把資料傳回給用戶端，即使沒有結果也要傳回。

MySQL體系結構總結

基于上面的分析的流程，我們一起來梳理下Mysql的子產品。

1，Connector:用來支援各種語言和SQL的互動，比如PHP、Python、Java的JDBC

2，Management Services & Utilities ： 系統管理和控制工具，包括備份恢複、MySQL複制、叢集等

3，Connection Pool：連接配接池，管理需要緩沖的資源，包括使用者密碼權限線程等等。

4，SQL Interface：用來接收使用者的SQL指令，傳回使用者需要的查詢結果

5，Parser：用來解析SQL語句

6，Optimizer：查詢優化器

7，Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，key緩存，權限緩存等等。

8，Pluggable Storage Engines：插件式存儲引擎，它提供API給服務層使用，和具體的檔案打交道。

架構分層

總體上，我們可以把MySQL分成三層，跟用戶端對接的連接配接層，真正執行操作的服務層，和跟硬體打交道的存儲引擎層（參考Mybatis：接口、核心、基礎）。

連接配接層

我們用戶端要連接配接到MySQL伺服器3306端口，必須要跟服務端建立連接配接，那麼管理所有的連接配接，驗證用戶端的身份和權限，這些功能就在連接配接層完成。

服務層

連接配接層會把SQL語句交給服務層，這裡面又包含一系列的流程。

比如查詢緩存的判斷、根據SQL調用相應的接口，對我們的SQL語句進行詞法和文法的解析（比如關鍵字怎麼識别，别名怎麼識别，文法有沒有錯誤等等）

優化器，Mysql會根據一定的規則對我們的SQL語句進行優化，最後再交給執行器區執行。

存儲引擎

存儲引擎就是我們的資料真正存放的地方，在Mysql裡面支援不同的存儲引擎。

一條更新SQL是如何執行的

在資料庫裡面，我們說的update操作其實包含了更新、插入和删除。如果大家有看過MyBatis的源碼，應該知道Executor裡面也隻有doQuery（）和doUpdate（）方法，沒有doDelete（）和doInsert（）。

更新流程和查詢流程有什麼不同呢？

基本流程也是一緻的，也就是說，它要經過解析器、優化器的處理，最後交給執行器。

差別就在于拿到符号條件的資料之後的操作。

緩沖池

首先，InnoDB的資料都是放在磁盤上的，InnoDB操作資料有一個最小的邏輯單元，叫做頁（索引頁和資料頁）。我們對于資料的操作，不是每次都直接操作磁盤，因為磁盤的速度太慢了。InnoDB使用了一種緩沖池的技術，也就是把磁盤讀到的頁放到一塊記憶體區域裡面。這個記憶體區域叫Buffer Pool。

下一次讀取相同的頁，先判斷是不是在緩沖池裡面，如果是，就直接讀取，不用再次通路磁盤。

修改資料的時候，先修改緩沖池裡面的頁。記憶體的資料和磁盤資料不一緻的時候，我們把它叫做髒頁。InnoDB裡面有專門的背景線程把Buffer Pool的資料寫到磁盤。

修改資料的時候，先修改緩沖池裡面的頁。記憶體的資料頁和磁盤資料不一緻的時候，我們把它叫做髒頁。InnoDB裡面有專門的背景線程把Buffer Pool的資料寫入到磁盤，每隔一段時間就一次性地把多個修改寫入磁盤，這個動作就叫做刷髒。

Buffer Pool是InnoDB裡面非常重要的一個結構，它的内部又分為幾塊區域。

InnoDB記憶體結構和磁盤結構

記憶體結構

Buffer Pool 主要分為3個部分：Buffer Pool、Change Buffer、Adaptive Hash Index，另外還有一個（redo）log buffer。

Buffer Pool

Buffer Pool緩存的是頁面資訊，包括資料頁、索引頁。

檢視伺服器狀态，裡面有很多跟Buffer Pool相關的資訊。

SHOW STATUS LIKE '%innodb_buffer_pool%';
           

這些狀态都可以在官網查到詳細的含義，用搜尋功能

Buffer Pool預設大小是128M（134217728位元組），可以調整。

檢視參數（系統變量）：

SHOW VARIABLES like '%innodb_buffer_pool%';
           

記憶體緩沖池寫滿了怎麼辦？（Redis設定的記憶體滿了怎麼辦？）InnoDB用LRU算法來管理緩沖池（連結清單實作，不是傳統的LRU，分成了young和old），經過淘汰的資料就是熱點資料。

記憶體緩沖區對于提升讀寫性能有很大的作用。思考一個問題：

當需要更新一個資料頁時，如果資料頁在Buffer Pool中存在，那麼就直接更新好了。否者的話就需要從磁盤加載到記憶體，再對記憶體的資料頁進行操作。也就是說，如果沒有命中緩沖池，至少要産生一次磁盤 IO，有沒有優化的方式呢。

Change Buffer 寫緩沖

如果這個資料頁不是唯一索引。不存在資料重複的情況，也就不需要從磁盤加載索引頁判斷資料是不是重複（唯一性檢查）。這種情況下可以先把修改記錄在記憶體的緩沖池中，進而提升更新語句（Insert，Delete、update）的執行速度。

這一塊區域就是Change Buffer。

最後把Change Buffer記錄到資料頁的操作叫做Merge。

什麼時候發生merge

在通路這個資料頁的時候，或者通過背景線程、或者資料庫shut down、redo log寫滿時觸發。

如果資料庫大部分索引都是非唯一索引，并且業務是寫多讀少，不會再寫資料庫後立刻讀取，就可以使用Change Buffer（寫緩沖）

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
           

（redo） Log Buffer

如果Buffer Pool裡面的髒頁還沒有刷入磁盤時，資料庫拓機或者重新開機，這些資料丢失。如果寫操作寫到一半，甚至可能會被破壞資料檔案導緻資料庫不可用。

為了避免這個問題，InnoDB把所有對頁面的修改操作專門寫入一個日志檔案，并且在資料庫啟動時從這個檔案進行恢複操作（實作crash-safe）-- 用它來實作事務的持久性

這個檔案就是磁盤的redo log（叫做重做日志），對應于/var/lib/mysql/目錄下的ib_logfile()和ib_logfile1，每個48M.。

這種日志和磁盤配合的整個過程，其實就是Mysql裡的WAL技術，它的關鍵點就是先寫日志，再寫磁盤。

show variables like 'innodb_log%';
           

問題：

同樣是寫磁盤，為什麼不直接寫到db file裡面去？ 為什麼先寫日志再寫磁盤？

我們先來了解一下随機I/O和順序I/O的概念。

磁盤的最小組成單元是扇區，通常是512個位元組。

作業系統和記憶體打交道，最小機關是頁Page。

作業系統和磁盤打交道，讀寫磁盤，最小機關是塊Block。

如果我們所需要的資料是随機分散在不同扇區中，那麼找到相應的資料需要等到磁臂旋轉到指定的頁，然後盤片尋找到對于的扇區，才能找到我們所需要的一塊資料，一次進行此過程直到找完所有資料，這個就是随機IO，讀取資料速度較慢。

假設我們已經找到了第一塊資料，并且其他所需要的資料就在這一塊資料後邊，那麼就不需要重新尋址，可以依次拿到我們所需要的資料，這個就叫做順序IO.

刷盤是随機I/O，而記錄日志是順序I/O，順序I/O效率更高。是以先把修改寫入日志。可以延遲刷盤時機，進而提升系統吞吐。

當然redo log也不是每一次都直接寫入磁盤，在buffer pool裡面有一塊記憶體區域（Log Buffer）專門用來儲存即将要寫入日志檔案的資料，預設16M，它一樣可以節省磁盤IO

磁盤結構

表空間可以看做是InnoDB存儲引擎邏輯結構的最高層，所有的資料都存放在表空間中。InnoDB的表空間分為5大類。

系統表空間system tablespace

在預設情況下InnoDB存儲引擎有一個共享表空間（對應檔案/var/lib/mysql/ibdata1）,也叫系統表空間。

InnoDB系統表空間包含InnoDB資料字典和雙寫緩沖區，chang buffer和undo logs，如果沒有指定file-per-table，也包含使用者建立的表和索引資料。

1，undo在後面介紹，因為有獨立的表空間。

2，資料字典：由内部系統表組成，存儲表和索引的中繼資料（定義資訊）

3，雙寫緩沖（InnoDB的一大特性）

InnoDB的頁和作業系統的頁大小不一緻，InnoDB頁大小一般為16Kb，作業系統頁大小為4K，InnoDB的頁寫入到磁盤時，一個頁需要分4次寫。

如果存儲引擎正在寫入頁的資料到磁盤時發生了拓機，可能出現頁隻寫了一部分的情況，比如隻寫了4k,就拓機了，這種情況叫做部分寫失效（partial page write），可能導緻資料丢失。

我們不是有redo log嗎？但是有個問題，如果這個頁本身已經損壞了，用它來做崩潰恢複是沒有意義的。是以在對于應用redo log之前，需要一個頁的副本。如果出現了寫入失效，就用頁的副本來還原這個頁，然後再引用redo log。這個頁的副本就是double write，InnoDB雙寫技術。通過它實作了資料頁的可靠性。

跟redo log一樣，double write 由兩部分組成，一部分是記憶體的double write，一個部分是磁盤上的double write。 因為double write是順序寫入的，不會帶來很大的開銷。

在預設情況下，所有的表共享一個系統表空間，這個檔案會越來越大，而且它的空間不會收縮。

獨占表空間 file-per-table tablespace

我們可以讓每張表獨占一個表空間。這個開關通過innodb_file_per_table設定，預設開啟。

SHOW VARIABLES LIKE 'innodb_file_per_table';
           

開啟後，則每張表會開辟一個表空間，這個檔案就是資料目錄下的ibd檔案，存放表的索引和資料。

其他類的資料，如復原（undo）資訊，插入緩沖索引頁、系統事務資訊，二次寫緩沖（Double write buffer）等還是存放在原來的共享表空間内。

通用表空間 general tablespace

通用表空間也是一種共享的表空間，跟ibdata1類似。

可以建立一個通用的表空間，用來存儲不同資料庫的表，資料路徑和檔案可以自定義。文法：

背景線程

背景線程的主要作用是負責重新整理記憶體池中的資料和把修改的資料頁重新整理到磁盤。背景線程分為：master thread，IO thread，purge thread，page cleaner thread。

master thread 負責重新整理緩存資料到磁盤并協調排程其他背景線程。

IO thread分為insert buffer、log、read、write程序。分别用來處理insert buffer、重做日志、讀寫請求的IO回調。

purge thread用來回收unde頁。

page cleaner thread 用來重新整理髒頁。

binlog

binlog以事件的形式記錄了所有的DDL和DML語句（因為它記錄的是操作而不是資料值，屬于邏輯日志），可以用來做主從複制和資料恢複。

跟redo log不一樣，它的檔案内容是可以追加的，沒有固定大小限制。

在開啟了binlog功能的情況下，我們可以把binlog導出成SQL語句，把所有的操作重放一遍，來實作資料的恢複。

binlog的另一個功能就是用來實作主從複制，它的原理就是從伺服器讀取主伺服器的binlog，然後執行一遍。