MySQL優(yōu)化之大字段longtext、text所生產(chǎn)的問(wèn)題
背景
對(duì)接多個(gè)外部接口,需要保存請(qǐng)求參數(shù)以及返回參數(shù),方便消息的補(bǔ)償,因?yàn)槎鄠€(gè)外部接口,多個(gè)接口字段都不統(tǒng)一,整體使用一個(gè)大字段(longtext)進(jìn)行存儲(chǔ),但是當(dāng)數(shù)據(jù)只有40w的時(shí)候查詢速度就非常慢長(zhǎng)達(dá)40s左右。
CREATE TABLE `risk_request_log_bak` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `risk_buss_no` varchar(32) DEFAULT NULL COMMENT '', `buss_no` varchar(32) DEFAULT NULL COMMENT '', `buss_order_no` varchar(32) DEFAULT NULL COMMENT '', `server_name` varchar(30) DEFAULT NULL COMMENT '', `url` varchar(500) DEFAULT NULL COMMENT '', `interface_code` varchar(10) DEFAULT NULL COMMENT '', `request_msg` longtext COMMENT '請(qǐng)求參數(shù)體', `response_msg` longtext COMMENT '響應(yīng)參數(shù)體', `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間', `remark` varchar(50) DEFAULT NULL COMMENT '', `resp_time` datetime DEFAULT NULL COMMENT '響應(yīng)時(shí)間', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=451029 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='接';
萬(wàn)金油的策略加索引,需要查詢的字段risk_buss_no上添加索引,速度由原來(lái)的5min以上,變?yōu)閹装賛s
思考:
40w的數(shù)據(jù)就算不加索引查詢的時(shí)間40s左右也是不正常的。
嘗試:
- `request_msg` longtext COMMENT '請(qǐng)求參數(shù)體',
- `response_msg` longtext COMMENT '響應(yīng)參數(shù)體'
這兩個(gè)字段設(shè)置成64位的數(shù)據(jù)之后,查詢效率明顯提升
原因:
為了清楚大字段對(duì)性能的影響,我們必須要知道innodb存儲(chǔ)引擎在底層對(duì)行的處理方式:
知識(shí)點(diǎn)一:在5.1中,innodb存儲(chǔ)引擎的默認(rèn)的行格式為compact(redundant為兼容以前的版本),對(duì)于blob,text,varchar(8099)這樣的大字段,innodb只會(huì)存放前768字節(jié)在數(shù)據(jù)頁(yè)中,而剩余的數(shù)據(jù)則會(huì)存儲(chǔ)在溢出段中(發(fā)生溢出情況的時(shí)候適用,不溢出的時(shí)候就全都存在數(shù)據(jù)行里);
知識(shí)點(diǎn)二:innodb的塊大小默認(rèn)為16kb,由于innodb存儲(chǔ)引擎表為索引組織表,樹(shù)底層的葉子節(jié)點(diǎn)為一雙向鏈表,因此每個(gè)頁(yè)中至少應(yīng)該有兩行記錄,這就決定了innodb在存儲(chǔ)一行數(shù)據(jù)的時(shí)候不能夠超過(guò)8k(8098字節(jié));
知識(shí)點(diǎn)三:使用了blob數(shù)據(jù)類型,是不是一定就會(huì)存放在溢出段中?通常我們認(rèn)為blob,clob這類的大對(duì)象的存儲(chǔ)會(huì)把數(shù)據(jù)存放在數(shù)據(jù)頁(yè)之外,其實(shí)不然,關(guān)鍵點(diǎn)還是要看一個(gè)page中到底能否存放兩行數(shù)據(jù),blob可以完全存放在數(shù)據(jù)頁(yè)中(單行長(zhǎng)度沒(méi)有超過(guò)8098字節(jié)),而varchar類型的也有可能存放在溢出頁(yè)中(單行長(zhǎng)度超過(guò)8098字節(jié),前768字節(jié)存放在數(shù)據(jù)頁(yè)中);
知識(shí)點(diǎn)四:5.1中的innodb_plugin引入了新的文件格式:barracuda(將compact和redundant合稱為antelope),該文件格式擁有新的兩種行格式:compressed和dynamic,兩種格式對(duì)blob字段采用完全溢出的方式,數(shù)據(jù)頁(yè)中只存放20字節(jié),其余的都存放在溢出段中:
知識(shí)點(diǎn)五:mysql在操作數(shù)據(jù)的時(shí)候,以page為單位,不管是更新,插入,刪除一行數(shù)據(jù),都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中,然后在進(jìn)行操作,這樣就存在一個(gè)命中率的問(wèn)題,如果一個(gè)page中能夠相對(duì)的存放足夠多的行,那么命中率就會(huì)相對(duì)高一些,性能就會(huì)有提升;
查詢一下字段的長(zhǎng)度:
可以知道這個(gè)字段的平均長(zhǎng)度大約在2.5kb
查看一下mysql的row_format
根據(jù)知識(shí)點(diǎn)四可以知道:數(shù)據(jù)頁(yè)中只存放20字節(jié),其余的都存放在溢出段中
實(shí)際:
1、innodb的data page默認(rèn)是16K,在新數(shù)據(jù)寫(xiě)入的時(shí)候,會(huì)預(yù)留1/16的空間,用于后續(xù)的新紀(jì)錄寫(xiě)入,減少頻繁的新增怕個(gè)的開(kāi)銷
2、每個(gè)data page,至少要存儲(chǔ)2行,因此理論上行的最大長(zhǎng)度是8K,實(shí)際上因?yàn)橐驗(yàn)橐恍┑膇nnodb內(nèi)部數(shù)據(jù)結(jié)構(gòu)導(dǎo)致每行要小于8K
3、結(jié)合上面的兩點(diǎn),為了保障良好的順序?qū)懭?,每個(gè)innodb最好有個(gè)自增的id,而且一個(gè)page頁(yè)最好的填充率是1/2到15/16
4、當(dāng)page少于兩行,innodb會(huì)進(jìn)行收縮,盡可能的釋放空間,最主要的兩種就是上面的知識(shí)點(diǎn)一和知識(shí)點(diǎn)四
結(jié)合上面的4點(diǎn),我們知道一行的最大長(zhǎng)度是2.5K遠(yuǎn)遠(yuǎn)小于8K,所以大字段的數(shù)據(jù)都會(huì)存到數(shù)據(jù)段中,而不會(huì)溢出到off page中,因此我們可以看出主要是由于大字段緩存到data page中,內(nèi)存利用率很差,造成了大量的隨機(jī)讀。
主要的應(yīng)對(duì)策略:
1、拆表,將大字段拆到另一個(gè)表中
2、索引,從訪問(wèn)密度較小的數(shù)據(jù)頁(yè)改為訪問(wèn)密度很大的索引頁(yè),隨機(jī)io轉(zhuǎn)換為順序io,同時(shí)內(nèi)存命中率大大提升;
總結(jié):核心思想是讓單個(gè)page能夠存放足夠多的行,不斷的提示內(nèi)存的命中率
到此這篇關(guān)于MySQL優(yōu)化之大字段longtext、text所生產(chǎn)的問(wèn)題的文章就介紹到這了,更多相關(guān)MySQL大字段longtext、text內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- mysql中text,longtext,mediumtext區(qū)別小結(jié)
- MySQL中字段類型為longtext的值導(dǎo)出后顯示二進(jìn)制串方式
- MySql中的longtext字段的返回問(wèn)題及解決
- Mysql LONGTEXT 類型存儲(chǔ)大文件(二進(jìn)制也可以) (修改+調(diào)試+整理)
- Mysql的longblob字段插入數(shù)據(jù)問(wèn)題解決
- php中將圖片gif,jpg或mysql longblob或blob字段值轉(zhuǎn)換成16進(jìn)制字符串
- 深入解析MySQL中的longtext與longblob及應(yīng)用場(chǎng)景
相關(guān)文章
Mysql中MyISAM和InnoDB的區(qū)別及說(shuō)明
這篇文章主要介紹了Mysql中MyISAM和InnoDB的區(qū)別及說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-12-12MySQL SHOW PROCESSLIST協(xié)助故障診斷全過(guò)程
這篇文章主要給大家介紹了關(guān)于MySQL SHOW PROCESSLIST協(xié)助故障診斷的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02淺談mysqldump使用方法(MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù))
下面小編就為大家?guī)?lái)一篇淺談mysqldump使用方法(MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù))。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-01-01MySQL數(shù)據(jù)庫(kù)查詢性能優(yōu)化策略
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)查詢性能優(yōu)化的策略,幫助大家的工作學(xué)習(xí)提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能,感興趣的朋友可以了解下2020-08-08Django2.* + Mysql5.7開(kāi)發(fā)環(huán)境整合教程圖解
這篇文章主要介紹了Django2.* + Mysql5.7開(kāi)發(fā)環(huán)境整合教程,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-09-09