推薦：SQL語句執(zhí)行順序相關(guān)問題。

MySQL Server架構(gòu)

分層概述

MySQL Server架構(gòu)可抽象為3層。

1. 連接層：驗證用戶名密碼，認證成功后，獲取當前賬號的權(quán)限并緩存，并分配TCP連接池和線程池資源。
2. 處理層：實現(xiàn)核心的處理功能。
3. 存儲層：將處理后的數(shù)據(jù)高性能安全的寫入磁盤，或從磁盤中正確的讀取。

模塊構(gòu)成與執(zhí)行順序

1. 連接層 ：提供多個線程用于客戶端和服務(wù)器交互，連接層包含連接池與線程池。
   • 連接池：MySQL可以有多個客戶端進行連接，為了解決TCP連接頻繁創(chuàng)建銷毀引起的性能損耗，所以建立了TCP連接池，采用長連接模式復(fù)用TCP連接。
   • 線程池：MySQL采用多線程的方式運行，MySQL Server也會分配一個線程來處理后面的流程，像TCP連接池一樣，為了避免開銷，也會創(chuàng)建一個線程池。
2. SQL接口：接收SQL指令，返回查詢結(jié)果。
3. 緩存緩沖區(qū)：使用鍵值對的方式緩存查詢的結(jié)果（由于命中率太低在8的版本中被廢棄）。
4. 解析器：對SQL語法進行分析，讓程序讀懂SQL。將SQL語句分解，驗證權(quán)限，創(chuàng)建為語法樹，如果SQL語法錯誤，也是在這一步給的提示。流程如下：詞法分析->語法分析->分析機->抽象語法樹。
5. 優(yōu)化器：對SQL的執(zhí)行進行優(yōu)化，進行查詢時，根據(jù)索引和SQL的情況，選擇最合適的查詢策略，這個模塊是最復(fù)雜的模塊。
6. 可插拔存儲引擎： 存儲引擎（InnoDB，MyISAM等）用于規(guī)范數(shù)據(jù)如何被高效安全的讀寫?？刹灏沃饕w現(xiàn)在針對庫或者表可以進行引擎切換，結(jié)合日志模塊 （老生常談的Bin、Relay、Redo、Undo、Error、General、Slow這些）生成相關(guān)日志。
7. 文件系統(tǒng)：這是操作系統(tǒng)層的東西，數(shù)據(jù)不是無腦存儲到磁盤上的，需要文件系統(tǒng)的約束，它提供了對存儲設(shè)備的訪問、分配、保護和檢索文件的方法，文件系統(tǒng)諸如NTFS，EXFAT，F(xiàn)AT32，NFS、NAS，EXT2、EXT3。

data文件相關(guān)

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

MySQL登錄成功后使用SHOW VARIABLES LIKE 'datadir';，或者Linux系統(tǒng)下查看vim /etc/my.cnf，找datadir項，可查看數(shù)據(jù)存儲的目錄。認準一個使用InnoDB引擎的非空數(shù)據(jù)庫，在datadir/數(shù)據(jù)庫名的目錄下會發(fā)現(xiàn)有.frm、.idb文件、.opt類型的文件。

1. .frm 存儲表結(jié)構(gòu)的數(shù)據(jù)。
2. .idb用于存儲數(shù)據(jù)（5.7及以上默認使用，8的版本只有.idb，把idb和.frm進行了合并）。
3. .opt，通常叫做db.opt，純文本，用于存儲字符集編碼排序規(guī)則那套東西：例如default-character-set=utf8mb4;default-collation=utf8mb4_unicode_ci;（8的版本已去除）

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

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

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

MySQL登錄成功后使用SHOW VARIABLES LIKE 'datadir';，或者Linux系統(tǒng)下查看vim /etc/my.cnf，找datadir項，可查看數(shù)據(jù)存儲的目錄。認準一個使用InnoDB引擎的非空數(shù)據(jù)庫，在datadir/數(shù)據(jù)庫名的目錄下會發(fā)現(xiàn)有.frm、.MYD、.MYI、.opt文件類型的文件。

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

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

既然有了information_schema 庫來存儲元數(shù)據(jù)，為什么還要.frm和.opt?

information_schema庫，用于存儲數(shù)據(jù)庫的結(jié)構(gòu)、表、視圖、列、約束、索引等信息的元數(shù)據(jù)，同時.frm和.opt也存儲了一份元數(shù)據(jù)，這也是問題的由來。

側(cè)重定位不同，information_schema 數(shù)據(jù)庫是一個用于快速檢索元數(shù)據(jù)的庫，方便開發(fā)者進行元數(shù)據(jù)分析和操作，而.frm是專門服務(wù)于表結(jié)構(gòu)的，MySQL本身玩的就是數(shù)據(jù)，適當?shù)娜哂嗖灰姷檬菈氖隆?/p>

information_schema的部分數(shù)據(jù)基于.frm、.opt，還是獨立維護？

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

擴展

MySQL8對緩存緩沖區(qū)的移除

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

客戶端連接器

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

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

查詢緩存命中率

執(zhí)行show status like 'Qcache%';會得到一個kv格式的表格
Qcache_free_blocks: 查詢緩存中空閑的內(nèi)存塊數(shù)量。
Qcache_free_memory: 查詢緩存中可用的內(nèi)存大小。
Qcache_hits: 查詢緩存命中的次數(shù)，即從查詢緩存中成功獲取到結(jié)果的查詢次數(shù)。
Qcache_inserts: 查詢緩存中插入的查詢次數(shù)。
Qcache_lowmem_prunes: 由于內(nèi)存不足而從查詢緩存中移除的查詢次數(shù)。
Qcache_not_cached: 由于不符合查詢緩存規(guī)則而沒有被緩存的查詢次數(shù)。
Qcache_queries_in_cache: 當前查詢緩存中緩存的查詢數(shù)量。
Qcache_total_blocks: 查詢緩存中的內(nèi)存塊總數(shù)量。

數(shù)據(jù)庫緩沖池

InnoDB是依靠頁來管理存儲空間的，CRUD的操作是對頁面的讀寫。因為磁盤IO操作慢，內(nèi)存操作快，所以MySQL Server會使用內(nèi)存來作為數(shù)據(jù)緩沖池，真正訪問頁之前，需要把磁盤上的頁緩存到內(nèi)存中的Buffer Pool后才可以訪問，用于提升MySQL的性能。
流程：當數(shù)據(jù)庫系統(tǒng)需要從磁盤讀取數(shù)據(jù)時，它首先檢查緩沖池中是否已經(jīng)緩存了相應(yīng)的數(shù)據(jù)頁。如果數(shù)據(jù)頁已經(jīng)在緩沖池中，則不需要從磁盤讀取，而是直接從緩沖池中獲取數(shù)據(jù)，這樣可以大大提高數(shù)據(jù)檢索速度。
緩沖池的數(shù)據(jù)有數(shù)據(jù)頁、索引頁、鎖數(shù)據(jù)、和數(shù)據(jù)字典。

配置緩沖池

MyISAM：緩沖池和innodb的不一樣，是鍵緩存，參數(shù)為key_buffer_size;
查看：SHOW VARIABLES LIKE 'key_buffer_size'; SHOW STATUS LIKE 'Key_blocks_%';單位為字節(jié)。
配置：在my.cnf中配置key_buffer_size = 256M后重啟。
InnoDB：
查看：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';單位為字節(jié)。
配置：在my.cnf中配置innodb_buffer_pool_size = 256M后重啟。

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

多個緩沖池

在多線程情況下，訪問buffer pool中的數(shù)據(jù)需要加鎖處理，對于并發(fā)量打的情況下，加鎖會影響處理速度，所以就考慮到拆分buffer pool的情況，用于提高并發(fā)處理的能力。每個buffer pool被稱為一個實例，他們是獨立的，獨立的申請內(nèi)存，獨立的管理數(shù)據(jù)。
查看：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時，設(shè)置多個實例是無效的。

執(zhí)行SQL時更新了緩沖池的數(shù)據(jù)，這些數(shù)據(jù)會實時同步到磁盤嗎？

不會。
對數(shù)據(jù)表中的記錄進行修改時，首先會修改緩沖池中的數(shù)據(jù)，然后會以一定的頻率刷新到磁盤上，也不是每次更新操作都會把數(shù)據(jù)刷新到磁盤。緩沖池會采用一個叫做checkpoint的方式將更改的數(shù)據(jù)（臟頁數(shù)據(jù)）寫入到磁盤，此操作用于提升數(shù)據(jù)庫的性能。文章來源地址http://www.zghlxwxcb.cn/news/detail-837796.html

InnoDB與MyISAM區(qū)別

到了這里，關(guān)于MySQL Server架構(gòu)概述的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！