瞭解 MySQL的資料行、行溢位機制嗎?
一、行 有哪些格式?#
你可以像下面這樣看一下你的MySQL行格式設定。
其實MySQL的資料行有兩種格式,一種就是圖中的 Compact格式,還有一種是Redundant格式。
Compact是一種緊湊的行格式,設計的初衷就是為了讓一個資料頁中可以存放更多的資料行。
你品一品,讓一個資料頁中可以存放更多的資料行是一個多麼激動人心的事,MySQL以資料頁為單位從磁碟中讀資料,如果能做到讓一個資料頁中有更多的行,那豈不是使用的空間變少了,且整體的效率直線飆升?
官網介紹:Compact能比Redundant格式節約20%的儲存。
Compact從MySQL5.0引入,MySQL5.1之後,行格式預設設定成 Compact 。所以本文描述的也是Compact格式。
二、緊湊的行格式長啥樣?#
你肯定曉得表中有的列允許為null,有的列是變長的varchar型別。
那Compact行格式是如何組織描述這些資訊的呢?如下圖:
img
每部分包含的資料可能要比我上面標註的1、2、3還要多。
為了給大家更直觀的感受和理解我只是挑了一部分展示給大家看。
三、MySQL單行能存多大體量的資料?#
在MySQL的設定中,單行資料最大能儲存65535byte的資料(注意是byte,而不是字元)
但是當你像下面這樣建立一張資料表時卻發生了錯誤:
MySQL不允許建立一個長度為65535byte的列,因為資料頁中每一行中都有我們上圖提到的隱藏列。
所以將varchar的長度降低到65532byte即可成功建立該表
注意這裡的65535指的是位元組,而不是字元。
所以如果你將charset換成utf8這種編碼格式,那varchar(N)中的N其實指的N個字元,而不是N個byte。所以如果你像下面這樣建立表就會報錯。
假如encode=utf8時三個byte表示一個字元。那麼65535 / 3 = 21845個字元。
四、Compact格式是如何做到緊湊的?#
MySQL每次進行隨機的IO讀
預設情況下,資料頁的大小為16KB。資料頁中儲存著數行。
那就意味著一個資料頁中能儲存越多的資料行,MySQL整體的進行的IO次數就越少?效能就越快?
Compact格式的實現思路是:當列的型別為VARCHAR、 VARBINARY、 BLOB、TEXT時,該列超過768byte的資料放到其他資料頁中去。
如下圖:
看到這裡來龍去脈是不是很清晰了呢?
MySQL這樣做,有效的防止了單個varchar列或者Text列太大導致單個資料頁中存放的行記錄過少而讓IO飆升的窘境且佔記憶體的。
五、什麼是行溢位?#
那什麼是行溢位呢?
如果資料頁預設大小為16KB,換算成byte: 16*1024 = 16384 byte
那你有沒有發現,單頁能儲存的16384byte和單行最大能儲存的 65535byte 差了好幾倍呢?
也就是說,假如你要儲存的資料行很大超過了65532byte那麼你是寫入不進去的。假如你要儲存的單行資料小於65535byte但是大於16384byte,這時你可以成功insert,但是一個資料頁又儲存不了你插入的資料。這時肯定會行溢位!
其實在MySQL的設定中,發生行溢位並不是達到16384byte邊緣才會發生。
對於varchar、text等型別的行。當這種列儲存的長度達到幾百byte時就會發生行溢。
六、行 如何溢位?#
還是看這張圖:
在MySQL設定中,當varchar列長度達到768byte後,會將該列的前768byte當作當作prefix存放在行中,多出來的資料溢位存放到溢位頁中,然後通過一個偏移量指標將兩者關聯起來,這就是行溢位機制。
七、思考一個問題#
不知道你有沒有想過這樣一個問題:
首先你肯定知道,MySQL使用的是B+Tree的聚簇索引,在這棵B+Tree中非葉子節點是隻存索引不存資料,葉子節點中儲存著真實的資料。同時葉子結點指向資料頁。
那當單行存不下的時候,為啥不儲存在兩個資料頁中呢?就像下圖這樣~。
單個節點儲存下,我用多個節點存總行吧!說不定這樣我的B+Tee還能變大長高(這其實是錯誤的想法)
這個錯誤的描述對應的腦圖如下:
那MySQL不這樣做的原因如下:
MySQL想讓一個資料頁中能存放更多的資料行,至少也得要存放兩行資料。否則就失去了B+Tree的意義。B+Tree也退化成一個低效的連結串列。
你可以品一下這句藍色的話,他說的每個資料頁至少要存放兩行資料的意思不是說 資料頁不能只存一行。你確確實實可以只往裡面寫一行資料,然後去吃個飯,乾點別的。一直讓這個資料頁中只有一行資料。
這句話的意思是,當你往這個資料頁中寫入一行資料時,即使它很大將達到了資料頁的極限,但是通過行溢位機制。依然能保證你的下一條資料還能寫入到這個資料頁中。
正確的腦圖如下:
相關文章
- 【MYSQL】InnoDB行溢位資料說明MySql
- mysql的text/blob和行溢位MySql
- IT行業的5個熱門崗位,你瞭解嗎?行業
- 【MySQL經典案例分析】關於資料行溢位由淺至深的探討MySql
- 帶你瞭解JavaScript的執行機制—Event LoopJavaScriptOOP
- Java SPI機制,你瞭解過嗎?Java
- MySQL行鎖、表鎖、間隙鎖,你都瞭解嗎MySql
- 從一道執行題,瞭解Node中JS執行機制JS
- setTimeout 是到了xx ms 就執行嗎,瞭解瀏覽器的 Event-Loop 機制瀏覽器OOP
- 聊聊資料溢位的事
- 瞭解js執行機制——微任務與巨集任務JS
- 從一道執行題,瞭解瀏覽器中JS執行機制瀏覽器JS
- Android事件分發機制,你瞭解過嗎?Android事件
- Mysql鎖機制簡單瞭解一下MySql
- 程式設計師必須瞭解的知識點——你搞懂mysql索引機制了嗎?程式設計師MySql索引
- 大資料行業年薪幾十萬,這些大資料崗位薪資瞭解一下大資料行業
- Oracle資料庫並行機制Parallel ExecutionOracle資料庫並行Parallel
- 帶你快速瞭解Oracle資料庫提供的恢復機制Oracle資料庫
- 關於Python的執行原理你瞭解嗎?Python
- 你瞭解一條sql的執行順序嗎SQL
- 圖解JS執行機制圖解JS
- js執行機制詳解JS
- Handler 機制再瞭解
- 全面瞭解mysql鎖機制(InnoDB)與問題排查MySql
- Java程式碼執行記憶體溢位詳解及解決方案Java記憶體溢位
- 阿里大佬講解Java記憶體溢位示例(堆溢位、棧溢位)阿里Java記憶體溢位
- Tomcat執行時報記憶體溢位Tomcat記憶體溢位
- 使用metasploit進行棧溢位攻擊-5
- 解讀MySQL 8.0資料字典快取管理機制MySql快取
- flink同步MySQL資料的時候出現記憶體溢位MySql記憶體溢位
- 瞭解MySQl資料庫目錄MySql資料庫
- 如何判斷整數資料的溢位
- Mysql資料庫二進位制日誌的管理MySql資料庫
- Node的垃圾回收機制與記憶體溢位捕獲(上)記憶體溢位
- 【RTOS】FreeRTOS中的任務堆疊溢位檢測機制
- javascript執行機制之執行順序詳解JavaScript
- MySQL 中 update 修改資料與原資料相同會再次執行嗎?MySql
- 二進位制漏洞挖掘之整數溢位