安徽网站建设网络公司,深圳龙岗网站建设,深圳做网站的公司哪个好,wordpress媒体库添加分类大家好#xff0c;我是蓝胖子#xff0c;mysql对大表(千万级数据)的ddl语句#xff0c;在生产上执行时一定要千万小心#xff0c;一不小心就有可能造成业务阻塞#xff0c;数据库io和cpu飙高的情况。今天我们就来看看如何针对大表执行ddl语句。
通过这篇文章#xff0c;…大家好我是蓝胖子mysql对大表(千万级数据)的ddl语句在生产上执行时一定要千万小心一不小心就有可能造成业务阻塞数据库io和cpu飙高的情况。今天我们就来看看如何针对大表执行ddl语句。
通过这篇文章你能了解到下面的知识点, 传统ddl 和online ddl的区别
mysql的ddl 经过了几个版本的演进Online DDL这个特性是在MySQL5.6.7开始支持在此之前mysql执行ddl语句时会生成新表然后将原表数据复制到新表整个过程是会阻塞DML语句的。
而online ddl 定义其实就是在执行ddl语句时不会阻塞dml语句那么我们就称这样的ddl为online ddl。
ddl 的算法参数选项又分为 copy, Inplace, INSTANT 其中copy就是之前传统ddl执行的过程会阻塞dml语句。Inplace, INSTANT 算法执行期间 都是可以执行DML语句的所以我们称使用这两种算法的ddl语句为online ddl。 但需要注意的是并不是所有的ddl操作都支持这两种算法具体什么ddl操作类型支持什么算法需要去查阅官方文档。 INSTANT 算法是mysql8.0 以后新加的它能在秒级别对千万级别的大表进行加字段操作至于其他ddl 语句类型是不是也支持INSTANT 算法需要去看下官网了由于我们线上还是使用的mysql5.7 所以我还是会给予mysql5.7去进行分析。
在mysql5.7中例如我们执行下面的ddl 加字段的语句,
ALTER TABLE tbl_name ADD COLUMN column_name column_definitionmysql会去判断当前执行的ddl语句类型能不能用online ddl inplace 方式如果能用那么它就会采用。
使用Inplace算法的ddl语句执行过程分为3个阶段
阶段1: Initialization初始化
在初始化阶段服务器将考虑存储引擎功能、语句中指定的操作以及用户指定的ALGORITHM和LOCK选项确定操作期间允许多少并发性。在此阶段使用一个可升级MDL读锁来保护当前表定义。
阶段2:Execution执行
如果评估阶段发现ddl语句不能使用inplace算法则会将mdl读锁升级为排它锁阻塞DML语句执行。并且这个阶段会真正的执行ddl语句。
阶段3:Commit Table Definition 提交表定义
在提交表定义阶段MDL读锁升级为MDL排他锁以排除旧表定义并提交新表定义。一旦授予独占MDL锁的持续时间就会很短。
可以看到如果使用inplcae 算法只有在任务提交阶段(时间很短) ddl才会阻塞dml语句因为任务提交阶段会持有MDL 排他锁而DML 语句执行时需要获取MDL读锁所以在此期间DML语句会被阻塞。
具体哪些ddl操作类型支持Inplace 算法可以查看官方文档链接比如下面的mysql5.7的文档
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html如下图所示可以发现mysql5.7对加字段的ddl 支持inplace 算法不过执行期间需要rebuild table即建立新表并且运行并发的dml语句执行。但是改变字段数据类型ddl则只能按copy算法进行执行。 inplace 算法不是不会产生数据的复制只是复制期间不会阻塞dml语句的执行。 mysql ddl 的陷阱
online ddl机制是否一定不会阻塞业务?
接着我们来看下ddl时使用inplcae 算法(online ddl)是不是一定不会阻塞业务其实答案是显而易见的业务也有可能阻塞因为online ddl 在提交表定义阶段是会获取MDL排他锁的如果有其他事务获取了MDL读锁那么online ddl 语句也会阻塞住从而导致发生在ddl语句执行时间点后面的那些需要获取MDL锁的sql阻塞掉。具体的操作例子可以查看mysql官方给出的一个例子
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-performance.htmlddl 过程中从库的延迟性
ddl的第二个陷阱是要注意从库的延迟性比如mysql5.7加新列虽然默认可以使用inplace算法来让dml语句不阻塞但是建立新列还是需要表的rebuild操作如果是大表整个过程还是很慢的如果从库只开启了一个线程去执行主从复制就会导致主从库间出现极大的延迟。
解决办法是开启并行复制可以用下面的语句在从库上执行查看从库是否开启了并行复制
SHOW VARIABLES LIKE slave_parallel_workers;online ddl Duplicate entry…错误
虽然使用inplace算法的ddl (online ddl) 可以不阻塞业务操作但是在大表上执行时由于ddl过程比较长还是有可能会出现Duplicate entry 错误。下面我来介绍下它出现的场景比如一张几千万的表里面有一个唯一键在add column ddl期间对表进行插入并且插入的值刚好就触发了唯一键约束。那么最后ddl再快完成的时候就会出现这个错误。
这是由于add column ddl期间会发生表的rebuild相当于新建一个临时表然后对旧表进行拷贝但是ddl期间还是允许业务修改插入数据所以online ddl将执行期间新的修改记录到一个叫做row_log的对象里在ddl最后阶段将mdl锁升级为排它锁然后将row_log对象中的数据和新表的数据进行合并这样就达到了ddl期间兼容dml操作的目的。
但是应用row log的过程是不允许报错如果期间发生了报错就会导致ddl回滚因为在ddl期间记录了相同唯一键的数据所以在应用row log的时候产生了报错。
官方也给出了online ddl 报错的场景连接如下
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-failure-conditions.html其实我认为本质原因是mysql5.7 执行add column 的ddl时间还是太长了在这么长时间里可能就会发生业务对相同唯一键的插入操作如果能缩短ddl执行时间应该就能很大程度避免这种问题。
mysql8.0 在add column 时可以采用instance 算法能达到秒级别的加新字段的操作理论上可以避免这个错误。
如果不是mysql8.0 又想对千万级的大表添加字段又要避免Duplicate entry 错误那么可以使用pt-online-schema-change这个工具。
pt-online-schema-change 工具进行字段添加
下面我就来简单的介绍下pt-online-schema-change它对表结构的修改原理是创建一张新表(拥有最新的表定义)然后在旧表上创建deleteupdate,insert的触发器来对增量数据进行更新对旧表数据采取insert ignore 新表 select 老表 LOCK S 的方式进行分块拷贝最后拷贝完成后在一个事务里对旧表进行删除新表进行重命名这样就完成了对表结构的变更。
同时在变更期间你能够通过下面的参数控制从库延迟
–max-lag 默认1s检查从库延迟的时间如果超过则停止copy data休息–check-interval秒后再重新开始copy数据查看通过延迟时间是通过从库show slave status查看Seconds_Behind_Master如果指定–check-slave-lag该工具只检查该服务器的延迟而不是所有服务器。 –check-interval 从库延迟超过指定的–max-lag中断copy data休息的时间默认为1s
下面是pt-online-schema-change 语句执行的完整示例,它同时会列出拷贝过程完成的百分比。
pt-online-schema-change --alter add pkg_source tinyint(2) default 0 not null; h主机ip,P端口,p密码,u用户名,D数据库名,t表明 --recursion-methodnone --execute --statistics如果你的ddl需要拷贝表那么用pt-online-schema-change 工具再合适不过了。
