MySQL_MySQL のテーブルパーティショニングテクノロジの詳細な分析

MySQL パーティショニング テクノロジ (mysql 5.1 以降で使用 -> Oracle mysql 技術チームのメンテナによってプラグインの形式で mysql に挿入されたテクノロジです)

1. 概要

単一のデータベーステーブルが特定のサイズに達すると、パフォーマンスが低下します。これは mysqlsql サーバーなどでは明らかなので、これらのデータはパーティション分割する必要があります。同時に、データの削除が発生する場合もあるため、パーティション テーブルはさらに便利です。

MySQL 5.1 の新しいパーティション機能が増加し始めており、その利点がますます明らかになってきています:

1. 単一のディスクまたはファイル システム パーティションよりも多くのデータを保存できます
2. 未使用または古いデータを簡単に削除
3. 一部のクエリは大幅に最適化できます
4. SUM()/COUNT()などの集計関数に関しては、並列実行可能です
5. IO スループットの向上
6. パーティショニングにより、任意のサイズに設定できるルールを使用して、単一テーブルの複数の部分をファイル システム全体に割り当てることができます。実際には、テーブルの異なる部分が別のテーブルとして異なる場所に保存されます。
   

2. パーティションのテクニカルサポート

5.6 より前では、このパラメータを使用して、現在の構成がパーティショニングをサポートしているかどうかを確認します:

リーリー

「はい」の場合、現在の構成がパーティショニングをサポートしていることを意味します。 5.6 以降で採用された後は、次の方法を使用して表示します:

リーリー

セット内の 42 行 (0.00 秒) 最後の行では、パーティションが ACTIVE であることがわかり、パーティショニングがサポートされていることを示します。

3. パーティションの種類と例

3.1 レンジパーティション

RANGE パーティション化: 指定された連続範囲に属する列値に基づいて、複数の行をパーティションに割り当てます。時間、連続定数値など -- 年ごとに区切る

リーリー

3.2 パーティションの一覧表示

LIST パーティショニング: RANGE によるパーティショニングと似ていますが、異なる点は、LIST パーティショニングは、離散値セット内の特定の値に一致する列値に基づいて選択されることです。たとえば、性別(1,2)などの属性値。

リーリー

リストに文字を使用すると、エラー エラー 1697 (HY000) が発生することに注意してください。パーティション 'psex1' の VALUES 値は INT 型でなければなりません。

3.3 個別パーティション

ハッシュ パーティション化: テーブルに挿入される行の列値を使用して計算されるユーザー定義式の戻り値に基づいて選択されるパーティション。この関数には、負でない整数値を生成する MySQL の有効な式を含めることができます。

--int フィールド ハッシュによるパーティション化

リーリー

--時間によるハッシュパーティション関数

リーリー

3.4 キーと値のパーティション

KEY パーティショニング: HASH によるパーティショニングと似ていますが、異なる点は、KEY パーティショニングでは 1 つ以上の列の計算のみがサポートされ、MySQL サーバーが独自のハッシュ関数を提供することです。整数値を含む列が 1 つ以上存在する必要があります。 その分割方法はハッシュと非常によく似ています:

リーリー

3.5 その他の指示

mysql-5.5 は、RANGE パーティションと LIST パーティションの進化と見なすことができる COLUMNS パーティションのサポートを開始し、非整数データを使用して直接パーティション化できます。 COLUMNS パーティションは次のデータ型をサポートします: INT SMALLINT TINYINT BIGINT などのすべての整数。 FLOAT と DECIMAL はサポートされていません。 DATE や DATETIME などの日付型。他の日付タイプはサポートされていません。 CHAR、VARCHAR、BINARY、VARBINARY などの文字列型。 BLOB 型と TEXT 型はサポートされていません。 COLUMNS は複数の列を使用してパーティション化できます。

リーリー

概要:

パーティションテーブルはMySQL5.1の新機能です。MySQL5.1.22-rc時点ではパーティショニング技術はあまり成熟しておらず、多くのパーティションの維持管理機能が実装されていません。たとえば、パーティション内のデータ ストレージ領域の回復、パーティションの修復、パーティションの最適化などです。MySQL パーティションはパーティションごとに削除できるテーブルで使用でき、データベースへの変更操作は大きくなく、パーティションに基づいたクエリが可能です。フィールドは頻繁に実行されます (たとえば、悪意のあるコード内の統計テーブルは日ごとにパーティション化されており、頻繁にクエリされ、時間に従ってグループ化されており、パーティションは日ごとに削除できます)。さらに、MySQL にはグローバル インデックスがなくパーティション インデックスしかないため、テーブルに 2 つの一意のインデックス [z5] がある場合、テーブルをパーティション化することはできず、パーティション カラムには主キーが含まれている必要があります。そうしないと、MySQL がエラーを報告します。

要するに、MySQL にはパーティショニングに関する制限が多く、ハッシュとキーのパーティショニングは実際にはあまり意味がないと個人的には考えています。

パーティショニングにより、クエリを最適化する新しい方法が導入されます (もちろん、相応の欠点もあります)。オプティマイザーはパーティション関数を使用してパーティションをトリミングしたり、クエリからパーティションを完全に削除したりできます。この最適化は、データが特定のパーティションで見つかるかどうかを推測することによって実現されます。したがって、最良の場合、トリミングによってクエリがアクセスできるデータが少なくなります。冗長に見えるかもしれませんが、WHERE 句でパーティション キーを定義することが重要です。パーティション キーを使用して、オプティマイザは未使用のパーティションを削除できます。そうでない場合、実行エンジンはマージ テーブルのようにテーブルのすべてのパーティションにアクセスすることになり、大きなテーブルでは非常に遅くなります。パーティション分割されたデータは、パーティション分割されていないデータよりも保守が容易で、古いデータはパーティションを削除することで削除できます。パーティション化されたデータをさまざまな物理的な場所に分散できるため、サーバーは複数のハード ドライブをより効率的に使用できます。

[z1] パーティション関数の戻り値は整数である必要があります。新しいパーティションのパーティション関数の戻り値は、既存のパーティションのパーティション関数の戻り値より大きくなければなりません。
[z2] 主キーを持つテーブルのエラー メッセージ: #1503
主キーにはテーブルのパーティショニング関数のすべての列が含まれなければなりません。主キーがない場合、そのような制約はありません
[z3] 注: RANGE によってパーティション化されたテーブルの場合、ADD PARTITION を使用してパーティション リストの上位に新しいパーティションを追加することのみが可能です。つまり、このパーティションの範囲より小さいパーティションを追加することはできません。
[z4] RANGE に従ってパーティション化されたテーブルの場合、隣接するパーティションのみを再編成できます。RANGE パーティションをスキップすることはできません。 REORGANIZEPARTITION を使用してテーブルのパーティション タイプを変更することはできません。たとえば、RANGE パーティションを HASH パーティションに、またはその逆に変更することはできません。また、このコマンドを使用してパーティション式または列を変更することもできません。
【z5】主キーと一意インデックスの違いに注意

公式情報: https://dev.mysql.com/doc/refman/5.5/en/partitioning.html