Chinaunix首页 | 论坛 | 博客
  • 博客访问: 448760
  • 博文数量: 97
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1141
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
  • 认证徽章:
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(97)

文章存档

2019年(18)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2017-04-12 15:47:37

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址: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
阅读(857) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册