一、儲存引擎的簡介
MySQL 5.7 支援的儲存引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。
1、InnoDB儲存引擎
從MySQL5.5版本之後，預設內建儲存引擎是InnoDB，主要特點有：
（1）災難恢復性比較好；
（2）支援事務。預設的事務隔離級別為可重複讀，透過MVCC（併發版本控制）來實現的。
（3）使用的鎖粒度為行級鎖，可以支援更高的併發；
（4）支援外來鍵；
（5）配合一些熱備工具可以支援線上熱備份；
（6）在InnoDB中存在著緩衝管理，透過緩衝池，將索引和資料全部快取起來，加快查詢的速度；
（7）支援聚簇索引。對於InnoDB型別的表，其資料的物理組織形式是聚簇表。所有的資料按照主鍵來組織。資料和索引放在一塊，都位於B+數的葉子節點上，透過聚簇索引來查詢可以減少回表查詢。
（8）InnoDB不儲存表的具體行數，執行select count(*) from table時需要全表掃描
（9）支援B-tree索引和全文檢索（ MySQL 5.6後InnoDB儲存引擎開始支援全文檢索）
（10）不支援Hash索引，但是內建了自適應hash索引。
2、MyISAM儲存引擎
在5.5版本之前，MyISAM是MySQL的預設儲存引擎，該儲存引擎併發性差，不支援事務，所以使用場景比較少，主要特點為：
（1）不支援事務；
（2）不支援外來鍵，如果強行增加外來鍵，不會提示錯誤，只是外來鍵不其作用；
（3）不支援聚簇索引，對資料的查詢快取只會快取索引，不會像InnoDB一樣快取資料，而且是利用作業系統本身的快取；
（4）預設的鎖粒度為表級鎖，所以併發度很差，加鎖快，鎖衝突較少，所以不太容易發生死鎖；
（5）支援全文索引（MySQL5.6之後，InnoDB儲存引擎也對全文索引做了支援），但是MySQL的全文索引基本不會使用，對於全文索引，現在有其他成熟的解決方案，比如：ElasticSearch，Solr，Sphinx等。
（6）資料庫所在主機如果當機，MyISAM的資料檔案容易損壞，而且難恢復；
二、InnoDB的結構組成
1、記憶體結構
1.Buffer Pool（讀緩衝池）
緩衝池是主記憶體中的一個區域，InnoDB在訪問表和索引資料時會在其中進行快取，避免每次訪問都進行磁碟IO。
Buffer Pool的三個連結串列，LRU連結串列、free連結串列、flush連結串列。
free連結串列：雙向連結串列，每個節點快取頁的描述資料塊的地址。
flush連結串列：被修改過的快取頁的描述資料塊，組成的雙向連結串列，後續都是要flush重新整理到磁碟上去。
LRU連結串列：將頻繁訪問的資料放在連結串列頭部，不怎麼訪問的資料連結串列末尾，空間不夠的時候就從尾部開始淘汰。
2.Change Buffer（寫緩衝池）
如果發生變化的輔助索引頁不在buffer pool裡，由Change Buffer先快取這些輔助索引頁的變更動作。
等輔助索引頁被讀取時，再將資料再將資料合併(merge)恢復到緩衝池中的技術；或者定期對寫輔助索引頁的changes buffer進行合併，寫到到buffer pool 中。目的是降低寫操作的磁碟IO，提升資料庫效能。
為什麼change buffer只對輔助索引生效？
以insert新增操作為例，插入順序一般是按照主鍵遞增順序進行插入的，插入聚集索引（主鍵索引）一般是順序的，不需要磁碟的隨機讀取。
這種情況下對聚集索引的修改速度是非常快的，所以不需要進行寫緩衝。
而對於輔助索引的插入或者更新操作，由於B+樹的索引結構的特性決定了輔助索引插入的離散型。
所以，對於輔助索引的插入或者更新操作，InnoDB中不是每一次都直接插入到索引頁中，而是先判斷插入的輔助索引頁是否在快取區中，若在直接插入；
若不在，則先放入到change buffer中，然後再以一定頻率和情況進行change buffer和輔助索引頁子節點的merge（合併）操作，
這時通常能將多個插入合併到一個操作中（因為在一個索引頁中），這就大大提高了對於輔助索引插入的效能。
3.自適應hash索引（Adaptive Hash Index，AHI）
InnoDB儲存引擎會監控對錶上各索引頁的查詢，如果觀察到建立hash索引可以提高查詢速度，則自動建立hash索引，索引的索引。
4. Log Buffer（日誌緩衝）
儲存要寫入磁碟上的日誌檔案資料的記憶體區域，由innodb_log_buffer_size變數定義大小，預設16MB。
日誌緩衝區的內容定期重新整理到磁碟，較大的日誌緩衝區使大型事務可以執行，而無需在事務提交之前將redo日誌資料寫入磁碟。
插入或刪除許多行的事務，增加日誌緩衝區的大小可以節省磁碟I/O。
2、磁碟結構
1、表
2、索引
3、表空間
系統表空間
系統表空間是存放change buffer的區域。
表單檔案表空間
每個表的資料和索引都會採用單獨的檔案進行儲存。是否啟動 file-per-table表空間是由innodb_file_per_table屬性來控制的。
常規表空間
常規表空間是使用 CREATE TABLESPACE 語法建立的共享InnoDB表空間。是共享的表空間，一個檔案能夠儲存多個表資料，常規表空間由於多表共享表空間，消耗的記憶體會更少一點，具有潛在的記憶體優勢。
Undo表空間
回滾表空間，用來儲存回滾日誌，即undo logs。
臨時表空間（Temporary Tablespaces）
InnoDB使用會話臨時表空間和全域性臨時表空間。
4、Doublewrite Buffer（雙寫緩衝）
doublewrite 緩衝區是一個儲存區域，InnoDB在將頁面寫入InnoDB資料檔案中之前，會寫入從緩衝池中重新整理的頁面。
如果在頁面寫入過程中發生作業系統，儲存子系統或mysqld程序崩潰，則InnoDB可以在崩潰恢復期間從doublewrite緩衝區中找到該頁面的良好副本。
MySQL 8.0.20之前，doublewrite緩衝區儲存區位於InnoDB系統表空間中。
MySQL 8.0.20開始，doublewrite緩衝區儲存區位於doublewrite檔案中。
5、Redo Log（重做日誌）
基於磁碟的資料結構，主要防止在崩潰恢復期間用於糾正不完整事務寫入的資料。
正常操作時，重做日誌對更改表資料的請求進行編碼記錄。初始化時，自動重播未完成意外關閉之前未完成更新資料檔案的修改。
預設情況下，redo log會自動生成2個檔案。
6、Undo Logs（回滾日誌）
回滾日誌主要是為了支援事務回滾功能。預設會生成2個回滾日誌，儲存在undo tablespaces下。
補充：二進位制日誌(binlog)
binlog是記錄所有資料庫表結構變更（例如CREATE、ALTER TABLE…）以及表資料修改（INSERT、UPDATE、DELETE…）的二進位制日誌。
用於資料恢復和資料複製
MySQL正是透過主伺服器的二進位制日誌來實現資料的傳遞。主伺服器上的二進位制日誌內容會被髮送到各個從伺服器，並在每個從伺服器上執行，從而保證了主從伺服器之間資料的一致性。
在預設配置下，MySQL不記錄二進位制日誌。可以透過設定引數–log-bin=[base_name]啟用二進位制日誌功能。
三、SQL的執行
1、查詢流程
2、寫入流程
http://cainiao.guashuw.com/