MySQL のパーティションテーブルの詳細な紹介-mysql チュートリアル-php.cn

この記事では、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 つの戦略があります。

インデックスを使用せずにデータ全体をスキャンする: WHERE 条件を使用して必要なデータをいくつかのパーティションに制限できる限り、効率は非常に高くなります。この戦略を使用すると、データを完全にメモリに配置する必要がなく、必要なデータがすべてディスク上にあることも前提となります。メモリが比較的小さいため、データはすぐにメモリから押し出されるため、キャッシュは何の役割も果たしません。この戦略は、大量のデータが通常の方法でアクセスされる場合に適しています。
データと個別のホットスポットのインデックス作成: データに明らかな「ホットスポット」があり、データのこの部分を除いて他のデータにはほとんどアクセスされない場合、ホットスポットデータのこの部分を別のパーティションに配置して、このパーティション内のデータをメモリにキャッシュすることができます。このようなクエリは、小さなパーティション化されたテーブルにのみアクセスでき、インデックスを使用でき、キャッシュも効果的に使用できます。

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

#上で紹介した 2 つのパーティショニング戦略は、2 つの非常に重要な前提に基づいています。クエリはフィルタリングできる。追加のパーティションをたくさん追加したり、パーティション自体に余分なコストがかかることはありません。

これら 2 つの前提は、一部のシナリオでは問題となることが判明しました:

パーティション列とインデックス列が一致しません: 定義されている場合インデックス列とパーティション列が一致しないと、クエリによるパーティションフィルタリングの実行が失敗します。
パーティションを選択するコストは高くなる可能性があります。パーティションの種類が異なれば実装方法も異なるため、パフォーマンスは異なります。特に範囲パーティション化では、サーバーが正しい答えを見つけるためにすべてのパーティション定義のリストをスキャンする必要があるため、対象となる行がどのパーティションに属しているかをクエリするコストが非常に高くなる可能性があります。
基礎となるすべてのテーブルを開いてロックするコストは高くなる可能性があります: クエリがパーティションテーブルにアクセスするとき、MySQL はすべての基礎となるテーブルを開いてロックする必要があります。これもパーティション化されたテーブルのオーバーヘッドです。
パーティションの保守コストが高くなる可能性があります: パーティションの追加や削除など、一部のパーティション保守操作は非常に高速です。パーティションの再編成や同様の ALTER ステートメントなどの一部の操作では、データのコピーが必要となるため、非常にコストがかかる場合があります。

以上がMySQL のパーティションテーブルの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。