架構(gòu)

image.png

1、 Connector：用來支持各種語言和 SQL 的交互姻锁，比如 PHP寞冯，Python渴析，Java 的 JDBC；
2吮龄、 Management Serveices & Utilities：系統(tǒng)管理和控制工具俭茧，包括備份恢復(fù)、MySQL 復(fù)制漓帚、集群等等母债；
3、 Connection Pool：連接池尝抖，管理需要緩沖的資源场斑，包括用戶密碼權(quán)限線程等 等；
4牵署、 SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結(jié)果
5喧半、 Parser：用來解析 SQL 語句奴迅；
6、 Optimizer：查詢優(yōu)化器挺据；
7取具、 Cache and Buffer：查詢緩存，除了行記錄的緩存之外扁耐，還有表緩存暇检，Key 緩 存，權(quán)限緩存等等婉称；
8块仆、 Pluggable Storage Engines：插件式存儲引擎，它提供 API 給服務(wù)層使用王暗， 跟具體的文件打交道

總體上悔据，我們可以把 MySQL 分成三層，跟客戶端對接的連接層俗壹，真正執(zhí)行操作的服務(wù)層科汗，和跟硬件打交道的存儲引擎層（參考 MyBatis：接口、核心绷雏、基礎(chǔ)）头滔。

image.png

連接層

我們的客戶端要連接到 MySQL 服務(wù)器 3306 端口怖亭，必須要跟服務(wù)端建立連接，那么 管理所有的連接坤检，驗證客戶端的身份和權(quán)限兴猩，這些功能就在連接層完成

服務(wù)層

連接層會把 SQL 語句交給服務(wù)層，這里面又包含一系列的流程： 比如查詢緩存的判斷缀蹄、根據(jù) SQL 調(diào)用相應(yīng)的接口峭跳，對我們的 SQL 語句進行詞法和語 法的解析（比如關(guān)鍵字怎么識別，別名怎么識別缺前，語法有沒有錯誤等等）蛀醉。 然后就是優(yōu)化器，MySQL 底層會根據(jù)一定的規(guī)則對我們的 SQL 語句進行優(yōu)化衅码，最 后再交給執(zhí)行器去執(zhí)行拯刁。

存儲引擎

存儲引擎就是我們的數(shù)據(jù)真正存放的地方，在 MySQL 里面支持不同的存儲引擎逝段。 再往下就是內(nèi)存或者磁盤垛玻。

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

image.png

我們的程序或者工具要操作數(shù)據(jù)庫奶躯，第一步要做什么事情帚桩？ 跟數(shù)據(jù)庫建立連接。

通信協(xié)議

首先嘹黔，MySQL 必須要運行一個服務(wù)账嚎，監(jiān)聽默認的 3306 端口。 在我們開發(fā)系統(tǒng)跟第三方對接的時候儡蔓，必須要弄清楚的有兩件事郭蕉。
第一個就是通信協(xié)議，比如我們是用 HTTP 還是 WebService 還是 TCP喂江？
第二個是消息格式召锈，比如我們用 XML 格式，還是 JSON 格式获询，還是定長格式涨岁？報文 頭長度多少，包含什么內(nèi)容吉嚣，每個字段的詳細含義卵惦。 比如我們之前跟銀聯(lián)對接，銀聯(lián)的銀行卡聯(lián)網(wǎng)規(guī)范瓦戚，約定了一種比較復(fù)雜的通訊協(xié) 議叫做：四進四出單工異步長連接（為了保證穩(wěn)定性和性能）沮尿。

image.png

MySQL 是支持多種通信協(xié)議的，可以使用同步/異步的方式，支持長連接/短連接畜疾。 這里我們拆分來看赴邻。第一個是通信類型。

通信類型：同步或者異步

同步通信的特點：
1啡捶、同步通信依賴于被調(diào)用方姥敛，受限于被調(diào)用方的性能。也就是說瞎暑，應(yīng)用操作數(shù)據(jù)庫彤敛， 線程會阻塞，等待數(shù)據(jù)庫的返回了赌。
2墨榄、一般只能做到一對一，很難做到一對多的通信勿她。

異步跟同步相反：
1袄秩、異步可以避免應(yīng)用阻塞等待，但是不能節(jié)省 SQL 執(zhí)行的時間
2逢并、如果異步存在并發(fā)之剧，每一個 SQL 的執(zhí)行都要單獨建立一個連接，避免數(shù)據(jù)混亂砍聊。 但是這樣會給服務(wù)端帶來巨大的壓力（一個連接就會創(chuàng)建一個線程背稼，線程間切換會占用 大量 CPU 資源）。另外異步通信還帶來了編碼的復(fù)雜度玻蝌，所以一般不建議使用雇庙。如果要 異步，必須使用連接池灶伊，排隊從連接池獲取連接而不是創(chuàng)建新連接。

一般來說我們連接數(shù)據(jù)庫都是同步連接寒跳。

連接方式：長連接或者短連接

MySQL 既支持短連接聘萨，也支持長連接。短連接就是操作完畢以后童太，馬上 close 掉米辐。 長連接可以保持打開，減少服務(wù)端創(chuàng)建和釋放連接的消耗书释，后面的程序訪問的時候還可 以使用這個連接翘贮。一般我們會在連接池中使用長連接。

保持長連接會消耗內(nèi)存爆惧。長時間不活動的連接狸页，MySQL 服務(wù)器會斷開

show global variables like 'wait_timeout'; -- 非交互式超時時間，如 JDBC 程序 
show global variables like 'interactive_timeout'; -- 交互式超時時間，如數(shù)據(jù)庫工具

默認都是 28800 秒芍耘，8 小時址遇。
我們怎么查看 MySQL 當(dāng)前有多少個連接？
可以用 show status 命令

show global status like 'Thread%';

Threads_cached：緩存中的線程連接數(shù)斋竞。
Threads_connected：當(dāng)前打開的連接數(shù)倔约。
Threads_created：為處理連接創(chuàng)建的線程數(shù)。
Threads_running：非睡眠狀態(tài)的連接數(shù)坝初，通常指并發(fā)連接數(shù)浸剩。
每產(chǎn)生一個連接或者一個會話，在服務(wù)端就會創(chuàng)建一個線程來處理鳄袍。反過來绢要，如果要 殺死會話，就是 Kill 線程

有了連接數(shù)畦木，怎么知道當(dāng)前連接的狀態(tài)袖扛？
也可以使用 SHOW PROCESSLIST; （root 用戶）查看 SQL 的執(zhí)行狀態(tài)。
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

image.png

image.png

show variables like 'max_connections';

show 的參數(shù)說明：
1勾栗、級別：會話 session 級別（默認）惨篱；全局 global 級別
2、動態(tài)修改：set围俘，重啟后失效砸讳；永久生效，修改配置文件/etc/my.cnf

set global max_connections = 1000;

通信協(xié)議

MySQL 支持哪些通信協(xié)議呢界牡？
第一種是 Unix Socket簿寂。
比如我們在 Linux 服務(wù)器上，如果沒有指定-h 參數(shù)宿亡，它就用 socket 方式登錄（省略 了-S /var/lib/mysql/mysql.sock）常遂。

image.png

它不用通過網(wǎng)絡(luò)協(xié)議，也可以連接到 MySQL 的服務(wù)器挽荠，它需要用到服務(wù)器上的一個 物理文件（/var/lib/mysql/mysql.sock）克胳。

select @@socket;

如果指定-h 參數(shù)，就會用第二種方式圈匆，TCP/IP 協(xié)議漠另。

mysql -h192.168.8.211 -uroot -p123456

我們的編程語言的連接模塊都是用 TCP 協(xié)議連接到 MySQL 服務(wù)器的，比如 mysql-connector-java-x.x.xx.jar跃赚。

image.png

另外還有命名管道（Named Pipes）和內(nèi)存共享（Share Memory）的方式笆搓，這兩種 通信方式只能在 Windows 上面使用，一般用得比較少。

通信方式

image.png

• 單工：
  在兩臺計算機通信的時候砚作，數(shù)據(jù)的傳輸是單向的窘奏。生活中的類比：遙控器。

• 半雙工：
  在兩臺計算機之間葫录，數(shù)據(jù)傳輸是雙向的着裹，你可以給我發(fā)送，我也可以給你發(fā)送米同， 但是在這個通訊連接里面骇扇，同一時間只能有一臺服務(wù)器在發(fā)送數(shù)據(jù)，也就是你要給我發(fā) 的話面粮，也必須等我發(fā)給你完了之后才能給我發(fā)少孝。生活中的類比：對講機

• 全雙工：
  數(shù)據(jù)的傳輸是雙向的，并且可以同時傳輸熬苍。生活中的類比：打電話稍走。

MySQL 使用了半雙工的通信方式？
要么是客戶端向服務(wù)端發(fā)送數(shù)據(jù)柴底，要么是服務(wù)端向客戶端發(fā)送數(shù)據(jù)婿脸，這兩個動作不能 同時發(fā)生。所以客戶端發(fā)送 SQL 語句給服務(wù)端的時候柄驻，（在一次連接里面）數(shù)據(jù)是不能 分成小塊發(fā)送的狐树，不管你的 SQL 語句有多大，都是一次性發(fā)送鸿脓。
比如我們用 MyBatis 動態(tài) SQL 生成了一個批量插入的語句抑钟，插入 10 萬條數(shù)據(jù)，values 后面跟了一長串的內(nèi)容野哭，或者 where 條件 in 里面的值太多在塔，會出現(xiàn)問題。
這個時候我們必須要調(diào)整 MySQL 服務(wù)器配置 max_allowed_packet 參數(shù)的值（默認 是 4M）拨黔，把它調(diào)大蛔溃，否則就會報錯

image.png

另一方面，對于服務(wù)端來說蓉驹，也是一次性發(fā)送所有的數(shù)據(jù)，不能因為你已經(jīng)取到了想 要的數(shù)據(jù)就中斷操作揪利，這個時候會對網(wǎng)絡(luò)和內(nèi)存產(chǎn)生大量消耗态兴。
所以，我們一定要在程序里面避免不帶 limit 的這種操作疟位，比如一次把所有滿足條件 的數(shù)據(jù)全部查出來瞻润，一定要先 count 一下。如果數(shù)據(jù)量的話，可以分批查詢绍撞。
執(zhí)行一條查詢語句正勒，客戶端跟服務(wù)端建立連接之后呢？下一步要做什么傻铣？

查詢緩存

MySQL 內(nèi)部自帶了一個緩存模塊章贞。
緩存的作用我們應(yīng)該很清楚了，把數(shù)據(jù)以 KV 的形式放到內(nèi)存里面非洲，可以加快數(shù)據(jù)的讀取速度鸭限，也可以減少服務(wù)器處理的時間。但是 MySQL 的緩存我們好像比較陌生两踏，從來 沒有去配置過败京，也不知道它什么時候生效？
比如 user_innodb 有 500 萬行數(shù)據(jù)梦染，沒有索引赡麦。我們在沒有索引的字段上執(zhí)行同樣 的查詢，大家覺得第二次會快嗎帕识？

select * from user_innodb where name='青山';

緩存沒有生效泛粹，為什么？MySQL 的緩存默認是關(guān)閉的渡冻。

show variables like 'query_cache%';

默認關(guān)閉的意思就是不推薦使用戚扳，為什么 MySQL 不推薦使用它自帶的緩存呢？
主要是因為 MySQL 自帶的緩存的應(yīng)用場景有限族吻，第一個是它要求 SQL 語句必須一 模一樣帽借，中間多一個空格，字母大小寫不同都被認為是不同的的 SQL超歌。
第二個是表里面任何一條數(shù)據(jù)發(fā)生變化的時候砍艾，這張表所有緩存都會失效，所以對 于有大量數(shù)據(jù)更新的應(yīng)用巍举，也不適合脆荷。
所以緩存這一塊，我們還是交給 ORM 框架（比如 MyBatis 默認開啟了一級緩存）懊悯， 或者獨立的緩存服務(wù)蜓谋，比如 Redis 來處理更合適。
在 MySQL 8.0 中炭分，查詢緩存已經(jīng)被移除了桃焕。

語法解析和預(yù)處理(Parser & Preprocessor)

我們沒有使用緩存的話，就會跳過緩存的模塊捧毛，下一步我們要做什么呢观堂？
OK让网，這里我會有一個疑問，為什么我的一條 SQL 語句能夠被識別呢师痕？假如我隨便執(zhí)行一 個字符串 penyuyan溃睹，服務(wù)器報了一個 1064 的錯：
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'penyuyan' at line 1
它是怎么知道我輸入的內(nèi)容是錯誤的？
這個就是 MySQL 的 Parser 解析器和 Preprocessor 預(yù)處理模塊胰坟。
這一步主要做的事情是對語句基于 SQL 語法進行詞法和語法分析和語義的解析因篇。

詞法解析

詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。
比如一個簡單的 SQL 語句：

select name from user where id = 1;

它會打碎成 8 個符號腕铸，每個符號是什么類型惜犀，從哪里開始到哪里結(jié)束。

第二步就是語法分析狠裹，語法分析會對 SQL 做一些語法檢查虽界，比如單引號有沒有閉合， 然后根據(jù) MySQL 定義的語法規(guī)則涛菠，根據(jù) SQL 語句生成一個數(shù)據(jù)結(jié)構(gòu)莉御。這個數(shù)據(jù)結(jié)構(gòu)我 們把它叫做解析樹（select_lex）。

image.png

任何數(shù)據(jù)庫的中間件俗冻，比如 Mycat礁叔，Sharding-JDBC（用到了 Druid Parser），都 必須要有詞法和語法分析功能迄薄，在市面上也有很多的開源的詞法解析的工具（比如 LEX琅关，Yacc）。

預(yù)處理器

問題：如果我寫了一個詞法和語法都正確的 SQL讥蔽，但是表名或者字段不存在涣易，會在 哪里報錯？是在數(shù)據(jù)庫的執(zhí)行層還是解析器冶伞？比如：

select * from penyuyan;

解析器可以分析語法新症，但是它怎么知道數(shù)據(jù)庫里面有什么表，表里面有什么字段呢响禽？
實際上還是在解析的時候報錯徒爹，解析 SQL 的環(huán)節(jié)里面有個預(yù)處理器
它會檢查生成的解析樹，解決解析器無法解析的語義芋类。比如隆嗅，它會檢查表和列名是 否存在，檢查名字和別名侯繁，保證沒有歧義胖喳。
預(yù)處理之后得到一個新的解析樹。

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

什么是優(yōu)化器巫击？

得到解析樹之后禀晓，是不是執(zhí)行 SQL 語句了呢？
這里我們有一個問題坝锰，一條 SQL 語句是不是只有一種執(zhí)行方式粹懒？或者說數(shù)據(jù)庫最終 執(zhí)行的 SQL 是不是就是我們發(fā)送的 SQL啄巧？
這個答案是否定的堪澎。一條 SQL 語句是可以有很多種執(zhí)行方式的，最終返回相同的結(jié) 果吟宦，他們是等價的弓颈。但是如果有這么多種執(zhí)行方式帽芽，這些執(zhí)行方式怎么得到的？最終選 擇哪一種去執(zhí)行翔冀？根據(jù)什么判斷標(biāo)準(zhǔn)去選擇导街？
這個就是 MySQL 的查詢優(yōu)化器的模塊（Optimizer）。
查詢優(yōu)化器的目的就是根據(jù)解析樹生成不同的執(zhí)行計劃（Execution Plan）纤子，然后選擇一種最優(yōu)的執(zhí)行計劃搬瑰，MySQL 里面使用的是基于開銷（cost）的優(yōu)化器，那種執(zhí)行計 劃開銷最小控硼，就用哪種泽论。
可以使用這個命令查看查詢的開銷：

show status like 'Last_query_cost';

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Last_query_cost

MySQL 的優(yōu)化器能處理哪些優(yōu)化類型呢？
舉兩個簡單的例子：
1卡乾、當(dāng)我們對多張表進行關(guān)聯(lián)查詢的時候翼悴，以哪個表的數(shù)據(jù)作為基準(zhǔn)表。
2幔妨、有多個索引可以使用的時候鹦赎，選擇哪個索引。
實際上陶冷，對于每一種數(shù)據(jù)庫來說钙姊，優(yōu)化器的模塊都是必不可少的，他們通過復(fù)雜的 算法實現(xiàn)盡可能優(yōu)化查詢效率的目標(biāo)埂伦。
如果對于優(yōu)化器的細節(jié)感興趣煞额，可以看看《數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)-原理解析與SQL 性能優(yōu)化》。

image.png

但是優(yōu)化器也不是萬能的沾谜，并不是再垃圾的 SQL 語句都能自動優(yōu)化膊毁，也不是每次都 能選擇到最優(yōu)的執(zhí)行計劃，大家在編寫 SQL 語句的時候還是要注意
如果我們想知道優(yōu)化器是怎么工作的基跑，它生成了幾種執(zhí)行計劃婚温，每種執(zhí)行計劃的 cost 是多少，應(yīng)該怎么做媳否？

優(yōu)化器是怎么得到執(zhí)行計劃的栅螟？

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
首先我們要啟用優(yōu)化器的追蹤（默認是關(guān)閉的）：

SHOW VARIABLES LIKE 'optimizer_trace'; 
set optimizer_trace='enabled=on';

注意開啟這開關(guān)是會消耗性能的荆秦，因為它要把優(yōu)化分析的結(jié)果寫到表里面，所以不 要輕易開啟力图，或者查看完之后關(guān)閉它（改成 off）步绸。
注意：參數(shù)分為 session 和 global 級別。
接著我們執(zhí)行一個 SQL 語句吃媒，優(yōu)化器會生成執(zhí)行計劃：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

這個時候優(yōu)化器分析的過程已經(jīng)記錄到系統(tǒng)表里面了瓤介，我們可以查詢：

select * from information_schema.optimizer_trace\G

它是一個 JSON 類型的數(shù)據(jù)，主要分成三部分赘那，準(zhǔn)備階段刑桑、優(yōu)化階段和執(zhí)行階段。

image.png

expanded_query 是優(yōu)化后的 SQL 語句募舟。
considered_execution_plans 里面列出了所有的執(zhí)行計劃祠斧。
分析完記得關(guān)掉它：

set optimizer_trace="enabled=off"; 
SHOW VARIABLES LIKE 'optimizer_trace';

優(yōu)化器得到的結(jié)果

優(yōu)化完之后，得到一個什么東西呢拱礁？
優(yōu)化器最終會把解析樹變成一個查詢執(zhí)行計劃梁肿，查詢執(zhí)行計劃是一個數(shù)據(jù)結(jié)構(gòu)。
當(dāng)然觅彰，這個執(zhí)行計劃是不是一定是最優(yōu)的執(zhí)行計劃呢吩蔑？不一定，因為 MySQL 也有可 能覆蓋不到所有的執(zhí)行計劃填抬。
我們怎么查看 MySQL 的執(zhí)行計劃呢烛芬？比如多張表關(guān)聯(lián)查詢，先查詢哪張表飒责？在執(zhí)行 查詢的時候可能用到哪些索引赘娄，實際上用到了什么索引？
MySQL 提供了一個執(zhí)行計劃的工具宏蛉。我們在 SQL 語句前面加上 EXPLAIN遣臼，就可以 看到執(zhí)行計劃的信息。

EXPLAIN select name from user where id=1;

*注意 Explain 的結(jié)果也不一定最終執(zhí)行的方式拾并。

存儲引擎

得到執(zhí)行計劃以后揍堰，SQL 語句是不是終于可以執(zhí)行了？
問題又來了：
1嗅义、從邏輯的角度來說屏歹，我們的數(shù)據(jù)是放在哪里的，或者說放在一個什么結(jié)構(gòu)里面之碗？
2蝙眶、執(zhí)行計劃在哪里執(zhí)行？是誰去執(zhí)行褪那？
我們先回答第一個問題：在關(guān)系型數(shù)據(jù)庫里面幽纷，數(shù)據(jù)是放在什么結(jié)構(gòu)里面的式塌？
（放在表 Table 里面的）
我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在存儲數(shù)據(jù)的同時友浸， 還要組織數(shù)據(jù)的存儲結(jié)構(gòu)珊搀，這個存儲結(jié)構(gòu)就是由我們的存儲引擎決定的，所以我們也可以把存儲引擎叫做表類型尾菇。

在 MySQL 里面，支持多種存儲引擎囚枪，他們是可以替換的派诬，所以叫做插件式的存儲引 擎。為什么要搞這么多存儲引擎呢链沼？一種還不夠用嗎(這個問題先留著)

查看存儲引擎

比如我們數(shù)據(jù)庫里面已經(jīng)存在的表默赂，我們怎么查看它們的存儲引擎呢？

show table status from `gupao`;

image.png

或者通過 DDL 建表語句來查看括勺。
在 MySQL 里面缆八，我們創(chuàng)建的每一張表都可以指定它的存儲引擎，而不是一個數(shù)據(jù)庫 只能使用一個存儲引擎疾捍。存儲引擎的使用是以表為單位的奈辰。而且，創(chuàng)建表之后還可以修 改存儲引擎乱豆。
我們說一張表使用的存儲引擎決定我們存儲數(shù)據(jù)的結(jié)構(gòu)奖恰，那在服務(wù)器上它們是怎么 存儲的呢？我們先要找到數(shù)據(jù)庫存放數(shù)據(jù)的路徑：

show variables like 'datadir';

默認情況下宛裕，每個數(shù)據(jù)庫有一個自己文件夾瑟啃，以 gupao 數(shù)據(jù)庫為例。
任何一個存儲引擎都有一個 frm 文件揩尸，這個是表結(jié)構(gòu)定義文件

image.png

常見存儲引擎

MyISAM 和 InnoDB 是我們用得最多的兩個存儲引擎湿颅，在 MySQL 5.5 版本之前， 默認的存儲引擎是 MyISAM粥诫，它是 MySQL 自帶的油航。我們創(chuàng)建表的時候不指定存儲引擎， 它就會使用 MyISAM 作為存儲引擎怀浆。
MyISAM 的前身是 ISAM（Indexed Sequential Access Method：利用索引谊囚，順序 存取數(shù)據(jù)的方法）怕享。
5.5 版本之后默認的存儲引擎改成了 InnoDB，它是第三方公司為 MySQL 開發(fā)的镰踏。 為什么要改呢函筋？最主要的原因還是 InnoDB 支持事務(wù)，支持行級別的鎖奠伪，對于業(yè)務(wù)一致 性要求高的場景來說更適合跌帐。
這個里面又有 Oracle 和 MySQL 公司的一段恩怨情仇。
InnoDB 本來是 InnobaseOy 公司開發(fā)的绊率，它和 MySQL AB 公司合作開源了 InnoDB 的代碼谨敛。但是沒想到 MySQL 的競爭對手 Oracle把 InnobaseOy 收購了。
后來 08 年 Sun 公司（開發(fā) Java 語言的 Sun）收購了 MySQL AB滤否，09 年 Sun 公司 又被 Oracle 收購了脸狸，所以 MySQL，InnoDB 又是一家了藐俺。有人覺得 MySQL 越來越像 Oracle炊甲，其實也是這個原因。

image.png

那么除了這兩個我們最熟悉的存儲引擎欲芹，數(shù)據(jù)庫還支持其他哪些常用的存儲引擎 呢卿啡？

數(shù)據(jù)庫支持的存儲引擎

我們可以用這個命令查看數(shù)據(jù)庫對存儲引擎的支持情況：

show engines ;

其中有存儲引擎的描述和對事務(wù)、XA 協(xié)議和 Savepoints 的支持菱父。
XA 協(xié)議用來實現(xiàn)分布式事務(wù)（分為本地資源管理器牵囤，事務(wù)管理器）。
Savepoints 用來實現(xiàn)子事務(wù)（嵌套事務(wù)）滞伟。創(chuàng)建了一個 Savepoints 之后揭鳞，事務(wù) 就可以回滾到這個點，不會影響到創(chuàng)建 Savepoints 之前的操作梆奈。

image.png

這些數(shù)據(jù)庫支持的存儲引擎野崇，分別有什么特性呢？
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

MyISAM（3 個文件）

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.
應(yīng)用范圍比較小亩钟。表級鎖定限制了讀/寫的性能乓梨，因此在 Web 和數(shù)據(jù)倉庫配置中， 它通常用于只讀或以讀為主的工作
特點：
支持表級別的鎖（插入和更新會鎖表）清酥。不支持事務(wù)扶镀。
擁有較高的插入（insert）和查詢（select）速度。
存儲了表的行數(shù)（count 速度更快）焰轻。

（怎么快速向數(shù)據(jù)庫插入 100 萬條數(shù)據(jù)臭觉？我們有一種先用 MyISAM 插入數(shù)據(jù)，然后 修改存儲引擎為 InnoDB 的操作。）

適合：只讀之類的數(shù)據(jù)分析的項目蝠筑。

InnoDB（2 個文件）

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
mysql 5.7 中的默認存儲引擎狞膘。InnoDB 是一個事務(wù)安全（與 ACID 兼容）的 MySQL 存儲引擎，它具有提交什乙、回滾和崩潰恢復(fù)功能來保護用戶數(shù)據(jù)挽封。InnoDB 行級鎖（不升級 為更粗粒度的鎖）和 Oracle 風(fēng)格的一致非鎖讀提高了多用戶并發(fā)性和性能。InnoDB 將 用戶數(shù)據(jù)存儲在聚集索引中臣镣，以減少基于主鍵的常見查詢的 I/O辅愿。為了保持?jǐn)?shù)據(jù)完整性，InnoDB 還支持外鍵引用完整性約束忆某。
特點：
支持事務(wù)点待，支持外鍵，因此數(shù)據(jù)的完整性褒繁、一致性更高。
支持行級別的鎖和表級別的鎖馍忽。
支持讀寫并發(fā)棒坏，寫不阻塞讀（MVCC）。
特殊的索引存放方式遭笋，可以減少 IO坝冕，提升查詢效率。
適合：經(jīng)常更新的表瓦呼，存在并發(fā)讀寫或者有事務(wù)處理的業(yè)務(wù)系統(tǒng)喂窟。

Memory（1 個文件）

將所有數(shù)據(jù)存儲在 RAM 中，以便在需要快速查找非關(guān)鍵數(shù)據(jù)的環(huán)境中快速訪問央串。這 個引擎以前被稱為堆引擎磨澡。其使用案例正在減少；InnoDB 及其緩沖池內(nèi)存區(qū)域提供了一 種通用质和、持久的方法來將大部分或所有數(shù)據(jù)保存在內(nèi)存中稳摄，而 ndbcluster 為大型分布式 數(shù)據(jù)集提供了快速的鍵值查找。
特點：
把數(shù)據(jù)放在內(nèi)存里面饲宿，讀寫的速度很快厦酬，但是數(shù)據(jù)庫重啟或者崩潰，數(shù)據(jù)會全部消 失瘫想。只適合做臨時表仗阅。
將表中的數(shù)據(jù)存儲到內(nèi)存中

CSV（3 個文件）

它的表實際上是帶有逗號分隔值的文本文件。csv表允許以csv格式導(dǎo)入或轉(zhuǎn)儲數(shù)據(jù)国夜， 以便與讀寫相同格式的腳本和應(yīng)用程序交換數(shù)據(jù)减噪。因為 csv 表沒有索引，所以通常在正 常操作期間將數(shù)據(jù)保存在 innodb 表中，并且只在導(dǎo)入或?qū)С鲭A段使用 csv 表旋廷。
特點：不允許空行鸠按，不支持索引。格式通用饶碘，可以直接編輯目尖，適合在不同數(shù)據(jù)庫之 間導(dǎo)入導(dǎo)出

Archive（2 個文件）

這些緊湊的未索引的表用于存儲和檢索大量很少引用的歷史、存檔或安全審計信息扎运。
特點：不支持索引瑟曲，不支持 update delete。
這是 MySQL 里面常見的一些存儲引擎豪治，我們看到了洞拨，不同的存儲引擎提供的特性都 不一樣，它們有不同的存儲機制负拟、索引方式烦衣、鎖定水平等功能
我們在不同的業(yè)務(wù)場景中對數(shù)據(jù)操作的要求不同，就可以選擇不同的存儲引擎來滿 足我們的需求掩浙，這個就是 MySQL 支持這么多存儲引擎的原因花吟。

如何選擇存儲引擎？

如果對數(shù)據(jù)一致性要求比較高厨姚，需要事務(wù)支持衅澈，可以選擇 InnoDB。
如果數(shù)據(jù)查詢多更新少谬墙，對查詢性能要求比較高今布，可以選擇 MyISAM。
如果需要一個用于查詢的臨時表拭抬，可以選擇 Memory部默。
如果所有的存儲引擎都不能滿足你的需求，并且技術(shù)能力足夠造虎，可以根據(jù)官網(wǎng)內(nèi)部 手冊用 C 語言開發(fā)一個存儲引擎：https://dev.mysql.com/doc/internals/en/custom-engine.html

執(zhí)行引擎（Query Execution Engine）甩牺，返回結(jié)果

OK，存儲引擎分析完了累奈，它是我們存儲數(shù)據(jù)的形式贬派，繼續(xù)第二個問題，是誰使用執(zhí) 行計劃去操作存儲引擎呢澎媒？
這就是我們的執(zhí)行引擎搞乏，它利用存儲引擎提供的相應(yīng)的 API 來完成操作。
為什么我們修改了表的存儲引擎戒努，操作方式不需要做任何改變请敦？因為不同功能的存 儲引擎實現(xiàn)的 API 是相同的镐躲。
最后把數(shù)據(jù)返回給客戶端，即使沒有結(jié)果也要返回侍筛。

一條更新 SQL 是如何執(zhí)行的萤皂？

講完了查詢流程，我們是不是再講講更新流程匣椰、插入流程和刪除流程裆熙？
在數(shù)據(jù)庫里面，我們說的 update 操作其實包括了更新禽笑、插入和刪除入录。如果大家有看 過 MyBatis 的源碼，應(yīng)該知道 Executor 里面也只有 doQuery()和 doUpdate()的方法佳镜， 沒有 doDelete()和 doInsert()僚稿。
更新流程和查詢流程有什么不同呢？
基本流程也是一致的蟀伸，也就是說蚀同，它也要經(jīng)過解析器、優(yōu)化器的處理啊掏，最后交給執(zhí) 行器蠢络。
區(qū)別就在于拿到符合條件的數(shù)據(jù)之后的操作。

緩沖池 Buffer Pool

首先脖律，InnnoDB 的數(shù)據(jù)都是放在磁盤上的谢肾，InnoDB 操作數(shù)據(jù)有一個最小的邏輯單 位腕侄，叫做頁（索引頁和數(shù)據(jù)頁）小泉。我們對于數(shù)據(jù)的操作，不是每次都直接操作磁盤冕杠，因 為磁盤的速度太慢了微姊。InnoDB 使用了一種緩沖池的技術(shù)，也就是把磁盤讀到的頁放到一 塊內(nèi)存區(qū)域里面分预。這個內(nèi)存區(qū)域就叫 Buffer Pool

image.png

InnoDB 內(nèi)存結(jié)構(gòu)和磁盤結(jié)構(gòu)

image.png

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

Buffer Pool

Buffer Pool 緩存的是頁面信息惠毁，包括數(shù)據(jù)頁、索引頁
查看服務(wù)器狀態(tài)崎页，里面有很多跟 Buffer Pool 相關(guān)的信息：

SHOW STATUS LIKE '%innodb_buffer_pool%';

這些狀態(tài)都可以在官網(wǎng)查到詳細的含義鞠绰，用搜索功能。 https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

image.png

Buffer Pool 默認大小是 128M（134217728 字節(jié)）飒焦，可以調(diào)整蜈膨。
查看參數(shù)（系統(tǒng)變量）：

SHOW VARIABLES like '%innodb_buffer_pool%'

這些參數(shù)都可以在官網(wǎng)查到詳細的含義，用搜索功能牺荠。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

image.png

Change Buffer 寫緩沖

如果這個數(shù)據(jù)頁不是唯一索引，不存在數(shù)據(jù)重復(fù)的情況卜壕，也就不需要從磁盤加載索 引頁判斷數(shù)據(jù)是不是重復(fù)（唯一性檢查）您旁。這種情況下可以先把修改記錄在內(nèi)存的緩沖 池中，從而提升更新語句（Insert印叁、Delete被冒、Update）的執(zhí)行速度军掂。
這一塊區(qū)域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入緩沖昨悼，現(xiàn)在也能支 持 delete 和 update蝗锥。
最后把 Change Buffer 記錄到數(shù)據(jù)頁的操作叫做 merge。什么時候發(fā)生 merge率触？ 有幾種情況：在訪問這個數(shù)據(jù)頁的時候终议，或者通過后臺線程、或者數(shù)據(jù)庫 shut down葱蝗、 redo log 寫滿時觸發(fā)
如果數(shù)據(jù)庫大部分索引都是非唯一索引穴张，并且業(yè)務(wù)是寫多讀少，不會在寫數(shù)據(jù)后立 刻讀取两曼，就可以使用 Change Buffer（寫緩沖）皂甘。寫多讀少的業(yè)務(wù)，調(diào)大這個值：

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'

代表 Change Buffer 占 Buffer Pool 的比例悼凑，默認 25%\

Adaptive Hash Index

索引應(yīng)該是放在磁盤的偿枕，為什么要專門把一種哈希的索引放到內(nèi)存？下次課再說户辫。

（redo）Log Buffer

思考一個問題：如果 Buffer Pool 里面的臟頁還沒有刷入磁盤時渐夸，數(shù)據(jù)庫宕機或者重 啟，這些數(shù)據(jù)丟失渔欢。如果寫操作寫到一半墓塌，甚至可能會破壞數(shù)據(jù)文件導(dǎo)致數(shù)據(jù)庫不可用。
為了避免這個問題奥额，InnoDB 把所有對頁面的修改操作專門寫入一個日志文件苫幢，并且 在數(shù)據(jù)庫啟動時從這個文件進行恢復(fù)操作（實現(xiàn) crash-safe）——用它來實現(xiàn)事務(wù)的持久性

image.png

show variables like 'innodb_log%';

image.png

image.png

image.png

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

需要注意：redo log 的內(nèi)容主要是用于崩潰恢復(fù)妒貌。磁盤的數(shù)據(jù)文件，數(shù)據(jù)來自 buffer pool铸豁。redo log 寫入磁盤灌曙，不是寫入數(shù)據(jù)文件。
那么节芥，Log Buffer 什么時候?qū)懭?log file在刺？
在我們寫入數(shù)據(jù)到磁盤的時候，操作系統(tǒng)本身是有緩存的头镊。flush 就是把操作系統(tǒng)緩 沖區(qū)寫入到磁盤蚣驼。
log buffer 寫入磁盤的時機，由一個參數(shù)控制相艇，默認是 1颖杏。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

image.png

image.png

image.png

這是 MySQL 的內(nèi)存結(jié)構(gòu)尤勋，總結(jié)一下喘落，分為： Buffer pool、change buffer最冰、Adaptive Hash Index瘦棋、 log buffer。
磁盤結(jié)構(gòu)里面主要是各種各樣的表空間暖哨，叫做 Table space

磁盤結(jié)構(gòu)

表空間可以看做是 InnoDB 存儲引擎邏輯結(jié)構(gòu)的最高層赌朋，所有的數(shù)據(jù)都存放在表空 間中。InnoDB 的表空間分為 5 大類篇裁。

系統(tǒng)表空間 system tablespace

在默認情況下 InnoDB 存儲引擎有一個共享表空間（對應(yīng)文件/var/lib/mysql/ ibdata1）沛慢，也叫系統(tǒng)表空間。
InnoDB 系統(tǒng)表空間包含 InnoDB 數(shù)據(jù)字典和雙寫緩沖區(qū)达布，Change Buffer 和 Undo Logs）团甲，如果沒有指定 file-per-table，也包含用戶創(chuàng)建的表和索引數(shù)據(jù)黍聂。

1躺苦、undo 在后面介紹，因為有獨立的表空間分冈。
2圾另、數(shù)據(jù)字典：由內(nèi)部系統(tǒng)表組成霸株，存儲表和索引的元數(shù)據(jù)（定義信息）雕沉。
3、雙寫緩沖（InnoDB 的一大特性）：
InnoDB 的頁和操作系統(tǒng)的頁大小不一致去件，InnoDB 頁大小一般為 16K坡椒，操作系統(tǒng)頁 大小為 4K扰路，InnoDB 的頁寫入到磁盤時，一個頁需要分 4 次寫倔叼。

image.png

如果存儲引擎正在寫入頁的數(shù)據(jù)到磁盤時發(fā)生了宕機汗唱，可能出現(xiàn)頁只寫了一部分的 情況，比如只寫了 4K丈攒，就宕機了哩罪，這種情況叫做部分寫失效（partial page write），可 能會導(dǎo)致數(shù)據(jù)丟失巡验。

show variables like 'innodb_doublewrite';

我們不是有 redo log 嗎际插？但是有個問題，如果這個頁本身已經(jīng)損壞了显设，用它來做崩 潰恢復(fù)是沒有意義的框弛。所以在對于應(yīng)用 redo log 之前，需要一個頁的副本捕捂。如果出現(xiàn)了 寫入失效瑟枫，就用頁的副本來還原這個頁，然后再應(yīng)用 redo log指攒。這個頁的副本就是 double write慷妙，InnoDB 的雙寫技術(shù)。通過它實現(xiàn)了數(shù)據(jù)頁的可靠性
跟 redo log 一樣允悦，double write 由兩部分組成景殷，一部分是內(nèi)存的 double write，一個部分是磁盤上的 double write澡屡。因為 double write 是順序?qū)懭氲脑持浚粫砗艽蟮?開銷
在默認情況下，所有的表共享一個系統(tǒng)表空間驶鹉，這個文件會越來越大绩蜻，而且它的空 間不會收縮

獨占表空間 file-per-table tablespaces

我們可以讓每張表獨占一個表空間。這個開關(guān)通過 innodb_file_per_table 設(shè)置室埋，默 認開啟

SHOW VARIABLES LIKE 'innodb_file_per_table';

開啟后办绝，則每張表會開辟一個表空間，這個文件就是數(shù)據(jù)目錄下的 ibd 文件（例如 /var/lib/mysql/gupao/user_innodb.ibd）姚淆，存放表的索引和數(shù)據(jù)
但是其他類的數(shù)據(jù)孕蝉，如回滾（undo）信息，插入緩沖索引頁腌逢、系統(tǒng)事務(wù)信息降淮，二次 寫緩沖（Double write buffer）等還是存放在原來的共享表空間內(nèi)

通用表空間 general tablespaces

通用表空間也是一種共享的表空間，跟 ibdata1 類似
可以創(chuàng)建一個通用的表空間搏讶，用來存儲不同數(shù)據(jù)庫的表佳鳖，數(shù)據(jù)路徑和文件可以自定 義霍殴。語法：

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

在創(chuàng)建表的時候可以指定表空間，用 ALTER 修改表空間可以轉(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)數(shù)據(jù)服務(wù)器正常關(guān)閉時科盛，該表空間被刪除尊搬，下次重新產(chǎn)生

Redo log

磁盤結(jié)構(gòu)里面的 redo log，在前面已經(jīng)介紹過了

undo log tablespace

https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-logs.html
undo log（撤銷日志或回滾日志）記錄了事務(wù)發(fā)生之前的數(shù)據(jù)狀態(tài)（不包括 select）土涝。 如果修改數(shù)據(jù)時出現(xiàn)異常佛寿，可以用 undo log 來實現(xiàn)回滾操作（保持原子性）。
在執(zhí)行 undo 的時候但壮，僅僅是將數(shù)據(jù)從邏輯上恢復(fù)至事務(wù)之前的狀態(tài)冀泻，而不是從物 理頁面上操作實現(xiàn)的，屬于邏輯格式的日志
redo Log 和 undo Log 與事務(wù)密切相關(guān)蜡饵，統(tǒng)稱為事務(wù)日志弹渔。
undo Log 的數(shù)據(jù)默認在系統(tǒng)表空間 ibdata1 文件中，因為共享表空間不會自動收 縮溯祸，也可以單獨創(chuàng)建一個 undo 表空間肢专。

show global variables like '%undo%';

有了這些日志之后，我們來總結(jié)一下一個更新操作的流程焦辅，這是一個簡化的過程博杖。 name 原值是 qingshan。

update user set name = 'penyuyan' where id=1;

1筷登、事務(wù)開始剃根，從內(nèi)存或磁盤取到這條數(shù)據(jù)，返回給 Server 的執(zhí)行器前方；
2狈醉、執(zhí)行器修改這一行數(shù)據(jù)的值為 penyuyan；
3惠险、記錄 name=qingshan 到 undo log苗傅；
4、記錄 name=penyuyan 到 redo log班巩；
5渣慕、調(diào)用存儲引擎接口，在內(nèi)存（Buffer Pool）中修改 name=penyuyan；
6摇庙、事務(wù)提交旱物。
內(nèi)存和磁盤之間遥缕，工作著很多后臺線程卫袒。

后臺線程

（供了解）
后臺線程的主要作用是負責(zé)刷新內(nèi)存池中的數(shù)據(jù)和把修改的數(shù)據(jù)頁刷新到磁盤。后 臺線程分為：master thread单匣，IO thread夕凝，purge thread，page cleaner thread户秤。
master thread 負責(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 用來刷新臟頁
除了 InnoDB 架構(gòu)中的日志文件，MySQL 的 Server 層也有一個日志文件汞窗，叫做 binlog姓赤，它可以被所有的存儲引擎使用。

Binlog

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html
binlog 以事件的形式記錄了所有的 DDL 和 DML 語句（因為它記錄的是操作而不是 數(shù)據(jù)值仲吏，屬于邏輯日志）不铆，可以用來做主從復(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í)行的:

image.png

這張圖片的重點
1翔悠、先記錄到內(nèi)存，再寫日志文件野芒。
2蓄愁、記錄 redo log 分為兩個階段。
3复罐、存儲引擎和 Server 記錄不同的日志
4涝登、先記錄 redo，再記錄 binlog效诅。

——學(xué)自咕泡學(xué)院