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

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 の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • SQLは、隣接する2行のデータに対して加算、減算、乗算、除算の演算を実行します。
  • 列の隣接する2つの行の差を計算するMySQLメソッド
  • MySqlで隣接データを取得する方法

<<:  画像の下部の空白部分の問題を解決する

>>:  ウェブサイトのコンテンツの100~1%はナビゲーションである

推薦する

Angular の 12 の典型的な問題について簡単に説明します

目次1. Angular 2 アプリケーションのライフサイクル フックとは何ですか? 2. Angu...

MySQL/MariaDB で完全な Unicode をサポートする方法

目次utf8mb4 の紹介UTF8 バイト数超過エラーutf8mb4 サポートデフォルトの文字エンコ...

HTML+CSS+JavaScript でガールフレンド版のスクラッチ カードを作成します (一度見ればすぐに覚えられます)

誰もがスクラッチ チケットで遊んだことがあると思います。子供の頃、ポケットにお金が入るとすぐに友達に...

CSS3 で半透明の背景画像と不透明なコンテンツを実現する方法の例

以前のブログのログインページを作成していたときに、この問題に遭遇しました。突然、透明な背景画像と不透...

React 並行関数エクスペリエンス (フロントエンド並行モード)

React は、開発者が Web およびモバイルベースのアプリケーションを作成するために使用するオ...

よくある Linux 英語エラーの中国語翻訳 (初心者必見)

1.コマンドが見つかりません コマンドが見つかりません2. そのようなファイルまたはディレクトリは...

CSS でのフレックスレイアウトの詳細な説明

フレックス レイアウトは、エラスティック レイアウトとも呼ばれます。任意のコンテナーをフレックス レ...

Linux Zabbixカスタム監視およびアラーム実装プロセスの分析

ターゲットzabbix フロントエンド監視の iostat コマンドでデータの 1 つを表示します。...

React antd タブの切り替えによりサブコンポーネントが繰り返し更新される

説明する: Tabs コンポーネントが切り替わると、TabPane に含まれる同じサブコンポーネント...

MySQL テーブルを削除するときに外部キー制約を無視するシンプルな実装

テーブルを削除することはあまり一般的ではありませんが、特に外部キーの関連付けがあるテーブルの場合は、...

Docker コンテナ入門から夢中になるまで(推奨)

1. Docker とは何ですか?仮想マシンについては誰もが知っています。Windows に Li...

Win10でIIS10を構成し、ASPプログラムのデバッグをサポートする手順

マイクロソフトIIS (Internet Information Server) は、Microso...

ウェブページを作るときに注意すべき5つのポイント

1. 色合わせの問題<br />Web ページには 3 色以上使用しないでください。そう...

WeChatアプレットの世界的な状況の詳細な説明

序文WeChat アプレットでは、App.js の globalData を中間ブリッジとして使用し...

タグ li はブロックレベル要素ですか?

なぜ高さを設定できるのでしょうか。<h1 /> などの要素とは異なり、「セミインライン」...