對MySQL的資料行和行溢出機制的理解

mysql影片教學欄位介紹資料行和行溢位機制。

相關免費學習推薦：mysql影片教學

一、行有哪些格式？

你可以像下面這樣看一下你的MySQL行格式設定。

其實MySQL的資料行有兩種格式，一種就是圖中的 Compact格式，還有一種是Redundant格式。

Compact是一種緊湊的行格式，設計的初衷就是為了讓一個資料頁中可以存放更多的資料行。

你品一品，讓一個數據頁中可以存放更多的數據行是一個多麼激動人心的事，MySQL以數據頁為單位從磁碟中讀數據，如果能做到讓一個數據頁中有更多的行，那豈不是使用的空間變少了，且整體的效率直線飆升？

官網介紹：Compact能比Redundant格式節省20%的儲存。

Compact從MySQL5.0引入，MySQL5.1之後，行格式預設為 Compact 。所以本文描述的也是Compact格式。

二、緊湊的行格式長啥樣？

你肯定曉得表中有的列允許為null，有的列是變長的varchar型別。

那Compact行格式是如何組織描述這些資訊的呢？如下圖：

每部分包含的資料可能比我上面標註的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的資料行和行溢出機制的理解的詳細內容。更多資訊請關注PHP中文網其他相關文章！