mysql5.7 onlineddl原理,MYSQL5.7 ONLINE DDL

MYSQL5.7 ONLINE DDL

mysql版本信息

mysql> select @@version;

+-----------+

| @@version |

+-----------+

| 5.7.22 |

+-----------+

创建测试表

mysql> create table trsenzhang(id int(10) unsigned not null auto_increment,

-> num1 int(10) not null default '1',

-> date1 timestamp not null default current_timestamp on update current_timestamp,

-> address varchar(200) not null default '',

-> primary key(id),key idx01_num1(num1),key idx02_date1(date1)) engine = innodb;

Query OK, 0 rows affected (0.05 sec)

创建存储过程

mysql> delimiter //

mysql> create procedure insert_trsenzhang(in row_num int)

-> begin

-> declare i int default 0;

-> while i < row_num do

-> insert into test.trsenzhang(num1,date1,address) values(floor(rand()*row_num),now(),repeat('mzhang',floor(rand()*20)));

-> set i = i + 1;

-> end while;

-> end

-> //

Query OK, 0 rows affected (0.01 sec)

mysql> call insert_trsenzhang(10000);

Query OK, 1 row affected (6.01 sec)

mysql> select count(1) from trsenzhang;

+----------+

| count(1) |

+----------+

| 10000 |

+----------+

1 row in set (0.00 sec)

mysql> alter table trsenzhang add name varchar(100) not null default 'mzhang';

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table trsenzhang algorithm=inplace,add name2 varchar(100) not null default 'mzhang';

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

这里没有所谓提升很多，可能是数据太少了，看不出来吧

锁表测试

session1:

mysql> show variables like '%commit';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| autocommit | ON |

| innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

2 rows in set (0.00 sec)

mysql> set session autocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> update trsenzhang set name = '' where id= 100;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

session2:

mysql> alter table trsenzhang add name3 varchar(100) not null default 'mzhang';

session3:

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------+

| 8 | root | localhost | test | Query | 28 | Waiting for table metadata lock | alter table trsenzhang add name3 varchar(100) not null default 'mzhang' |

| 9 | root | localhost | test | Sleep | 62 | | NULL |

| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> alter table trsenzhang algorithm=inplace,add name3 varchar(100) not null default 'mzhang';

dml可以操作，但是如果有大事物的dml操作，肯定阻塞修改表结构的语句

就像官方文档所说的那样，INPLACE只支持varchar，如果要更改其他类型只能采用copy方式

mysql> alter table trsenzhang ALGORITHM=INPLACE,modify id int(11) unsigned not null;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

因为online ddl原理：是将online期间产生的操作放进了buffer中(innodb_online_alter_log_max_size),

如果DDL执行时间很长，期间又产生了大量的dml操作，以至于超过了innodb_online_alter_log_max_size变量所指定的大小，会引起DB_ONLINE_LOG_TOO_BIG错误。默认为128M，特别对于需要拷贝大表的alter操作，考虑临时加大该值，以此获得更大的日志缓存空间。

mysql> show variables like 'innodb_online_alter_log_max_size';

+----------------------------------+-----------+

| Variable_name | Value |

+----------------------------------+-----------+

| innodb_online_alter_log_max_size | 134217728 |

+----------------------------------+-----------+

总结：此操作就像oracle中的在线重定义，测试环境一切可以，小负载的库也可以，放到高并发的大库大表中，一般都会出现问题；所以线上的操作建议在业务低峰期里进行处理，而且有环境模拟，首先进行模拟，例如利用python的多线成模拟应用进行并发操作，然后操作