MySQLデータ行と行オーバーフローのメカニズムの詳細な説明

1. 行の形式は何ですか?

MySQL の行形式の設定は次のように表示されます。

実際、MySQL データ行には 2 つの形式があり、1 つは図に示す Compact 形式、もう 1 つは Redundant 形式です。

Compact はコンパクトな行形式であり、1 つのデータページにより多くのデータ行を格納するように設計されています。

考えてみてください。データページにさらに多くのデータ行を保存できるというのは、とても素晴らしいことです。MySQL は、データページでディスクからデータを読み取ります。データページにさらに多くの行を作成できれば、使用するスペースが少なくなり、全体的な効率が向上するのではないでしょうか。

公式サイト紹介: Compact は Redundant 形式と比較して 20% のストレージを節約できます。

Compact は MySQL 5.0 で導入されました。MySQL 5.1 以降では、行形式はデフォルトで Compact に設定されています。そこでこの記事では、コンパクト形式についても説明します。

2. コンパクト行形式はどのようになりますか?

テーブル内の一部の列では null が許可され、一部の列は可変長の varchar 型であることを知っておく必要があります。

コンパクト行形式ではこの情報をどのように整理し、説明するのでしょうか?以下のように表示されます。

各部分には、上でマークした 1、2、3 より多くのデータが含まれる場合があります。
より直感的に感じて理解していただくために、一部だけを抜粋してご紹介しました。

3. MySQL の 1 行に保存できるデータの量はどれくらいですか?

MySQLの設定では、1行のデータには最大65535バイトのデータが保存できます（文字ではなくバイトであることに注意してください）。

しかし、次のようなデータテーブルを作成するとエラーが発生します。

MySQL では、データページの各行に上の図に示す非表示の列があるため、長さが 65535 バイトの列を作成することはできません。

したがって、テーブルを正常に作成するには、varchar の長さを 65532 バイトに減らしてください。

ここでの 65535 は文字数ではなくバイト数を指すことに注意してください。

したがって、文字セットを utf8 に変更すると、varchar(N) の N は実際には N バイトではなく N 文字を参照します。したがって、以下のようなテーブルを作成するとエラーが発生します。

encode=utf8 の場合、3 バイトで 1 文字を表します。すると、65535 / 3 = 21845 文字になります。

4. コンパクト形式はなぜコンパクトなのですか?

MySQLは毎回ランダムIO読み取りを実行します

デフォルトでは、データページのサイズは 16 KB です。データページには複数の行が格納されます。

つまり、データページに保存できるデータ行が増えるほど、MySQL が全体的に実行する IO 回数が少なくなるということですか?パフォーマンスは速くなりますか?

コンパクト形式の実装の考え方は、列の型が VARCHAR、VARBINARY、BLOB、または TEXT の場合、768 バイトを超える列のデータは他のデータページに配置されます。

以下のように表示されます。

物語の全体像は理解できましたか?

これにより、MySQL は、単一の varchar 列または text 列が大きすぎる状況、つまり単一のデータページに格納される行レコードが少なすぎる状況、IO が急増してメモリを占有する状況を効果的に防止します。

5. 行オーバーフローとは何ですか?

では、行オーバーフローとは何でしょうか?

デフォルトのデータページサイズが16KBの場合、バイトに変換すると16*1024 = 16384バイトになります。

1 ページに格納できる 16384 バイトは、1 行に格納できる最大 65535 バイトの数倍であることに気付きましたか?

つまり、保存したいデータ行が 65532 バイトより大きい場合は、書き込むことができません。保存するデータの単一行が 65535 バイト未満で 16384 バイトより大きい場合、挿入は正常に行えますが、挿入したデータを 1 つのデータページに保存することはできません。この時点で間違いなくオーバーフローが発生します!

実際、MySQL の設定では、サイズが 16384 バイトに達したときのみ行オーバーフローが発生しません。

varchar、text などのタイプの行の場合。このような列ストレージの長さが数百バイトに達すると、行オーバーフローが発生します。

6. 行がオーバーフローするのはなぜですか?

または、この写真を見てください:

MySQL の設定では、varchar 列の長さが 768 バイトに達すると、列の最初の 768 バイトがプレフィックスとして行に格納され、超過データはオーバーフローページに格納され、その後、オフセットポインターを介して 2 つがリンクされます。これが行オーバーフローのメカニズムです。

7. 質問について考える

あなたはこの質問について考えたことがあるでしょうか:

まず、MySQL は B+Tree のクラスター化インデックスを使用することを知っておく必要があります。この B+Tree では、非リーフノードにはインデックスのみが格納され、データは格納されませんが、リーフノードには実際のデータが格納されます。同時に、リーフノードはデータページを指します。

では、1 行を保存できない場合は、2 つのデータページに保存してみてはいかがでしょうか。下の写真の通りです。

シングルノードストレージでは、本社の保存に複数のノードを使用します。たぶん私のB+Teeはもっと大きくて背が高くなるでしょう（これは実は間違った考えです）

このエラーの説明に対応する脳マップは次のとおりです。

MySQL がこれを行わない理由は次のとおりです。

MySQL が 1 つのデータページに複数のデータ行を保存する場合、少なくとも 2 行のデータを保存する必要があります。そうしないと、B+Tree の意味が失われます。 B+ツリーも非効率的なリンクリストに退化します。

この青い文の意味は理解できます。「各データページには少なくとも 2 行のデータが保存される必要がある」とは、データページには 1 行しか保存できないという意味ではありません。文字通り、1 行のデータを書き込むだけで、その後は食事に行ったり、他の何かをしたりできます。このデータページには、常に 1 行のデータのみが含まれます。

つまり、このデータページにデータ行を書き込むと、そのデータが非常に大きく、データページの制限に達した場合でも、行オーバーフローメカニズムによってオーバーフローされます。これにより、次のデータがこのデータページに書き込まれることが保証されます。

正しいマインドマップは次のとおりです。