MySQL データテーブルのパーティション戦略と利点と欠点の分析

なぜパーティションが必要なのでしょうか?

巨大なデータテーブルに直面した場合、少なくとも 1 つのことは確かです。それは、テーブルが非常に大きいため、クエリを実行するたびにテーブル全体をスキャンすることはできないということです。この時点では、インデックスは使用できないか、またはインデックスの重要性がほとんどなく、インデックスのメンテナンスコストと占有スペースが非常に高いことは言うまでもありません。インデックスに依存すると、断片化された低密度のデータが大量に生成され、クエリ中に何千ものランダム I/O アクセスが発生し、ダウンタイムが発生します。この場合、通常は 1 ～ 2 個のインデックスのみが使用され、それ以上は使用されません。この場合、2 つのオプションが考えられます。クエリは、テーブルの指定された部分またはデータの必要な部分から順番に検索する必要があり、そのインデックスはサーバーのメモリと一致する必要があります。

繰り返しになりますが、ストレージスペースが大きすぎる場合、インデックスがクエリ全体をカバーしない限り、バイナリツリーインデックスは機能しません。サーバーはデータテーブル内のデータの行全体を検索し、大きな領域の範囲でランダム I/O 操作を実行する必要があるため、クエリ応答時間が許容できないほど長くなります。インデックスの維持（ディスク領域、I/O 操作）にもコストがかかります。

これはパーティショニングによって解決できる問題です。ここで重要なのは、パーティショニングはインデックス作成の原始的な形式であり、オーバーヘッドが少なく、近くのデータから結果を取得できることです。この場合、隣接するデータを順番にスキャンするか、隣接するデータをメモリにロードして取得することができます。パーティションの負荷が低い理由は、対応するデータ行へのポインターがなく、更新する必要がないためです。パーティショニングでは、データを正確に行に分割するわけではなく、いわゆるデータ構造も使用されません。実際、パーティショニングはデータの分類と同等です。

パーティショニング戦略

大規模なデータテーブルの場合、パーティション分割には 2 つの戦略があります。

インデックスは使用されません: データテーブルを作成するときに、インデックスは追加されません。代わりに、パーティションを使用して必要なデータ行が検索されます。 WHERE 条件を使用してクエリを小さなパーティション範囲に分割するだけで十分です。現時点では、クエリ応答時間が許容可能かどうかを計算するには数学的手法が必要です。もちろん、ここでの前提は、データがメモリに格納されず、すべてのデータがディスクから読み取られることです。したがって、データは他のクエリによってすぐに上書きされ、キャッシュを使用する意味がほとんどありません。この状況は、データテーブルの基数が大きい場合によく発生します。パーティションの数は数百に制限する必要があることに注意してください。
インデックスを使用してホットゾーンデータを分離する: ホットゾーンデータ以外のデータのほとんどが使用されない場合は、ホットゾーンデータを個別にパーティション化し、このパーティションとインデックスをメモリにロードできます。このとき、通常のデータテーブルを操作するのと同じように、インデックスを使用してパフォーマンスを最適化できます。

パーティションの危険性

2 つのパーティション分割戦略は、クエリ中にパーティションをフィルタリングすることで検索範囲を絞り込むことができ、パーティション自体のコストは高くないという 2 つの重要な前提に基づいています。ただし、これら 2 つの仮定は必ずしも有効であるとは限りません。次のような問題が発生する可能性があります。

NULL 値によりパーティションフィルタリングが失敗する可能性があります。パーティション関数が NULL になる可能性がある場合、パーティション分割作業の結果は非常に奇妙なものになります。最初のパーティションは特別なものであると想定されます。 PARTITION BY RANGE YEAR(order_date) が使用されていると仮定すると、order_date 列が NULL または無効な日付の場合、最初のパーティションに格納されます。次のクエリ条件を使用してクエリを記述するとします: WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'。 MySQL は実際には 2 つのパーティションをチェックします。1 つは無効な入力を受け取ったときに NULL を返す可能性がある関数である YEAR 用で、もう 1 つは NULL になる可能性がある適格な値 (最初のパーティションに格納されている) 用です。これは、TO_DAYS などの他の関数でも可能です。最初のパーティションが大きい場合、特にインデックスなしの最初の戦略を使用する場合、問題が発生する可能性があります。 1 つのパーティションではなく 2 つのパーティションからデータを検索する効果はまったく予想外です。これを回避するには、たとえば PARTITION p_nulls VALUES LESS THAN (0) のように、「偽の」最初のパーティションを作成する必要があります。データテーブルに無効なデータが格納されていない場合、最初のパーティションは空になります。スキャンされますが、空であるか、データが非常に少ないため、パフォーマンスにほとんど影響はありません。 MySQL 5.5 以降では、列を直接パーティション分割に使用する場合はこの状況を処理する必要はありませんが、関数を使用する場合は処理する必要があります。
インデックスがパーティションと一致しません: パーティション条件と一致しないインデックスが定義されている場合、クエリはパーティションをフィルター処理できない可能性があります。フィールド a にインデックスが定義されているが、フィールド b がパーティション分割に使用されているとします。各パーティションには独自のインデックスがあるため、このインデックスに対するクエリはすべてのパーティションのインデックスツリーを走査します。インデックスツリーのリーフ以外のノードがすべてメモリに格納されている場合、クエリは高速になりますが、インデックス全体のスキャンを回避する方法はありません。この状況を回避するには、WHERE 条件自体でパーティションを指定できない限り、パーティション化されていないインデックス列の使用を避ける必要があります。これは簡単に避けられるように思えますが、実際には驚くべきことです。たとえば、パーティションテーブルが 2 番目のテーブルとの結合クエリで使用され、結合クエリで使用されるインデックスがパーティションインデックスではないとします。次に、ユニオンクエリの各行が 2 番目のテーブルのパーティションにアクセスしてスキャンします。
どのパーティションを使用するかを決定するのはコストがかかる可能性があります。パーティション分割はさまざまな方法で実装されるため、実際のパフォーマンスは常に一貫しているとは限りません。特に、「このデータ行はどのパーティションに属しているか」や「クエリ条件に一致するデータ行を見つけるにはどうすればよいか」などの質問に遭遇した場合。パーティションが非常に多い場合、このような質問に答えるのは困難です。線形検索は必ずしも効率的ではなく、結果としてパーティションの数が増えるにつれてコストが高くなります。最悪なのは、行ごとに挿入することです。パーティション化されたデータテーブルにデータ行が挿入されるたびに、サーバーは新しいデータ行を格納するために使用するパーティションを見つけるために 1 回スキャンする必要があります。この問題はパーティションの数を制限することで軽減できますが、実際には、パーティション数が 100 を超えることは推奨されません。もちろん、キーパーティションやハッシュパーティションなどの他のパーティションタイプにはこのような制限はありません。
パーティションを開いてロックするのもコストがかかる場合があります。パーティション化されたテーブルの副作用の 1 つは、クエリで各パーティションを開いてロックする必要があることです。このプロセスは、パーティションをフィルタリングする前に実行されます。このコストはパーティションタイプとは無関係であり、すべての操作ステートメントに影響します。この効果は、1 行のデータのみをクエリする場合など、データ量が少ないクエリで特に顕著になります。この欠陥は、一度に複数の行を挿入する、LOAD DATA INFILE、一度に範囲別にデータを削除するなど、単一の操作ではなくバッチ操作を実行することで軽減できます。もちろん、パーティションの数を制限することも効果的です。
メンテナンス操作にはコストがかかる場合があります。パーティションの作成や削除など、一部のパーティションメンテナンスは短時間で完了します。パーティションの調整などのその他の操作は、テーブルに対する ALTER 操作に少し似ており、ループとデータ行のコピーが必要です。たとえば、パーティションのサイズを変更すると、一時的なパーティションが作成され、データが新しいパーティションに移動されてから、古いパーティションが削除されます。

上で述べたように、パーティショニングは完璧な解決策ではありません。現在のバージョンの MySQL には、他にもいくつかの制約があります。

すべてのパーティションは同じストレージエンジンを使用する必要があります。
パーティション関数として使用できる関数または式には、一定の制限があります。
一部のストレージエンジンはパーティション分割をサポートしていません。
MYISAM データテーブルの場合、LOAD INDEX INTO CACHE は使用できません。
MYISAM データテーブルの場合、パーティション化されたテーブルでは、より多くのオープンファイル記述子が必要になります。つまり、単一のデータテーブルキャッシュエントリが複数のファイル記述子に対応する可能性があります。したがって、基本構成では、サーバーオペレーティングシステムの前処理量を超えないようにデータテーブルのキャッシュが制限され、パーティションテーブルでは実際にこの制限を超える可能性があります。

もちろん、MySQL バージョンの更新と反復により、パーティショニングのサポートはますます向上し、多くのパーティショニングの問題が修正されました。

上記は、MySQL データテーブルのパーティショニング戦略と長所と短所の分析の詳細な内容です。MySQL データテーブルのパーティショニング戦略と長所と短所の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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