MySQLで特定のテーブルにパーティショニング設定し高速化を図る方法
以前に他社が設計されたログ系のテーブルを拝見した際にパーティショニング設定が行われていた。その時はそのままスルーしたんだけど、せっかくならパーティショニングを行えるよう知識は持っておきたい。以前に取得した市区町村データが約15万行とそこそこなデータ数なのでこれで試してみたい。以下に対応方法のメモ。
リファレンス
https://dev.mysql.com/doc/refman/8.0/ja/partitioning-overview.html
テーブル構造
mysql> SHOW COLUMNS FROM `ad_address`; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | id | int | NO | PRI | 0 | | | ken_id | int | YES | | NULL | | | city_id | int | YES | | NULL | | | town_id | int | YES | | NULL | | | zip | varchar(8) | YES | | NULL | | | office_flg | tinyint(1) | YES | | NULL | | | delete_flg | tinyint(1) | YES | | NULL | | | ken_name | varchar(8) | YES | | NULL | | | ken_furi | varchar(8) | YES | | NULL | | | city_name | varchar(24) | YES | | NULL | | | city_furi | varchar(24) | YES | | NULL | | | town_name | varchar(32) | YES | | NULL | | | town_furi | varchar(32) | YES | | NULL | | | town_memo | varchar(16) | YES | | NULL | | | kyoto_street | varchar(32) | YES | | NULL | | | block_name | varchar(64) | YES | | NULL | | | block_furi | varchar(64) | YES | | NULL | | | memo | varchar(255) | YES | | NULL | | | office_name | varchar(255) | YES | | NULL | | | office_furi | varchar(255) | YES | | NULL | | | office_address | varchar(255) | YES | | NULL | | | new_id | int | YES | | NULL | | +----------------+--------------+------+-----+---------+-------+
テーブル内容については過去記事参照。
パーティショニング設定
事前準備
テーブルとデータをコピーしておく。
CREATE TABLE `ad_address_pt` LIKE `ad_address`; INSERT INTO `ad_address_pt` SELECT * FROM `ad_address`;
またadminerより以下変更を行った。理由については後述する。
- ストレージエンジンをMyISAMからInnoDBに変更
- プライマリキーをidからid,ken_idの複合に変更
パーティショニング追加
ad_address_ptテーブルに以下でパーティショニングを追加した。
ALTER TABLE `ad_address_pt` PARTITION BY RANGE (`ken_id`) (
PARTITION ken_id_1 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION ken_id_2 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION ken_id_3 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION ken_id_4 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION ken_id_5 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION ken_id_6 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION ken_id_7 VALUES LESS THAN (7) ENGINE = InnoDB,
PARTITION ken_id_8 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION ken_id_9 VALUES LESS THAN (9) ENGINE = InnoDB,
PARTITION ken_id_10 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION ken_id_11 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION ken_id_12 VALUES LESS THAN (12) ENGINE = InnoDB,
PARTITION ken_id_13 VALUES LESS THAN (13) ENGINE = InnoDB,
PARTITION ken_id_14 VALUES LESS THAN (14) ENGINE = InnoDB,
PARTITION ken_id_15 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION ken_id_16 VALUES LESS THAN (16) ENGINE = InnoDB,
PARTITION ken_id_17 VALUES LESS THAN (17) ENGINE = InnoDB,
PARTITION ken_id_18 VALUES LESS THAN (18) ENGINE = InnoDB,
PARTITION ken_id_19 VALUES LESS THAN (19) ENGINE = InnoDB,
PARTITION ken_id_20 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION ken_id_21 VALUES LESS THAN (21) ENGINE = InnoDB,
PARTITION ken_id_22 VALUES LESS THAN (22) ENGINE = InnoDB,
PARTITION ken_id_23 VALUES LESS THAN (23) ENGINE = InnoDB,
PARTITION ken_id_24 VALUES LESS THAN (24) ENGINE = InnoDB,
PARTITION ken_id_25 VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION ken_id_26 VALUES LESS THAN (26) ENGINE = InnoDB,
PARTITION ken_id_27 VALUES LESS THAN (27) ENGINE = InnoDB,
PARTITION ken_id_28 VALUES LESS THAN (28) ENGINE = InnoDB,
PARTITION ken_id_29 VALUES LESS THAN (29) ENGINE = InnoDB,
PARTITION ken_id_30 VALUES LESS THAN (30) ENGINE = InnoDB,
PARTITION ken_id_31 VALUES LESS THAN (31) ENGINE = InnoDB,
PARTITION ken_id_32 VALUES LESS THAN (32) ENGINE = InnoDB,
PARTITION ken_id_33 VALUES LESS THAN (33) ENGINE = InnoDB,
PARTITION ken_id_34 VALUES LESS THAN (34) ENGINE = InnoDB,
PARTITION ken_id_35 VALUES LESS THAN (35) ENGINE = InnoDB,
PARTITION ken_id_36 VALUES LESS THAN (36) ENGINE = InnoDB,
PARTITION ken_id_37 VALUES LESS THAN (37) ENGINE = InnoDB,
PARTITION ken_id_38 VALUES LESS THAN (38) ENGINE = InnoDB,
PARTITION ken_id_39 VALUES LESS THAN (39) ENGINE = InnoDB,
PARTITION ken_id_40 VALUES LESS THAN (40) ENGINE = InnoDB,
PARTITION ken_id_41 VALUES LESS THAN (41) ENGINE = InnoDB,
PARTITION ken_id_42 VALUES LESS THAN (42) ENGINE = InnoDB,
PARTITION ken_id_43 VALUES LESS THAN (43) ENGINE = InnoDB,
PARTITION ken_id_44 VALUES LESS THAN (44) ENGINE = InnoDB,
PARTITION ken_id_45 VALUES LESS THAN (45) ENGINE = InnoDB,
PARTITION ken_id_46 VALUES LESS THAN (46) ENGINE = InnoDB,
PARTITION ken_id_47 VALUES LESS THAN (47) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);パーティショニング確認
SHOW CREATE TABLE `ad_address_pt`; | ad_address_pt | CREATE TABLE `ad_address_pt` ( `id` int NOT NULL DEFAULT '0', `ken_id` int NOT NULL, `city_id` int DEFAULT NULL, `town_id` int DEFAULT NULL, `zip` varchar(8) DEFAULT NULL, `office_flg` tinyint(1) DEFAULT NULL, `delete_flg` tinyint(1) DEFAULT NULL, `ken_name` varchar(8) DEFAULT NULL, `ken_furi` varchar(8) DEFAULT NULL, `city_name` varchar(24) DEFAULT NULL, `city_furi` varchar(24) DEFAULT NULL, `town_name` varchar(32) DEFAULT NULL, `town_furi` varchar(32) DEFAULT NULL, `town_memo` varchar(16) DEFAULT NULL, `kyoto_street` varchar(32) DEFAULT NULL, `block_name` varchar(64) DEFAULT NULL, `block_furi` varchar(64) DEFAULT NULL, `memo` varchar(255) DEFAULT NULL, `office_name` varchar(255) DEFAULT NULL, `office_furi` varchar(255) DEFAULT NULL, `office_address` varchar(255) DEFAULT NULL, `new_id` int DEFAULT NULL, PRIMARY KEY (`id`,`ken_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 /*!50100 PARTITION BY RANGE (`ken_id`) (PARTITION ken_id_1 VALUES LESS THAN (1) ENGINE = InnoDB, PARTITION ken_id_2 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION ken_id_3 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION ken_id_4 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION ken_id_5 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION ken_id_6 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION ken_id_7 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION ken_id_8 VALUES LESS THAN (8) ENGINE = InnoDB, PARTITION ken_id_9 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION ken_id_10 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION ken_id_11 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION ken_id_12 VALUES LESS THAN (12) ENGINE = InnoDB, PARTITION ken_id_13 VALUES LESS THAN (13) ENGINE = InnoDB, PARTITION ken_id_14 VALUES LESS THAN (14) ENGINE = InnoDB, PARTITION ken_id_15 VALUES LESS THAN (15) ENGINE = InnoDB, PARTITION ken_id_16 VALUES LESS THAN (16) ENGINE = InnoDB, PARTITION ken_id_17 VALUES LESS THAN (17) ENGINE = InnoDB, PARTITION ken_id_18 VALUES LESS THAN (18) ENGINE = InnoDB, PARTITION ken_id_19 VALUES LESS THAN (19) ENGINE = InnoDB, PARTITION ken_id_20 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION ken_id_21 VALUES LESS THAN (21) ENGINE = InnoDB, PARTITION ken_id_22 VALUES LESS THAN (22) ENGINE = InnoDB, PARTITION ken_id_23 VALUES LESS THAN (23) ENGINE = InnoDB, PARTITION ken_id_24 VALUES LESS THAN (24) ENGINE = InnoDB, PARTITION ken_id_25 VALUES LESS THAN (25) ENGINE = InnoDB, PARTITION ken_id_26 VALUES LESS THAN (26) ENGINE = InnoDB, PARTITION ken_id_27 VALUES LESS THAN (27) ENGINE = InnoDB, PARTITION ken_id_28 VALUES LESS THAN (28) ENGINE = InnoDB, PARTITION ken_id_29 VALUES LESS THAN (29) ENGINE = InnoDB, PARTITION ken_id_30 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION ken_id_31 VALUES LESS THAN (31) ENGINE = InnoDB, PARTITION ken_id_32 VALUES LESS THAN (32) ENGINE = InnoDB, PARTITION ken_id_33 VALUES LESS THAN (33) ENGINE = InnoDB, PARTITION ken_id_34 VALUES LESS THAN (34) ENGINE = InnoDB, PARTITION ken_id_35 VALUES LESS THAN (35) ENGINE = InnoDB, PARTITION ken_id_36 VALUES LESS THAN (36) ENGINE = InnoDB, PARTITION ken_id_37 VALUES LESS THAN (37) ENGINE = InnoDB, PARTITION ken_id_38 VALUES LESS THAN (38) ENGINE = InnoDB, PARTITION ken_id_39 VALUES LESS THAN (39) ENGINE = InnoDB, PARTITION ken_id_40 VALUES LESS THAN (40) ENGINE = InnoDB, PARTITION ken_id_41 VALUES LESS THAN (41) ENGINE = InnoDB, PARTITION ken_id_42 VALUES LESS THAN (42) ENGINE = InnoDB, PARTITION ken_id_43 VALUES LESS THAN (43) ENGINE = InnoDB, PARTITION ken_id_44 VALUES LESS THAN (44) ENGINE = InnoDB, PARTITION ken_id_45 VALUES LESS THAN (45) ENGINE = InnoDB, PARTITION ken_id_46 VALUES LESS THAN (46) ENGINE = InnoDB, PARTITION ken_id_47 VALUES LESS THAN (47) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
パーティショニング削除
以下で削除できる。
ALTER TABLE `ad_address_pt` DROP PARTITION ken_id_1;
尚、パーティショニングが削除されると共に範囲内のデータも削除される点に注意。例えばだけど今回使ったテーブルにはken_id=1が北海道の市区町村データが入っており、上記SQL文を実行すると北海道のデータも削除される。
速度確認
パーティショニング設定していないテーブル
mysql> SELECT count(*) FROM `ad_address` WHERE `ken_id` = 1; +----------+ | count(*) | +----------+ | 10302 | +----------+ 1 row in set (1.12 sec)
パーティショニング設定しているテーブル
mysql> SELECT count(*) FROM `ad_address_pt` WHERE `ken_id` = 1; +----------+ | count(*) | +----------+ | 10302 | +----------+ 1 row in set (0.00 sec)
大幅な差が出た。データの内容やSQL文によってはかなり使えそう。
エラー関連
The mix of handlers in the partitions is not allowed in this version of MySQL
MyISAMのテーブルにInnoDBでパーティショニング設定していたのがダメっぽい。今回はテーブル自体をInnoDBに変更することで解決した。
A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
パーティショニング設定する際はプライマリキーを含める必要があるとのこと。ken_id&idをプライマリキーに指定することで解決した。尚、ken_idだけだと今回はケースでは重複カラムが多数発生するのでken_id&idの複数指定することにより重複を避けた。
ERROR: Table has no partition for value from
レンジパーティションを設定する際は終端まで定義する必要があるとのこと。以下を設定することで解決。
PARTITION pmax VALUES LESS THAN MAXVALUE
使いどころ
規模が大きめで集計を行うようなテーブルの場合に有効だと思われる。例えば売り上げログ系のテーブルとかだと後から年 / 月 / 日単位で集計することになると思うが、その際にインデックスよりもパーティションの方が効果は大きいっぽい(参考記事)。
所感
使い方にもよるんだろうけど今回のケースだと大幅に速度アップした。尚、今回のケースの場合は都道府県IDに対して設定したため最初から47パターンのみになるが、例えばログ系のテーブルで登録日時に対して設定するような場合は未来日付(2038年迄等)を設定するのが良いらしい。
関連記事
-
MySQLで日時や日付で範囲検索する際に動作速度を向上させる方法
MySQLでログ系等の大量にデータが格納されているテーブルから日時や日付で範囲検 ...
-
MySQLでJOINの高速化
JOINで無茶苦茶悩んだのでメモ。やりたかった事は、MySQLで テーブルAには ...
-
MySQLでdatetime型にもインデックスを使用して高速化を図る方法
ログ関連のデータを格納するテーブルがあり、集計を行う際に非常に時間がかかった。レ ...