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:滑动窗口的最大值 介绍&#…...

装饰模式(Decorator Pattern)重构java邮件发奖系统实战
前言 现在我们有个如下的需求,设计一个邮件发奖的小系统, 需求 1.数据验证 → 2. 敏感信息加密 → 3. 日志记录 → 4. 实际发送邮件 装饰器模式(Decorator Pattern)允许向一个现有的对象添加新的功能,同时又不改变其…...

Day131 | 灵神 | 回溯算法 | 子集型 子集
Day131 | 灵神 | 回溯算法 | 子集型 子集 78.子集 78. 子集 - 力扣(LeetCode) 思路: 笔者写过很多次这道题了,不想写题解了,大家看灵神讲解吧 回溯算法套路①子集型回溯【基础算法精讲 14】_哔哩哔哩_bilibili 完…...

【7色560页】职场可视化逻辑图高级数据分析PPT模版
7种色调职场工作汇报PPT,橙蓝、黑红、红蓝、蓝橙灰、浅蓝、浅绿、深蓝七种色调模版 【7色560页】职场可视化逻辑图高级数据分析PPT模版:职场可视化逻辑图分析PPT模版https://pan.quark.cn/s/78aeabbd92d1...
Vite中定义@软链接
在webpack中可以直接通过符号表示src路径,但是vite中默认不可以。 如何实现: vite中提供了resolve.alias:通过别名在指向一个具体的路径 在vite.config.js中 import { join } from pathexport default defineConfig({plugins: [vue()],//…...

阿里云Ubuntu 22.04 64位搭建Flask流程(亲测)
cd /home 进入home盘 安装虚拟环境: 1、安装virtualenv pip install virtualenv 2.创建新的虚拟环境: virtualenv myenv 3、激活虚拟环境(激活环境可以在当前环境下安装包) source myenv/bin/activate 此时,终端…...
多元隐函数 偏导公式
我们来推导隐函数 z z ( x , y ) z z(x, y) zz(x,y) 的偏导公式,给定一个隐函数关系: F ( x , y , z ( x , y ) ) 0 F(x, y, z(x, y)) 0 F(x,y,z(x,y))0 🧠 目标: 求 ∂ z ∂ x \frac{\partial z}{\partial x} ∂x∂z、 …...

小智AI+MCP
什么是小智AI和MCP 如果还不清楚的先看往期文章 手搓小智AI聊天机器人 MCP 深度解析:AI 的USB接口 如何使用小智MCP 1.刷支持mcp的小智固件 2.下载官方MCP的示例代码 Github:https://github.com/78/mcp-calculator 安这个步骤执行 其中MCP_ENDPOI…...

Python环境安装与虚拟环境配置详解
本文档旨在为Python开发者提供一站式的环境安装与虚拟环境配置指南,适用于Windows、macOS和Linux系统。无论你是初学者还是有经验的开发者,都能在此找到适合自己的环境搭建方法和常见问题的解决方案。 快速开始 一分钟快速安装与虚拟环境配置 # macOS/…...

Selenium 查找页面元素的方式
Selenium 查找页面元素的方式 Selenium 提供了多种方法来查找网页中的元素,以下是主要的定位方式: 基本定位方式 通过ID定位 driver.find_element(By.ID, "element_id")通过Name定位 driver.find_element(By.NAME, "element_name"…...

理想汽车5月交付40856辆,同比增长16.7%
6月1日,理想汽车官方宣布,5月交付新车40856辆,同比增长16.7%。截至2025年5月31日,理想汽车历史累计交付量为1301531辆。 官方表示,理想L系列智能焕新版在5月正式发布,全系产品力有显著的提升,每…...