Author: ACatSmiling
Since: 2024-09-21
邏輯架構
邏輯架構剖析
伺服器處理客戶端請求
首先，MySQL 是典型的 C/S 架構，即Client/Server架構，伺服器端程式使用的是mysqld。
不論客戶端程序和伺服器程序是採用哪種方式進行通訊，最後實現的效果都是：客戶端程序向伺服器程序傳送一段文字（SQL 語句），伺服器程序處理後再向客戶端程序傳送一段文字（處理結果）。
那伺服器程序對客戶端程序傳送的請求做了什麼處理，才能產生最後的處理結果呢？這裡以查詢請求為例展示：
下面具體展開看一下：
分析：
此圖是針對 MySQL 5.7。
Connectors
Connectors：指的是不同語言中與 SQL 的互動。MySQL 首先是一 個網路程式，在 TCP 之上定義了自己的應用層協議。所以要使用 MySQL，我們可以編寫程式碼，跟 MySQL Server 建立 TCP 連線，之後按照其定義好的協議進行互動。或者比較方便的辦法是呼叫 SDK，比如 Native C API、JDBC、PHP 等各語言 MySQL Connector，或者透過 ODBC。但透過 SDK 來訪問 MySQL，本質上還是在 TCP 連線上透過 MySQL 協議跟 MySQL 進行互動。
第 1 層：連線層
系統（客戶端）訪問 MySQL 伺服器前，做的第一件事就是建立 TCP 連線。經過三次握手建立連線成功後， MySQL 伺服器對 TCP 傳輸過來的賬號密碼做身份認證、許可權獲取。
- 使用者名稱或密碼不對，會收到一個 Access denied for user 錯誤，客戶端程式結束執行。
- 使用者名稱密碼認證透過，會從許可權表查出賬號擁有的許可權與連線關聯，之後的許可權判斷邏輯，都將依賴於此時讀到的許可權。
TCP 連線收到請求後，必須要分配給一個執行緒專門與這個客戶端的互動。所以還會有個執行緒池，去走後面的流程，每一個連線從執行緒池中獲取執行緒，省去了建立和銷燬執行緒的開銷。
接著我們來思考一個問題：一個系統只會和 MySQL 伺服器建立一個連線嗎？只能有一個系統和 MySQL 伺服器建立連線嗎？
當然不是，多個系統都可以和 MySQL 伺服器建立連線，每個系統建立的連線肯定不止一個。所以，為了解決 TCP 無限建立與 TCP 頻繁建立銷燬帶來的資源耗盡、效能下降問題，MySQL 伺服器裡有專門的 TCP 連線池限制連線數，採用長連線模式複用 TCP 連線，來解決上述問題。
TCP 連線收到請求後，必須要分配給一個執行緒專門與這個客戶端進行互動。所以還會有個執行緒池，去走後面的流程。每一個連線從執行緒池中獲取執行緒，省去了建立和銷燬執行緒的開銷。這些內容我們都歸納到 MySQL 的連線管理元件中。
所以連線管理的職責是：負責認證、管理連線、獲取許可權資訊。
第 2 層：服務層
第二層架構主要完成大多數的核心服務功能，如 SQL 介面，並完成快取的查詢，SQL 的分析和最佳化及部分內建函式的執行。所有跨儲存引擎的功能也在這一層實現，如過程、函式等。在該層，伺服器會解析查詢並建立相應的內部解析樹，並對其完成相應的最佳化：如確定查詢表的順序，是否利用索引等，最後生成相應的執行操作。
如果是 SELECT 語句，伺服器還會查詢內部的快取。如果快取空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的效能。
SQL Interface：SQL 介面
- 接收使用者的 SQL 命令，並且返回使用者需要查詢的結果。比如 SELECT ... FROM 就是呼叫 SQL Interface。
- MySQL 支援 DML（資料操作語言）、DDL（資料定義語言）、檢視、儲存過程、觸發器、自定義函式等多種 SQL 語言介面。
Parser：解析器
- 在解析器中對 SQL 語句進行語法分析、語義分析。將 SQL 語句分解成資料結構，並將這個結構傳遞到後續步驟，以後 SQL 語句的傳遞和處理就是基於這個結構的。如果在分解構成中遇到錯誤，那麼就說明這個 SQL 語句是不合理的。
- SQL 命令傳遞到解析器的時候會被解析器驗證和解析，併為其建立
語法樹，並根據資料字典豐富查詢語法樹，會驗證該客戶端是否具有執行該查詢的許可權。建立好語法樹後，MySQL 還會對 SQl 查詢進行語法上的最佳化，進行查詢重寫。
Optimizer：查詢最佳化器
- SQL 語句在語法解析之後、查詢之前會使用查詢最佳化器確定 SQL 語句的執行路徑，生成一個
執行計劃。 - 這個執行計劃表明應該使用哪些索引進行查詢（全表檢索還是使用索引檢索），表之間的連線順序如何，最後會按照執行計劃中的步驟呼叫儲存引擎提供的方法來真正的執行查詢，並將查詢結果返回給使用者。
- 它使用
選取-投影-連線策略進行查詢。例如SELECT id, name FROM student WHERE gender = '女';。- 這個 SELECT 查詢先根據 WHERE 語句進行
選取，而不是將表全部查詢出來以後再進行 gender 過濾； - 這個 SELECT 查詢先根據 id 和 name 進行屬性
投影，而不是將屬性全部取出以後再進行過濾； - 然後，將這兩個查詢條件
連線起來，生成最終查詢結果。
- 這個 SELECT 查詢先根據 WHERE 語句進行
Caches & Buffers：查詢快取元件
- MySQL 內部維持著一些 Cache 和 Buffer，比如 Query Cache 用來快取一條 SELECT 語句的執行結果，如果能夠在其中找到對應的查詢結果，那麼就不必再進行查詢解析、最佳化和執行的整個過程了，直接將結果反饋給客戶端。
- 這個快取機制是由一系列小快取組成的。比如表快取，記錄快取，key 快取，許可權快取等。
- 這個查詢快取可以在不同客戶端之間共享。
- 從 MySQL 5.7.20 開始，不推薦使用查詢快取，並在 MySQL 8.0 中刪除。
第 3 層：引擎層
和其它資料庫相比，MySQL 有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用，主要體現在儲存引擎的架構上，外掛式的儲存引擎架構將查詢處理和其它的系統任務以及資料的儲存提取相分離。這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。同時，開源的 MySQL 還允許開發人員設定自己的儲存引擎。
這種高效的模組化架構為那些希望專門針對特定應用程式需求(例如資料倉儲、事務處理或高可用性情況)的人提供了巨大的好處，同時享受使用一組獨立於任何介面和服務的優勢儲存引擎。
外掛式儲存引擎層（Storage Engines），真正的負責了 MySQL 中資料的儲存和提取，對物理伺服器級別維護的底層資料執行操作，伺服器透過 API 與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣就可以根據實際需要進行選取。
MySQL 8.0.29 預設支援的儲存引擎如下：
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
儲存層
所有的資料，資料庫、表的定義，表的每一行的內容，索引，都是存在檔案系統上，以檔案的方式存在的，並完成與儲存引擎的互動。當然有些儲存引擎比如 InnoDB，也支援不使用檔案系統直接管理裸裝置，但現代檔案系統的實現使得這樣做沒有必要了。在檔案系統之下，可以使用本地磁碟，可以使用 DAS、NAS、SAN 等各種儲存系統。
小結
本節開篇所示的 MySQL 架構圖，為了熟悉 SQL 執行流程方便，可以簡化如下：
簡化為三層結構：
連線層：客戶端和伺服器端建立連線，客戶端傳送 SQL 至伺服器端。SQL 層（服務層）：對 SQL 語句進行查詢處理；與資料庫檔案的儲存方式無關。儲存引擎層：與資料庫檔案打交道，負責資料的儲存和讀取。
SQL 執行流程
MySQL 中的 SQL 執行流程
MySQL 的查詢流程：
查詢快取
Server 如果在查詢快取中發現了這條 SQL 語句，就會直接將結果返回給客戶端；如果沒有，就進入到解析器階段。需要說明的是，因為查詢快取往往效率不高，所以在 MySQL 8.0 之後捨棄了這個功能。
大多數情況查詢快取就是個雞肋，為什麼呢？
查詢快取是提前把查詢結果快取起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在 MySQL 中的查詢快取，不是快取查詢計劃，而是查詢對應的結果。這就意味著查詢匹配的魯棒性大大降低，只有相同的查詢操作才會命中查詢快取。兩個查詢請求在任何字元上的不同（例如：空格、註釋、大小寫），都會導致快取不會命中，因此 MySQL 的查詢快取命中率不高。
同時，如果查詢請求中包含某些系統函式、使用者自定義變數和函式、一些系統表，如 mysql 、information_schema、 performance_schema 資料庫中的表，那這個請求就不會被快取。以某些系統函式舉例，可能同樣的函式的兩次呼叫會產生不一樣的結果，比如函式 NOW()，每次呼叫都會產生最新的當前時間，如果在一個查詢請求中呼叫了這個函式，那即使查詢請求的文字資訊都一樣，那不同時間的兩次查詢也應該得到不同的結果，如果在第一次查詢時就快取了，那第二次查詢的時候直接使用第一次查詢的結果就是錯誤的！
此外，既然是快取，那就有快取失效的時候。MySQL 的快取系統會監測涉及到的每張表，只要該表的結構或者資料被修改，如對該表使用了 INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE 或 DROP DATABASE 語句，那使用該表的所有快取記憶體查詢都將變為無效並從快取記憶體中刪除！對於更新壓力大的資料庫來說，查詢快取的命中率會非常低。
總之，因為查詢快取往往弊大於利，查詢快取的失效非常頻繁。
一般建議大家在靜態表裡使用查詢快取，什麼叫靜態表呢？就是一般我們極少更新的表。比如，一個系統配置表、字典表，這張表上的查詢才適合使用查詢快取。好在 MySQL 也提供了這種 "按需使用" 的方式。你可以將 my.cnf 引數query_cache_type設定成DEMAND，代表當 SQL 語句中有SQL_CACHE關鍵詞時才快取。比如：
# query_cache_ type 有 3 個值：0 代表關閉查詢快取 0FF，1 代表開啟 ON，2 代表 DEMAND
query_cache_ type=2
這樣對於預設的 SQL 語句都不使用查詢快取，而對於你確定要使用查詢快取的語句，可以用 SQL_CACHE 顯式指定，像下面這個語句一樣：
SELECT SQL_CACHE * FROM test WHERE id = 5;
檢視當前 MySQL |例項是否開啟快取機制：
# MySQL 5.7 中：
mysql> show global variables like "%query_cache_type%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)
# MySQL 8.0 中：
mysql> show global variables like "%query_cache_type%";
Empty set (0.00 sec)
監控查詢快取的命中率：
show status like '%Qcache%';
Qcache_free_blocks：表示查詢快取中還有多少剩餘的 blocks，如果該值顯示較大，則說明查詢快取中的記憶體碎片過多了，可能在一定的時間進行整理。Qcache_free_memory：查詢快取的記憶體大小，透過這個引數可以很清晰的知道當前系統的查詢記憶體是否夠用，是多了，還是不夠用，DBA 可以根據實際情況做出調整。Qcache_hits：表示有多少次命中快取。我們主要可以透過該值來驗證我們的查詢快取的效果。數字越大，快取效果越理想。Qcache_inserts：表示多少次未命中然後插入，意思是新來的 SQL 請求在快取中未找到，不得不執行查詢處理，執行查詢處理後把結果 insert 到查詢快取中。這樣的情況的次數越多，表示查詢快取應用到的比較少，效果也就不理想。當然系統剛啟動後，查詢快取是空的，這很正常。Qcache_lowmem_prunes：該引數記錄有多少條查詢因為記憶體不足而被移除出查詢快取。透過這個值，使用者可以適當的調整快取大小。Qcache_not_cached：表示因為 query_cache_type 的設定而沒有被快取的查詢數量。Qcache_queries_in_cache：當前快取中快取的查詢數量。Qcache_total_blocks：當前快取的 block 數量。
解析器
在解析器中對 SQL 語句進行語法分析、語義分析。
如果沒有命中查詢快取，就要開始真正執行語句了。首先，MySQL 需要知道你要做什麼，因此需要對 SQL 語句做解析。SQL 語句的分析分為詞法分析與語法分析。
分析器先做詞法分析。輸入的是由多個字串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字串分別是什麼，代表什麼。 例如，MySQL 從輸入的 SELECT 關鍵字識別出來，這是一個查詢語句。另外，MySQL 也要把字串 T 識別成表名 T，把字串 ID 識別成列 ID 等。
接著，要做語法分析。根據詞法分析的結果，語法分析器（比如：Bison）會根據語法規則，判斷輸入的這個 SQL 語句是否滿足 MySQL 語法。
如果你的語句不對，就會收到 "" 的錯誤提醒，比如這個語句 FROM 寫成了 FRO。
mysql> SELECT * FROM user;
ERROR 1064 (42000): 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 'fro user' at line 1
如果 SQL 語句正確，則會生成一個這樣的語法樹：
下圖是 SQL 詞法分析的過程步驟：
至此我們解析器的工作任務也基本圓滿了，接下來進入到最佳化器。
最佳化器
在最佳化器中會確定 SQL 語句的執行路徑，比如是根據全表檢索，還是根據索引檢索等。
經過了解析器，MySQL 就知道你要做什麼了。在開始執行之前，還要先經過最佳化器的處理。一條查詢可以有很多種執行方式，最後都返回相同的結果，最佳化器的作用就是找到這其中最好的執行計劃。
例如，如下語句是執行兩個表的 JOIN：
SELECT * FROM test1 JOIN test2 USING(ID) WHERE test1.name = 'aaa' AND test2.name = 'bbb';
方案 1：可以先從表 test1 裡面取出 name = 'aaa' 的記錄的 ID 值，再根據 ID 值關聯到表 test2，再判斷 test2 裡面 name 的值是否等於 'bbb'。
方案 2：可以先從表 test2 裡面取出 name = 'bbb' 的記錄的 ID 值，再根據 ID 值關聯到表 test1，再判斷 test1 裡面 name 的值是否等於 'aaa'。
這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而最佳化器的作用就是決定選擇使用哪一個方案。最佳化器階段完成後，SQL 語句的執行方案就確定下來了，然後進入執行器階段。
至於最佳化器是怎麼選擇索引的，有沒有可能選擇錯等，索引章節再次說明。
在查詢最佳化器中，可以分為邏輯查詢最佳化階段和物理查詢最佳化階段。
- 邏輯查詢最佳化，是透過改變 SQL 語句的內容，來使得 SQL 查詢更高效，同時為物理查詢最佳化提供更多的候選執行計劃。通常採用的方式是對 SQL 語句進行
等價變換，對查詢進行重寫，而查詢重寫的數學基礎就是關係代數。對條件表示式進行等價謂詞重寫、條件簡化，對檢視進行重寫，對子查詢進行最佳化，對連線語義進行了外連線消除、巢狀連線消除等。 - 物理查詢最佳化，是基於
關係代數進行的查詢重寫，而關係代數的每一步都對應著物理計算，這些物理計算往往存在多種演算法，因此需要計算各種物理路徑的代價，從中選擇代價最小的作為執行計劃。在這個階段裡，對於單表和多表連線的操作，需要高效的使用索引，提升查詢效率。
執行器
截止到現在，還沒有真正去讀寫真實的表，僅僅只是產出了一個執行計劃，接下來，就進入了執行器階段。
在執行之前需要判斷該使用者是否具備許可權。如果沒有，就會返回許可權錯誤；如果具備許可權，就執行 SQL 查詢並返回結果。在 MySQL 8.0 以下的版本，如果設定了查詢快取，這時會將查詢結果進行快取。
如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，呼叫儲存引擎API對錶進行的讀寫。儲存引擎API只是抽象介面，下面還有個儲存引擎層，具體實現還是要看錶選擇的儲存引擎。
例如，表 test 中，name 欄位沒有索引，對於以下 SQL，執行器的執行流程是這樣的：
SELECT * FROM test WHERE name = 'a'
- 呼叫 InnoDB 引擎介面取 test 表的第一行，判斷 name 值是不是 a，如果不是則跳過，如果是則將這行存在結果集中。
- 呼叫引擎介面取下一行，重複相同的判斷邏輯，直到取到這個表的最後一行。
- 執行器將上述遍歷過程中，所有滿足條件的行組成的記錄集作為結果集返回給客戶端。
- 至此，這個語句就執行完成了。對於有索引的表，執行的邏輯也差不多。
執行流程
綜上，SQL 語句在 MySQL 中的流程是：SQL 語句 ---> [查詢快取] ---> 解析器 ---> 最佳化器 ---> 執行器。
MySQL 8 中的 SQL 執行原理
前面的結構圖很複雜，我們需要抓取最核心的部分：SQL 的執行原理。不同的 DBMS 的 SQL 的執行原理是相通的，只是在不同的軟體中，各有各的實現路徑。
既然一條 SQL 語句會經歷不同的模組，那我們就來看下，在不同的模組中，SQL 執行所使用的資源（時間）是怎樣的。
下面演示如何在 MySQL 中對一條 SQL 語句的執行時間進行分析。
第一步，確認 profiling 是否開啟。
瞭解查詢語句底層執行的過程：select @@profiling;，或者show variables like '%profiling%'檢視是否開啟計劃。開啟它可以讓 MySQL 收集在 SQL 執行時所使用的資源情況，命令如下：
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)
# profiling = 0 代表關閉，需要把 profiling 開啟，即設定為 1
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Profiling 功能由 MySQL 會話變數 profiling 控制，預設是 OFF（關閉狀態）。
第二步，多次執行相同的 SQL 查詢。
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM employees;
+----------+
| COUNT(1) |
+----------+
| 107 |
+----------+
1 row in set (0.01 sec)
第三步，檢視 profiles。
# 檢視當前會話產生的所有 profiles
mysql> SHOW profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00148825 | SHOW VARIABLES LIKE '%profiling%' |
| 2 | 0.00018850 | SELECT DATABASE() |
| 3 | 0.00016375 | SELECT DATABASE() |
| 4 | 0.00073875 | show databases |
| 5 | 0.00081400 | show tables |
| 6 | 0.00087100 | SELECT COUNT(1) FROM employees |
| 7 | 0.00092375 | SELECT COUNT(1) FROM employees |
| 8 | 0.00126200 | SELECT COUNT(1) FROM employees |
| 9 | 0.00078000 | SELECT COUNT(1) FROM employees |
| 10 | 0.00096625 | SELECT COUNT(1) FROM employees |
+----------+------------+-----------------------------------+
10 rows in set, 1 warning (0.00 sec)
第四步，檢視 profile。
# 預設顯示最後一次查詢的執行計劃，檢視程式的執行步驟
mysql> SHOW profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000132 |
| Executing hook on transaction | 0.000029 |
| starting | 0.000022 |
| checking permissions | 0.000013 | # 許可權檢查
| Opening tables | 0.000079 | # 開啟表
| init | 0.000009 | # 初始化
| System lock | 0.000013 | # 鎖系統
| optimizing | 0.000006 | # 最佳化查詢
| statistics | 0.000024 |
| preparing | 0.000019 | # 準備
| executing | 0.000537 | # 執行
| end | 0.000006 |
| query end | 0.000003 |
| waiting for handler commit | 0.000006 |
| closing tables | 0.000005 |
| freeing items | 0.000060 |
| cleaning up | 0.000007 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
# 也可以查詢指定的 Query ID，Query 10 即為最後一次查詢的 ID，與上面的結果相同
mysql> SHOW profile FOR QUERY 10;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000132 |
| Executing hook on transaction | 0.000029 |
| starting | 0.000022 |
| checking permissions | 0.000013 |
| Opening tables | 0.000079 |
| init | 0.000009 |
| System lock | 0.000013 |
| optimizing | 0.000006 |
| statistics | 0.000024 |
| preparing | 0.000019 |
| executing | 0.000537 |
| end | 0.000006 |
| query end | 0.000003 |
| waiting for handler commit | 0.000006 |
| closing tables | 0.000005 |
| freeing items | 0.000060 |
| cleaning up | 0.000007 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
# 還可以查詢更豐富的內容
mysql> SHOW profile cpu, block io FOR QUERY 10;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000132 | 0.000075 | 0.000061 | 0 | 0 |
| Executing hook on transaction | 0.000029 | 0.000013 | 0.000011 | 0 | 0 |
| starting | 0.000022 | 0.000011 | 0.000010 | 0 | 0 |
| checking permissions | 0.000013 | 0.000008 | 0.000006 | 0 | 0 |
| Opening tables | 0.000079 | 0.000043 | 0.000036 | 0 | 0 |
| init | 0.000009 | 0.000005 | 0.000003 | 0 | 0 |
| System lock | 0.000013 | 0.000007 | 0.000006 | 0 | 0 |
| optimizing | 0.000006 | 0.000003 | 0.000003 | 0 | 0 |
| statistics | 0.000024 | 0.000013 | 0.000010 | 0 | 0 |
| preparing | 0.000019 | 0.000011 | 0.000009 | 0 | 0 |
| executing | 0.000537 | 0.000849 | 0.000000 | 0 | 0 |
| end | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000002 | 0.000000 | 0 | 0 |
| waiting for handler commit | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| closing tables | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
| freeing items | 0.000060 | 0.000060 | 0.000000 | 0 | 0 |
| cleaning up | 0.000007 | 0.000006 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
mysql> SHOW profile cpu, block io FOR QUERY 9;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000073 | 0.000039 | 0.000032 | 0 | 0 |
| Executing hook on transaction | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |
| starting | 0.000006 | 0.000003 | 0.000003 | 0 | 0 |
| checking permissions | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |
| Opening tables | 0.000027 | 0.000015 | 0.000012 | 0 | 0 |
| init | 0.000004 | 0.000002 | 0.000001 | 0 | 0 |
| System lock | 0.000006 | 0.000003 | 0.000003 | 0 | 0 |
| optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |
| statistics | 0.000014 | 0.000008 | 0.000006 | 0 | 0 |
| preparing | 0.000012 | 0.000007 | 0.000006 | 0 | 0 |
| executing | 0.000524 | 0.000477 | 0.000390 | 0 | 0 |
| end | 0.000009 | 0.000003 | 0.000003 | 0 | 0 |
| query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |
| waiting for handler commit | 0.000006 | 0.000003 | 0.000002 | 0 | 0 |
| closing tables | 0.000006 | 0.000003 | 0.000003 | 0 | 0 |
| freeing items | 0.000073 | 0.000040 | 0.000033 | 0 | 0 |
| cleaning up | 0.000008 | 0.000004 | 0.000003 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
除了檢視 CPU、IO 阻塞等引數情況，還可以檢視下列引數的利用情況：
SQL 語法順序
隨著 MySQL 版本的更新換代，其最佳化器也在不斷的升級，最佳化器會分析不同執行順序產生的效能消耗的不同，而動態調整執行順序。
需求：查詢每個部門年齡高於 20 歲的人數，且高於 20 歲人數不能少於 2 人，顯示人數最多的部門資訊。
下面是經常出現的查詢順序：
資料庫緩衝池（Buffer Pool）
InnoDB 儲存引擎是以頁為單位來管理儲存空間的，我們進行的增刪改查操作其實本質上都是在訪問頁面（包括讀頁面、寫頁面、建立新頁面等操作）。磁碟 I/O 需要消耗的時間很多，而在記憶體中進行操作，效率則會高很多。為了能讓資料表或者索引中的資料隨時被所用，DBMS 會申請佔用記憶體來作為資料緩衝池，在真正訪問頁面之前，需要把在磁碟上的頁快取到記憶體中的 Buffer Pool 之後才可以訪問。
這樣做的好處是可以讓磁碟活動最小化，從而減少與磁碟直接進行 I/O 的時間。要知道，這種策略對提升 SQL 語句的查詢效能來說至關重要，如果索引的資料在緩衝池裡，那麼訪問的成本就會降低很多。
查詢快取 vs 緩衝池
查詢快取
查詢快取是提前把查詢結果快取起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在 MySQL 中的查詢快取，不是快取查詢計劃，而是查詢對應的結果。因為命中條件苛刻，而且只要資料表發生變化，查詢快取就會失效，因此命中率低。
緩衝池
首先需要了解在 InnoDB 儲存引擎中，緩衝池都包括了哪些。
在 InnoDB 儲存引擎中有一部分資料會放到記憶體中，緩衝池則佔了這部分記憶體的大部分，它用來儲存各種資料的快取，如下圖所示：
從圖中，能看到 InnoDB 緩衝池包括了資料頁、索引頁、插入緩衝、鎖資訊、自適應 Hash 和資料字典資訊等。
快取池的重要性：
對於使用 InnoDB 作為儲存引擎的表來說，不管是用於儲存使用者資料的索引（包括聚簇索引和二級索引），還是各種系統資料，都是以頁的形式存放在表空間中的，而所謂的表空間只不過是 InnoDB 對檔案系統上一個或幾個實際檔案的抽象，也就是說資料歸根結底還是儲存在磁碟上。同時，磁碟的速度，遠遠跟不上 CPU 的速度。因此，緩衝池可以很大程度上消除 CPU 和磁碟之間的鴻溝。
所以，InnoDB 儲存引擎在處理客戶端的請求時，當需要訪問某個頁的資料時，就會把完整的頁資料全部載入到記憶體中，也就是說，即使只需要訪問一個頁的一條記錄，也需要先把整個頁的資料載入到記憶體中。將整個頁載入到記憶體中後，就可以進行讀寫訪問，在進行完讀寫訪問之後，並不會立即把頁對應的記憶體空間釋放掉，而是將其快取起來，這樣將來有請求再次訪問該頁面時，就可以省去磁碟 I/O 的開銷。
快取原則：
位置 * 頻次這個原則，可以對磁碟 I/O 訪問效率進行最佳化。
首先，位置決定效率，提供緩衝池就是為了在記憶體中可以直接訪問資料。
其次，頻次決定優先順序順序。因為緩衝池的大小是有限的，比如磁碟有 200 GB，但是記憶體只有 16 GB，緩衝池大小隻有 1 GB，就無法將所有資料都載入到緩衝池裡，這時就涉及到優先順序順序，會優先對使用頻次高的熱資料進行載入。
緩衝池的預讀特性：
緩衝池的作用是提升磁碟 I/O 效率，而進行讀取資料的時候，存在一個區域性性原理，也就是使用了一些資料後，大機率還會使用它周圍的一些資料，因此採用預讀的機制提前載入，可以減少未來可能的磁碟 I/O 操作。
緩衝池如何讀取資料
緩衝池管理器會盡量將經常使用的資料儲存起來，在資料庫進行頁面讀操作的時候，首先會判斷該頁面是否在緩衝池中，如果存在就直接讀取，如果不存在，就會透過記憶體或磁碟將頁面存放到緩衝池中再進行讀取。
快取在資料庫中的結構和作用如下圖所示：
如果執行 SQL 語句的時候更新了快取池中的資料，那麼這些資料會馬上同步到磁碟上嗎？
實際上，當對資料庫中的記錄進行修改的時候，首先會修改緩衝池中頁裡面的記錄資訊，然後資料庫會以一定的頻率重新整理到磁碟上。注意：並不是每一次發生更新操作，都會立刻進行磁碟迴寫，緩衝池會採用一種叫做checkpoint的機制，將資料回寫到磁碟上，這樣做的好處就是提升了資料庫的整體效能。
比如，當緩衝池不夠用時，需要釋放掉一些不常用的頁，此時就可以強行採用 checkpoint 的方式，將不常用的髒頁回寫到磁碟上，然後再從緩衝池中將這些頁釋放掉。這些髒頁（dirty page）指的是緩衝池中被修改過的頁，與磁碟上的資料頁不一致。
檢視和設定緩衝池的大小
如果你使用的是 MySQL MyISAM 儲存引擎,它只快取索引，不快取資料，對應的鍵快取引數key_buffer_size，你可以用它進行檢視。
如果你使用的是 InnoDB 儲存引擎，可以透過檢視innodb_buffer_pool_size變數來檢視緩衝池的大小。命令如下：
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
InnoDB 的緩衝池大小，預設只有 $134217728 / 1024 / 1024 = 128$ MB。緩衝池大小可以修改，比如改為 256 MB：
mysql> SET GLOBAL innodb_buffer_pool_size = 268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.00 sec)
也可以透過配置檔案修改：
[server] innodb_buffer_pool_size = 268435456
多個緩衝池例項
Buffer Pool 本質是 InnoDB 向作業系統申請的一塊連續的記憶體空間，在多執行緒環境下，訪問 Buffer Pool 中的資料都需要加鎖處理。在 Buffer Pool 特別大而且多執行緒併發訪問特別高的情況下，單一的 Buffer Pool 可能會影響請求的處理速度。所以在 Buffer Pool 特別大的時候，可以把它們拆分成若干個小的 Buffer Pool，每個 Buffer Pool 都稱為一個例項，它們彼此是獨立的，獨立的申請記憶體空間，獨立的管理各種連結串列。因此，在多執行緒併發訪問時並不會相互影響，從而提高併發處理能力。
可以在伺服器啟動的時候，透過設定innodb_buffer_pool_instances的值，來修改 Buffer Pool 例項的個數：
[server]
innodb_buffer_pool_instances = 1
這樣就表明我們要建立 1 個 Buffer Pool 例項。
命令檢視緩衝池例項的個數：
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)
每個 Buffer Pool 例項實際佔用的記憶體空間：innodb_buffer_pool_size/innodb_buffer_pool_instances。也就是總共的緩衝池大小除以例項的個數，結果就是每個 Buffer Pool 例項佔用的記憶體空間大小。
不過也不是 Buffer Pool 的例項建立的越多越好，分別管理各個 Buffer Pool 也需要效能開銷，InnoDB 規定：當 innodb_buffer_pool_size 的值小於 1 GB 的時候，設定多個例項是無效的，此時，InnoDB 會預設把 innodb_buffer_pool_instances 的值修改為 1。當 innodb_buffer_pool_size 的值大於或等於 1 GB 的時候，建議設定多個例項。
引申問題
Buffer Pool 是 MySQL 記憶體結構中十分核心的一個組成，可以先把它想象成一個黑盒子。
黑盒下的更新資料流程：
當查詢資料的時候，會先去 Buffer Pool 中查詢，如果 Buffer Pool 中不存在，儲存引擎會先將資料從磁碟載入到 Buffer Pool 中，然後將資料返回給客戶端。同理，當更新某個資料的時候，如果這個資料不存在於 Buffer Pool 中，儲存引擎也會先將資料從磁碟載入到 Buffer Pool 中，然後修改 Buffer Pool 中的資料，被修改過的資料（髒資料），會在之後統一刷入磁碟中。
這個過程存在一個問題，假如修改 Buffer Pool 中的資料成功，但還未將資料刷入磁碟時，MySQL 服務發生異常當機。此時，更新後的資料只存在於 Buffer Pool 中，因為 MySQL 當機，會導致這部分修改的資料丟失。再者，更新操作到一半時，MySQL 服務發生異常當機，此時，需要回滾到更新之前的狀態，又該如何處理呢？
答案是：redo log 和 undo log。
儲存引擎
為了便於管理，人們把連線管理、查詢快取、語法解析、查詢最佳化這些並不涉及真實資料儲存的功能劃分為MySQL Server的功能，把真實存取資料的功能劃分為儲存引擎的功能。在 MySQL Server 完成了查詢最佳化後，只需要按照生成的執行計劃呼叫底層儲存引擎提供的 API，獲取到資料後返回給客戶端就可以了。
簡而言之，儲存引擎就是指表的型別。儲存引擎最開始叫表處理器，後來改名為儲存引擎，它的功能就是接收上層傳下來的指令，然後對錶中的資料進行提取或寫入操作。
檢視儲存引擎
檢視 MySQL 提供的儲存引擎：
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
設定系統預設的儲存引擎
檢視預設的儲存引擎：
# 方式一
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)
# 方式二
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
如果在建立表的語句中沒有顯式指定表的儲存引擎的話，那就會預設使用 InnoDB 作為表的儲存引擎。如果想改變表的預設儲存引擎的話，可以這樣寫啟動伺服器的命令列：
SET DEFAULT_STORAGE_ENGINE = MyISAM;
或者修改 my.cnf 檔案，然後重啟服務：
default-storage-engine=MyISAM
設定表的儲存引擎
儲存引擎是負責對錶中的資料進行提取和寫入工作的，可以為不同的表設定不同的儲存引擎，也就是說不同的表可以有不同的物理儲存結構，不同的提取和寫入方式。
建立表時指定儲存引擎
顯式的指定表的儲存引擎：
CREATE TABLE 表名(
建表語句;
) ENGINE = 儲存引擎名稱
修改表的儲存引擎
如果表已經建好了，也可以修改表的儲存引擎：
ALTER TABLE 表名 ENGINE = 儲存引擎名稱
修改完後，可以檢視錶結構驗證是否修改成功，例如：
mysql> SHOW CREATE TABLE engine_demo_table\G
*************************** 1. row ***************************
Table: engine_demo_table
Create Table: CREATE TABLE `engine_demo_table` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
引擎介紹
InnoDB 引擎
InnoDB 是具備外來鍵支援功能的事務儲存引擎。
- MySQL 從 3.23.34a 開始就包含 InnoDB 儲存引擎，大於等於 5.5 之後，預設採用 InnoDB 引擎 。
- InnoDB 是 MySQL 的
預設事務型引擎，它被設計用來處理大量的短期（short-lived）事務，可以確保事務的完整提交（Commit）和回滾（Rollback）。 - 除了增加和查詢外，還需要更新、刪除操作，那麼，應優先選擇 InnoDB 儲存引擎。
- 除非有非常特別的原因需要使用其他的儲存引擎，否則應該優先考慮 InnoDB 引擎。
- 資料檔案結構：
- 表名.frm 儲存表結構（MySQL 8.0 時，合併在表名.ibd 中）。
- 表名.ibd 儲存資料和索引。
- InnoDB是
為處理巨大資料量的最大效能設計。- 在以前的版本中，字典資料以後設資料檔案、非事務表等來儲存，現在這些後設資料檔案被刪除了。比如：.frm，.par，.trn，.isl，.db.opt 等都在 MySQL 8.0 中不存在了。
- 對比 MyISAM 儲存引擎， InnoDB 寫的處理效率差一些 ，並且會佔用更多的磁碟空間以儲存資料和索引。
- MyISAM 只快取索引，不快取真實資料；InnoDB 不僅快取索引還要快取真實資料， 對記憶體要求較高 ，而且記憶體大小對效能有決定性的影響。
MyISAM 引擎
MyISAM 引擎是主要的非事務處理儲存引擎。
- MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函式（GIS）等，但 MyISAM 不支援事務、行級鎖、外來鍵，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。
- MySQL 5.5 之前預設的儲存引擎。
- 優勢是訪問的速度快，對事務完整性沒有要求或者以 SELECT、INSERT 為主的應用。
- 針對資料統計有額外的常數儲存，故而 COUNT(*) 的查詢效率很高。
- 資料檔案結構：
- 表名.frm 儲存表結構。
- 表名.MYD 儲存資料 (MYData)。
- 表名.MYI 儲存索引 (MYIndex)。
- 應用場景：只讀應用或者以讀為主的業務。
Archive 引擎
Archive 引擎用於資料存檔。功能：
Blackhole 引擎
Blackhole 引擎：丟棄寫操作，讀操作會返回空內容。
CSV 引擎
CSV 引擎：儲存資料時，以逗號分隔各個資料項。
示例：
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test VALUES (1, 'record one'), (2, 'record two');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+---+------------+
| i | c |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)
建立 CSV 表還會建立相應的元檔案，用於儲存表的狀態和表中存在的行數。此檔案的名稱與表的名稱相同，字尾為 CSM。如圖所示：
Memory 引擎
Memory 引擎：採用的邏輯介質是記憶體， 響應速度很快 ，但是當 mysqld 守護程序崩潰的時候資料會丟失 。另外，要求儲存的資料是資料長度不變的格式，比如，Blob 和 Text 型別的資料不可用，因為長度是不固定的。
主要特徵：
- Memory 同時支援雜湊（HASH）索引和 B+Tree 索引。
- Memory表至少比 MyISAM 表要快一個數量級。
- MEMORY 表的大小是受到限制的。表的大小主要取決於兩個引數，分別是 max_rows 和 max_heap_table_size。其中，max_rows 可以在建立表時指定；max_heap_table_size 的大小預設為 16 MB，可以按需要進行擴大。
- 資料檔案與索引檔案分開儲存。
- 缺點：資料易丟失，生命週期短。基於這個缺陷，選擇 MEMORY 儲存引擎時需要特別小心。
使用 Memory 儲存引擎的場景：
- 目標資料比較小 ，而且非常頻繁的進行訪問，在記憶體中存放資料，如果太大的資料會造成記憶體溢位。可以透過引數 max_heap_table_size 控制 Memory 表的大小，限制 Memory 表的最大的大小。
- 如果資料是臨時的，而且必須立即可用得到，那麼就可以放在記憶體中。
- 儲存在 Memory 表中的資料如果突然間丟失的話也沒有太大的關係。
Federated 引擎
Federated 引擎：訪問遠端表。Federated 引擎是訪問其他 MySQL 伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。
Merge 引擎
Merge 引擎：管理多個 MyISAM 表構成的表集合。
NDB 引擎
NDB 引擎：MySQL 叢集專用儲存引擎，也叫做 NDB Cluster 儲存引擎，主要用於 MySQL Cluster 分散式叢集環境，類似於 Oracle 的 RAC 集
群。
引擎對比
MySQL 中同一個資料庫，不同的表可以選擇不同的儲存引擎。如下表對常用儲存引擎做出了對比：
| 特點 | MyISAM | InnoDB | Memory | Merge | NDB |
|---|---|---|---|---|---|
| 儲存限制 | 有 | 64 TB | 有 | 沒有 | 有 |
事務安全性 |
支援 | ||||
鎖機制 |
表鎖，即使操作一條 記錄也會鎖住整個表，不適合高併發的操作 | 行鎖，操作時只鎖某一行，不對其它行有影響，適合高併發的操作 | 表鎖 | 表鎖 | 行鎖 |
| B 樹索引 | 支援 | 支援 | 支援 | 支援 | 支援 |
| 雜湊索引 | 支援 | 支援 | |||
| 全文索引 | 支援 | ||||
| 叢集索引 | 支援 | ||||
| 資料快取 | 支援 | 支援 | |||
索引快取 |
只快取索引，不快取真實資料 | 不僅快取索引還要快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響 | 支援 | 支援 | 支援 |
| 資料可壓縮 | 支援 | ||||
| 空間使用 | 低 | 高 | N/A | 低 | 低 |
| 記憶體使用 | 低 | 高 | 中等 | 低 | 高 |
| 批次插入的速度 | 高 | 低 | 高 | 高 | 高 |
支援外來鍵 |
支援 |
MyISAM vs InnoDB
MySQL 5.5 之前預設的儲存引擎是 MyISAM，5.5 之後改為了 InnoDB。
首先，對於 InnoDB 儲存引擎，提供了良好的事務管理、崩潰修復能力和併發控制。因為 InnoDB 儲存引擎支援事務，所以對於要求事務完整性的場合，需要選擇 InnoDB。比如資料操作除了插入和查詢以外，還包含很多更新、刪除操作，像財務系統等對資料準確性要求較高的系統。缺點是讀寫效率稍差，佔用的資料空間相對較大。
其次，對於 MyISAM 儲存引擎，如果是小型應用，系統以讀操作和插入操作為主，只有很少的更新、刪除操作，並且對事務的要求沒有那麼高，則可以選擇這個儲存引擎。MyISAM 儲存引擎的優勢在於佔用空間小、處理速度快。缺點是不支援事務的完整性和併發性。
這兩種儲存引擎各有特點，當然，在 MySQL 中，也可以針對不同的資料表，可以選擇不同的儲存引擎。
| 對比項 | MyISAM | InnoDB |
|---|---|---|
| 外來鍵 | 不支援 | 支援 |
| 事務 | 不支援 | 支援 |
| 行表鎖 | 表鎖，即使操作一條記錄，也會鎖住整個表，不適合高併發的操作 | 行鎖，操作時只鎖某一行，對其他行沒有影響，適合高併發的操作 |
| 快取 | 只快取索引，不快取真實資料 | 不僅快取索引，也快取真實資料，對記憶體要求較高，而且記憶體大小對效能有決定性的影響 |
| 自帶系統表使用 | Y | N |
| 關注點 | 效能：節省資源、消耗少、簡單業務 | 事務：併發寫、事務、更大資源 |
| 預設安裝 | Y | Y |
| 預設使用 | N | Y |
InnoDB 補充
InnoDB 表的優勢
InnoDB 儲存引擎在實際應用中擁有諸多優勢，比如操作便利、提高了資料庫的效能、維護成本低等。如果由於硬體或軟體的原因導致伺服器崩潰，那麼在重啟伺服器之後不需要進行額外的操作。InnoDB 崩潰恢復功能自動將之前提交的內容定型，然後撤銷沒有提交的程序，重啟之後繼續從崩潰點開始執行。
InnoDB 儲存引擎在主記憶體中維護緩衝池，高頻率使用的資料將在記憶體中直接被處理。這種快取方式應用於多種資訊，加速了處理程序。
在專用伺服器上，實體記憶體中高達 80% 的部分被應用於緩衝池。如果需要將資料插入不同的表中，可以設定外來鍵加強資料的完整性。更新或者刪除資料，關聯資料將會被自動更新或刪除。如果試圖將資料插入從表，但在主表中沒有對應的資料，插入的資料將被自動移除。如果磁碟或記憶體中的資料出現崩潰，在使用髒資料之前，校驗和機制會發出警告。當每個表的主鍵都設定合理時，與這些列有關的操作會被自動最佳化。插入、更新和刪除操作透過做改變緩衝自動機制進行最佳化。 InnoDB 不僅支援當前讀寫，也會緩衝改變的資料到資料流磁碟。
InnoDB 的效能優勢不只存在於長時執行查詢的大型表。在同一列多次被查詢時，自適應雜湊索引會提高查詢的速度。使用 InnoDB 可以壓縮表和相關的索引，可以在不影響效能和可用性的情況下建立或刪除索引。對於大型文字和 BLOB 資料，使用動態行形式，這種儲存佈局更高效。透過查詢 INFORMATION_SCHEMA 庫中的表可以監控儲存引擎的內部工作。在同一個語句中，InnoDB 表可以與其他儲存引擎表混用。即使有些作業系統限制檔案大小為 2 GB，InnoDB 仍然可以處理。 當處理大資料量時，InnoDB 兼顧 CPU，以達到最大效能。
InnoDB 和 ACID 模型
ACID 模型是一系列資料庫設計規則，這些規則著重強調可靠性，而可靠性對於商業資料和任務關鍵型應用非常重要。MySQL 包含類似 InnoDB 儲存引擎的元件，與 ACID 模型緊密相連，這樣出現意外時，資料不會崩潰，結果不會失真。如果依賴 ACID 模型，可以不使用一致性檢查和崩潰恢復機制。如果擁有額外的軟體保護，極可靠的硬體或者應用可以容忍一小部分的資料丟失和不一致，可以將 MySQL 設定調整為只依賴部分 ACID 特性，以達到更高的效能。下面是 InnoDB 儲存引擎與 ACID 模型相同作用的四個方面：
原子方面：ACID 的原子方面主要涉及 InnoDB 事務。與 MySQL 相關的特性主要包括：- 自動提交設定。
- COMMIT 語句。
- ROLLBACK 語句。
- 操作 INFORMATION_SCHEMA 庫中的表資料。
一致性方面：ACID 模型的一致性主要涉及保護資料不崩潰的內部 InnoDB 處理過程。與 MySQL 相關的特性主要包括：- InnoDB 雙寫快取。
- InnoDB 崩潰恢復。
隔離方面：隔離是應用於事務的級別。與 MySQL 相關的特性主要包括：- 自動提交設定。
- SET ISOLATION LEVEL 語句。
- InnoDB 鎖的低階別資訊。
耐久性方面：ACID 模型的耐久性主要涉及與硬體配置相互影響的 MySQL 軟體特性。由於硬體複雜多樣化，耐久性方面沒有具體的規則可循。與 MySQL 相關的特性有：- InnoDB 雙寫快取，透過 innodb_doublewrite 配置項配置。
- 配置項 innodb_flush_log_at_trx_commit。
- 配置項 sync_binlog。
- 配置項 innodb_file_per_table。
- 儲存裝置的寫入快取。
- 儲存裝置的備用電池快取。
- 執行 MySQL 的作業系統。
- 持續的電力供應。
- 備份策略。
- 對分散式或託管的應用，最主要的在於硬體裝置的地點以及網路情況。
InnoDB 架構
緩衝池：緩衝池是主記憶體中的一部分空間，用來快取已使用的表和索引資料。緩衝池使得經常被使用的資料能夠直接在記憶體中獲得，從而提高速度。更改快取：更改快取是一個特殊的資料結構，當受影響的索引頁不在快取中時，更改快取會快取輔助索引頁的更改。索引頁被其他讀取操作時會載入到快取池，快取的更改內容就會被合併。不同於叢集索引，輔助索引並非獨一無二的。當系統大部分閒置時，清除操作會定期執行，將更新的索引頁刷入磁碟。更新快取合併期間，可能會大大降低查詢的效能。在記憶體中，更新快取佔用一部分 InnoDB 緩衝池。在磁碟中，更新快取是系統表空間的一部分。更新快取的資料型別由 innodb_change_buffering 配置項管理。自適應雜湊索引：自適應雜湊索引將負載和足夠的記憶體結合起來，使得 InnoDB 像記憶體資料庫一樣執行，不需要降低事務上的效能或可靠性。這個特性透過 innodb_adaptive_hash_index 選項配置，或者透過 --skip-innodb_adaptive_hash_index 命令列在服務啟動時關閉。重做日誌快取：重做日誌快取存放要放入重做日誌的資料。重做日誌快取大小透過 innodb_log_buffer_size 配置項配置。重做日誌快取會定期地將日誌檔案刷入磁碟。大型的重做日誌快取使得大型事務能夠正常執行而不需要寫入磁碟。系統表空間：系統表空間包括 InnoDB 資料字典、雙寫快取、更新快取和撤銷日誌，同時也包括表和索引資料。多表共享，系統表空間被視為共享表空間。雙寫快取：雙寫快取位於系統表空間中，用於寫入從快取池重新整理的資料頁。只有在重新整理並寫入雙寫快取後，InnoDB 才會將資料頁寫入合適的位置。撤銷日誌：撤銷日誌是一系列與事務相關的撤銷記錄的集合，包含如何撤銷事務最近的更改。如果其他事務要查詢原始資料，可以從撤銷日誌記錄中追溯未更改的資料。撤銷日誌存在於撤銷日誌片段中，這些片段包含於回滾片段中。每個表一個檔案的表空間：每個表一個檔案的表空間是指每個單獨的表空間建立在自身的資料檔案中，而不是系統表空間中。這個功能透過 innodb_file_per_table 配置項開啟。每個表空間由一個單獨的 .ibd 資料檔案代表，該檔案預設被建立在資料庫目錄中。通用表空間：使用 CREATE TABLESPACE 語法建立共享的 InnoDB 表空間。通用表空間可以建立在 MySQL 資料目錄之外能夠管理多個表並支援所有行格式的表。撤銷表空間：撤銷表空間由一個或多個包含撤銷日誌的檔案組成。撤銷表空間的數量由 innodb_undo_tablespaces 配置項配置。臨時表空間：使用者建立的臨時表空間和基於磁碟的內部臨時表都建立於臨時表空間。innodb_temp_data_file_path 配置項定義了相關的路徑、名稱、大小和屬性。如果該值為空，預設會在 innodb_data_home_dir 變數指定的目錄下建立一個自動擴充套件的資料檔案。重做日誌：重做日誌是基於磁碟的資料結構，在崩潰恢復期間使用，用來糾正資料。正常操作期間，重做日誌會將請求資料進行編碼，這些請求會改變 InnoDB 表資料。遇到意外崩潰後，未完成的更改會自動在初始化期間重新進行。
原文連結
https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md