推薦：SQL語句執行順序相關問題。
MySQL Server架構
分層概述
MySQL Server架構可抽象為3層。
- 連線層：驗證使用者名稱密碼，認證成功後，獲取當前賬號的許可權並快取，並分配TCP連線池和執行緒池資源。
- 處理層：實現核心的處理功能。
- 儲存層：將處理後的資料高效能安全的寫入磁碟，或從磁碟中正確的讀取。
模組構成與執行順序
- 連線層 ：提供多個執行緒用於客戶端和伺服器互動，連線層包含連線池與執行緒池。
- 連線池：MySQL可以有多個客戶端進行連線，為了解決TCP連線頻繁建立銷燬引起的效能損耗，所以建立了TCP連線池，採用長連線模式複用TCP連線。
- 執行緒池：MySQL採用多執行緒的方式執行，MySQL Server也會分配一個執行緒來處理後面的流程，像TCP連線池一樣，為了避免開銷，也會建立一個執行緒池。
- SQL介面：接收SQL指令，返回查詢結果。
- 快取緩衝區：使用鍵值對的方式快取查詢的結果（由於命中率太低在8的版本中被廢棄）。
- 解析器：對SQL語法進行分析，讓程式讀懂SQL。將SQL語句分解，驗證許可權，建立為語法樹，如果SQL語法錯誤，也是在這一步給的提示。流程如下：詞法分析->語法分析->分析機->抽象語法樹。
- 最佳化器：對SQL的執行進行最佳化，進行查詢時，根據索引和SQL的情況，選擇最合適的查詢策略，這個模組是最複雜的模組。
- 可插拔儲存引擎： 儲存引擎（InnoDB，MyISAM等）用於規範資料如何被高效安全的讀寫。可插拔主要體現在針對庫或者表可以進行引擎切換，結合日誌模組 （老生常談的Bin、Relay、Redo、Undo、Error、General、Slow這些）生成相關日誌。
- 檔案系統：這是作業系統層的東西，資料不是無腦儲存到磁碟上的，需要檔案系統的約束，它提供了對儲存裝置的訪問、分配、保護和檢索檔案的方法，檔案系統諸如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型別的檔案。
- .frm 儲存表結構的資料。
- .idb用於儲存資料（5.7及以上預設使用，8的版本只有.idb，把idb和.frm進行了合併）。
- .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檔案型別的檔案。
- .frm 儲存表結構的資料（在8的版本變成了.sdi）。
- .MYD，用於存資料。
- .MYI，用於儲存索引。
- .MYD、.MYI合併到一起，相當於InnoDB引擎的idb檔案。
- .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通訊方式類似）。
- TCP就是常見的IP埠號的方式。
- 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區別
| 專案 | InnoDB | MyISAM |
|---|---|---|
| 事務 | 支援 | 不支援 |
| 外來鍵 | 支援（但不支援跨引擎） | 不支援 |
| 最小鎖粒度 | 行鎖 | 表鎖 |
| 日誌 | 支援redo、undo、bin log | 支援bin log |
| 聚簇索引 | 支援 | 不支援 |
| 二級索引葉子節點儲存 | 索引值與主鍵 | 索引值與所在行地址 |
| 適用場景 | 高併發，事務，金融 | 節省資源，輕量級簡單業務 |
| count(*)統計演算法 | 逐行遍歷，時間複雜度O(n) | 內部自動維護，時間複雜度O(1) |