高達(dá) 00 人物集合

目錄：

• 1、一個(gè)查詢語句SQL是如何執(zhí)行的？

  • 1.1、通信協(xié)議

    • 通信類型：同步 or 者異步

    • 連接方式

    • 通信協(xié)議

  • 1.2.、查詢緩存（Query Cache）

  • 1.3、語法解析和預(yù)處理

    • 1.3.1、詞法解析

    • 1.3.2、語法解析

    • 1.3.3、預(yù)處理器

  • 1.4、查詢優(yōu)化(Query Optimizer)與查詢執(zhí)行計(jì)劃

    • 1.4.1、什么是優(yōu)化器?

    • 1.4.2、優(yōu)化器可以做什么?

    • 1.4.3.優(yōu)化器是怎么得到執(zhí)行計(jì)劃的?

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

  • 1.5、存儲(chǔ)引擎

    • 1.5.1、存儲(chǔ)引擎基本介紹

    • 1.5.2、查看存儲(chǔ)引擎

    • 1.5.3、存儲(chǔ)引擎比較

    • 1.5.4、如何選擇存儲(chǔ)引擎?

  • 1.6、執(zhí)行引擎

• 思考??：MySQL 服務(wù)允許的最大連接數(shù)是多少呢?

• 思考??：MySQL 使用了半雙工的通信方式?

• 思考??問題：默認(rèn)關(guān)閉的意思就是不推薦使用，為什么 MySQL 不推薦使用它自帶的緩存呢?

• 思考??：優(yōu)化完之后，得到一個(gè)什么東西呢?

• 思考??：得到執(zhí)行計(jì)劃以后，SQL 語句是不是終于可以執(zhí)行了?問題又來了:1、從邏輯的角度來說，數(shù)據(jù)是放在哪里的，或者說放在一個(gè)什么結(jié)構(gòu)里面? 2、執(zhí)行計(jì)劃在哪里執(zhí)行? 是誰去執(zhí)行?

• 思考??：是誰使用執(zhí)行計(jì)劃去操作存儲(chǔ)引擎呢?

• 2、MySQL體系結(jié)構(gòu)總結(jié)

  • 2.1. 模塊詳解

  • 2.2. 架構(gòu)分層

    • 2.2.1、連接層

    • 2.1.2、服務(wù)層

    • 2.1.3、存儲(chǔ)引擎

• 3、一條更新SQL是如何執(zhí)行的?

  • 3.1. 緩沖池 Buffer Pool

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

    • 3.2.1.內(nèi)存結(jié)構(gòu)

      • 1、Buffer Pool

      • 2、Change Buffer 寫緩沖

      • 3、Adaptive Hash Index

      • 4、(redo)Log Buffer

    • 3.2.2、磁盤結(jié)構(gòu)

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

      • b、獨(dú)占表空間 file-per-table tablespaces

      • c、通用表空間 general tablespaces

      • d、臨時(shí)表空間 temporary tablespaces

      • e、undo log tablespace

    • 3.2.3、后臺(tái)線程

  • 思考??問題：內(nèi)存的緩沖池寫滿了怎么辦?(Redis 設(shè)置的內(nèi)存滿了怎么辦?)

  • 思考??問題：思考一個(gè)問題: 當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在 Buffer Pool 中存在，那么就直接更新好了。 否則的話就需要從磁盤加載到內(nèi)存，再對(duì)內(nèi)存的數(shù)據(jù)頁進(jìn)行操作。也就是說，如果沒有命中緩沖池，至少要產(chǎn)生一次磁盤 IO，有沒有優(yōu)化的方式呢?

  • 思考??問題：同樣是寫磁盤，為什么不直接寫到 db file 里面去?為什么先寫日志再寫磁盤?

  • 思考??問題：有了這些日志之后，總結(jié)一下一個(gè)更新操作的流程？

  • 3.3. Binlog

1、一個(gè)查詢語句SQL是如何執(zhí)行的？

SQL執(zhí)行過程

1.1、 通信協(xié)議

MySQL 是支持多種通信協(xié)議的，可以使用同步/異步的通訊類型，支持長連接/短連接。

• a、通信類型：同步 or 者異步

  • 同步通信的特點(diǎn)：
    • 1、同步通信依賴于被調(diào)用方，受限于被調(diào)用方的性能。應(yīng)用服務(wù)器操作數(shù)據(jù)庫，線程會(huì)阻塞，等待數(shù)據(jù)庫的返回。(常用的方式)
    • 2、一般只能做到一對(duì)一，很難做到一對(duì)多的通信。
  • 異步通訊的特點(diǎn):
    • 1、異步可以避免應(yīng)用阻塞等待，但是不能節(jié)省 SQL 執(zhí)行的時(shí)間。
    • 2、如果異步存在并發(fā)，每一個(gè) SQL 的執(zhí)行都要單獨(dú)建立一個(gè)連接，避免數(shù)據(jù)混亂。 但是這樣會(huì)給服務(wù)端帶來巨大的壓力(一個(gè)連接就會(huì)創(chuàng)建一個(gè)線程，線程間切換會(huì)占用 大量 CPU 資源)。另外異步通信還帶來了編碼的復(fù)雜度，所以一般不建議使用。如果要異步，必須使用連接池，排隊(duì)從連接池獲取連接而不是創(chuàng)建新連接。

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

• b、連接方式：
  MySQL 既支持短連接，也支持長連接。

  • 短連接就是操作完畢以后，馬上 close 掉。
  • 長連接可以保持打開，減少服務(wù)端創(chuàng)建和釋放連接的消耗，后面的程序訪問的時(shí)候還可以使用這個(gè)連接。默認(rèn)會(huì)在連接池中使用長連接。

  保持長連接會(huì)消耗內(nèi)存。長時(shí)間不活動(dòng)的連接，MySQL 服務(wù)器會(huì)斷開。(默認(rèn)都是 28800 秒，8 小時(shí))

## 查看交互超時(shí)時(shí)間
show global variables like 'wait_timeout'; -- 非交互式超時(shí)時(shí)間，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超時(shí)時(shí)間，如數(shù)據(jù)庫工具
## 查看 MySQL 當(dāng)前有多少個(gè)連接?
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ù)。

【思考??：MySQL 服務(wù)允許的最大連接數(shù)是多少呢?】

【答案】： 在 5.7 版本中默認(rèn)是 151 個(gè)，最大可以設(shè)置成 16384(2^14)。

• c、通信協(xié)議：

  
  
  通信協(xié)議
  • 1、單工：
    在兩臺(tái)計(jì)算機(jī)通信的時(shí)候，數(shù)據(jù)的傳輸是單向的。
    生活中的類比:遙控器。
  • 2、半雙工：
    在兩臺(tái)計(jì)算機(jī)之間，數(shù)據(jù)傳輸是雙向的，你可以給我發(fā)送，我也可以給你發(fā)送，
    但是在這個(gè)通訊連接里面，同一時(shí)間只能有一臺(tái)服務(wù)器在發(fā)送數(shù)據(jù)，也就是你要給我發(fā)的話，也必須等我發(fā)給你完了之后才能給我發(fā)。
    生活中的類比:對(duì)講機(jī)。
  • 3、全雙工：
    數(shù)據(jù)的傳輸是雙向的，并且可以同時(shí)傳輸。
    生活中的類比:打電話。

【思考??：MySQL 使用了半雙工的通信方式?】

【答案】：要么是客戶端向服務(wù)端發(fā)送數(shù)據(jù)，要么是服務(wù)端向客戶端發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。所以客戶端發(fā)送 SQL 語句給服務(wù)端的時(shí)候，(在一次連接里面)數(shù)據(jù)是不能分成小塊發(fā)送的，不管你的 SQL 語句有多大，都是一次性發(fā)送。
比如用 MyBatis 動(dòng)態(tài) SQL 生成了一個(gè)批量插入的語句，插入 10 萬條數(shù)據(jù)，values 后面跟了一長串的內(nèi)容，或者 where 條件 in 里面的值太多，會(huì)出現(xiàn)問題。這個(gè)時(shí)候必須要調(diào)整 MySQL 服務(wù)器配置 max_allowed_packet 參數(shù)的值(默認(rèn) 是 4M)，把它調(diào)大，否則就會(huì)報(bào)錯(cuò)。
另一方面，對(duì)于MySql服務(wù)端來說，也是一次性發(fā)送所有的數(shù)據(jù)，不能因?yàn)槟阋呀?jīng)取到了想要的數(shù)據(jù)就中斷操作，這個(gè)時(shí)候會(huì)對(duì)網(wǎng)絡(luò)和內(nèi)存產(chǎn)生大量消耗。
所以，一定要在程序里面避免不帶 limit 的這種操作，比如一次把所有滿足條件 的數(shù)據(jù)全部查出來，一定要先 count 一下。如果數(shù)據(jù)量的話，可以分批查詢。

1.2.、查詢緩存（Query Cache）：

MySQL 內(nèi)部自帶了一個(gè)緩存模塊。
緩存的作用：把數(shù)據(jù)以 KV 的形式放到內(nèi)存里面，可以加快數(shù)據(jù)的讀取速度，也可以減少服務(wù)器處理的時(shí)間。但是 MySQL 的緩存比較陌生，從來沒有去配置過，也不知道它什么時(shí)候生效?
MySQL 的緩存默認(rèn)是關(guān)閉的。
【思考??問題：默認(rèn)關(guān)閉的意思就是不推薦使用，為什么 MySQL 不推薦使用它自帶的緩存呢?】
【答案】：**主要是因?yàn)?MySQL 自帶的緩存的應(yīng)用場(chǎng)景有限：

• 第一個(gè)是它要求 SQL 語句必須一 模一樣，中間多一個(gè)空格，字母大小寫不同都被認(rèn)為是不同的的 SQL。
• 第二個(gè)是表里面任何一條數(shù)據(jù)發(fā)生變化的時(shí)候，這張表所有緩存都會(huì)失效，所以對(duì)于有大量數(shù)據(jù)更新的應(yīng)用，也不適合。
  所以緩存這一塊，還是交給 ORM 框架(比如 MyBatis 默認(rèn)開啟了一級(jí)緩存)， 或者獨(dú)立的緩存服務(wù)，比如 Redis 來處理更合適。

在 MySQL 8.0 中，查詢緩存已經(jīng)被移除了。

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

• 1.3.1、詞法解析
  詞法分析就是把一個(gè)完整的 SQL 語句打碎成一個(gè)個(gè)的單詞。
• 1.3.2、語法解析
  語法分析會(huì)對(duì) SQL 做一些語法檢查，比如單引號(hào)有沒有閉合， 然后根據(jù) MySQL 定義的語法規(guī)則，根據(jù) SQL 語句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)。這個(gè)數(shù)據(jù)結(jié)構(gòu)我 們把它叫做解析樹(select_lex)。
  
  解析樹
• 1.3.3、預(yù)處理器

【思考??】：如果寫了一個(gè)詞法和語法都正確的 SQL，但是表名或者字段不存在，會(huì)在哪里報(bào)錯(cuò)? 是在數(shù)據(jù)庫的執(zhí)行層還是解析器?

select * from balabala;

【答案】：解析器可以分析語法，但是它怎么知道數(shù)據(jù)庫里面有什么表，表里面有什么字段呢? 實(shí)際上還是在解析的時(shí)候報(bào)錯(cuò)，解析 SQL 的環(huán)節(jié)里面有個(gè)預(yù)處理器。 它會(huì)檢查生成的解析樹，解決解析器無法解析的語義。比如，它會(huì)檢查表和列名是
否存在，檢查名字和別名，保證沒有歧義。 預(yù)處理之后得到一個(gè)新的解析樹。

1.4、查詢優(yōu)化(Query Optimizer)與查詢執(zhí)行計(jì)劃：

• 1.4.1、什么是優(yōu)化器?
  查詢優(yōu)化器的目的：就是根據(jù)解析樹生成不同的執(zhí)行計(jì)劃(Execution Plan)，然后選擇一種最優(yōu)的執(zhí)行計(jì)劃，MySQL 里面使用的是基于開銷(cost)的優(yōu)化器，哪種執(zhí)行計(jì)劃開銷最小，就用哪種。
• 1.4.2、優(yōu)化器可以做什么?
  MySQL 的優(yōu)化器能處理哪些優(yōu)化類型呢? 舉兩個(gè)簡單的例子:
  1、對(duì)多張表進(jìn)行關(guān)聯(lián)查詢的時(shí)候，以哪個(gè)表的數(shù)據(jù)作為基準(zhǔn)表。
  2、有多個(gè)索引可以使用的時(shí)候，選擇哪個(gè)索引。 實(shí)際上，對(duì)于每一種數(shù)據(jù)庫來說，優(yōu)化器的模塊都是必不可少的，他們通過復(fù)雜的算法實(shí)現(xiàn)盡可能優(yōu)化查詢效率的目標(biāo)。 如果對(duì)于優(yōu)化器的細(xì)節(jié)感興趣，可以看看《數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)-原理解析與 SQL性能優(yōu)化》。
• 1.4.3.優(yōu)化器是怎么得到執(zhí)行計(jì)劃的?
  首先要啟用優(yōu)化器的追蹤(默認(rèn)是關(guān)閉的):
  注意?。?！開啟這開關(guān)是會(huì)消耗性能，因?yàn)樗褍?yōu)化分析的結(jié)果寫到表里面，所以不要輕易開啟，或者查看完之后關(guān)閉它(改成 off)。
  注意:參數(shù)分為 session 和 global 級(jí)別。 接著執(zhí)行一個(gè) SQL 語句，優(yōu)化器會(huì)生成執(zhí)行計(jì)劃:

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

這個(gè)時(shí)候優(yōu)化器分析的過程已經(jīng)記錄到系統(tǒng)表里面了，可以查詢:

select * from information_schema.optimizer_trace\G

分析完記得關(guān)掉它：

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

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

【思考??問題】：優(yōu)化完之后，得到一個(gè)什么東西呢?

【答案】：優(yōu)化器最終會(huì)把解析樹變成一個(gè)查詢執(zhí)行計(jì)劃，查詢執(zhí)行計(jì)劃是一個(gè)數(shù)據(jù)結(jié)構(gòu)。 這個(gè)執(zhí)行計(jì)劃是不是一定是最優(yōu)的執(zhí)行計(jì)劃呢? 不一定，因?yàn)?MySQL 也有可能覆蓋不到所有的執(zhí)行計(jì)劃。
怎么查看 MySQL 的執(zhí)行計(jì)劃呢?
比如多張表關(guān)聯(lián)查詢，先查詢哪張表?在執(zhí)行查詢的時(shí)候可能用到哪些索引，實(shí)際上用到了什么索引?
MySQL 提供了一個(gè)執(zhí)行計(jì)劃的工具。在 SQL 語句前面加上 EXPLAIN，就可以看到執(zhí)行計(jì)劃的信息。

EXPLAIN select name from user where id=1;

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

1.5、存儲(chǔ)引擎：

【思考??】得到執(zhí)行計(jì)劃以后，SQL 語句是不是終于可以執(zhí)行了?問題又來了:1、從邏輯的角度來說，數(shù)據(jù)是放在哪里的，或者說放在一個(gè)什么結(jié)構(gòu)里面? 2、執(zhí)行計(jì)劃在哪里執(zhí)行? 是誰去執(zhí)行?

接下來時(shí)點(diǎn)給與解答：

• 1.5.1、存儲(chǔ)引擎基本介紹
  關(guān)系型數(shù)據(jù)庫里面，數(shù)據(jù)是放在什么結(jié)構(gòu)里面的? (放在表 Table 里面的)。表在存儲(chǔ)數(shù)據(jù)的同時(shí)，還要組織數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)，這個(gè)存儲(chǔ)結(jié)構(gòu)就是由存儲(chǔ)引擎決定的，所以也可以把存儲(chǔ)引擎叫做表類型。
  在 MySQL 里面，支持多種存儲(chǔ)引擎，他們是可以替換的，所以叫做插件式的存儲(chǔ)引擎。
• 1.5.2、查看存儲(chǔ)引擎
  數(shù)據(jù)庫里面已經(jīng)存在的表，怎么查看它們的存儲(chǔ)引擎呢?
  通過sql or DDL 建表語句來查看。

show table status from `student`;

在 MySQL 里面，創(chuàng)建的每一張表都可以指定它的存儲(chǔ)引擎，而不是一個(gè)數(shù)據(jù)庫 只能使用一個(gè)存儲(chǔ)引擎。存儲(chǔ)引擎的使用是以表為單位的。而且，創(chuàng)建表之后還可以修改存儲(chǔ)引擎。
一張表使用的存儲(chǔ)引擎決定存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，那在服務(wù)器上它們是怎么存儲(chǔ)的呢?
首先要找到數(shù)據(jù)庫存放數(shù)據(jù)的路徑:

show variables like 'datadir';

• 1.5.3、存儲(chǔ)引擎比較
  MyISAM 和 InnoDB 是用得最多的兩個(gè)存儲(chǔ)引擎，在 MySQL 5.5 版本之前，默認(rèn)的存儲(chǔ)引擎是 MyISAM，它是 MySQL 自帶的。創(chuàng)建表的時(shí)候不指定存儲(chǔ)引擎， 它就會(huì)使用 MyISAM 作為存儲(chǔ)引擎。
  MyISAM 的前身是 ISAM(Indexed Sequential Access Method:利用索引，順序 存取數(shù)據(jù)的方法)。
  5.5 版本之后默認(rèn)的存儲(chǔ)引擎改成了 InnoDB，它是第三方公司為 MySQL 開發(fā)的。 為什么要改呢?最主要的原因還是 InnoDB 支持事務(wù)，支持行級(jí)別的鎖，對(duì)于業(yè)務(wù)一致性要求高的場(chǎng)景來說更適合。
  這個(gè)里面又有 Oracle 和 MySQL 公司的一段恩怨情仇：
  InnoDB 本來是 InnobaseOy 公司開發(fā)的，它和 MySQL AB 公司合作開源了 InnoDB 的代碼。但是沒想到 MySQL 的競(jìng)爭對(duì)手 Oracle 把 InnobaseOy 收購了。后來 08 年 Sun 公司(開發(fā) Java 語言的 Sun)收購了 MySQL AB，09 年 Sun 公司 又被 Oracle 收購了，所以 MySQL，InnoDB 又是一家了。有人覺得 MySQL 越來越像 Oracle，其實(shí)也是這個(gè)原因。
  

  Oracle與Mysql恩怨情仇

• 1.5.4、如何選擇存儲(chǔ)引擎?

  • 如果對(duì)數(shù)據(jù)一致性要求比較高，需要事務(wù)支持，可以選擇 InnoDB。
  • 如果數(shù)據(jù)查詢多更新少，對(duì)查詢性能要求比較高，可以選擇 MyISAM。
  • 如果需要一個(gè)用于查詢的臨時(shí)表，可以選擇 Memory。
  • 如果所有的存儲(chǔ)引擎都不能滿足你的需求，并且技術(shù)能力足夠，可以根據(jù)官網(wǎng)內(nèi)部手冊(cè)用 C 語言開發(fā)一個(gè)存儲(chǔ)引擎??。

1.6、執(zhí)行引擎(Query Execution Engine)

【思考??】：是誰使用執(zhí)行計(jì)劃去操作存儲(chǔ)引擎呢?

【答案】：這就是執(zhí)行引擎，它利用存儲(chǔ)引擎提供的相應(yīng)的 API 來完成操作。
為什么修改了表的存儲(chǔ)引擎，操作方式不需要做任何改變?
因?yàn)椴煌δ艿拇鎯?chǔ)引擎實(shí)現(xiàn)的 API 是相同的。
最后把數(shù)據(jù)返回給客戶端，即使沒有結(jié)果也要返回。

2、MySQL體系結(jié)構(gòu)總結(jié)

2.1. 模塊詳解

mysql體系結(jié)構(gòu)

• Connector：用來支持各種語言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC;
• Management Serveices & Utilities：系統(tǒng)管理和控制工具，包括備份恢復(fù)、MySQL 復(fù)制、集群等等。
• Connection Pool：連接池，管理需要緩沖的資源，包括用戶密碼權(quán)限線程等。
• SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結(jié)果
• Parser：用來解析 SQL 語句。
• Optimizer：查詢優(yōu)化器。
• Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key 緩
  存，權(quán)限緩存等等。
• Pluggable Storage Engines：插件式存儲(chǔ)引擎，它提供 API 給服務(wù)層使用，
  跟具體的文件打交道。

2.2. 架構(gòu)分層

總體上，把 MySQL 分成三層，跟客戶端對(duì)接的連接層，真正執(zhí)行操作的服務(wù)層，和跟硬件打交道的存儲(chǔ)引擎層(參考 MyBatis:接口、核心、基礎(chǔ))。

架構(gòu)分層

• 2.2.1、連接層
  客戶端要連接到 MySQL 服務(wù)器 3306 端口，必須要跟服務(wù)端建立連接，那么管理所有的連接，驗(yàn)證客戶端的身份和權(quán)限，這些功能就在連接層完成。
• 2.1.2、服務(wù)層
  連接層會(huì)把 SQL 語句交給服務(wù)層，這里面又包含一系列的流程:比如查詢緩存的判斷、根據(jù) SQL 調(diào)用相應(yīng)的接口，對(duì) SQL 語句進(jìn)行詞法和語法的解析(比如關(guān)鍵字怎么識(shí)別，別名怎么識(shí)別，語法有沒有錯(cuò)誤等等)。
  然后就是優(yōu)化器，MySQL 底層會(huì)根據(jù)一定的規(guī)則對(duì) SQL 語句進(jìn)行優(yōu)化，最后再交給執(zhí)行器去執(zhí)行。
• 2.1.3.存儲(chǔ)引擎
  存儲(chǔ)引擎就是數(shù)據(jù)真正存放的地方，在 MySQL 里面支持不同的存儲(chǔ)引擎。 再往下就是內(nèi)存或者磁盤。

3、一條更新SQL是如何執(zhí)行的?

3.1. 緩沖池 Buffer Pool

首先，InnnoDB 的數(shù)據(jù)都是放在磁盤上的，InnoDB 操作數(shù)據(jù)有一個(gè)最小的邏輯單位，叫做頁(索引頁和數(shù)據(jù)頁)。對(duì)于數(shù)據(jù)的操作，不是每次都直接操作磁盤，因?yàn)榇疟P的速度太慢了。InnoDB 使用了一種緩沖池的技術(shù)，也就是把磁盤讀到的頁放到一塊內(nèi)存區(qū)域里面。這個(gè)內(nèi)存區(qū)域就叫 Buffer Pool。

buffer pool交互圖

下一次讀取相同的頁，先判斷是不是在緩沖池里面，如果是，就直接讀取，不用再 次訪問磁盤。
修改數(shù)據(jù)的時(shí)候，先修改緩沖池里面的頁。內(nèi)存的數(shù)據(jù)頁和磁盤數(shù)據(jù)不一致的時(shí)候， 把它叫做臟頁。InnoDB 里面有專門的后臺(tái)線程把 Buffer Pool 的數(shù)據(jù)寫入到磁盤， 每隔一段時(shí)間就一次性地把多個(gè)修改寫入磁盤，這個(gè)動(dòng)作就叫做刷臟。
Buffer Pool 是 InnoDB 里面非常重要的一個(gè)結(jié)構(gòu)，它的內(nèi)部又分成幾塊區(qū)域。這里趁機(jī)到官網(wǎng)來認(rèn)識(shí)一下 InnoDB 的內(nèi)存結(jié)構(gòu)和磁盤結(jié)構(gòu)。

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

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

• 3.2.1.內(nèi)存結(jié)構(gòu)：
  Buffer Pool 主要分為 3 個(gè)部分: Buffer Pool、Change Buffer、Adaptive Hash Index，另外還有一個(gè)(redo)log buffer。
  • 1、Buffer Pool：
    Buffer Pool 緩存的是頁面信息，包括數(shù)據(jù)頁、索引頁。 查看服務(wù)器狀態(tài)，里面有很多跟 Buffer Pool 相關(guān)的信息：

SHOW STATUS LIKE '%innodb_buffer_pool%';

這些狀態(tài)都可以在官網(wǎng)查到詳細(xì)的含義，用搜索功能。
Buffer Pool 默認(rèn)大小是 128M(134217728 字節(jié))，可以調(diào)整。 查看參數(shù)(系統(tǒng)變量):

SHOW VARIABLES like '%innodb_buffer_pool%';

【思考??問題】：內(nèi)存的緩沖池寫滿了怎么辦?(Redis 設(shè)置的內(nèi)存滿了怎么辦?)

InnoDB 用 LRU 算法來管理緩沖池(鏈表實(shí)現(xiàn)，不是傳統(tǒng)的 LRU，分成了 young 和 old)，經(jīng)過淘汰的數(shù)據(jù)，剩下的就是熱點(diǎn)數(shù)據(jù)。
內(nèi)存緩沖區(qū)對(duì)于提升讀寫性能有很大的作用。

【思考??問題】：思考一個(gè)問題: 當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在 Buffer Pool 中存在，那么就直接更新好了。 否則的話就需要從磁盤加載到內(nèi)存，再對(duì)內(nèi)存的數(shù)據(jù)頁進(jìn)行操作。也就是說，如果沒有命中緩沖池，至少要產(chǎn)生一次磁盤 IO，有沒有優(yōu)化的方式呢?

• 2、Change Buffer 寫緩沖
  如果這個(gè)數(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。什么時(shí)候發(fā)生 merge? 有幾種情況：在訪問這個(gè)數(shù)據(jù)頁的時(shí)候，或者通過后臺(tái)線程、或者數(shù)據(jù)庫 shut down、 redo log 寫滿時(shí)觸發(fā)。
  如果數(shù)據(jù)庫大部分索引都是非唯一索引，并且業(yè)務(wù)是寫多讀少，不會(huì)在寫數(shù)據(jù)后立刻讀取，就可以使用 Change Buffer(寫緩沖)。寫多讀少的業(yè)務(wù)，調(diào)大這個(gè)值:

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

代表 Change Buffer 占 Buffer Pool 的比例，默認(rèn) 25%。

• 3、Adaptive Hash Index
  索引應(yīng)該是放在磁盤的，為什么要專門把一種哈希的索引放到內(nèi)存?后續(xù)再補(bǔ)。
• 4、(redo)Log Buffer

【思考??一個(gè)問題】：如果 Buffer Pool 里面的臟頁還沒有刷入磁盤時(shí)，數(shù)據(jù)庫宕機(jī)或者重啟，這些數(shù)據(jù)丟失。如果寫操作寫到一半，甚至可能會(huì)破壞數(shù)據(jù)文件導(dǎo)致數(shù)據(jù)庫不可用？

【答案】：為了避免這個(gè)問題，InnoDB 把所有對(duì)頁面的修改操作專門寫入一個(gè)日志文件，并且在數(shù)據(jù)庫啟動(dòng)時(shí)從這個(gè)文件進(jìn)行恢復(fù)操作(實(shí)現(xiàn) crash-safe)——用它來實(shí)現(xiàn)事務(wù)的持久性。

crash-safe(崩潰恢復(fù))

這個(gè)文件就是磁盤的 Redo Log(重做日志)，對(duì)應(yīng)于/var/lib/mysql/目錄下的 ib_logfile0 和 ib_logfile1，每個(gè) 48M。
這種日志和磁盤配合的整個(gè)過程，其實(shí)就是 MySQL 里 WAL 技術(shù) (Write-Ahead Logging)，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤。

show variables like 'innodb_log%';

【思考??問題】：同樣是寫磁盤，為什么不直接寫到 db file 里面去?為什么先寫日志再寫磁盤?

我們先來了解一下隨機(jī) I/O 和順序 I/O的概念。 磁盤的最小組成單元是扇區(qū)，通常是 512 個(gè)字節(jié)。 操作系統(tǒng)和內(nèi)存打交道，最小的單位是頁 Page。 操作系統(tǒng)和磁盤打交道，讀寫磁盤，最小的單位是塊 Block。

機(jī)器讀取數(shù)據(jù)過程

buffer pool 對(duì)Redo Log優(yōu)化

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

需要注意：Redo Log 的內(nèi)容主要是用于崩潰恢復(fù)。磁盤的數(shù)據(jù)文件，數(shù)據(jù)來自 buffer pool。Redo Log 寫入磁盤，不是寫入數(shù)據(jù)文件。
那么，Log Buffer 什么時(shí)候?qū)懭?log file?
在寫入數(shù)據(jù)到磁盤的時(shí)候，操作系統(tǒng)本身是有緩存的。flush 就是把操作系統(tǒng)緩 沖區(qū)寫入到磁盤。
log buffer 寫入磁盤的時(shí)機(jī)，由一個(gè)參數(shù)控制，默認(rèn)是 1。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

事務(wù)寫logBuffer & osCache & 磁盤的控制

這是內(nèi)存結(jié)構(gòu)的第 4 塊內(nèi)容，redo log，它又分成內(nèi)存和磁盤兩部分。redo log 有什么特點(diǎn)?

• 1、redo log 是 InnoDB 存儲(chǔ)引擎實(shí)現(xiàn)的，并不是所有存儲(chǔ)引擎都有。
• 2、不是記錄數(shù)據(jù)頁更新之后的狀態(tài)，而是記錄這個(gè)頁做了什么動(dòng)作，屬于物理日志。
• 3、redo log 的大小是固定的，前面的內(nèi)容會(huì)被覆蓋。
  
  redo log 覆蓋
  
  check point 是當(dāng)前要覆蓋的位置。如果 write pos 跟 check point 重疊，說明 redo log 已經(jīng)寫滿，這時(shí)候需要同步 redo log 到磁盤中。

MySQL 的內(nèi)存結(jié)構(gòu)，總結(jié)一下，分為：Buffer pool、change buffer、Adaptive Hash Index、 log buffer。

• 3.2.2、磁盤結(jié)構(gòu)
  表空間可以看做是 InnoDB 存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層，所有的數(shù)據(jù)都存放在表空間中。InnoDB 的表空間分為 5 大類：
  • a、系統(tǒng)表空間 system tablespace
    在默認(rèn)情況下 InnoDB 存儲(chǔ)引擎有一個(gè)共享表空間(對(duì)應(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 在后面介紹，因?yàn)橛歇?dú)立的表空間。
    • 2、數(shù)據(jù)字典:由內(nèi)部系統(tǒng)表組成，存儲(chǔ)表和索引的元數(shù)據(jù)(定義信息)。
    • 3、雙寫緩沖(InnoDB 的一大特性)：InnoDB 的頁和操作系統(tǒng)的頁大小不一致，InnoDB 頁大小一般為 16K，操作系統(tǒng)頁大小為 4K，InnoDB 的頁寫入到磁盤時(shí)，一個(gè)頁需要分 4 次寫。
      
      image.png
      
      如果存儲(chǔ)引擎正在寫入頁的數(shù)據(jù)到磁盤時(shí)發(fā)生了宕機(jī)，可能出現(xiàn)頁只寫了一部分的情況，比如只寫了 4K，就宕機(jī)了，這種情況叫做部分寫失效(partial page write)，可能會(huì)導(dǎo)致數(shù)據(jù)丟失。
      【思考??】：不是有 redo log 嗎?
      但是有個(gè)問題，如果這個(gè)頁本身已經(jīng)損壞了，用它來做崩潰恢復(fù)是沒有意義的。所以在對(duì)于應(yīng)用 redo log 之前，需要一個(gè)頁的副本。如果出現(xiàn)了寫入失效，就用頁的副本來還原這個(gè)頁，然后再應(yīng)用 redo log。這個(gè)頁的副本就是 double write，InnoDB 的雙寫技術(shù)。通過它實(shí)現(xiàn)了數(shù)據(jù)頁的可靠性。
      跟 redo log 一樣，double write 由兩部分組成，一部分是內(nèi)存的 double write，
      一個(gè)部分是磁盤上的 double write。因?yàn)?double write 是順序?qū)懭氲?，不?huì)帶來很大的 開銷。
      在默認(rèn)情況下，所有的表共享一個(gè)系統(tǒng)表空間，這個(gè)文件會(huì)越來越大，而且它的空間不會(huì)收縮。
    • b、獨(dú)占表空間 file-per-table tablespaces
      讓每張表獨(dú)占一個(gè)表空間。這個(gè)開關(guān)通過 innodb_file_per_table 設(shè)置，默 認(rèn)開啟。
      開啟后，則每張表會(huì)開辟一個(gè)表空間，這個(gè)文件就是數(shù)據(jù)目錄下的 ibd 文件(例如 /var/lib/mysql/mysql/student_innodb.ibd)，存放表的索引和數(shù)據(jù)。
      但是其他類的數(shù)據(jù)，如回滾(undo)信息，插入緩沖索引頁、系統(tǒng)事務(wù)信息，二次 寫緩沖(Double write buffer)等還是存放在原來的共享表空間內(nèi)。
    • c、通用表空間 general tablespaces
      通用表空間也是一種共享的表空間，跟 ibdata1 類似。
      可以創(chuàng)建一個(gè)通用的表空間，用來存儲(chǔ)不同數(shù)據(jù)庫的表，數(shù)據(jù)路徑和文件可以自定義。
      不同表空間的數(shù)據(jù)是可以移動(dòng)的,刪除表空間需要先刪除里面的所有表。
    • d、臨時(shí)表空間 temporary tablespaces
      存儲(chǔ)臨時(shí)表的數(shù)據(jù)，包括用戶創(chuàng)建的臨時(shí)表，和磁盤的內(nèi)部臨時(shí)表。對(duì)應(yīng)數(shù)據(jù)目錄 下的 ibtmp1 文件。當(dāng)數(shù)據(jù)服務(wù)器正常關(guān)閉時(shí)，該表空間被刪除，下次重新產(chǎn)生。
    • e、undo log tablespace
      undo log(撤銷日志 or 回滾日志)記錄了事務(wù)發(fā)生之前的數(shù)據(jù)狀態(tài)(不包括select)。 如果修改數(shù)據(jù)時(shí)出現(xiàn)異常，可以用 undo log 來實(shí)現(xiàn)回滾操作(保持原子性)。
      在執(zhí)行 undo 的時(shí)候，僅僅是將數(shù)據(jù)從邏輯上恢復(fù)至事務(wù)之前的狀態(tài)，而不是從物理頁面上操作實(shí)現(xiàn)的，屬于邏輯格式的日志。
      redo Log 和 undo Log 與事務(wù)密切相關(guān)，統(tǒng)稱為事務(wù)日志。
      undo Log 的數(shù)據(jù)默認(rèn)在系統(tǒng)表空間 ibdata1 文件中，因?yàn)楣蚕肀砜臻g不會(huì)自動(dòng)收 縮，也可以單獨(dú)創(chuàng)建一個(gè) undo 表空間。

【思考??】有了這些日志之后，總結(jié)一下一個(gè)更新操作的流程？

update student set name = 'biudefu' where id=1;

1、事務(wù)開始，從內(nèi)存或磁盤取到這條數(shù)據(jù)，返回給 Server 的執(zhí)行器;
2、執(zhí)行器修改這一行數(shù)據(jù)的值為 biudefu;
3、記錄 name=biudefu 到 undo log;
4、記錄 name=biudefu 到 redo log;
5、調(diào)用存儲(chǔ)引擎接口，在內(nèi)存(Buffer Pool)中修改 name=biudefu;
6、 事務(wù)提交。

• 3.2.3、后臺(tái)線程
  后臺(tái)線程的主要負(fù)責(zé)刷新內(nèi)存池(buffer pool)中的數(shù)據(jù)和把修改的數(shù)據(jù)頁刷新到磁盤。后臺(tái)線程分為：
  • 1、master thread（負(fù)責(zé)刷新緩存數(shù)據(jù)到磁盤并協(xié)調(diào)調(diào)度其它后臺(tái)進(jìn)程）
  • 2、IO thread（分為 insert buffer、log、read、write 進(jìn)程。分別用來處理 insert buffer、 重做日志、讀寫請(qǐng)求的 IO 回調(diào)。）
  • 3、purge thread（用來回收 undo 頁）
  • 4、page cleaner thread（用來刷新臟頁）

除了 InnoDB 架構(gòu)中的日志文件，MySQL 的 Server 層也有一個(gè)日志文件，叫做
binlog，它可以被所有的存儲(chǔ)引擎使用。

3.3. Binlog

binlog 以事件的形式記錄了所有的 DDL 和 DML 語句(因?yàn)樗涗浀氖遣僮鞫皇菙?shù)據(jù)值，屬于邏輯日志)，可以用來做主從復(fù)制和數(shù)據(jù)恢復(fù)。
跟 redo log 不一樣，它的文件內(nèi)容是可以追加的，沒有固定大小限制。
在開啟了 binlog 功能的情況下，可以把 binlog 導(dǎo)出成 SQL 語句，把所有的過程操作重放一遍，來實(shí)現(xiàn)數(shù)據(jù)的恢復(fù)。
binlog 的另一個(gè)功能就是用來實(shí)現(xiàn)主從復(fù)制，它的原理就是從服務(wù)器讀取主服務(wù)器的 binlog，然后執(zhí)行一遍。
有了這兩個(gè)日志之后，我們來看一下一條更新語句是怎么執(zhí)行的：

sql執(zhí)行整體流程

例如一條語句:update student set name='biudefu' where id=1;

1、先查詢到這條數(shù)據(jù)，如果有緩存，也會(huì)用到緩存。
2、把 name 改成biudefu，然后調(diào)用引擎的 API 接口，寫入這一行數(shù)據(jù)到內(nèi)存，同時(shí)記錄 redo log。這時(shí) redo log 進(jìn)入 prepare 狀態(tài)，然后告訴執(zhí)行器，執(zhí)行完成了，可以隨時(shí)提交。
3、執(zhí)行器收到通知后記錄 binlog，然后調(diào)用存儲(chǔ)引擎接口，設(shè)置 redo log 為 commit 狀態(tài)。
4、更新完成。

這張圖片的重點(diǎn)：

• 1、先記錄到內(nèi)存，再寫日志文件。
• 2、記錄 redo log 分為兩個(gè)階段。
• 3、存儲(chǔ)引擎和 Server 記錄不同的日志。
• 4、先記錄 redo，再記錄 binlog。

參考資料：

《數(shù)據(jù)庫查詢優(yōu)化器的藝術(shù)-原理解析與SQL性能優(yōu)化》
《MySQL高性能書籍第3版（中文）》
《MySQL技術(shù)內(nèi)幕-InnoDB存儲(chǔ)引擎第2版》