快捷導(dǎo)航

詳解MySQL的數(shù)據(jù)行和行溢出機制

更新時間：2020年11月25日 15:29:01 作者：賜我白日夢

在前面的文章中，白日夢曾不止一次的提及到：InnoDB從磁盤中讀取數(shù)據(jù)的最小單位是數(shù)據(jù)頁。而你想得到的id = xxx的數(shù)據(jù)，就是這個數(shù)據(jù)頁眾多行中的一行。這篇文章我們就一起來看一下數(shù)據(jù)行設(shè)計的多么巧妙。

一、行有哪些格式？

你可以像下面這樣看一下你的MySQL行格式設(shè)置。

其實MySQL的數(shù)據(jù)行有兩種格式，一種就是圖中的 Compact格式，還有一種是Redundant格式。

Compact是一種緊湊的行格式，設(shè)計的初衷就是為了讓一個數(shù)據(jù)頁中可以存放更多的數(shù)據(jù)行。

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

官網(wǎng)介紹：Compact能比Redundant格式節(jié)約20%的存儲。

Compact從MySQL5.0引入，MySQL5.1之后，行格式默認設(shè)置成 Compact 。所以本文描述的也是Compact格式。

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

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

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

每部分包含的數(shù)據(jù)可能要比我上面標注的1、2、3還要多。

為了給大家更直觀的感受和理解我只是挑了一部分展示給大家看。

三、MySQL單行能存多大體量的數(shù)據(jù)？

在MySQL的設(shè)定中，單行數(shù)據(jù)最大能存儲65535byte的數(shù)據(jù)（注意是byte，而不是字符）

但是當你像下面這樣創(chuàng)建一張數(shù)據(jù)表時卻發(fā)生了錯誤：

MySQL不允許創(chuàng)建一個長度為65535byte的列，因為數(shù)據(jù)頁中每一行中都有我們上圖提到的隱藏列。

所以將varchar的長度降低到65532byte即可成功創(chuàng)建該表

注意這里的65535指的是字節(jié)，而不是字符。

所以如果你將charset換成utf8這種編碼格式，那varchar(N)中的N其實指的N個字符，而不是N個byte。所以如果你像下面這樣創(chuàng)建表就會報錯。

假如encode=utf8時三個byte表示一個字符。那么65535 / 3 = 21845個字符。

四、Compact格式是如何做到緊湊的？

MySQL每次進行隨機的IO讀

默認情況下，數(shù)據(jù)頁的大小為16KB。數(shù)據(jù)頁中存儲著數(shù)行。

那就意味著一個數(shù)據(jù)頁中能存儲越多的數(shù)據(jù)行，MySQL整體的進行的IO次數(shù)就越少？性能就越快？

Compact格式的實現(xiàn)思路是：當列的類型為VARCHAR、 VARBINARY、 BLOB、TEXT時，該列超過768byte的數(shù)據(jù)放到其他數(shù)據(jù)頁中去。

如下圖：

看到這里來龍去脈是不是很清晰了呢？

MySQL這樣做，有效的防止了單個varchar列或者Text列太大導(dǎo)致單個數(shù)據(jù)頁中存放的行記錄過少而讓IO飆升的窘境且占內(nèi)存的。

五、什么是行溢出？

那什么是行溢出呢？

如果數(shù)據(jù)頁默認大小為16KB，換算成byte： 16*1024 = 16384 byte

那你有沒有發(fā)現(xiàn)，單頁能存儲的16384byte和單行最大能存儲的 65535byte 差了好幾倍呢？

也就是說，假如你要存儲的數(shù)據(jù)行很大超過了65532byte那么你是寫入不進去的。假如你要存儲的單行數(shù)據(jù)小于65535byte但是大于16384byte，這時你可以成功insert，但是一個數(shù)據(jù)頁又存儲不了你插入的數(shù)據(jù)。這時肯定會行溢出！

其實在MySQL的設(shè)定中，發(fā)生行溢出并不是達到16384byte邊緣才會發(fā)生。

對于varchar、text等類型的行。當這種列存儲的長度達到幾百byte時就會發(fā)生行溢。

六、行如何溢出？

還是看這張圖：