MySQL的基本架構以及解決長連接內存占用問題的兩種方案分享

本篇內容介紹了“MySQL的基本架構以及解決長連接內存占用問題的兩種方案分享”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

MySQL分為server層和存儲引擎兩部分。

Server層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋MySQL的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

而存儲引擎層負責數據的存儲和提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎。現在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認存儲引擎。

連接器

連接命令

mysql -h$ip -P$port -u$user -p

連接命令中的mysql是客戶端工具，用來跟服務端建立連接。在完成經典的TCP握手后，連接器就要開始認證你的身份，這個時候用的就是你輸入的用戶名和密碼。

• 如果用戶名或密碼不對，你就會收到一個"Access denied for user"的錯誤，然后客戶端程序結束執行。

• 如果用戶名密碼認證通過，連接器會到權限表里面查出你擁有的權限。之后，這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。

一個用戶建立連接后，即使被修改了權限，也不會影響現在，下次重新鏈接后才生效，因為連接器在權限表查出權限進行判斷。

連接完成后，如果你沒有后續的動作，這個連接就處于空閑狀態，你可以在show processlist命令中看到它。文本中這個圖是show processlist的結果，其中的Command列顯示為“Sleep”的這一行，就表示現在系統里面有一個空閑連接。

客戶端如果太長時間沒動靜，連接器就會自動將它斷開。這個時間是由參數wait_timeout控制的，默認值是8小時。

mysql> show variables like 'wait_timeout';

如果在連接被斷開之后，客戶端再次發送請求的話，就會收到一個錯誤提醒： Lost connection to MySQL server during query。這時候如果你要繼續，就需要重連，然后再執行請求了。

數據庫里面，長連接是指連接成功后，如果客戶端持續有請求，則一直使用同一個連接。短連接則是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

建立連接的過程通常是比較復雜的，所以我建議你在使用中要盡量減少建立連接的動作，也就是盡量使用長連接。

但是全部使用長連接后，你可能會發現，有些時候MySQL占用內存漲得特別快，這是因為MySQL在執行過程中臨時使用的內存是管理在連接對象里面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來，可能導致內存占用太大，被系統強行殺掉（OOM），從現象看就是MySQL異常重啟了。

解決長連接內存占用問題的兩種方案： 1、定期斷開長連接。使用一段時間，或者程序里面判斷執行過一個占用內存的大查詢后，斷開連接，之后要查詢再重連。 2、如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作后，通過執行 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創建完時的狀態。

關于數據庫連接池：

現在假設：最大維持連接數是10，最大連接數是20，最大等待時間是10000（毫秒）

其過程：最開始在連接池中有10個連接（最大連接數），當有用戶申請連接時，其將一個連接分配用戶，直到連接池中的10個連接全都分配出去，當第11個用戶申請連接時，它將創建第11個連接并分配給該用戶，直到把第20個連接（最大連接數）分配給第20個用戶，當第21個用戶申請連接時，它需要等待，直到前面的20個用戶中某一個斷開了連接，才會把那個連接分配給第21個用戶，當用戶斷開連接時（第11至20個連接），該連接不會立刻被釋放而是需要等待10000毫秒（最大等待時間），才被釋放，當沒有用戶連接時，連接池內還保持10個連接。

關于查詢緩存

我找到了一篇關于MySQL8.0禁用緩存的博客

mysql> show variables like '%query_cache%'; 

query_cache_type 為 ON 表示已經開啟）

query_cache_type: 是否打開緩存 

1. OFF: 關閉 

2. ON: 總是打開 

3. DEMAND: 只有明確寫了SQL_CACHE的查詢才會吸入緩存
   query_cache_size: 緩存使用的總內存空間大小,單位是字節,這個值必須是1024的整數倍,否則MySQL實際分配可能跟這個數值不同(感覺這個應該跟文件系統的blcok大小有關)
   query_cache_min_res_unit: 分配內存塊時的最小單位大小
   query_cache_limit: MySQL能夠緩存的最大結果,如果超出,則增加 Qcache_not_cached的值,并刪除查詢結果
   query_cache_wlock_invalidate: 如果某個數據表被鎖住,是否仍然從緩存中返回數據,默認是OFF,表示仍然可以返回
   GLOBAL STAUS 中 關于 緩存的參數解釋: 
   Qcache_free_blocks: 緩存池中空閑塊的個數 
   Qcache_free_memory: 緩存中空閑內存量 
   Qcache_hits: 緩存命中次數 
   Qcache_inserts: 緩存寫入次數 
   Qcache_lowmen_prunes: 因內存不足刪除緩存次數 
   Qcache_not_cached: 查詢未被緩存次數,例如查詢結果超出緩存塊大小,查詢中包含可變函數等 
   Qcache_queries_in_cache: 當前緩存中緩存的SQL數量 
   Qcache_total_blocks: 緩存總block數

好在MySQL也提供了這種“按需使用”的方式。你可以將參數query_cache_type設置成DEMAND，這樣對于默認的SQL語句都不使用查詢緩存。而對于你確定要使用查詢緩存的語句，可以用SQL_CACHE顯式指定，像下面這個語句一樣：

mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了，也就是說8.0開始徹底沒有這個功能了 。

分析器

如果沒有命中查詢緩存，就要開始真正執行語句了。首先,對SQL語句做解析,分析器先會做“詞法分析”,然后做“語法分析”。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。不對的SQL你會收到你最熟悉的錯誤，你懂的...

優化器

就是形成優化方案（ 索引和連接順序優化，最后由執行器開始執行 ），然后進入執行器階段。

執行器

開始執行的時候，要先判斷一下你對這個表T有沒有執行查詢的權限( 這時候對 "表" 的權限進行判斷。而連接器是驗證用戶身份。 )，如果沒有，就會返回沒有權限的錯誤，如下所示(在工程實現上，如果命中查詢緩存，會在查詢緩存放回結果的時候，做權限驗證。查詢也會在優化器之前調用precheck驗證權限)。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口。

比如我們這個例子中的表T中，ID字段沒有索引，那么執行器的執行流程是這樣的：

1. 調用InnoDB引擎接口取這個表的第一行（ 一般是以頁為單位從硬盤讀取，而不是每次只讀一行 ），判斷ID值是不是10，如果不是則跳過，如果是則將這行存在結果集中；

2. 調用引擎接口取“下一行”，重復相同的判斷邏輯，直到取到這個表的最后一行。

3. 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

至此，這個語句就執行完成了。

“MySQL的基本架構以及解決長連接內存占用問題的兩種方案分享”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！