MySQL のパーティション テーブルの詳細な紹介

この記事では、MySQL のパーティション テーブルについて詳しく説明します。必要な方は参考にしていただければ幸いです。

ユーザーにとって、パーティション テーブルは独立した論理テーブルですが、下部にある複数の物理サブテーブルで構成されています。パーティショニングを実装するコードは、実際には、基礎となるテーブルのセットのハンドル オブジェクトのカプセル化であり、パーティション テーブルに対するリクエストは、ハンドル オブジェクトを通じてストレージ エンジンへのインターフェイス呼び出しに変換されます。

意味

MySQL は、テーブルの作成時に PARTITION BY 句を使用して、各パーティションに格納されるデータを定義できます。クエリを実行するとき、オプティマイザはパーティション定義に基づいて必要なデータを持たないパーティションをフィルタリングするため、クエリですべてのパーティションをスキャンする必要はなく、必要なデータを含むパーティションのみを見つけることができます。

パーティショニングの主な目的の 1 つは、より粗い粒度で異なるテーブルにデータを格納することです。これにより、関連するデータをまとめて保存できるほか、パーティション全体のデータを一度に一括削除したい場合にも非常に便利です。

パーティショニングは、次のシナリオで大きな役割を果たす可能性があります。

• テーブルが大きすぎるため、すべてをメモリに配置することも、テーブルのみに配置することもできません。 最後の部分ホットスポット データがあり、残りは履歴データです

• パーティション テーブル データは保守が容易です

• パーティション テーブル データは物理的に異なる場所に分散できますデバイス

• パーティション テーブルを使用して特定のボトルネックを回避できます

• 必要に応じて、独立したパーティションをバックアップおよび復元できます

パーティション テーブル自体にもいくつかの 制限があります。次の点が特に重要です:

• テーブルには最大 1024 個しか含めることができません。 Partition

• MySQL5.1 では、パーティション式は整数、または整数を返す式である必要があります。 MySQL5.5 では、一部のシナリオでカラムをパーティショニングに直接使用できます

• パーティション化されたテーブルでは外部キー制約を使用できません

• パーティショニングの場合フィールドに主キー列または一意のインデックス列がある場合は、すべての主キー列と一意のインデックス列を含める必要があります

パーティション テーブルの原則

ストレージ エンジンによるパーティション内の各基礎テーブルの管理と通常のテーブルの管理には違いはありません (すべての基礎テーブルが同じストレージ エンジンを使用する必要があります)
パーティション テーブルのインデックスは追加するだけです。基礎となる各テーブルと同一のインデックス。ストレージ エンジンの観点からは、基礎となるテーブルと通常のテーブルの間に違いはなく、ストレージ エンジンは、それが通常のテーブルであるかパーティション テーブルの一部であるかを認識する必要はありません。

パーティション テーブルに対する操作は、次の操作ロジックに従って実行されます。

SELECT クエリ

パーティション テーブルをクエリする場合、最初にパーティション レイヤーが開き、下部のすべてがロックされます。レイヤー テーブルを使用する場合、オプティマイザはまず一部のパーティションをフィルタリングできるかどうかを判断し、次に対応するストレージ エンジン インターフェイスを呼び出して各パーティションのデータにアクセスします。

INSERT 操作

レコードを書き込むとき、パーティションはレイヤー まず、基になるすべてのテーブルを開いてロックし、次にどのパーティションがレコードを受け取るかを決定し、対応する基になるテーブルにレコードを書き込みます。

DELETE 操作

レコードが削除されると、パーティションはレイヤーは最初に基になるすべてのテーブルを開いてロックし、次にデータに対応するパーティションを決定し、最後に対応する基になるテーブルを削除します。

UPDATE 操作

レコードを更新するときは、パーティション レイヤーが最初に開きます。そして、MySQL はすべての基礎となるテーブルをロックします。まず、レコードを更新する必要があるパーティションを決定し、次にデータを取り出して更新し、次に更新されたデータをどのパーティションに配置するかを決定し、最後に基礎となるテーブルに書き込み、元のデータが存在する基礎となるテーブルを削除します。

これらの操作はフィルタリングをサポートしています。

各操作は「まず基礎となるすべてのテーブルを開いてロックします」が、この は、 の処理中にパーティション テーブルがテーブル全体をロックすることを意味するものではありません。ストレージ エンジンが独自に行レベルのロックを実装できる場合、対応するテーブル ロックはパーティション レベルで解放されます。このロックおよびロック解除のプロセスは、通常の InnoDB のクエリと似ています。

パーティション テーブルの種類

MySQL は、さまざまなパーティション テーブルをサポートしています。最も一般的なのは、各パーティション ストレージが特定の範囲内にあることです。 。 の記録。パーティション式は、列または列を含む式にすることができます。

たとえば、次のテーブルでは、各年の売上がさまざまなパーティションに格納されています。

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION パーティション句ではさまざまな関数を使用できます。ただし、要件があります。 式によって返される値は、明確な整数である必要があり、定数であってはなりません。

MySQL は、キー値、ハッシュ、リストのパーティショニングなどもサポートしています。

パーティション テーブルの使用方法

非常に大きなテーブルから一定期間のレコードをクエリしたい場合、このテーブルをどのようにクエリすればよいのか、またどのようにクエリを実行できるのかを説明します。より効率的になりますか？

データの量が非常に大きいため、クエリを実行するたびにテーブル全体をスキャンすることはできません。インデックスのスペースとメンテナンスの消費を考慮すると、インデックスは使用したくありません。インデックスを使用した場合でも、データが望ましい方法で集約されていないことがわかり、その結果、大量の断片化が発生し、最終的にクエリで数千のランダム I/O が生成されることになります。実際、データ量が非常に多い場合、B-Tree インデックスは機能しなくなります。

そのため、大量のデータに対応するメタデータの小さな部分のみにインデックスを付けるなど、より粗く、より低コストでデータを取得する方法を選択できます。

これはまさにパーティショニングが行うことです。パーティショニングを理解することは、インデックスの初期形式とみなすことができます。 パーティションは、各パーティションにデータを記録するために追加のデータ構造を必要としないため、パーティションは各データの位置を正確に特定する必要がないため、追加のデータ構造も必要ありません。そのため、コストが非常に低くなります。 。各パーティションにどのようなデータが格納されているかを表現するには、単純な式のみが必要です。

大量のデータのスケーラビリティを確保するには、通常 2 つの戦略があります。

1. インデックスを使用せずにデータ全体をスキャンする: WHERE 条件を使用して必要なデータをいくつかのパーティションに制限できる限り、効率は非常に高くなります。この戦略を使用すると、データを完全にメモリに配置する必要がなく、必要なデータがすべてディスク上にあることも前提となります。メモリが比較的小さいため、データはすぐにメモリから押し出されるため、キャッシュは何の役割も果たしません。この戦略は、大量のデータが通常の方法でアクセスされる場合に適しています。

2. データと個別のホット スポットのインデックス作成: データに明らかな「ホット スポット」があり、データのこの部分を除いて他のデータにはほとんどアクセスされない場合、ホットスポット データのこの部分を別のパーティションに配置して、このパーティション内のデータをメモリにキャッシュすることができます。このようなクエリは、小さなパーティション化されたテーブルにのみアクセスでき、インデックスを使用でき、キャッシュも効果的に使用できます。

#どのような状況で問題が発生しますか

• パーティション列とインデックス列が一致しません: 定義されている場合インデックス列とパーティション列が一致しないと、クエリによるパーティション フィルタリングの実行が失敗します。

• パーティションを選択するコストは高くなる可能性があります。 パーティションの種類が異なれば実装方法も異なるため、パフォーマンスは異なります。特に範囲パーティション化では、サーバーが正しい答えを見つけるためにすべてのパーティション定義のリストをスキャンする必要があるため、対象となる行がどのパーティションに属しているかをクエリするコストが非常に高くなる可能性があります。

• 基礎となるすべてのテーブルを開いてロックするコストは高くなる可能性があります: クエリがパーティション テーブルにアクセスするとき、MySQL はすべての基礎となるテーブルを開いてロックする必要があります。これもパーティション化されたテーブルのオーバーヘッドです。

• パーティションの保守コストが高くなる可能性があります: パーティションの追加や削除など、一部のパーティション保守操作は非常に高速です。パーティションの再編成や同様の ALTER ステートメントなどの一部の操作では、データのコピーが必要となるため、非常にコストがかかる場合があります。