文章目錄
- 1. 邏輯架構剖析
- 1.1 伺服器處理用戶端請求
- 1.2 Connectors
- 1.3 第 1 層：連接配接層
- 1.4 第 2 層：服務層
- 1.5 第 3 層：引擎層
- 1.6 存儲層
- 1.7 小結
- 2. SQL 執行流程
- 2.1 MySQL 中的 SQL執行流程
- 2.1.1 查詢緩存
- 2.1.2 解析器
- 2.1.3 優化器
- 2.1.4 執行器
- 2.2 MySQL8中SQL執行原理
- 2.2.1. 确認profiling是否開啟
- 2.2.2.多次執行相同SQL查詢
- 2.2.3.檢視profiles
- 2.2.4.檢視profile
- 2.3 MySQL5.7中SQL執行原理
- 2.3.1.配置檔案中開啟查詢緩存
- 2.3.2.重新開機Mysql服務
- 2.3.3.開啟查詢計劃
- 2.3.4.執行語句兩次
- 2.3.5.檢視profiles
- 2.3.6.檢視profile
- 2.4 SQL文法順序
- 2.5 Oracle中的SQL執行流程(了解)
- 3. 資料庫緩沖池（buffer pool）
- 3.1 緩沖池 vs 查詢緩存
- 3.1.1 緩沖池（Buffer Pool）
- 3.1.2 查詢緩存
- 3.2 緩沖池如何讀取資料
- 3.3 檢視/設定緩沖池的大小
- 3.4 多個Buffer Pool執行個體
- 3.5 引申問題
1. 邏輯架構剖析
1.1 伺服器處理用戶端請求
首先MySQL是典型的C/S架構，即
Client/Server
架構‘ ， 伺服器端程式使用的mysqld。
不論用戶端程序和伺服器程序是采用哪種方式進行通信，最後實作的效果都是:用戶端程序向伺服器程序發送一段文本(SQL語句) ,伺服器程序處理後再向用戶端程序發送一段文本(處理結果)
那伺服器程序對用戶端程序發送的請求做了什麼處理，才能産生最後的處理結果呢?這裡以查詢請求為例展示：
下面具體展開看一下:(針對MySQL5.7)
分析
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 錯誤，用戶端程式結束執行
- 使用者名密碼認證通過，會從權限表查出賬号擁有的權限與連接配接關聯，之後的權限判斷邏輯，都将依賴于此時讀到的權限
TCP
連接配接收到請求後，必須要配置設定給一個線程專門與這個用戶端的互動。是以還會有個線程池，去走後面的流程。每一個連接配接從線程池中擷取線程，省去了建立和銷毀線程的開銷。
接着我們來思考一個問題
一個系統隻會和MySQL伺服器建立一個連接配接嗎？隻能有一個系統和MySQL服務 器建立連接配接嗎？
當然不是，多個系統都可以和MySQL伺服器建立連接配接，每個系統建立的連接配接肯定不止一個。是以，為了解決TCP無限建立與TCP頻繁建立銷毀帶來的資源耗盡、性能下降問題。MySQL伺服器裡有專門的
TCP連接配接池
限制連接配接數，采用
長連接配接模式
複用TCP連接配接,來解決上述問題。
TCP
連接配接收到請求後，必須要配置設定給一個線程專門與這個用戶端的互動。是以還會有個線程池，去走後面的流程。每一個連接配接從線程池中擷取線程，省去了建立和銷毀線程的開銷。
這些内容我們都歸納到
MySQL
的連接配接管理元件中。
是以連接配接管理的職責是負責認證、管理連接配接、擷取權限資訊。
1.4 第 2 層：服務層
第二層架構主要完成大多數的核心服務功能，如SQL接口， 并完成緩存的查詢，SQL的分析和優化及部分内置函數的執行。所有跨存儲引擎的功能也在這一層實作，如過程、函數等。
在該層，伺服器會解析查詢并建立相應的内部解析樹，并對其完成相應的優化：如确定查詢表的順序，是否利用索引等，最後生成相應的執行操作。
如果是SELECT語句，伺服器還會查詢内部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
- SQL Interface：SQL接口
- 接收使用者的 SQL 指令，并且傳回使用者需要查詢的結果。比如
SELECT ... FROM
- MySQL 支援 DML（資料操作語言）、DDL（資料定義語言）、存儲過程、視圖、觸發器、自定義函數等多種 SQL 語言接口
- Parser：解析器
- 在解析器中對 SQL 語句進行文法分析、語義分析。将 SQL 語句分解成資料結構，并将這個結構傳遞到後續步驟，以後 SQL 語句的傳遞和處理就是基于這個結構的。如果在分解構成中遇到錯誤，那麼就說明這個 SQL 語句是不合理的。
- 在 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中删除
小故事：
如果我問你9+8×16-3×2×17的值是多少，你可能會用電腦去算一下，最終結果35。如果再問你一遍9+8×16-
3×2×17的值是多少，你還用再傻呵呵的再算一遍嗎？我們剛剛已經算過了，直接說答案就好了。 1.5 第 3 層：引擎層
和其它資料庫相比，MySQL有點與衆不同，它的架構可以在多種不同場景中應用并發揮良好作用，主要展現在存儲引擎的架構上，插件式的存儲引擎架構将查詢處理和其它的系統任務以及資料的存儲提取相分離。這種架構可以根據業務的需求和實際需要選擇合适的存儲引擎。同時開源的 MySQL還允許開發人員設定自己的存儲引擎。
這種高效的子產品化架構為那些希望專門針對特定應用程式需求(例如資料倉庫、事務處理或高可用性情況)的人提供了巨大的好處，同時享受使用一組獨立于任何接口和服務的優勢存儲引擎。
插件式存儲引擎層（Storage Engines），真正的負責了MySQL中資料的存儲和提取，對實體伺服器級别維護的底層資料執行操作 ，伺服器通過 API 與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。
MySQL 8.0.25 預設支援的存儲引擎如下:
1.6 存儲層
所有的資料，資料庫、表的定義，表的每一行的内容，索引，都是存在
檔案系統
上，以
檔案
的方式存 在的，并完成與存儲引擎的互動。當然有些存儲引擎比如InnoDB，也支援不使用檔案系統直接管理裸裝置，但現代檔案系統的實作使得這樣做沒有必要了。在檔案系統之下，可以使用本地磁盤，可以使用 DAS、NAS、SAN等各種存儲系統。
1.7 小結
MySQL 架構圖本節開篇所示。下面為了熟悉 SQL 執行流程友善，我們可以簡化如下:
簡化為三層結構：
- 連接配接層：用戶端和伺服器端建立連接配接，用戶端發送 SQL 至伺服器端;
- SQL 層（服務層）：對 SQL 語句進行查詢處理；與資料庫檔案的存儲方式無關;
- 存儲引擎層：與資料庫檔案打交道，負責資料的存儲和讀取。
2. SQL 執行流程
2.1 MySQL 中的 SQL執行流程
MySQL的查詢流程:
2.1.1 查詢緩存
Server 如果在查詢緩存中發現了這條 SQL 語句，就會直接将結果傳回給用戶端;如果沒 有，就進入到解析器階段。需要說明的是，因為查詢緩存往往效率不高，是以在 MySQL 8.0 之後就抛棄 了這個功能。
大多數情況查詢緩存就是個雞肋，為什麼呢?
SELECT employee_id,last_name FROM employees WHERE employee_id = 101; 查詢緩存是提前把查詢結果緩存起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在 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 ;| 檢視目前mysq|執行個體是否開啟緩存機制
# MySQL5.7 中：
mysql> show global variables like "%query_cache_type%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)
# MySQL8.0 中：
mysql> show global variables like "%query_cache_type%";
Empty set (0.00 sec) 監控查詢緩存的命中率
show status like '%Qcache%'; -
Qcache_free_blocks
記憶體碎片
-
Qcache_free_memory
-
Qcache_hits
-
Qcache_inserts
-
Qcache_lowmem_prunes
-
Qcache_not_cached
-
Qcache_queries_in_cache
緩存的查詢數量
-
Qcache_total_blocks
2.1.2 解析器
在解析器中對 SQL 語句進行文法分析、語義分析。
如果沒有命中查詢緩存，就要開始真正執行語句了。首先，MySQL 需要知道你要做什麼，是以需要對SQL語句做解析。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; 如果你的語句不對，就會收到“”的錯誤提醒，比如這個語句from寫成了rom。
mysql> select * fro 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詞法分析的過程步驟：
至此我們解析器的工作任務也基本圓滿了。接下來進入到優化器。
2.1.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語句進行
等價變換
，對查詢進行重寫，而查詢重寫的數學基礎就是關系代數。對條件表達式進行等價謂詞重寫、條件簡化，對視圖進行重寫，對子查詢進行優化，對連接配接語義進行了外連接配接消除、嵌套連接配接消除等。
實體查詢優化是基于關系代數進行的查詢重寫,而關系代數的每一步都對應着實體計算, 這些實體計算往往存在多種算法，是以需要計算各種實體路徑的代價，從中選擇代價最小的作為執行計劃。在這個階段裡，對于單表和多表連接配接的操作，需要高效地
使用索引
，提升查詢效率。
2.1.4 執行器
截止到現在，還沒有真正去讀寫真實的表，僅僅隻是産出了一個執行計劃。于是就進入了
執行器階段
。
在執行之前需要判斷該使用者是否具備權限 。如果沒有，就會傳回權限錯誤。如果具備權限，就執行 SQL 查詢并傳回結果。在 MySQL 8.0 以下的版本，如果設定了查詢緩存，這時會将查詢結果進行緩存。
select * from test where id=1; 如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，調用存儲引擎API對表進行的讀寫。存儲引擎API隻是抽象接口，下面還有個存儲引擎層，具體實作還是要看表選擇的存儲引擎。
比如：表 test 中，ID 字段沒有索引，那麼執行器的執行流程是這樣的：
- 調用 InnoDB 引擎接口取這個表的第一行，判斷 ID 值是不是1，如果不是則跳過，如果是則将這行存在結果集中; 調用引擎接口取“下一行”，重複相同的判斷邏輯，直到取到這個表的最後一行。
- 執行器将上述周遊過程中所有滿足條件的行組成的記錄集作為結果集傳回給用戶端。
至此，這個語句就執行完成了。對于有索引的表，執行的邏輯也差不多。
SQL 語句在 MySQL 中的流程是：
SQL 語句 → 查詢緩存 → 解析器 → 優化器 → 執行器
。
2.2 MySQL8中SQL執行原理
前面的結構圖很複雜，我們需要抓取最核心的部分: SQL的執行原理。不同的DBMS的SQL的執行原理是相通的，隻是在不同的軟體中，各有各的實作路徑。
既然一條SQL語句會經曆不同的子產品,那我們就來看下，在不同的子產品中，SQL 執行所使用的資源(時間)是怎樣的。如何在MySQL中對一條SQL語句的執行時間進行分析。
2.2.1. 确認profiling是否開啟
了解查詢語句底層執行的過程：
select @@profiling;
或者
show variables like '%profiling%'
檢視是否開啟計劃。開啟它可以讓MySQL收集在SQL執行時所使用的資源情況，指令如下：
mysql> select @@profiling;
mysq1> show variables like 'profiling'; profiling = 0代表關閉，我們需要把profiling打開，即設定為1；
mysql> set profiling = 1; Profiling功能由MySQL會話變量：profiling控制。預設是OFF（關閉狀态）。
2.2.2.多次執行相同SQL查詢
mysql> select * from employees;
mysql> select * from employees; 2.2.3.檢視profiles
mysql> show profiles #查詢所有sql語句的分析概覽 2.2.4.檢視profile
mysql> show profie; mysql> show profie for query Query_ID;# 檢視某一次sql執行的分析過程 2.3 MySQL5.7中SQL執行原理
這裡我們需要
顯示開啟查詢緩存模式
。在MySQL5.7中如下設定：
2.3.1.配置檔案中開啟查詢緩存
query_cache_type=1 2.3.2.重新開機Mysql服務
systemctl restart mysqld 2.3.3.開啟查詢計劃
mysql> set profiling = 1; 2.3.4.執行語句兩次
select * from departments;
select * from departments; 2.3.5.檢視profiles
2.3.6.檢視profile
結論不言而喻.執行編号10時,比執行編号9時少了很多資訊,從截圖中可以看出查詢語句直接從緩存中擷取資料 .
- 注意1：SQL必須時一緻的,否則,不能命中緩存.
例如：
#雖然查詢結果一緻， 但并沒有命中緩存。
select * from mydb . mytbl where id=2
select * from mydb . mytbl where id>1 and id<3 - **注意2：**同樣的開啟緩存的配置資訊如果在MySQL8中添加。重新開機服務時會報錯:
[ root@atguigu01 ~]# vim /etc/my.cnf
[root@atguigu01 ~]# systemctl restart mysqld;
Job for mysqld.service failed because the control process exited with error code. See
"systemctl status mysqld. service" and "journalctl -xe" for 分别在MySQL5.7和MySQL8中執行如下指令：
mysql> show variables like '%query_ cache%'; MySQL5.7中顯示：
MySQL8.0中顯示：
2.4 SQL文法順序
随着 MySQL 版本的更新換代，其優化器也在不斷的更新，優化器會分析不同執行順序産生的性能消耗不同而動态調整執行順序。
需求：查詢每個部門年齡高于 20 歲的人數且高于 20 歲人數不能少于 2 人，顯示人數最多的第一名部門資訊。
下面是經常出現的查詢順序:
]
2.5 Oracle中的SQL執行流程(了解)
Oracle中采用了共享池來判斷SQL語句是否存在緩存和執行計劃，通過這一步驟我們可以知道應該采用硬解析還是軟解析。
我們先來看下SQL在Oracle中的執行過程:
從上面這張圖中可以看出, SQL語句在Oracle中經曆了以下的幾個步驟。
- 文法檢查：檢查SQL拼寫是否正确，如果不正确，Oracle 會報文法錯誤。
- 語義檢查：檢查SQL中的通路對象是否存在。比如我們在寫SELECT語句的時候，列名寫錯了，系統就會提示錯誤。文法檢查和語義檢查的作用是保證SQL語句沒有錯誤。
- 權限檢查：看使用者是否具備通路該資料的權限。
Oracle通過檢查共享池是否存在SQL語句的執行計劃，來判斷進行軟解析,還是硬解析。那軟解析和硬解析又該怎麼了解呢?
在共享池中，Oracle 首先對SQL語句進行
Hash運算
，然後根據Hash值在庫緩存(Library Cache)中查找，如果
存在SQL語句的執行計劃
，就直接拿來執行，直接進入“執行器”的環節,這就是
軟解析
。
如果沒有找到SQL語句和執行計劃，Oracle 就需要建立解析樹進行解析，生成執行計劃，進入“優化器”這個步驟,這就是
硬解析
。
- 優化器:優化器中就是要進行硬解析，也就是決定怎麼做，比如建立解析樹，生成執行計劃。
- 執行器:當有了解析樹和執行計劃之後,就知道了SQL該怎麼被執行，這樣就可以在執行器中執行語句了。
共享池是Oracle中的術語，包括了庫緩存,資料字典緩沖區等。我們_上面已經講到了庫緩存區，它主要緩存SQL語句和執行計劃。而
資料字典緩沖區
存儲的是Oracle中的對象定義,比如表、視圖、索引等對象。當對SQL語句進行解析的時候，如果需要相關的資料，會從資料字典緩沖區中提取。
庫緩存
這一個步驟，決定了SQL語句是否需要進行硬解析。為了提升SQL的執行效率,我們應該盡量避免硬解析，因為在SQL的執行過程中，建立解析樹，生成執行計劃是很消耗資源的。
你可能會問，如何避免硬解析，盡量使用軟解析呢?在Oracle中，
綁定變量
是它的一大特色。綁定變量就是在SQL語句中使用變量，通過不同的變量取值來改變SQL的執行結果。這樣做的好處是能
提升軟解析的可能性
，不足之處在于可能會導緻生成的執行計劃不夠優化，是以是否需要綁定變量還需要視情況而定。
舉個例子，我們可以使用下面的查詢語句：
SQL> select * from player where player_id = 10001; 你也可以使用綁定變量，如:
SOL> select * from plaver where plaver id = :plaver_id: 這兩個查詢語句的效率在Oracle中是完全不同的。如果你在查詢player. _id= 10001之後,還會查詢10002、10003之類的資料,那麼每- -次查詢都會建立一 個新的查詢解析。 而第二種方式使用了綁定變量，那麼在第一-次查詢之後，在共享池中就會存在這類查詢的執行計劃，也就是軟解析。
是以，我們可以通過使用綁定變量來減少硬解析，減少Oracle的解析工作量。但是這種方式也有缺點，使用動态SQL的方式，因為參數不同，會導緻SQL的執行效率不同，同時SQL優化也會比較困難。
Oracle的架構圖：
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存儲弓|擎在處理用戶端的請求時，當需要通路某個頁的資料時，就會把
完整的頁的資料全部加載到記憶體
中，也就是說即使我們隻需要通路一個頁的一條記錄，那也需要先把整個頁的資料加載到記憶體中。将整個頁加載到記憶體中後就可以進行讀寫通路了，在進行完讀寫通路之後并不着急把該頁對應的記憶體空間釋放掉,而是将其
緩存
起來,這樣将來有請求再次通路該頁面時，就可以
省去磁盤I0
的開銷了。
緩存原則：
“
位置 * 頻次
”這個原則，可以幫我們對 I/O 通路效率進行優化。 首先，位置決定效率，提供緩沖池就是為了在記憶體中可以直接通路資料。
其次，頻次決定優先級順序。因為緩沖池的大小是有限的，比如磁盤有 200G，但是記憶體隻有 16G，緩沖池大小隻有 1G，就無法将所有資料都加載到緩沖池裡，這時就涉及到優先級順序，會
優先對使用頻次高的熱資料進行加載
。
緩沖池的預讀特性:
一個緩存了解了緩沖池的作用之後，我們還需要了解緩沖池的另一個特性:
預讀
。
緩沖池的作用就是提升I/0效率，而我們進行讀取資料的時候存在一個“局部性原理”， 也就是說我使用了一些資料，
大機率還會使用它周圍的一些資料
，是以采用“預讀”的機制提前加載，可以減少未來可能的磁盤1/O操作。
3.1.2 查詢緩存
那麼什麼是查詢緩存呢?
查詢緩存是提前把
查詢結果緩存
起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在 MySQL 中的查詢緩存，不是緩存查詢計劃，而是查詢對應的結果。因為命中條件苛刻，而且隻要資料表發生變化，查詢緩存就會失效，是以命中率低。
3.2 緩沖池如何讀取資料
緩沖池管理器會盡量将經常使用的資料儲存起來，在資料庫進行頁面讀操作的時候，首先會判斷該頁面是否在緩沖池中，如果存在就直接讀取，如果不存在，就會通過記憶體或磁盤将頁面存放到緩沖池中再進行讀取。
緩存在資料庫中的結構和作用如下圖所示：
如果我們執行SQL語句的時候更新了緩存池中的資料，那麼這些資料會馬上同步到磁盤上嗎?
實際上，當我們對資料庫中的記錄進行修改的時候，首先會修改緩沖池中頁裡面的記錄資訊,然後資料庫會
以一定的頻率重新整理
到磁盤上。注意并不是每次發生更新操作，都會立刻進行磁盤回寫。緩沖池會采用一種叫做
checkpoint的機制
将資料回寫到磁盤上,這樣做的好處就是提升了資料庫的整體性能。
比如，當
緩沖池不夠用
時，需要釋放掉一些不常用的頁，此時就可以強行采用checkpoint的方式，将不常用的髒頁回寫到磁盤上,然後再從緩沖池中将這些頁釋放掉。這裡髒頁(dirty page)指的是緩沖池中被修改過的頁，與磁盤上的資料頁不一緻。
3.3 檢視/設定緩沖池的大小
如果你使用的是MySQL MyISAM存儲引擎,它隻緩存索引， 不緩存資料，對應的鍵緩存參數
key_buffer_size
，你可以用它進行檢視。
如果你使用的是InnoDB存儲引擎，可以通過檢視
innodb_buffer_pool_size
變量來檢視緩沖池的大 小。指令如下:
show variables like 'innodb_buffer_pool_size'; 你能看到此時 InnoDB 的緩沖池大小隻有 134217728/1024/1024=128MB。我們可以修改緩沖池大小，比如 改為256MB，方法如下:
set global innodb_buffer_pool_size = 268435456; 或者:
[server]
innodb_buffer_pool_size = 268435456 然後再來看下修改後的緩沖池大小，此時已成功修改成了 256 MB:
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
執行個體。
我們看下如何檢視緩沖池的個數，使用指令:
show variables like 'innodb_buffer_pool_instances'; 那每個
Buffer Pool
執行個體實際占多少記憶體空間呢?其實使用這個公式算出來的：
innodb_buffer_pool_size/innodb_buffer_pool_instances
也就是總共的大小除以執行個體的個數，結果就是每個
Buffer Pool
執行個體占用的大小。
不過也不是說Buffer Pool執行個體建立的越多越好，分别
管理各個Buffer Pool也是需要性能開銷
的，InnoDB規定:當innodb_buffer_pool_size的值小于1G的時候設定多個執行個體是無效的，InnoDB會預設把innodb_ buffe _pool_instances 的值修改為1。而我們鼓勵在Buffer Ppol大于或等于1G的時候設定多個Buffer Pool執行個體。
3.5 引申問題
Buffer Pool 是 MySQL 記憶體結構中十分核心的一個組成，你可以先把它想象成一個黑盒子。
黑盒下的更新資料流程
當我們查詢資料的時候，會先去Buffer Pool中查詢。如果Buffer Pool中不存在,存儲弓|擎會先将資料從磁盤加載到Buffer Pool中，然後将資料傳回給用戶端;同理，當我們更新某個資料的時候，如果這個資料不存在于Buffer Pool,同樣會先資料加載進來，然後修改修改記憶體的資料。被修改過的資料會在之後統一刷入磁盤。
這個過程看似沒啥問題，實則是有問題的。假設我們修改Buffer Pool中的資料成功，但是還沒來得及将資料刷入磁盤MySQL就挂了怎麼辦?按照上圖的邏輯，此時更新之後的資料隻存在于Buffer Pool中,如果此時MySQL當機了，這部分資料将會永久地丢失;