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