MySQL Online DDL(二)(r11笔记第88天)

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online DDL的算法,目前有三个操作选项,default,inplace,copy可选

具体可以参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

> select count(*) from newtest;
+----------+
| count(*) |
+----------+
| 22681426 |
+----------+
1 row in set (45.76 sec)

表结构信息如下:

> show create table newtest\G
*************************** 1. row ***************************
       Table: newtest
Create Table: CREATE TABLE `newtest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `game_type` int(11) NOT NULL DEFAULT '-1' ,
  `login_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `login_account` varchar(100) DEFAULT NULL ,
  `cn_master` varchar(100) NOT NULL DEFAULT '' ,
  `client_ip` varchar(100) DEFAULT '' ,
  PRIMARY KEY (`id`),
  KEY `ind_tmp_account1` (`login_account`),
  KEY `ind_login_time_newtest` (`login_time`)
) ENGINE=InnoDB AUTO_INCREMENT=22681850 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

默认的copy选项

比如我们运行下面的SQL,添加一个字段,默认情况下是使用copy的算法,即数据是平行复制一份。

alter table newtest add column newcol varchar(10) default '';

这个变更过程会生成两个临时的文件.frm,.ibd -rw-r----- 1 mysql mysql 8840 Dec 5 18:13 newtest.frm -rw-r----- 1 mysql mysql 4353687552 Dec 5 18:45 newtest.ibd ... -rw-r----- 1 mysql mysql 8874 Feb 27 22:25 #sql-6273_2980ab.frm -rw-r----- 1 mysql mysql 41943040 Feb 27 22:25 #sql-ib280-3638407428.ibd ...

在这个变更的过程中,是运行DML操作的,而且没有任何阻塞。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (0.05 sec)

因为使用了主键自增,所以我可以用同样的语句再插入一条记录,也是全然没有阻塞。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (0.00 sec)

这个时候查看show processlist的结果,相比就显得有些简单了。不像之前的版本中会有table metadata lock的字样了。

+---------+-----------------+-----------------------------+----------------+-------------+---------+--------------------------------
|Id      | User            | Host                        |  db             | Command     | Time    | State                          
+---------+-----------------+-----------------------------+----------------+-------------+---------+--------------------------------
| 2719915 | root            | localhost                   | test           | Query       |      75 | altering table

对比临时文件和现有配置文件

我们简单看看上面列举出来的配置文件.frm

可以通过strings的方式看到一个基本的结构信息。

# strings newtest.frm PRIMARY ind_tmp_account1 ind_login_time_newtest InnoDB ) game_type login_time login_account cn_master client_ip game_type login_time login_account cn_master client_ip 而查看临时创建的.frm文件

# strings "#sql-6273_2980ab.frm" PRIMARY ind_tmp_account1 ind_login_time_newtest InnoDB ) game_type login_time login_account cn_master client_ip newcol game_type login_time login_account cn_master client_ip newcol整个添加字段的操作持续时间为10分钟左右。

> alter table newtest add column newcol varchar(10) default ''; Query OK, 0 rows affected (10 min 31.64 sec) Records: 0 Duplicates: 0 Warnings: 0

可以看到修改后的.ibd文件大小相比要大了一些。

-rw-r----- 1 mysql mysql 8874 Feb 27 22:25 newtest.frm -rw-r----- 1 mysql mysql 4047503360 Feb 27 22:34 newtest.ibd

而如果我们换一个角度来看,我们删除一个字段。

--alter table newtest drop column newcol , ALGORITHM=INPLACE; --这种方式是有问题的,采用如下的方式,我们声明使用inplace算法,而实际情况如何呢。

> alter table newtest drop column newcol , ALGORITHM=INPLACE; Query OK, 0 rows affected (9 min 54.18 sec) Records: 0 Duplicates: 0 Warnings: 0

我们可以看到DML操作畅通无阻。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (0.15 sec)

这个过程可以看到效果和启用copy算法是一样的,为什么呢。因为添加字段,删除字段是一个数据重组的过程,所以相比而言,这个操作的代价也是昂贵的。

添加/删除索引

然后我们添加索引,启用inplace算法。

alter table newtest add index (client_ip) ,algorithm=inplace;

这个过程就特别了,依旧会创建.frm的临时文件,但是数据文件不会复制,而是现改。 -rw-r----- 1 mysql mysql 8840 Feb 27 22:49 newtest.frm -rw-r----- 1 mysql mysql 4018143232 Feb 27 23:06 newtest.ibd ... -rw-r----- 1 mysql mysql 8840 Feb 27 23:06 #sql-6273_2980ab.frm

这个过程中,DML依旧是畅通的。

> insert into newtest(game_type,login_time,login_account,cn_master,client_ip) values(1,'2017-02-27 16:22:10','150581500032','572031626','183.128.143.113'); Query OK, 1 row affected (0.04 sec)

整个添加的过程相比而言,持续时间要短很多,大概是3分钟左右。

> alter table newtest add index (client_ip) ,algorithm=inplace; Query OK, 0 rows affected (3 min 42.84 sec) Records: 0 Duplicates: 0 Warnings: 0 而如果此时删除索引,这个过程就如同非一般的感觉,不到一秒即可完成。

> alter table newtest drop index client_ip ,algorithm=inplace; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0

整个过程中.frm和.ibd文件没有任何大小变化。

-rw-r----- 1 mysql mysql 8840 Feb 27 23:13 newtest.frm -rw-r----- 1 mysql mysql 4785700864 Feb 27 23:13 newtest.ibd

而如果我们为了对比同样的inpalce和copy操作场景下的代价,可以使用copy显示创建一个索引,即可得到一个基本的对比情况。

alter table newtest add index (client_ip) ,algorithm=copy;

整个过程因为.ibd文件较大,持续时间也会放大很多,这个环境中执行时间是29分,差别已然非常明显。

> alter table newtest add index (client_ip) ,algorithm=copy; Query OK, 22681430 rows affected (29 min 13.80 sec) Records: 22681430 Duplicates: 0 Warnings: 0

小结

Online DDL还是存在着一些限定情况,很多场景还没有完全测试到,需要结合具体的场景和需求来考量。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-02-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏性能与架构

MySQL 8.0 将结束 MyISAM 引擎

MyISAM 存储引擎已经有了20年的历史,在1995年时,MyISAM 是 MySQL 唯一的存储引擎,服务了20多年,即将退居二线 MySQL 5.7 中...

3396
来自专栏IT大咖说

2018年,让你的数据库变更快的十个建议

摘要 大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十...

4287
来自专栏Jerry的SAP技术分享

在ABAP里取得一个数据库表记录数的两种方法

这个函数使用起来很简单,只需要将想查询的数据库表名称维护进输入参数IT_TABLES:

1685
来自专栏Java后端技术栈

MySQL存储引擎MyISAM与InnoDB区别总结整理

在MySQL 5.1之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。

3343
来自专栏PHP在线

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更...

3468
来自专栏PHP在线

Mysql存储引擎中InnoDB与Myisam的区别

1. 事务处理innodb 支持事务功能,myisam 不支持。 Myisam 的执行速度更快,性能更好。 2. select ,update ,insert...

3175
来自专栏L宝宝聊IT

Mysql性能优化——索引

对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。

842
来自专栏杨建荣的学习笔记

MySQL和Oracle的添加字段的处理差别 (r10笔记第73天)

昨天在微信群中有个朋友也是无意中问了一下,说数据库中的表字段想保持一种相对规范的顺序,怎么办?要知道Oracle中这个操作就比较纠结了,因为是按照追加的方式来处...

3686
来自专栏数据和云

深入解析:你听说过Oracle数据库的更新重启动吗?

杨廷琨 云和恩墨高级咨询顾问, ITPUB Oracle 数据库管理版版主 ,人称 “杨长老”,十数年如一日坚持进行 Oracle 技术研究与写作,号称 ...

954
来自专栏跟着阿笨一起玩NET

SQL语句使用总结(一)

1>. FROM 2>. WHERE 3>. GROUP BY 4>. HAVING 5>. SELECT 6>. ORDER BY

621

扫码关注云+社区

领取腾讯云代金券