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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL パーティション テーブルに関するパフォーマンス バグ
  • MySQL テーブルパーティションの使用法と基本原理の詳細な説明
  • MySQL シャーディング入門ガイド
  • MySql テーブル、データベース、シャーディング、パーティショニングの知識の詳細な説明
  • MySQLパーティションテーブルの詳細な説明
  • MySQL ベストプラクティス: パーティションテーブルの基本タイプ
  • MySQL パーティション テーブルの基本入門チュートリアル
  • MySQL パーティションテーブルの正しい使用方法
  • MySQL パーティションテーブルの制限と制約の詳細な説明
  • MySQLはデータテーブル内の既存のテーブルを分割します

<<:  Vue ルーティングフォールバックに最適なソリューション (vue-route-manager)

>>:  Linux のネイティブ openjdk をアンインストールして sun jdk をインストールする方法

推薦する

MySQL 5.7 をインストールした後にコマンドライン ウィンドウを開くとクラッシュする問題の解決方法

序文最近、MySQL 5.7 をインストールしましたが、問題が見つかりました。コマンド ライン ウィ...

IE のテキストモード! DOCTYPE の役割の紹介

前の記事で説明したフォームの自動入力の問題を解決した後、新しい問題が発生しました。ページの一部のスタ...

Vue+canvas は、ウォーターフォール チャートを上から下までリアルタイムに更新する効果を実現します (QT と同様)

早速ですが、デモ画像をご紹介します。実装されている機能は、左側に凡例、右側にウォーターフォール チャ...

MySQL はカスタムシーケンスを使用して row_number 関数を実装します (詳細な手順)

いくつかの記事を読んだ後、ようやく MySQL で row_number() ソートを実装する方法が...

DCL を使用して MySQL でユーザーを管理し、権限を制御する方法

DCL (データ制御言語): データベースのアクセス権とセキュリティ レベルを定義し、ユーザーを作成...

Vueルーティングルーターの詳細な説明

目次ルーティングプラグインをモジュール方式で使用するルートの使用宣言型ナビゲーションプログラムによる...

Vueページジャンプの実装方法

1. this.$router.push() 1. ビュー <テンプレート> <d...

mysql5.7.14 解凍版インストールグラフィックチュートリアル

MySQL は、コミュニティ エディション (コミュニティ サーバー) とエンタープライズ エディシ...

メタタグを簡単に説明すると

META タグは、一般的に タグと呼ばれ、HTML Web ページのソース コード内の重要な HTM...

Linux 環境での Oracle 導入チュートリアル

1. 環境と関連ソフトウェア仮想マシン: VMwore Workstation Linuxシステム:...

proxy_pass がパス パスに従って転送する場合の "/" 問題の詳細な説明

nginx で proxy_pass を設定するときに、^~ に従ってパスを一致させる場合は、pro...

Linux コマンドラインのクイックヒント: ファイルの検索方法

私たちのコンピューターには、ディレクトリ、写真、ソース コードなどのファイルが保存されています。たく...

Centos 7 64 ビット デスクトップ バージョンのインストール グラフィック チュートリアル

システムが遅いと感じてソースを変更したい場合は、別の記事で整理しました https://blog.c...

MySQL データベースのインデックス順序の詳細な説明

目次事件の原因解剖学ファイルの並べ替えファイルのソートが非常に遅いのですが、他に解決策はありますか?...

HTML DOM入門_PowerNode Javaアカデミー

DOMとは何ですか? JavaScript を使用すると、HTML ドキュメント全体を再構築できます...