MySQL Online DDL

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

前言:

 

做为一个DBA,会经常执行Online DDL,但是在MySQL5.6之前,执行DDL会锁表,导致大量的Waiting for meta data lock,DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的,特别是在单表上千万的情况下,加个索引或改个列类型,就有可能堵塞整个表的读写。这可能是很多DBA心中的痛。在MySQL5.6引入一个新特性Online DDL,这个新特性解决了执行DDL锁表的问题,在表变更时,不会阻塞线上业务的读写,数据库依然可以正常对外提供访问

 

 

一、Online DDL的前世今生

 

1、MySQL5.6以前的Online DDL

在 MySQL 5.1 (带InnoDB Plugin)和5.5中,有个新特性叫 Fast Index Creation(下称 FIC),就是在添加或者删除二级索引的时候,可以不用复制原表。对于之前的版本对于索引的添加删除这类DDL操作,MySQL数据库的操作过程为:1)首先新建Temp table,表结构是 ALTAR TABLE 新定义的结构

2)然后把原表中数据导入到这个Temp table

3)删除原表

4)最后把临时表rename为原来的表名

为了保持数据的一致性,中间复制数据(Copy Table)全程锁表只读,如果有写请求进来将无法提供服务,连接数爆张。

引入FIC之后,创建二级索引时会对原表加上一个S锁,创建过程不需要重建表(no-rebuild);删除InnoDB二级索引只需要更新内部视图,并标记这个索引的空间可用,去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作,不能写入,但大大加快了修改索引的速度(不含主键索引,InnoDB IOT的特性决定了修改主键依然需要 Copy Table )。

 

2、MySQL5.7的Online DDL

ALGORITHM=INPLACE,可以避免重建表带来的IO和CPU消耗,保证ddl期间依然有良好的性能和并发。

ALGORITHM=COPY,需要拷贝原始表,所以不允许并发DML写操作,可读。这种copy方式的效率还是不如 inplace ,因为前者需要记录undo和redo log,而且因为临时占用buffer pool引起短时间内性能受影响。

·         In-Place为Yes是优选项,说明该操作支持INPLACE

·         Copies Table为No是优选项,因为为Yes需要重建表。大部分情况与In-Place是相反的

·         Allows Concurrent DML?为Yes是优选项,说明ddl期间表依然可读写,可以指定 LOCK=NONE(如果操作允许的话mysql自动就是NONE)

·         Allows Concurrent Query?默认所有DDL操作期间都允许查询请求,放在这只是便于参考

Notes会对前面几列Yes/No带*号的限制说明

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

 

 

二、在线DDL的限制

 

1)在alter table时,如果涉及到table copy操作,要确保datadir目录有足够的磁盘空间,能够放的下整张表,因为拷贝表的的操作是直接在数据目录下进行的。

2)添加索引无需table copy,但要确保tmpdir目录足够存下索引一列的数据(如果是组合索引,当前临时排序文件一合并到原表上就会删除)

3)在主从环境下,主库执行alter命令在完成之前是不会进入binlog记录事件,如果允许dml操作则不影响记录时间,所以期间不会导致延迟。然而,由于从库是单个SQL Thread按顺序应用relay log,轮到ALTER语句时直到执行完才能下一条,所以从库会在master ddl完成后开始产生延迟。(pt-osc可以控制延迟时间,所以这种场景下它更合适)

4)During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT … FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.

5)在执行一个允许并发DML在线 ALTER TABLE时,结束之前这个线程会应用 online log 记录的增量修改,而这些修改是其它thread里产生的,所以有可能会遇到重复键值错误(ERROR 1062 (23000): Duplicate entry)。

6)涉及到table copy时,目前还没有机制限制暂停ddl,或者限制IO阀值
7)在MySQL 5.7.6开始能够通过 performance_schema 观察alter table的进度

一般来说,建议把多个alter语句合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。

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

9)执行完 ALTER TABLE 之后,最好 ANALYZE TABLE tb1 去更新索引统计信息

 

 

三、Online DDL的实现过程

online ddl主要包括3个阶段,prepare阶段,ddl执行阶段,commit阶段,rebuild方式比no-rebuild方式实质多了一个ddl执行阶段,prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

 

Prepare阶段:

创建新的临时frm文件(与InnoDB无关)

持有EXCLUSIVE-MDL锁,禁止读写

根据alter类型,确定执行方式(copy,online-rebuild,online-norebuild)
假如是Add Index,则选择online-norebuild即INPLACE方式

更新数据字典的内存对象

分配row_log对象记录增量(仅rebuild类型需要)

生成新的临时ibd文件(仅rebuild类型需要)

 

ddl执行阶段:

降级EXCLUSIVE-MDL锁,允许读写

扫描old_table的聚集索引每一条记录rec

遍历新表的聚集索引和二级索引,逐一处理

根据rec构造对应的索引项

将构造索引项插入sort_buffer块排序

将sort_buffer块更新到新的索引上

记录ddl执行过程中产生的增量(仅rebuild类型需要)

重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)

重放row_log间产生dml操作append到row_log最后一个Block

 

commit阶段:

当前Block为row_log最后一个时,禁止读写,升级到EXCLUSIVE-MDL锁

重做row_log中最后一部分增量

更新innodb的数据字典表

提交事务(刷事务的redo日志)

修改统计信息

rename临时idb文件,frm文件

变更完成

 

 

四、测试实验

 

建立数据

root@localhost [test]>create table test01( id int(4) unsigned not null auto_increment, age int(4) unsigned not null default '0', `name` varchar(3) default null, primary key(id) )engine=innodb;

Query OK, 0 rows affected (0.14 sec)

root@localhost [test]>desc test01;

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

| Field | Type            | Null | Key | Default | Extra          |

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

| id    | int(4) unsigned | NO   | PRI | NULL    | auto_increment |

| age   | int(4) unsigned | NO   |     | 0       |                |

| name  | varchar(3)      | YES  |     | NULL    |                |

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

3 rows in set (0.03 sec)

root@localhost [test]>insert into test01 values(1,18,'小芳');

Query OK, 1 row affected (0.05 sec)

root@localhost [test]>insert into test01 values(2,19,'小亚');

Query OK, 1 row affected (0.02 sec)

root@localhost [test]>insert into test01 values(3,20,'小飞');

Query OK, 1 row affected (0.00 sec)

root@localhost [test]>SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

 

1、创建删除二级索引测试

Session1

root@localhost [test]>alter table test01 add index idx_name(name);

Query OK, 0 rows affected (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0



root@localhost [test]>

Session2

root@localhost [test]>insert into test01 values(4,19,'小亚');

Query OK, 1 row affected (0.01 sec)



root@localhost [test]>select * from test01;

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

| id | age | name   |

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

|  1 |  18 | 小芳   |

|  2 |  19 | 小亚   |

|  3 |  20 | 小飞   |

|  4 |  19 | 小亚   |

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

4 rows in set (0.00 sec)

root@localhost [test]>alter table test01 drop index idx_name;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0



root@localhost [test]>desc test01;

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

| Field | Type            | Null | Key | Default | Extra          |

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

| id    | int(4) unsigned | NO   | PRI | NULL    | auto_increment |

| age   | int(4) unsigned | NO   |     | 0       |                |

| name  | varchar(3)      | YES  |     | NULL    |                |

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

3 rows in set (0.00 sec)

root@localhost [test]>desc test01;

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

| Field | Type            | Null | Key | Default | Extra          |

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

| id    | int(4) unsigned | NO   | PRI | NULL    | auto_increment |

| age   | int(4) unsigned | NO   |     | 0       |                |

| name  | varchar(3)      | YES  |     | NULL    |                |

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

3 rows in set (0.00 sec)

由此可见,删除、增加索引是自动提交的,不会copy表,不影响读写。

 

2、添加列

示例不再粘贴,经本人测试,

添加、删除一个普通列不会copy table。不影响读写。

删除一个主键列会copy table。

添加一个主键列不会copy table。

改变表中列类型会copy table。不会影响查询、写入。

 

建议:

1)此中类型太多不一一列举,详细可以到MySQL官方看一下,都清楚了。

地址:http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

2)如果在alter之前有大事务在执行,会阻塞ddl以及后续的所有请求,因此DDL操作要在业务低峰期进行

 

 

参考文档:

http://www.cnblogs.com/cchust/p/4639397.html

http://www.cnblogs.com/zengkefu/p/5671661.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

 

 

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看