mysqlで日付パーティションのテーブル作成

スポンサーリンク
mysqlで日付パーティションのテーブル作成 環境構築
mysqlで日付パーティションのテーブル作成
この記事は約12分で読めます。
よっしー
よっしー

こんにちは。よっしーです(^^)

今日は、mysqlで日付パーティションのテーブル作成する方法についてご紹介します。

スポンサーリンク

背景

mysqlの日付パーティションを調査した際に、検証環境の構築したときの内容を備忘として残しました。

この記事のソースは下記のリポジトリにあります。

作成ファイル一覧

下記のファイルを作成しました。ファイルの内容は下記のセクションに記載しています。

        new file:   aws/work/01_create_table.sql
        new file:   aws/work/02_alter_partition.sql
        new file:   aws/work/03_insert.sql
        new file:   aws/work/04_select_partition.sql
        modified:   compose.yml

compose.yml

   aws:
     build: ./aws
     container_name: aws
+    volumes:
+      - ./aws/work:/work
     networks:
       - net

aws/work/01_create_table.sql


CREATE TABLE test(
    `id`      INT UNSIGNED AUTO_INCREMENT            COMMENT 'ID'
,   `name`    TEXT                                   COMMENT '名称'
,   `date_at` DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '日付'
,   PRIMARY KEY(`id`, `date_at`)
);

aws/work/02_alter_partition.sql


ALTER TABLE `test` PARTITION BY RANGE COLUMNS(`date_at`) (
    PARTITION  p20231015 VALUES LESS THAN ('2023-10-16 00:00:00')
,   PARTITION  p20231016 VALUES LESS THAN ('2023-10-17 00:00:00')
);

aws/work/01_create_table.sql


INSERT INTO test(`name`, `date_at`)
VALUES ('テストデータ1',  '2023-10-15 16:00:01')
     , ('テストデータ2',  '2023-10-15 16:00:02')
     , ('テストデータ3',  '2023-10-15 16:00:03')
     , ('テストデータ4',  '2023-10-15 16:00:04')
     , ('テストデータ5',  '2023-10-15 16:00:05')
     , ('テストデータ6',  '2023-10-15 16:00:06')
     , ('テストデータ7',  '2023-10-15 16:00:07')
     , ('テストデータ8',  '2023-10-15 16:00:08')
     , ('テストデータ9',  '2023-10-15 16:00:09')
     , ('テストデータ10', '2023-10-15 16:00:10')
;

aws/work/04_select_partition.sql


SELECT TABLE_SCHEMA
     , PARTITION_NAME
     , PARTITION_ORDINAL_POSITION
     , TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME =  'test'
;

動作確認

下記のコマンドを実行します。

make build
make up
make login_aws
cd /work

下記のコマンドでテーブルを作成します。

bash-5.2# mysql -hmysql -uroot -p -Dsample < 01_create_table.sql
Enter password: P@ssw0rd

下記のコマンドでパーティションを作成します。

bash-5.2# mysql -hmysql -uroot -p -Dsample < 02_alter_partition.sql
Enter password: P@ssw0rd

下記のコマンドでデータを登録します。

bash-5.2# mysql -hmysql -uroot -p -Dsample < 03_insert.sql
Enter password: P@ssw0rd

下記のコマンドでパーティションの情報を取得します。

bash-5.2# mysql -hmysql -uroot -p -Dsample < 04_select_partition.sql
Enter password: P@ssw0rd

下記の結果になっていれば成功です。

TABLE_SCHEMA    PARTITION_NAME  PARTITION_ORDINAL_POSITION      TABLE_ROWS
sample  p20231015       1       10
sample  p20231016       2       0

解説

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function (prefixed columns are not considered).

このエラーは、MySQLデータベースにおいて、テーブルのパーティショニング(分割)が設定されている場合に発生するエラーです。エラーメッセージ “A PRIMARY KEY must include all columns in the table’s partitioning function” は、テーブルの主キー(PRIMARY KEY)に関連しています。このエラーが発生する主な理由は次のとおりです:

  1. パーティショニング関数と主キーの不一致: テーブルがパーティショニングキーを使用して分割されている場合、そのパーティションキーのすべての列は主キーに含まれる必要があります。つまり、パーティショニングキーのすべての列が主キーの一部である必要があります。エラーメッセージは、この一貫性が保たれていない場合に発生します。
  2. パーティショニングの誤った設定: パーティショニングを正しく設定しなかった場合、エラーが発生することがあります。テーブルのパーティショニングキーを選択し、それを主キーと関連付けることが重要です。
  3. パーティショニングキーのプレフィックス列: パーティショニングキーの一部である列が主キーの一部でない場合、エラーが発生します。パーティショニングキーのすべての列が主キーに含まれている必要があります。

エラーを解決するためには、次の手順を検討することが役立つでしょう:

  1. パーティショニングキーと主キーを再検討し、パーティショニングキーのすべての列を主キーに含めるように設計し直します。
  2. パーティショニングの設定を確認し、正しく設定されていることを確認します。
  3. パーティショニングキーのプレフィックス列が主キーの一部でないことを確認します。

テーブルの構造とパーティショニングの設定を確認し、設定に問題がある場合は修正して、このエラーを解決できるでしょう。

explain

下記のコマンドを実行して、パーティションが利用されていることが確認できます。

bash-5.2# mysql -hmysql -uroot -p -Dsample -e 'explain select `name` from test where date_at = "2023-10-15 16:00:04" \G;'
Enter password: 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: p20231015
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where

日付パーティション

MySQLにおける日付パーティションは、大量のデータを日付ごとに分割し、データの管理およびパフォーマンスの向上を実現するための機能です。日付パーティショニングは、特に時間ベースのデータを効率的に格納するのに役立ちます。以下に、MySQLにおける日付パーティショニングについての基本的な情報を提供します。

  1. パーティショニングの利点:
    • 高速なクエリ処理: パーティショニングを使用することで、クエリのパフォーマンスが向上します。特定の日付範囲にアクセスする際に、必要なパーティションのみをスキャンするためです。
    • データの効率的な保持: 過去のデータをアーカイブする場合、古いパーティションを削除するだけで済みます。
    • 容易なデータ管理: パーティションごとにデータを分割するため、データの管理が容易になります。
  2. 日付パーティショニングの作成: 日付パーティショニングを作成するには、以下の手順を実行します。
    • パーティショニングキーとして使用する日付カラムを含むテーブルを作成します。
    • パーティション定義を設定し、どのようにテーブルを日付ごとに分割するかを指定します。例えば、月ごと、年ごとに分割することができます。
  3. データの挿入と検索: パーティショニングを設定すると、データの挿入や検索は通常のテーブルと同じ方法で行えます。MySQLは自動的に適切なパーティションにデータを挿入し、クエリを実行する際に適切なパーティションのみをスキャンします。
  4. パーティションのメンテナンス: 古いデータをアーカイブするために、不要なパーティションを削除したり、新しいパーティションを追加したりすることができます。これにより、データベースのパフォーマンスと管理が向上します。

日付パーティショニングは、特にログデータ、センサーデータ、タイムシリーズデータなど、時間に依存するデータを効果的に管理するための優れた方法です。データベースの設計とクエリの最適化において役立つ手法の一つです。

CREATE TABLE log_data (
    log_id INT AUTO_INCREMENT,
    log_timestamp DATETIME
) PARTITION BY RANGE (YEAR(log_timestamp)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

この例では、log_timestampカラムを使用してテーブルを年ごとに分割しています。2020年までのデータはp0パーティションに、2021年までのデータはp1パーティションに格納されます。

aws/work/04_select_partition.sql

このSQLクエリは、MySQLデータベース内のパーティションテーブルに関する情報を取得するためのものです。具体的には、テーブル名が ‘test’ であるテーブルの各パーティションに関する情報を取得します。以下はクエリ内の各部分の説明です:

  1. SELECT TABLE_SCHEMA, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS: これは、取得するカラムを指定しています。
    • TABLE_SCHEMA: パーティションテーブルが所属しているデータベース(スキーマ)の名前を示します。
    • PARTITION_NAME: パーティションの名前を示します。各パーティションは一意の名前を持ちます。
    • PARTITION_ORDINAL_POSITION: パーティションの順序を示します。たとえば、1、2、3、… というように連続的な整数が割り当てられます。
    • TABLE_ROWS: 各パーティション内の行数(レコード数)を示します。
  2. FROM INFORMATION_SCHEMA.PARTITIONS: データを取得するテーブルを指定しています。INFORMATION_SCHEMA は、MySQLが提供するシステムデータベースで、データベースのメタ情報にアクセスするための情報が格納されています。PARTITIONS テーブルは、パーティショニングに関する情報を提供します。
  3. WHERE TABLE_NAME = 'test': フィルタ条件です。ここでは、TABLE_NAME カラムが ‘test’ であるテーブルの情報のみを取得するように指定しています。つまり、’test’ という名前のテーブルに関する情報だけを抽出します。

このクエリは、特定のテーブル(ここでは ‘test’ テーブル)のパーティション情報を取得するために使用されます。パーティショニングは、大規模なデータセットを効率的に管理し、クエリのパフォーマンスを向上させるのに役立つ機能であり、このような情報を取得することはデータベースのモニタリングや管理に役立ちます。

おわりに

今日は、mysqlで日付パーティションのテーブル作成する方法についてご紹介しました。

よっしー
よっしー

何か質問や相談があれば、コメントをお願いします。また、エンジニア案件の相談にも随時対応していますので、お気軽にお問い合わせください。

それでは、また明日お会いしましょう(^^)

コメント

タイトルとURLをコピーしました