高達 00 人物集合

目錄：

• 1昌渤、一個查詢語句SQL是如何執(zhí)行的赴穗？

  • 1.1、通信協(xié)議

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

    • 連接方式

    • 通信協(xié)議

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

  • 1.3般眉、語法解析和預處理

    • 1.3.1加矛、詞法解析

    • 1.3.2、語法解析

    • 1.3.3煤篙、預處理器

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

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

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

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

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

  • 1.5苛茂、存儲引擎

    • 1.5.1、存儲引擎基本介紹

    • 1.5.2鸠窗、查看存儲引擎

    • 1.5.3妓羊、存儲引擎比較

    • 1.5.4、如何選擇存儲引擎?

  • 1.6稍计、執(zhí)行引擎

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

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

• 思考??問題：默認關閉的意思就是不推薦使用躁绸，為什么 MySQL 不推薦使用它自帶的緩存呢?

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

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

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

• 2、MySQL體系結構總結

  • 2.1. 模塊詳解

  • 2.2. 架構分層

    • 2.2.1怎虫、連接層

    • 2.1.2暑认、服務層

    • 2.1.3、存儲引擎

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

  • 3.1. 緩沖池 Buffer Pool

  • 3.2. InnoDB 內(nèi)存結構和磁盤結構

    • 3.2.1.內(nèi)存結構

      • 1蘸际、Buffer Pool

      • 2、Change Buffer 寫緩沖

      • 3徒扶、Adaptive Hash Index

      • 4粮彤、(redo)Log Buffer

    • 3.2.2、磁盤結構

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

      • b驾诈、獨占表空間 file-per-table tablespaces

      • c缠诅、通用表空間 general tablespaces

      • d溶浴、臨時表空間 temporary tablespaces

      • e、undo log tablespace

    • 3.2.3管引、后臺線程

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

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

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

  • 思考??問題：有了這些日志之后隅熙，總結一下一個更新操作的流程稽煤？

  • 3.3. Binlog

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

SQL執(zhí)行過程

1.1、 通信協(xié)議

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

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

  • 同步通信的特點：
    • 1拳芙、同步通信依賴于被調(diào)用方察藐，受限于被調(diào)用方的性能。應用服務器操作數(shù)據(jù)庫舟扎，線程會阻塞转培，等待數(shù)據(jù)庫的返回。(常用的方式)
    • 2浆竭、一般只能做到一對一浸须，很難做到一對多的通信。
  • 異步通訊的特點:
    • 1邦泄、異步可以避免應用阻塞等待删窒，但是不能節(jié)省 SQL 執(zhí)行的時間。
    • 2顺囊、如果異步存在并發(fā)肌索，每一個 SQL 的執(zhí)行都要單獨建立一個連接，避免數(shù)據(jù)混亂特碳。 但是這樣會給服務端帶來巨大的壓力(一個連接就會創(chuàng)建一個線程诚亚，線程間切換會占用 大量 CPU 資源)。另外異步通信還帶來了編碼的復雜度午乓，所以一般不建議使用站宗。如果要異步，必須使用連接池益愈，排隊從連接池獲取連接而不是創(chuàng)建新連接梢灭。

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

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

  • 短連接就是操作完畢以后，馬上 close 掉钥顽。
  • 長連接可以保持打開义屏，減少服務端創(chuàng)建和釋放連接的消耗，后面的程序訪問的時候還可以使用這個連接蜂大。默認會在連接池中使用長連接湿蛔。

  保持長連接會消耗內(nèi)存。長時間不活動的連接县爬，MySQL 服務器會斷開阳啥。(默認都是 28800 秒，8 小時)

## 查看交互超時時間
show global variables like 'wait_timeout'; -- 非交互式超時時間财喳，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超時時間察迟，如數(shù)據(jù)庫工具
## 查看 MySQL 當前有多少個連接?
show global status like 'Thread%';
##Threads_cached:緩存中的線程連接數(shù)。
##Threads_connected:當前打開的連接數(shù)耳高。
##Threads_created:為處理連接創(chuàng)建的線程數(shù)扎瓶。 
##Threads_running:非睡眠狀態(tài)的連接數(shù)，通常指并發(fā)連接數(shù)泌枪。

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

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

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

  
  
  通信協(xié)議
  • 1误证、單工：
    在兩臺計算機通信的時候，數(shù)據(jù)的傳輸是單向的修壕。
    生活中的類比:遙控器愈捅。
  • 2、半雙工：
    在兩臺計算機之間慈鸠，數(shù)據(jù)傳輸是雙向的蓝谨，你可以給我發(fā)送，我也可以給你發(fā)送青团，
    但是在這個通訊連接里面譬巫，同一時間只能有一臺服務器在發(fā)送數(shù)據(jù)，也就是你要給我發(fā)的話督笆，也必須等我發(fā)給你完了之后才能給我發(fā)芦昔。
    生活中的類比:對講機。
  • 3胖腾、全雙工：
    數(shù)據(jù)的傳輸是雙向的烟零，并且可以同時傳輸瘪松。
    生活中的類比:打電話咸作。

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

【答案】：要么是客戶端向服務端發(fā)送數(shù)據(jù)锨阿，要么是服務端向客戶端發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生记罚。所以客戶端發(fā)送 SQL 語句給服務端的時候墅诡，(在一次連接里面)數(shù)據(jù)是不能分成小塊發(fā)送的，不管你的 SQL 語句有多大桐智，都是一次性發(fā)送末早。
比如用 MyBatis 動態(tài) SQL 生成了一個批量插入的語句，插入 10 萬條數(shù)據(jù)说庭，values 后面跟了一長串的內(nèi)容然磷，或者 where 條件 in 里面的值太多，會出現(xiàn)問題刊驴。這個時候必須要調(diào)整 MySQL 服務器配置 max_allowed_packet 參數(shù)的值(默認 是 4M)姿搜，把它調(diào)大，否則就會報錯捆憎。
另一方面舅柜，對于MySql服務端來說，也是一次性發(fā)送所有的數(shù)據(jù)躲惰，不能因為你已經(jīng)取到了想要的數(shù)據(jù)就中斷操作致份，這個時候會對網(wǎng)絡和內(nèi)存產(chǎn)生大量消耗。
所以础拨，一定要在程序里面避免不帶 limit 的這種操作氮块，比如一次把所有滿足條件 的數(shù)據(jù)全部查出來，一定要先 count 一下诡宗。如果數(shù)據(jù)量的話雇锡，可以分批查詢。

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

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

• 第一個是它要求 SQL 語句必須一 模一樣肮韧，中間多一個空格融蹂，字母大小寫不同都被認為是不同的的 SQL旺订。
• 第二個是表里面任何一條數(shù)據(jù)發(fā)生變化的時候，這張表所有緩存都會失效超燃，所以對于有大量數(shù)據(jù)更新的應用区拳，也不適合。
  所以緩存這一塊意乓，還是交給 ORM 框架(比如 MyBatis 默認開啟了一級緩存)樱调， 或者獨立的緩存服務，比如 Redis 來處理更合適届良。

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

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

• 1.3.1乞而、詞法解析
  詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。
• 1.3.2慢显、語法解析
  語法分析會對 SQL 做一些語法檢查爪模，比如單引號有沒有閉合， 然后根據(jù) MySQL 定義的語法規(guī)則鳍怨，根據(jù) SQL 語句生成一個數(shù)據(jù)結構呻右。這個數(shù)據(jù)結構我 們把它叫做解析樹(select_lex)。
  
  解析樹
• 1.3.3鞋喇、預處理器

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

select * from balabala;

【答案】：解析器可以分析語法侦香，但是它怎么知道數(shù)據(jù)庫里面有什么表落塑，表里面有什么字段呢? 實際上還是在解析的時候報錯，解析 SQL 的環(huán)節(jié)里面有個預處理器罐韩。 它會檢查生成的解析樹憾赁，解決解析器無法解析的語義。比如散吵，它會檢查表和列名是
否存在龙考，檢查名字和別名，保證沒有歧義矾睦。 預處理之后得到一個新的解析樹晦款。

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

• 1.4.1枚冗、什么是優(yōu)化器?
  查詢優(yōu)化器的目的：就是根據(jù)解析樹生成不同的執(zhí)行計劃(Execution Plan)缓溅，然后選擇一種最優(yōu)的執(zhí)行計劃，MySQL 里面使用的是基于開銷(cost)的優(yōu)化器赁温，哪種執(zhí)行計劃開銷最小坛怪，就用哪種淤齐。
• 1.4.2、優(yōu)化器可以做什么?
  MySQL 的優(yōu)化器能處理哪些優(yōu)化類型呢? 舉兩個簡單的例子:
  1袜匿、對多張表進行關聯(lián)查詢的時候更啄，以哪個表的數(shù)據(jù)作為基準表。
  2沉帮、有多個索引可以使用的時候锈死，選擇哪個索引贫堰。 實際上穆壕，對于每一種數(shù)據(jù)庫來說，優(yōu)化器的模塊都是必不可少的其屏，他們通過復雜的算法實現(xiàn)盡可能優(yōu)化查詢效率的目標喇勋。 如果對于優(yōu)化器的細節(jié)感興趣，可以看看《數(shù)據(jù)庫查詢優(yōu)化器的藝術-原理解析與 SQL性能優(yōu)化》偎行。
• 1.4.3.優(yōu)化器是怎么得到執(zhí)行計劃的?
  首先要啟用優(yōu)化器的追蹤(默認是關閉的):
  注意４ū场！蛤袒！開啟這開關是會消耗性能熄云，因為它要把優(yōu)化分析的結果寫到表里面，所以不要輕易開啟妙真，或者查看完之后關閉它(改成 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

分析完記得關掉它：

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

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

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

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

EXPLAIN select name from user where id=1;

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

1.5歌懒、存儲引擎：

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

接下來時點給與解答：

• 1.5.1验烧、存儲引擎基本介紹
  關系型數(shù)據(jù)庫里面板驳，數(shù)據(jù)是放在什么結構里面的? (放在表 Table 里面的)。表在存儲數(shù)據(jù)的同時碍拆，還要組織數(shù)據(jù)的存儲結構若治，這個存儲結構就是由存儲引擎決定的，所以也可以把存儲引擎叫做表類型感混。
  在 MySQL 里面端幼，支持多種存儲引擎，他們是可以替換的弧满，所以叫做插件式的存儲引擎婆跑。
• 1.5.2、查看存儲引擎
  數(shù)據(jù)庫里面已經(jīng)存在的表庭呜，怎么查看它們的存儲引擎呢?
  通過sql or DDL 建表語句來查看滑进。

show table status from `student`;

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

show variables like 'datadir';

• 1.5.3吉执、存儲引擎比較
  MyISAM 和 InnoDB 是用得最多的兩個存儲引擎疯淫，在 MySQL 5.5 版本之前，默認的存儲引擎是 MyISAM戳玫，它是 MySQL 自帶的熙掺。創(chuàng)建表的時候不指定存儲引擎， 它就會使用 MyISAM 作為存儲引擎咕宿。
  MyISAM 的前身是 ISAM(Indexed Sequential Access Method:利用索引币绩，順序 存取數(shù)據(jù)的方法)。
  5.5 版本之后默認的存儲引擎改成了 InnoDB府阀，它是第三方公司為 MySQL 開發(fā)的缆镣。 為什么要改呢?最主要的原因還是 InnoDB 支持事務，支持行級別的鎖试浙，對于業(yè)務一致性要求高的場景來說更適合董瞻。
  這個里面又有 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，其實也是這個原因截珍。
  

  Oracle與Mysql恩怨情仇

• 1.5.4攀甚、如何選擇存儲引擎?

  • 如果對數(shù)據(jù)一致性要求比較高，需要事務支持岗喉，可以選擇 InnoDB秋度。
  • 如果數(shù)據(jù)查詢多更新少，對查詢性能要求比較高沈堡，可以選擇 MyISAM静陈。
  • 如果需要一個用于查詢的臨時表燕雁，可以選擇 Memory诞丽。
  • 如果所有的存儲引擎都不能滿足你的需求，并且技術能力足夠拐格，可以根據(jù)官網(wǎng)內(nèi)部手冊用 C 語言開發(fā)一個存儲引擎??僧免。

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

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

【答案】：這就是執(zhí)行引擎捏浊，它利用存儲引擎提供的相應的 API 來完成操作懂衩。
為什么修改了表的存儲引擎，操作方式不需要做任何改變?
因為不同功能的存儲引擎實現(xiàn)的 API 是相同的金踪。
最后把數(shù)據(jù)返回給客戶端浊洞，即使沒有結果也要返回。

2胡岔、MySQL體系結構總結

2.1. 模塊詳解

mysql體系結構

• Connector：用來支持各種語言和 SQL 的交互法希，比如 PHP，Python靶瘸，Java 的 JDBC;
• Management Serveices & Utilities：系統(tǒng)管理和控制工具苫亦，包括備份恢復、MySQL 復制怨咪、集群等等屋剑。
• Connection Pool：連接池，管理需要緩沖的資源诗眨，包括用戶密碼權限線程等唉匾。
• SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結果
• Parser：用來解析 SQL 語句匠楚。
• Optimizer：查詢優(yōu)化器巍膘。
• Cache and Buffer：查詢緩存卫病，除了行記錄的緩存之外，還有表緩存典徘，Key 緩
  存蟀苛，權限緩存等等。
• Pluggable Storage Engines：插件式存儲引擎逮诲，它提供 API 給服務層使用帜平，
  跟具體的文件打交道。

2.2. 架構分層

總體上梅鹦，把 MySQL 分成三層裆甩，跟客戶端對接的連接層，真正執(zhí)行操作的服務層齐唆，和跟硬件打交道的存儲引擎層(參考 MyBatis:接口嗤栓、核心、基礎)箍邮。

架構分層

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

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

3.1. 緩沖池 Buffer Pool

首先镇饺，InnnoDB 的數(shù)據(jù)都是放在磁盤上的乎莉，InnoDB 操作數(shù)據(jù)有一個最小的邏輯單位，叫做頁(索引頁和數(shù)據(jù)頁)。對于數(shù)據(jù)的操作惋啃，不是每次都直接操作磁盤哼鬓，因為磁盤的速度太慢了。InnoDB 使用了一種緩沖池的技術边灭，也就是把磁盤讀到的頁放到一塊內(nèi)存區(qū)域里面异希。這個內(nèi)存區(qū)域就叫 Buffer Pool。

buffer pool交互圖

下一次讀取相同的頁绒瘦，先判斷是不是在緩沖池里面称簿，如果是，就直接讀取惰帽，不用再 次訪問磁盤憨降。
修改數(shù)據(jù)的時候，先修改緩沖池里面的頁该酗。內(nèi)存的數(shù)據(jù)頁和磁盤數(shù)據(jù)不一致的時候授药， 把它叫做臟頁随静。InnoDB 里面有專門的后臺線程把 Buffer Pool 的數(shù)據(jù)寫入到磁盤陨舱， 每隔一段時間就一次性地把多個修改寫入磁盤搭综，這個動作就叫做刷臟斤寇。
Buffer Pool 是 InnoDB 里面非常重要的一個結構，它的內(nèi)部又分成幾塊區(qū)域搪哪。這里趁機到官網(wǎng)來認識一下 InnoDB 的內(nèi)存結構和磁盤結構狼忱。

3.2. InnoDB 內(nèi)存結構和磁盤結構

InnoDB的內(nèi)存結構&磁盤結構

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

SHOW STATUS LIKE '%innodb_buffer_pool%';

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

SHOW VARIABLES like '%innodb_buffer_pool%';

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

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

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

• 2、Change Buffer 寫緩沖
  如果這個數(shù)據(jù)頁不是唯一索引案疲，不存在數(shù)據(jù)重復的情況封恰，也就不需要從磁盤加載索引頁判斷數(shù)據(jù)是不是重復(唯一性檢查)。這種情況下可以先把修改記錄在內(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è)務是寫多讀少尿背，不會在寫數(shù)據(jù)后立刻讀取，就可以使用 Change Buffer(寫緩沖)捶惜。寫多讀少的業(yè)務田藐，調(diào)大這個值:

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

代表 Change Buffer 占 Buffer Pool 的比例，默認 25%吱七。

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

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

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

crash-safe(崩潰恢復)

這個文件就是磁盤的 Redo Log(重做日志)舷丹，對應于/var/lib/mysql/目錄下的 ib_logfile0 和 ib_logfile1抒钱，每個 48M。
這種日志和磁盤配合的整個過程颜凯，其實就是 MySQL 里 WAL 技術 (Write-Ahead Logging)谋币，它的關鍵點就是先寫日志，再寫磁盤症概。

show variables like 'innodb_log%';

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

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

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

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

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

需要注意：Redo Log 的內(nèi)容主要是用于崩潰恢復。磁盤的數(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';

事務寫logBuffer & osCache & 磁盤的控制

這是內(nèi)存結構的第 4 塊內(nèi)容孩擂，redo log，它又分成內(nèi)存和磁盤兩部分箱熬。redo log 有什么特點?

• 1类垦、redo log 是 InnoDB 存儲引擎實現(xiàn)的狈邑，并不是所有存儲引擎都有。
• 2蚤认、不是記錄數(shù)據(jù)頁更新之后的狀態(tài)米苹，而是記錄這個頁做了什么動作，屬于物理日志烙懦。
• 3驱入、redo log 的大小是固定的，前面的內(nèi)容會被覆蓋氯析。
  
  redo log 覆蓋
  
  check point 是當前要覆蓋的位置亏较。如果 write pos 跟 check point 重疊，說明 redo log 已經(jīng)寫滿掩缓，這時候需要同步 redo log 到磁盤中雪情。

MySQL 的內(nèi)存結構，總結一下你辣，分為：Buffer pool巡通、change buffer、Adaptive Hash Index舍哄、 log buffer宴凉。

• 3.2.2、磁盤結構
  表空間可以看做是 InnoDB 存儲引擎邏輯結構的最高層表悬，所有的數(shù)據(jù)都存放在表空間中弥锄。InnoDB 的表空間分為 5 大類：
  • a、系統(tǒng)表空間 system tablespace
    在默認情況下 InnoDB 存儲引擎有一個共享表空間(對應文件/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)搞旭，可能會導致數(shù)據(jù)丟失。
      【思考??】：不是有 redo log 嗎?
      但是有個問題菇绵，如果這個頁本身已經(jīng)損壞了肄渗，用它來做崩潰恢復是沒有意義的。所以在對于應用 redo log 之前咬最，需要一個頁的副本翎嫡。如果出現(xiàn)了寫入失效，就用頁的副本來還原這個頁永乌，然后再應用 redo log惑申。這個頁的副本就是 double write，InnoDB 的雙寫技術翅雏。通過它實現(xiàn)了數(shù)據(jù)頁的可靠性圈驼。
      跟 redo log 一樣，double write 由兩部分組成望几，一部分是內(nèi)存的 double write绩脆，
      一個部分是磁盤上的 double write。因為 double write 是順序?qū)懭氲拈献保粫砗艽蟮?開銷衙伶。
      在默認情況下，所有的表共享一個系統(tǒng)表空間害碾，這個文件會越來越大矢劲，而且它的空間不會收縮。
    • b慌随、獨占表空間 file-per-table tablespaces
      讓每張表獨占一個表空間芬沉。這個開關通過 innodb_file_per_table 設置，默 認開啟阁猜。
      開啟后丸逸，則每張表會開辟一個表空間，這個文件就是數(shù)據(jù)目錄下的 ibd 文件(例如 /var/lib/mysql/mysql/student_innodb.ibd)剃袍，存放表的索引和數(shù)據(jù)黄刚。
      但是其他類的數(shù)據(jù)，如回滾(undo)信息民效，插入緩沖索引頁憔维、系統(tǒng)事務信息涛救，二次 寫緩沖(Double write buffer)等還是存放在原來的共享表空間內(nèi)。
    • c业扒、通用表空間 general tablespaces
      通用表空間也是一種共享的表空間检吆，跟 ibdata1 類似。
      可以創(chuàng)建一個通用的表空間程储，用來存儲不同數(shù)據(jù)庫的表蹭沛，數(shù)據(jù)路徑和文件可以自定義。
      不同表空間的數(shù)據(jù)是可以移動的,刪除表空間需要先刪除里面的所有表章鲤。
    • d摊灭、臨時表空間 temporary tablespaces
      存儲臨時表的數(shù)據(jù)，包括用戶創(chuàng)建的臨時表咏窿，和磁盤的內(nèi)部臨時表斟或。對應數(shù)據(jù)目錄 下的 ibtmp1 文件。當數(shù)據(jù)服務器正常關閉時集嵌，該表空間被刪除萝挤，下次重新產(chǎn)生。
    • e根欧、undo log tablespace
      undo log(撤銷日志 or 回滾日志)記錄了事務發(fā)生之前的數(shù)據(jù)狀態(tài)(不包括select)怜珍。 如果修改數(shù)據(jù)時出現(xiàn)異常，可以用 undo log 來實現(xiàn)回滾操作(保持原子性)凤粗。
      在執(zhí)行 undo 的時候酥泛，僅僅是將數(shù)據(jù)從邏輯上恢復至事務之前的狀態(tài)，而不是從物理頁面上操作實現(xiàn)的嫌拣，屬于邏輯格式的日志柔袁。
      redo Log 和 undo Log 與事務密切相關，統(tǒng)稱為事務日志异逐。
      undo Log 的數(shù)據(jù)默認在系統(tǒng)表空間 ibdata1 文件中捶索，因為共享表空間不會自動收 縮，也可以單獨創(chuàng)建一個 undo 表空間灰瞻。

【思考??】有了這些日志之后腥例，總結一下一個更新操作的流程？

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

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

• 3.2.3茁肠、后臺線程
  后臺線程的主要負責刷新內(nèi)存池(buffer pool)中的數(shù)據(jù)和把修改的數(shù)據(jù)頁刷新到磁盤。后臺線程分為：
  • 1缩举、master thread（負責刷新緩存數(shù)據(jù)到磁盤并協(xié)調(diào)調(diào)度其它后臺進程）
  • 2垦梆、IO thread（分為 insert buffer、log仅孩、read托猩、write 進程。分別用來處理 insert buffer辽慕、 重做日志京腥、讀寫請求的 IO 回調(diào)。）
  • 3溅蛉、purge thread（用來回收 undo 頁）
  • 4公浪、page cleaner thread（用來刷新臟頁）

除了 InnoDB 架構中的日志文件，MySQL 的 Server 層也有一個日志文件船侧，叫做
binlog欠气，它可以被所有的存儲引擎使用。

3.3. Binlog

binlog 以事件的形式記錄了所有的 DDL 和 DML 語句(因為它記錄的是操作而不是數(shù)據(jù)值镜撩，屬于邏輯日志)预柒，可以用來做主從復制和數(shù)據(jù)恢復。
跟 redo log 不一樣袁梗，它的文件內(nèi)容是可以追加的宜鸯，沒有固定大小限制。
在開啟了 binlog 功能的情況下遮怜，可以把 binlog 導出成 SQL 語句淋袖，把所有的過程操作重放一遍，來實現(xiàn)數(shù)據(jù)的恢復奈泪。
binlog 的另一個功能就是用來實現(xiàn)主從復制适贸，它的原理就是從服務器讀取主服務器的 binlog，然后執(zhí)行一遍涝桅。
有了這兩個日志之后拜姿，我們來看一下一條更新語句是怎么執(zhí)行的：

sql執(zhí)行整體流程

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

1、先查詢到這條數(shù)據(jù)冯遂，如果有緩存蕊肥，也會用到緩存。
2、把 name 改成biudefu壁却，然后調(diào)用引擎的 API 接口批狱，寫入這一行數(shù)據(jù)到內(nèi)存，同時記錄 redo log展东。這時 redo log 進入 prepare 狀態(tài)赔硫，然后告訴執(zhí)行器，執(zhí)行完成了盐肃，可以隨時提交爪膊。
3、執(zhí)行器收到通知后記錄 binlog砸王，然后調(diào)用存儲引擎接口推盛，設置 redo log 為 commit 狀態(tài)。
4谦铃、更新完成耘成。

這張圖片的重點：

• 1、先記錄到內(nèi)存驹闰，再寫日志文件瘪菌。
• 2、記錄 redo log 分為兩個階段疮方。
• 3控嗜、存儲引擎和 Server 記錄不同的日志。
• 4骡显、先記錄 redo疆栏，再記錄 binlog。

參考資料：

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