====== MySQLのパーティション(Ver5.6にて) ====== ===== パーティション付きのテーブル作成 ===== mysql> CREATE TABLE `log_user_action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `type` varchar(255) NOT NULL, `reg_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`reg_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE COLUMNS(reg_date) (PARTITION p20151201 VALUES LESS THAN ('2015-12-02'), PARTITION p20151202 VALUES LESS THAN ('2015-12-03'), PARTITION p20151203 VALUES LESS THAN ('2015-12-04'), PARTITION p20151204 VALUES LESS THAN ('2015-12-05'), PARTITION p20151205 VALUES LESS THAN ('2015-12-06'), PARTITION pmax VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.20 sec) ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | p20151201 | 1 | 0 | | testdb | log_user_action | p20151202 | 2 | 0 | | testdb | log_user_action | p20151203 | 3 | 0 | | testdb | log_user_action | p20151204 | 4 | 0 | | testdb | log_user_action | p20151205 | 5 | 0 | | testdb | log_user_action | pmax | 6 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 6 rows in set (0.03 sec) ===== レコード挿入 ===== mysql> INSERT INTO `log_user_action` (`id`, `user_id`, `type`, `reg_date`) VALUES (NULL, '1', 'quest', '2015-12-03 12:59:04'); Query OK, 1 row affected (0.00 sec) ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | p20151201 | 1 | 0 | | testdb | log_user_action | p20151202 | 2 | 0 | | testdb | log_user_action | p20151203 | 3 | 1 | | testdb | log_user_action | p20151204 | 4 | 0 | | testdb | log_user_action | p20151205 | 5 | 0 | | testdb | log_user_action | pmax | 6 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 6 rows in set (0.00 sec) ===== パーティションを指定してSELECT ===== mysql> SELECT * FROM `log_user_action` PARTITION (p20151202, p20151203) WHERE type = 'quest'; +----+---------+-------+---------------------+ | id | user_id | type | reg_date | +----+---------+-------+---------------------+ | 1 | 1 | quest | 2015-12-03 12:59:04 | +----+---------+-------+---------------------+ 1 row in set (0.00 sec) ===== パーティションが使われていることを確認 ===== mysql> EXPLAIN PARTITIONS SELECT * FROM `log_user_action` PARTITION (p20151202, p20151203) WHERE type = 'quest'; +----+-------------+-----------------+---------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+---------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | log_user_action | p20151202,p20151203 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+-----------------+---------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) ===== パーティション追加(pmaxを細分化して再定義) ===== mysql> ALTER TABLE log_user_action REORGANIZE PARTITION pmax INTO (PARTITION p20151206 VALUES LESS THAN ('2015-12-07'), PARTITION pmax VALUES LESS THAN MAXVALUE) Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | p20151201 | 1 | 0 | | testdb | log_user_action | p20151202 | 2 | 0 | | testdb | log_user_action | p20151203 | 3 | 0 | | testdb | log_user_action | p20151204 | 4 | 0 | | testdb | log_user_action | p20151205 | 5 | 0 | | testdb | log_user_action | p20151206 | 6 | 0 | | testdb | log_user_action | pmax | 7 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 7 rows in set (0.02 sec) ===== パーティション削除 ===== mysql> ALTER TABLE `log_user_action` DROP PARTITION p20151201; Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0ALTER TABLE `log_user_action` DROP PARTITION p20151201; ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | p20151202 | 1 | 0 | | testdb | log_user_action | p20151203 | 2 | 0 | | testdb | log_user_action | p20151204 | 3 | 0 | | testdb | log_user_action | p20151205 | 4 | 0 | | testdb | log_user_action | p20151206 | 5 | 0 | | testdb | log_user_action | pmax | 6 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 6 rows in set (0.00 sec) ===== 非パーティションテーブルに変更 ===== mysql> ALTER TABLE log_user_action REMOVE PARTITIONING; Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | NULL | NULL | 2 | +--------------+-----------------+----------------+----------------------------+------------+ 1 row in set (0.01 sec) ===== 非パーティションテーブルにパーティション追加 ===== mysql> ALTER TABLE log_user_action PARTITION BY RANGE COLUMNS(reg_date) (PARTITION p20151201 VALUES LESS THAN ('2015-12-02'), PARTITION p20151202 VALUES LESS THAN ('2015-12-03'), PARTITION p20151203 VALUES LESS THAN ('2015-12-04'), PARTITION p20151204 VALUES LESS THAN ('2015-12-05'), PARTITION p20151205 VALUES LESS THAN ('2015-12-06'), PARTITION pmax VALUES LESS THAN MAXVALUE); Query OK, 3 rows affected (0.77 sec) Records: 3 Duplicates: 0 Warnings: 0 ===== パーティション情報確認 ===== mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,PARTITION_NAME ,PARTITION_ORDINAL_POSITION ,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'log_user_action'; +--------------+-----------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+-----------------+----------------+----------------------------+------------+ | testdb | log_user_action | p20151201 | 1 | 0 | | testdb | log_user_action | p20151202 | 2 | 0 | | testdb | log_user_action | p20151203 | 3 | 2 | | testdb | log_user_action | p20151204 | 4 | 0 | | testdb | log_user_action | p20151205 | 5 | 0 | | testdb | log_user_action | pmax | 6 | 0 | +--------------+-----------------+----------------+----------------------------+------------+ 6 rows in set (0.01 sec)