MySQL シャーディング入門ガイド

序文

リレーショナルデータベースは、システムのボトルネックになる可能性が高くなります。単一のマシンのストレージ容量、接続数、および処理能力には制限があります。データ量と同時実行性が増加すると、データベースをセグメント化する必要があります。

データシャーディングの手段は、データベースとテーブルを異なる部分に分割することです。ライブラリとテーブルのシャーディングには 2 つの側面があります。シャーディングテーブルなしでライブラリのみをシャーディングする場合と、シャーディングライブラリなしでテーブルのみをシャーディングする場合があります。

データベース分散の核となる内容は、データの分割と、分割後のデータの配置および統合に他なりません。

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

サブテーブル

1 つのテーブル内のデータの量が多すぎると、SQL 実行のパフォーマンスに重大な影響を及ぼします。一般的に、単一のテーブルが数百万に達すると、パフォーマンスは比較的低下するため、テーブルをサブテーブルに分割する必要があります。

テーブルを分割するということは、1 つのテーブルのデータを複数のテーブルに入れて、1 つのテーブルのみをクエリすることを意味します。たとえば、テーブルをプロジェクト ID ごとに分割し、1 つのテーブルに固定数のプロジェクトデータを配置して、各テーブルのデータ量を制御可能な範囲内で制御できるようにします。

サブライブラリ

経験上、データベースは最大 2000 件の同時リクエストをサポートするときに拡張する必要があり、健全な単一データベースの同時実行値は 1000 前後で維持するのが最適です。すると、1 つのライブラリのデータを複数のライブラリに分割し、アクセスするときに 1 つのライブラリのみにアクセスすれば済みます。

これは、データベースとテーブルのシャーディングと呼ばれます。なぜデータベースとテーブルをシャーディングする必要があるのでしょうか?

同時サポート機能の向上
ディスク使用量を削減
SQL実行パフォーマンスの向上

データベースとテーブルを分割する方法

写真を直接見てください:

垂直分割の場合、テーブルの垂直分割を回避するために、システム設計の最初にテーブルを適切に設計することをお勧めします。

水平分割は、範囲または特定のフィールドハッシュによって実行できます。範囲で分割するメリットは、容量の拡張が簡単で、新しいテーブルやデータベースを用意するだけで済むことです。ただし、ホットな問題を引き起こしやすいため、実際に使用する際にはビジネスシナリオと組み合わせて検討する必要があります。ハッシュ分割のメリットは、各データベースやテーブルのリクエスト負荷を均等に分散できることです。デメリットは、容量拡張が難しく、以前のデータを再ハッシュする必要があり、データ移行のプロセスが必要になることです。

シャーディングによって生じる問題

サブデータベースとサブテーブルは、単一のマシンと単一のデータベースによってもたらされるネットワーク IO、ハードウェアリソース、および接続数への負荷を効果的に軽減できます。しかし、いくつかの問題も発生しました。

トランザクションの一貫性の問題は、分散トランザクションまたは最終的な一貫性の確保を通じて解決されます。
クロスノード関連クエリ結合問題グローバルテーブル、フィールド冗長性、データアセンブリ、ER 断片化
ノード間のページング、ソート、集計関数の問題まず、異なるシャードノードでクエリを実行し、最後に結果を要約またはマージします
グローバル主キー重複回避問題さまざまな分散ID生成アルゴリズム
データの移行と拡張の問題が範囲シャーディングである場合、容量を拡張するにはノードを追加するだけで済みます。
ハッシュの場合、一般的な方法は、まず履歴データを読み取り、次に指定されたシャーディングルールに従って各シャードノードにデータを書き込みます。

データ移行

2 つのデータ移行ソリューションを紹介します。

最も低レベルの解決策は、システムをしばらくシャットダウンし、事前に作成されたデータインポートツールを使用して単一のテーブルからデータを抽出し、それをサブライブラリとサブテーブルに書き込むことです。

2 番目のソリューションは、デュアル書き込み移行ソリューションであり、より信頼性が高いようです。オンラインシステムでは、以前にデータが書き込まれたすべての場所、追加、削除、変更操作、古いデータベースの追加、削除、変更に加えて、新しいデータベースの追加、削除、変更が追加されます。これは、いわゆる二重書き込みです。システムが展開された後、ソリューション 1 のデータインポートツールを実行して、古いデータベースを読み取り、新しいデータベースを書き込みます。書き込み時には、gmt_modified などのフィールドに基づいてデータの最終変更時刻を決定する必要があります。新しいデータベースにデータがない場合、またはデータが新しいデータベースのデータよりも新しい場合にのみ、データが書き込まれます。簡単に言えば、新しいデータを古いデータで上書きすることは許可されていません。

1 回の書き込み後も、不一致が残る場合があります。この場合、プログラムは自動的に新しい検証ラウンドを実行し、新しいデータベースと古いデータベースの各テーブルの各データを比較します。相違点がある場合、プログラムは古いデータベースからデータを読み取り、再度書き込みます。データが完全に一致するまでこのサイクルを繰り返します。

ミドルウェア

サブライブラリとサブテーブル用のより一般的なミドルウェアは次のとおりです。

Cobar: これは Alibaba の b2b チームによって開発され、オープンソース化されました。これは、アプリケーションサーバーとデータベースサーバーの間にあるプロキシレイヤーソリューションです。アプリケーションは、JDBC ドライバーを介して Cobar クラスターにアクセスします。Cobar は、SQL とデータベースのパーティション分割ルールに従って SQL を分解し、MySQL クラスター内のさまざまなデータベースインスタンスに配布して実行します。読み取り/書き込み分離、ストアドプロシージャ、データベース間の結合、ページングなどの操作はサポートされていません。近年更新されておらず、利用する人もあまりいません。
TDDL: Taobao チームによって開発されたクライアント層ソリューションです。基本的な CRUD 構文と読み取り/書き込み分離をサポートしていますが、結合や複数テーブルクエリなどの構文はサポートしていません。現時点ではまだ Taobao のダイヤモンド構成管理システムに依存しているため、あまり使用されていません。
Atlas: 360 オープンソース、プロキシレイヤーソリューションに属します。数年間メンテナンスが行われておらず、現在使用している企業はごくわずかです。
Sharding-jdbc: Dangdang のオープンソースのクライアント層ソリューションは、ShardingSphere に名前が変更されました。データベースとテーブルのシャーディング、読み取りと書き込みの分離、分散 ID 生成、柔軟なトランザクション (ベストエフォート配信トランザクション、TCC トランザクション) など、あまり制限のない幅広い SQL 構文をサポートします。また、多くの企業が利用しており、コミュニティも活発です。
Mycat: Cobar をベースにしたプロキシレイヤーソリューションです。サポートされている機能は非常に充実しています。 Sharding-jdbc と比較すると、新しいです。

要約すると、Sharding-jdbc と Mycat が検討できるオプションです。

Sharding-jdbc などのクライアント層ソリューションの利点は、デプロイメントが不要で、運用および保守コストが低く、プロキシ層での二次転送が不要で、パフォーマンスが高いことです。欠点はカップリングです。
Mycat のプロキシレイヤーソリューションの欠点は、一連のミドルウェアを自分で導入して保守する必要があり、運用と保守のコストが高くなることですが、利点はプロジェクトに対して透過的であることです。

MySQL パーティショニング (非推奨)

ここでパーティショニングが導入されているのは、主に分割、データベースとテーブルのシャーディングなどの概念との混乱を避けるためです。
MySQL はバージョン 5.1 以降でパーティション機能をサポートしています。パーティショニングとは、データベースが特定のルールに従ってテーブルを複数のより小さく管理しやすい部分に分割することを意味します。データベースにアクセスするアプリケーションに関しては、論理的には 1 つのテーブルまたは 1 つのインデックスしかありませんが、実際にはこのテーブルは複数の物理パーティションで構成されている可能性があり、これはアプリケーションには透過的です。
MySQL パーティショニングでは、パーティションキーの概念が導入され、非常に大きなテーブルの管理に役立つ分割統治アプローチが採用されています。パーティションキーは、特定の間隔値、特定の値リスト、または HASH 関数に基づいてデータを集計し、ルールに従ってデータが異なるパーティションに分散されるようにするために使用されます。 MySQL 5.7 では、主に 6 つのパーティションタイプが使用できます。

RANGE パーティション分割: 指定された連続した間隔範囲に基づいて、データを異なるパーティションに割り当てます。
LIST パーティション分割: RANGE パーティション分割と似ていますが、違いは、LIST パーティション分割は列挙値リストパーティション分割に基づいているのに対し、RANGE は指定された連続間隔範囲パーティション分割に基づいていることです。
COLUMNS パーティション分割: RANGE および LIST と似ていますが、パーティションキーが複数の列または非整数にできる点が異なります。
HASH パーティション分割: 指定されたパーティション数に基づいて、データは異なるパーティションを法として分散されます。
KEY パーティショニング: HASH パーティショニングに似ていますが、MySQL が提供するハッシュ関数を使用します。
サブパーティション: 複合パーティションまたは結合パーティションとも呼ばれ、プライマリパーティションの下に別のパーティションを作成し、データを再度分割します。

LIST パーティションの例を次に示します。

テーブルorders_listを作成します（
  id INT AUTO_INCREMENT、
  顧客姓 VARCHAR(30)、
  ストアID INT、
  営業担当者ID INT、
  注文日 日付、
  注 VARCHAR(500)、
  インデックス idx (id)
) エンジン = INNODB
  PARTITION BY LIST(store_id) (
  パーティション p1
  値 (1, 3, 4, 17)
  インデックスディレクトリ = '/var/orders/district1'
  データディレクトリ = '/var/orders/district1'、
  パーティション p2
  (2, 12, 14)の値
  インデックスディレクトリ = '/var/orders/district2'
  データディレクトリ = '/var/orders/district2',
  パーティション p3
  値 (6, 8, 20)
  インデックスディレクトリ = '/var/orders/district3'
  データディレクトリ = '/var/orders/district3',
  パーティション p4
  値 (5, 7, 9, 11, 16)
  インデックスディレクトリ = '/var/orders/district4'
  データディレクトリ = '/var/orders/district4',
  パーティション p5
  値 (10, 13, 15, 18)
  インデックスディレクトリ = '/var/orders/district5'
  データディレクトリ = '/var/orders/district5'
);

パーティショニングの利点:

ストレージ容量を拡張します。
クエリを最適化します。 WHERE 句にパーティション条件が含まれている場合、必要なパーティションのみをスキャンしてクエリの効率を向上させることができます。SUM() や COUNT() などの集計関数を含むクエリを実行する場合、各パーティションで並列処理できます。
有効期限が切れたデータパーティションや保存する必要のないデータパーティションについては、パーティションを削除することでデータをすばやく削除できます。
クエリデータを複数のディスクに分散して、クエリスループットを向上させます。