MySQL の高度な機能 - データテーブルパーティショニングの概念とメカニズムの詳細な説明

MySQL のパーティショニングはデータテーブルをラップすることによって実装されます。つまり、インデックスは実際にはテーブル全体ではなく各パーティションに基づいて定義されます。この機能は、インデックスとテーブルをより柔軟かつ複雑な方法でパーティション分割できる Oracle とは異なります。

MySQL パーティショニングでは、PATITION BY 句の条件を定義することによって、データ行が属するパーティションを決定します。クエリを実行する際、クエリオプティマイザーはパーティションを区別します。つまり、クエリはすべてのパーティションをチェックするのではなく、要求されたデータを含むパーティションのみをチェックします。

パーティショニングの主な目的は、データテーブルを大まかにインデックス付けしてクラスタ化することです。これにより、広範囲のデータテーブルにアクセスし、関連するデータ行を近くに格納する必要性が軽減されます。パーティション分割の利点は、特に次のシナリオで顕著です。

データテーブルが大きすぎてメモリに収まらない場合、またはデータテーブルに履歴データとホットゾーンの行が多数ある場合。
パーティション化されたデータは、パーティション化されていないデータよりも保守が容易です。たとえば、パーティション全体を削除することで古いデータを簡単に消去できるほか、個々のパーティションを簡単に最適化、チェック、修復することもできます。
パーティショニングにより、データをストレージ全体に物理的に分散できるため、サーバーは複数のハードドライブをより効率的に使用できるようになります。
パーティショニングを使用すると、特定のワークロードのボトルネックを回避できます。
データのバックアップでは、個々のパーティションを個別にバックアップまたは復元できるため、大規模なデータセットに非常に便利です。

MySQL パーティショニングの実装の詳細は非常に複雑で、理解するのが困難です。パフォーマンスにのみ焦点を当てる必要があります。さらに詳しく知りたい場合は、MySQL マニュアルのパーティショニングに関するセクションをお読みください。パーティショニングには、他にも次のような問題や制限があります。

テーブルを作成および変更するコマンドはより複雑です。
各テーブルには最大 1024 個のパーティションを含めることができます。
MySQL 5.1 では、パーティショニング式は整数であるか、整数を返す必要があります。MySQL 5.5 以降では、場合によっては列をパーティショニングに使用できます。
主キーまたは一意のインデックスには、パーティション式のすべての列が含まれている必要があります。
外部キー制約は使用できません。

パーティション分割メカニズム

前述したように、パーティションテーブルには実際には複数の隠し物理ストレージテーブルがあり、それらはハンドルオブジェクトを通じて表示されます。パーティションに直接アクセスすることはできません。通常、各パーティションはストレージエンジンによって管理され (したがって、すべてのパーティションに同じストレージエンジンが必要になります)、データテーブル内のインデックスは、実際には非表示の物理ストレージテーブルのインデックスです。ストレージエンジンの観点から見ると、パーティションもデータテーブルです。ストレージエンジンは、データテーブルが独立しているか、またはより大きなデータテーブルのパーティションであるかを実際には認識しません。パーティションテーブルに対する操作は、次の論理操作を通じて実装されます。

SELECTクエリ

パーティション化されたテーブルをクエリする場合、パーティションレイヤーはすべての非表示パーティションを開いてロックし、クエリオプティマイザーはどの非表示パーティションを無視できるかを決定し、パーティションレイヤーはハンドル API を通じてパーティションを管理するストレージエンジンを呼び出してクエリ結果を取得します。

INSERT操作

データ行が挿入されると、パーティションレイヤーはすべてのパーティションを開いてロックし、現在のデータ行が格納されているパーティションを決定して、対応するパーティションにデータ行を格納します。

削除操作

データ行を削除する場合、パーティショニングレイヤーはすべてのパーティションを開いてロックし、どのパーティションにデータ行が含まれているかを確認し、そのパーティションに削除要求を送信します。

更新操作

データ行を変更する場合、パーティショニングレイヤーはすべてのパーティションを開いてロックし、どのパーティションにデータ行が含まれているかを確認し、変更するデータ行を取得して、どのパーティションに新しいデータ行を含めるかを決定し、そのパーティションに挿入要求を送信し、古いパーティションに削除要求を送信します。
上記の操作の一部は、パーティションフィルタリング (つまり、無関係なパーティションを無視する) をサポートしています。たとえば、行を削除する場合、サーバーはまずデータ行を見つける必要があります。 WHERE 条件に一致するパーティション式条件が指定されている場合、サーバーは行が含まれていないパーティションを無視できます。 UPDATE 操作でも同様であり、INSERT 操作でも同様です。サーバーは、挿入するパーティションをすべてではなく、1 つのパーティションのみを検索します。

パーティションレイヤーはすべてのパーティションを開いてロックしますが、パーティションがロックされたままになるわけではありません。 InnoDB などのストレージエンジンは行レベルのロックをサポートしており、パーティションレベルでのみパーティションのロックを解除します。ロックおよびロック解除のプロセスは、通常の InnoDB データテーブルのロックプロセスと同様です。

パーティションの種類

MySQL はいくつかのタイプのパーティショニングをサポートしています。最も一般的に使用されるタイプは範囲パーティショニングで、特定の列の異なる範囲の値または式によってパーティショニングします。たとえば、次のステートメントは、売上データを年ごとに異なるパーティションに分割します。

テーブルsalesを作成する（
  order_date DATETIME NULLではない
  --その他の列定義) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
  パーティション p_2018 値が (2018) より小さい、
  パーティション p_2019 値が (2019) より小さい、
  パーティション p_2020 の値が (2020) より小さい、
  PARTITION p_other の値が MAXVALUE 未満です);

パーティション句ではさまざまな関数を使用できます。主な要件は、非定数の決定論的な整数を返す必要があることです。上記の例では YEAR 関数が使用されていますが、TO_DAYS() などの他の関数も使用できます。日付ベースのデータの場合、パーティション分割に時間間隔を使用するのが一般的なアプローチです。

MySQL はキー、ハッシュ、リストのパーティショニング方法もサポートしており、一部の MySQL ではサブパーティショニングもサポートされています (実際にはほとんど使用されません)。 MySQL 5.5 以降では、日付を整数に変換する関数を使用せずに、RANGE COLUMNS パーティションタイプを使用して、日付ベースの列で直接パーティション分割できます。その他の一般的なパーティション分割手法には次のものがあります。

InnoDB ミューテックスの競合を減らすためにパーティション分割にキーを使用します。
モジュロ計算方式を使用して、範囲パーティションをループすることができます。たとえば、過去数日間のデータのみを保持する必要がある場合は、日付または曜日ごとにモジュロ 7 を取得してパーティション分割することができます。
データテーブルに自動増分主キーがないが、クラスター化されたホットゾーンデータもパーティション分割するとします。タイムスタンプが主キーにないため、タイムスタンプパーティション分割は使用できません。このとき、HASH(id DIV 1000000) を使用すると、1,000,000 行ごとにデータをパーティション分割できます。これにより、主キーを変更せずに目的の効果を実現できます。これには追加の効果もあります。つまり、新しいデータを保持するためにパーティションの定数を作成する必要はありません。

上記は、MySQL の高度な機能であるデータテーブルパーティションの概念とメカニズムの詳細な内容です。MySQL の高度な機能であるデータテーブルパーティションの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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