推薦：SQL語句執行順序相關問題。

MySQL Server架構

分層概述

MySQL Server架構可抽象為3層。

1. 連接層：驗證用戶名密碼，認證成功後，獲取當前賬號的許可權並緩存，並分配TCP連接池和線程池資源。
2. 處理層：實現核心的處理功能。
3. 存儲層：將處理後的數據高性能安全的寫入磁碟，或從磁碟中正確的讀取。

模塊構成與執行順序

1. 連接層 ：提供多個線程用於客戶端和伺服器交互，連接層包含連接池與線程池。
   • 連接池：MySQL可以有多個客戶端進行連接，為瞭解決TCP連接頻繁創建銷毀引起的性能損耗，所以建立了TCP連接池，採用長連接模式復用TCP連接。
   • 線程池：MySQL採用多線程的方式運行，MySQL Server也會分配一個線程來處理後面的流程，像TCP連接池一樣，為了避免開銷，也會創建一個線程池。
2. SQL介面：接收SQL指令，返回查詢結果。
3. 緩存緩衝區：使用鍵值對的方式緩存查詢的結果（由於命中率太低在8的版本中被廢棄）。
4. 解析器：對SQL語法進行分析，讓程式讀懂SQL。將SQL語句分解，驗證許可權，創建為語法樹，如果SQL語法錯誤，也是在這一步給的提示。流程如下：詞法分析->語法分析->分析機->抽象語法樹。
5. 優化器：對SQL的執行進行優化，進行查詢時，根據索引和SQL的情況，選擇最合適的查詢策略，這個模塊是最複雜的模塊。
6. 可插拔存儲引擎： 存儲引擎（InnoDB，MyISAM等）用於規範數據如何被高效安全的讀寫。可插拔主要體現在針對庫或者表可以進行引擎切換，結合日誌模塊 （老生常談的Bin、Relay、Redo、Undo、Error、General、Slow這些）生成相關日誌。
7. 文件系統：這是操作系統層的東西，數據不是無腦存儲到磁碟上的，需要文件系統的約束，它提供了對存儲設備的訪問、分配、保護和檢索文件的方法，文件系統諸如NTFS，EXFAT，FAT32，NFS、NAS，EXT2、EXT3。

data文件相關

InnoDB引擎.frm、.idb、.opt文件是什麼？

MySQL登錄成功後使用SHOW VARIABLES LIKE 'datadir';，或者Linux系統下查看vim /etc/my.cnf，找datadir項，可查看數據存儲的目錄。認準一個使用InnoDB引擎的非空資料庫，在datadir/資料庫名的目錄下會發現有.frm、.idb文件、.opt類型的文件。

1. .frm 存儲表結構的數據。
2. .idb用於存儲數據（5.7及以上預設使用，8的版本只有.idb，把idb和.frm進行了合併）。
3. .opt，通常叫做db.opt，純文本，用於存儲字元集編碼排序規則那套東西：例如default-character-set=utf8mb4;default-collation=utf8mb4_unicode_ci;（8的版本已去除）

InnoDB引擎.idb與ibdata1文件版本差異

註意mysql5.5.7到5.6.6的版本中的數據，是放在data/ibdata1文件中的。
.idb叫做獨立表空間，ibdata1叫做系統表空間。
使用show variables like 'innodb_file_per_table';可查看相關配置，如果是OFF，則表示使用ibdata1文件。ON表示使用獨立表空間。

MyISAM引擎.frm、.MYD、.MYI、.opt文件是什麼的？

MySQL登錄成功後使用SHOW VARIABLES LIKE 'datadir';，或者Linux系統下查看vim /etc/my.cnf，找datadir項，可查看數據存儲的目錄。認準一個使用InnoDB引擎的非空資料庫，在datadir/資料庫名的目錄下會發現有.frm、.MYD、.MYI、.opt文件類型的文件。

1. .frm 存儲表結構的數據（在8的版本變成了.sdi）。
2. .MYD，用於存數據。
3. .MYI，用於存儲索引。
4. .MYD、.MYI合併到一起，相當於InnoDB引擎的idb文件。
5. .opt，通常叫做db.opt，純文本，用於存儲字元集編碼排序規則那套東西：例如default-character-set=utf8mb4;default-collation=utf8mb4_unicode_ci;（8的版本已去除）。

data下的各種日誌，會在另一篇文章中講。

既然有了information_schema 庫來存儲元數據，為什麼還要.frm和.opt?

information_schema庫，用於存儲資料庫的結構、表、視圖、列、約束、索引等信息的元數據，同時.frm和.opt也存儲了一份元數據，這也是問題的由來。

側重定位不同，information_schema 資料庫是一個用於快速檢索元數據的庫，方便開發者進行元數據分析和操作，而.frm是專門服務於表結構的，MySQL本身玩的就是數據，適當的冗餘不見得是壞事。

information_schema的部分數據基於.frm、.opt，還是獨立維護？

部分基於.frm、.opt。
試試就知道，開了一個虛擬機找一個測試庫，.frm非文本文件沒法改，修改某個庫的.opt文件，將default-collation=utf8mb4_unicode_ci;改為default-collation=utf8mb4_general_ci;重啟MySQL服務，執行SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'db_name';發現編碼同步做了更改。

擴展

MySQL8對緩存緩衝區的移除

緩存緩衝區和Redis在項目中作用與用法相似，用於緩存查詢語句查詢出來的結果，key為SQL語句，val為數據，使用空間換時間，裡面涉及表緩存，記錄緩存，許可權緩存等。
此模塊在8的版本中移除，因為命中率太低。如果查詢請求包含某些系統函數（now()），或者一些系統庫（如mysql、information_schema、performance_schema）那麼請求就不會被緩存。是緩存就會有一致性的問題，mysql會監聽每一張表的寫操作（DDL，DML），如果發生了變更，將會刪除緩存。其次是對於複雜的業務，不會只有讀操作，這也是分表冷熱數據分離的原因之一，所以被移除掉了。

客戶端連接器

連接器屬於客戶端（MySQL Client、Navicat、PHP的PDO，Java的JDBC等）的組件，所以放到了這裡。用於和MySQL Server通信。一般是有TCP和Socket兩種通信方式（與PHP與Nginx通信方式類似）。

1. TCP就是常見的IP埠號的方式。
2. Socket就是UNIX套接字,一種本地通信方式。在linux中創建一個套接字文件（.sock文件），客戶端通過該文件與伺服器通信。與TCP/IP相比，使用UNIX域套接字可以更快地進行本地通信，因為不需要經過網路協議棧的處理。但是因為無法遠程的局限性，所以用得少，對PHP開發者來說，PDO和主流框架，都支持此連接方式。

查詢緩存命中率

執行show status like 'Qcache%';會得到一個kv格式的表格
Qcache_free_blocks: 查詢緩存中空閑的記憶體塊數量。
Qcache_free_memory: 查詢緩存中可用的記憶體大小。
Qcache_hits: 查詢緩存命中的次數，即從查詢緩存中成功獲取到結果的查詢次數。
Qcache_inserts: 查詢緩存中插入的查詢次數。
Qcache_lowmem_prunes: 由於記憶體不足而從查詢緩存中移除的查詢次數。
Qcache_not_cached: 由於不符合查詢緩存規則而沒有被緩存的查詢次數。
Qcache_queries_in_cache: 當前查詢緩存中緩存的查詢數量。
Qcache_total_blocks: 查詢緩存中的記憶體塊總數量。

資料庫緩衝池

InnoDB是依靠頁來管理存儲空間的，CRUD的操作是對頁面的讀寫。因為磁碟IO操作慢，記憶體操作快，所以MySQL Server會使用記憶體來作為數據緩衝池，真正訪問頁之前，需要把磁碟上的頁緩存到記憶體中的Buffer Pool後才可以訪問，用於提升MySQL的性能。
流程：當資料庫系統需要從磁碟讀取數據時，它首先檢查緩衝池中是否已經緩存了相應的數據頁。如果數據頁已經在緩衝池中，則不需要從磁碟讀取，而是直接從緩衝池中獲取數據，這樣可以大大提高數據檢索速度。
緩衝池的數據有數據頁、索引頁、鎖數據、和數據字典。

配置緩衝池

MyISAM：緩衝池和innodb的不一樣，是鍵緩存，參數為key_buffer_size;
查看：SHOW VARIABLES LIKE 'key_buffer_size'; SHOW STATUS LIKE 'Key_blocks_%';單位為位元組。
配置：在my.cnf中配置key_buffer_size = 256M後重啟。
InnoDB：
查看：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';單位為位元組。
配置：在my.cnf中配置innodb_buffer_pool_size = 256M後重啟。

不想要重啟，可以使用set globak k=v，（5.7及以上可用）。但是無法持久化保存。

多個緩衝池

在多線程情況下，訪問buffer pool中的數據需要加鎖處理，對於併發量打的情況下，加鎖會影響處理速度，所以就考慮到拆分buffer pool的情況，用於提高併發處理的能力。每個buffer pool被稱為一個實例，他們是獨立的，獨立的申請記憶體，獨立的管理數據。
查看：SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
配置：在my.cnf中配置innodb_buffer_pool_instances= 2後重啟。
每個buffer_size為innodb_buffer_pool_size / innodb_buffer_pool_instances；
當innodb_buffer_pool_size<=1GB時，設置多個實例是無效的。

執行SQL時更新了緩衝池的數據，這些數據會實時同步到磁碟嗎？

不會。
對數據表中的記錄進行修改時，首先會修改緩衝池中的數據，然後會以一定的頻率刷新到磁碟上，也不是每次更新操作都會把數據刷新到磁碟。緩衝池會採用一個叫做checkpoint的方式將更改的數據（臟頁數據）寫入到磁碟，此操作用於提升資料庫的性能。

InnoDB與MyISAM區別