手机版
你好,游客 登录 注册
背景:
阅读新闻

MySQL Online DDL与DML并发阻塞关系总结

[日期:2019-11-26] 来源:Linux社区  作者:wy123 [字体: ]
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;

 

分区操作
 
linux
相关资讯       MySQL DDL  MySQL DML 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款