背景

對接多個外部接口，需要保存請求參數(shù)以及返回參數(shù)，方便消息的補(bǔ)償，因?yàn)槎鄠€外部接口，多個接口字段都不統(tǒng)一，整體使用一個大字段（longtext）進(jìn)行存儲，但是當(dāng)數(shù)據(jù)只有40w的時候查詢速度就非常慢長達(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 '請求參數(shù)體',
  `response_msg` longtext COMMENT '響應(yīng)參數(shù)體',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',
  `remark` varchar(50) DEFAULT NULL COMMENT '',
  `resp_time` datetime DEFAULT NULL COMMENT '響應(yīng)時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=451029 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='接';

萬金油的策略加索引，需要查詢的字段risk_buss_no上添加索引，速度由原來的5min以上，變?yōu)閹装賛s

思考：

40w的數(shù)據(jù)就算不加索引查詢的時間40s左右也是不正常的。

嘗試：

•   `request_msg` longtext COMMENT '請求參數(shù)體',
•   `response_msg` longtext COMMENT '響應(yīng)參數(shù)體'

這兩個字段設(shè)置成64位的數(shù)據(jù)之后，查詢效率明顯提升

原因：

為了清楚大字段對性能的影響，我們必須要知道innodb存儲引擎在底層對行的處理方式：

知識點(diǎn)一：在5.1中，innodb存儲引擎的默認(rèn)的行格式為compact(redundant為兼容以前的版本），對于blob，text，varchar(8099)這樣的大字段，innodb只會存放前768字節(jié)在數(shù)據(jù)頁中，而剩余的數(shù)據(jù)則會存儲在溢出段中（發(fā)生溢出情況的時候適用，不溢出的時候就全都存在數(shù)據(jù)行里）；

知識點(diǎn)二：innodb的塊大小默認(rèn)為16kb，由于innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點(diǎn)為一雙向鏈表，因此每個頁中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k（8098字節(jié)）；

知識點(diǎn)三：使用了blob數(shù)據(jù)類型，是不是一定就會存放在溢出段中？通常我們認(rèn)為blob，clob這類的大對象的存儲會把數(shù)據(jù)存放在數(shù)據(jù)頁之外，其實(shí)不然，關(guān)鍵點(diǎn)還是要看一個page中到底能否存放兩行數(shù)據(jù)，blob可以完全存放在數(shù)據(jù)頁中(單行長度沒有超過8098字節(jié))，而varchar類型的也有可能存放在溢出頁中(單行長度超過8098字節(jié)，前768字節(jié)存放在數(shù)據(jù)頁中)；

知識點(diǎn)四：5.1中的innodb_plugin引入了新的文件格式：barracuda（將compact和redundant合稱為antelope），該文件格式擁有新的兩種行格式：compressed和dynamic，兩種格式對blob字段采用完全溢出的方式，數(shù)據(jù)頁中只存放20字節(jié)，其余的都存放在溢出段中:

知識點(diǎn)五：mysql在操作數(shù)據(jù)的時候，以page為單位，不管是更新，插入，刪除一行數(shù)據(jù)，都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中，然后在進(jìn)行操作，這樣就存在一個命中率的問題，如果一個page中能夠相對的存放足夠多的行，那么命中率就會相對高一些，性能就會有提升；

查詢一下字段的長度：

可以知道這個字段的平均長度大約在2.5kb

查看一下mysql的row_format

根據(jù)知識點(diǎn)四可以知道：數(shù)據(jù)頁中只存放20字節(jié)，其余的都存放在溢出段中

實(shí)際：

1、innodb的data page默認(rèn)是16K，在新數(shù)據(jù)寫入的時候，會預(yù)留1/16的空間，用于后續(xù)的新紀(jì)錄寫入，減少頻繁的新增怕個的開銷

2、每個data page，至少要存儲2行，因此理論上行的最大長度是8K，實(shí)際上因?yàn)橐驗(yàn)橐恍┑膇nnodb內(nèi)部數(shù)據(jù)結(jié)構(gòu)導(dǎo)致每行要小于8K

3、結(jié)合上面的兩點(diǎn)，為了保障良好的順序?qū)懭?，每個innodb最好有個自增的id，而且一個page頁最好的填充率是1/2到15/16

4、當(dāng)page少于兩行，innodb會進(jìn)行收縮，盡可能的釋放空間，最主要的兩種就是上面的知識點(diǎn)一和知識點(diǎn)四

結(jié)合上面的4點(diǎn)，我們知道一行的最大長度是2.5K遠(yuǎn)遠(yuǎn)小于8K，所以大字段的數(shù)據(jù)都會存到數(shù)據(jù)段中，而不會溢出到off page中，因此我們可以看出主要是由于大字段緩存到data page中，內(nèi)存利用率很差，造成了大量的隨機(jī)讀。

主要的應(yīng)對策略：

1、拆表，將大字段拆到另一個表中

2、索引，從訪問密度較小的數(shù)據(jù)頁改為訪問密度很大的索引頁，隨機(jī)io轉(zhuǎn)換為順序io，同時內(nèi)存命中率大大提升；

總結(jié)：核心思想是讓單個page能夠存放足夠多的行，不斷的提示內(nèi)存的命中率

到此這篇關(guān)于MySQL優(yōu)化之大字段longtext、text所生產(chǎn)的問題的文章就介紹到這了,更多相關(guān)MySQL大字段longtext、text內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論