MariaDB(MySQL)でパーティショニング（パーティション化テーブル）により大量レコードのテーブルの処理を高速化
MariaDB(MySQL)でパーティショニング（パーティション化テーブル）により大量レコードのテーブルの処理を高速化できる可能性があります。
パーティション分割をサポートするストレージエンジンは InnoDB と NDB です。
MySQL公式（https://dev.mysql.com/doc/refman/8.0/ja/partitioning-overview.html）には以下の記述があります。
指定された WHERE 句を満たすデータを 1 つ以上のパーティションのみに格納できることによって、検索からほかのパーティションが自動的に除外され、一部のクエリーが大幅に最適化されることがあります。
テスト用データベースの作成
説明のためにテスト用データベースとパーティション化テーブルの作成です。
-- データベース作成 CREATE DATABASE IF NOT EXISTS db_partition_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE db_partition_test;
KEY パーティショニングによるパーティション化テーブルの作成
KEY パーティショニングによるパーティション化テーブルは、指定したカラムをキーとして分割し、レコードを配置します。
例えば、以下のクエリは、code kind をキーに８つのパーテイションに分割したテーブル「t_item1」を作成しています。
PARTITION BY KEYに指定できるカラムは、UNIQUE INDEX / PRIMARY KEYに指定したカラム全て又は一部だけです。
ただし、UNIQUE INDEX / PRIMARY KEY がない場合はこの限りではありません。
CREATE TABLE t_item1( code VARCHAR(8) NOT NULL COMMENT '商品コード', del_flag TINYINT NOT NULL COMMENT '削除フラグ 0:通常 1:非表示 2:削除', name VARCHAR(20) NOT NULL COMMENT '商品名', PRIMARY KEY(code, del_flag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PARTITION BY KEY(code,del_flag) PARTITIONS 8;
9レコードを挿入します
INSERT INTO t_item1(code,name,del_flag) values
('00000001','パソコン',0), ('00000002','マウス',0), ('00000003','モニター',0), ('00000004','電源',0),
('00000005','HDMIケーブル',1), ('00000006','DVIケーブル',1), ('00000007','LANケーブル',1),
('00000008','電源タップ',2), ('00000009','電源ケーブル1',2);
パーティションにどのように挿入されたか確認すると、パーティション「p1」に偏って挿入されています。
キーに指定したカラムの値が偏る場合（特にVARCHAR型は偏りやすい）はパーティションの配置も偏るので、なるべく偏らないカラムを選択しましょう。
select table_name, partition_name, table_rows from information_schema.PARTITIONS where table_schema='db_partition_test' and table_name='t_item1'; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | t_item1 | p0 | 0 | | t_item1 | p1 | 2 | | t_item1 | p2 | 0 | | t_item1 | p3 | 0 | | t_item1 | p4 | 3 | | t_item1 | p5 | 0 | | t_item1 | p6 | 0 | | t_item1 | p7 | 4 | +------------+----------------+------------+
HASH パーティショニングによるパーティション化テーブルの作成
HASH パーティショニングによるパーティション化テーブルは指定したカラムのハッシュ値をキーとして分割しレコードを配置します。
例えば、以下のクエリは、code kind をキーに８つのパーテイションに分割したテーブル「t_item2」を作成しています。
PARTITION BY HASHに指定できるカラムは、UNIQUE INDEX / PRIMARY KEYに指定した「数値型」カラムの全て又は一部です。
CREATE TABLE t_item2( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(8) NOT NULL COMMENT '商品コード', del_flag TINYINT NOT NULL COMMENT '削除フラグ 0:通常 1:非表示 2:削除', name VARCHAR(20) NOT NULL COMMENT '商品名' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PARTITION BY HASH(id) PARTITIONS 8;
9レコードを挿入します
INSERT INTO t_item2(code,name,del_flag) values
('00000001','パソコン',0), ('00000002','マウス',0), ('00000003','モニター',0), ('00000004','電源',0),
('00000005','HDMIケーブル',1), ('00000006','DVIケーブル',1), ('00000007','LANケーブル',1),
('00000008','電源タップ',2), ('00000009','電源ケーブル1',2);
パーティションにどのように挿入されたか確認します。
select table_name, partition_name, table_rows from information_schema.PARTITIONS where table_schema='db_partition_test' and table_name='t_item2'; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | t_item2 | p0 | 1 | | t_item2 | p1 | 2 | | t_item2 | p2 | 1 | | t_item2 | p3 | 1 | | t_item2 | p4 | 1 | | t_item2 | p5 | 1 | | t_item2 | p6 | 1 | | t_item2 | p7 | 1 | +------------+----------------+------------+
RANGE パーティショニングによるパーティション化テーブルの作成
RANGE パーティショニングによるパーティション化テーブルは指定された範囲に収まるようにパーティション化されます。
例えば、以下のクエリは、del_flagが0の時はパーテイションp0、1の時はパーティションp1、2の時はパーティションp2に
分割したテーブル「t_item3」を作成しています。
パーティションを跨いで一意性を保証できないため、UNIQUE INDEX / PRIMARY KEY がある場合は パーティションキーを必ず含まなければならないので、
以下例では INDEX のみ作成し、UNIQUE INDEX / PRIMARY KEY を作成していません。
CREATE TABLE t_item3( id INT AUTO_INCREMENT, code VARCHAR(8) NOT NULL COMMENT '商品コード', del_flag TINYINT NOT NULL COMMENT '削除フラグ 0:通常 1:非表示 2:削除', name VARCHAR(20) NOT NULL COMMENT '商品名', INDEX id_idx(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PARTITION BY RANGE COLUMNS(del_flag) ( PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN MAXVALUE );
9レコードを挿入します
INSERT INTO t_item3(code,name,del_flag) values
('00000001','パソコン',0), ('00000002','マウス',0), ('00000003','モニター',0), ('00000004','電源',0),
('00000005','HDMIケーブル',1), ('00000006','DVIケーブル',1), ('00000007','LANケーブル',1),
('00000008','電源タップ',2), ('00000009','電源ケーブル1',2);
パーティションにどのように挿入されたか確認します。
パーテイションp0にdel_flag=0の4レコード、
パーティションp1にdel_flag=1の3レコード、
パーティションp2にdel_flag=2の2レコード
が格納されているのを確認できます。
select table_name, partition_name, table_rows from information_schema.PARTITIONS where table_schema='db_partition_test' and table_name='t_item3'; +------------+----------------+------------+ | table_name | partition_name | table_rows | +------------+----------------+------------+ | t_item3 | p0 | 4 | | t_item3 | p1 | 3 | | t_item3 | p2 | 2 | +------------+----------------+------------+
次に、
インストール先ディレクトリ/data/db_partition_test
を確認すると各テーブルが指定した数のファイルに分割配置されているのが確認できます。
select * from db_partition_test.t_item3 where del_flag=2;
のようなSQLだと t_item3#p#p2.ibd ファイルしか参照されないので処理速度が速くなります。
Mode LastWriteTime Length Name ---- ------------- ------ ---- -a---- 2026/05/08 11:28 67 db.opt -a---- 2026/05/11 15:24 65536 t_item1#p#p0.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p1.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p2.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p3.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p4.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p5.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p6.ibd -a---- 2026/05/11 15:24 65536 t_item1#p#p7.ibd -a---- 2026/05/11 15:24 1215 t_item1.frm -a---- 2026/05/11 15:24 80 t_item1.par -a---- 2026/05/11 15:26 65536 t_item2#p#p0.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p1.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p2.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p3.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p4.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p5.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p6.ibd -a---- 2026/05/11 15:26 65536 t_item2#p#p7.ibd -a---- 2026/05/11 15:26 1227 t_item2.frm -a---- 2026/05/11 15:26 80 t_item2.par -a---- 2026/05/11 15:30 81920 t_item3#p#p0.ibd -a---- 2026/05/11 15:30 81920 t_item3#p#p1.ibd -a---- 2026/05/11 15:30 81920 t_item3#p#p2.ibd -a---- 2026/05/11 15:30 1398 t_item3.frm -a---- 2026/05/11 15:30 44 t_item3.par