在 MySQL 8.0.12 中，我们引入了一种新的 DDL 算法，该算法在更改表的定义时不会阻塞表。第一个即时操作是在表格末尾添加一列，这是来自腾讯游戏的贡献。
然后在 MySQL 8.0.29 中，我们添加了在表中任意位置添加（或删除）列的可能性。
在这篇文章中，我想重点讨论盲目使用此功能时可能发生的一些危险。
默认算法
从 MySQL 8.0.12 开始，对于任何支持的 DDL，默认算法是 INSTANT。这意味着 ALTER 语句只会修改数据字典中表的元数据。在操作的准备和执行阶段，不会对表进行独占元数据锁，表数据不受影响，使得操作是即时的。
另外两种算法是 COPY 和 INPLACE，Online DDL 操作参见手册。
然而，即使支持操作，Online DDL 也存在限制：一个表支持 64 次即时更改。到限制后，需要“重建”该表。
如果在 ALTER 语句（DDL 操作）期间未指定算法，则会默默地选择适当的算法。当然，如果没有预料到，这可能会导致生产中出现噩梦般的情况。
始终指定算法
因此，第一个建议始终是指定算法，即使它是执行 DDL 时的默认算法。当指定算法时，如果 MySQL 无法使用它，它将抛出错误，而不是使用其他算法执行操作：
SQL > ALTER TABLE t1 DROP col1, ALGORITHM=INSTANT;
ERROR: 4092 (HY000): Maximum row versions reached for table test/t1.
No more columns can be added or dropped instantly. Please use COPY/INPLACE.
监控即时变化
第二个建议也是监视对表执行的即时更改的数量。
MySQL 在 information_schema 表中保留行版本：
SQL > SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 | 63 |
+---------+--------------------+
在上面的示例中，DBA 将能够执行一项额外的 INSTANT DDL 操作，但在此之后，MySQL 将无法执行另一项操作。
作为 DBA，监视所有表并决定何时需要重建表（以重置该计数器）是一个很好的做法。
这是添加到监控工具的建议查询的示例：
SQL > SELECT NAME, TOTAL_ROW_VERSIONS, 64-TOTAL_ROW_VERSIONS AS
"REMAINING_INSTANT_DDLs",
ROUND(TOTAL_ROW_VERSIONS/64 * 100,2) AS "DDLs %"
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE TOTAL_ROW_VERSIONS > 0 ORDER BY 2 DESC;
+--------------------------+--------------------+------------------------+--------+
| NAME | TOTAL_ROW_VERSIONS | REMAINING_INSTANT_DDLs | DDLs % |
+--------------------------+--------------------+------------------------+--------+
| test/t1 | 63 | 1 | 98.44 |
| test/t | 4 | 60 | 6.25 |
| test2/t1 | 3 | 61 | 4.69 |
| sbtest/sbtest1 | 2 | 62 | 3.13 |
| test/deprecation_warning | 1 | 63 | 1.56 |
+--------------------------+--------------------+------------------------+--------+
要重置计数器并重建表，可以使用 OPTIMIZE TABLE
| 类型 | 地址 |
|---|---|
| 版本库 | https://github.com/actiontech/sqle |
| 文档 | https://actiontech.github.io/sqle-docs/ |
| 发布信息 | https://github.com/actiontech/sqle/releases |
| 数据审核插件开发文档 | https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse |
著作权归作者所有
热门内容
全站热门评论
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
关于作者
推荐关注
OSCHINA 社区
联系我们
加入我们
合作伙伴
Open API
在线工具
企业研发管理
CopyCat-代码克隆检测
实用在线工具
攻略
Awesome 软件（持续更新中）
QQ群
公众号
视频号
开源软件推进联盟
社区规范
粤ICP备12009483号
.codeBlock:hover .oscCode{display: block !important;} .codeBlock{z-index: 2;position: fixed;right: 20px;bottom: 57px; overflow: hidden; margin-bottom: 4px;padding: 8px 0 6px;width: 40px;height: auto;box-sizing: content-box;cursor: pointer;border: 1px solid #ddd;background: #f5f5f5;text-align: center;transition: background 0.4s ease;}
@media only screen and (max-width: 767px){ .codeBlock{display: none;}}
/*
html{
-webkit-filter: grayscale(100%);
-moz-filter: grayscale(100%);
-ms-filter: grayscale(100%);
-o-filter: grayscale(100%);
filter:progid:DXImageTransform.Microsoft.BasicImage(grayscale=1);
_filter:none;
}
*/
if(window.location.href.indexOf("www.oschina.net/group")!=-1 && window.location.href.indexOf("/admin/")!=-1){
document.querySelector("#mainScreen > div > div.group-admin-container > div.admin-body-box.box-card > div > div.menu-box > div > div:nth-child(4)").remove()
}
(function(){
var bp = document.createElement('script');
var curProtocol = window.location.protocol.split(':')[0];
if (curProtocol === 'https'){
bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
}
else{
bp.src = 'http://push.zhanzhang.baidu.com/push.js';
}
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(bp, s);
})();
var _hmt = _hmt || [];
_hmt.push(['_requirePlugin', 'UrlChangeTracker', {
shouldTrackUrlChange: function (newPath, oldPath) {
return newPath && oldPath;
}}
]);
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?a411c4d1664dd70048ee98afe7b28f0b";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
{
"@context": "https://ziyuan.baidu.com/contexts/cambrian.jsonld",
"@id": "https://my.oschina.net/actiontechoss/blog/11050457",
"appid": "1653861004982757",
"title":"MySQL DBA 需要了解一下 InnoDB Online DDL 算法更新 - 爱可生开源社区",
"images": [""],
"description":"在 MySQL 8.0.12 中，我们引入了一种新的 DDL 算法，该算法在更改表的定义时不会阻塞表。第一个即时操作是在表格末尾添加一列，这是来自腾讯游戏的贡献。 然后在 MySQL 8.0.29 中，我们添加了在表中任意位置添加（...",
"pubDate": "2024-04-02T17:01:39+08:00",
"upDate":"2024-04-02T17:01:39+08:00",
"lrDate":""
}
<!--
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-TK89C9ZD80');
-->
window.goatcounter = {
path: function(p) { return location.host + p }
}
(function(){
var el = document.createElement("script");
el.src = "https://lf1-cdn-tos.bytegoofy.com/goofy/ttzz/push.js?2f2c965c87382dadf25633a3738875e5ccd132720338e03bf7e464e2ec709b9dfd9a9dcb5ced4d7780eb6f3bbd089073c2a6d54440560d63862bbf4ec01bba3a";
el.id = "ttzz";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(el, s);
})(window)
xz 遭受恶意代码攻击，众多 Linux 发行版受影响
Mojo 编程语言正式开源标准库，宣称比 Python 快 9 万倍
Podman 5.0 正式发布
iOS 版 Firefox 充斥着大量“屎山代码”
知名开源前端框架「威优易」，你们学吗？
百度智能云发布 Baidu Comate 2.0，个人开发者可免费使用
Ubuntu 24.04 将 Cheese 替换为 GNOME Snapshot
周鸿祎自称“开源信徒”，宣布将开源 360 智脑 7B 模型，支持 50 万字长文本输入
Linux 基金会创建 Redis 分支：Valkey
谷歌 Rust 团队工作效率是 C++ 团队的两倍
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
一：背景 1. 讲故事 很多.NET开发者在学习高级调试的时候，使用sos的命令输出会发现这里也看不懂那里也看不懂，比如截图中的这位朋友。 .NET高级调试属于一个偏冷门的领域，国内可观测的资料比较少，所以很多东西需要你自己去探究源代码，然后用各种调试工具去验…
点击引领话题
发布并加入讨论