大規模MariaDBテーブルのパフォーマンス向上！CHAR型とDATE型列による主キーパーティショニング

2024-06-22

このチュートリアルでは、CHAR 型と DATE 型の列を主キーとしてパーティショニングする方法を説明します。

要件

このチュートリアルを完了するには、以下のものが必要です。

MariaDB がインストールおよび実行されている
パーティショニングの基本的な知識

手順

次のクエリを使用して、サンプルテーブルを作成します。

CREATE TABLE my_table (
  id CHAR(36) NOT NULL,
  created_at DATE NOT NULL,
  data TEXT,
  PRIMARY KEY (id, created_at)
);

ALTER TABLE my_table
PARTITION BY RANGE (created_at) (
  PARTITION p2023_01 VALUES LESS THAN ('2023-01-01'),
  PARTITION p2023_02 VALUES LESS THAN ('2023-02-01'),
  PARTITION p2023_03 VALUES LESS THAN ('2023-03-01'),
  PARTITION p2023_04 VALUES LESS THAN ('2023-04-01'),
  PARTITION p2023_05 VALUES LESS THAN ('2023-05-01'),
  PARTITION p2023_06 VALUES LESS THAN ('2023-06-01'),
  PARTITION p2023_07 VALUES LESS THAN ('2023-07-01'),
  PARTITION p2023_08 VALUES LESS THAN ('2023-08-01'),
  PARTITION p2023_09 VALUES LESS THAN ('2023-09-01'),
  PARTITION p2023_10 VALUES LESS THAN ('2023-10-01'),
  PARTITION p2023_11 VALUES LESS THAN ('2023-11-01'),
  PARTITION p2023_12 VALUES LESS THAN ('2024-01-01')
);

このクエリは、created_at 列の値に基づいてテーブルを 12 個のパーティションに分割します。各パーティションには、特定の月のデータが含まれます。

SHOW CREATE TABLE my_table;

出力には、次のようになります。

CREATE TABLE `my_table` (
  `id` CHAR(36) NOT NULL,
  `created_at` DATE NOT NULL,
  `data` TEXT,
  PRIMARY KEY (`id`, `created_at`),
  PARTITION BY RANGE (created_at) (
    PARTITION `p2023_01` VALUES LESS THAN ('2023-01-01'),
    PARTITION `p2023_02` VALUES LESS THAN ('2023-02-01'),
    PARTITION `p2023_03` VALUES LESS THAN ('2023-03-01'),
    PARTITION `p2023_04` VALUES LESS THAN ('2023-04-01'),
    PARTITION `p2023_05` VALUES LESS THAN ('2023-05-01'),
    PARTITION `p2023_06` VALUES LESS THAN ('2023-06-01'),
    PARTITION `p2023_07` VALUES LESS THAN ('2023-07-01'),
    PARTITION `p2023_08` VALUES LESS THAN ('2023-08-01'),
    PARTITION `p2023_09` VALUES LESS THAN ('2023-09-01'),
    PARTITION `p2023_10` VALUES LESS THAN ('2023-10-01'),
    PARTITION `p2023_11` VALUES LESS THAN ('2023-11-01'),
    PARTITION `p2023_12` VALUES LESS THAN ('2024-01-01')

MariaDB で CHAR 型と DATE 型の列を主キーとしてパーティショニングを行うサンプルコード

-- サンプルテーブルの作成
CREATE TABLE my_table (
  id CHAR(36) NOT NULL,
  created_at DATE NOT NULL,
  data TEXT,
  PRIMARY KEY (id, created_at)
);

-- テーブルのパーティショニング
ALTER TABLE my_table
PARTITION BY RANGE (created_at) (
  PARTITION p2023_01 VALUES LESS THAN ('2023-01-01'),
  PARTITION p2023_02 VALUES LESS THAN ('2023-02-01'),
  PARTITION p2023_03 VALUES LESS THAN ('2023-03-01'),
  PARTITION p2023_04 VALUES LESS THAN ('2023-04-01'),
  PARTITION p2023_05 VALUES LESS THAN ('2023-05-01'),
  PARTITION p2023_06 VALUES LESS THAN ('2023-06-01'),
  PARTITION p2023_07 VALUES LESS THAN ('2023-07-01'),
  PARTITION p2023_08 VALUES LESS THAN ('2023-08-01'),
  PARTITION p2023_09 VALUES LESS THAN ('2023-09-01'),
  PARTITION p2023_10 VALUES LESS THAN ('2023-10-01'),
  PARTITION p2023_11 VALUES LESS THAN ('2023-11-01'),
  PARTITION p2023_12 VALUES LESS THAN ('2024-01-01')
);

-- パーティショニングの確認
SHOW CREATE TABLE my_table;

このコードは、以下の操作を実行します。

my_table という名前のテーブルを作成します。このテーブルには、id、created_at、data という 3 つの列があります。id 列は主キーであり、CHAR(36) 型です。created_at 列も主キーであり、DATE 型です。data 列は TEXT 型です。
テーブルのパーティショニング構成を表示します。

このコードは、CHAR 型と DATE 型の列を主キーとしてパーティショニングする方法の例です。具体的な要件に応じて、コードを調整する必要があります。

説明

このコードは、MariaDB でパーティショニングを使用する基本的な方法を示しています。パーティショニングは、大規模なテーブルのパフォーマンスとスケーラビリティを向上させるために使用できる強力な手法です。

パーティショニングの詳細については、MariaDB のドキュメントを参照してください: https://mariadb.com/kb/en/partitioning-overview/

MariaDB で CHAR 型と DATE 型の列を主キーとしてパーティショニングするその他の方法

LIST パーショニングを使用すると、特定の値のリストに基づいてテーブルをパーティション化できます。これは、created_at 列の値が限られた数の既知の値のみに制限されている場合に役立ちます。

ALTER TABLE my_table
PARTITION BY LIST (created_at) (
  PARTITION p2023_01 VALUES IN ('2023-01-01'),
  PARTITION p2023_02 VALUES IN ('2023-02-01'),
  PARTITION p2023_03 VALUES IN ('2023-03-01'),
  ...
);

HASH パーショニングを使用すると、ハッシュ関数を使用してレコードをパーティションに分散させることができます。これは、created_at 列の値が均一に分布していない場合に役立ちます。

ALTER TABLE my_table
PARTITION BY HASH (created_at) (
  PARTITIONS 16
);

レンジとリストの組み合わせの使用

レンジパーティショニングとリストパーティショニングを組み合わせて、より複雑なパーティショニングスキームを作成することもできます。たとえば、次のように、各月にさらにサブパーティションを作成できます。

ALTER TABLE my_table
PARTITION BY RANGE (created_at) (
  PARTITION p2023_01 (
    PARTITION p2023_01_01 VALUES LESS THAN ('2023-01-08'),
    PARTITION p2023_01_02 VALUES LESS THAN ('2023-01-15'),
    ...
  ),
  PARTITION p2023_02 (
    PARTITION p2023_02_01 VALUES LESS THAN ('2023-02-08'),
    PARTITION p2023_02_02 VALUES LESS THAN ('2023-02-15'),
    ...
  ),
  ...
);

最適なパーティショニング方法の選択

使用するパーティショニング方法は、テーブルのデータとアクセスパターンによって異なります。単純なケースでは、レンジパーティショニングで十分です。より複雑なケースでは、リストパーティショニング、HASHパーティショニング、またはレンジとリストの組み合わせが必要になる場合があります。

パーティショニングには、次のような利点があります。

パフォーマンスの向上: パーティショニングにより、クエリオプティマイザが関連するパーティションのみをスキャンできるため、クエリのパフォーマンスが向上します。
スケーラビリティの向上: テーブルをパーティション化すると、複数のストレージデバイスにデータを分散させることができるため、スケーラビリティが向上します。
管理の簡素化: パーティショニングにより、個々のパーティションを個別に管理しやすくなります。

追加の複雑さ: パーティショニングは、テーブルの設計と管理に複雑さを加えます。
メンテナンスのオーバーヘッド: パーティショニングには、パーティションの作成、削除、再編成などの追加のメンテナンス作業が必要となります。
潜在的なパフォーマンスの低下: パーティションが多すぎると、クエリのパフォーマンスが低下する可能性があります。

MariaDB でパーティショニングを使用すると、大規模なテーブルのパフォーマンスとスケーラビリティを向上させることができます。ただし、パーティショニングには、追加の複雑さ、メンテナンスのオーバーヘッド、潜在的なパフォーマンスの低下などの注意点もあります。パーティショニングを使用する前に、これらの利点と欠点を比較検討する必要があります。

mariadb partitioning