乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

關于MySQL InnoDB存儲引擎 text blob 大字段的存儲和優化MYSQL教程

作者：VEPHP   時間 2017-09-27

《關于MySQL InnoDB存儲引擎 text blob 大字段的存儲和優化MYSQL教程》要點：
本文介紹了關于MySQL InnoDB存儲引擎 text blob 大字段的存儲和優化MYSQL教程，希望對您有用。如果有疑問，可以聯系我們。

導讀：最近在數據庫優化的時候,看到一些表在設計上使用了text或者blob的字段,單表的存儲空間已經達到了近100G,這種情況再去改變和優化就非常難了
最近在數據庫優化的時候,看到一些表在設計上使用了text或者blob的字段,單表的存儲空間已經達到了近100G,這種情況再去改變和優化就非常難了

一、簡介

為了清楚大字段對性能的影響,我們必須要知道innodb存儲引擎的處理方式：MYSQL學習

1、一些知識點?

1.1 在InnoDB 1.0.x版本之前,InnoDB 存儲引擎提供了?Compact?和?Redundant(Redundant 格式是為兼容之前版本而保留的)?兩種格式來存放行記錄數據,compact 和 redundant 合稱為Antelope (羚羊)MYSQL學習

對于blob,text,varchar(5120)這樣的大字段,innodb只會存放前768字節在數據頁中,而剩余的數據則會存儲在溢出段中(發生溢出情況的時候適用),最大768字節的作用是便于創建前綴索引/prefix index,其余更多的內容存儲在額外的page里,哪怕只是多了一個字節.因此,所有列長度越短越好MYSQL學習

  • 大字段在InnoDB里可能浪費大量空間.例如,若存儲字段值只是比行的要求多了一個字節,也會使用整個頁面來存儲剩下的字節,浪費了頁面的大部分空間.如果有一個值只是稍微超過了32個頁的大小,實際上就需要使用96個頁面
  • 擴展存儲禁用了自適應哈希,因為需要完整的比較列的整個長度,才能發現是不是正確的數據(哈希幫助InnoDB非常快速的找到“猜測的位置”,但是必須檢查“猜測的位置”是不是正確).因為自適應哈希是完全的內存結構,并且直接指向Buffer Pool中訪問“最”頻繁的頁面,但對于擴展存儲空間卻無法使用Adaptive Hash

關于MySQL InnoDB存儲引擎 text blob 大字段的存儲和優化MYSQL學習

?MYSQL學習

1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式：Barracuda (梭子魚),該文件格式擁有新的兩種行格式：compressed和dynamic,兩種格式對blob字段采用完全溢出的方式,數據頁中只存放20字節,其余的都存放在溢出段中,因此,強烈不建議使用BLOB、TEXT、超過255長度的VARCHAR列類型；MYSQL學習

關于MySQL InnoDB存儲引擎 text blob 大字段的存儲和優化MYSQL學習

1.3?innodb的page大小默認為16kb,innodb存儲引擎表為索引組織表,樹底層的葉子節點為一雙向鏈表,因此每個頁中至少應該有兩行記錄,這就決定了innodb在存儲一行數據的時候不能夠超過8k,但事實上應該更小,因為還有一些InnoDB內部數據結構要存儲,5.6版本以后,新增選項 innodb_page_size 可以修改,在5.6以前的版本,只能修改源碼重新編譯,但并不推薦修改這個配置MYSQL學習

1.4?InnoDB的data page在有新數據寫入時,會預留1/16的空間,預留出來的空間可用于后續的新紀錄寫入,減少頻繁的新增data page的開銷,受限于InnoDB存儲方式,數據如果是順序寫入的話,最理想的情況下,data page的填充率是15/16,但一般沒辦法保證完全的順序寫入,因此data page的填充率一般是1/2到15/16.因此每個InnoDB表都最好要有一個自增列作為主鍵,使得新紀錄寫入盡可能是順序的；當data page填充率不足1/2時,InnoDB會進行收縮,釋放空閑空間MYSQL學習

1.5?COMPACT行格式相比REDUNDANT,大概能節省20%的存儲空間,COMPRESSED相比COMPACT大概能節省50%的存儲空間,但會導致TPS下降了90%.因此強烈不推薦使用COMPRESSED行格式MYSQL學習

1.6 使用了blob數據類型,是不是一定就會存放在溢出段中?通常我們認為blob這類的大對象的存儲會把數據存放在數據頁之外,其實不然,關鍵點還是要看一個page中到底能否存放兩行數據,blob可以完全存放在數據頁中(單行長度沒有超過8096字節),而varchar類型的也有可能存放在溢出頁中(單行長度超過8096字節,前768字節存放在數據頁中)MYSQL學習

1.7 mysql在操作數據的時候,以page為單位,不管是更新,插入,刪除一行數據,都需要將那行數據所在的page讀到內存中,然后在進行操作,這樣就存在一個命中率的問題,如果一個page中能夠相對的存放足夠多的行,那么命中率就會相對高一些,性能就會有提升MYSQL學習

1.8?在off-page中存儲的BLOB、TEXT或者長VARCHAR列的page是獨享的,不能共享.因此強烈不建議在一個表中使用多個長列MYSQL學習

1.9 MySQL 5.6 中默認還是 Compact 行格式,也是目前使用最多的一種 ROW FORMAT.用戶可以通過命令?SHOW TABLE STATUS LIKE'table_name'?來查看當前表使用的行格式,其中?row_format?列表示當前所使用的行記錄結構類型
?MYSQL學習

  1. mysql>desc?db_page;?
  2. +-----------------+----------------+----------------+---------------+-------------------+-----------------+?
  3. |?Field???????????|?Type???????????|?Null???????????|?Key???????????|?Default???????????|?Extra???????????|?
  4. +-----------------+----------------+----------------+---------------+-------------------+-----------------+?
  5. |?id??????????????|?int(11)????????|?NO?????????????|?PRI???????????|???????????????????|?auto_increment??|?
  6. |?title???????????|?varchar(100)???|?NO?????????????|???????????????|???????????????????|?????????????????|?
  7. |?name????????????|?varchar(100)???|?YES????????????|???????????????|???????????????????|?????????????????|?
  8. |?content?????????|?text???????????|?YES????????????|???????????????|???????????????????|?????????????????|?
  9. +-----------------+----------------+----------------+---------------+-------------------+-----------------+?
  10. mysql>show?variables?like?"innodb_file_format";?
  11. +-------------------------+-----------------+?
  12. |?Variable_name???????????|?Value???????????|?
  13. +-------------------------+-----------------+?
  14. |?innodb_file_format??????|?Barracuda???????|?
  15. +-------------------------+-----------------+?
  16. mysql>show?table?status?like?"db_page"?\G?
  17. ***************************?1.?row?***************************?
  18. ???????????Name:?db_page?
  19. ?????????Engine:?InnoDB?
  20. ????????Version:?10?
  21. ?????Row_format:?Compact?
  22. ???????????Rows:?2?
  23. ?Avg_row_length:?8192?
  24. ????Data_length:?16384?
  25. Max_data_length:?0?
  26. ???Index_length:?0?
  27. ??????Data_free:?0?
  28. ?Auto_increment:?3?
  29. ????Create_time:?2017-03-07?13:30:19?
  30. ????Update_time:?
  31. ?????Check_time:?
  32. ??????Collation:?utf8_general_ci?
  33. ???????Checksum:?
  34. ?Create_options:?
  35. ????????Comment:?
  36. ???Block_format:?Original?

在 msyql 5.7.9 及以后版本,默認行格式由innodb_default_row_format變量決定,它的默認值是DYNAMIC,也可以在 create table 的時候指定ROW_FORMAT=DYNAMIC.MYSQL學習

注意,如果要修改現有表的行模式為compressed或dynamic,必須先將文件格式設置成Barracuda：set global innodb_file_format=Barracuda;,再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效,否則修改無效卻無提示MYSQL學習

二、對TEXT/BLOB這類大字段類型的影響

2.1 compact

變長大字段類型包括blob,text,varchar,其中varchar列值長度大于某數N時也會存溢出頁,在latin1字符集下N值可以這樣計算：innodb的塊大小默認為16kb,由于innodb存儲引擎表為索引組織表,樹底層的葉子節點為一雙向鏈表,因此每個頁中至少應該有兩行記錄,這就決定了innodb在存儲一行數據的時候不能夠超過8k,減去其它列值所占字節數,約等于N.對于InnoDB,內存是極為珍貴的,如果把768字節長度的blob都放在數據頁,雖然可以節省部分IO,但是能緩存行數就變少,也就是能緩存的索引值變少了,降低了索引效率MYSQL學習

2.2 dynamic

dynamic行格式,列存儲是否放到off-page頁,主要取決于行大小,它會把行中最長的那一列放到off-page,直到數據頁能存放下兩行.TEXT/BLOB列 <=40 bytes 時總是存放于數據頁.這種方式可以避免compact那樣把太多的大列值放到 B-tree Node,因為dynamic格式認為,只要大列值有部分數據放在off-page,那把整個值放入都放入off-page更有效.MYSQL學習

compressed?物理結構上與dynamic類似,但是對表的數據行使用zlib算法進行了壓縮存儲.在long blob列類型比較多的情況下用,可以降低off-page的使用,減少存儲空間(一般40%左右),但要求更高的CPU,buffer pool里面可能會同時存儲數據的壓縮版和非壓縮版,所以也多占用部分內存.這里?MySQL 5.6 Manual innodb-compression-internals?講的十分清楚.MYSQL學習

另外,由于ROW_FORMAT=DYNAMIC?和?ROW_FORMAT=COMPRESSED?是從?ROW_FORMAT=COMPACT?變化來的,所以他們處理?CHAR類型存儲的方式和 COMPACT 一樣.MYSQL學習

三. 對TEXT/BLOB型字段存取優化

mysql的 io 以page為單位,因此不必要的數據(大字段)也會隨著需要操作的數據一同被讀取到內存中來,這樣帶來的問題由于大字段會占用較大的內存(相比其他小字段),使得內存利用率較差,造成更多的隨機讀取.從上面的分析來看,我們已經看到性能的瓶頸在于由于大字段存放在數據頁中,造成了內存利用較差,帶來過多的隨機讀,那怎么來優化掉這個大字段的影響MYSQL學習

3.1 壓縮&合并

a、innodb提供了barracuda文件格式,將大字段完全存放在溢出段中,數據段中只存放20個字節,這樣就大大的減小了數據頁的空間占用,使得一個數據頁能夠存放更多的數據行,也就提高了內存的命中率(對于本實例,大多數行的長度并沒有超過8k,所以優化的幅度有限)；如果對溢出段的數據進行壓縮,那么在空間使用上也會大大的降低,具體的的壓縮比率可以設置key_blok_size來實現.MYSQL學習

b、可以把大字段用COMPRESS()壓縮后再存為BLOB,或者在發送到MySQL前在應用程序中進行壓縮MYSQL學習

c、一張表有多個類blob字段,把它們組合起來如<TEXT><f_big_col1>long..</f_big_col1> <f_content>long..</f_content></TEXT>,再壓縮存儲MYSQL學習

d、如果預期長度范圍varchar就滿足,就避免使用TEXTMYSQL學習

3.2?拆分

將主表拆分為一對一的兩個關聯表,將大字段單獨放到另外一張表后,單行長度變的非常的小,page的行密度相比原來的表大很多,這樣就能夠緩存足夠多的行,buffer pool的命中率就會提高,應用程序需要額外維護的是一張大字段的子表,還可以通過覆蓋索引來優化,將索引和原表結構分開,從訪問密度較小的數據頁改為訪問密度很大的索引頁,隨機io轉換為順序ioMYSQL學習

?MYSQL學習

總結：還是讓單個page能夠存放足夠多的行,不斷的提示內存的命中率,從數據庫底層存儲的原理出發,能夠更深刻的優化數據庫MYSQL學習

綜上,如果在實際業務中,確實需要在InnoDB表中存儲BLOB、TEXT、長VARCHAR列時,有下面幾點建議：MYSQL學習

  • 盡可能將所有數據序列化、壓縮之后,存儲在同一個列里,避免發生多次off-pageMYSQL學習

  • 如果預期長度范圍varchar就滿足,就避免使用TEXTMYSQL學習

  • 如果無法將所有列整合到一個列,可以退而求其次,根據每個列最大長度進行排列組合后拆分成多個子表,盡量是的每個子表的總行長度小于8KB,減少發生off-page的頻率MYSQL學習

?MYSQL學習

參考文檔：

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%8BMYSQL學習

http://www.mysqlperformanceblog.com/2008/01/11/mysql-blob-compression-performance-benefits/MYSQL學習

http://www.mysqlperformanceblog.com/2012/05/30/data-compression-in-innodb-for-text-and-blob-fields/MYSQL學習

http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.htmlMYSQL學習

http://blog.opskumu.com/mysql-blob.htmlMYSQL學習

http://hidba.org/?p=551MYSQL學習

http://blog.chinaunix.net/uid-24485075-id-3523032.htmlMYSQL學習

http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.htmlMYSQL學習

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/5596.html


    標簽：