MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明
发表于:2024-09-22 作者:千家信息网编辑
千家信息网最后更新 2024年09月22日,Online DDL 是否锁表、是否rebuild表、inplace或copy算法的说明:原文: https://dev.mysql.com/doc/refman/5.6/en/innodb-crea
千家信息网最后更新 2024年09月22日MySQL5.6 Online DDL 是否锁表、rebuild表、inplace的说明
Online DDL 是否锁表、是否rebuild表、inplace或copy算法的说明:
原文: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html?spm=5176.100239.blogcont64664.13.SpL8lH
Operation | In-Place? | Rebuilds Table? | Permits Concurrent DML? | Only Modifies Metadata? | Notes |
---|---|---|---|---|---|
CREATE INDEX , ADD INDEX | Yes* | No* | Yes | No | Restrictions apply for FULLTEXT indexes; see next row. |
ADD FULLTEXT INDEX | Yes* | No* | No | No | Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table. |
DROP INDEX | Yes | No | Yes | Yes | Only modifies table metadata. |
OPTIMIZE TABLE | Yes* | Yes | Yes | No | Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables withFULLTEXT indexes. |
Set column default value | Yes | No | Yes | Yes | Only modifies table metadata. |
Change auto-incrementvalue | Yes | No | Yes | No* | Modifies a value stored in memory, not the data file. |
Add foreign key constraint | Yes* | No | Yes | Yes | The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only theCOPY algorithm is supported. |
Drop foreign key constraint | Yes | No | Yes | Yes | foreign_key_checks can be enabled or disabled. |
Rename column | Yes | No | Yes* | Yes | To permit concurrent DML, keep the same data type and only change the column name. |
Add column | Yes | Yes | Yes* | No | Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. |
Drop column | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Reorder columns | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Change ROW_FORMAT property | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Change KEY_BLOCK_SIZE property | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Make column NULL | Yes | Yes* | Yes | No | Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. |
Make column NOT NULL | Yes* | Yes* | Yes | No | Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, "ALTER TABLE Syntax". Data is reorganized substantially, making it an expensive operation. |
Change column data type | No | Yes | No | No | Only supports ALGORITHM=COPY |
Add primary key | Yes* | Yes* | Yes | No | Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted toNOT NULL . |
Drop primary key and add another | Yes | Yes | Yes | No | Data is reorganized substantially, making it an expensive operation. |
Drop primary key | No | Yes | No | No | Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the sameALTER TABLE statement. |
Convert character set | No | Yes* | No | No | Rebuilds the table if the new character encoding is different. |
Specify character set | No | Yes* | No | No | Rebuilds the table if the new character encoding is different. |
Rebuild with FORCE option | Yes* | Yes | Yes | No | Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes. |
"null" rebuild usingALTER TABLE ... ENGINE=INNODB | Yes* | Yes | Yes | No | Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables withFULLTEXT indexes. |
Set STATS_PERSISTENT ,STATS_AUTO_RECALC ,STATS_SAMPLE_PAGES persistent statisticsoptions | Yes | No | Yes | Yes | Only modifie |
其余可参考文章: https://yq.aliyun.com/articles/64664
原文
文章
算法
参考
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
电影网站的数据库设计问题
网络安全维护都有哪些方法
饥荒专用服务器模组怎么自动更新
网络安全建设新趋势
对日嵌入式软件开发
怎么设置网络安全
总结无线传感器网络技术
开发区运行服务器
知网如何使用博士论文数据库
sugon服务器管理软件
go开发 使用什么数据库
网络安全教育行业应该怎么做
中国石化网络安全制度管理原则
windowsftp服务器架设
excel 表单数据库中
网络安全大班教案及反思
高职扩招计算机网络技术难吗
小学生网络安全真实案例学生
网络安全法 盗号
贵州国企招聘软件开发
策略组开启数据库在哪里
学习网络安全跟英语有关系吗
地平线4线上模式连接不到服务器
商标数据库网
国内工业网络安全事件
学生与学校的关系数据库
计算机网络技术是学的什么
哪个行业最重视网络安全
什么芯片可以连接服务器
一年几百万的服务器