MySQLデータベースとテーブルシャーディングの概要

プロジェクトの開発中に、データベースのデータがどんどん大きくなり、その結果、1 つのテーブルにデータが多すぎる状態になります。その結果、テーブル ロック メカニズムによってクエリが遅くなり、アプリケーション操作に大きな影響が出てしまい、データベース パフォーマンスのボトルネックが発生しました。

このような状況では、データベースまたはテーブルを分割すること、つまり、単一のデータベースまたはテーブルを複数のライブラリと複数のデータ テーブルに分割することが考えられます。その後、ユーザーがアクセスすると、特定のアルゴリズムとロジックに基づいて、異なるライブラリと異なるテーブルにアクセスできます。このようにして、データは複数のデータ テーブルに分散され、単一のデータ テーブルへのアクセス負荷が軽減されます。データベース アクセス パフォーマンスが向上しました。

以下は、プロジェクト内のサブライブラリとサブテーブルの概要です。

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

単一のデータベースと単一のテーブルは、最も一般的なデータベース設計です。たとえば、データベース 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 つ (リーフ ノード ロックと子ノード ロック) しかありません。ご想像のとおり、ページ分割が発生したり、新しいリーフが追加されたりすると、テーブルにデータを書き込むことができなくなります。そのため、シャーディングの方が適しています。

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

テストでは、単一テーブル内のレコード数が 1,000 万未満の場合に、書き込みと読み取りのパフォーマンスが比較的良好であることが示されています。このように、バッファーを残しておけば、すべてのデータ文字を含む単一テーブル内のレコード数を 800 万未満に抑えることができ、文字を含む単一テーブル内のレコード数を 500 万未満に抑えることができます。

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

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

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

本当の質問

ウェブサイトのユーザー数は数千万人だが、アクティブユーザー数はわずか1%だとします。データベースを最適化してアクティブユーザーのアクセス速度を上げるにはどうすればよいでしょうか。

答え：

MySQL パーティショニングを使用すると、アクティブ ユーザーを 1 つのゾーンに、非アクティブ ユーザーを別のゾーンに分割できます。アクティブ ユーザー ゾーン自体のデータ量は比較的少ないため、アクティブ ユーザーのアクセス速度を向上させることができます。

テーブルを水平に分割して、アクティブなユーザーを 1 つのテーブルに配置し、非アクティブなユーザーを別のテーブルに配置することもできます。これにより、アクティブなユーザーのアクセス速度が向上します。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。