MySQL パーティションテーブルのベストプラクティスガイド

序文：

パーティショニングはテーブル設計パターンです。一般的に、テーブル パーティショニングとは、条件に基づいて大きなテーブルを複数の小さなテーブルに分割することです。ただし、アプリケーションにとっては、パーティション化されたテーブルはパーティション化されていないテーブルと同じです。つまり、パーティショニングはアプリケーションに対して透過的であり、データベースによるデータの再編成にすぎません。この記事では、MySQL のパーティション テーブルとその使用シナリオを紹介します。困っている方は参考にしてください。お役に立てれば幸いです。

1. パーティションの目的と種類

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

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

以下では、一般的な 4 つのパーティション タイプについて簡単に説明します。

• RANGE パーティション分割: 最も一般的に使用され、指定された連続間隔に属する列の値に基づいて複数の行をパーティションに割り当てます。最も一般的なのは時間フィールドに基づいています。
• LIST パーティション: LIST パーティションは RANGE パーティションに似ていますが、LIST は列挙値リストのコレクションであり、RANGE は連続した間隔値のコレクションであるという違いがあります。
• HASH パーティション分割: テーブルに挿入される行の列値を使用して計算されるユーザー定義式の戻り値に基づいてパーティションが選択されます。この関数には、負でない整数値を生成する、MySQL で有効な任意の式を含めることができます。
• KEY パーティショニング: HASH パーティショニングと似ていますが、KEY パーティショニングでは 1 つ以上の列の計算のみがサポートされ、MySQL サーバーは独自のハッシュ関数を提供するという点が異なります。 1 つ以上の列に整数値が含まれている必要があります。
  

上記の 4 つのパーティション タイプのうち、RANGE パーティションが最もよく使用されます。 RANGE パーティションの特徴は、複数のパーティションの範囲が連続している必要があり、重複できないことです。デフォルトでは、VALUES LESS THAN 属性が使用され、つまり、各パーティションには指定された値が含まれません。

2. パーティション操作の例

このセクションでは、RANGE パーティション分割を例に、パーティション テーブルに関連する操作について説明します。

# パーティションテーブルを作成するmysql> CREATE TABLE `tr` (
 -> `id` INT, 
 -> `名前` VARCHAR(50), 
 -> `購入` 日付
 -> ) エンジン=InnoDB デフォルト文字セット=utf8
 -> 範囲によるパーティション(年(購入))(
 -> パーティション p0 の値が (1990) 未満の場合、
 -> パーティション p1 の値が (1995) 未満の場合、
 -> パーティション p2 の値が (2000) 未満である、
 -> パーティション p3 の値が (2005) 未満です。
 -> パーティション p4 の値が (2010) 未満です。
 -> パーティション p5 の値が (2015) 未満
 -> );
クエリは正常、影響を受けた行は 0 行 (0.28 秒)

# データを挿入mysql> INSERT INTO `tr` VALUES
 -> (1, 'デスクオーガナイザー', '2003-10-15'),
 -> (2, '目覚まし時計', '1997-11-05'),
 -> (3, '椅子', '2009-03-10'),
 -> (4, '本棚', '1989-01-10'),
 -> (5, 'エクササイズバイク', '2014-05-09'),
 -> (6, 'ソファ', '1987-06-05'),
 -> (7, 'エスプレッソメーカー', '2011-11-22'),
 -> (8, '水族館', '1992-08-04'),
 -> (9, '勉強机', '2006-09-16'),
 -> (10, '溶岩ランプ', '1998-12-25');
クエリは正常、10 行が影響を受けました (0.03 秒)
記録: 10 重複: 0 警告: 0

作成後、各パーティションが ibd ファイルに対応していることがわかります。上記の作成ステートメントは理解しやすいです。このパーティション テーブルでは、DATE 日付の年が YEAR 関数によって抽出され、整数に変換されます。1990 未満の年はパーティション p0 に格納され、1995 未満の年はパーティション p1 に格納されます。各パーティションは最低から最高の順に定義されていることに注意してください。挿入されたデータに対応するパーティションが見つからないためにエラーが報告されるのを防ぐには、時間内に新しいパーティションを作成する必要があります。以下に、パーティションのメンテナンスに関連するその他の操作を示します。

# パーティションのデータを表示します。mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+-------------+
| ID | 名前 | 購入 |
+------+-------------+-------------+
| 2 | 目覚まし時計 | 1997-11-05 |
| 10 | 溶岩ランプ | 1998-12-25 |
+------+-------------+-------------+
セット内の 2 行 (0.00 秒)

# パーティションを追加mysql> alter table tr addpartition(
 -> パーティション p6 の値が (2020) 未満
 -> );
クエリは正常、影響を受けた行は 0 行 (0.06 秒)
レコード: 0 重複: 0 警告: 0

# パーティションを分割mysql> alter table tr reorganize partition p5 into(
 -> パーティションs0の値が(2012)より小さい、
 -> パーティション s1 の値が(2015) より小さい
 -> );
クエリは正常、影響を受けた行は 0 行 (0.26 秒)
レコード: 0 重複: 0 警告: 0

# パーティションをマージmysql> alter table tr reorganize partition s0,s1 into ( 
 -> パーティションp5の値が小さい（2015） 
 -> );
クエリは正常、影響を受けた行は 0 行 (0.12 秒)
レコード: 0 重複: 0 警告: 0

# パーティションのデータをクリアします。mysql> alter table tr truncate partition p0;
クエリは正常、影響を受けた行は 0 行 (0.11 秒)

# パーティションを削除します。mysql> alter table tr drop part p1;
クエリは正常、影響を受けた行は 0 行 (0.06 秒)
レコード: 0 重複: 0 警告: 0

# 交換パーティション # まず、パーティションテーブルと同じ構造の交換テーブルを作成します。mysql> CREATE TABLE `tr_archive` (
 -> `id` INT, 
 -> `名前` VARCHAR(50), 
 -> `購入` 日付
 -> ) ENGINE=InnoDB デフォルト文字セット=utf8;
クエリは正常、影響を受けた行は 0 行 (0.28 秒)
# 交換パーティションを実行します。 mysql> alter table tr exchange PARTITION p2 with table tr_archive;
クエリは正常、影響を受けた行は 0 行 (0.13 秒)

3. パーティションの考慮事項と適用可能なシナリオ

実際、パーティション テーブルを使用する際には多くの制限や注意事項があります。公式ドキュメントを参照すると、いくつかのポイントが次のように簡単にまとめられています。

• パーティション フィールドは整数型または整数に解決される式である必要があります。
• パーティション フィールドを NOT NULL に設定することをお勧めします。データ行のパーティション フィールドが NULL の場合、RANGE パーティション分割では、データ行は最小のパーティションに分割されます。
• MySQL パーティショニングに主キーまたは一意キーがある場合は、パーティショニング列をそれに含める必要があります。
• Innodb パーティション テーブルは外部キーをサポートしません。
• sql_mode 設定を変更すると、パーティション化されたテーブルのパフォーマンスに影響する可能性があります。
• パーティション化されたテーブルは自動インクリメント列に影響しません。

上記の紹介から、パーティション テーブルが一部のログ テーブルに適していることがわかります。このタイプのテーブルは、大量のデータ、コールド データとホット データの区別、および時間ディメンションに従ったデータ アーカイブを特徴としています。このタイプのテーブルは、パーティション テーブルの使用に適しています。パーティション テーブルは個別のパーティションを維持できるため、データのアーカイブに便利です。

4. パーティションテーブルがあまり使われない理由

私たちのプロジェクト開発では、パーティション テーブルはほとんど使用されません。その理由はいくつかあります。

• パーティション フィールドの選択は制限されています。
• クエリでパーティション キーを使用しない場合、すべてのパーティションがスキャンされる可能性があり、効率は向上しません。
• データが不均一に分散され、パーティション サイズが大きく異なる場合、パフォーマンスの向上は制限される可能性があります。
• 通常のテーブルをパーティション化されたテーブルに変換するのはかなり複雑です。
• パーティションは継続的に維持する必要があります。たとえば、6 月の新しいパーティションは 6 月までに追加する必要があります。
• 学習コストの増加と未知のリスク。
  

要約:

この記事では、MySQL のパーティショニングについて詳しく紹介します。パーティション テーブルを使用する場合は、事前に計画を立て、初期化時にパーティション テーブルを作成し、メンテナンス プランを作成することをお勧めします。適切に使用すると非常に便利です。特に、履歴データのアーカイブが必要なテーブルの場合、パーティション テーブルを使用すると、アーカイブがより便利になります。もちろん、パーティション テーブルに関するコンテンツは他にもたくさんあります。興味のある学生は、多数の例が含まれている公式ドキュメントを参照してください。

参照:

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

https://www.jb51.net/article/187690.htm

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