onlineDDL测试

最新推荐文章于 2022-10-17 16:51:17 发布

weixin_33778778

最新推荐文章于 2022-10-17 16:51:17 发布

阅读量100

点赞数

文章标签：数据库



onlineDDL语法：
 alter table
  ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  ADD   [COLUMN] col_name  column_definition [FIRST|AFTER col_name]
  CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
  MODIFY [COLUMN] col_name column_definition
 [FIRST | AFTER col_name],
ALGORITHM [=] {DEFAULT|INPLACE|COPY}  LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}; 简单的说就是原来的语句上，加个ALGORITHM=xxx LOCK=XXXX ALGORITHM指定了onlineDDL时候是使用COPY,还是INPLACE， （1）COPY表示执行DDL的时候会创建临时表。 （2）INPLACE表示不需要创建临时表。（inplace英文单词是原地的意思） （3）DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法，old_alter_table参数默认为OFF，表示采用INPLACE的方式 LOCK部分为索引创建或删除时对表添加锁的情况，默认是default，可选择的如下： （1）NONE，目标表不添加任何锁，可以进行读写操作，不阻塞任何操作。如果手工指定NONE，但是onlineDDL不支持SHARE模式，返回一个错误信息，告诉你用SHARE摸索。 （2）SHARE，对操作表加一个S锁。不阻塞读操作。写操作会阻塞，将会发生等待MDL锁，如果手工指定SHARE，但是onlineDDL不支持SHARE模式，将返回一个错误信息。 （3）EXCLUSIVE，执行索引创建或删除时，对目标表加上一个X锁。读写事务均不能进行。会阻塞所有的线程。这和COPY方式类似，但是不需要像COPY方式那样创建一张临时表。 （4）DEFAULT，该模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。 1.创建一个测试表 (mysql5.7-101)root@localhost [test]> create table ddl_test (id int(11) not null,name varchar(20) not null default '',age int(3) null default 0,primary key(id)); Query OK, 0 rows affected (0.02 sec) (mysql5.7-101)root@localhost [test]> ###这边先看看onlineDDL语法，就是DDL语句后面价格ALGORITHM=INPLACE, lock=none，是否拷贝表，锁模式，什么都指定默认为defalut (mysql5.7-101)root@localhost [test]> ALTER TABLE `ddl_test` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ALGORITHM=INPLACE, lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. (mysql5.7-101)root@localhost [test]> ALTER TABLE `ddl_test` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ALGORITHM=cpoy, lock=none; ERROR 1800 (HY000): Unknown ALGORITHM 'cpoy' (mysql5.7-101)root@localhost [test]> ALTER TABLE `ddl_test` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ALGORITHM=copy, lock=none; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. (mysql5.7-101)root@localhost [test]> ALTER TABLE `ddl_test` MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST ,ALGORITHM=copy, lock=shared; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 创建一个大表 (mysql5.7-101)root@localhost [test]> insert into ddl_test (name,age) select name,age from ddl_test; Query OK, 20971520 rows affected (1 min 20.73 sec) Records: 20971520 Duplicates: 0 Warnings: 0 ###onlineDDL，前不能有个大事务，不然会等待MDL锁 session 1 (mysql5.7-101)root@localhost [test]> select count(name) from ddl_test; session 2 (mysql5.7-101)root@localhost [test]> alter table ddl_test add column address varchar(255) not null default '',ALGORITHM=INPLACE, lock=none; session 3 (mysql5.7-101)root@localhost [crm]> show processlist; +----+---------+---------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+---------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 40 | root | localhost | test | Query | 6 | optimizing | select count(name) from ddl_test | | 41 | root | localhost | test | Query | 3 | Waiting for table metadata lock | alter table ddl_test add column address varchar(255) not null default '',ALGORITHM=INPLACE, lock=non | | 42 | root | localhost | crm | Query | 0 | starting | show processlist | | 44 | odstest | 172.16.123.63:9689 | NULL | Sleep | 921 | | NULL | | 45 | odstest | 172.16.123.63:9707 | test | Sleep | 876 | | NULL | | 46 | odstest | 172.16.123.63:10169 | test | Sleep | 893 | | NULL | | 47 | odstest | 172.16.123.63:8959 | test | Sleep | 79 | | NULL | +----+---------+---------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec) 在等待MDL锁 2.测试onlineDDL #增加列 session 1 (mysql5.7-101)root@localhost [test]> alter table ddl_test add column address varchar(255) not null default '',ALGORITHM=INPLACE, lock=none; session 2 (mysql5.7-101)root@localhost [test]> update ddl_test set name='adfadf' where id>1 and id <10000000; session 3 看到木有，并不阻塞update (mysql5.7-101)root@localhost [crm]> show processlist; +----+---------+---------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+---------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 40 | root | localhost | test | Query | 3 | updating | update ddl_test set name='adfadf' where id>1 and id <10000000 | | 41 | root | localhost | test | Query | 5 | altering table | alter table ddl_test add column address varchar(255) not null default '',ALGORITHM=INPLACE, lock=non | | 42 | root | localhost | crm | Query | 0 | starting | show processlist | | 44 | odstest | 172.16.123.63:9689 | NULL | Sleep | 1812 | | NULL | | 45 | odstest | 172.16.123.63:9707 | test | Sleep | 1767 | | NULL | | 46 | odstest | 172.16.123.63:10169 | test | Sleep | 1784 | | NULL | | 47 | odstest | 172.16.123.63:8959 | test | Sleep | 970 | | NULL | +----+---------+---------------------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec) 删除一行数据试试，也不阻塞 (mysql5.7-101)root@localhost [test]> delete from ddl_test where id=20000000; Query OK, 1 row affected (0.02 sec) (mysql5.7-101)root@localhost [test]> ####删除列 session 1 (mysql5.7-101)root@localhost [test]> alter table ddl_test drop column address,algorithm=inplace,lock=none;session 2 #### session1--DDL ，执行之后执行。(mysql5.7-101)root@localhost [test]> select count(name) from ddl_test;+-------------+| count(name) |+-------------+|    41943040 |+-------------+1 row in set (6.99 sec) session 3 (mysql5.7-101)root@localhost [crm]> show processlist; +----+---------+---------------------+------+---------+------+----------------+----------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+---------------------+------+---------+------+----------------+----------------------------------------------------------------------+ | 40 | root | localhost | test | Query | 3 | optimizing | select count(name) from ddl_test | | 41 | root | localhost | test | Query | 10 | altering table | alter table ddl_test drop column address,algorithm=inplace,lock=none | | 42 | root | localhost | crm | Query | 0 | starting | show processlist | | 44 | odstest | 172.16.123.63:9689 | NULL | Sleep | 1079 | | NULL | | 45 | odstest | 172.16.123.63:9707 | test | Sleep | 1034 | | NULL | | 46 | odstest | 172.16.123.63:10169 | test | Sleep | 1051 | | NULL | | 47 | odstest | 172.16.123.63:8959 | test | Sleep | 237 | | NULL | +----+---------+---------------------+------+---------+------+----------------+----------------------------------------------------------------------+ 7 rows in set (0.00 sec) 忘记了是DML，删除一行试一下，看会阻塞么，哈哈，并不阻塞（因为表数据很多，执行这个语句，会话二还在执行） (mysql5.7-101)root@localhost [test]> delete from ddl_test where id=200; Query OK, 0 rows affected (0.02 sec) (mysql5.7-101)root@localhost [test]> ### #####修改列 先试一下看看能不能不拷贝表， (mysql5.7-101)root@localhost [test]> alter table ddl_test modify column address varchar(200) not null default '',ALGORITHM=INPLACE, lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 呵呵了，要拷贝表 (mysql5.7-101)root@localhost [test]> alter table ddl_test modify column address varchar(200) not null default '',ALGORITHM=copy, lock=none; 那么看看是不是不锁表，也呵呵了，只支持共享锁，那么意味这阻塞DML，不阻塞select ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. (mysql5.7-101)root@localhost [test]> session 1 (mysql5.7-101)root@localhost [test]> alter table ddl_test modify column address varchar(200) not null default '',ALGORITHM=copy, lock=shared; Query OK, 83886079 rows affected (5 min 21.72 sec) Records: 83886079 Duplicates: 0 Warnings: 0 session 2 (mysql5.7-101)root@localhost [test]> update ddl_test set name='adeefadf' where id>1 and id <10000000; Query OK, 9606885 rows affected (8 min 26.51 sec) Rows matched: 9606885 Changed: 9606885 Warnings: 0 session 3 (mysql5.7-101)root@localhost [test]> show processlist; +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 42 | root | localhost | crm | Sleep | 4536 | | NULL | | 49 | root | localhost | test | Query | 0 | starting | show processlist | | 51 | odstest | 172.16.123.63:7428 | NULL | Sleep | 2966 | | NULL | | 53 | odstest | 172.16.123.63:1677 | test | Sleep | 620 | | NULL | | 54 | odstest | 172.16.123.63:2082 | test | Sleep | 606 | | NULL | | 56 | root | localhost | NULL | Sleep | 262 | | NULL | | 57 | root | localhost | test | Query | 5 | copy to tmp table | alter table ddl_test modify column address varchar(200) not null default '',ALGORITHM=copy, lock=sha | | 58 | root | localhost | test | Query | 2 | Waiting for table metadata lock | update ddl_test set name='adeefadf' where id>1 and id <10000000 | +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) DML在等待MDL锁，那么来看看select会不会阻塞，新起一个会话， session 4(mysql5.7-101)root@localhost [test]> select count(name) from ddl_test; session 3 再次查看一下 (mysql5.7-101)root@localhost [test]> show processlist; +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 42 | root | localhost | test | Query | 15 | optimizing | select count(name) from ddl_test | | 49 | root | localhost | test | Query | 0 | starting | show processlist | | 51 | odstest | 172.16.123.63:7428 | NULL | Sleep | 3033 | | NULL | | 53 | odstest | 172.16.123.63:1677 | test | Sleep | 687 | | NULL | | 54 | odstest | 172.16.123.63:2082 | test | Sleep | 673 | | NULL | | 56 | root | localhost | NULL | Sleep | 329 | | NULL | | 57 | root | localhost | test | Query | 72 | copy to tmp table | alter table ddl_test modify column address varchar(200) not null default '',ALGORITHM=copy, lock=sha | | 58 | root | localhost | test | Query | 69 | Waiting for table metadata lock | update ddl_test set name='adeefadf' where id>1 and id <10000000 | +----+---------+--------------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) 不阻塞select 小结：只要LOCK模式不是NONE的DDL操作，锁最小范围都是共享锁，意味着还是会阻塞DML，其他情况我就不一一测试了，参考官方手册

问题


####

(mysql5.7-101)root@localhost [test]> alter table ddl_test add column address varchar(255) not null default '',ALGORITHM=INPLACE, lock=none; ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again. (mysql5.7-101)root@localhost [test]> show global variables like 'innodb_online_alter_log_max_size'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | innodb_online_alter_log_max_size | 134217728 | +----------------------------------+-----------+ 1 row in set (0.02 sec) 支持动态修改 (mysql5.7-101)root@localhost [test]> (mysql5.7-101)root@localhost [(none)]> set global innodb_online_alter_log_max_size=2147483648; Query OK, 0 rows affected (0.00 sec) (mysql5.7-101)root@localhost [(none)]> innodb存储引擎实现Online DDL的原理是在执行创建或者删除操作同时，将DML操作日志写入到一个缓存中，待完成后再将重做应用到表上，以此达到数据的一致性。 这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认大小为128MB,支持动态修改 如果更新的表比较大，并且创建过程中有大量的写操作，如果遇到innodb_online_alter_log_max_size的空间不能存放日志时，会抛出相应的错误， 如果遇到改错误，我们可以调大该参数，以此获得更大的日志缓存空间。还可以设置lock的模式为SHARE，这样在执行过程中会阻塞写操作发生，
因此不需要进行DML日志的记录。