博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5762894.html
随着分区表在MySQL 5.7中的完善和性能的提高，分区表使用逐渐广泛起来。因此，生产环境对分区表的操作也比较多了。本文介绍的是MySQL将普通表转换分区表的常用两种方法。
先说明数据库版本
(dbwacher)root@localhost [(none)]> select version();
+------------+
| version() |
+------------+
| 5.7.17-log |
+------------+
1.alter转换为分区表
(dbwacher)root@localhost [test]> show create table tc \G
*************************** 1. row ***************************
Table: tc
Create Table: CREATE TABLE `tc` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
(dbwacher)root@localhost [test]> select * from tc;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dbayang |
| 10 | zhangjing |
| 100 | dbwatcher |
| 150 |zj |
| 200 | xiaozhang |
| 250 | lisi |
| 300 | zhangsan |
| 350 | mazi |
+-----+-----------+
8 rows in set (0.00 sec)
(dbwacher)root@localhost [test]> alter table tc PARTITION BY RANGE (id) (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (200),
-> PARTITION p2 VALUES LESS THAN (300),
-> PARTITION p3 VALUES LESS THAN MAXVALUE);
Query OK, 8 rows affected (0.21 sec)
Records: 8 Duplicates: 0 Warnings: 0
转换后表定义如下：
(dbwacher)root@localhost [test]> show create table tc \G
*************************** 1. row ***************************
Table: tc
Create Table: CREATE TABLE `tc` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.03 sec)
数据检查
(dbwacher)root@localhost [test]> select * from tc partition(p1);
+-----+-----------+
| id | name |
+-----+-----------+
| 100 | dbwatcher |
| 150 | zj |
+-----+-----------+
2 rows in set (0.00 sec)
执行sql时候，执行计划的变化：
(dbwacher)root@localhost [test]> explain select * from tc where id=150;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tc | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
二，使用pt-osc工具进行在线转换
(dbwacher)root@localhost [test]> CREATE TABLE tt(id INT NOT NULL,
-> name varchar(20),
-> PRIMARY KEY (id) );
Query OK, 0 rows affected (0.08 sec)
(dbwacher)root@localhost [test]> select * from tt;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dbayang |
| 10 |
zhangjing |
| 100 | dbwatcher |
| 150 | zj |
| 200 | xiaozhang |
| 250 | lisi |
| 300 | zhangsan |
| 350 | mazi |
+-----+-----------+
8 rows in set (0.02 sec)
开始进行转换
[root@mysql57 ~]# pt-online-schema-change h=localhost,D=test,t=tt --recursion-method none --execute --alter "PARTITION BY RANGE(id) (
> PARTITION p0 VALUES LESS THAN (100),
> PARTITION p1 VALUES LESS THAN (200),
> PARTITION p2 VALUES LESS THAN (300),
> PARTITION p3 VALUES LESS THAN MAXVALUE)"
No slaves found. See --recursion-method if host mysql57 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `test`.`tt`...
Creating new table...
Created new table test._tt_new OK.
Altering new table...
Altered `test`.`_tt_new` OK.
2017-04-11T18:27:39 Creating triggers...
2017-04-11T18:27:39 Created triggers OK.
2017-04-11T18:27:39 Copying approximately 8 rows...
2017-04-11T18:27:39 Copied rows OK.
2017-04-11T18:27:39 Analyzing new table...
2017-04-11T18:27:39 Swapping tables...
2017-04-11T18:27:39 Swapped original and new tables OK.
2017-04-11T18:27:39 Dropping old table...
2017-04-11T18:27:39 Dropped old table `test`.`_tt_old` OK.
2017-04-11T18:27:39 Dropping triggers...
2017-04-11T18:27:39 Dropped triggers OK.
Successfully altered `test`.`tt`.
转换完成，检查转换结果
数据：
(dbwacher)root@localhost [test]> select * from tt partition(p1);
+-----+-----------+
| id | name |
+-----+-----------+
| 100 | dbwatcher |
| 150 | zj |
+-----+-----------+
2 rows in set (0.00 sec)
(dbwacher)root@localhost [test]> select * from tt partition(p2);
+-----+-----------+
| id | name |
+-----+-----------+
| 200 | xiaozhang |
| 250 | lisi |
+-----+-----------+
2 rows in set (0.00 sec)
(dbwacher)root@localhost [test]> explain select * from tt where id=150;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tt | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
最后看看转换后表结构如下：
(dbwacher)root@localhost [test]> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (300) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
至此使用pt-online-change-schema将普通表在线转换为分区表。
---The end
