MySQL的基本架構(gòu)以及解決長(zhǎng)連接內(nèi)存占用問(wèn)題的兩種方案分享

本篇內(nèi)容介紹了“MySQL的基本架構(gòu)以及解決長(zhǎng)連接內(nèi)存占用問(wèn)題的兩種方案分享”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

MySQL分為server層和存儲(chǔ)引擎兩部分。

Server層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋MySQL的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

而存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是InnoDB，它從MySQL 5.5.5版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎。

連接器

連接命令

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

連接命令中的mysql是客戶端工具，用來(lái)跟服務(wù)端建立連接。在完成經(jīng)典的TCP握手后，連接器就要開(kāi)始認(rèn)證你的身份，這個(gè)時(shí)候用的就是你輸入的用戶名和密碼。

• 如果用戶名或密碼不對(duì)，你就會(huì)收到一個(gè)"Access denied for user"的錯(cuò)誤，然后客戶端程序結(jié)束執(zhí)行。

• 如果用戶名密碼認(rèn)證通過(guò)，連接器會(huì)到權(quán)限表里面查出你擁有的權(quán)限。之后，這個(gè)連接里面的權(quán)限判斷邏輯，都將依賴于此時(shí)讀到的權(quán)限。

一個(gè)用戶建立連接后，即使被修改了權(quán)限，也不會(huì)影響現(xiàn)在，下次重新鏈接后才生效，因?yàn)檫B接器在權(quán)限表查出權(quán)限進(jìn)行判斷。

連接完成后，如果你沒(méi)有后續(xù)的動(dòng)作，這個(gè)連接就處于空閑狀態(tài)，你可以在show processlist命令中看到它。文本中這個(gè)圖是show processlist的結(jié)果，其中的Command列顯示為“Sleep”的這一行，就表示現(xiàn)在系統(tǒng)里面有一個(gè)空閑連接。

客戶端如果太長(zhǎng)時(shí)間沒(méi)動(dòng)靜，連接器就會(huì)自動(dòng)將它斷開(kāi)。這個(gè)時(shí)間是由參數(shù)wait_timeout控制的，默認(rèn)值是8小時(shí)。

mysql> show variables like 'wait_timeout';

如果在連接被斷開(kāi)之后，客戶端再次發(fā)送請(qǐng)求的話，就會(huì)收到一個(gè)錯(cuò)誤提醒： Lost connection to MySQL server during query。這時(shí)候如果你要繼續(xù)，就需要重連，然后再執(zhí)行請(qǐng)求了。

數(shù)據(jù)庫(kù)里面，長(zhǎng)連接是指連接成功后，如果客戶端持續(xù)有請(qǐng)求，則一直使用同一個(gè)連接。短連接則是指每次執(zhí)行完很少的幾次查詢就斷開(kāi)連接，下次查詢?cè)僦匦陆⒁粋€(gè)。

建立連接的過(guò)程通常是比較復(fù)雜的，所以我建議你在使用中要盡量減少建立連接的動(dòng)作，也就是盡量使用長(zhǎng)連接。

但是全部使用長(zhǎng)連接后，你可能會(huì)發(fā)現(xiàn)，有些時(shí)候MySQL占用內(nèi)存漲得特別快，這是因?yàn)镸ySQL在執(zhí)行過(guò)程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的。這些資源會(huì)在連接斷開(kāi)的時(shí)候才釋放。所以如果長(zhǎng)連接累積下來(lái)，可能導(dǎo)致內(nèi)存占用太大，被系統(tǒng)強(qiáng)行殺掉（OOM），從現(xiàn)象看就是MySQL異常重啟了。

解決長(zhǎng)連接內(nèi)存占用問(wèn)題的兩種方案： 1、定期斷開(kāi)長(zhǎng)連接。使用一段時(shí)間，或者程序里面判斷執(zhí)行過(guò)一個(gè)占用內(nèi)存的大查詢后，斷開(kāi)連接，之后要查詢?cè)僦剡B。 2、如果你用的是 MySQL 5.7 或更新版本，可以在每次執(zhí)行一個(gè)比較大的操作后，通過(guò)執(zhí)行 mysql_reset_connection 來(lái)重新初始化連接資源。這個(gè)過(guò)程不需要重連和重新做權(quán)限驗(yàn)證，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完時(shí)的狀態(tài)。

關(guān)于數(shù)據(jù)庫(kù)連接池：

現(xiàn)在假設(shè)：最大維持連接數(shù)是10，最大連接數(shù)是20，最大等待時(shí)間是10000（毫秒）

其過(guò)程：最開(kāi)始在連接池中有10個(gè)連接（最大連接數(shù)），當(dāng)有用戶申請(qǐng)連接時(shí)，其將一個(gè)連接分配用戶，直到連接池中的10個(gè)連接全都分配出去，當(dāng)?shù)?1個(gè)用戶申請(qǐng)連接時(shí)，它將創(chuàng)建第11個(gè)連接并分配給該用戶，直到把第20個(gè)連接（最大連接數(shù)）分配給第20個(gè)用戶，當(dāng)?shù)?1個(gè)用戶申請(qǐng)連接時(shí)，它需要等待，直到前面的20個(gè)用戶中某一個(gè)斷開(kāi)了連接，才會(huì)把那個(gè)連接分配給第21個(gè)用戶，當(dāng)用戶斷開(kāi)連接時(shí)（第11至20個(gè)連接），該連接不會(huì)立刻被釋放而是需要等待10000毫秒（最大等待時(shí)間），才被釋放，當(dāng)沒(méi)有用戶連接時(shí)，連接池內(nèi)還保持10個(gè)連接。

關(guān)于查詢緩存

我找到了一篇關(guān)于MySQL8.0禁用緩存的博客

mysql> show variables like '%query_cache%'; 

query_cache_type 為 ON 表示已經(jīng)開(kāi)啟）

query_cache_type: 是否打開(kāi)緩存 

1. OFF: 關(guān)閉 

2. ON: 總是打開(kāi) 

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

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

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

MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了，也就是說(shuō)8.0開(kāi)始徹底沒(méi)有這個(gè)功能了 。

分析器

如果沒(méi)有命中查詢緩存，就要開(kāi)始真正執(zhí)行語(yǔ)句了。首先,對(duì)SQL語(yǔ)句做解析,分析器先會(huì)做“詞法分析”,然后做“語(yǔ)法分析”。根據(jù)詞法分析的結(jié)果，語(yǔ)法分析器會(huì)根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè)SQL語(yǔ)句是否滿足MySQL語(yǔ)法。不對(duì)的SQL你會(huì)收到你最熟悉的錯(cuò)誤，你懂的...

優(yōu)化器

就是形成優(yōu)化方案（ 索引和連接順序優(yōu)化，最后由執(zhí)行器開(kāi)始執(zhí)行 ），然后進(jìn)入執(zhí)行器階段。

執(zhí)行器

開(kāi)始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表T有沒(méi)有執(zhí)行查詢的權(quán)限( 這時(shí)候?qū)?"表" 的權(quán)限進(jìn)行判斷。而連接器是驗(yàn)證用戶身份。 )，如果沒(méi)有，就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤，如下所示(在工程實(shí)現(xiàn)上，如果命中查詢緩存，會(huì)在查詢緩存放回結(jié)果的時(shí)候，做權(quán)限驗(yàn)證。查詢也會(huì)在優(yōu)化器之前調(diào)用precheck驗(yàn)證權(quán)限)。

mysql> select * from T where ID=10;

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

如果有權(quán)限，就打開(kāi)表繼續(xù)執(zhí)行。打開(kāi)表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。

比如我們這個(gè)例子中的表T中，ID字段沒(méi)有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

1. 調(diào)用InnoDB引擎接口取這個(gè)表的第一行（ 一般是以頁(yè)為單位從硬盤(pán)讀取，而不是每次只讀一行 ），判斷ID值是不是10，如果不是則跳過(guò)，如果是則將這行存在結(jié)果集中；

2. 調(diào)用引擎接口取“下一行”，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行。

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

至此，這個(gè)語(yǔ)句就執(zhí)行完成了。

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