专栏首页AustinDatabasesMYSQL 的老大难,instant ,inplace,copy, DDL 怎么弄, 我不想迷迷糊糊

MYSQL 的老大难,instant ,inplace,copy, DDL 怎么弄, 我不想迷迷糊糊

DDL 操作一直是我们的 MYSQL 的一个软肋,从MYSQL 5.6 其实相关的alter 语句已经有了改变,也就是题目的的inplace 和 copy 。其实很多人都知道,但用的比较少,因为有pt-OSC 工具呀,还有另外一个工具gh-ost

(之前是写过的,如有需要可以自己往前翻翻)

维护现在有提起这串豆腐的原因就是MYSQL 8 发展的太快, pt 工具有点跟不上,根据官方的文档,8.013后的版本,PT的部分工具就开始有的时候使用上会出现各种问题。

所以我们在mysql 的正根 alter 语句在高版本上还的拿出来用,而这里面就牵扯,什么时候 inplace 什么时候 copy 到底这都是什么鬼 ?MYSQL 的 DDL 好累心。

OK 下面就是一段官方+测试的东西 + MGR MYSQL 8.018

本次主要是针对字段的DDL 的 增删改来进行的

从上面的8.0 提供的表来看

下面是mysql 5.7 提供的,可以很清晰的看出,的确死不一样了,多了一列叫Instant

但实际上可以看出这个立即能做的事情不多,adding a column ,setting a column default value , Dropping the column default value 这些才可以进行instant 但 最常用到的 adding a column也上面有一个* 号,这说明不可以都可以,是要有条件的。

条件:

1 要不你就添加字段,你要是混合使用alter table语句,那恕不进行instant的操作,例如一条语句又是加字段,又是删字段

2 字段只能加到表最后一列,你要是想在之间加什么字段,恕不管用

3 表的row_format 不能是压缩的 compressed 的格式

4 表里面有全文索引,no no no 不可以

5 临时表不可以

6 数据字典表不可以

添加字段还是蛮快的。下面我们在一个新表,并且一直插入数据的状态下,看看添加字段还这么惬意吗?

我们看看结果如何

DROP TABLE IF EXISTS test.test;

CREATE TABLE test.test(

id int(10) not null auto_increment,

name varchar(20) not null,

age smallint not null,

work_years smallint not null,

PRIMARY key (`id`)

)ENGINE INNODB DEFAULT CHARSET utf8 COMMENT 'test';

#清空数据

TRUNCATE table test.test;

*/

#定义存储过程

delimiter //

DROP PROCEDURE IF EXISTS insert_test_val;

##num_limit 要插入数据的数量,rand_limit 最大随机的数值

CREATE PROCEDURE insert_test_val()

BEGIN

DECLARE i int default 1;

DECLARE a varchar(20) ;

DECLARE b smallint ;

DECLARE c smallint ;

WHILE i<=1000000 do

set b = FLOOR(rand()*50);

set c = FLOOR(rand()*10);

if i mod 2 = 0 then

set a = 'peter';

elseif i mod 3 = 0 then

set a = 'jimmy';

elseif i mod 5 = 0 then

set a = 'Tim';

elseif i mod 4 = 0 then

set a = 'semon';

else

set a = 'lisa';

end if;

if b < 20 then

set b = b + 15;

end if;

INSERT into test.test values (null,a,b,c);

set i = i + 1;

END WHILE;

END

//

#调用存储过程

call insert_test_val();

我们在test 库建立一个表,并且往里面插入大量的数据,然后我们

alter table test add column column1 varchar(500) ,ALGORITHM=INSTANT;

在这个表上添加一个字段,结果如何

SESSION 1

SESSION 2

字段瞬间添加上了,但是存储过程在运行的途中直接报错,通过上表的实验证明 MYSQL 8 添加字段,不在是一个问题,PT 工具可以收手了。

当然这里添加的是一般的column如果你要添加自增的列,则就不可以这样做,还是需要不能进行 DML 操作,类似锁表的操作,好在MYSQL 里面添加自增序列的人不多,大多都是添加普通字段而已。

虽然可以瞬间将阻碍的DML 操作终止,并快速添加字段,但这在生产上来说对应用程序的某些事务性的操作时有害的,所以使用的时候,要小心,避免产生不愿意发生的“特殊情况”。instant 好处是只对数据字典中的元数据进行更改。在SE更改期间不需要获取元数据锁,也不涉及表的数据。这个更改也影响了LOCK=…语义。没有必要为INSTANT algorihtm指定锁。

任何不能立即完成的操作设置ALGORITHM=INSTANT,您将得到一个错误,如下所示。这里的思想是预先失败并快速失败,而不是进行无声的转换并在幕后切换到另一个算法。

所以这是要注意的。这个功能是由腾讯游戏的DBA 团队提出的功能改进。

那这个更改对实际当中的意义在哪里

1 对于大型表,这可能需要很长时间,特别是在复制设置中。 2 磁盘空间需求将增加一倍以上,大致与现有表的大小相同。 3 DDL操作需要大量资源,对CPU、内存和IO的要求很高。这将从用户事务中窃取资源。 4 如果涉及复制,用户可能需要等待更长的时间才能准备好从服务器。DDL完成后将外部化

粗浅的说完alter table 的 instant 的问题, 下面的说说经常要添加索引的问题,在添加索引时是不能使用instant的功能的。目前在MYSQL 8 里面的最优的还是inplace的方。(在你无法使用工具的时候)

我们继续,一个测试,我们往test表里面插入数据,同时在另一个线程添加索引。

session 1

session 2

他大致的操作步骤

  • 新建frm临时文件
  • 锁原表,不许DML,可以查询
  • 按聚集索引顺序,查数据,找索引列数据,排序并插入到新的索引页中
  • 原表不能读操作,也就是原表此时不提供读写服务
  • 进行rename操作,替换frm文件,完成DDL过程

从上边的图可以看到,索引已经添加并且表中国的数据也一直在插入,并没有产生什么看似不良的影响。(以上操作在MGR 集群中操作)

当然这不能说明,就不会有问题,生产系统的复杂性不是我们可以想象的,所以以上测试仅仅代表他能,但对非常繁忙的系统还是要小心。

而算法inplace——顾名思义,它修改表的模式,而不创建原始表的临时表,而是修改原始表本身。在更改表模式(DDL)期间,它不会导致对原始表的读写锁(数据操作语言)

算法copy——顾名思义,它改变了模式的现有表创建一个新的临时表改变模式(在我们的例子中,添加一个新的列),迁移到新的临时表的数据,改变了链接到新表,滴旧表,完成了。

使用ALGORITHM=COPY子句运行的ALTER TABLE操作可以防止并发的DML操作。仍然允许并发查询。也就是说,表复制操作总是至少包含LOCK=SHARED(允许查询,但不允许DML)的并发限制。您可以通过指定LOCK=EXCLUSIVE来进一步限制此类操作的并发性,这可以防止DML和查询。

目前写到这里,因为发现这篇文字已经接近 2千多字了,有同学反映,写的字太多,看的头疼,所以......

最后在啰嗦一下,加字段您INSTANT, 建索引您inplace ,其他能inplace 别copy。磁盘空间不能抠,DDL操作部分Double 磁盘空间。

本文分享自微信公众号 - AustinDatabases(AustinDatabases),作者:carol11

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-02-04

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MYSQL 主从不一致怎么办?

    MYSQL 中主从不一致是很可能发生的,而怎么能发现不一致则并不是一个很简单的问题。尤其对数据量较大的情况下。废话说多无益,看下面的例子:

    AustinDatabases
  • MYSQL 查询条件的函数不要乱用, 与随机函数怎么走索引

    偶然想起一事,具体的人和场景就不提了,事情是一条语句,明明是很简单的一句话,有索引,验证也是很快了,但只要在程序里面就慢的要死。后来发现是在语句后面使用了某函数...

    AustinDatabases
  • PostgreSQL 的JSON 处理甩“你”几条街

    首先这里的你绝对不是MONGODB ,至于是谁,你是谁,那的先了解POSTGRESQL 处理 JSON 的方式后,才能确定那个你是谁。

    AustinDatabases
  • 一个“良心未泯”的国产敲诈者病毒分析

    一、 前言 近两年,以敲诈勒索为目的的文件加密恶意软件逐渐成为恶意软件中的主力军。以Locky家族,ceber家族为典型代表的敲诈勒索软件席卷国外,对政府机构,...

    FB客服
  • 简述https的几种加密方式

    用户4143945
  • 计算机视觉下一个技术拐点?前端成像或将开启“视觉2.0时代”

    镁客网
  • 一行代码即可调用18款主流模型!PyTorch Hub轻松解决论文可复现性

    机器学习论文的可复现性一直是个难题。许多机器学习相关论文要么无法复现,要么难以重现。有时候论文读者经常为了调用各种经典机器学习模型,还要重复造轮子。

    新智元
  • 一行代码即可调用18款主流模型!PyTorch Hub轻松解决论文可复现性

    机器学习论文的可复现性一直是个难题。许多机器学习相关论文要么无法复现,要么难以重现。有时候论文读者经常为了调用各种经典机器学习模型,还要重复造轮子。

    用户2769421
  • 突破Java面试(33)-Dubbo的工作原理

    MQ、ES、Redis、Dubbo,上来先问你一些思考的问题,原理(kafka高可用架构原理、es分布式架构原理、redis线程模型原理、Dubbo工作原理),...

    JavaEdge
  • 灵活使用Maven Profile

    项目中一直应用Maven的profile特性解决不同环境的部署问题。最近在尝试解决本地调试环境的时候碰到一些问题,顺便仔细研究了一下。因为项目仍然在用普通Spr...

    程序猿讲故事

扫码关注云+社区

领取腾讯云代金券