はじめに
この実験では、大規模データセットのクエリ性能とデータ管理を改善するために、MySQL のパーティショニングを実装する方法を学びます。sales テーブルの sale_date 列を使い、範囲でテーブルをパーティション分割します。
まず MySQL サーバーに接続し、sales_data データベースを作成します。次に sales テーブルを作成し、sale_date の年に基づいて、2020、2021、2022、2023 年のパーティションと、それ以降の日付を格納するパーティションに分割します。その後、特定のパーティションからデータを取得する方法、ALTER TABLE を使ってパーティションを再編成する方法、パーティショニングがクエリ速度に与える影響を確認する方法を学びます。
注意： この実験では、最初に一度だけ MySQL シェルに入り、最後に終了します。以降の手順に記載されている SQL コマンドは、同じ MySQL セッション内で実行してください。
パーティション分割したテーブルを作成する
このステップでは、MySQL にデータベースとパーティション分割したテーブルを作成します。パーティショニングでは、指定したルールに基づいてテーブルを小さく扱いやすい単位に分割するため、大規模データセットを管理しやすくなります。特に、パーティションキーでデータを絞り込むクエリでは、クエリ性能が大幅に向上する場合があります。
まず、LabEx VM でターミナルを開きます。すでに ~/project ディレクトリにいることを確認してください。
root ユーザーとして MySQL サーバーに接続します。この操作は実験の最初に一度だけ実行してください。
sudo mysql -u root
MySQL シェルが開きます。実験が終わるまで、以降の SQL コマンドはすべてこのセッション内で実行してください。
セッションを開いている間、ステップのチェックでこのセッションに入力した SQL を読み取れるよう、SQL の記録を有効にします。
tee /home/labex/project/partitioning-session.log
実験が終わるまで、SQL の記録を有効にしたままにしてください。ここで入力した SQL は、ターミナルシェルの履歴には記録されません。
テーブルを格納する sales_data データベースを作成します。
CREATE DATABASE sales_data;
作成したデータベースに切り替えます。
USE sales_data;
次に、sales という名前のテーブルを作成し、sale_date 列の年に基づいてパーティション分割します。2020、2021、2022、2023 年の各パーティションと、それ以降の日付を格納するパーティションを作成します。
CREATE TABLE sales (
sale_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
PARTITION BY RANGE 句の内容を確認しましょう。
PARTITION BY RANGE (YEAR(sale_date))：sale_date列にYEAR()関数を適用して得られる値の範囲に基づいて、テーブルをパーティション分割します。PARTITION p2020 VALUES LESS THAN (2021)：p2020というパーティションを作成します。sale_dateの年が 2021 より小さい行（つまり 2020 年の行）が、このパーティションに格納されます。PARTITION p2021 VALUES LESS THAN (2022)：2021 年のデータを格納するp2021パーティションを作成します。PARTITION p2022 VALUES LESS THAN (2023)：2022 年のデータを格納するp2022パーティションを作成します。PARTITION p2023 VALUES LESS THAN (2024)：2023 年のデータを格納するp2023パーティションを作成します。PARTITION pFuture VALUES LESS THAN MAXVALUE：pFutureというパーティションを作成し、sale_dateの年が 2024 以上のデータを格納します。MAXVALUEは、ほかのどの値よりも大きい特別な値です。
CREATE TABLE 文を実行したら、次のコマンドでテーブルの構造とパーティションを確認します。
SHOW CREATE TABLE sales;
出力に PARTITION BY RANGE 句が表示され、指定したパーティションが作成されていることを確認してください。
次に、sales テーブルにサンプルデータを挿入します。MySQL は sale_date に基づいて、各行を対応するパーティションに自動で格納します。
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);
これで、パーティション分割したテーブルを作成し、データを挿入できました。次のステップでは、特定のパーティションからデータを取得する方法を学びます。
特定のパーティションからデータを取得する
このステップでは、特定のパーティションを対象にクエリを実行し、パーティション分割したテーブルから効率よくデータを取得する方法を確認します。パーティショニングの大きな利点の一つは、MySQL が関連するパーティションだけをスキャンできることです。これにより処理するデータ量が大幅に減り、クエリ性能が向上します。
確認： 引き続き MySQL シェル内で、sales_data データベースを使用してください。そうでない場合は、次のコマンドを実行します。
USE sales_data;
特定のパーティションのデータを取得するには、パーティションキーを絞り込む WHERE 句を指定します。MySQL のクエリオプティマイザは、WHERE 句に基づいて関連するパーティションを特定できる場合が多くあります。
たとえば、2021 年の売上をすべて取得するには、次のクエリを実行します。ここでは sale_date に対する範囲条件を直接指定しています。WHERE 句で YEAR(sale_date) のような関数を使うと、MySQL がパーティションプルーニングを実行できず、すべてのパーティションをスキャンする可能性があります。
SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
このクエリで MySQL がどのパーティションにアクセスするかは、EXPLAIN PARTITIONS 文で確認できます。
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
EXPLAIN PARTITIONS の出力では、partitions 列を確認してください。p2021 と表示されていれば、このクエリの処理で MySQL がスキャンするのは p2021 パーティションだけです。
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2021 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
複数のパーティションにまたがるデータも取得できます。たとえば、2022 年と 2023 年の売上データを取得するには、次のクエリを実行します。
SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
続けて EXPLAIN PARTITIONS を実行すると、MySQL が p2022 と p2023 の両方にアクセスすることを確認できます。
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
partitions 列には p2022,p2023 と表示されます。
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2022,p2023 | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
この結果から、クエリの実行時に MySQL が不要なパーティションを除外（プルーニング）することで、処理を高速化できることが分かります。特に、テーブル全体をスキャンすると時間がかかる大規模なテーブルで効果を発揮します。
各パーティションの行数は、INFORMATION_SCHEMA.PARTITIONS テーブルを使って確認できます。
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
このクエリを実行すると、各パーティションにデータがどのように分布しているかを確認できます。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 1 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 1 |
+----------------+------------+
これで、特定のパーティションからデータを取得し、MySQL がクエリの最適化にパーティショニングを利用する方法を確認できました。
パーティションを再編成して管理する
このステップでは、ALTER TABLE 文を使って既存のテーブルのパーティション構造を変更する方法を学びます。データの増加や要件の変化に応じて、パーティションの構成を調整するときに役立ちます。
確認： 引き続き MySQL シェル内で、sales_data データベースを使用してください。そうでない場合は、次のコマンドを実行します。
USE sales_data;
2024 年用の新しいパーティションを追加するとします。現在、2024 年以降のデータは pFuture パーティションに格納されています。pFuture は VALUES LESS THAN MAXVALUE で定義されており、常に最後のパーティションでなければならないため、ADD PARTITION では新しいパーティションを追加できません。
そこで、pFuture パーティションを分割するために REORGANIZE を使います。pFuture を、2024 年用の p2024 パーティションと、それ以降のデータを格納する新しい pFuture パーティションに分割します。
ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
このコマンドは既存の pFuture パーティションを再編成します。2024 年のデータは新しい p2024 パーティションに移動し、pFuture は 2025 年以降の日付を対象とするように定義し直されます。sale_date が '2024-01-01' の行は p2024 に移動します。
パーティション構造と各パーティションの行数を確認します。
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
新しい p2024 パーティションが表示されることを確認してください。2024 年の行は p2024 に移動しています。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 0 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
次に、パーティションを結合します。p2020 と p2021 を p2020_2021 という一つのパーティションにまとめるとします。
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
このコマンドは、p2020 と p2021 のデータを p2020_2021 という新しいパーティションにまとめます。VALUES LESS THAN (2022) 句で、結合後のパーティションの境界を定義します。
もう一度、パーティション構造を確認します。
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
p2020 と p2021 がなくなり、両方の行数を合計した p2020_2021 が表示されます。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
最後に、パーティションを削除します。ここでは p2024 パーティションを削除します。この操作では、そのパーティション内のデータもすべて削除される点に注意してください。
ALTER TABLE sales DROP PARTITION p2024;
最後に、パーティション構造を確認します。
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
p2024 パーティションが一覧に表示されなくなっていることを確認してください。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 0 |
+----------------+------------+
これで、ALTER TABLE を使ってパーティションを再編成、結合、削除できました。データの変化に応じて、パーティション分割したテーブルを柔軟に管理できることが分かります。
クエリ速度へのパーティショニングの影響を確認する
このステップでは、パーティショニングがクエリ性能に与える影響を確認します。現在のデータセットは小規模ですが、MySQL が必要なパーティションだけをスキャンするパーティションプルーニングの仕組みを確認できます。大規模なデータセットでは、この効果がより顕著になります。
確認： 引き続き MySQL シェル内で、sales_data データベースを使用してください。そうでない場合は、次のコマンドを実行します。
USE sales_data;
パーティショニングの影響を確認するには、クエリの実行計画を表示する EXPLAIN 文を使います。特に EXPLAIN PARTITIONS を使うと、アクセスされるパーティションを確認できます。
パーティションキー（sale_date の年）で絞り込むクエリを実行します。
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
出力の partitions 列を確認してください。スキャン対象が p2023 パーティションだけであることが表示されます。
次に、パーティションキーではなく、別の列（amount）で絞り込むクエリを実行します。
EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;
このクエリ条件はパーティションキー（sale_date）を直接対象としていないため、一致する行を見つけるために MySQL が複数またはすべてのパーティションをスキャンする場合があります。EXPLAIN PARTITIONS の出力にある partitions 列で、どのパーティションが対象になったかを確認できます。データセットが小さいため、すべてのパーティションがスキャンされる場合もあります。
クエリの実行過程と所要時間をより詳しく確認するには、MySQL のプロファイリング機能を使います。
プロファイリングを有効にします。
SET profiling = 1;
続けて、先ほどの 2 つのクエリをもう一度実行します。
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;
プロファイリングの結果を表示します。
SHOW PROFILES;
出力には、実行したクエリとその所要時間が表示されます。Query_ID を指定すると、特定のクエリの詳細を確認できます。
SHOW PROFILE FOR QUERY [Query_ID];
[Query_ID] を、SHOW PROFILES の出力にある分析対象のクエリ ID に置き換えてください。実行の各段階と、それぞれにかかった時間を確認します。
小規模なデータセットでは時間の差がほとんどない場合があります。しかし、実際の環境で数百万行を扱う場合、パーティションプルーニングを利用できるクエリ（YEAR(sale_date) で絞り込むクエリなど）は、複数またはすべてのパーティションをスキャンするクエリより大幅に高速になります。
最後に、プロファイリングを無効にします。
SET profiling = 0;
このステップでは、EXPLAIN PARTITIONS とプロファイリングを使って、パーティショニングがクエリの実行や性能に与える影響を確認しました。
まとめ
この実験では、大規模データセットのクエリ性能とデータ管理を改善するために、MySQL のパーティショニングを実装する方法を学びました。まず、日付列の年に基づいて範囲でパーティション分割するデータベースとテーブルを作成しました。次に、特定のパーティションからデータを取得し、MySQL がパーティションプルーニングを使ってクエリを最適化する様子を確認しました。最後に、ALTER TABLE 文を使ってパーティションを追加、分割、結合して再編成する方法を学び、EXPLAIN PARTITIONS とプロファイリングを使って、パーティショニングがクエリ速度に与える影響を調べました。パーティショニングは、MySQL で大規模なテーブルを効率よく管理し、クエリを実行するための有効な手法です。
すべてのステップが終わったら、次のコマンドを入力して MySQL シェルを終了します。
exit;
