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. 書き込み圧力が非常に高い場合は、データベースのシャーディングが必要になります。