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 的 增删改来进行的
从上面的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和查询。
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。