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;

知らせ：

HASH パーティション分割では、PARTITIONS 句を指定する必要はありません。たとえば、上記のテキストで PARTITIONS 4 が指定されている場合、デフォルトのパーティション数は 1 です。
パーティションの数を指定せずに PARTITIONS を書き込むことはできません。
RANGE パーティション分割や LIST パーティション分割と同様に、PARTITION BY HASH (expr) 句の expr は整数値を返す必要があります。
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 パーティション分割に似ていますが、次の違いがあります。

KEY パーティションでは複数の列が許可されますが、HASH パーティションでは 1 つの列のみが許可されます。
主キーまたは一意キーがある場合、キー内のパーティション列は未指定のままにすることができます。デフォルトは主キーまたは一意キーです。そうでない場合は、列を明示的に指定する必要があります。
KEY パーティションオブジェクトは、列に基づく式ではなく、列である必要があります。
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 をよろしくお願いいたします。

以下もご興味があるかもしれません: