一、MySQL的發(fā)展歷史和版本分支

MySQL發(fā)展歷史：

MySQL主要分支：

• MariaDB：Oracle收購MySQL之后，MySQL創(chuàng)始人之一Monty擔(dān)心MySQL發(fā)展的未來(開發(fā)緩慢、封閉、可能閉源)，就創(chuàng)建了一個分支MariaDB(2009年)，默認(rèn)使用Maria存儲引擎，它是原MyISAM存儲引擎的升級版本。
• Percona Server：基于InnoDB存儲引擎，提升了性能和易管理性，形成了XtraDB引擎。
• 網(wǎng)易的InnoSQL
• 極數(shù)據(jù)云舟的ArkDB

二、一條SQL語句是如何執(zhí)行的？

SQL執(zhí)行流程

1. 連接

MySQL服務(wù)監(jiān)聽的端口默認(rèn)是3306。
查看當(dāng)前有多少連接？

show global status like 'Thread%';

連接超時參數(shù)：

show global variables like 'wait_timeout'; -- 非交互超時時間，如JDBC程序
show global variables like 'interactive_timeout'; -- 交互時超時工具，如navicat

默認(rèn)都是28800秒，8小時。
MySQL允許最大連接數(shù)(并發(fā)數(shù))為151個，最大可設(shè)置為100000.

show variables like 'max_connections';

2，查詢緩存

show variables like 'query_cache%';

MySQL的緩存默認(rèn)是關(guān)閉的。不推薦使用，因為它要求SQL語句必須一模一樣(多一個空格，字母大小寫都不行)，還有當(dāng)表中的數(shù)據(jù)發(fā)生變化時，緩存會失效。 在MySQL8.0中，查詢緩存被移除了。

3，詞法解析和預(yù)處理(Parser & Preprocessor)

1. 詞法解析：就是把一個完整的SQL語句打碎成一個個的單詞。每個符號是什么類型，從哪里開始到哪里結(jié)束。

2. 語法解析：對SQL進行語法檢查，如單引號有沒有閉合，根據(jù)MySQL的語法規(guī)則，生成解析樹(select_lex)

   
   
   詞法解析
   
   

   任何數(shù)據(jù)庫中間件，要解析SQL完成路由功能，必須要有詞法和語法分析功能，如Mycat，Sharding-JDBC等，開源詞法解析有LEX、Yacc等。

3. 預(yù)處理器：檢查生成的解析樹，解決解析器無法解析的語義。比如檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。

4，查詢優(yōu)化(Query Optimizer)與查詢執(zhí)行計劃

查詢優(yōu)化器的目的是根據(jù)解析樹生成不同的執(zhí)行計劃(Execution Plan)，然后選擇一種最做優(yōu)的執(zhí)行計劃，MySQL里使用的是基于開銷(cost)的優(yōu)化器。查看查詢的開銷：

show status like 'Last_query_cost';

參考書籍：《數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)-原理解析與SQL性能優(yōu)化》
優(yōu)化器最終會把解析樹變成一個查詢執(zhí)行計劃，查詢執(zhí)行計劃是一個數(shù)據(jù)結(jié)構(gòu)?？梢允褂?EXPLAIN，可以看到查詢執(zhí)行計劃的信息。

EXPLAIN select name from user where id = 1;

如果要得到詳細的信息，可以用 FORMAT=JSON，或者開啟optimizer trace.

explain FORMAT=JSON select name from user where id = 1;

5，存儲引擎

查看數(shù)據(jù)庫存放數(shù)據(jù)的路徑：

show VARIABLES like 'datadir'; 

默認(rèn)情況下，每個數(shù)據(jù)庫有一個自己的文件夾，任何一個存儲引擎都有一個frm文件，這個是表結(jié)構(gòu)的定義文件。不同的存儲引擎存放數(shù)據(jù)的方式不一樣，產(chǎn)生的文件也不一樣，innodb是1個，memory沒有，myisam有兩個。

一張表的存儲引擎是在創(chuàng)建表的時候指定的，使用ENGINE關(guān)鍵字，5.5.5之前，默認(rèn)為MyISAM，5.5.5之后，默認(rèn)的存儲引擎為InnoDB.
常見存儲引擎：
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

• MyISAM：使用范圍較小。表級鎖定限制了讀/寫的性能，常用于只讀或以讀為主的工作。特點：1，支持表級別的鎖(插入和更新會鎖表)。不支持事務(wù)。2，擁有較高的插入和查詢速度。3，存儲了表的行數(shù)(count速度快)。
  適合：只讀之類的數(shù)據(jù)分析項目。
  如果快速向數(shù)據(jù)庫插入100萬數(shù)據(jù)，先用MyISAM插入，再修改存儲引擎為InnoDB.

• InnoDB：InnoDB是一個事務(wù)安全(與ACID兼容)的存儲引擎，具有提交、回滾和崩潰恢復(fù)功能來保護用戶數(shù)據(jù)。InnoDB行級鎖提高了多用戶并發(fā)性和性能。InnoDB將用戶數(shù)據(jù)存儲在聚集索引中，以減少基于主鍵的常見查詢的I/O。為保持?jǐn)?shù)據(jù)完整性，InnoDB還支持外健引用完整性約束。
  特點：1，支持事務(wù)、支持外健，數(shù)據(jù)完整性、一致性更高。2，支持行級別鎖和表級別鎖。3，支持讀寫并發(fā)，寫不阻塞讀(MVCC)。4，特殊的索引存放方式，可減少I/O，提升查詢效率。
  適合：經(jīng)常更新的表，存在并發(fā)讀寫或有事務(wù)處理的業(yè)務(wù)系統(tǒng)。

• Memory：將數(shù)據(jù)存儲在RAM中，以快速查找非關(guān)鍵數(shù)據(jù)的環(huán)境提供了快速訪問。特點：數(shù)據(jù)放在內(nèi)存里，讀寫速度快，但數(shù)據(jù)庫重啟或崩潰，數(shù)據(jù)會丟失。只適合做臨時表。

選擇存儲引擎：
對數(shù)據(jù)一致性要求高，需要事務(wù)支持，選擇InnoDB。
數(shù)據(jù)查詢多更新少，對查詢性能要求較高，選擇MyISAM。
用于查詢的臨時表，選擇Memory。
可以根據(jù)官網(wǎng)的手冊用C語言開發(fā)一個存儲引擎：https://dev.mysql.com/doc/internals/en/custom-engine.html

show engine innodb status;

6，執(zhí)行引擎(Query Execution Engine)，返回結(jié)果

使用存儲引擎提供的API來完成相應(yīng)操作，修改了存儲引擎，操作方式不需要改變？因為不同的存儲引擎實現(xiàn)的API是相同的。

三，MySQL體系架構(gòu)

MySQL架構(gòu)

1，連接層：客戶端要連接到MySQL服務(wù)器的3306端口，要跟服務(wù)器建立連接，管理所有的連接，驗證客戶端的身份和權(quán)限，這些功能在連接層完成。
2，服務(wù)層：查詢緩存的判斷、根據(jù)SQL調(diào)用相應(yīng)的接口，對SQL進行詞法和語法的解析，對SQL進行優(yōu)化，交給執(zhí)行器去執(zhí)行。
3，存儲引擎：存數(shù)據(jù)的地方，支持不同的存儲引擎

四，更新SQL是怎么執(zhí)行的

update操作包含了更新、插入和刪除。在MyBatis源碼中，Executor里只有doQuery()和doUpdate()方法，沒有doDelete()和doInsert()。

1，緩沖池 Buffer Pool

對InnoDB存儲引擎來說，要操作數(shù)據(jù)，要把磁盤的數(shù)據(jù)加載到內(nèi)存里才能操作。存儲引擎有一個預(yù)讀取的概念，就是磁盤上的一塊數(shù)據(jù)被讀取時，很有可能它附近位置的數(shù)據(jù)馬上被讀取到，這個叫局部性原理。
InnoDB設(shè)定存儲引擎從磁盤讀取數(shù)據(jù)到內(nèi)存的最小單位，叫做頁。默認(rèn)為16KB。數(shù)據(jù)所在的頁叫數(shù)據(jù)頁。
InnoDB設(shè)計了一個內(nèi)存緩沖區(qū)，讀數(shù)據(jù)的時候，先判斷是不是在這個內(nèi)存區(qū)域中，如果是直接讀取，然后操作，不用從磁盤加載。如果不是，讀取之后寫到這個內(nèi)存的緩沖區(qū)，這個緩沖區(qū)就叫做 Buffer Pool。

Buffer Pool

修改數(shù)據(jù)的時候，也是先寫到buffer pool，而不是直接寫到磁盤。內(nèi)存中的數(shù)據(jù)和磁盤數(shù)據(jù)不一致的時候，叫做臟頁。InnoDB有專門的后臺線程把Buffer Pool的數(shù)據(jù)寫入到磁盤，每隔一段時間就一次性把多個修改寫入磁盤，叫臟刷。
Buffer Pool的作用是提高讀寫的效率。

2，Redo log

InnoDB把所有頁面的修改操作專門寫到一個日志文件，如果有未同步到磁盤的數(shù)據(jù)，數(shù)據(jù)庫啟動的時候，會從這個日志文件中進行恢復(fù)(實現(xiàn)crash-safe)，即事務(wù)ACID中的D(持久性)，就是用它來實現(xiàn)的。這個日志文件就是磁盤的redo log。

redo log

寫日志文件和寫數(shù)據(jù)文件有什么區(qū)別？
刷盤是隨機I/O，而記錄日志是順序I/O，順序I//O效率更高，本質(zhì)上是數(shù)據(jù)集中存儲和分散存儲的區(qū)別。先把修改寫到日志文件，在保證內(nèi)存數(shù)據(jù)安全性的情況下，可以延遲刷盤時機，進行提升系統(tǒng)吞吐。
redo log位于/var/lib/mysql目錄下的ib_logfile0和ib_logfile1，默認(rèn)2個文件，每個48M。

show variables like 'innodb_log%';

redo log的特點：

1. redo log是InnoDB存儲引擎實現(xiàn)的，并不是所有存儲引擎都有，支持崩潰恢復(fù)是InnoDB的一個特性。
2. redo log不是記錄數(shù)據(jù)頁更新之后的狀態(tài)，而是記錄“在某個數(shù)據(jù)頁上做了什么修改”。屬于物理日志。
3. redo log大小是固定的，前端的內(nèi)容會被覆蓋，一旦寫滿，會觸發(fā)buffer pool到磁盤的同步。

3，Undo log

undo log(撤銷或回滾日志)記錄了事務(wù)發(fā)生之前的數(shù)據(jù)狀態(tài)，分為insert undo log 和 update undo log。如果修改數(shù)據(jù)庫時出現(xiàn)異常，可用undo log來實現(xiàn)回滾操作(原子性)。
undo log記錄的是反向的操作，如insert記錄的是delete，update會記錄update原來的值。

show global variables like '%undo%';

4，更新過程

1，事務(wù)開始，從內(nèi)存(buffer pool)或磁盤(data file)取到包含這條數(shù)據(jù)的數(shù)據(jù)頁，返回給server執(zhí)行器。
2，server執(zhí)行器修改數(shù)據(jù)頁的這一行數(shù)據(jù)值。
3，記錄undo log。
4，記錄redo log。
5，調(diào)用存儲引擎接口，記錄數(shù)據(jù)頁到Buffer Pool。
6，事務(wù)提交。

5，InnoDB總體架構(gòu)

InnoDB架構(gòu)

1，內(nèi)存架構(gòu)
Buffer Pool主要分3個部分：Buffer Pool、Change Buffer、Adaptive Hash Index，還有一個(redo) log buffer。
1.1、Buffer Pool
Buffer Pool緩存的是頁面信息，包括數(shù)據(jù)頁、索引頁。默認(rèn)大小為128M，可調(diào)整。

show variables like '%innodb_buffer_pool%';

查看服務(wù)器狀態(tài)，有很多和Buffer Pool有關(guān)的信息：

show status like '%innodb_buffer_pool%';

可在官網(wǎng)查詢詳細含義：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
1.2、LRU
內(nèi)存緩沖池寫滿之后，InnoDB用LRU算法來管理緩沖池(鏈表實現(xiàn))。
InnoDB使用一個雙向鏈表，LRU list，該list放的不是data page，而是指向緩存頁的指針。InnoDB的數(shù)據(jù)不是都是在訪問的時候才緩存到buffer pool中的，InnoDB有一個預(yù)讀機制(read ahead)，設(shè)計者認(rèn)為訪問某個page數(shù)據(jù)的時候，相鄰的page可能很快被訪問到，所以把這些page放到buffer pool中緩存起來。預(yù)讀機制分為兩種類型，一種叫線性預(yù)讀(異步的)(Linear read-ahead)。為便于管理，InnoDB把64個相鄰的page叫做一個extent（區(qū)）。如果順序訪問了一個extent的56個page，這時InnoDB會把下一個extent緩存到buffer pool中。
順序訪問到多少page才緩存下一個extent，由一個參數(shù)控制：

show variables like 'innodb_read_ahead_threshold';

第二種叫隨機預(yù)讀(Random read-ahead)，如果buffer pool已經(jīng)緩存了同個extent的數(shù)據(jù)頁超過13個，就會把這個extent剩余的page全部緩存到buffer pool。
隨機預(yù)讀的功能默認(rèn)不啟用，由參數(shù)控制：

show variables like 'innodb_random_read_ahead';

LRU

所有新數(shù)據(jù)加入buffer pool的時候，一律放到冷區(qū)的head，不管是預(yù)讀的，還是普通的讀操作。如果再次被訪問，就移動到熱區(qū)的head。熱區(qū)的數(shù)據(jù)長時間沒有被訪問，先移動到冷區(qū)的head，慢慢在tail被淘汰。
默認(rèn)情況下，熱區(qū)占了5/8的大小，冷區(qū)占了3/8。由innodb_old_blocks_pct控制。
對于加載到冷區(qū)的數(shù)據(jù)，設(shè)置一個時間窗口，只有超過這個時間之后被訪問，才認(rèn)為是有效的訪問，通過innodb_old_blocks_time參數(shù)控制，默認(rèn)1秒鐘。從而解決全表描述或者預(yù)讀的數(shù)據(jù)污染熱數(shù)據(jù)。
1.3、Change Buffer 寫緩沖
如果這個數(shù)據(jù)頁不是唯一索引，不存在數(shù)據(jù)重復(fù)的情況，可以先把修改記錄在內(nèi)存的緩沖池中，從而提升更新語句的執(zhí)行速度。這一區(qū)域叫Change Buffer。把Change Buffer的數(shù)據(jù)記錄到數(shù)據(jù)頁的操作叫merge。在訪問這個數(shù)據(jù)頁的時候，或者通過后臺線程，或者數(shù)據(jù)庫shut down，redo log寫滿的時候觸發(fā)。
如果數(shù)據(jù)庫大部分索引是非唯一索引，并且業(yè)務(wù)寫多讀少，不會在寫數(shù)據(jù)后立刻讀取，可以使用Change Buffer（寫緩沖）。

show variables like 'innodb_change_buffer_max_size';

代表change buffer占buffer pool的比例，默認(rèn)為25%。
1.4、Adaptive Hash Index
哈希索引，見下節(jié)。
1.5、Redo log Buffer
Redo log也不是每一次都寫入磁盤，在Buffer Pool里有一塊內(nèi)存區(qū)域(Log Buffer)用來保存將要寫入日志文件的數(shù)據(jù)，默認(rèn)16M，可節(jié)省磁盤IO。

redo log buffer

show variables like 'innodb_log_buffer_size';

redo log主要用于崩潰恢復(fù)。redo log寫入磁盤，不是寫入數(shù)據(jù)文件。
log buffer寫入磁盤的時機，由一個參數(shù)控制，默認(rèn)為1。

show variables like 'innodb_flush_log_at_trx_commit';

log buffer刷盤策略

1.3 磁盤結(jié)構(gòu)
表空間是InnoDB存儲引擎結(jié)構(gòu)的最高層，所有的數(shù)據(jù)都保存在表空間中。分為5類。

• 系統(tǒng)表空間(system tablespace)：默認(rèn)情況下InnoDB存儲引擎有一個共享表空間(/var/lib/mysql/ibdata1)，叫系統(tǒng)表空間。
  包含InnoDB的數(shù)據(jù)字典和雙寫緩沖區(qū)，Change Buffer和Undo Logs，如果沒有指定file-per-table，也包含用戶創(chuàng)建的表和索引數(shù)據(jù)。
  1. undo在后面介紹，因為可以設(shè)置獨立的表空間
  2. 數(shù)據(jù)字典：由內(nèi)部系統(tǒng)表組成，存儲表和索引的元數(shù)據(jù)。
  3. 雙寫緩沖。
     如果存儲引擎在寫頁的數(shù)據(jù)時發(fā)生了宕機，可能出現(xiàn)頁只寫了一部分的情況，這種情況叫做寫失效(partial page write)，可能導(dǎo)致數(shù)據(jù)丟失。這時頁本身已經(jīng)損壞，無法進行崩潰恢復(fù)。所以在應(yīng)用redo log之前，需要一個頁的副本。如果出現(xiàn)了寫失效，就用頁的副本來還原這個頁，然后再用redo log。這個頁的副本就是double write。
• 獨占表空間 file-per-table tablespace：可以讓每張表占一個表空間，通過innodb_file_per_table配置，默認(rèn)開啟。

show variables like 'innodb_file_per_table';

開啟后，每張表會開啟一個表空間，這個文件就是數(shù)據(jù)目錄下的idb文件，用來存放表的索引和數(shù)據(jù)。

• 通用表空間 general tablespace：可能創(chuàng)建一個能用表空間，用來存放不同數(shù)據(jù)庫的表，數(shù)據(jù)路徑和文件可以自定義。語法：

create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;

在創(chuàng)建表的時候可以指定表空間，用ALERT修改表空間可以轉(zhuǎn)換表空間。

create table t2673(id integer) tablespace ts2673;

不同表空間的數(shù)據(jù)是可以移動的。刪除表空間需要先刪除里面的所有表。

drop table t2673;
drop tablespace ts2673;

• 臨時表空間 temporary tablespaces
  存儲臨時表的數(shù)據(jù)，包括用戶創(chuàng)建的臨時表，和磁盤內(nèi)部的臨時表。對應(yīng)數(shù)據(jù)目錄下的ibtmp1文件。當(dāng)服務(wù)器正常關(guān)閉吧，該表空間被刪除。

6，后臺進程

后臺線程主要作用是負(fù)責(zé)刷新內(nèi)存池中的數(shù)據(jù)和把修改的數(shù)據(jù)頁刷新到磁盤。后臺線程分為：master thread，IO thread，purge thread，page cleaner thread。

• master thread ：負(fù)責(zé)刷新緩存數(shù)據(jù)到磁盤并協(xié)調(diào)調(diào)度其它后臺進程。
• IO thread：分為insert buffer、log、read、write進程。分別用來處理insert buffer、重寫日志、讀寫請求的IO回調(diào)。
• purge thread：用來回收undo頁。
• page cleaner thread：用來刷新臟頁。

7，Binlog

binlog以事件的形式記錄了所有的DDL和DML語句(記錄的是操作，屬于邏輯日志)，可以用來做主從復(fù)制和數(shù)據(jù)恢復(fù)。
跟redo log不一樣，它的文件內(nèi)容是可以追加的，沒有固定大小限制。
在開啟binlog功能的前提下，可以把binlog導(dǎo)出成SQL語句，把所有的操作重放一遍，來實現(xiàn)數(shù)據(jù)的恢復(fù)。
binlog的另一個功能是實現(xiàn)主從復(fù)制，它的原理是從服務(wù)器讀取主服務(wù)器的binlog，然后執(zhí)行一遍。

更新語句執(zhí)行流程

為什么需要兩階段提交？
如果在執(zhí)行更新操作的時候，如果寫完redo log，還沒有寫binlog的時候，MySQL重啟了。因為redo log用于在重啟的時候恢復(fù)數(shù)據(jù)，所以會執(zhí)行更新，但是binlog沒有記錄這個邏輯日志，這時候用binlog去恢復(fù)數(shù)據(jù)或者同步到從庫，會出現(xiàn)數(shù)據(jù)不一致的情況。所以在寫兩個日志的情況下，binlog充當(dāng)事務(wù)的協(xié)調(diào)者。通知InnoDB來執(zhí)行prepare或者commit或者rollback。如果6步寫binlog失敗，就不會提交。