MySQL 的工作流程
1.一條查詢 SQL 語句是如何執行的
我們的程序或者工具要操作資料庫，第一步要做什麼事情？
跟資料庫建立連接。
1.1. 通信協議
首先，MySQL 必須要運行一個服務，監聽默認的 3306 埠。
在我們開發系統跟第三方對接的時候，必須要弄清楚的有兩件事。
第一個就是通信協議，比如我們是用 HTTP 還是 WebService 還是 TCP？
第二個是消息格式，比如我們用 XML 格式，還是 JSON 格式，還是定長格式？報文頭長度多少，包含什麼內容，每個欄位的詳細含義。比如我們之前跟銀聯對接，銀聯的銀行卡聯網規範，約定了一種比較複雜的通訊協議叫做：四進四出單工異步長連接（為了保證穩定性和性能）。
MySQL 是支持多種通信協議的，可以使用同步/異步的方式，支持長連接/短連接。這裡我們拆分來看。第一個是通信類型
通信類型： 同步或者異步
同步通信的特點：
1、同步通信依賴於被調用方，受限於被調用方的性能。也就是說，應用操作資料庫，線程會阻塞，等待資料庫的返回。
2、一般只能做到一對一，很難做到一對多的通信。
異步跟同步相反：
1、異步可以避免應用阻塞等待，但是不能節省 SQL 執行的時間。
2、如果異步存在並發，每一個 SQL 的執行都要單獨建立一個連接，避免數據混亂。但是這樣會給服務端帶來巨大的壓力（一個連接就會創建一個線程，線程間切換會占用大量 CPU 資源）。另外異步通信還帶來了編碼的複雜度，所以一般不建議使用。如果要異步，必須使用連接池，排隊從連接池獲取連接而不是創建新連接。
一般來說我們連接資料庫都是同步連接。
連接方式： 長連接或者短連接
MySQL 既支持短連接，也支持長連接。短連接就是操作完畢以後，馬上 close 掉。長連接可以保持打開，減少服務端創建和釋放連接的消耗，後面的程序訪問的時候還可以使用這個連接。一般我們會在連接池中使用長連接。
保持長連接會消耗內存。長時間不活動的連接，MySQL 伺服器會斷開。
show global variables like 'wait_timeout'; -- 非交互式超時時間， 如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超時時間， 如資料庫工具
默認都是 28800 秒，8 小時。
我們怎麼查看 MySQL 當前有多少個連接？
可以用 show status 命令：
show global status like 'Thread%';
Threads_cached：緩存中的線程連接數。
Threads_connected：當前打開的連接數。
Threads_created：為處理連接創建的線程數。
Threads_running：非睡眠狀態的連接數，通常指並發連接數。
每產生一個連接或者一個會話，在服務端就會創建一個線程來處理。反過來，如果要殺死會話，就是 Kill 線程。
有了連接數，怎麼知道當前連接的狀態？
也可以使用 SHOW PROCESSLIST; （root 用戶）查看 SQL 的執行狀態。
一些常見的狀態：
MySQL 服務允許的最大連接數是多少呢？
在 5.7 版本中默認是 151 個，最大可以設置成 16384（2^14）。
show variables like 'max_connections';
show 的參數說明：
1、級別：會話 session 級別（默認）；全局 global 級別
2、動態修改：set，重啟後失效；永久生效，修改配置文件/etc/my.conf
set global max_connections = 1000;
通信協議
MySQL 支持哪些通信協議呢？
第一種是 Unix Socket。
比如我們在 Linux 伺服器上，如果沒有指定-h 參數，它就用 socket 方式登錄（省略了-S /var/lib/mysql/mysql.sock）。
它不用通過網絡協議，也可以連接到 MySQL 的伺服器，它需要用到伺服器上的一個物理文件（/var/lib/mysql/mysql.sock）。
select @@socket;
如果指定-h 參數，就會用第二種方式，TCP/IP 協議。
mysql -h192.168.8.211 -uroot -p123456
我 們 的 編 程 語 言 的 連 接 模 塊 都 是 用 TCP 協 議 連 接 到 MySQL 服 務 器 的 ， 比 如
mysql-connector-java-x.x.xx.jar。
另外還有命名管道（Named Pipes）和內存共享（Share Memory）的方式，這兩種通信方式只能在 Windows 上面使用，一般用得比較少。
1.1.2.通信方式
第二個是通信方式。
單工：
在兩台計算機通信的時候，數據的傳輸是單向的。生活中的類比：遙控器。
半雙工：
在兩台計算機之間，數據傳輸是雙向的，你可以給我發送，我也可以給你發送，
但是在這個通訊連接裡面，同一時間只能有一台伺服器在發送數據，也就是你要給我發
的話，也必須等我發給你完了之後才能給我發。生活中的類比：對講機。
全雙工：
數據的傳輸是雙向的，並且可以同時傳輸。生活中的類比：打電話。
MySQL 使用了半雙工的通信方式？
要麼是客戶端向服務端發送數據，要麼是服務端向客戶端發送數據，這兩個動作不能同時發生。所以客戶端發送 SQL 語句給服務端的時候，（在一次連接裡面）數據是不能分成小塊發送的，不管你的 SQL 語句有多大，都是一次性發送。
比如我們用 MyBatis 動態 SQL 生成了一個批量插入的語句，插入 10 萬條數據，values後面跟了一長串的內容，或者 where 條件 in 裡面的值太多，會出現問題。
這個時候我們必須要調整 MySQL 伺服器配置 max_allowed_packet 參數的值（默認是 4M），把它調大，否則就會報錯。
另一方面，對於服務端來說，也是一次性發送所有的數據，不能因為你已經取到了想要的數據就中斷操作，這個時候會對網絡和內存產生大量消耗。
所以，我們一定要在程序裡面避免不帶 limit 的這種操作，比如一次把所有滿足條件的數據全部查出來，一定要先 count 一下。如果數據量的話，可以分批查詢。
執行一條查詢語句，客戶端跟服務端建立連接之後呢？下一步要做什麼？
1.2. 查詢緩存
MySQL 內部自帶了一個緩存模塊。
緩存的作用我們應該很清楚了，把數據以 KV 的形式放到內存裡面，可以加快數據的讀取速度，也可以減少伺服器處理的時間。但是 MySQL 的緩存我們好像比較陌生，從來沒有去配置過，也不知道它什麼時候生效？
比如 user_innodb 有 500 萬行數據，沒有索引。我們在沒有索引的欄位上執行同樣的查詢，大家覺得第二次會快嗎？
select * from user_innodb where name='javaHuang';
為何緩存沒有生效，為什麼？MySQL 的緩存默認是關閉的。
show variables like 'query_cache%';
默認關閉的意思就是不推薦使用，為什麼 MySQL 不推薦使用它自帶的緩存呢？
主要是因為 MySQL 自帶的緩存的應用場景有限，第一個是它要求 SQL 語句必須一模一樣，中間多一個空格，字母大小寫不同都被認為是不同的的 SQL。
第二個是表裡面任何一條數據發生變化的時候，這張表所有緩存都會失效，所以對於有大量數據更新的應用，也不適合。
所以緩存這一塊，我們還是交給 ORM 框架（比如 MyBatis 默認開啟了一級緩存），或者獨立的緩存服務，比如 Redis 來處理更合適。
在 MySQL 8.0 中，查詢緩存已經被移除了。
1.3. 語法解析和預處理(Parser & Preprocessor)
我們沒有使用緩存的話，就會跳過緩存的模塊，下一步我們要做什麼呢？
OK，這裡我會有一個疑問，為什麼我的一條 SQL 語句能夠被識別呢？假如我隨便執行一個字符串 penyuyan，伺服器報了一個 1064 的錯：
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for theright syntax to use near 'penyuyan' at line 1
它是怎麼知道我輸入的內容是錯誤的？
這個就是 MySQL 的 Parser 解析器和 Preprocessor 預處理模塊。
這一步主要做的事情是對語句基於 SQL 語法進行詞法和語法分析和語義的解析。
1.3.1.詞法解析
詞法分析就是把一個完整的 SQL 語句打碎成一個個的單詞。
比如一個簡單的 SQL 語句：
select name from user where id = 1;
它會打碎成 8 個符號，每個符號是什麼類型，從哪裡開始到哪裡結束。
1.3.2.語法解析
第二步就是語法分析，語法分析會對 SQL 做一些語法檢查，比如單引號有沒有閉合，然後根據 MySQL 定義的語法規則，根據 SQL 語句生成一個數據結構。這個數據結構我們把它叫做解析樹（select_lex）。
任何資料庫的中間件，比如 Mycat，Sharding-JDBC（用到了 Druid Parser），都必須要有詞法和語法分析功能，在市面上也有很多的開源的詞法解析的工具（比如 LEX，Yacc）。
1.3.3.預處理器
問題：如果我寫了一個詞法和語法都正確的 SQL，但是表名或者欄位不存在，會在哪裡報錯？是在資料庫的執行層還是解析器？比如：
select * from penyuyan;
解析器可以分析語法，但是它怎麼知道資料庫裡面有什麼表，表裡面有什麼欄位呢？
實際上還是在解析的時候報錯，解析 SQL 的環節裡面有個預處理器。
它會檢查生成的解析樹，解決解析器無法解析的語義。比如，它會檢查表和列名是否存在，檢查名字和別名，保證沒有歧義。
預處理之後得到一個新的解析樹。
1.4. 查詢優化（ Query Optimizer） 與查詢執行計劃
1.4.2. 優化器可以做什麼？
MySQL 的優化器能處理哪些優化類型呢？
舉兩個簡單的例子：
1、當我們對多張表進行關聯查詢的時候，以哪個表的數據作為基準表。
2、有多個索引可以使用的時候，選擇哪個索引。
實際上，對於每一種資料庫來說，優化器的模塊都是必不可少的，他們通過複雜的算法實現儘可能優化查詢效率的目標。
如果對於優化器的細節感興趣，可以看看《資料庫查詢優化器的藝術-原理解析與SQL性能優化》。
但是優化器也不是萬能的，並不是再垃圾的 SQL 語句都能自動優化，也不是每次都能選擇到最優的執行計劃，大家在編寫 SQL 語句的時候還是要注意。
如果我們想知道優化器是怎麼工作的，它生成了幾種執行計劃，每種執行計劃的 cost是多少，應該怎麼做？
1.4.3.優化器是怎麼得到執行計劃的？
首先我們要啟用優化器的追蹤（默認是關閉的）：
SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';
注意開啟這開關是會消耗性能的，因為它要把優化分析的結果寫到表裡面，所以不要輕易開啟，或者查看完之後關閉它（改成 off）。
注意：參數分為 session 和 global 級別。
接著我們執行一個 SQL 語句，優化器會生成執行計劃：
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
這個時候優化器分析的過程已經記錄到系統表裡面了，我們可以查詢：
select * from information_schema.optimizer_trace\G
它是一個 JSON 類型的數據，主要分成三部分，準備階段、優化階段和執行階段。
expanded_query 是優化後的 SQL 語句。
considered_execution_plans 裡面列出了所有的執行計劃。
分析完記得關掉它：
set optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';
1.4.4. 優化器得到的結果
優化完之後，得到一個什麼東西呢？
優化器最終會把解析樹變成一個查詢執行計劃，查詢執行計劃是一個數據結構。
當然，這個執行計劃是不是一定是最優的執行計劃呢？不一定，因為 MySQL 也有可能覆蓋不到所有的執行計劃。
我們怎麼查看 MySQL 的執行計劃呢？比如多張表關聯查詢，先查詢哪張表？在執行查詢的時候可能用到哪些索引，實際上用到了什麼索引？
MySQL 提供了一個執行計劃的工具。我們在 SQL 語句前面加上 EXPLAIN，就可以看到執行計劃的信息。
EXPLAIN select name from user where id=1
注意 Explain 的結果也不一定最終執行的方式。
1.5. 存儲引擎
得到執行計劃以後，SQL 語句是不是終於可以執行了？
問題又來了：
1、從邏輯的角度來說，我們的數據是放在哪裡的，或者說放在一個什麼結構裡面？
2、執行計劃在哪裡執行？是誰去執行？
1.5.1. 存儲引擎基本介紹
我們先回答第一個問題：在關係型資料庫裡面，數據是放在什麼結構裡面的？（放在表 Table 裡面的）
我們可以把這個表理解成 Excel 電子表格的形式。所以我們的表在存儲數據的同時，還要組織數據的存儲結構，這個存儲結構就是由我們的存儲引擎決定的，所以我們也可以把存儲引擎叫做表類型。
在 MySQL 裡面，支持多種存儲引擎，他們是可以替換的，所以叫做插件式的存儲引擎。為什麼要搞這麼多存儲引擎呢？一種還不夠用嗎？
這個問題先留著。
1.5.2. 查看存儲引擎
比如我們資料庫裡面已經存在的表，我們怎麼查看它們的存儲引擎呢？
show table status from `ToBeTopJavaer`;
或者通過 DDL 建表語句來查看。
在 MySQL 裡面，我們創建的每一張表都可以指定它的存儲引擎，而不是一個資料庫只能使用一個存儲引擎。存儲引擎的使用是以表為單位的。而且，創建表之後還可以修改存儲引擎。
我們說一張表使用的存儲引擎決定我們存儲數據的結構，那在伺服器上它們是怎麼存儲的呢？我們先要找到資料庫存放數據的路徑：
show variables like 'datadir';
默認情況下，每個資料庫有一個自己文件夾，以 ToBeTopJavaer資料庫為例。
任何一個存儲引擎都有一個 frm 文件，這個是表結構定義文件。
不同的存儲引擎存放數據的方式不一樣，產生的文件也不一樣，innodb 是 1 個，memory 沒有，myisam 是兩個。
這些存儲引擎的差別在哪呢？
1.5.3. 存儲引擎比較
常見存儲引擎
MyISAM 和 InnoDB 是我們用得最多的兩個存儲引擎，在 MySQL 5.5 版本之前，默認的存儲引擎是 MyISAM，它是 MySQL 自帶的。我們創建表的時候不指定存儲引擎，它就會使用 MyISAM 作為存儲引擎。
MyISAM 的前身是 ISAM（Indexed Sequential Access Method：利用索引，順序存取數據的方法）。
5.5 版本之後默認的存儲引擎改成了 InnoDB，它是第三方公司為 MySQL 開發的。
為什麼要改呢？最主要的原因還是 InnoDB 支持事務，支持行級別的鎖，對於業務一致性要求高的場景來說更適合。
這個裡面又有 Oracle 和 MySQL 公司的一段恩怨情仇。
InnoDB 本來是 InnobaseOy 公司開發的，它和 MySQL AB 公司合作開源了 InnoDB的代碼。但是沒想到 MySQL 的競爭對手 Oracle 把 InnobaseOy 收購了。後來 08 年 Sun 公司（開發 Java 語言的 Sun）收購了 MySQL AB，09 年 Sun 公司又被 Oracle 收購了，所以 MySQL，InnoDB 又是一家了。有人覺得 MySQL 越來越像Oracle，其實也是這個原因。
那麼除了這兩個我們最熟悉的存儲引擎，資料庫還支持其他哪些常用的存儲引擎呢？
資料庫支持的存儲引擎
我們可以用這個命令查看資料庫對存儲引擎的支持情況：
show engines ;
其中有存儲引擎的描述和對事務、XA 協議和 Savepoints 的支持。XA 協議用來實現分布式事務（分為本地資源管理器，事務管理器）。
Savepoints 用來實現子事務（嵌套事務）。創建了一個 Savepoints 之後，事務就可以回滾到這個點，不會影響到創建 Savepoints 之前的操作。
這些資料庫支持的存儲引擎，分別有什麼特性呢？
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.
應用範圍比較小。表級鎖定限制了讀/寫的性能，因此在 Web 和數據倉庫配置中，它通常用於只讀或以讀為主的工作。
特點：
1.支持表級別的鎖（插入和更新會鎖表）。不支持事務。
2.擁有較高的插入（insert）和查詢（select）速度。
3.存儲了表的行數（count 速度更快）。
（怎麼快速向資料庫插入 100 萬條數據？我們有一種先用 MyISAM 插入數據，然後修改存儲引擎為 InnoDB 的操作。）
適合：只讀之類的數據分析的項目。
InnoDB（ 2 個文件）
The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.
InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
mysql 5.7 中的默認存儲引擎。InnoDB 是一個事務安全（與 ACID 兼容）的 MySQL存儲引擎，它具有提交、回滾和崩潰恢復功能來保護用戶數據。InnoDB 行級鎖（不升級為更粗粒度的鎖）和 Oracle 風格的一致非鎖讀提高了多用戶並發性和性能。InnoDB 將用戶數據存儲在聚集索引中，以減少基於主鍵的常見查詢的 I/O。為了保持數據完整性，InnoDB 還支持外鍵引用完整性約束。
特點：
1.支持事務，支持外鍵，因此數據的完整性、一致性更高。
2.支持行級別的鎖和表級別的鎖。
3.支持讀寫並發，寫不阻塞讀（MVCC）。
4.特殊的索引存放方式，可以減少 IO，提升查詢效率。
適合：經常更新的表，存在並發讀寫或者有事務處理的業務系統。
Memory（ 1 個文件）
Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
將所有數據存儲在 RAM 中，以便在需要快速查找非關鍵數據的環境中快速訪問。這個引擎以前被稱為堆引擎。其使用案例正在減少；InnoDB 及其緩衝池內存區域提供了一種通用、持久的方法來將大部分或所有數據保存在內存中，而 ndbcluster 為大型分布式數據集提供了快速的鍵值查找。
特點：
1.把數據放在內存裡面，讀寫的速度很快，但是資料庫重啟或者崩潰，數據會全部消
失。只適合做臨時表。
2.將表中的數據存儲到內存中。
CSV（ 3 個文件）
Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
它的表實際上是帶有逗號分隔值的文本文件。csv表允許以csv格式導入或轉儲數據，以便與讀寫相同格式的腳本和應用程式交換數據。因為 csv 表沒有索引，所以通常在正常操作期間將數據保存在 innodb 表中，並且只在導入或導出階段使用 csv 表。
特點：不允許空行，不支持索引。格式通用，可以直接編輯，適合在不同資料庫之間導入導出。
Archive（ 2 個文件）
These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical,archived, or security audit information.
這些緊湊的未索引的表用於存儲和檢索大量很少引用的歷史、存檔或安全審計信息。
特點：不支持索引，不支持 update delete。
這是 MySQL 裡面常見的一些存儲引擎，我們看到了，不同的存儲引擎提供的特性都不一樣，它們有不同的存儲機制、索引方式、鎖定水平等功能。
我們在不同的業務場景中對數據操作的要求不同，就可以選擇不同的存儲引擎來滿足我們的需求，這個就是 MySQL 支持這麼多存儲引擎的原因。
1.5.4. 如何選擇存儲引擎？
如果對數據一致性要求比較高，需要事務支持，可以選擇 InnoDB。
如果數據查詢多更新少，對查詢性能要求比較高，可以選擇 MyISAM。
如果需要一個用於查詢的臨時表，可以選擇 Memory。
如果所有的存儲引擎都不能滿足你的需求，並且技術能力足夠，可以根據官網內部手冊用 C 語言開發一個存儲引擎：https://dev.mysql.com/doc/internals/en/custom-eng
1.6. 執行引擎（ Query Execution Engine） ， 返回結果
OK，存儲引擎分析完了，它是我們存儲數據的形式，繼續第二個問題，是誰使用執行計劃去操作存儲引擎呢？
這就是我們的執行引擎，它利用存儲引擎提供的相應的 API 來完成操作。
為什麼我們修改了表的存儲引擎，操作方式不需要做任何改變？因為不同功能的存儲引擎實現的 API 是相同的。
最後把數據返回給客戶端，即使沒有結果也要返回。
2. MySQL 體系結構總結
基於上面分析的流程，我們一起來梳理一下 MySQL 的內部模塊。
2.1. 模塊詳解
1、 Connector：用來支持各種語言和 SQL 的交互，比如 PHP，Python，Java 的JDBC；
2、 Management Serveices & Utilities：系統管理和控制工具，包括備份恢復、MySQL 複製、集群等等；
3、 Connection Pool：連接池，管理需要緩衝的資源，包括用戶密碼權限線程等等；
4、 SQL Interface：用來接收用戶的 SQL 命令，返回用戶需要的查詢結果
5、 Parser：用來解析 SQL 語句；
6、 Optimizer：查詢優化器；
7、 Cache and Buffer：查詢緩存，除了行記錄的緩存之外，還有表緩存，Key 緩存，權限緩存等等；
8、 Pluggable Storage Engines：插件式存儲引擎，它提供 API 給服務層使用，跟具體的文件打交道。
2.2. 架構分層
總體上，我們可以把 MySQL 分成三層，跟客戶端對接的連接層，真正執行操作的服務層，和跟硬體打交道的存儲引擎層（參考 MyBatis：接口、核心、基礎）。
2.1.1.連接層
我們的客戶端要連接到 MySQL 伺服器 3306 埠，必須要跟服務端建立連接，那麼管理所有的連接，驗證客戶端的身份和權限，這些功能就在連接層完成。
2.1.2.服務層
連接層會把 SQL 語句交給服務層，這裡面又包含一系列的流程：
比如查詢緩存的判斷、根據 SQL 調用相應的接口，對我們的 SQL 語句進行詞法和語法的解析（比如關鍵字怎麼識別，別名怎麼識別，語法有沒有錯誤等等）。然後就是優化器，MySQL 底層會根據一定的規則對我們的 SQL 語句進行優化，最後再交給執行器去執行。
2.1.3.存儲引擎
存儲引擎就是我們的數據真正存放的地方，在 MySQL 裡面支持不同的存儲引擎。再往下就是內存或者磁碟。
3. 一條更新 SQL 是如何執行的？
講完了查詢流程，我們是不是再講講更新流程、插入流程和刪除流程？
在資料庫裡面，我們說的 update 操作其實包括了更新、插入和刪除。如果大家有看過 MyBatis 的源碼，應該知道 Executor 裡面也只有 doQuery()和 doUpdate()的方法，沒有 doDelete()和 doInsert()。
更新流程和查詢流程有什麼不同呢？
基本流程也是一致的，也就是說，它也要經過解析器、優化器的處理，最後交給執行器。
區別就在於拿到符合條件的數據之後的操作。
3.1. 緩衝池 Buffer Pool
首先，InnnoDB 的數據都是放在磁碟上的，InnoDB 操作數據有一個最小的邏輯單位，叫做頁（索引頁和數據頁）。我們對於數據的操作，不是每次都直接操作磁碟，因為磁碟的速度太慢了。InnoDB 使用了一種緩衝池的技術，也就是把磁碟讀到的頁放到一塊內存區域裡面。這個內存區域就叫 Buffer Pool。下一次讀取相同的頁，先判斷是不是在緩衝池裡。
下一次讀取相同的頁，先判斷是不是在緩衝池裡面，如果是，就直接讀取，不用再次訪問磁碟。
修改數據的時候，先修改緩衝池裡面的頁。內存的數據頁和磁碟數據不一致的時候，我們把它叫做髒頁。InnoDB 裡面有專門的後台線程把 Buffer Pool 的數據寫入到磁碟，每隔一段時間就一次性地把多個修改寫入磁碟，這個動作就叫做刷髒。
Buffer Pool 是 InnoDB 裡面非常重要的一個結構，它的內部又分成幾塊區域。這裡我們趁機到官網來認識一下 InnoDB 的內存結構和磁碟結構。
3.2. InnoDB 內存結構和磁碟結構
3.3.1.內存結構
Buffer Pool 主要分為 3 個部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外還有一個（redo）log buffer。
1、 Buffer Pool
Buffer Pool 緩存的是頁面信息，包括數據頁、索引頁。
查看伺服器狀態，裡面有很多跟 Buffer Pool 相關的信息：
SHOW STATUS LIKE '%innodb_buffer_pool%';
這些狀態都可以在官網查到詳細的含義，用搜索功能。
Buffer Pool 默認大小是 128M（134217728 字節），可以調整。
查看參數（系統變量）：
SHOW VARIABLES like '%innodb_buffer_pool%';
這些參數都可以在官網查到詳細的含義，用搜索功能。
內存的緩衝池寫滿了怎麼辦？（Redis 設置的內存滿了怎麼辦？）InnoDB 用 LRU算法來管理緩衝池（鍊表實現，不是傳統的 LRU，分成了 young 和 old），經過淘汰的數據就是熱點數據。
內存緩衝區對於提升讀寫性能有很大的作用。思考一個問題：
當需要更新一個數據頁時，如果數據頁在 Buffer Pool 中存在，那麼就直接更新好了。否則的話就需要從磁碟加載到內存，再對內存的數據頁進行操作。也就是說，如果沒有命中緩衝池，至少要產生一次磁碟 IO，有沒有優化的方式呢？
2、 Change Buffer 寫緩衝
如果這個數據頁不是唯一索引，不存在數據重複的情況，也就不需要從磁碟加載索引頁判斷數據是不是重複（唯一性檢查）。這種情況下可以先把修改記錄在內存的緩衝池中，從而提升更新語句（Insert、Delete、Update）的執行速度。
這一塊區域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入緩衝，現在也能支持 delete 和 update。
最後把 Change Buffer 記錄到數據頁的操作叫做 merge。什麼時候發生 merge？有幾種情況：在訪問這個數據頁的時候，或者通過後台線程、或者資料庫 shut down、redo log 寫滿時觸發。
如果資料庫大部分索引都是非唯一索引，並且業務是寫多讀少，不會在寫數據後立刻讀取，就可以使用 Change Buffer（寫緩衝）。寫多讀少的業務，調大這個值：
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
代表 Change Buffer 占 Buffer Pool 的比例，默認 25%。
3、 Adaptive Hash Index
索引應該是放在磁碟的，為什麼要專門把一種哈希的索引放到內存？好好思考。
4、 （ redo） Log Buffer
思考一個問題：如果 Buffer Pool 裡面的髒頁還沒有刷入磁碟時，資料庫宕機或者重啟，這些數據丟失。如果寫操作寫到一半，甚至可能會破壞數據文件導致資料庫不可用。
為了避免這個問題，InnoDB 把所有對頁面的修改操作專門寫入一個日誌文件，並且在資料庫啟動時從這個文件進行恢復操作（實現 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 的概念。
磁碟的最小組成單元是扇區，通常是 512 個字節。
作業系統和內存打交道，最小的單位是頁 Page。
作業系統和磁碟打交道，讀寫磁碟，最小的單位是塊 Block。
如果我們所需要的數據是隨機分散在不同頁的不同扇區中，那麼找到相應的數據需要等到磁臂旋轉到指定的頁，然後碟片尋找到對應的扇區，才能找到我們所需要的一塊數據，一次進行此過程直到找完所有數據，這個就是隨機 IO，讀取數據速度較慢。
假設我們已經找到了第一塊數據，並且其他所需的數據就在這一塊數據後邊，那麼就不需要重新尋址，可以依次拿到我們所需的數據，這個就叫順序 IO。
刷盤是隨機 I/O，而記錄日誌是順序 I/O，順序 I/O 效率更高。因此先把修改寫入日誌，可以延遲刷盤時機，進而提升系統吞吐。
當然 redo log 也不是每一次都直接寫入磁碟，在 Buffer Pool 裡面有一塊內存區域（Log Buffer）專門用來保存即將要寫入日誌文件的數據，默認 16M，它一樣可以節省磁碟 IO。
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
需要注意：redo log 的內容主要是用於崩潰恢復。磁碟的數據文件，數據來自 buffer pool。redo log 寫入磁碟，不是寫入數據文件。
那麼，Log Buffer 什麼時候寫入 log file？
在我們寫入數據到磁碟的時候，作業系統本身是有緩存的。flush 就是把作業系統緩衝區寫入到磁碟。
log buffer 寫入磁碟的時機，由一個參數控制，默認是 1。
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
這是內存結構的第 4 塊內容，redo log，它又分成內存和磁碟兩部分。redo log 有什麼特點？
1、redo log 是 InnoDB 存儲引擎實現的，並不是所有存儲引擎都有。
2、不是記錄數據頁更新之後的狀態，而是記錄這個頁做了什麼改動，屬於物理日誌。
3、redo log 的大小是固定的，前面的內容會被覆蓋。
check point 是當前要覆蓋的位置。如果 write pos 跟 check point 重疊，說明 redo log 已經寫滿，這時候需要同步 redo log 到磁碟中。
這是 MySQL 的內存結構，總結一下，分為：
Buffer pool、change buffer、Adaptive Hash Index、 log buffer。
磁碟結構裡面主要是各種各樣的表空間，叫做 Table space。
3.3.2.磁碟結構
表空間可以看做是 InnoDB 存儲引擎邏輯結構的最高層，所有的數據都存放在表空間中。InnoDB 的表空間分為 5 大類。
系統表空間 system tablespace
在默認情況下 InnoDB 存儲引擎有一個共享表空間（對應文件/var/lib/mysql/ibdata1），也叫系統表空間。
InnoDB 系統表空間包含 InnoDB 數據字典和雙寫緩衝區，Change Buffer 和 Undo Logs），如果沒有指定 file-per-table，也包含用戶創建的表和索引數據。
1、undo 在後面介紹，因為有獨立的表空間。
2、數據字典：由內部系統表組成，存儲表和索引的元數據（定義信息）。
3、雙寫緩衝（InnoDB 的一大特性）：
InnoDB 的頁和作業系統的頁大小不一致，InnoDB 頁大小一般為 16K，作業系統頁大小為 4K，InnoDB 的頁寫入到磁碟時，一個頁需要分 4 次寫。
如果存儲引擎正在寫入頁的數據到磁碟時發生了宕機，可能出現頁只寫了一部分的情況，比如只寫了 4K，就宕機了，這種情況叫做部分寫失效（partial page write），可能會導致數據丟失。
show variables like 'innodb_doublewrite';
我們不是有 redo log 嗎？但是有個問題，如果這個頁本身已經損壞了，用它來做崩潰恢復是沒有意義的。所以在對於應用 redo log 之前，需要一個頁的副本。如果出現了寫入失效，就用頁的副本來還原這個頁，然後再應用 redo log。這個頁的副本就是 double write，InnoDB 的雙寫技術。通過它實現了數據頁的可靠性。
跟 redo log 一樣，double write 由兩部分組成，一部分是內存的 double write，一個部分是磁碟上的 double write。因為 double write 是順序寫入的，不會帶來很大的開銷。
在默認情況下，所有的表共享一個系統表空間，這個文件會越來越大，而且它的空間不會收縮。
獨占表空間 file-per-table tablespaces
我們可以讓每張表獨占一個表空間。這個開關通過 innodb_file_per_table 設置，默認開啟。
SHOW VARIABLES LIKE 'innodb_file_per_table';
開啟後，則每張表會開闢一個表空間，這個文件就是數據目錄下的 ibd 文件（例如/var/lib/mysql/gupao/user_innodb.ibd），存放表的索引和數據。
但是其他類的數據，如回滾（undo）信息，插入緩衝索引頁、系統事務信息，二次寫緩衝（Double write buffer）等還是存放在原來的共享表空間內。
通用表空間 general tablespaces
通用表空間也是一種共享的表空間，跟 ibdata1 類似。
可以創建一個通用的表空間，用來存儲不同資料庫的表，數據路徑和文件可以自定義。語法：
create tablespace ts2673 add datafile '/var/lib/mysql/ts2673.ibd' file_block_size=16K engine=innodb;
在創建表的時候可以指定表空間，用 ALTER 修改表空間可以轉移表空間。
create table t2673(id integer) tablespace ts2673;
不同表空間的數據是可以移動的。
刪除表空間需要先刪除裡面的所有表：
drop table t2673;
drop tablespace ts2673;
臨時表空間 temporary tablespaces
存儲臨時表的數據，包括用戶創建的臨時表，和磁碟的內部臨時表。對應數據目錄下的 ibtmp1 文件。當數據伺服器正常關閉時，該表空間被刪除，下次重新產生。
Redo log
磁碟結構裡面的 redo log，在前面已經介紹過了。
undo log tablespace
undo log（撤銷日誌或回滾日誌）記錄了事務發生之前的數據狀態（不包括 select）。如果修改數據時出現異常，可以用 undo log 來實現回滾操作（保持原子性）。
在執行 undo 的時候，僅僅是將數據從邏輯上恢復至事務之前的狀態，而不是從物理頁面上操作實現的，屬於邏輯格式的日誌。
redo Log 和 undo Log 與事務密切相關，統稱為事務日誌。
undo Log 的數據默認在系統表空間 ibdata1 文件中，因為共享表空間不會自動收縮，也可以單獨創建一個 undo 表空間。
show global variables like '%undo%';
有了這些日誌之後，我們來總結一下一個更新操作的流程，這是一個簡化的過程。
name 原值是 javaHuang。
update user set name = 'penyuyan' where id=1;
1、事務開始，從內存或磁碟取到這條數據，返回給 Server 的執行器；
2、執行器修改這一行數據的值為 penyuyan；
3、記錄 name=qingshan 到 undo log；
4、記錄 name=penyuyan 到 redo log；
5、調用存儲引擎接口，在內存（Buffer Pool）中修改 name=penyuyan；
6、事務提交。
內存和磁碟之間，工作著很多後台線程。
3.3.3.後台線程
（供了解）
後台線程的主要作用是負責刷新內存池中的數據和把修改的數據頁刷新到磁碟。後台線程分為：master thread，IO thread，purge thread，page cleaner thread。
master thread 負責刷新緩存數據到磁碟並協調調度其它後台進程。
IO thread 分為 insert buffer、log、read、write 進程。分別用來處理 insert buffer、重做日誌、讀寫請求的 IO 回調。
purge thread 用來回收 undo 頁。
page cleaner thread 用來刷新髒頁。
除了 InnoDB 架構中的日誌文件，MySQL 的 Server 層也有一個日誌文件，叫做binlog，它可以被所有的存儲引擎使用。
3.3. Binlog
binlog 以事件的形式記錄了所有的 DDL 和 DML 語句（因為它記錄的是操作而不是數據值，屬於邏輯日誌），可以用來做主從複製和數據恢復。
跟 redo log 不一樣，它的文件內容是可以追加的，沒有固定大小限制。
在開啟了 binlog 功能的情況下，我們可以把 binlog 導出成 SQL 語句，把所有的操作重放一遍，來實現數據的恢復。
binlog 的另一個功能就是用來實現主從複製，它的原理就是從伺服器讀取主伺服器的 binlog，然後執行一遍。
配置方式和主從複製的實現原理在後續會有專文詳解。
有了這兩個日誌之後，我們來看一下一條更新語句是怎麼執行的：
例如一條語句：update teacher set name='盆魚宴' where id=1;
1、先查詢到這條數據，如果有緩存，也會用到緩存。
2、把 name 改成盆魚宴，然後調用引擎的 API 接口，寫入這一行數據到內存，同時記錄 redo log。這時 redo log 進入 prepare 狀態，然後告訴執行器，執行完成了，可以隨時提交。
3、執行器收到通知後記錄 binlog，然後調用存儲引擎接口，設置 redo log為 commit狀態。
4、更新完成。
這張圖片的重點：
1、先記錄到內存，再寫日誌文件。
2、記錄 redo log 分為兩個階段。
3、存儲引擎和 Server 記錄不同的日誌。
4、先記錄 redo，再記錄 binlog。