大規模データセットのための MySQL パーティショニング

はじめに

この実験では、クエリのパフォーマンスとデータ管理を向上させるために、大規模データセットに対する 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 コマンドはこのセッションで実行してください。

テーブルを格納するために 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);

パーティション化されたテーブルを正常に作成し、データを挿入しました。次のステップでは、特定のパーティションからデータをクエリする方法を学びます。

特定のパーティションからデータをクエリする

このステップでは、特定のパーティションを対象とすることで、パーティション化されたテーブルから効率的にデータをクエリする方法を探ります。これはパーティショニングの主な利点の 1 つであり、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 を 2 つの新しいパーティションに分割します。1 つは 2024 年用 (p2024)、もう 1 つはそれ以降すべてを対象とする新しい 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;