![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-cf0d92129138e2c138e143696168013a.png)
在 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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-560240836b0f3c495516cdfed0d36e1b.jpg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-401f020e9d9f97afcf00bf8998656e01.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-c78f2f1d790054c50d7ee6ed5eafd065.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-689d0fcd3ad548d0c83178e65c463c26.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-262e3e7c793f2f7bc888bf3d8bfb730c.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-affa076a0c67ba444c02a4b4e7e99207.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-43da0c259a9cba9658ef3a9c8fe57cea.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-77c81c9eb76b8baf5742605272a3a074.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-2b3722e849d13ef9e7d3fd5932fef380.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-fb1947e37fb7a21d1dc9c139cd644b39.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-6f82fe44bc971b25a7f634ecb5f9c61a.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-940dbd296e32bee1c850d00c8f3755de.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-7429492922211c5b4076bf00dc333725.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-1ea7c5490078238b5b360220d26d4838.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-2c6c01f46eccbbd455741521c96a8111.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-5fb7e208aa61e82fb55f885eead30980.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-8cd456e0e7cfd6cd317b983d2834b19b.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-bccaecb623fad027c66fbc1d762e0642.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-c78f2f1d790054c50d7ee6ed5eafd065.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-49230d446418d45f5d7b65310db211da.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-65d07132c1dc5957ff3e46fb628733d8.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-fb1947e37fb7a21d1dc9c139cd644b39.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-1888d374439234c4780f670e0962d5a8.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-07768aa4580f399e6bcda3a3a8032433.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-fb1947e37fb7a21d1dc9c139cd644b39.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-368a438290961e91adfcbb762fbc7ea3.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-a34b55cdb8619379b33e3818a0024424.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-708a27bb37a4d2c361c8cc4997933d7b.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-1567b427e293f986a1abb068d97cd0d2.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-32fcd9c48dcd376196e992c558e3e8f9.gif)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-c7a4ad4875b1f70624d82a1f3671db6b.jpg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-5647d82dfd8c4bf8c034edb2910e381c.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-cbc734a928453a665b3eaf252d7f0c39.jpeg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-3e4a309153e6f330bb8e98f4cf9a37db.png)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-2c6c01f46eccbbd455741521c96a8111.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-5bc6971a26d7039ac0e3e104513f5141.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-bccaecb623fad027c66fbc1d762e0642.jpeg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-263473f144a45e5ca207128f2f9f9a3b.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-77c81c9eb76b8baf5742605272a3a074.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-17372aeaab83e65a367cef02d9a7a345.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-c78f2f1d790054c50d7ee6ed5eafd065.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-c81456c40da51316b3e78ec67977645f.jpeg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-bccaecb623fad027c66fbc1d762e0642.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-32fcd9c48dcd376196e992c558e3e8f9.gif)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-74e9837489e4b7c2da6a6a44038a50cc.jpg)
.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)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-0230353d764acc1e8b5f72d8f71a947f.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-10ac835904c6c72fc8552e3a16b145bb.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-5b364b0e35224920c91e0dac06ddaeee.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-20f4f2f17e22956960f2c0695fd2d68a.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-449de1ce8acf2351f8f1dbde580308ac.jpeg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-900ae064d46bb5db7aa7406f628c48b3.jpg)
.comments-box__list-news .osc-avatar{border-radius: 5px}.comments-box__list-news .comment-item:hover{background: rgba(201,201,201,0.1)}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-1fe8572a245f5f430289b6936b5b92e7.jpg)
开源软件推进联盟
社区规范
粤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;
}
*/
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-24af0afdc5a416783b8aa724020d62ea.png)
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()
}
![](http://www.e1idc.net/wp-content/uploads/2024/04/frc-723200b4b6344b2fb5d4ff89480cb511.jpg)
(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
PostgreSQL 主要贡献者 Simon Riggs 因坠机去世
点击引领话题📣
发布并加入讨论🔥