MySQL学習記録: KEYパーティションが引き起こした血なまぐさい事件

需要背景

ビジネステーブル tb_image のデータの一部は次のとおりです。id は一意ですが、image_no は一意ではありません。 image_no は各ファイルの番号を表します。各ファイルはビジネスシステムで複数のファイルを生成します。各ファイルの一意の ID はフィールド id です。

ビジネステーブル tb_image に関する情報は次のとおりです。

image_no によるクエリと id によるクエリ。
在庫データ 2kw;
毎日の成長は約4週間です。
1 日のクエリ量は約 200,000 件です。
これは非 ToC システムなので、同時実行の上限がわかります。

ソリューションの選択

上記の業務分析に基づくと、データベースとテーブルを分離する必要はまったくありません。単一のデータベースがシャーディングされている場合、クエリは image_no と id に基づいて実行する必要があるため、1 つの解決策は冗長シャーディングを使用することです (つまり、データの 1 つのコピーは image_no をシャーディングキーとして保存し、データのもう 1 つのコピーは id をシャーディングキーとして保存します)。もう 1 つの解決策は、image_no のみをシャーディングキーとして使用し、id のクエリ要件に基づいて、ビジネスレイヤーが結果をマージするか、サードパーティのミドルウェアを導入することです。

単一のデータベースをテーブルに分割するのはより複雑であることを考慮して、パーティション機能を使用することにしました。さらに、128 個のパーティション (各パーティションには kW レベルのデータボリュームがあります) を備えた容量評価パーティションテーブルソリューションにより、少なくとも 15 年間はビジネスが安定して実行されることが完全に保証されます (図のオレンジ色の部分は、実際のビジネスの成長とより一致しています)。

さらに、RANGE、LIST、および HASH パーティションは VARCHAR 列をサポートしていないため、KEY パーティションを使用することが決定されています。その原理の公式な導入は、MySQL 組み込みのハッシュアルゴリズムを使用し、パーティション番号の係数を取ることです。

パフォーマンステスト

シャードキーを image_no として選択し、パーティション数を 128 に決定したら、実現可能性とパフォーマンスのテストのためにデータをロードします。パーティション数を 128 に選択した理由は、11 億 / 1kw = 110 ≈ 128 です。また、プログラマーは 2 の累乗を使用することを好みます。しかし、パーティション番号 128 からすべての悪夢が始まります。

128 個のパーティションに 100,000 個のデータを挿入しようとしました。挿入後、驚くべき現象が発生しました。すべての奇数パーティション (p1、p3、p5、…、p2n-1) にデータがありませんでした。同時に、偶数パーティションには大量のデータがあり、あまり均等ではありませんでした。次の図に示すように:

注意: 奇数パーティションの ibd ファイルサイズは 112k です。これはパーティションテーブルを作成するときの初期化サイズです。実際にはデータはありません。これは SQL で確認できます: select partition_name, partition_expression, table_rows from information_schema.partitions where table_schema = schema() and table_name='image_subpart' ;、結果の一部を次の図に示します。

問題を説明するには 10 万個のデータで十分ではないでしょうか?平均すると、各パーティションには約 800 個のデータが含まれています。さて、思い切ったことをしてみましょう。さらに 990w のデータを挿入して、合計 1kw のデータにします。結果は同じで、奇数パーティションにはデータがなく、偶数パーティションにはパーティションがあります。

考えるべき質問

KEY パーティショニングの原理を思い出してみましょう。MySQL に組み込まれているハッシュアルゴリズムを使用してシャードキーのハッシュ値を計算し、パーティション番号の係数を取得します。この原則は、MySQL の公式 Web サイトにも記載されています。リンクをクリックしてください: 22.2.5 KEY パーティショニング: https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html。元のテキストは次のとおりです:

キーによるパーティショニングはハッシュによるパーティショニングに似ていますが、ハッシュパーティショニングではユーザー定義の式が使用されるのに対し、キーパーティショニングのハッシュ関数は MySQL サーバーによって提供される点が異なります。NDB Cluster はこの目的で MD5() を使用します。他のストレージエンジンを使用するテーブルの場合、サーバーは PASSWORD() と同じアルゴリズムに基づく独自の内部ハッシュ関数を使用します。

**この世にそんなひどいハッシュアルゴリズムがあるはずがないですよね？ **どんなアルゴリズムを書いても、そんなに不均一にはならないですよね？現時点では、何らかの設定が原因となっているのではないかと思います。しかし、show variables にはパーティション関連の変数はありません。

その時、一万頭の馬が駆け抜けていった。ドキュメントとソースコードが同期していない可能性がありますか?さて、MySQL のソースコードを見てみましょう。結局のところ、ソースコードが真実に最も近いのです。 KEY パーティションに関連するソースコードは、sql_partition.cc ファイルにあります。著者は、以下に示すように、いくつかのキーソースコードを傍受しました。一見すると、何も問題はありません。まず、パーティションフィールドのハッシュ値を計算し、次にパーティション番号の係数を取得します。

/**
 (SUB)PARTITION BY KEYのpart_idを計算する
 @param ファイル ストレージエンジンへのハンドラ
 @param field_array PARTTION KEYのフィールドの配列
 @param num_parts KEYパーティションの数
 @param func_value[out] 計算されたハッシュ値を返す
 @return 計算されたパーティションID
*/
列をなして
静的 uint32 get_part_id_key(ハンドラー *ファイル、
               フィールド **field_array、
               uint num_parts、
               long long *func_value)
{
 DBUG_ENTER("パーツIDキーを取得");
 // パーティション フィールドのハッシュ値を計算します *func_value = file->calculate_key_hash_value(field_array);
 // パーティション数を法とする DBUG_RETURN((uint32) (*func_value % num_parts));
}

絶望して、検索エンジンで「不均一なKEYパーティションデータ」を検索してください。検索結果のCSDNフォーラム（https://bbs.csdn.net/topics/390857704）に、民俗学者のHua Xia Xiao Zu氏が次のように答えています。

同僚がパスワード機能を分析して測定したところ、キー分割の場合、各パーティションにデータが含まれるようにするには、パーティションの数を素数としてのみ指定できることが分かりました。 11 パーティションから 17 パーティションまでテストしました。パーティション 11、13、および 17 のデータのみが基本的に均等に分散されます。

その時、さらに一万頭の馬が駆け抜けていった。しかし、一体何事かと不思議に思いながらも、解決策を見つけたかもしれないと少し興奮していました (MySQL の組み込みハッシュアルゴリズムがなぜこのように動作するのかは、まだわかりませんが)。最終的に、KEY パーティションを再度テストし、次のように結論付けました。

パーティション番号を 40、64、128 (PARTITIONS 64) などの偶数に設定すると、奇数パーティション (p1、p3、p5、p7、… p2n-1) にデータを挿入できなくなります。
パーティション数を 63 や 121 などの奇数で素数でない数に設定すると (PARTITIONS 63)、すべてのパーティションにデータが含まれますが、不均等になります。
パーティションの数を 137 や 31 (PARTITIONS 137) などの素数に設定すると、すべてのパーティションにデータが格納され、非常に均等に間隔が空けられます。

下の図に示すように、これは著者がパーティションの数を 127 に調整し、100 万のデータを挿入した後の状況です。SQL は、各パーティションのデータ量がほぼ同じであることを証明しています。

まとめ

MySQL の KEY パーティションの使用に伴う大きな落とし穴について公式な説明がないことを知ってショックを受けました。さらに、MySQLのバグがあります: バグ#72428 KEY()によるパーティション分割により、データの分散が不均一になります

この記事を読んで強い関心を持っている学生は、この問題についてさらに深く掘り下げてみることができます。著者は、MySQL ソースコードを詳しく調べて、ハッシュアルゴリズムの実装がパーティションの数に非常に敏感である理由を調べる時間も設ける予定です。

これで、MySQL学習記録のKEYパーティションによって引き起こされた血なまぐさい事件に関するこの記事は終わりです。MySQL KEYパーティション血なまぐさい事件に関するより関連のある内容については、123WORDPRESS.COMの以前の記事を検索するか、以下の関連記事を引き続き閲覧してください。皆様、今後とも123WORDPRESS.COMを応援してください。

以下もご興味があるかもしれません: