MySQL 8.0 笔记(二)--Online DDL INSTANT
1. online DDL
The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include:
• Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
• For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the LOCK clause
• Less disk space usage and I/O overhead than the table-copy method.
Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible.
2. INSTANT
1.As of MySQL 8.0.12, ALGORITHM=INSTANT is supported for ALTER TABLE operations.Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by operations that support it. If ALGORITHM=INSTANT is specified but not supported, the operation fails immediately with an error.
2. Prior to MySQL 8.0.29, an instantly added column could only be added as the last column of the table. From MySQL 8.0.29, an instantly added column can be added to any position in the table.
3. Instantly added or dropped columns create a new version of the affected row. Up to 64 row versions are permitted. A new TOTAL_ROW_VERSIONS column was added to the Information Schema INNODB_TABLES table to track the number of row versions.
3. 注意事项
1. Tables that use ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables 不支持 使用ALGORITHM=INSTANT。Temporary tables only support ALGORITHM=COPY.。
2.A new row version is created after each ALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.
需要注意的时,即使不指明 ALGORITHM=INSTANT,从8.0.29 之后,默认的就是ALGORITHM=INSTANT。
3. When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.
TOTAL_ROW_VERSIONS 数值会从零增长到64.到64的时候,如果执行了OPTIMIZE TABLE 操作,或者是执行了 可以导致table-rebuilding 的 ALTER TABLE 操作,TOTAL_ROW_VERSIONS 会自动重置为0。
当 TOTAL_ROW_VERSIONS 为64时,还显式 ---sql 语句中 指明ALGORITHM=INSTANT,就会报错。
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
4.如果 TOTAL_ROW_VERSIONS 等于64,alter table 语句中,不显式指明ALGORITHM=INSTANT,执行会不会报错呢?此时,不报错。说明优化器,会自动判断,会自动降级使用 COPY or INPLACE。
当等于64时,再次执行DDL的命令,要特别留意性能和磁盘空间的变化情况。
5.MySQL 8.0.30/31 版本下,当表的TOTAL_ROW_VERSIONS 不为0的时候,Percona XtraBackup的备份也会失败。报错信息:
Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
具体的解释说明可以参考网址:https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html
当MySQL升级到MySQL 8.0.32,同时Percona XtraBackup 下载8.0.32-25问题就解决了。
Starting with version 8.0.32-25, Percona XtraBackup allows instant columns in the backup for MySQL 8.0.32. This limitation still exists for backing up MySQL 8.0.31 and lower versions.
https://docs.percona.com/percona-xtrabackup/8.0/release-notes/8.0/8.0.32-25.0.html
6.更改字段的类型,不支持INSTANT
Changing the column data type is only supported with ALGORITHM=COPY。
7.修改字段的长度,需留意长度对应的字节数。
The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:
ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
4. Online DDL and Metadata Locks
Online DDL operations can be viewed as having three phases:
• Phase 1: Initialization In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM and LOCK options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.
• Phase 2: Execution In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.
• Phase 3: Commit Table Definition In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.
Due to the exclusive metadata lock requirements outlined above, an online DDL operation may have to wait for concurrent transactions that hold metadata locks on the table to commit or rollback. Transactions started before or during the DDL operation can hold metadata locks on the table being altered. In the case of a long running or inactive transaction, an online DDL operation can time out waiting for an exclusive metadata lock. Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.
Metadata lock information is also exposed through the Performance Schema metadata_locks table, which provides information about metadata lock dependencies between sessions, the metadata lock a session is waiting for, and the session that currently holds the metadata lock.
也可以通过 SHOW FULL PROCESSLIST 去查看是否有事务正在等待 metadata lock.如果有的话,State值为 Waiting for table metadata lock。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2019-05-21 MySQL 时间类型 DATE、DATETIME和TIMESTAMP