mysql 大表如何ddl
大家好,我是蓝胖子,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_definition
mysql会去判断当前执行的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.html
ddl 过程中从库的延迟性
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,它对表结构的修改原理是创建一张新表(拥有最新的表定义),然后在旧表上创建delete,update,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-method=none --execute --statistics
如果你的ddl需要拷贝表,那么用pt-online-schema-change 工具再合适不过了。
相关文章:

mysql 大表如何ddl
大家好,我是蓝胖子,mysql对大表(千万级数据)的ddl语句,在生产上执行时一定要千万小心,一不小心就有可能造成业务阻塞,数据库io和cpu飙高的情况。今天我们就来看看如何针对大表执行ddl语句。 通过这篇文章,…...

C++新特性:智能指针
一 、为什么需要智能指针 智能指针主要解决以下问题: 1)内存泄漏:内存手动释放,使用智能指针可以自动释放 2)共享所有权指针的传播和释放,比如多线程使用同一个对象时析构问题,例如同样的数据…...

SAP FI之批量修改财务凭证的BAPI
文章目录 前言一、pandas是什么?二、使用步骤 1.引入库2.读入数据总结 前言 一般涉及修改财务凭证,或者其它凭证,不应直接更新数据库,而是使用系统提供的function module,或者BAPI,或者使用BDC。 一、 示例…...
Spring Boot + Vue的网上商城之商品分类
Spring Boot Vue的网上商城之商品分类 在网上商城中,商品分类是非常重要的一个功能,它可以帮助用户更方便地浏览和筛选商品。本文将介绍如何使用Spring Boot和Vue来实现商品分类的功能,包括一级分类和二级分类的管理以及前台按分类浏览商品…...

Docker 容器逃逸漏洞 (CVE-2020-15257)复现
漏洞概述 containerd是行业标准的容器运行时,可作为Linux和Windows的守护程序使用。在版本1.3.9和1.4.3之前的容器中,容器填充的API不正确地暴露给主机网络容器。填充程序的API套接字的访问控制验证了连接过程的有效UID为0,但没有以其他方式…...

Python 如何使用 csv、openpyxl 库进行读写 Excel 文件详细教程(更新中)
csv 基本概述 首先介绍下 csv (comma separated values),即逗号分隔值(也称字符分隔值,因为分隔符可以不是逗号),是一种常用的文本格式,用以存储表格数据,包括数字或者字符。 程序在处理数据时…...

$nextTick属性使用与介绍
属性介绍 $nextTick 是 Vue.js 中的一个重要方法,之前我们也说过$ref 等一些重要的属性,这次我们说$nextTick,$nextTick用于在 DOM 更新后执行回调函数。它通常用于处理 DOM 更新后的操作,因为 Vue 在更新 DOM 后不会立即触发回调…...

【群智能算法改进】一种改进的鹈鹕优化算法 IPOA算法[2]【Matlab代码#58】
文章目录 【获取资源请见文章第5节:资源获取】1. 原始POA算法2. 改进后的IPOA算法2.1 随机对立学习种群初始化2.2 动态权重系数2.3 透镜成像折射方向学习 3. 部分代码展示4. 仿真结果展示5. 资源获取 【获取资源请见文章第5节:资源获取】 1. 原始POA算法…...

k8s 入门到实战--部署应用到 k8s
k8s 入门到实战 01.png 本文提供视频版: 背景 最近这这段时间更新了一些 k8s 相关的博客和视频,也收到了一些反馈;大概分为这几类: 公司已经经历过服务化改造了,但还未接触过云原生。公司部分应用进行了云原生改造&…...
编程语言新特性:instanceof的改进
以前也写过类似的博文,可能重复。 要判断一个对象是哪个类或父类的实例,JAVA用到instanceof,其实语言也有类似语法。而类一般是多层继承的,有时就让人糊涂。所以我提出改进思路: instanceof:保持不变。ins…...

数据挖掘的学习路径
⭐️⭐️⭐️⭐️⭐️欢迎来到我的博客⭐️⭐️⭐️⭐️⭐️ 🐴作者:秋无之地 🐴简介:CSDN爬虫、后端、大数据领域创作者。目前从事python爬虫、后端和大数据等相关工作,主要擅长领域有:爬虫、后端、大数据…...
逻辑回归Logistic
回归 概念 假设现在有一些数据点,我们用一条直线对这些点进行拟合(这条直线称为最佳拟合直线),这个拟合的过程就叫做回归。进而可以得到对这些点的拟合直线方程。 最后结果用sigmoid函数输出 因此,为了实现 Logisti…...

Flink提交jar出现错误RestHandlerException: No jobs included in application.
今天打包一个flink的maven工程为jar,通过flink webUI提交,发现居然报错。 如上图所示,提示错误为: Server Response Message: org.apache.flink.runtime.rest.handler.RestHandlerException: No jobs included in application. …...
【数仓基础(一)】基础概念:数据仓库【用于决策的数据集合】的概念、建立数据仓库的原因与好处
文章目录 一. 数据仓库的概念1. 面向主题2. 集成3. 随时间变化4. 非易失粒度 二. 建立数据仓库的原因三. 使用数据仓库的好处 一. 数据仓库的概念 数据仓库的主要作用: 数据仓库概念主要是解决多重数据复制带来的高成本问题。 在没有数据仓库的时代,需…...

电商类面试问题--01Elasticsearch与Mysql数据同步问题
在实现基于关键字的搜索时,首先需要确保MySQL数据库和ES库中的数据是同步的。为了解决这个问题,可以考虑两层方案。 全量同步:全量同步是在服务初始化阶段将MySQL中的数据与ES库中的数据进行全量同步。可以在服务启动时,对ES库进…...

天线材质介绍--FPC天线
...
vue3 的 ref、 toRef 、 toRefs
1、ref: 对原始数据进行拷贝。当修改 ref 响应式数据的时候,模版中引用 ref 响应式数据的视图处会发生改变,但原始数据不会发生改变 <template><div>{{refA}}</div> </template><script lang"ts" setup> impor…...

WebRTC中 setup:actpass、active、passive
1、先看一下整个DTLS的流程 setup:actpass、active、passive就发生在Offer sdp和Anser SDP中 Offer的SDP是setup:actpass,这个是服务方: v0\r o- 1478416022679383738 2 IN IP4 127.0.0.1\r s-\r t0 0\r agroup:BUNDLE 0 1\r aextmap-allow-mixed\r amsid-semanti…...

ModuleNotFoundError: No module named ‘lavis‘解决方案
大家好,我是爱编程的喵喵。双985硕士毕业,现担任全栈工程师一职,热衷于将数据思维应用到工作与生活中。从事机器学习以及相关的前后端开发工作。曾在阿里云、科大讯飞、CCF等比赛获得多次Top名次。现为CSDN博客专家、人工智能领域优质创作者。喜欢通过博客创作的方式对所学的…...
双指针的问题解法以及常见的leetcode例题。
目录 介绍: 问题1:双指针 剑指offer57 和为S的两个数字。 问题2:剑指Offer 21. 调整数组顺序使奇数位于偶数前面 问题3:连续奇数子串(笔试遇到的真题) 问题4:滑动窗口的最大值 介绍&#…...

Linux应用开发之网络套接字编程(实例篇)
服务端与客户端单连接 服务端代码 #include <sys/socket.h> #include <sys/types.h> #include <netinet/in.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <arpa/inet.h> #include <pthread.h> …...
Vue记事本应用实现教程
文章目录 1. 项目介绍2. 开发环境准备3. 设计应用界面4. 创建Vue实例和数据模型5. 实现记事本功能5.1 添加新记事项5.2 删除记事项5.3 清空所有记事 6. 添加样式7. 功能扩展:显示创建时间8. 功能扩展:记事项搜索9. 完整代码10. Vue知识点解析10.1 数据绑…...

3.3.1_1 检错编码(奇偶校验码)
从这节课开始,我们会探讨数据链路层的差错控制功能,差错控制功能的主要目标是要发现并且解决一个帧内部的位错误,我们需要使用特殊的编码技术去发现帧内部的位错误,当我们发现位错误之后,通常来说有两种解决方案。第一…...
FastAPI 教程:从入门到实践
FastAPI 是一个现代、快速(高性能)的 Web 框架,用于构建 API,支持 Python 3.6。它基于标准 Python 类型提示,易于学习且功能强大。以下是一个完整的 FastAPI 入门教程,涵盖从环境搭建到创建并运行一个简单的…...
工业自动化时代的精准装配革新:迁移科技3D视觉系统如何重塑机器人定位装配
AI3D视觉的工业赋能者 迁移科技成立于2017年,作为行业领先的3D工业相机及视觉系统供应商,累计完成数亿元融资。其核心技术覆盖硬件设计、算法优化及软件集成,通过稳定、易用、高回报的AI3D视觉系统,为汽车、新能源、金属制造等行…...
是否存在路径(FIFOBB算法)
题目描述 一个具有 n 个顶点e条边的无向图,该图顶点的编号依次为0到n-1且不存在顶点与自身相连的边。请使用FIFOBB算法编写程序,确定是否存在从顶点 source到顶点 destination的路径。 输入 第一行两个整数,分别表示n 和 e 的值(1…...
高效线程安全的单例模式:Python 中的懒加载与自定义初始化参数
高效线程安全的单例模式:Python 中的懒加载与自定义初始化参数 在软件开发中,单例模式(Singleton Pattern)是一种常见的设计模式,确保一个类仅有一个实例,并提供一个全局访问点。在多线程环境下,实现单例模式时需要注意线程安全问题,以防止多个线程同时创建实例,导致…...
智能AI电话机器人系统的识别能力现状与发展水平
一、引言 随着人工智能技术的飞速发展,AI电话机器人系统已经从简单的自动应答工具演变为具备复杂交互能力的智能助手。这类系统结合了语音识别、自然语言处理、情感计算和机器学习等多项前沿技术,在客户服务、营销推广、信息查询等领域发挥着越来越重要…...
C++.OpenGL (20/64)混合(Blending)
混合(Blending) 透明效果核心原理 #mermaid-svg-SWG0UzVfJms7Sm3e {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-icon{fill:#552222;}#mermaid-svg-SWG0UzVfJms7Sm3e .error-text{fill…...

RSS 2025|从说明书学习复杂机器人操作任务:NUS邵林团队提出全新机器人装配技能学习框架Manual2Skill
视觉语言模型(Vision-Language Models, VLMs),为真实环境中的机器人操作任务提供了极具潜力的解决方案。 尽管 VLMs 取得了显著进展,机器人仍难以胜任复杂的长时程任务(如家具装配),主要受限于人…...