C-04.MySQL邏輯架構

来源:https://www.cnblogs.com/changming06/p/18095633
-Advertisement-
Play Games

C-04.邏輯架構 1.邏輯架構剖析 1.1 伺服器處理客戶端請求 首先MySQL是典型的C/S架構，即Client/Server架構，客戶端使用的是mysql，伺服器端程式使用的mysqld。 不論客戶端進程和伺服器進程是採用那種方式進行通信，最後實現的效果都是：客戶端進程向伺服器進程發送一段文本 ...


C-04.邏輯架構

1.邏輯架構剖析

1.1 伺服器處理客戶端請求

首先MySQL是典型的C/S架構，即Client/Server架構，客戶端使用的是mysql，伺服器端程式使用的mysqld。

不論客戶端進程和伺服器進程是採用那種方式進行通信，最後實現的效果都是：客戶端進程向伺服器進程發送一段文本(SQL語句)，伺服器進程處理後再向客戶端進程發送一段文本(處理結果)。

那伺服器進程對客戶端進程發送的請求做了什麼處理，才能產生最後的處理結果呢？這裡以查詢請求為例展示：


下麵具體展開看一下：

1.2 Connectors

Connectors，指的是不同語言中與SQL的交互。MySQL首先是一個網路程式，在TCP之上定義了自己的應用層協議。所以要使用MySQL，我們可以編寫代碼，跟MySQL Server建立TCP連接，之後按照其定義好的協議進行交互。或者比較方便的辦法是調用SDK，比如Native C API，JDBC，PHP等各語言MySQL Connector，或者通過ODBC。但通過SDK來訪問MySQL，本質上還是在TCP連接上通過MySQL協議跟MySQL進行交互。接下來的MySQL Server結構可以分為如下的三層。

1.3 第1層：連接層

系統(客戶端)訪問MySQL伺服器前，做的第一件事就是建立TCP連接。

經過三次握手建立連接成功後，MySQL伺服器對TCP傳輸過來的賬號密碼做身份認證，許可權獲取。

  • 用戶名或密碼不對，會收到一個Access denied for user錯誤，客戶端程式結束執行。
  • 用戶名密碼認證通過，會從許可權表查出賬號擁有的許可權與連接關聯，之後的許可權判斷邏輯，都將依賴於此時讀到的許可權。

一個系統只會和MySQL伺服器建立一個連接嗎？只能有一個系統和MySQL伺服器建立連接嗎？

當然不是，多個系統都可以和MySQL伺服器建立連接，每個系統建立的連接肯定不止一個。所以，為瞭解決TCP無限創建與TCP頻繁創建銷毀帶來的資源耗盡、性能下降問題。MySQL伺服器里有專門的TCP連接池限制連接數，採用長連接模式復用TCP連接，來解決上述問題。


TCP連接收到請求後，必須要分配給一個線程專門與這個客戶端的交互。所以還會有個線程池，去走後面的流程。每一個連接從線程池中獲取線程，省去了創建和銷毀線程的開銷。

這些內容我們都歸納到MySQL的連接管理組件中。

所以連接管理的職責是負責認證，管理連接，獲取許可權信息。

1.4 第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進行屬性投影，而不是將屬性全部取出以後再進行過濾，將這兩個查詢條件連接起來生成最終查詢結果。

  • Caches & Buffers：查詢緩存組件

    • MySQL內部維持著一些Cache和Buffer，比如Query Cache用來緩存一條SELECT語句的執行結果，如果能夠在其中找到對應的查詢結果，那麼就不必再進行查詢解析、優化和執行的整個過程了，直接將結果反饋給客戶端。

    • 這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，許可權緩存等 。

    • 這個查詢緩存可以在不同客戶端之間共用。

    • 從MySQL 5.7.20開始，不推薦使用查詢緩存，併在MySQL 8.0中刪除。

1.5 第3層：引擎層

插件式存儲引擎層(Storage Engines)，真正的負責了MySQL中數據的存儲和提取，對物理伺服器級別維護的底層數據執行操作，伺服器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。

MySQL8.0.25預設支持的存儲引擎如下

1.6 存儲層

所有的數據，資料庫、表的定義，表的每一行的內容，索引，都是存在文件系統上，以文件的方式存在的，並完成與存儲引擎的交互。當然有些存儲引擎比如InnoDB，也支持不使用文件系統直接管理裸設備，但現代文件系統的實現使得這樣做沒有必要了。在文件系統之下，可以使用本地磁碟，可以使用DAS、NAS、SAN等各種存儲系統。

1.7 小結


簡化為三層結構：

  1. 連接層：客戶端和伺服器端建立連接，客戶端發送 SQL 至伺服器端；
  2. SQL 層(服務層)：對 SQL 語句進行查詢處理；與資料庫文件的存儲方式無關；
  3. 存儲引擎層：與資料庫文件打交道，負責數據的存儲和讀取。

2.SQL執行流程

2.1 MySQL中的SQL執行流程


MySQL的查詢流程

1.查詢緩存：Server 如果在查詢緩存中發現了這條 SQL 語句，就會直接將結果返回給客戶端；如果沒有，就進入到解析器階段。需要說明的是，因為查詢緩存往往效率不高，所以在 MySQL8.0 之後就拋棄了這個功能。

大多數情況查詢緩存就是個雞肋，為什麼呢？

查詢緩存是提前把查詢結果緩存起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在MySQL 中的查詢緩存，不是緩存查詢計劃，而是查詢對應的結果。這就意味著查詢匹配的魯棒性大大降低，只有 相同的查詢操作才會命中查詢緩存 。兩個查詢請求在任何字元上的不同（例如：空格、註釋、大小寫），都會導致緩存不會命中。因此 MySQL 的查詢緩存命中率不高。

魯棒是Robust的音譯，也就是健壯和強壯的意思。它是在異常和危險情況下系統生存的能力。

同時，如果查詢請求中包含某些系統函數、用戶自定義變數和函數、一些系統表，如 mysql 、information_schema、 performance_schema 資料庫中的表，那這個請求就不會被緩存。以某些系統函數舉例，可能同樣的函數的兩次調用會產生不一樣的結果，比如函數NOW，每次調用都會產生最新的當前時間，如果在一個查詢請求中調用了這個函數，那即使查詢請求的文本信息都一樣，那不同時間的兩次查詢也應該得到不同的結果，如果在第一次查詢時就緩存了，那第二次查詢的時候直接使用第一次查詢的結果就是錯誤的！

此外，既然是緩存，那就有它緩存失效的時候。MySQL的緩存系統會監測涉及到的每張表，只要該表的結構或者數據被修改，如對該表使用了INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE語句，那使用該表的所有高速緩存查詢都將變為無效並從高速緩存中刪除！對於更新壓力大的資料庫來說，查詢緩存的命中率會非常低。

總之，因為查詢緩存往往弊大於利，查詢緩存的失效非常頻繁。

一般建議大家在靜態表裡使用查詢緩存，什麼叫靜態表呢？就是一般我們極少更新的表。比如，一個系統配置表、字典表，這張表上的查詢才適合使用查詢緩存。不過MySQL對於查詢緩存提供了按需使用的方式。可以將my.cnd參數query_cache_type設置成DEMAND，代表當sql語句中有SQL_CACHE關鍵詞時，才緩存。比如：

#query_cache_type有3個值，0代表關閉，1代表開啟，2代表DEMAND
query_cache_type=2

這樣對於預設的sql語句都不適用查詢緩存，而對於確定要使用的查詢緩存語句，可以用SQL_CACHE顯示指定

select SQL_CACHE * FROM test where id = 5;

查看，是否開啟查詢緩存

mysql> select @@global.query_cache_type;
+---------------------------+
| @@global.query_cache_type |
+---------------------------+
| ON                        |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

#mysql8.0中執行
mysql> select @@global.query_cache_type;
ERROR 1193 (HY000): Unknown system variable 'query_cache_type'

監控插敘緩存的命中率

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1021240 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 7       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Qcache_free_blocks:表示查詢緩存中還有多少剩餘的blocks，如果該值顯示較大，則說明查詢緩存中的記憶體碎片過多了，可能在一定的時間進行整理。

Qcache_free_memory :查詢緩存的記憶體大小，通過這個參數可以很清晰的知道當前系統的查詢記憶體是否夠用，是多了，還是不夠用，DBA可以根據實際情況做出調整。

Qcache_hits :表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數字越大，緩存效果越理想。

Qcache_inserts:表示多少次未命中然後插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理後把結果insert到查詢緩存中。這樣的情況的次數越多，表示查詢緩存應用到的比較少，效果也就不理想。當然系統剛啟動後，查詢緩存是空的，這很正常。
Qcache_lowmem_prunes:該參數記錄有多少條查詢因為記憶體不足而被移除出查詢緩存。通過這個值用戶可以適當的調整緩存大小。

Ocache_not_cached:表示因為query_cache_type的設置而沒有被緩存的查詢數量。

Qcache_queries_in_cache:當前緩存中緩存的查詢數量。

Qcache_total_blocks :當前緩存的block數量。

2.解析器：在解析器中對 SQL 語句進行語法分析、語義分析。


分析器先做“詞法分析”。你輸入的是由多個字元串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字元串分別是什麼，代表什麼。 MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字元串“T”識別成“表名 T”，把字元串“ID”識別成“列 ID”。

接著，要做“語法分析”。根據詞法分析的結果，語法分析器（比如：Bison）會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。

#該語句，詞法解析正確，語法錯誤
select department_id,job_id,avg(salary) from employees group by department_id;

如果SQL語句正確，則會生成一個這樣的語法樹：

3.優化器：在優化器中會確定 SQL 語句的執行路徑，比如是根據全表檢索，還是根據索引檢索等。

經過瞭解析器，MySQL就知道要做什麼了。在開始執行之前，還要先經過優化器的處理。一條查詢可以有多種執行方式，最後都返回相同的結果。優化器的作用就是找到這些執行計劃中，執行時間最短的執行計劃。

舉例：如下語句是執行兩個表的 join：

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高級課程';
/*
方案1：可以先從表 test1 裡面取出 name='zhangwei'的記錄的 ID 值，再根據 ID 值關聯到表 test2，再判
斷 test2 裡面 name的值是否等於 'mysql高級課程'。
方案2：可以先從表 test2 裡面取出 name='mysql高級課程' 的記錄的 ID 值，再根據 ID 值關聯到 test1，
再判斷 test1 裡面 name的值是否等於 zhangwei。
這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。優化
器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段。
優化器是怎麼選擇索引的，有沒有可能選擇錯等。後續章節會講到。
*/

在查詢優化器中，可以分為邏輯查詢優化階段和物理查詢優化階段。

邏輯查詢優化就是通過改變sQL語句的內容來使得sQL查詢更高效，同時為物理查詢優化提供更多的候選執行計劃。通常採用的方式是對SQL語句進行等價變換，對查詢進行重寫，而查詢重寫的數學基礎就是關係代數。對條件表達式進行等價謂詞重寫、條件簡化，對視圖進行重寫，對子查詢進行優化，對連接語義進行了外連接消除、嵌套連接消除等。

物理查詢優化是基於關係代數進行的查詢重寫，而關係代數的每一步都對應著物理計算，這些物理計算往往存在多種演算法，因此需要計算各種物理路徑的代價，從中選擇代價最小的作為執行計劃。在這個階段里，對於單表和多表連接的操作，需要高效地使用索引，提升查詢效率。

4.執行器

截止到現在，還沒有真正去讀寫真實的表，僅僅只是產出了一個執行計劃。於是就進入了執行器階段。

在執行之前需要判斷該用戶是否具備許可權。如果沒有，就會返回許可權錯誤。如果具備許可權，就執行 SQL查詢並返回結果。在 MySQL8.0 以下的版本，如果設置了查詢緩存，這時會將查詢結果進行緩存。

如果有許可權，就打開表繼續執行。打開表的時候，執行器會根據表的引擎定義，調用存儲引擎API對錶進行讀寫。存儲引擎API只是抽象介面，下麵還有存儲引擎層，具體實現，看表的存儲引擎。

select * from test where id=1;

比如：表 test 中，ID 欄位沒有索引，那麼執行器的執行流程是這樣的：

/*調用 InnoDB 引擎介面取這個表的第一行，判斷 ID 值是不是1，如果不是則跳過，如果是則將這行存在結果集中；

調用引擎介面取“下一行”，重覆相同的判斷邏輯，直到取到這個表的最後一行。

執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。*/

至此，這個語句就執行完成了。對於有索引的表，執行的邏輯也差不多。

SQL 語句在 MySQL 中的流程是： SQL語句→查詢緩存→解析器→優化器→執行器 。

2.2 MySQL8中SQL執行原理

2.2.1 確認profiling是否開啟
mysql> select @@profiling;

mysql> show variables like 'profiling';

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

profiling=0 代表關閉，我們需要把 profiling 打開，即設置為 1：

註意，profiling只能在會話層面開啟，不能配置到my.cnf中全局，開啟，如果配置，在mysqld重啟時，會報錯。

這也證明，mysql系統參數，並不是都能在my.cnf文件中配置使用，有些是在安裝時，就指定了，比如表名的大小寫等。

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.2.2 多次執行相同SQL查詢
mysql> select * from employees;

mysql> select * from employees;
2.2.3 查看profiles

查看當前會話所產生的所有profiles

mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00036725 | SELECT DATABASE()       |
|        2 | 0.01012700 | show databases          |
|        3 | 0.00398050 | show tables             |
|        4 | 0.00237800 | select * from employees |
|        5 | 0.00043100 | select * from employees |
+----------+------------+-------------------------+
5 rows in set, 1 warning (0.00 sec)
2.2.4 查看profile

顯示執行計劃，查看程式的執行步驟：

mysql> show profile;#預設顯示最近一次sql語句的執行計劃


也可以查詢指定的Query ID

mysql> show profile for query 5;

也可以查詢CPU，IO相關的內容

mysql>  show profile cpu,block io for query 5;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000101 | 0.000021 |   0.000078 |            0 |             0 |
| Executing hook on transaction  | 0.000007 | 0.000001 |   0.000003 |            0 |             0 |
| starting                       | 0.000007 | 0.000001 |   0.000005 |            0 |             0 |
| checking permissions           | 0.000005 | 0.000001 |   0.000004 |            0 |             0 |
| Opening tables                 | 0.000025 | 0.000005 |   0.000021 |            0 |             0 |
| init                           | 0.000005 | 0.000001 |   0.000003 |            0 |             0 |
| System lock                    | 0.000007 | 0.000002 |   0.000005 |            0 |             0 |
| optimizing                     | 0.000055 | 0.000056 |   0.000000 |            0 |             0 |
| statistics                     | 0.000014 | 0.000013 |   0.000000 |            0 |             0 |
| preparing                      | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
| executing                      | 0.000119 | 0.000119 |   0.000000 |            0 |             0 |
| end                            | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| query end                      | 0.000023 | 0.000024 |   0.000000 |            0 |             0 |
| waiting for handler commit     | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000026 | 0.000027 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

2.3 MySQL 5.7中SQL執行原理

2.3.1 配置文件中開啟查詢緩存

在/etc/my.cnf中新增一行

query_chche_type=1
2.3.2 重啟mysql服務
systemctl restart mysqld
2.3.3 開啟查詢執行計劃
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.3.4 執行兩次sql
mysql> select * from employees;

mysql> select * from employees;
2.3.5 查看profiles
mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00018050 | SELECT DATABASE()       |
|        2 | 0.00053900 | show databases          |
|        3 | 0.00014850 | show tables             |
|        4 | 0.00008875 | select * from employees |
|        5 | 0.00004925 | select * from employees |
+----------+------------+-------------------------+
5 rows in set, 1 warning (0.00 sec)
2.3.6 查看執行計劃

顯示執行計劃，查看程式的執行步驟：

mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000005 |
| starting                       | 0.000003 |
| checking query cache for query | 0.000012 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000007 |
| sending cached result to clien | 0.000030 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

從截圖中可以看出查詢語句直接從緩存中獲取數據。

2.4 SQL語法順序

3.資料庫緩衝池(Buffer pool 瞭解)

InnoDB存儲引擎是以頁為單位來管理存儲空間的，我們進行的增刪改查操作其實本質上都是在訪問頁面（包括讀頁面、寫頁面、創建新頁面等操作）。而磁碟 I/O 需要消耗的時間很多，而在記憶體中進行操作，效率則會高很多，為了能讓數據表或者索引中的數據隨時被我們所用，DBMS 會申請占用記憶體來作為數據緩衝池，在真正訪問頁面之前，需要把在磁碟上的頁緩存到記憶體中的Buffer Pool之後才可以訪問。

這樣做的好處是可以讓磁碟活動最小化，從而減少與磁碟直接進行 I/O 的時間。要知道，這種策略對提升 SQL 語句的查詢性能來說至關重要。如果索引的數據在緩衝池裡，那麼訪問的成本就會降低很多。

3.1 緩衝池 VS 查詢緩存

緩衝池和查詢緩存是一個東西嗎？不是。

3.1.1 緩衝池（Buffer Pool）

首先我們需要瞭解在 InnoDB 存儲引擎中，緩衝池都包括了哪些。

在 InnoDB 存儲引擎中有一部分數據會放到記憶體中，緩衝池則占了這部分記憶體的大部分，它用來存儲各種數據的緩存，如下圖所示：


從圖中，你能看到 InnoDB 緩衝池包括了數據頁、索引頁、插入緩衝、鎖信息、自適應 Hash 和數據字典信息等。

緩存池的重要性：

對於使用InnoDB作為存儲引擎的表來說，不管是用於存儲用戶數據的索引(包括聚簇索引和二級索引)，還是各種系統數據，都是以頁的

形式存放在表空間中的，而所謂的表空間只不過是InnoDB對文件系統上一個或幾個實際文件的抽象，也就是說我們的數據說到底還是存

儲在磁碟上的。但是磁碟的速度慢的跟烏龜一樣，怎麼能配得上“快如風，疾如電”的CPU呢?這裡，緩衝池可以幫助我們消除CPU和磁碟之

間的鴻溝。所以InnoDB存儲引擎在處理客戶端的請求時，當需要訪問某個頁的數據時，就會把完整的頁的數據全部載入到記憶體中，也就

是說即使我們只需要訪問一個頁的一條記錄，那也需要先把整個頁的數據載入到記憶體中。將整個頁載入到記憶體中後就可以進行讀寫訪問

了，在進行完讀寫訪問之後並不著急把該頁對應的記憶體空間釋放掉，而是將其緩存起來，這樣將來有請求再次訪問該頁面時，就可以省去磁碟IO的開銷了。

緩存原則：

“位置 * 頻次”這個原則，可以幫我們對 I/O 訪問效率進行優化。

首先，位置決定效率，提供緩衝池就是為了在記憶體中可以直接訪問數據。其次，頻次決定優先順序順序。因為緩衝池的大小是有限的，比如磁碟有 200G，但是記憶體只有 16G，緩衝池大小隻有 1G，就無法將所有數據都載入到緩衝池裡，這時就涉及到優先順序順序，會優先對使用頻次高的熱數據進行載入。

緩衝池的預讀特性：

瞭解了緩衝池的作用之後，我們還需要瞭解緩衝池的另一個特性:預讀。

緩衝池的作用就是提升I/o效率，而我們進行讀取數據的時候存在一個“局部性原理”，也就是說我們使用了一些數據，大概率還會使用它周圍的一些數據，因此採用“預讀”的機制提前載入，可以減少未來可能的磁碟I/O操作。

3.1.2 查詢緩存

那麼什麼是查詢緩存呢？

查詢緩存是提前把 查詢結果緩存 起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在MySQL 中的查詢緩存，不是緩存查

詢計劃，而是查詢對應的結果。因為命中條件苛刻，而且只要數據表發生變化，查詢緩存就會失效，因此命中率低。

緩衝池服務於資料庫整體的I/O操作，它們的共同點都是通過緩存的機制來提升效率。

3.2 緩衝池如何讀取數據

緩衝池管理器會儘量將經常使用的數據保存起來，在資料庫進行頁面讀操作的時候，首先會判斷該頁面是否在緩衝池中，如果存在就直接

讀取，如果不存在，就會通過記憶體或磁碟將頁面存放到緩衝池中再進行讀取。

緩存池在資料庫中的結構和作用如下圖所示：


如果我們執行 SQL 語句的時候更新了緩存池中的數據，那麼這些數據會馬上同步到磁碟上嗎？

實際上，當我們對資料庫中的記錄進行修改的時候，首先會修改緩衝池中頁裡面的記錄信息，然後資料庫會以一定的頻率刷新到磁碟上。

註意並不是每次發生更新操作，都會立刻進行磁碟回寫。緩衝池會採用一種叫做checkpoint 的機制將數據回寫到磁碟上，這樣做的好處

就是提升了資料庫的整體性能。

比如，當緩衝池不夠用時，需要釋放掉一些不常用的頁，此時就可以強行採用checkpoint 的方式將不常用的臟頁回寫到磁碟上，然後再

從緩衝池中將這些頁釋放掉。這裡臟頁（dirty page)指的是緩衝池中被修改過的頁，與磁碟上的數據頁不一致。

3.3 查看/設置緩衝池的大小

MyIASM存儲引擎，只緩存索引，不緩存數據，對應的鍵緩存參數為key_buffer_size

如果你使用的是 InnoDB 存儲引擎，可以通過查看 innodb_buffer_pool_size 變數來查看緩衝池的大小。命令如下：

mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
|                        134217728 |
+----------------------------------+
1 row in set (0.01 sec)

你能看到此時 InnoDB 的緩衝池大小隻有 134217728/1024/1024=128MB。我們可以修改緩衝池大小，比如改為256MB，方法如下：

set global innodb_buffer_pool_size = 268435456;

或者

[server]
innodb_buffer_pool_size = 268435456 #需要重啟mysql服務

mysql> select @@global.innodb_buffer_pool_size;
+----------------------------------+
| @@global.innodb_buffer_pool_size |
+----------------------------------+
|                        268435456 |
+----------------------------------+
1 row in set (0.00 sec)

3.4 多個Buffer Pool實例

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 = 2

這樣就表明我們要創建2個 Buffer Pool 實例。我們看下如何查看緩衝池的個數，使用命令：

mysql> select @@global.innodb_buffer_pool_instances;
+---------------------------------------+
| @@global.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的

值小於1G的時候設置多個實例是無效的，InnoDB會預設把innodb_buffer_pool_instances的值修改為1。而我們鼓勵在Buffer Pool大於

或等於1G的時候設置多個Buffer Pool實例。

3.5 引申問題

Buffer Pool是MySQL記憶體結構中十分核心的一個組成，你可以先把它想象成一個黑盒子。

當我們查詢數據的時候，會先去Buffer Pool中查詢。如果Buffer Pool中不存在，存儲引擎會先將數據從磁碟載入到Buffer Pool中，然後

將數據返回給客戶端;同理，當我們更新某個數據的時候，如果這個數據不存在於BufferPool，同樣會先數據載入進來，然後修改記憶體

的數據。被修改過的數據會在之後統一刷入磁碟。

這個過程看似沒啥問題，實則是有問題的。假設我們修改Buffer Pool中的數據成功，但是還沒來得及將數據刷入磁碟MysQL就掛了怎麼

辦?按照上圖的邏輯，此時更新之後的數據只存在於Buffer Pool中，如果此時MysQL宕機了，這部分數據將會永久地丟失;

還有更新到一半突然發生錯誤了，想要回滾到更新之前的版本，該怎麼辦？連數據持久化的保證、事務回滾都做不到還談什麼崩潰恢復？

Redo Log & Undo Log

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 鴻蒙HarmonyOS實戰-ArkUI組件（Stack）
    一、Stack 1.概述 HarmonyOS中的層疊佈局Stack是一種可以將多個組件按照一定順序疊放的佈局。Stack佈局中的組件可以是任意類型的組件，且每個組件都可以設置在哪個位置疊放。在疊放時，後添加的組件會自動覆蓋前面添加的組件。 Stack佈局佈局中的每個子組件都可以設置偏移量、旋 ...
  • 如何理解TCP/IP協議?
    一、是什麼 TCP/IP，傳輸控制協議/網際協議，是指能夠在多個不同網路間實現信息傳輸的協議簇 TCP（傳輸控制協議） 一種面向連接的、可靠的、基於位元組流的傳輸層通信協議 IP（網際協議） 用於封包交換數據網路的協議 TCP/IP協議不僅僅指的是TCP和IP兩個協議，而是指一個由FTP、SMTP、T ...
  • 任何樣式，javascript都可以操作，讓你所向披靡
    前言 習慣了在 css 文件裡面編寫樣式，其實JavaScript 的 CSS對象模型也提供了強大的樣式操作能力， 那就隨文章一起看看，有多少能力是你不知道的吧。 樣式來源 客從八方來， 樣式呢， 樣式五方來。 chrome舊版本用戶自定義樣式目錄： %LocalAppData%/Google/Ch ...
  • Vue 腳手架
    VUE 腳手架 腳手架文件結構 ├── node_modules ├── public │ ├── favicon.ico: 頁簽圖標 │ └── index.html: 主頁面 ├── src │ ├── assets: 存放靜態資源 │ │ └── logo.png │ │── componen ...
  • 15 分鐘帶你感受 CSS :has() 選擇器的強大
    最近看到了許多關於 :has() 選擇器的知識點，在此總結下來。 MDN 對 :has() 選擇器 的解釋是這樣的： CSS 函數式偽類 :has() 表示一個元素，如果作為參數傳遞的任何相對選擇器在錨定到該元素時，至少匹配一個元素。這個偽類通過把可容錯相對選擇器列表作為參數，提供了一種針對引用元素 ...
  • AI+軟體工程：10倍提效！用ChatGPT編寫系統功能文檔
    系統功能文檔是一種描述軟體系統功能和操作方式的文檔。它讓開發團隊、測試人員、項目管理者、客戶和最終用戶對系統行為有清晰、全面的瞭解。 通過ChatGPT，我們能讓編寫系統功能文檔的效率提升10倍以上。 用ChatGPT生成系統功能文檔 我們以線上商城系統為例，介紹如何使用ChatGPT幫我們完成系統 ...
  • Objective-C之Class底層結構探索
    isa 走點陣圖 在講 OC->Class 底層類結構之前，先看下下麵這張圖： 通過isa走點陣圖 得出的結論是： 1，類，父類，元類都包含了 isa, superclass 2，對象isa指向類對象，類對象的isa指向了元類，元類的 isa 指向了根元類，根元類 isa 指向自己 3，類的 super ...
  • Kafka之Producer網路傳輸
    零售商家為什麼要建設線上商城？ 傳統的實體門店服務範圍有限，只能吸引周邊500米以內的消費者。因此，如何拓展服務範圍，吸引更多的消費者到店，成為了店家迫切需要解決的問題。 缺乏忠實顧客，客戶基礎不穩，往往是一次性購物，門店無法形成有效的顧客迴流。在當前的市場環境下，構建並維護粉絲群體，成為了商家的核 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類