インタビューの答え: 各 MySQL テーブルのデータ数は 2,000 万以下が最適ですよね?-見出し-php.cn

MySQL の各テーブルにはどれくらいのデータを保存できますか?実際には、テーブルごとにフィールドやフィールドが占める領域が異なり、最適なパフォーマンスで格納できるデータ量も異なるため、手動で計算する必要があります。

内容は次のとおりです

以下は私の友人のインタビュー記録です:

インタビュアー: 教えてくださいインターンでは何をしていたんですか？友人: インターンシップ中に、ユーザーの操作記録を保存する機能を構築しましたが、主に上流サービスから送られてくるユーザーの操作情報をMQから取得し、MySQLに保存してデータウェアハウスに提供する機能です。 . 同僚が使用します。友人: データ量が比較的多く、毎日 4,000 ～ 5,000 万件のエントリがあるため、それに対してサブテーブル操作も実行しました。 3 つのテーブルが毎日定期的に生成され、テーブル内の過剰なデータによってクエリ速度が低下することを防ぐために、データがモデル化されてこれら 3 つのテーブルにそれぞれ格納されます。

このステートメントには何の問題もないようですよね? 心配しないで、読み続けましょう:

インタビュアー: では、なぜそれを 3 つの表に分割するのでしょうか。 ? 2 つのテーブルは機能しません。テーブルが 4 つあれば機能しないでしょうか? 友人: 各 MySQL テーブルのデータは 2,000 万個を超えてはなりません。そうしないと、クエリ速度が低下し、パフォーマンスに影響します。 1 日あたりのデータは約 5,000 万個であるため、3 つのテーブルに分割する方が安全です。インタビュアー: 他に何かありますか? 友人: もうだめです...何してるの、痛いインタビュアー: それなら戻って通知を待ちます。

話し終えましたが、何か見えましたか?私の友人の答えに何か問題があると思いますか?

序文

多くの人は、各 MySQL テーブルのデータが 2,000 万個を超えないようにするのが最善だと言います。そうしないと、パフォーマンスの低下につながります。 Alibaba の Java 開発マニュアルには、単一テーブルの行数が 500 万を超える場合、または単一テーブルの容量が 2GB を超える場合にのみ、データベースとテーブルを分割することをお勧めすると記載されています。しかし、実際には、この 2,000 万または 500 万は単なる大まかな数字であり、すべてのシナリオに当てはまるわけではありません。テーブルデータが 2,000 万を超えない限り、盲目的に考えると、問題ありませんが、システムのパフォーマンスが大幅に低下する可能性があります。実際の状況では、各テーブルのフィールドとフィールドが占有する領域が異なるため、最適なパフォーマンスで格納できるデータの量も異なります。それでは、各テーブルの適切なデータ量を計算するにはどうすればよいでしょうか?心配しないで、ゆっくりと下を見てください。この記事は読者に適していますこの記事を読むには、特定の MySQL 基盤が必要です。 InnoDB と B-tree についてのある程度の理解、MySQL の学習経験が 1 年以上 (1 年くらい?)、「一般的に B-tree の高さを維持する方が良い」という理論的知識を知っている必要があるかもしれません。 InnoDB のツリーは 3 レベル以内にあります。」この記事では「InnoDBの高さ3のBツリーにはどのくらいのデータを格納できるのか？」というテーマを中心に説明します。さらに、この記事のデータの計算は比較的厳密です (少なくとも、インターネット上の関連ブログ投稿の 95% 以上よりも厳密です)。これらの詳細が気になり、現時点ではよくわからない場合は、読み続けてください。この記事を読むのにかかる時間は 10 ～ 20 分程度ですが、読みながらデータを確認すると 30 分ほどかかる場合があります。 #この記事のマインドマップ

##基礎知識の簡単な復習インタビューの答え: 各 MySQL テーブルのデータ数は 2,000 万以下が最適ですよね?

ご存知のとおり、MySQL における InnoDB のストレージ構造は B ツリーです。特徴は大まかに以下の通りなので、一緒に簡単におさらいしていきましょう！

注: 次の内容は本質です。読んだり理解できない学生は、まずこの記事を保存し、知識ベースを取得した後に戻って読むことをお勧めします。 ##。 ?? データテーブルは通常、1 つ以上のツリーのストレージに対応します。ツリーの数はインデックスの数に関連します。各インデックスには個別のツリーがあります。

主キーインデックスもクラスター化インデックスであり、非主キーインデックスは非クラスター化インデックスです。形式情報を除き、両方のインデックスの非リーフノードはインデックスデータのみを格納します
リーフノード間の違いは次のとおりです:
- クラスター化インデックスのリーフノードには、通常、このデータの すべてのフィールド情報が格納されます。したがって、id = 1 のテーブルから select * を実行すると、常にリーフノードに移動してデータを取得します。
- 非クラスター化インデックスのリーフノードには、このデータに対応する 主キーとインデックス列の情報が格納されます。たとえば、この非クラスター化インデックスがユーザー名で、テーブルの主キーが id の場合、非クラスター化インデックスのリーフノードにはユーザー名と ID が格納されますが、他のフィールドは格納されません。これは、まず非クラスター化インデックスから主キーの値を検索し、次に主キーインデックスに基づいてデータの内容をチェックすることと同じです。通常、(インデックスがカバーされていない限り) 2 回チェックする必要があります。 Back to the table とも呼ばれます。これは、データが保存されている実際のアドレスを指すポインターの保存に少し似ています。
B ツリーのクエリは上から下へ階層ごとにクエリされます。一般的に、B ツリーの高さは 3 階層以内に抑えるのがよいと考えられます。つまり、上の 2 つの層はインデックスであり、最後の層はデータを格納します。このようにして、テーブルを検索するときに必要なディスク IO は 3 回だけです (ルートノードがメモリ内に常駐するため、実際には 1 回少なくなります)。、保存できるデータ量も非常に印象的です。データ量が多すぎてBの数が4レベルになると、各クエリに4回のディスクIOが必要となり、パフォーマンスが低下します。 だからこそ、InnoDB の 3 層 B ツリーが保存できるデータの最大数を計算します。
各 MySQL ノードのデフォルトサイズは 16 KB です。つまり、各ノードは最大 16 KB のデータを保存でき、そのデータは変更可能で、最大 64 KB で、最小 4KB。拡張: 特定の行のデータが特に大きく、ノードのサイズを超えた場合はどうなりますか? MySQL5.7 ドキュメントでは次のように説明されています:
- 4KB、8KB、16KB、および 32KB 設定の場合、最大行長はデータベースページの半分よりわずかに小さくなります。たとえば、デフォルトの 16KB ページサイズの場合、最大行長は 8KB よりわずかに小さくなり、デフォルトの 32KB ページサイズの場合、最大行長は 16KB よりわずかに小さくなります。
- 64KB ページの場合、最大行長は 16KB よりわずかに小さくなります。
- 行が最大行長を超える場合、行が最大行長制限に達するまで、可変長列は外部ページに格納されます。 つまり、この行のデータ長を減らすために、可変長の varchar と text が外部ページに格納されます。

ドキュメントアドレス: MySQL :: MySQL 5.7 リファレンスマニュアル :: 14.12.2 ファイルスペース管理

MySQL のクエリ速度は、主にディスクの読み取りおよび書き込み速度に依存します。これは、MySQL が一度に 1 つのノードのみをメモリに読み取り、データを通じて次のターゲットを見つけるためです。ノードの位置を読み取り、必要なデータがクエリされるかデータが存在しないまで、次のノードのデータを読み取ります。各ノードのデータをクエリする必要はないのではないかと疑問に思う人もいるでしょう。ここで所要時間が計算されないのはなぜですか? これは、ノードデータ全体を読み取った後、メモリに保存されるためです。メモリ内のノードデータのクエリには、実際には非常に短い時間がかかります。MySQL クエリメソッドと組み合わせると、時間の複雑さはほぼゼロになります。 $##OD (l o # について ##g 2_{N}) O(log_2N)$

MySQL InnoDB ノードストレージのコンテンツ

Innodb の B ツリーでは、私たちがよく参照するノードは ページ (ページ) と呼ばれます。 )、各ページにはユーザーデータが保存され、すべてのページがまとめて B ツリーを形成します (もちろん、実際にはさらに複雑になりますが、保存できるデータの数を計算する必要があるだけなので、当面はこうなるかもしれません、わかりますか？）

Page は、InnoDB ストレージエンジンがデータベースを管理するために使用する最小のディスクユニットです。各ノードは 16 KB であるとよく言われますが、実際には各ページのサイズを意味します。は16KBです。

この 16KB のスペースには、ページ形式情報と 行形式情報を保存する必要があります。行形式情報には、メタデータとユーザーデータも含まれています。したがって、計算するときは、これらすべてのデータを含める必要があります。

ページ形式

各ページの基本的な形式、つまり各ページに含まれる情報の概要表は次のとおりです。 :

未修正空きスペース。ユーザーレコードが追加されるときにここからスペースを取得します。 Unfixedページディレクトリは、ページ内のユーザーデータの位置情報を保存するために使用されます。 8 バイトファイルの終わりの情報。主にページの整合性を検証するために使用されます。

模式図:

インタビューの答え: 各 MySQL テーブルのデータ数は 2,000 万以下が最適ですよね?

公式サイトをずっと眺めていたのですが、ページ形式の内容が見つかりませんでした。。。。私が書いていないからなのか、目が見えないからなのかわかりませんが、もし見つけた人がいたら、コメント欄に投稿するのを手伝っていただけないでしょうか。

したがって、上記のページの形式の表の内容は、主にいくつかのブログからの学習と要約に基づいています。

さらに、新しいレコードが InnoDB クラスター化インデックスに挿入されると、InnoDB は今後のインデックスレコードの挿入と更新のためにページの 1/16 を空き領域に残そうとします。インデックスレコードが順序どおり (昇順または降順) に挿入される場合、結果のページには使用可能なスペースの約 15/16 が含まれます。レコードがランダムな順序で挿入される場合、ページ領域の約 1/2 ～ 15/16 が使用可能になります。参考ドキュメント: MySQL :: MySQL 5.7 リファレンスマニュアル :: 14.6.2.2 InnoDB インデックスの物理構造

ユーザーレコード と 空き領域 を除く占有メモリは $##38 56 26 8 = 128 38 56 26 8 = 128$ × $16 1024 \frac{−}{128} = 15232 16 \回\frac{15}{16}\times 1024 - 128 = 15232$ まず、MySQL5.6 のデフォルトの行フォーマットは COMPACT (コンパクト) であることを述べておく必要があると思います。 ), 5.7 将来のデフォルトの行フォーマットは DYNAMIC (動的) です。異なる行フォーマットは異なる方法で保存されます。他にも 2 つの行フォーマットがあります。この記事の以下の内容は主に DYNAMIC (動的) に基づいて説明されています。

公式ドキュメントリンク:

MySQL :: MySQL 5.7 リファレンスマニュアル:: 14.11 InnoDB 行形式 (次の行形式コンテンツのほとんどはこの中にあります)

レコードの各行には次の情報が含まれており、そのほとんどは公式文書に記載されています。ここに書いたことはあまり詳しくありません。スペースの計算に役立つ知識を書いただけです。より詳細な情報については、オンラインで「MySQL 行フォーマット」を検索してください。

名前	スペース	意味や機能など
`ファイルヘッダー`	38 バイト	ファイルヘッダー。ページのヘッダー情報を記録するために使用されます。チェックサム、ページ番号、前後のノードへの 2 つのポインター、ページタイプ、テーブルスペースなどが含まれます。
`ページヘッダー`	56 バイト	ページヘッダー。ページのステータス情報を記録するために使用されます。ページディレクトリ内のスロットの数、空き領域のアドレス、このページのレコード数、削除されたレコードが占有しているバイト数などが含まれます。
`上限と上限`	26 バイト	は、現在のページレコードの境界値を制限するために使用されます。最小値と最大値。
`ユーザーレコード`	未修正	ユーザーレコード、挿入したデータはここに保存されます。
#空きスペース
ページディレクトリ		各スロットには 4 ～ 8 個のユーザーデータが格納され、1 スロットは 1 ～ 2 バイトを占有します。 1 スロットが 8 データを超えると、自動的に 2 つのスロットに分割されます。
ファイルトレーラー

#実際のデータ未修正この部分は実際のデータです。

概略図:

インタビューの答え: 各 MySQL テーブルのデータ数は 2,000 万以下が最適ですよね?

また、注意すべき点がいくつかあります:

オーバーフローページ (外部ページ) のストレージ

注: これは DYNAMIC の機能です。

DYNAMIC を使用してテーブルを作成すると、InnoDB は長い可変長カラム (VARCHAR、VARBINARY、BLOB、TEXT 型など) の値を削除し、## に格納します。 #overflow page 、オーバーフローページを指す 20 バイトのポインターのみがこの列で予約されています。

COMPACT 行形式 (MySQL5.6 のデフォルト形式) では、最初の 768 バイトと 20 バイトのポインタが B ツリーノードのレコードに保存され、残りはオーバーフローページに保存されます。。

列がオフページに格納されるかどうかは、ページサイズと行の合計サイズによって異なります。行が長すぎる場合は、クラスター化インデックスのレコードが B ツリーページに収まるまで、最長の列がオフページストレージとして選択されます (文書にはその数が記載されていません)。 40 バイト以下の TEXT および BLOB は行内に直接格納され、ページングされません。

利点

DYNAMIC 行形式では、B ツリーノードに大量のデータが詰め込まれて列が長くなってしまうという問題が回避されます。

DYNAMIC 行形式の背後にある考え方は、長いデータ値の一部がオフページに格納されている場合、通常は値全体をオフページに格納するのが最も効率的であるということです。

DYNAMIC 形式では、可能な限り短い列が B ツリーノードに保持され、特定の行に必要なオーバーフローページの数が最小限に抑えられます。

異なる文字エンコーディングでのストレージ

Char、varchar、text などは、文字エンコーディングタイプを設定する必要があります。占有領域を計算するときは、異なるエンコーディングを設定する必要があります。占有スペースを考慮します。

varchar、text、およびその他の型には、それらが占める長さを記録するための長さフィールドリストがありますが、char は固定長型であり、これは特殊な状況です。フィールド名の型が char( であると仮定します。

固定長文字エンコーディング (ASCII コードなど) の場合、フィールド名は固定長形式で保存されます。 ASCII コードのは 1 バイトを占めるため、name は 10 バイトを占めます。
可変長文字エンコーディング (utf8mb4 など) の場合、名前用に少なくとも 10 バイトが予約されます。可能であれば、InnoDB は末尾の空白を削除して 10 バイトに保存します。
トリミング後にスペースを保存できない場合、末尾のスペースは
列値のバイト長 の最小値 (通常は 1 バイト) までトリミングされます。
列の最大長は次のとおりです:
$文字エンコーディングの最大文字長 xx N 文字エンコーディングの最大文字長\N 倍$ 10 $4 ##×$

正直に言うと、char の設計はよくわかりません。公式ドキュメントやいくつかのブログを含めて長い間読んできましたが、理解できる学生であれば理解できると思いますコメントエリアで疑問を明らかにしてください:

可変長の文字エンコーディングについて、char は可変長型に似ていますか?一般的に使用される utf8mb4 は 1 ～ 4 バイトを占有するため、char(10) が占有するスペースは 10 ～ 40 バイトになります。この変更は非常に大きなものですが、十分なスペースを残しておらず、また、 char フィールドのスペース使用量を記録するための可変長フィールドリスト?

計算を開始します

各ページに何が保存されているかはすでにわかっており、計算能力も備わりました。

上記のページ形式でページの残りのスペースを計算済みなので、各ページに使用できるバイト数は 15232 バイトになります。すぐ下の行を計算してみましょう。

#非リーフノードの計算

単一ノードの計算

インデックスページがノードですインデックスが格納される場所、つまり非リーフノード。

各インデックスレコードには、

現在のインデックスの値、6 バイトのポインター情報、使用される 5 バイトの行ヘッダー が含まれます。データページの次の層へのポインタを指します。

公式ドキュメントのインデックスレコードでポインタが占めるスペースが見つかりませんでした?この 6 バイトについては他のブログ投稿を参照しました。ソースでは 6 バイトであるとのことです。コードですが、特にソースコードのどの部分が分からないのですか?
もっと詳しい学生がコメント欄で疑問を解消できることを願っています。

主キー ID が bigint 型 (8 バイト) であると仮定すると、インデックスページ内のデータの各行が占めるスペースは

$8 6 5 = 19 8 6 5 = 19$ $##15232 ÷ 19 ≈ # を節約できます##801 15232 \div 19 \約 801$

ページディレクトリを含めて、スロットあたり 6 個のデータの平均を計算すると、少なくとも $##801 になります。 ÷ 6 ≈ 134 801 \div 6 \約 134$

主キーが int 型の場合、さらに多くのインデックスデータを約 993 個格納できます。

最初の 2 つの層の非リーフノードの計算

B ツリーで、ノードインデックスレコードが $N$ $#N N# になります。$ $になります。 N$ $N # が含まれます。 ##N$ $#N$ 2 $^{} ##N$

$##78 7 {を格納できます2}^{#=} 619369 787 ^ 2 = 619369$ # を格納できます
$##9$

前述しました前に、

行の最大長は、データベースページ

の半分よりわずかに小さいです。半分よりわずかに小さい理由は、各ページが

ページ形式の他のコンテンツ用にスペースを残しておくためです。 , したがって、各ページには少なくとも 2 つのデータを保持でき、各データは 8KB よりわずかに小さいと考えることができます。行のデータ長がこの値を超える場合、InnoDB は確実に一部のデータを overflow page に分割するため、考慮しません。

各データが 8KB の場合、各リーフノードには 2 つのデータしか格納できません。このようなテーブルは、主キーが bigint の場合、 $## しか格納できません。 #2 × 619369 = 1238738 2 \ 619369 倍 = 1238738$ 保存されるレコードの数が増える

テーブルが次のようになっているとします:-- 这是一张非常普通的课程安排表，除id外，仅包含了课程id和老师id两个字段 -- 且这几个字段均为 int 型（当然实际生产中不会这么设计表，这里只是举例）。 CREATE TABLE `course_schedule` ( `id` int NOT NULL, `teacher_id` int NOT NULL, `course_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ログイン後にコピー
まず、このテーブルの行データを分析しましょう。NULL 値リスト、可変長フィールドリストはなく、トランザクション ID とポインターフィールドをカウントする必要があり、行レコードヘッダーをカウントする必要があります。次に、各行が占めるスペースを分析します。データの量: $##4 4 4 6 7 5 = 30 4 4 4 6 7 5 = 30$ $##1 5232 ##÷$
算上页目录的槽位所占空间，每个叶子节点可以存放 502 条数据，那么三层B+树可以存放的最大数据量就是 $502 \times 986049 = 494,996,598$ ，将近5亿条数据！没想到吧??。
常规表的存放记录数
大部分情况下我们的表字段都不是上面那样的，所以我选择了一场比较常规的表来进行分析，看看能存放多少数据。表情况如下：
CREATE TABLE `blog` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id', `author_id` bigint unsigned NOT NULL COMMENT '作者id', `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题', `description` varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述', `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码', `cover_image` char(32) DEFAULT NULL COMMENT '封面图', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `release_time` datetime DEFAULT NULL COMMENT '首次发表时间', `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', `status` tinyint unsigned NOT NULL COMMENT '发表状态', `is_delete` tinyint unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `author_id` (`author_id`), KEY `school_code` (`school_code`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;
ログイン後にコピー
这是我的开源项目“校园博客”（GitHub地址：github.com/stick-i/scb…）中的博客表，用于存放博客的基本数据。

分析一下这张表的行记录：

行记录头信息：肯定得有，占用5字节。

可变长度字段列表：表中 title占用1字节，description占用2字节，共3字节。

null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。

事务ID和指针字段：两个都得有，占用13字节。

字段内容信息：

id、author_id、school_code 均为bigint型，各占用8字节，共24字节。

create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。

status、is_delete 为tinyint类型，各占用1字节，共2字节。

cover_image 为char(32)，字符编码为表默认值utf8，由于该字段实际存的内容仅为英文字母（存url的），结合前面讲的字符编码不同情况下的存储 ，故仅占用32字节。

title と description はそれぞれ varchar(50) と varchar(250) です。どちらもオーバーフローページを生成しないはずです (未確認)、文字エンコーディングは両方とも utf8mb4 です実際の運用では、70% 以上が中国語 (3 バイト)、25% 以上が英語 (1 バイト)、5% が 4 バイトの絵文字で保存されます。ストレージがいっぱいになると、これらが占有されます $(50 250) ##(0.7 × 3 0.25 × 1 0.05 #4) = 765 (50 250) \times (0.7 \times 3 0.25 \times 1 0.05 \times 4 ) = 765$

統計上面的所有分析，共佔用869 位元組，則每個葉子節點可以存放 $15232 \div 869 \approx 17$

$273$
，
約一千萬條數據，再次沒想到吧?。

資料計算總結
根據上面三種不同情況下的計算，可以看出，InnoDB三層B 樹情況下的資料儲存量範圍為一百二十多萬條到將近5億條
，這個跨度還是非常大的，同時我們也計算了一張博客資訊表，可以儲存
約一千萬條
數據。

所以啊，我們在做專案考慮分錶的時候還是得多關註表的實際情況，而不是盲目的認為兩千萬資料就是那個臨界點。
######如果面試時談到這塊的問題，我想面試官並不是想知道這個數字到底是多少，而是想看你如何分析這個問題，看你得出這個數字的過程。 ###

名前	スペース	意味や働きなど
行レコードのヘッダー情報	5 バイト	行レコードのヘッダー情報いくつかのフラグビット、データ型、その他の情報が含まれます削除フラグ、最小レコードフラグ、ソートなどレコード、データ型、ページ内の次のレコードの位置など。
可変長フィールドリスト	は固定されていません	保存できるものを保存します varchar、text、blob などの可変長フィールドが占めるバイト数。可変長フィールドの長さが 255 バイト未満の場合は、`1 バイト` で表され、 255 バイトを超える場合は、`2 で表されます。バイト`。テーブルフィールドに複数の可変長フィールドがある場合、リストには複数の値が存在しますが、何もない場合は保存されません。
null 値リスト	未修正	null になり得るフィールドが null かどうかを格納するために使用されます。ここでは、Null 許容フィールドはそれぞれ 1 ビットを占有します。これがビットマップの考え方です。このリストが占めるスペースはバイト単位で増加します。たとえば、NULL にできる列が 9 ～ 16 個ある場合、1.5 バイトではなく 2 バイトが使用されます。
トランザクション ID とポインターフィールド	6 7 バイト	MVCC を知っている人は、データ行に 6 バイトのトランザクション ID が含まれていることを知っているはずです。およびは 7 バイトのポインターフィールドです。主キーが定義されていない場合は、追加の 6 バイトの行 ID フィールドが存在します。もちろん、誰もが主キーを持っているため、この行 ID は計算しません。