MySQL DDL 操作执行的三种方式
1,INPLACE, 在进行 DDL 操作时, 不影响表的读 & 写, 可以正常执行表上的 DML 操作, 避免与 COPY 方法相关的磁盘 I/O 和 CPU 周期, 从而最小化数据库的总体负载.
最小化负载有助于在 DDL 操作期间保持良好的性能和高吞吐量.
2,COPY, 不允许并发执行过多个 DDL, 执行过程中表不允许写但可读.
过程是通过创建一个新结构的临时表, 将数据 copy 到临时表, 完成后删除原表, 重命名新表的方式, 需要拷贝原始表,
3,INSTANT, 从 MySQL 8.0.12 开始被引入并默认使用. 目前 INSTANT 算法只支持增加列等少量 DDL 类型的操作, 其他类型仍然会默认使用 INPLACE.
以下是 MySQL 5.7 版本中各种 DDL 操作的执行方式, 总结一下:
1, 如果 DDL 的执行方式是 InPlace = YES , 那么改 DDL 的执行会支持并发 DML, 不会影响表的增删查改,
1.1, 如果 DDL 的执行方式是 InPlace = YES & Rebuilds Table = No, 那么 Only Modifies Metadata 一定为 Yes, 也即仅仅修改元数据, 类似于 INSTANT
1.2, 如果 DDL 的执行方式是 InPlace = YES & Rebuilds Table = Yes, 那么 Only Modifies Metadata 一定为 No, 需要考虑 Rebuilds Table 对 IO 和 CPU 等资源的消耗
2, 如果 DDL 的执行方式是 InPlace = NO, 那么改 DDL 的执行期间表只读, 阻塞写(增删改), 同时需要考虑对 IO 和 CPU 等资源的消耗
3, 如果是 INSTANT 方式, 类似于 1.1
如下, 对于执行期间不支持并发 DML 的操作, 标记了出来, 如果不是影响并发 DML 的操作, 就不需要考虑第三方工具了, 只需要考虑 IO 和 CPU 等资源的消耗.
因为用第三方工具同样需要消耗 IO 以及 CPU 等资源.
正常来说操作, 修改字段数据类型, 以及增加衍生列, 修改衍生列字段顺序这三种, 以及多数分区相关的操作的同时, 不支持并发 DML, 其他 DDL 执行时都支持并发 DML.
索引操作
- CREATE INDEX name ON table (col_list);(ALTER TABLE tbl_name ADD INDEX name (col_list);)
- DROP INDEX name ON table;(ALTER TABLE tbl_name DROP INDEX name;)
- ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;
- CREATE FULLTEXT INDEX name ON table(column);
- CREATE TABLE geom (g GEOMETRY NOT NULL);ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;
- ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INPLACE;
主键操作
- ALTER TABLE tbl_name ADD PRIMARY KEY (column)
- ALTER TABLE tbl_name DROP PRIMARY KEY
- ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column)
列操作
- ALTER TABLE tbl_name ADD COLUMN column_name column_definition,
- ALTER TABLE tbl_name DROP COLUMN column_name
- ALTER TABLE tbl CHANGE old_col_name new_col_name data_type
- ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST
- ALTER TABLE tbl_name CHANGE c1 c1 BIGINT
- ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255)
- ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal
- ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT
- ALTER TABLE table AUTO_INCREMENT=next_value
- ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL
- ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL
- ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd')
衍生列 (generated column) 操作
- ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED)
- ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST
- ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;
- ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL)
- ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST
- ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE
外键操作
- ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)REFERENCES tbl2(col2) referential_actions;
- ALTER TABLE tbl DROP FOREIGN KEY fk_name;
表操作
- ALTER TABLE tbl_name ROW_FORMAT = row_format
- ALTER TABLE tbl_name KEY_BLOCK_SIZE = value
- ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;
- ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;
- ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;
- OPTIMIZE TABLE tbl_name;
- ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;
- ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
- ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INPLACE, LOCK=NONE;
表空间操作
ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;
分区操作
来源: http://www.linuxidc.com/Linux/2019-11/161583.htm