MySQL ベストプラクティス: パーティションテーブルの基本タイプ

MySQL パーティションテーブルの概要

MySQL の人気が高まるにつれて、MySQL に保存されるデータはますます大きくなっています。日常業務では、数億、あるいは数十億ものレコードを格納するテーブルに遭遇することがよくあります。これらのテーブルには大量の履歴レコードが保存されます。 すべてのデータが共通テーブルにあるため、これらの履歴データをクリーンアップするのは面倒です。したがって、削除するには、where 条件 (通常、where 条件は時間) を持つ 1 つ以上の delete ステートメントのみを有効にできます。 これにより、データベースに大きな負担がかかります。これらを削除しても、基礎となるデータ ファイルは小さくなりませんでした。このような問題に直面した場合、最も効果的な方法はパーティション テーブルを使用することです。最も一般的なパーティション分割方法は、時間でパーティション分割することです。 パーティショニングの最大の利点の 1 つは、履歴データを非常に効率的にクリーンアップできることです。

パーティションタイプ

現在、MySQL は、範囲パーティション (RANGE)、リスト パーティション (LIST)、ハッシュ パーティション (HASH)、および KEY パーティションの 4 種類のパーティションをサポートしています。各パーティションタイプを見てみましょう。

RANGE パーティション分割

指定された連続した間隔内にある列の値に基づいて、複数の行をパーティションに割り当てます。最も一般的なものは、時間フィールドに基づいています。パーティション ベースの列は、整数であることが望ましいです。日付型の場合は、関数を使用して整数に変換できます。この例では、to_days関数が使用されています

テーブルmy_range_datetime(を作成します。
 id INT、
 雇用日 日時
） 
範囲によるパーティション (TO_DAYS(採用日) ) (
 パーティション p1 の値が (TO_DAYS('20171202') より小さい)
 パーティション p2 の値が (TO_DAYS('20171203') より小さい)
 パーティション p3 の値が (TO_DAYS('20171204') より小さい)
 パーティション p4 の値が (TO_DAYS('20171205') より小さい)
 パーティション p5 の値が (TO_DAYS('20171206') より小さい)
 パーティション p6 の値が (TO_DAYS('20171207') より小さい)
 パーティション p7 の値が (TO_DAYS('20171208') より小さい)
 パーティション p8 の値が (TO_DAYS('20171209') より小さい)
 パーティション p9 の値が (TO_DAYS('20171210') より小さい)
 パーティション p10 の値が (TO_DAYS('20171211') より小さい)
 パーティション p11 の値は (MAXVALUE) より小さい 
);

p11 はデフォルトのパーティションであり、20171211 より大きいすべてのレコードがこのパーティションに含まれます。 MAXVALUE は無限値です。 p11 はオプションのパーティションです。テーブルを定義するときにこのパーティションが指定されていない場合、20171211 より大きいデータを挿入するときにエラーが発生します。

クエリを実行するときは、パーティション フィールドを含める必要があります。これにより、パーティショントリミング機能を使用できるようになります

mysql> my_range_datetime に挿入し、test から * を選択します。                                  
クエリは正常、1000000 行が影響を受けました (8.15 秒)
レコード: 1000000 重複: 0 警告: 0

mysql> explain パーティション select * from my_range_datetime where hiredate >= '20171207124503' and hiredate<='20171210111230'; 
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | possible_keys | キー | key_len | ref | 行 | 追加 |
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
| 1 | SIMPLE | my_range_datetime | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400061 | where の使用 |
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
セット内の1行（0.03秒）

実行プランのパーティションの内容に注意してください。クエリされるのは、p7、p8、p9、p10 の 3 つのパーティションのみです。このことから、to_days 関数を使用してパーティション プルーニングを実際に実行できることがわかります。

上記は datetime に基づいています。timestamp 型の場合、上記の問題が発生するとどうなるでしょうか?

実際、MySQL は UNIX_TIMESTAMP 関数に基づく RANGE パーティション分割スキームを提供しています。また、UNIX_TIMESTAMP 関数のみを使用できます。to_days などの他の関数を使用すると、次のエラーが報告されます: 「ERROR 1486 (HY000): (サブ) パーティション分割関数内の定数、ランダム、またはタイムゾーンに依存する式は許可されていません」。

また、公式ドキュメントには「TIMESTAMP 値を含むその他の式は許可されません。(バグ #42849 を参照してください。)」とも記載されています。

次に、UNIX_TIMESTAMP 関数に基づく RANGE パーティション分割スキームをテストして、パーティション プルーニングを実現できるかどうかを確認します。

TIMESTAMP のパーティション スキーム

テーブル作成ステートメントは次のとおりです。

テーブルmy_range_timestampを作成します（
  id INT、
  雇用日 タイムスタンプ
）
範囲によるパーティション ( UNIX_TIMESTAMP ( hiredate ) ) (
  パーティション p1 の値は ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ) 未満です。
  パーティション p2 の値が ( UNIX_TIMESTAMP('2017-12-03 00:00:00') 未満)、
  パーティション p3 の値が ( UNIX_TIMESTAMP('2017-12-04 00:00:00') 未満)
  パーティション p4 の値が ( UNIX_TIMESTAMP('2017-12-05 00:00:00') 未満)、
  パーティション p5 の値が ( UNIX_TIMESTAMP('2017-12-06 00:00:00') 未満)
  パーティション p6 の値が ( UNIX_TIMESTAMP('2017-12-07 00:00:00') 未満)、
  パーティション p7 の値は ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ) 未満です。
  パーティション p8 の値が ( UNIX_TIMESTAMP('2017-12-09 00:00:00') 未満)、
  パーティション p9 の値は ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ) 未満です。
  パーティション p10 の値は (UNIX_TIMESTAMP('2017-12-11 00:00:00') より小さい
);

データを挿入し、上記のクエリの実行プランを表示します。

mysql> my_range_timestamp に挿入し、test から * を選択します。
クエリは正常、1000000 行が影響を受けました (13.25 秒)
レコード: 1000000 重複: 0 警告: 0

mysql> explain パーティション select * from my_range_timestamp where hiredate >= '20171207124503' and hiredate<='20171210111230';
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | possible_keys | キー | key_len | ref | 行 | 追加 |
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
| 1 | SIMPLE | my_range_timestamp | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400448 | where の使用 |
+----+-------------+-------------------+--------------+-------+---------------+-------+--------+---------+------------+-------------+
セット内の 1 行 (0.00 秒)

パーティションのプルーニングも実現できます。

バージョン 5.7 より前では、DATA および DATETIME 型の列に対してパーティション プルーニングを実装する場合、YEAR() および TO_DAYS() 関数のみを使用できます。バージョン 5.7 では、TO_SECONDS() 関数が追加されました。

リストパーティション

リストパーティション

LIST パーティション分割は RANGE パーティション分割に似ていますが、LIST は列挙値リストのコレクションであり、RANGE は連続した間隔値のコレクションであるという違いがあります。これら 2 つの構文は非常に似ています。また、LIST パーティション列は非 NULL 列にすることをお勧めします。そうしないと、列挙リストに NULL 値がない場合に NULL 値の挿入が失敗します。これは他のパーティションとは異なります。RANGE パーティションはこれを最小パーティション値として保存し、HASH\KEY パーティションはこれを 0 に変換して保存します。主な理由は、LIST パーティションは整数のみをサポートし、非整数フィールドは関数を使用して整数に変換する必要があるためです。

テーブル t_list を作成します( 
  整数(11)、 
  b 整数(11) 
  )(リストによる分割(b) 
  パーティションp0の値を（1,3,5,7,9）に分割し、 
  パーティションp1の値は（2,4,6,8,0） 
  );

ハッシュパーティショニング

実際の仕事では、メンバーシップ テーブルのようなテーブルによく遭遇します。パーティション分割のための明確な特徴フィールドはありません。しかし、テーブルデータは非常に大きいです。このタイプのデータをパーティション分割するために、MySQL はハッシュ パーティション分割を提供します。指定されたパーティション数に基づいて、データは異なるパーティションに割り当てられます。HASH パーティション分割では、整数に対してのみ HASH を実行できます。非整数フィールドは、式を通じてのみ整数に変換できます。式は、MySQL で有効な関数または式にすることができます。非整数 HASH の場合、データをテーブルに挿入するときに、式計算の追加手順が発生します。したがって、パフォーマンスに影響するため、複雑な式の使用はお勧めしません。

ハッシュ パーティション テーブルの基本的なステートメントは次のとおりです。

テーブルmy_memberを作成します（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  作成日 NULL ではない デフォルト '1970-01-01'、
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
ハッシュによるパーティション(id)
パーティション4;

知らせ：

1. HASH パーティション分割では、PARTITIONS 句を指定する必要はありません。たとえば、上記のテキストで PARTITIONS 4 が指定されている場合、デフォルトのパーティション数は 1 です。
2. パーティションの数を指定せずに PARTITIONS を書き込むことはできません。
3. RANGE パーティション分割や LIST パーティション分割と同様に、PARTITION BY HASH (expr) 句の expr は整数値を返す必要があります。
4. HASH パーティショニングの基礎となる実装は、実際には MOD 関数に基づいています。例えば次の表の場合
   

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; col3 が "2017-09-15" のレコードを挿入する場合、パーティションの選択は次の値によって決まります。

MOD(YEAR('2017-09-01'),4) = MOD(2017,4) = 1

リニアハッシュパーティショニング

LINEAR HASH パーティション分割は、特殊なタイプの HASH パーティション分割です。MOD 関数に基づく HASH パーティション分割とは異なり、LINEAR HASH パーティション分割は別のアルゴリズムに基づいています。

形式は次のとおりです。

テーブルmy_membersを作成します（
  id INT NOT NULL、
  fname VARCHAR(30)、
  lname VARCHAR(30)、
  雇用日 NULL ではない デフォルト '1970-01-01',
  区切られた日付 NULL ではない デフォルト '9999-12-31'、
  ジョブコード INT、
  ストアID INT
）
線形ハッシュによるパーティション(id)
パーティション4;

注: 利点は、TB レベルなどの大量のデータを扱うシナリオで、パーティションの追加、削除、結合、分割が高速になることです。欠点は、HASH パーティションと比較して、データの分散が不均一になる可能性が高いことです。

KEYパーティション

KEY パーティション分割は実際には HASH パーティション分割に似ていますが、次の違いがあります。

1. KEY パーティションでは複数の列が許可されますが、HASH パーティションでは 1 つの列のみが許可されます。
2. 主キーまたは一意キーがある場合、キー内のパーティション列は未指定のままにすることができます。デフォルトは主キーまたは一意キーです。そうでない場合は、列を明示的に指定する必要があります。
3. KEY パーティション オブジェクトは、列に基づく式ではなく、列である必要があります。
4. KEY パーティション分割と HASH パーティション分割のアルゴリズムは異なります。PARTITION BY HASH (expr) では、MOD 値は expr によって返される値ですが、PARTITION BY KEY (column_list) では、列の MD5 値に基づきます。
   

形式は次のとおりです。

テーブルk1を作成します（
  id INT NOT NULL 主キー、  
  名前 VARCHAR(20)
）
キーによるパーティション()
パーティション 2;

主キーまたは一意キーがない場合、形式は次のようになります。

テーブルtm1を作成します（
  s1 文字(32)
）
キーによるパーティション(s1)
パーティション 10;

要約:

MySQL パーティショニングに主キーまたは一意キーがある場合は、パーティショニング列をそれに含める必要があります。

ネイティブ RANGE パーティション、LIST パーティション、および HASH パーティションの場合、パーティション オブジェクトは整数値のみを返すことができます。

パーティションフィールドはNULLにできません。そうしないとパーティション範囲が決定されないため、NOT NULLを使用するようにしてください。

これで、MySQL のベスト プラクティスとパーティション テーブルの基本タイプに関するこの記事は終了です。MySQL のパーティション テーブルの基本タイプの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。