最近在數(shù)據(jù)庫優(yōu)化的時候,看到一些表在設(shè)計上使用了text或者blob的字段,單表的存儲空間已經(jīng)達到了近100G,這種情況再去改變和優(yōu)化就非常難了
一、簡介
為了清楚大字段對性能的影響,我們必須要知道innodb存儲引擎的處理方式:
1、一些知識點
1.1 在InnoDB 1.0.x版本之前,InnoDB 存儲引擎提供了 Compact
和 Redundant(Redundant 格式是為兼容之前版本而保留的)
兩種格式來存放行記錄數(shù)據(jù),compact 和 redundant 合稱為Antelope (羚羊)
對于blob,text,varchar(5120)這樣的大字段,innodb只會存放前768字節(jié)在數(shù)據(jù)頁中,而剩余的數(shù)據(jù)則會存儲在溢出段中(發(fā)生溢出情況的時候適用),最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index,其余更多的內(nèi)容存儲在額外的page里,哪怕只是多了一個字節(jié)。因此,所有列長度越短越好
大字段在InnoDB里可能浪費大量空間。例如,若存儲字段值只是比行的要求多了一個字節(jié),也會使用整個頁面來存儲剩下的字節(jié),浪費了頁面的大部分空間。如果有一個值只是稍微超過了32個頁的大小,實際上就需要使用96個頁面
擴展存儲禁用了自適應(yīng)哈希,因為需要完整的比較列的整個長度,才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)(哈希幫助InnoDB非??焖俚恼业健安聹y的位置”,但是必須檢查“猜測的位置”是不是正確)。因為自適應(yīng)哈希是完全的內(nèi)存結(jié)構(gòu),并且直接指向Buffer Pool中訪問“最”頻繁的頁面,但對于擴展存儲空間卻無法使用Adaptive Hash
1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式:Barracuda (梭子魚)
,該文件格式擁有新的兩種行格式:compressed
和dynamic,兩種格式對blob字段采用完全溢出的方式,數(shù)據(jù)頁中只存放20字節(jié),其余的都存放在溢出段中,因此,強烈不建議使用BLOB、TEXT、超過255長度的VARCHAR列類型;
1.3 innodb的page大小默認為16kb,innodb存儲引擎表為索引組織表,樹底層的葉子節(jié)點為一雙向鏈表,因此每個頁中至少應(yīng)該有兩行記錄,這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k,但事實上應(yīng)該更小,因為還有一些InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)要存儲,5.6版本以后,新增選項 innodb_page_size 可以修改,在5.6以前的版本,只能修改源碼重新編譯,但并不推薦修改這個配置
1.4 InnoDB的data page在有新數(shù)據(jù)寫入時,會預(yù)留1/16的空間,預(yù)留出來的空間可用于后續(xù)的新紀錄寫入,減少頻繁的新增data page的開銷,受限于InnoDB存儲方式,數(shù)據(jù)如果是順序?qū)懭氲脑挘罾硐氲那闆r下,data page的填充率是15/16,但一般沒辦法保證完全的順序?qū)懭?,因此data page的填充率一般是1/2到15/16。因此每個InnoDB表都最好要有一個自增列作為主鍵,使得新紀錄寫入盡可能是順序的;當data page填充率不足1/2時,InnoDB會進行收縮,釋放空閑空間
1.5 COMPACT行格式相比REDUNDANT,大概能節(jié)省20%的存儲空間,COMPRESSED相比COMPACT大概能節(jié)省50%的存儲空間,但會導(dǎo)致TPS下降了90%。因此強烈不推薦使用COMPRESSED行格式
1.6 使用了blob數(shù)據(jù)類型,是不是一定就會存放在溢出段中?通常我們認為blob這類的大對象的存儲會把數(shù)據(jù)存放在數(shù)據(jù)頁之外,其實不然,關(guān)鍵點還是要看一個page中到底能否存放兩行數(shù)據(jù),blob可以完全存放在數(shù)據(jù)頁中(單行長度沒有超過8096字節(jié)),而varchar類型的也有可能存放在溢出頁中(單行長度超過8096字節(jié),前768字節(jié)存放在數(shù)據(jù)頁中)
1.7 mysql在操作數(shù)據(jù)的時候,以page為單位,不管是更新,插入,刪除一行數(shù)據(jù),都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中,然后在進行操作,這樣就存在一個命中率的問題,如果一個page中能夠相對的存放足夠多的行,那么命中率就會相對高一些,性能就會有提升
1.8 在off-page中存儲的BLOB、TEXT或者長VARCHAR列的page是獨享的,不能共享。因此強烈不建議在一個表中使用多個長列
1.9 MySQL 5.6 中默認還是 Compact 行格式,也是目前使用最多的一種 ROW FORMAT。用戶可以通過命令 SHOW TABLE STATUS LIKE'table_name'
來查看當前表使用的行格式,其中 row_format 列表示當前所使用的行記錄結(jié)構(gòu)類型
mysql>desc db_page; +-----------------+----------------+----------------+---------------+-------------------+-----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+----------------+----------------+---------------+-------------------+-----------------+ | id | int(11) | NO | PRI | | auto_increment | | title | varchar(100) | NO | | | | | name | varchar(100) | YES | | | | | content | text | YES | | | | +-----------------+----------------+----------------+---------------+-------------------+-----------------+ mysql>show variables like "innodb_file_format"; +-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | innodb_file_format | Barracuda | +-------------------------+-----------------+ mysql>show table status like "db_page" \G *************************** 1. row *************************** Name: db_page Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 3 Create_time: 2017-03-07 13:30:19 Update_time: Check_time: Collation: utf8_general_ci Checksum: Create_options: Comment: Block_format: Original
在 msyql 5.7.9 及以后版本,默認行格式由innodb_default_row_format
變量決定,它的默認值是DYNAMIC
,也可以在 create table 的時候指定ROW_FORMAT=DYNAMIC
。
注意,如果要修改現(xiàn)有表的行模式為compressed
或dynamic
,必須先將文件格式設(shè)置成Barracuda:set global innodb_file_format=Barracuda;
,再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;
去修改才能生效,否則修改無效卻無提示
二、對TEXT/BLOB這類大字段類型的影響
2.1 compact
變長大字段類型包括blob,text,varchar,其中varchar列值長度大于某數(shù)N時也會存溢出頁,在latin1字符集下N值可以這樣計算:innodb的塊大小默認為16kb,由于innodb存儲引擎表為索引組織表,樹底層的葉子節(jié)點為一雙向鏈表,因此每個頁中至少應(yīng)該有兩行記錄,這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k,減去其它列值所占字節(jié)數(shù),約等于N。對于InnoDB,內(nèi)存是極為珍貴的,如果把768字節(jié)長度的blob都放在數(shù)據(jù)頁,雖然可以節(jié)省部分IO,但是能緩存行數(shù)就變少,也就是能緩存的索引值變少了,降低了索引效率
2.2 dynamic
dynamic行格式,列存儲是否放到off-page頁,主要取決于行大小,它會把行中最長的那一列放到off-page,直到數(shù)據(jù)頁能存放下兩行。TEXT/BLOB列 <=40 bytes 時總是存放于數(shù)據(jù)頁。這種方式可以避免compact那樣把太多的大列值放到 B-tree Node,因為dynamic格式認為,只要大列值有部分數(shù)據(jù)放在off-page,那把整個值放入都放入off-page更有效。
compressed 物理結(jié)構(gòu)上與dynamic類似,但是對表的數(shù)據(jù)行使用zlib算法進行了壓縮存儲。在long blob列類型比較多的情況下用,可以降低off-page的使用,減少存儲空間(一般40%左右),但要求更高的CPU,buffer pool里面可能會同時存儲數(shù)據(jù)的壓縮版和非壓縮版,所以也多占用部分內(nèi)存。這里 MySQL 5.6 Manual innodb-compression-internals 講的十分清楚。
另外,由于ROW_FORMAT=DYNAMIC
和 ROW_FORMAT=COMPRESSED
是從 ROW_FORMAT=COMPACT
變化來的,所以他們處理 CHAR
類型存儲的方式和 COMPACT 一樣。
三. 對TEXT/BLOB型字段存取優(yōu)化
mysql的 io 以page為單位,因此不必要的數(shù)據(jù)(大字段)也會隨著需要操作的數(shù)據(jù)一同被讀取到內(nèi)存中來,這樣帶來的問題由于大字段會占用較大的內(nèi)存(相比其他小字段),使得內(nèi)存利用率較差,造成更多的隨機讀取。從上面的分析來看,我們已經(jīng)看到性能的瓶頸在于由于大字段存放在數(shù)據(jù)頁中,造成了內(nèi)存利用較差,帶來過多的隨機讀,那怎么來優(yōu)化掉這個大字段的影響
3.1 壓縮&合并
a、innodb提供了barracuda文件格式,將大字段完全存放在溢出段中,數(shù)據(jù)段中只存放20個字節(jié),這樣就大大的減小了數(shù)據(jù)頁的空間占用,使得一個數(shù)據(jù)頁能夠存放更多的數(shù)據(jù)行,也就提高了內(nèi)存的命中率(對于本實例,大多數(shù)行的長度并沒有超過8k,所以優(yōu)化的幅度有限);如果對溢出段的數(shù)據(jù)進行壓縮,那么在空間使用上也會大大的降低,具體的的壓縮比率可以設(shè)置key_blok_size來實現(xiàn)。
b、可以把大字段用COMPRESS()壓縮后再存為BLOB,或者在發(fā)送到MySQL前在應(yīng)用程序中進行壓縮
c、一張表有多個類blob字段,把它們組合起來如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>
,再壓縮存儲
d、如果預(yù)期長度范圍varchar就滿足,就避免使用TEXT
3.2 拆分
將主表拆分為一對一的兩個關(guān)聯(lián)表,將大字段單獨放到另外一張表后,單行長度變的非常的小,page的行密度相比原來的表大很多,這樣就能夠緩存足夠多的行,buffer pool的命中率就會提高,應(yīng)用程序需要額外維護的是一張大字段的子表,還可以通過覆蓋索引來優(yōu)化,將索引和原表結(jié)構(gòu)分開,從訪問密度較小的數(shù)據(jù)頁改為訪問密度很大的索引頁,隨機io轉(zhuǎn)換為順序io
總結(jié):還是讓單個page能夠存放足夠多的行,不斷的提示內(nèi)存的命中率,從數(shù)據(jù)庫底層存儲的原理出發(fā),能夠更深刻的優(yōu)化數(shù)據(jù)庫
綜上,如果在實際業(yè)務(wù)中,確實需要在InnoDB表中存儲BLOB、TEXT、長VARCHAR列時,有下面幾點建議:
盡可能將所有數(shù)據(jù)序列化、壓縮之后,存儲在同一個列里,避免發(fā)生多次off-page
如果預(yù)期長度范圍varchar就滿足,就避免使用TEXT
如果無法將所有列整合到一個列,可以退而求其次,根據(jù)每個列最大長度進行排列組合后拆分成多個子表,盡量是的每個子表的總行長度小于8KB,減少發(fā)生off-page的頻率
參考文檔:
http://www.hudong.com/wiki/%E3%80%8AMySQL%E6%8A%80%E6%9C%AF%E5%86%85%E5%B9%95%EF%BC%9AInnoDB%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E3%80%8B
http://www.mysqlperformanceblog.com/2008/01/11/mysql-blob-compression-performance-benefits/
http://www.mysqlperformanceblog.com/2012/05/30/data-compression-in-innodb-for-text-and-blob-fields/
http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html
http://blog.opskumu.com/mysql-blob.html
http://hidba.org/?p=551
http://blog.chinaunix.net/uid-24485075-id-3523032.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html
作者:踏雪無痕
本文版權(quán)歸作者和博客園共有,如需轉(zhuǎn)載,請聯(lián)系 pingzhao1990#163.com
如果您覺得本文對您的學(xué)習(xí)有所幫助,可通過支付寶(左) 或者 微信(右) 來打賞博主,增加博主的寫作動力
分類: MYSQL
http://www.cnblogs.com/chenpingzhao/p/6719258.html