本文共 2780 字，大约阅读时间需要 9 分钟。
MySQL-OnlineDDL
概述
online DDL特性可以使很多ALTER TABLE操作避免表的复制,以及在DDL执行过程中对DML操作的阻塞。
它具有下面的优点:
在繁忙的生产系统中,当你修改索弓|或字段定义的时候,你可能会让一-张表再几分钟内不可用,这个特性提高了数据库的响应速度和高可用性。
雷区:以为无论什么情况下,修改表结构都不会锁表,理想很丰满,现实很骨感!
MySQL最新版本中, InnoDB支持了所谓的Online方式DDL。online方式支持DDL时不仅可以读,还可以写。5.7以前的版本只能读不能写。
一 MySQL5.7 Online DDL
MySQL5.7 Online DDL可以做到DDL\DML\SELECT同时进行
Locking Options for Online DDL :
这个特性让你在DDL操作的时候,通过选择是否阻塞对整张表的访问:
LOCK=EXCLUSIVE :既不允许查询,也不允许DML操作) ,
LOCK=SHARED :允许查询但不允许DML操作
LOCK=NONE :同时允许查询和DML操作
LOCK=DEFAULT :忽略LOCK语句, 会根据操作的类型允许尽可能多的并发。
二MySQL8.0开始支持原子DDL(atomic DDL)
MySQL8.0开始支持原子DDL(atomic DDL)，把数据字典的更新、存储引擎操作、写二进制日志结合成了一个事务。
在MySQL8.0之前, alter table操作在server crash的情况下，会遗留.frm , .ibd文件。
MySQL8.0能实现原子DDL (包括DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE ),
alter table操作,在server crash的情况下,不会遗留.frm , .ibd临时文件。
三 DDL限制
四 在线DDL实验
4.1 DDL前检查
正常情况下，一张1千万的表，需要增加一个字段，你应该注意事项，具体怎么操作?
1)注意磁盘空间(临时表目录)
2）检查当前的内存剩余量
3)查看当前有没有大事务l长时间在执行
4) innodb_online_alter_log_max_size 和表的大小一致。
show variables like '%innodb_online%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
5）主从:先停主从，先加从，再加主，处理完后再开启。
4.2 实验
4.2.1 创建测试数据
创建出测试表
create table itpux_ddl(
id int(10) unsigned not null auto_increment,
c1 int(10) not null default '0',
c2 int(10) unsigned default null,
c3 timestamp not null default current_timestamp on update current_timestamp,
c4 varchar(200) not null default '',
c5 int(10) unsigned not null default '0',
primary key(id),key idx_fg1(c1),key idx_fg2(c2) ) engine=innodb ;
create procedure insert_itpux_ddl(in row_num int )
begin
declare i int default 0;
while i < row_num do
insert into itpux_ddl(c1,c2,c5,c3,c4) values(FLOOR(RAND()*row_num),FLOOR(RAND()*row_num),FLOOR(RAND()*row_num),now(),REPEAT( 'fgjy-', FLOOR(RAND()*20)));
commit;
set i = i+1;
end while;
end
call insert_itpux_ddl(20000);
select count(id) from itpux_ddl;
+-----------+
| count(id) |
+-----------+
| 20000 |
+-----------+
1 row in set (0.00 sec)
4.2.2 在线修改字段
mysql> alter table itpux_ddl add c6 varchar(60) not null default '';
Query OK, 0 rows affected (0.88 sec)
Records: 0 Duplicates: 0 Warnings: 0
Inplace 可读不可写，速度快。
mysql> alter table itpux_ddl algorithm=inplace,add c7 varchar(60) not null default '';
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
目前5.7以上速度都很快不需要inplace模式。
4.2.3 在线加索引
alter table itpux_ddl add index idx_id(c1);
Query OK, 0 rows affected, 1 warning (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 1
alter table itpux_ddl add index idx_id(c1),algorithm=inplace;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 1
转载地址：http://cabai.baihongyu.com/