【mysql】關於InnoDB存儲引擎 text blob 大欄位的存儲和優化

来源:http://www.cnblogs.com/chenpingzhao/archive/2017/04/16/6719258.html
-Advertisement-
Play Games

最近在資料庫優化的時候,看到一些表在設計上使用了text或者blob的欄位,單表的存儲空間已經達到了近100G,這種情況再去改變和優化就非常難了 一、簡介 為了清楚大欄位對性能的影響,我們必須要知道innodb存儲引擎的處理方式: 1、一些知識點 1.1 在InnoDB 1.0.x版本之前,Inno ...


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

一、簡介

為了清楚大欄位對性能的影響,我們必須要知道innodb存儲引擎的處理方式:

1、一些知識點 

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

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

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

 

1.2 MySQL 5.1 中的 innodb_plugin 引入了新的文件格式Barracuda (梭子魚),該文件格式擁有新的兩種行格式:compresseddynamic,兩種格式對blob欄位採用完全溢出的方式,數據頁中只存放20位元組,其餘的都存放在溢出段中,因此,強烈不建議使用BLOB、TEXT、超過255長度的VARCHAR列類型

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

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會進行收縮,釋放空閑空間

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

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

1.7 mysql在操作數據的時候,以page為單位,不管是更新,插入,刪除一行數據,都需要將那行數據所在的page讀到記憶體中,然後在進行操作,這樣就存在一個命中率的問題,如果一個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 列表示當前所使用的行記錄結構類型

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

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

二、對TEXT/BLOB這類大欄位類型的影響

2.1 compact

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

2.2 dynamic

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

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

另外,由於ROW_FORMAT=DYNAMIC 和 ROW_FORMAT=COMPRESSED 是從 ROW_FORMAT=COMPACT 變化來的,所以他們處理 CHAR類型存儲的方式和 COMPACT 一樣。

三. 對TEXT/BLOB型欄位存取優化

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

3.1 壓縮&合併

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

b、可以把大欄位用COMPRESS()壓縮後再存為BLOB,或者在發送到MySQL前在應用程式中進行壓縮

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

d、如果預期長度範圍varchar就滿足,就避免使用TEXT

3.2 拆分

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

 

總結:還是讓單個page能夠存放足夠多的行,不斷的提示記憶體的命中率,從資料庫底層存儲的原理出發,能夠更深刻的優化資料庫

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

  • 儘可能將所有數據序列化、壓縮之後,存儲在同一個列里,避免發生多次off-page

  • 如果預期長度範圍varchar就滿足,就避免使用TEXT

  • 如果無法將所有列整合到一個列,可以退而求其次,根據每個列最大長度進行排列組合後拆分成多個子表,儘量是的每個子表的總行長度小於8KB,減少發生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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Play 2D games on Pixel running Android Nougat (N7.1.2) with Daydream View VR headset. ...
  • 1.下載下邊的三個jar包,對dex2jar和jd-gui進行解壓 2.將需要反編譯的apk尾碼名改為.rar,然後進行解壓 3.將解壓後生成的classes.dex加入第一步解壓後的dex2jar的文件夾內 4.windows+R > cmd 5.執行上邊之後,會自動生成; 6.打開解壓的,選中第 ...
  • 本文摘自文章: [20+ Awesome Open-Source Android Apps To Boost Your Development Skills](https://blog.aritraroy.in/20-awesome-open-source-android-apps-to-boost... ...
  • IOS-- UIView中的坐標轉換 // 將像素point由point所在視圖轉換到目標視圖view中,返回在目標視圖view中的像素值 - (CGPoint)convertPoint:(CGPoint)point toView:(UIView *)view; // 將像素point從view中轉 ...
  • CUBE:CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。 ROLLUP:ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合。 GROUPING:當行由 CUBE 或 ROLLUP 運算符添加時,該函數將導致附加列的輸出值為 1;當行不由 CUBE 或 ROLLUP 運算符添加時 ...
  • 說到視窗框架就不得不提起開窗函數。 開窗函數支持分區、排序和框架三種元素,其語法格式如下: 視窗分區: 就是將視窗指定列具有相同值的那些行進行分區,分區與分組比較類似,但是分組指定後對於整個SELECT語句只能按照這個分組,不過 分區可以在一條語句中指定不同的分區。 1 <PARTITION BY ...
  • 先說點題外話,因為後面我會用到這個函數。 前兩天自定義了一個 sql 的字元串分割函數(Split),不過後來發現有點問題,例如: 我之前只處理了截取的最後一個為空的字元串,所以會出現以上的結果,現在我做了一些修改。代碼如下: 紅色部分的代碼為添加或修改的部分,下麵再看一下效果。 之前自定義 Spl ...
  • 導讀: 隨著大數據概念的火熱,啤酒與尿布的故事廣為人知。我們如何發現買啤酒的人往往也會買尿布這一規律?數據挖掘中的用於挖掘頻繁項集和關聯規則的Apriori演算法可以告訴我們。本文首先對Apriori演算法進行簡介,而後進一步介紹相關的基本概念,之後詳細的介紹Apriori演算法的具體策略和步驟,最後給出 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...