所在的位置: mysql >> mysql发展 >> MySQL的OnlineDDL语句

MySQL的OnlineDDL语句

概念

什么是OnlineDDL?在了解OnlineDDL之前,我们先来了解一下什么是DDL。说DDL,就不得不提一下它的另外两个兄弟:DML和DCL。

他们三者的区别如下:

DDL:datadefinitionlanguage,数据定义语言,用来定义数据的语句。例如我们平时的createtable,createindex,createfunction等。DML:datamanipulationlanguage,数据修改语言,用来操作数据的语句。例如我们平时的select,insert,update,delete语句。DCL:datacontrollanguage,数据控制语言,用来定义数据访问控制的语句。例如我们平时的grant,revoke等。而OnlineDDL是指在线的数据定义语言,所谓的在线是指:在数据表在不停止服务的情况下来应用我们的DDL语句,即表在应用DDL语句的时候,仍然可以提供读写的服务。

诉求背景

我们平时所说的在线的DDL操作一般情况是指以下操作:

在表中增加字段在表中增加索引在表中修改字段类型删除表中的字段删除表中的索引如果是一张小表,只有几百行或者几千行的数据表,我们要增加字段或增加索引,基本不用考虑什么,一个字:干,就完了。

但如果是一个大表呢?表中几千万,上亿条数据。怎么办?

我们之所以不敢直接干的原因,大概是担心以下问题的发生,这也是我们经常锁的MySQL的onlineDDL要格外注意的原因。

表被锁上,不能进行正常的读写或者写。不能写还好一下,最怕是连读都不能读。如果有主从复制架构,担心主从同步出现大幅度的延迟。解决方法

原生支持的方式

在5.5之前的版本,这些都是我们比较担心的问题。但是,在MySQL5.6和5.7版本中,默认情况下,MySQL就是支持online的DDL操作的,在online的DDL语句执行的过程中,MySQL会尽量少使用锁的限制,我们不需要特殊的操作来启用它。

MySQL在选择的时候,尽量少使用锁,但是不排除它会选择使用锁。而如果我担心它选择了锁而导致我们的表不能读也不能写,显然这不是我们想要的结果,我们希望:如果选择了锁就不要执行,直接退出执行;如果没有选择锁就执行。想要达到我们希望的这个效果,该怎么做呢?

可以在执行我们的onlineDDL语句的时候,使用ALGORITHM和LOCK关键字,这两个关键字在我们的DDL语句的最后面,用逗号隔开即可。示例如下:

ALTERTABLEtbl_nameADDCOLUMNcol_namecol_type,ALGORITHM=INPLACE,LOCK=NONE;

其中的ALGORITHM有如下选项INPLACE:替换:直接在原表上面执行DDL的操作。COPY:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的操作。执行期间,表不允许DML的操作。DEFAULT:默认方式,有MySQL自己选择,优先使用INPLACE的方式。其中的LOCK有如下选项SHARE:共享锁,执行DDL的表可以读,但是不可以写。NONE:没有任何限制,执行DDL的表可读可写。EXCLUSIVE:排它锁,执行DDL的表不可以读,也不可以写。DEFAULT:默认值,也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT,那就是交给MySQL子句去觉得锁还是不锁表。不建议使用,如果你确定你的DDL语句不会锁表,你可以不指定lock或者指定它的值为default,否则建议指定它的锁类型。执行DDL操作时,ALGORITHM选项可以不指定,这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT,也是同样的效果。如果指定了ALGORITHM选项,但不支持的话,会直接报错。

注意:在执行OnlineDDL之前,要在非业务高峰期去执行,并要确认待执行的表上面没有未提交的事务、锁等信息。可以通过如下的SQL语句查看是否有事务和锁等信息。

select*frominformation_schema.innodb_locks;select*frominformation_schema.innodb_trx;select*frominformation_schema.innodb_lock_waits;select*frominformation_schema.processlist;

使用第三方工具

首选,推荐使用这种方式,因为这种工具已经很早大家都在用,效果还不错,不易出现问题。

借助第三方工具:pt-online-schema-change

Percona工具PT-OSC(PerconaToolkitOnlineSchemaChange)

下载安装PerconaToolkit

下载二进制压缩包

下载的时候,可以选择各种格式的安装文件,我们这里使用下载编译好的二进制压缩包,解压后即可直接使用里面的命令。

下载


转载请注明:http://www.aierlanlan.com/tzrz/2495.html