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

はじめに

この実験では、大規模データセットに対して MySQL のパーティショニングを実装し、クエリ性能とデータ管理を改善する方法を学びます。この実験では、範囲によるテーブルのパーティショニングに焦点を当てており、具体的には sales テーブルの sale_date 列を使用します。

まず、MySQL サーバーに接続し、sales_data データベースを作成します。次に、sales テーブルを作成し、sale_date の年に基づいて、2020 年、2021 年、2022 年、2023 年のパーティションと、将来のパーティションに分割します。その後の手順では、特定のパーティションからデータをクエリする方法、ALTER TABLE を使用してパーティションを再編成する方法、およびパーティショニングがクエリ速度に与える影響を確認する方法を説明します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("Table Modification") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/date("Date Type") subgraph Lab Skills mysql/use_database -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} mysql/create_database -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} mysql/create_table -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} mysql/alter_table -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} mysql/select -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} mysql/date -.-> lab-550912{{"大規模データセットのための MySQL パーティショニング"}} end

範囲によるテーブルのパーティショニング

このステップでは、MySQL で範囲によってテーブルをパーティショニングする方法を学びます。範囲によるパーティショニングは、日付、ID、またはその他の数値データなどの値の範囲に基づいてデータを論理的なグループに分割したい場合に便利です。これにより、クエリ性能が向上し、データ管理が容易になります。

まず、MySQL サーバーに接続しましょう。LabEx VM でターミナルを開きます。すでに ~/project ディレクトリにいるはずです。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。root パスワードを設定していない場合は、Enter キーを押してください。

次に、sales_data という名前のデータベースを作成しましょう。

CREATE DATABASE sales_data;
USE sales_data;

次に、sale_id、sale_date、および amount の列を持つ sales という名前のテーブルを作成します。このテーブルを sale_date 列でパーティショニングします。

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
)
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
);

この CREATE TABLE 文を分解してみましょう。

PARTITION BY RANGE (YEAR(sale_date)): これは、sale_date 列の年を使用して、テーブルを範囲によってパーティショニングすることを指定します。
PARTITION p2020 VALUES LESS THAN (2021): これは、sale_date の年が 2021 未満（つまり、2020 年）のすべての行を含む p2020 という名前のパーティションを作成します。
PARTITION p2021 VALUES LESS THAN (2022): これは、sale_date の年が 2022 未満（つまり、2021 年）のすべての行を含む p2021 という名前のパーティションを作成します。
PARTITION p2022 VALUES LESS THAN (2023): これは、sale_date の年が 2023 未満（つまり、2022 年）のすべての行を含む p2022 という名前のパーティションを作成します。
PARTITION p2023 VALUES LESS THAN (2024): これは、sale_date の年が 2024 未満（つまり、2023 年）のすべての行を含む p2023 という名前のパーティションを作成します。
PARTITION pFuture VALUES LESS THAN MAXVALUE: これは、sale_date の年が 2024 以上のすべての行を含む pFuture という名前のパーティションを作成します。MAXVALUE は、将来の年もこのパーティションに含まれることを保証します。

次に、sales テーブルにいくつかのサンプルデータを挿入しましょう。

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);

データが正しいパーティションに挿入されたことを確認するには、EXPLAIN PARTITIONS 文を使用できます。例えば：

EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date BETWEEN '2021-01-01' AND '2021-12-31';

これにより、MySQL がクエリを実行するために使用するパーティションが表示されます。p2021 パーティションのみを使用することがわかるはずです。

また、次のクエリを使用して、各パーティションの行数を確認することもできます。

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

このクエリは、sales_data データベース内の sales テーブルの各パーティションの名前と行数を取得します。

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          1 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| pFuture        |          1 |
+----------------+------------+

これにより、データが sale_date に基づいて正しくパーティショニングされたことが確認されます。

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

このステップでは、MySQL の特定のパーティションからデータをクエリする方法を学びます。特定の期間や値の範囲のデータのみを取得する必要がある場合、これは非常に有用です。なぜなら、MySQL がスキャンする必要のあるデータ量を制限することで、クエリ性能を大幅に向上させることができるからです。

まず、MySQL サーバーに接続し、sales_data データベースを使用していることを確認します。前のステップで接続を閉じた場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、sales_data データベースを選択します。

USE sales_data;

次に、p2021 パーティションからデータをクエリしましょう。これは、パーティショニングキーに基づいてデータをフィルタリングする WHERE 句を追加することで行えます。この場合、パーティショニングキーは sale_date の年です。

SELECT * FROM sales WHERE YEAR(sale_date) = 2021;

このクエリは、sale_date の年が 2021 である sales テーブルのすべての行を取得します。テーブルは年でパーティショニングされているため、MySQL はこのクエリを実行するために p2021 パーティションのみをスキャンする必要があります。

これを EXPLAIN PARTITIONS 文を使用して確認できます。

EXPLAIN PARTITIONS SELECT * FROM sales WHERE YEAR(sale_date) = 2021;

この文の出力は、MySQL がクエリを実行するために使用するパーティションを示します。p2021 パーティションのみを使用することがわかるはずです。

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | sales | p2021      | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------+

ご覧の通り、partitions 列に p2021 と表示されており、p2021 パーティションのみにアクセスされたことを示しています。

また、WHERE 句で IN 演算子を使用することで、複数のパーティションからデータをクエリすることもできます。

SELECT * FROM sales WHERE YEAR(sale_date) IN (2021, 2022);

このクエリは、sale_date の年が 2021 または 2022 である sales テーブルのすべての行を取得します。MySQL はこのクエリを実行するために p2021 と p2022 の両方のパーティションをスキャンします。

再び、EXPLAIN PARTITIONS を使用してこれを確認できます。

EXPLAIN PARTITIONS SELECT * FROM sales WHERE YEAR(sale_date) IN (2021, 2022);

出力は、p2021 と p2022 の両方のパーティションにアクセスされることを示します。

+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | partitions    | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | sales | p2021,p2022   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                       |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-----------------------------------+

特定のパーティションからデータをクエリすることで、MySQL が処理する必要のあるデータ量を大幅に削減でき、クエリの実行時間を短縮することができます。これは、数百万または数十億の行を持つ大規模なテーブルに特に有益です。

ALTER TABLE でパーティションを再編成する

このステップでは、MySQL の ALTER TABLE 文を使用してパーティションを再編成する方法を学びます。パーティションを再編成することは、パーティションの統合、分割、または新しいパーティションの追加など、様々な理由で有用です。

まず、MySQL サーバーに接続し、sales_data データベースを使用していることを確認します。前のステップで接続を閉じた場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、sales_data データベースを選択します。

USE sales_data;

まず、2024 年用の新しいパーティションを追加しましょう。現在、2024 年以降のすべての販売データは pFuture パーティションに格納されています。ALTER TABLE 文と ADD PARTITION 句を使用して、2024 年用の特定のパーティションを作成できます。

ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));

この文は、sale_date の年が 2025 未満（つまり、2024 年）のすべての行を含む p2024 という名前の新しいパーティションを追加します。パーティションを追加した後、pFuture パーティションを再編成して 2024 年を除外する必要があります。

ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

このコマンドは、pFuture パーティションを p2025 と新しい pFuture パーティションに分割します。p2025 は 2025 年のデータを含み、新しい pFuture は 2026 年以降のデータを含みます。

次に、パーティション構造を確認しましょう。

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

出力には、新しい p2024、p2025 および更新された pFuture パーティションが表示されるはずです。

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020          |          1 |
| p2021          |          2 |
| p2022          |          2 |
| p2023          |          2 |
| p2024          |          1 |
| p2025          |          0 |
| pFuture        |          0 |
+----------------+------------+

次に、パーティションを統合する方法を見てみましょう。p2020 と p2021 パーティションを p2020_2021 という名前の単一のパーティションに統合したいとします。これは、ALTER TABLE 文と REORGANIZE PARTITION 句を使用して行うことができます。

ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p2020_2021 VALUES LESS THAN (2022)
);

この文は、p2020 と p2021 パーティションを p2020_2021 という名前の新しいパーティションに統合します。VALUES LESS THAN (2022) 句は、このパーティションが sale_date の年が 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            |          1 |
| p2025            |          0 |
| pFuture          |          0 |
+------------------+------------+

最後に、パーティションを削除する方法を見てみましょう。p2025 パーティションを削除したいとします。これは、ALTER TABLE 文と DROP PARTITION 句を使用して行うことができます。

ALTER TABLE sales DROP PARTITION p2025;

最後に、パーティション構造を確認しましょう。

SELECT
    PARTITION_NAME,
    TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';

p2025 パーティションが削除されていることがわかるはずです。

+------------------+------------+
| PARTITION_NAME   | TABLE_ROWS |
+------------------+------------+
| p2020_2021       |          3 |
| p2022            |          2 |
| p2023            |          2 |
| p2024            |          1 |
| pFuture          |          0 |
+------------------+------------+

様々な句を持つ ALTER TABLE 文を使用することで、変化するデータ管理のニーズに合わせてパーティションを簡単に再編成することができます。

クエリ速度に対するパーティショニングの影響を確認する

このステップでは、MySQL においてパーティショニングがクエリ速度にどのような影響を与えるかを調査します。現在のデータセットは小規模ですが、ここで示される原則は、大規模なデータセットでのパーティショニングの利点を理解するために重要です。

まず、MySQL サーバーに接続し、sales_data データベースを使用していることを確認します。前のステップで接続を閉じた場合は、以下のコマンドを使用して再接続します。

mysql -u root -p

パスワードの入力を求められたら、root パスワードを入力します。その後、sales_data データベースを選択します。

USE sales_data;

クエリの実行時間を測定するために、SET profiling = 1; コマンドを使用します。これによりプロファイリングが有効になり、各クエリが使用するリソースを確認できます。

SET profiling = 1;

次に、特定のパーティションを対象とするクエリを実行しましょう。2023 年のすべての販売データを選択します。

SELECT * FROM sales WHERE YEAR(sale_date) = 2023;

次に、すべてのパーティションをスキャンする可能性のあるクエリを実行します（ただし、データが限られているため、最適化される場合もあります）。金額が 200 を超えるすべての販売データを選択します。

SELECT * FROM sales WHERE amount > 200;

次に、プロファイリング情報を調べて、各クエリの実行時間を確認しましょう。これは SHOW PROFILES; コマンドを使用して行えます。

SHOW PROFILES;

出力には、プロファイリングが有効になっている間に実行されたクエリのリストと、それぞれの実行時間が表示されます。

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

特定のクエリに関する詳細情報を取得するには、SHOW PROFILE [query_id]; コマンドを使用し、[query_id] を調査したいクエリの ID に置き換えます。クエリ ID は SHOW PROFILES の出力から見つけることができます。たとえば、最初のクエリの ID が 1 の場合、次のコマンドを実行します。

SHOW PROFILE 1;

これにより、クエリの実行の内訳が表示され、各段階（たとえば、準備、データ送信など）で費やされた時間が示されます。

特定のパーティションを対象とするクエリと、すべてのパーティションをスキャンするクエリのプロファイリング情報を比較することで、パーティショニングがクエリ速度に与える影響を観察できます。小規模なデータセットでは、差異が最小限になることに注意してください。ただし、大規模なデータセットでは、パーティショニングによるパフォーマンスの向上は著しくなります。

最後に、プロファイリングを無効にします。

SET profiling = 0;

このステップでは、MySQL のプロファイリングを使用してクエリの実行時間を分析し、パーティショニングのパフォーマンス上の利点を理解する方法を示しました。パーティショニングの有効性は、データのサイズと実行するクエリの種類に依存することを忘れないでください。

まとめ

この実験では、MySQL で範囲によるテーブルのパーティショニングを行い、クエリパフォーマンスとデータ管理の向上に焦点を当てました。MySQL サーバーに接続し、sales_data という名前のデータベースを作成し、sale_date 列の年によってパーティショニングされた sales という名前のテーブルを作成しました。これには、特定の年の範囲（2020 年、2021 年、2022 年、2023 年）のパーティションと、定義された範囲を超える値の将来のパーティションを定義することが含まれていました。

CREATE TABLE 文では、PARTITION BY RANGE を使用して sale_date の年に基づいてデータを分割し、行をその年に基づいて特定のパーティションに割り当てる方法を示しました。このパーティショニング戦略により、日付範囲に基づいてデータを論理的にグループ化することで、大規模なデータセットのクエリと管理をより効率的に行うことができます。