源碼倉庫Gitee · GitHubMySQL的體系結構可以清楚地看到 SQL 語句在 MySQL 的各個功能模塊中的執(zhí)行過程Server層包括連接層、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等涵蓋MySQL的大多數(shù)核心服務功能以及所有的內(nèi)置函數(shù)如日期、時間、數(shù)學和加密函數(shù)等實現(xiàn)所有跨存儲引擎的功能比如存儲過程、觸發(fā)器、視圖等存儲引擎層負責數(shù)據(jù)的存儲和提取MySQL5.5.5版本開始默認使用InnoDB即你執(zhí)行create table建表時不指定引擎類型默認使用InnoDB連接器連接管理Connection Handling當一個客戶端進行連接服務端時會得到一個server處理線程每個連接的請求處理都是單線程的同時服務器會緩存這些線程所以當一個新的連接來時就不需要創(chuàng)建和銷毀線程【MySQL5.5及其以上版本提供了一個支持線程池插件的接口】權限認證Authentication當客戶端連接服務端時服務端需要對連接進行認證即對username、host、password進行認證安全Securith當客戶端成功連接上服務端時服務端會對每個連接查看它的系統(tǒng)權限清單【mysql show privileges \G】之后這個連接里面的權限判斷邏輯都將依賴于此時讀到的權限即一個用戶成功建立連接后即使對這個用戶的權限做了修改也不會影響到已經(jīng)存在連接的權限修改完后只有再新建的連接才會使用新的權限設置備注連接完成后如果沒有后續(xù)的操作這個連接就處于空閑狀態(tài)可以使用show processlist進行查看當Command列中出現(xiàn)Sleep時表示該連接是空閑狀態(tài)客戶端如果太長時間沒有動靜連接器會自動斷開連接這個時間由參數(shù)wait_timeout控制默認值是8小時如果在連接被斷開后客戶端再次發(fā)起請求會收到一個錯誤提醒Lost connection to MySQL server during query。這時候如果要繼續(xù)就需要重連然后再執(zhí)行請求數(shù)據(jù)庫里面的長連接成功后如果客戶端持續(xù)有請求則一直使用同一個連接短連接則是每次執(zhí)行完很少的幾次查詢就斷開連接下次查詢再重新建立一個全部使用長連接后有時候MySQL占用內(nèi)存漲的很快這是因為MySQL在執(zhí)行過程中臨時使用的內(nèi)存是管理在連接對象中的這些資源在斷開時才會被釋放如果長連接累計下來可能導致內(nèi)存占用太大被系統(tǒng)強行殺掉OOM)從現(xiàn)象看就是MySQL異常重啟了解決方案1定期斷開長連接。使用一段時間或程序里面判斷執(zhí)行過一個占用內(nèi)存的大查詢后斷開連接之后要查詢再重連解決方案2MySQL版本如果是5.7及其以上可以在每次執(zhí)行一個大的操作后通過執(zhí)行mysql_reset_connection來重新初始化連接資源。這個過程不需要重連和做權限驗證會將連接恢復到剛剛創(chuàng)建完時的狀態(tài)。分析器ParserMySQL分析器會創(chuàng)建一個內(nèi)部結構解析樹MySQL的解析行為就像單趟編譯器一樣進行詞法解析、語法解析、語義解析詞法解析MySQL對語句進行識別里面的字符串分別是什么代表什么比如”select“是一個查詢語句把字符串”tableName識別為“表名 tableName把字符串”id“識別成”列id語法解析根據(jù)語法規(guī)則判斷輸入的SQL語句是否滿足MySQL語法比如“elect *from tableName這個會提示”You hava an error in your SQL syntax“的錯誤提醒一般語法錯誤會提示第一個出現(xiàn)錯誤的位置所以要關注的是緊接”use near的內(nèi)容優(yōu)化器Optimizer當解析樹完成后MySQL會進行一系列的優(yōu)化操作比如選擇重寫查詢語句、讀表順序、索引選擇等你可以使用explain進行查看細節(jié)【MySQL筆記3-- SQL分析】因此優(yōu)化器主要做下面的事情選擇最合適的索引選擇表掃還是走索引選擇表關聯(lián)順序優(yōu)化 where 子句排除管理中無用表決定 order by 和 group by 是否走索引嘗試使用 inner join 替換 outer join簡化子查詢決定結果緩存合并試圖緩沖器Caches緩沖器會緩存查詢的結果集當進行查詢解析前服務端會去緩存器查找如果改查詢命中緩存那就不需要進行解析、優(yōu)化、執(zhí)行等等操作直接返回緩存結果集【以key-value保存執(zhí)行過的語句及查詢結果】下面是查詢緩存的問題如果對表進行了更新操作該表的所有查詢緩存都會清空同時cache 的訪問由一個單一的全局鎖來控制這時候大量的查詢將被阻塞直至鎖釋放。所以不要簡單認為設置 cache 必定會帶來性能提升。MySQL官方文檔顯示The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.表示在MySQL5.7.20版本就不推薦使用在8.0版本就移除了緩沖器執(zhí)行器開始執(zhí)行時需要先判斷你有沒有這個表的執(zhí)行權限如果沒有返回沒有權限的錯誤【同時對于命中查詢緩存會在查詢緩存返回結果時做權限驗證查詢會在優(yōu)化器之前調(diào)用precheck驗證權限】如果有權限打開表繼續(xù)執(zhí)行執(zhí)行器根據(jù)表的引擎定義去使用這個引擎提供的接口比如查詢語句select *from table where id1;中的id字段沒有索引那么執(zhí)行器的執(zhí)行流程是調(diào)用InnoDB引擎接口取這個表的第一行判斷id是不是1如果不是則跳過如果是則將這行存在結果集中調(diào)用引擎接口取”下一行“重復相同的判斷邏輯直到取到這個表的最后一行執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的結果集返回給客戶端完整體系結構圖管理工具和服務Services and utilities提供備份backup、恢復recovery、安全security、復制replication、集群cluster、分區(qū)partitioning、工作臺workbench功能SQL接口接受用戶的SQL命令返回用戶需要查詢的結果比如select from調(diào)用了SQL Interface;是在MySQL客戶端和服務端之間進行服務的工具SQL接口組件包括了數(shù)據(jù)處理語言【Data Manipulation Language (DML)】、數(shù)據(jù)定義語言【Data Definition Language(DDL)】存儲過程、視圖、觸發(fā)器等等存儲引擎MyISAMMySQL5.1及其之前版本默認的存儲引擎該存儲引擎支持全文索引基于分詞創(chuàng)建的索引支持復雜的查詢、壓縮、空間函數(shù)等但不支持事務和行級鎖而且崩潰后無法安全恢復對于只讀的數(shù)據(jù)或表比較小、可以忍受修復操作則可以繼續(xù)使用MyISAMMyISAM會將表存儲在兩個文件中數(shù)據(jù)文件.MYD為擴展名和索引文件.MYI為擴展名)MyISAM支持表鎖即對整張表進行加鎖讀取時會對需要讀到的所有表加共享鎖寫入時對表加排他鎖。但是在表有讀取查詢的同時也可以往表插入新的記錄并發(fā)插入CONCURRENT INSERT)使用了MyISAM存儲引擎的表MySQL可以手工或自動執(zhí)行檢查check table mytable和修復repair table mytable)操作但執(zhí)行表的修復可以導致一些數(shù)據(jù)丟失并且修復操作是非常慢的對于BLOB和TEXT等長字段可以基于其前500個字符創(chuàng)建索引創(chuàng)建MyISAM表的時候如果指定了DELAY_KEY_WRITE(延遲更新索引鍵選項在每次修改執(zhí)行完時不會立刻將修改的索引數(shù)據(jù)寫入磁盤而是會寫到內(nèi)存的鍵緩沖區(qū)in-memory key buffer中只有在清理鍵緩沖區(qū)或關閉表時才將對應的索引塊寫入到磁盤。這種方式大大的提升寫入性能但是在數(shù)據(jù)庫或主機崩潰時會造成索引損壞需要進行修復操作。如果表在創(chuàng)建并導入數(shù)據(jù)后不會再進行修改操作可以使用MyISAM壓縮表使用myisampack對MyISAM表進行壓縮也叫打包pack壓縮表是不能進行修改的除非先將表解除壓縮修改數(shù)據(jù)然后再次壓縮壓縮表可以極大的減少磁盤空間占用和磁盤I/O從而 提升了查詢性能壓縮表支持索引但索引是只讀的InnoDBMySQL默認的事務型引擎用來處理大量的短期short-lived事務短期事務大部分情況是正常提交的很少會被回滾。InnoDB的性能和自動崩潰恢復特性使得它在非事務型存儲的需求中也很流行。MySQL5.1新的InnoDB plugin支持一些新特性比如利用排序創(chuàng)建索引、刪除或增加索引時不需要復制全表數(shù)據(jù)、新的支持壓縮的存儲格式、新的大型列值如BLOB的存儲方式、以及文件格式管理等。InnoDB采用MVCC來支持高并發(fā)并且實現(xiàn)了四個標準的隔離級別。其默認級別是repeatable read可重復讀并且通過間隙鎖next-key-locking)策略防止幻讀的出現(xiàn)。間隙鎖使得InnoDB不僅僅鎖定查詢設計的行還會對索引中的間隙進行鎖定防止幻影行的插入InnoDB表是基于聚簇索引建立的聚簇索引對主鍵查詢有很高的性能InnoDB的存儲格式是平臺獨立的也就是說可以將數(shù)據(jù)和索引文件從Intel平臺復制到PowerPc等其他平臺InnoDB從磁盤讀取數(shù)據(jù)時采用了可預測性預讀能夠自動在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應哈希索引能夠加速插入操作的插入緩沖區(qū)等InnoDB通過一些機制和工具支持真正的熱備份MySQL的其他存儲引擎不支持熱備份要獲取一致性視圖需要停止對所有表的寫入而在讀寫混合場景中停止寫入可以也意味著停止讀取Federated是訪問其他MySQL服務器的一個代理它會創(chuàng)建一個到遠程MySQL服務器的客戶端連接并將查詢傳輸?shù)竭h程服務器執(zhí)行然后提取或發(fā)送需要的數(shù)據(jù)。Archive只支持insert和select操作在MySQL5.1之前不支持索引Archive引擎會緩存所有的寫并利用zlib對插入的行進行壓縮所以比MyISAM表的磁盤I/O更少但是每次select都需要進行全表掃描Archive表適合日志和數(shù)據(jù)采集類應用這類應用做數(shù)據(jù)分析時往往需要全表掃描Archive引擎支持行級鎖和專用的緩沖區(qū)所以支持實現(xiàn)高并發(fā)的插入。在一個查詢開始直到返回表中存在的所有行數(shù)之前Archive引擎會阻止其他的select執(zhí)行以實現(xiàn)一致性讀。另外也實現(xiàn)了批量插入在完成之前對讀操作也是不可見的Merge是MyISAM引擎的一個變種Merge表是由多個MyISAM表合并而來的虛擬表但是將分區(qū)功能引入后該引擎已經(jīng)被放棄Memory如果需要快速地訪問數(shù)據(jù)并且這些數(shù)據(jù)不會被修改重啟以后丟失也沒關系那么使用Memory表是非常有用的。Memory表所有的數(shù)據(jù)都保存到內(nèi)存中不需要進行磁盤I/O表的結構在重啟以后會保留但數(shù)據(jù)會丟失使用場景比如用于查找或映射表將郵編和州名映射的表、緩存周期性聚合數(shù)據(jù)的結果、保存數(shù)據(jù)分析中產(chǎn)生的中間數(shù)據(jù)支持hash索引Memory表是表級鎖并發(fā)寫入的性能較低不支持BLOB或TEXT類型的列并且每行的長度是固定的所以即使指定了varchar列實際存儲時也會轉(zhuǎn)換成char這可以導致部分內(nèi)存的浪費如果使用臨時表來保存中間結果該臨時表使用Memory引擎但中間結果太大超出了Memory表的限制或者含有BLOB或TEXT字段則臨時表會轉(zhuǎn)換成MyISAM表Cluster指的是MySQL服務器、NDB集群存儲引擎以及分布式的、share-nothing的、容災的、高可用的NDB數(shù)據(jù)庫的組合被稱為MySQL集群Blackhole沒有實現(xiàn)任何的存儲機制所有插入的數(shù)據(jù)都會丟棄不做任何保存但服務器會記錄Blackhole表的日志所以可以用于復制數(shù)據(jù)到備庫或只是簡單地記錄到日志。這種特殊的存儲引擎可以在一些特殊的復制架構和日志審核時發(fā)揮作用CSV可以將普通的CSV文件逗號分割值的文件作為MySQL表來處理但不支持索引。CSV引擎可以在數(shù)據(jù)庫運行時拷入或拷出文件可以將excel等電子表格軟件中的數(shù)據(jù)存儲為CSV文件然后復制到MySQL數(shù)據(jù)目錄下就能在MySQL中打開使用。同樣如果將數(shù)據(jù)寫入到一個CSV引擎表其他的外部程序也能立即從表的數(shù)據(jù)文件中讀取CSV格式的數(shù)據(jù)。因此CSV引擎可以作為一種數(shù)據(jù)交換的機制Example該引擎作為MySQL源代碼中的示例說明了如何開始編寫新的存儲引擎它主要是開發(fā)人員感興趣的。存儲引擎是什么都不做的“存根”可以使用此引擎創(chuàng)建表但是不能將數(shù)據(jù)存儲在表中或從表中檢索數(shù)據(jù)。參考【網(wǎng)址需要翻墻】理解MySQL的體系結構https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html高性能MySQLhttps://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html#ftn.CHP-1-FN-2MySQL 5.7官方文檔《高性能MySQL》作者: 施瓦茨 (Baron Schwartz) / 扎伊采夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) 譯者: 寧海元 / 周振興 / 彭立勛 / 翟衛(wèi)祥,劉輝