mysql:partitioning
目次
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)
mysql/partitioning.txt · 最終更新: 2016/01/26 05:14 by clownclown