mysql でテーブルを分割する場合

mysql でのテーブル分割に適した状況: 1. データの量が大きすぎて、通常の運用とメンテナンスがビジネス アクセスに影響を与える場合。たとえば、データベースのバックアップに大量のディスク IO とネットワーク IO が必要になる場合です。テーブルの DDL 変更によりテーブル全体がロックされ、大きなテーブルにアクセスして更新するとロック待機が発生します。2. ビジネスの発展に伴い、一部のフィールドを垂直に分割する必要があります。3. 1 つのテーブル内のデータ量が増加します。パフォーマンスがボトルネックに近づいている場合は、水平スライスを考慮する必要があります。

このチュートリアルの動作環境: Windows7 システム、mysql8 バージョン、Dell G3 コンピューター。

すべてのテーブルを分割する必要があるわけではありません。分割するかどうかは主にデータの増加率によって決まります。セグメント化によりビジネスはある程度複雑になりますが、データベースはデータの保存とクエリの実行に加えて、ビジネスのニーズをより適切に実現するのに役立つ重要なタスクの 1 つです。

「過剰設計」や「時期尚早の最適化」を避けるために絶対に必要な場合を除き、サブデータベースとサブテーブルという大きなトリックは使用しないでください。データベースやテーブルを分割する前に、分割のためだけに分割するのではなく、ハードウェアのアップグレード、ネットワークのアップグレード、読み取りと書き込みの分離、インデックスの最適化など、最初にできることから最善を尽くしてください。データ量が単一テーブルのボトルネックに達した場合は、データベースとテーブルのシャーディングを検討してください。

では、mysql でサブテーブルを考慮する必要があるのはどのような場合ですか?

1. データ量が大きすぎて正常に動作する運用と保守はビジネス アクセスに影響を及ぼします

ここで説明する運用と保守とは次のことを指します:

• データベースのバックアップの場合、単一のテーブルが大きすぎる場合は、バックアップ中には、ディスク IO とネットワーク IO の量が必要になります。たとえば、1T のデータがネットワーク経由で送信され、50MB を占有する場合、完了までに 20,000 秒かかり、プロセス全体のリスクは比較的高くなります。大きなテーブルを作成すると、MySQL はテーブル全体を長時間ロックし、その間、ビジネスはテーブルにアクセスできなくなり、大きな影響を与えます。 pt-online-schema-changeを使用すると、使用中にトリガーやシャドウテーブルが作成されるため、これにも時間がかかります。この操作中はリスクタイムとしてカウントされます。データテーブルを分割して総量を減らすと、このリスクを軽減できます。

• 大きなテーブルは頻繁にアクセスおよび更新されるため、ロック待機が発生する可能性が高くなります。データを分割し、スペースを時間と引き換えに、アクセスのプレッシャーを隠して軽減します

2。ビジネスの発展に伴い、一部のフィールドは垂直に分割する必要があります

たとえば、プロジェクトの開始時に設計されたユーザー テーブルが次のような場合:

プロジェクトの初期段階では、この設計は単純なビジネス ニーズを満たします。迅速な反復開発。ビジネスが急速に発展すると、ユーザー数は 10 万人から 10 億人に急増し、ユーザーは非常にアクティブになるため、ログインするたびに last_login_name フィールドが更新され、ユーザー テーブルが常に更新されるため、非常にストレスがかかります。その他のフィールド: id、name、personal_info は変更されないか、ほとんど更新されないため、ビジネスの観点からは、last_login_time を分割し、新しい user_time テーブルを作成する必要があります。

personal_info 属性は更新およびクエリの頻度が低く、テキスト フィールドが占めるスペースが多すぎます。このときuser_extテーブルを垂直分割する必要があります。

3. データ量は急速に増加します

ビジネスの急速な発展に伴い、1 つのテーブル内のデータ量は増加し続けます。ボトルネックに近い場合は、水平セグメンテーションが個別のデータベースとテーブルを作成するために行われることを考慮する必要があります。このとき、適切なセグメンテーション ルールを選択し、事前にデータ容量を見積もる必要があります

ビジネス ケース分析

1. ユーザーセンター ビジネス シナリオ

ユーザー センターは、主にユーザー登録、ログイン、クエリ/変更、その他の機能を提供する非常に一般的なビジネスです。そのコア テーブルは次のとおりです:

ビジネスから切り離されたアーキテクチャ設計は不正です。サブデータベースとテーブル サブデータベースの前に、ビジネス シナリオの要件を整理する必要があります:

#ユーザー側：フロントアクセス、トラフィック量 より大規模、高可用性、高一貫性を確保する必要がある。要件には主に 2 つのタイプがあります:

ユーザー ログイン: ログイン名/電話/電子メールを通じてユーザー情報をクエリします。リクエストの 1% は以下に属します。このタイプへの

• • ユーザー情報クエリ: ログイン後、uid を介してユーザー情報をクエリします。リクエストの 99% はこのタイプです

  ##運用面: バックエンド アクセス、運用ニーズのサポート、年齢、性別、ログイン時間、登録時間などに基づくページング クエリ。これは、アクセス量が少なく、可用性と一貫性に関する要件が低い内部システムです。

• 2. 水平方向の分割方法

データ量が大きくなると、前述のようにデータベースを水平方向に分割する必要があります。分割方法には、「数値範囲に基づく」と「数値剰余に基づく」があります。

「値の範囲に基づく」

: 主キー uid を基準として、uid の範囲に応じてデータを複数のデータベースに水平分割します。たとえば、user-db1 は 0 ～ 1000w の範囲の uid を持つデータを格納し、user-db2 は 1000w ～ 2000wuid の範囲の uid を持つデータを格納します。

• 利点は、拡張が簡単で、容量が足りない場合は新しいデータベースを追加するだけです。

• 欠点は、リクエスト量が不均等であることです。一般に、新しく登録されたユーザーの方がアクティブになるため、新しい user-db2 の負荷が user-db1 よりも高くなり、その結果、サーバー使用率が低いバランス

#「数値に基づくモジュロ」: 主キー uid も除算の基礎として使用され、データが分割されますuid のモジュロ値に基づいて複数のデータベースに水平方向に転送します。たとえば、user-db1 は uid データを法 1 で保存し、user-db2 は uid データを法 0 で保存します。

• メリットは、データ量とリクエスト量が均等に分散されることです。

• デメリットは、容量が足りない場合の拡張が面倒であることです。十分です。新しいデータベースを追加します。再ハッシュが必要です。データのスムーズな移行を考慮する必要があります。

非 uid クエリ メソッド

水平セグメンテーションの後、uid によるクエリの需要は非常に高くなる可能性があります。満足した場合は、特定のデータベースに直接ルーティングできます。 login_name などの非 uid に基づくクエリの場合、どのライブラリにアクセスする必要があるかが不明であり、この場合、すべてのライブラリを走査する必要があり、パフォーマンスが大幅に低下します。

ユーザー側では「非uid属性からuidへのマッピング関係を確立する」、運用側では「フロントエンドとバックエンドを分離する」という解決策が採れます。採用することができます。

1. 非 uid 属性から uid へのマッピング関係を確立します

• マッピング関係

例: ログイン名をデータベースに直接配置することはできませんが、ログイン名→uid のマッピング関係を確立し、インデックス テーブルまたはキャッシュに保存できます。 login_name にアクセスするときは、まずマッピング テーブルを使用して login_name に対応する uid を照会し、次に uid を使用して特定のライブラリを見つけます。

マッピング テーブルには列が 2 つしかなく、大量のデータを保持できます。データ量が多すぎる場合は、マッピング テーブルを水平に分割することもできます。このタイプの kv 形式のインデックス構造では、キャッシュを使用してクエリのパフォーマンスを最適化でき、マッピング関係が頻繁に変更されず、キャッシュ ヒット率が非常に高くなります。

• 遺伝子メソッド

ライブラリ遺伝子の分割: ライブラリが uid によって分割される場合、ライブラリは 8 つのライブラリに分割され、uid% を使用してルーティングされます。 8 、現時点では、この行の User データがどのライブラリに該当するかを決定するのは uid の最後の 3 ビットであり、これらの 3 ビットはサブライブラリ遺伝子と見なすことができます。

2. フロントエンドとバックエンドの分離

ユーザー側の主な要件は、単一行のクエリに焦点を当てることです。 login_name/phone/email から uid へのマッピング関係を確立するため、これらのフィールドのクエリの問題を解決できます。

運用面では、バッチページングや様々な条件を伴うクエリが多く、計算量が多く、返されるデータも多く、データベースのパフォーマンスも高くなります。このとき、同じバッチのサービスやデータベースがユーザー側と共有されている場合、少数のバックグラウンドリクエストが大量のデータベースリソースを占有し、ユーザー側のアクセスパフォーマンスの低下やタイムアウトが発生する可能性があります。

このようなビジネスは、運用側のバックエンド事業が独立したサービスやDBを抽出し、フロントエンドとの結合を解決する「フロントエンド・バックエンド分離」ソリューションを採用するのが最適です。エンドビジネスシステム。操作側は可用性や一貫性に対する高い要件を持たないため、リアルタイムライブラリにアクセスする必要はなく、binlog を介して操作ライブラリにデータを非同期に同期してアクセスできます。データ量が多い場合は、ES 検索エンジンや Hive を使用して、バックグラウンドで複雑なクエリ方法に対応することもできます。

[関連する推奨事項: mysql ビデオ チュートリアル ]

以上がmysql でテーブルを分割する場合の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。