こんにちは。よっしーです(^^)
今日は、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.ymlcompose.yml
aws:
build: ./aws
container_name: aws
+ volumes:
+ - ./aws/work:/work
networks:
- netaws/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）に関連しています。このエラーが発生する主な理由は次のとおりです：
- パーティショニング関数と主キーの不一致: テーブルがパーティショニングキーを使用して分割されている場合、そのパーティションキーのすべての列は主キーに含まれる必要があります。つまり、パーティショニングキーのすべての列が主キーの一部である必要があります。エラーメッセージは、この一貫性が保たれていない場合に発生します。
- パーティショニングの誤った設定: パーティショニングを正しく設定しなかった場合、エラーが発生することがあります。テーブルのパーティショニングキーを選択し、それを主キーと関連付けることが重要です。
- パーティショニングキーのプレフィックス列: パーティショニングキーの一部である列が主キーの一部でない場合、エラーが発生します。パーティショニングキーのすべての列が主キーに含まれている必要があります。
エラーを解決するためには、次の手順を検討することが役立つでしょう：
- パーティショニングキーと主キーを再検討し、パーティショニングキーのすべての列を主キーに含めるように設計し直します。
- パーティショニングの設定を確認し、正しく設定されていることを確認します。
- パーティショニングキーのプレフィックス列が主キーの一部でないことを確認します。
テーブルの構造とパーティショニングの設定を確認し、設定に問題がある場合は修正して、このエラーを解決できるでしょう。
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における日付パーティショニングについての基本的な情報を提供します。
- パーティショニングの利点:
- 高速なクエリ処理: パーティショニングを使用することで、クエリのパフォーマンスが向上します。特定の日付範囲にアクセスする際に、必要なパーティションのみをスキャンするためです。
- データの効率的な保持: 過去のデータをアーカイブする場合、古いパーティションを削除するだけで済みます。
- 容易なデータ管理: パーティションごとにデータを分割するため、データの管理が容易になります。
- 日付パーティショニングの作成: 日付パーティショニングを作成するには、以下の手順を実行します。
- パーティショニングキーとして使用する日付カラムを含むテーブルを作成します。
- パーティション定義を設定し、どのようにテーブルを日付ごとに分割するかを指定します。例えば、月ごと、年ごとに分割することができます。
- データの挿入と検索: パーティショニングを設定すると、データの挿入や検索は通常のテーブルと同じ方法で行えます。MySQLは自動的に適切なパーティションにデータを挿入し、クエリを実行する際に適切なパーティションのみをスキャンします。
- パーティションのメンテナンス: 古いデータをアーカイブするために、不要なパーティションを削除したり、新しいパーティションを追加したりすることができます。これにより、データベースのパフォーマンスと管理が向上します。
日付パーティショニングは、特にログデータ、センサーデータ、タイムシリーズデータなど、時間に依存するデータを効果的に管理するための優れた方法です。データベースの設計とクエリの最適化において役立つ手法の一つです。
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’ であるテーブルの各パーティションに関する情報を取得します。以下はクエリ内の各部分の説明です：
SELECT TABLE_SCHEMA, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS: これは、取得するカラムを指定しています。TABLE_SCHEMA: パーティションテーブルが所属しているデータベース（スキーマ）の名前を示します。PARTITION_NAME: パーティションの名前を示します。各パーティションは一意の名前を持ちます。PARTITION_ORDINAL_POSITION: パーティションの順序を示します。たとえば、1、2、3、… というように連続的な整数が割り当てられます。TABLE_ROWS: 各パーティション内の行数（レコード数）を示します。
FROM INFORMATION_SCHEMA.PARTITIONS: データを取得するテーブルを指定しています。INFORMATION_SCHEMAは、MySQLが提供するシステムデータベースで、データベースのメタ情報にアクセスするための情報が格納されています。PARTITIONSテーブルは、パーティショニングに関する情報を提供します。WHERE TABLE_NAME = 'test': フィルタ条件です。ここでは、TABLE_NAMEカラムが ‘test’ であるテーブルの情報のみを取得するように指定しています。つまり、’test’ という名前のテーブルに関する情報だけを抽出します。
このクエリは、特定のテーブル（ここでは ‘test’ テーブル）のパーティション情報を取得するために使用されます。パーティショニングは、大規模なデータセットを効率的に管理し、クエリのパフォーマンスを向上させるのに役立つ機能であり、このような情報を取得することはデータベースのモニタリングや管理に役立ちます。
おわりに
今日は、mysqlで日付パーティションのテーブル作成する方法についてご紹介しました。
何か質問や相談があれば、コメントをお願いします。また、エンジニア案件の相談にも随時対応していますので、お気軽にお問い合わせください。
それでは、また明日お会いしましょう(^^)
コメント