MySQL データベース テーブルとデータベース パーティショニング戦略

まず、テーブルを分割する必要がある理由について説明します。

データシートが数百万に達すると、1 回のクエリにかかる時間が長くなります。共同クエリがある場合は、そこで失敗する可能性があります。テーブルをシャーディングする目的は、データベースの負担を軽減し、クエリ時間を短縮することです。日常の開発では、大きなテーブルに遭遇することがよくあります。いわゆる大きなテーブルとは、数百万、数千万のレコードを格納するテーブルを指します。このようなテーブルは大きすぎるため、データベースのクエリと挿入に時間がかかり、パフォーマンスが低下します。結合クエリが関係する場合、パフォーマンスはさらに悪くなります。テーブル シャーディングとテーブル パーティショニングの目的は、データベースの負荷を軽減し、データベースの効率を向上させることです。一般的に言えば、テーブルの追加、削除、変更、チェックの効率を向上させることです。データベース内のデータ量は、必ずしも制御可能ではありません。シャーディングを行わないと、時間の経過とともにビジネスが発展するにつれて、データベース内のテーブルはますます増え、テーブル内のデータ量も増加します。それに応じて、データ操作、追加、削除、変更、クエリのオーバーヘッドも増加します。また、分散展開が不可能であり、サーバーのリソース（CPU、ディスク、メモリ、IOなど）が制限されているため、データベースが運ぶことができるデータ量とデータ処理能力は最終的にボトルネックに遭遇します。

mysql が sql を実行するプロセスは次のとおりです。

1. SQL を受信します。

2. SQL をキューに入れます。

3. SQLを実行します。

4. 実行結果を返します。

最も時間のかかるプロセスはどこで発生しますか?まず、キューでの待機時間、次に SQL の実行時間です。実はこの2つは同じものです。待機中はSQLが実行されているはずです。そのため、SQL の実行時間を短縮する必要があります。

MySQL には、テーブル ロックと行ロックと呼ばれるメカニズムがあります。このメカニズムはなぜ存在するのでしょうか。それは、データの整合性を確保するためです。例を挙げてみましょう。2 つの SQL が同じテーブル内の同じデータを変更したい場合、このとき何をすべきでしょうか。両方の SQL が同時にこのデータを変更できますか。明らかに、MySQL はこの状況をテーブル ロック (myisam ストレージ エンジン) と行ロック (innodb ストレージ エンジン) の 2 つの方法で処理します。テーブルをロックすると、誰もこのテーブルを操作できなくなります。テーブル操作が完了するまでお待ちください。行ロックについても同様です。他の SQL ステートメントは、このデータに対する操作が完了するまで待機してから、このデータに対して操作を行う必要があります。データが多すぎると実行時間が長くなり、待機時間も長くなるため、テーブルを分割する必要があります。

2. サブテーブルの計画

1. クラスター

1. MySQL クラスターを構築するときに、ルート テーブルとパーティション テーブルの関係は何かと疑問に思う人もいるかもしれません。実際の意味でのシャードテーブルではありませんが、シャーディングテーブルの機能を有効にします。クラスタリングの意義は何ですか?データベースの負荷を軽減するということは、簡単に言えば、SQL キュー内の SQL の数を減らすことです。たとえば、SQL リクエストが 10 個ある場合、データベース サーバーのキューに入れられると、長時間待たなければなりません。この 10 個の SQL リクエストを 5 台のデータベース サーバーのキューに分散すると、1 台のデータベース サーバーのキューには 2 個だけになります。このようにすると、待機時間が大幅に短縮されるのでしょうか。これはすでに明らかです。そこで、サブテーブルのスコープ内に配置しました。私はいくつかの MySQL クラスターを実行しました:

Linux MySQL プロキシのインストール、構成、および読み取り/書き込みの分離

MySQL レプリケーションの相互マスタースレーブのインストールと構成、およびデータ同期

利点: 優れたスケーラビリティ、複数のサブテーブル後の複雑な操作がない (PHP コード)

デメリット: 1 つのテーブル内のデータ量は変化せず、操作にかかる時間も同じままで、ハードウェアのオーバーヘッドが高くなります。

2. サブテーブル

テーブルを分割する 2 つの方法:

フィールドを異なるテーブルに分割し、元のテーブル内の文字列型フィールドを他のテーブルに分割すると、メイン テーブルのクエリが高速化されます。

2. 垂直分割は分野別です。

データベースには、id、user、password、first_name、last_name、email、addr などの数十のフィールドを含む 3,000 万のユーザー レコードがあります。ユーザー ログインには、user フィールドと password フィールドが必要ですが、user フィールドと password フィールドの検索は低速です。user フィールドと password フィールドを別のテーブルに作成すると、速度が速くなります。ユーザーの他のフィールド用に別のテーブルを作成します。これは単なる例です。

データを同じ構造を持つ複数のテーブルに分割します。

水平とは、レコードごとに分割することを意味します。データベースに 3,000 万件のユーザー レコードがあり、処理速度が遅い場合、3,000 万件を 600 万件ずつ 5 つの部分に分割し、異なるマシンに配置することができます。

水平テーブル:

つまり、データ量が多くアクセス頻度の高いテーブルがあることを事前に予測し、それを複数のテーブルに分割します。この予測はおおよそ正しいです。フォーラムの投稿テーブルは時間の経過とともに間違いなく非常に大きくなり、数十万、さらには数百万のデータになる可能性があります。チャット ルームには情報テーブルがあります。何十人もの人が一晩中チャットをします。時間が経つにつれて、このテーブルのデータは非常に大きくなるはずです。このような状況はたくさんあります。そのため、このような予測可能な大容量データテーブルについては、事前に N 個のテーブルに分割します。N の値は実際の状況によって異なります。チャット情報テーブルを例に挙げます。

事前に、message_00、message_01、message_02...message_98、message_99 という 100 個のテーブルを作成しました。次に、ユーザー ID を使用して、ユーザーのチャット情報をどのテーブルに配置するかを判断しました。剰余メソッドを使用して情報を取得できました。

3. 実際の応用：

垂直テーブル パーティション分割と水平テーブル パーティション分割を組み合わせる必要があります。データベースに 3,000 万人のユーザーがいる場合は、最初に垂直パーティション分割を検討し、パーティション分割後に水平パーティション分割を実行できます。

つまり、まず他のフィールドを user_info テーブルに分割し、ユーザー ID、パスワード、ユーザー名などのキー フィールドのみをユーザー メイン テーブルに残します。

次に、水平分割を実行して、ユーザー テーブルとユーザー情報テーブルを同じ構造を持つ複数のテーブルに分割します。

次に、MYSQL がデータを別々のテーブルに保存するときにどのように動作するかを見てみましょう。

1. シンプルな MySQL マスタースレーブレプリケーション:

MySQL のマスター スレーブ レプリケーションは、データベースの読み取りと書き込みの分離を解決し、次の図に示すように読み取りパフォーマンスを大幅に向上させます。

マスタースレーブレプリケーションプロセスを次の図に示します。

ただし、マスター スレーブ レプリケーションでは、他の一連のパフォーマンス ボトルネックも発生します。

1. 書き込みをスケールできない

2. 書き込みはキャッシュできない

3. レプリケーションの遅延

4. ロック率の向上

5. テーブルが大きくなり、キャッシュレートが低下する

この問題を解決するには、次の最適化ソリューションを導き出す必要があります。見てみましょう。

2. MySQLの垂直分割

業務が十分に独立して分割されている場合、異なる業務のデータを異なるデータベース サーバーに配置することは良い解決策になります。また、いずれかの業務がクラッシュしても、他の業務の正常な動作には影響しません。負荷分散の役割も果たし、データベースのスループットが大幅に向上します。垂直分割後のデータベース アーキテクチャ図は次のとおりです。

しかし、ビジネスは十分に独立しているものの、基本的に各ビジネスに関連付けられているユーザーなど、ビジネス間には常に何らかのつながりがあります。さらに、このパーティション分割方法では、単一テーブル内のデータ量の急増の問題を解決できないため、水平パーティション分割を試してみませんか?

3. MySQL 水平シャーディング

これは非常に良いアイデアです。ユーザーは特定のルール (ID ハッシュによる) に従ってグループ化され、このユーザー グループのデータはデータベース シャード (つまりシャーディング) に保存されます。このように、ユーザー数が増えても、サーバーを構成するだけで済みます。原理図は次のとおりです。

ユーザーが配置されているシャードを特定するにはどうすればよいでしょうか。ユーザーとシャードに対応するデータ テーブルを作成できます。リクエストが行われるたびに、まずこのテーブルからユーザーのシャード ID を見つけ、次に示すように、対応するシャードから関連データをクエリします。

単一のデータベースと単一のテーブル

単一のデータベースと単一のテーブルは、最も一般的なデータベース設計です。たとえば、データベース db にはユーザー テーブルがあり、すべてのユーザーは db ライブラリのユーザー テーブルで見つかります。

複数のテーブルを持つ単一のデータベース

ユーザー数が増えると、ユーザー テーブルのデータ量もどんどん大きくなります。データ量が一定レベルに達すると、ユーザー テーブルに対するクエリが徐々に遅くなり、DB 全体のパフォーマンスに影響を及ぼします。 MySQL を使用する場合、さらに深刻な問題が発生します。列を追加する必要がある場合、MySQL はテーブルをロックし、すべての読み取りおよび書き込み操作を待機させる必要があります。

ユーザーを何らかの方法で水平に分割して、user_0000 と user_0001 のように、まったく同じテーブル構造を持つ 2 つのテーブルを生成することができます。user_0000 + user_0001 + ... のデータは、まさに完全なデータ セットです。

複数のデータベースと複数のテーブル

データ量が増えると、単一の DB のストレージ容量が足りなくなる場合があります。クエリの数が増えると、単一のデータベース サーバーではサポートできなくなる場合があります。このとき、データベースを水平に差別化することができます。

シャーディングのルール

テーブルを設計するときは、テーブルをさまざまなデータベースとテーブルに分割するためのルールを決定する必要があります。たとえば、新しいユーザーがログインすると、プログラムはどのテーブルにユーザー情報を追加するかを決定する必要があります。同様に、ログインすると、ユーザーのアカウント番号を通じてデータベース内の対応するレコードを見つける必要があり、これらすべてを特定のルールに従って実行する必要があります。

ルーティング

ライブラリとテーブルのパーティション分割ルールを通じて対応するテーブルとライブラリを見つけるプロセス。たとえば、データベースとテーブルを分割するルールが user_id mod 4 である場合、ユーザーがアカウント ID 123 で新しいアカウントを登録すると、id mod 4 を使用して、このアカウントを User_0003 テーブルに保存することを決定できます。ユーザー 123 がログインすると、123 mod 4 を実行して、レコードが User_0003 にあることを確認します。

サブライブラリとサブテーブルによって生じる問題と注意事項

1. データベースとテーブルの分割に関する問題

ユーザーが商品を購入した場合、取引記録を保存して取得する必要があります。テーブルをユーザーの緯度に応じて分割すると、各ユーザーの取引記録が同じテーブルに保存されるため、ユーザーの購入状況を見つけるのが迅速かつ便利です。ただし、特定の商品の購入状況は複数のテーブルに分散している可能性があり、見つけるのがより困難になります。逆に、商品ディメンションごとにテーブルを分割すると、この商品の購入状況は簡単にわかりますが、購入者の取引記録を見つけるのは面倒です。

したがって、一般的な解決策は次のとおりです。

a. テーブルをスキャンして問題を解決します。この方法は基本的に不可能であり、効率が低すぎます。

b. ユーザーディメンションに従って 1 セット、製品ディメンションに従って 1 セット、合計 2 セットのデータを記録します。

c. 検索エンジンを通じて解決しますが、リアルタイム要件が非常に高い場合は、リアルタイム検索に関連します。

2. 共同クエリの問題

関連するテーブルが同じデータベース内に存在しない可能性があるため、ユニオンクエリは基本的に不可能です。

3. データベース間のトランザクションを避ける

1 つのトランザクションで db0 のテーブルを変更している間は、db1 のテーブルを変更しないでください。これにより、操作が複雑になり、効率に影響します。

4. 同じデータセットを同じDBサーバーに配置する

例えば、販売者Aの商品と取引情報をdb0に置いておくと、db1に障害が発生しても、販売者Aの関連情報を正常に利用できます。これは、あるデータベースのデータが別のデータベースのデータに依存しないようにすることを意味します。

1つのマスター、複数のバックアップ

実際のアプリケーションでは、ほとんどの場合、読み取りが書き込みをはるかに上回ります。 MySQL は読み取りと書き込みを分離するメカニズムを提供します。すべての書き込み操作はマスターに対応している必要があります。読み取り操作はマスターとスレーブのマシンで実行できます。スレーブの構造はマスターとまったく同じです。マスターは複数のスレーブを持つことができ、スレーブをスレーブに接続することもできます。この方法により、DB クラスターの QPS を効果的に向上できます。

すべての書き込み操作は最初にマスターで実行され、その後スレーブに同期されるため、マスターからスレーブ マシンへの同期には一定の遅延が発生します。システムが非常にビジーな場合、遅延の問題はさらに深刻になり、スレーブ マシンの数が増えると、この問題もさらに深刻になります。

さらに、マスターがクラスターのボトルネックになっていることがわかります。書き込み操作が多すぎると、マスターの安定性に重大な影響が出ます。マスターに障害が発生すると、クラスター全体が正常に動作しなくなります。

したがって、1. 読み取り圧力が非常に高い場合は、スレーブ マシンを追加して問題を解決することを検討できますが、スレーブ マシンの数が一定レベルに達すると、データベースを分割することを検討する必要があります。 2. 書き込み圧力が非常に高い場合は、データベースのシャーディングが必要になります。

MySQL をデータベースとテーブルに分割する必要があるのはなぜですか?

MySQL が使用される場所では、データ量が大きい限り、データベースをテーブルに分割するという問題にすぐに直面すると言えます。

ここで質問です: データベースとテーブルを分割する必要があるのはなぜですか? MySQL は大きなテーブルを処理できないのですか?

実際、大きなテーブルを扱うことも可能です。私が経験したプロジェクトでは、1つのテーブルの物理ファイルサイズは80G以上、1つのテーブル内のレコード数は5億以上、このテーブルは

これは、非常にコアなテーブルである友人関係テーブルに属します。

しかし、この方法は最善ではありません。Ext3 ファイル システムなどのファイル システムも、大きなファイルの処理に多くの問題を抱えているからです。

このレベルはxfsファイルシステムに置き換えることができますが、単一のMySQLテーブルが大きすぎる場合、テーブル構造の調整に関連する操作ベースという解決が難しい問題があります。

これはもう不可能です。したがって、大規模なプロジェクトでは、使用中に個別のデータベースとテーブルを適用する必要が生じます。

Innodb自体の観点から見ると、データファイルのBtreeにはリーフノードロックと子ノードロックの2つのロックしかありません。想像がつくと思いますが、ページの分割や追加が発生すると、

新しいリーフが作成されると、テーブルにデータを書き込むことができなくなります。

したがって、サブライブラリとサブテーブルの方が依然として良い選択です。

では、サブライブラリとサブテーブルはいくつが適切でしょうか?

テストの結果、1000万件のレコードを持つ単一のテーブルでの書き込みと読み取りのパフォーマンスは比較的良好でした。このように、バッファが残っていると、単一のテーブルがデータフォントでいっぱいになります。

レコード数は 800 万未満、文字データ型の単一テーブルは 500 万未満に抑えられます。

ユーザービジネスなどの 100 個のデータベースと 100 個のテーブルに基づくプランの場合:

500 万 * 100 * 100 = 50000000 = 5000 億件のレコード。

大まかなアイデアが頭の中にあれば、ビジネスに基づいた計画を立てるのは比較的簡単です。