温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MYSQL的DDL该怎么理解及应用

发布时间:2022-01-04 10:40:48 来源:亿速云 阅读:150 作者:柒染 栏目:大数据

MYSQL的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 的 增删改来进行的

MYSQL的DDL该怎么理解及应用

从上面的8.0 提供的表来看

MYSQL的DDL该怎么理解及应用

下面是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  数据字典表不可以

MYSQL的DDL该怎么理解及应用

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

我们看看结果如何

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

MYSQL的DDL该怎么理解及应用

SESSION 2

MYSQL的DDL该怎么理解及应用

字段瞬间添加上了,但是存储过程在运行的途中直接报错,通过上表的实验证明 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 

MYSQL的DDL该怎么理解及应用

session 2

MYSQL的DDL该怎么理解及应用

他大致的操作步骤

  • 新建frm临时文件

  • 锁原表,不许DML,可以查询

  • 按聚集索引顺序,查数据,找索引列数据,排序并插入到新的索引页中

  • 原表不能读操作,也就是原表此时不提供读写服务

  • 进行rename操作,替换frm文件,完成DDL过程


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

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

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

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

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

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI