欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql online ddl_MySQL

程序员文章站 2022-05-28 23:14:05
...
大家知道,互联网业务是典型的OLTP(online transaction process)应用,这种应用访问数据库的特点是大量的短事务高并发运行。因此任何限制高并发的动作都是不可接受的,甚至会给网站带来灾难。对于数据库而言,高并发通常与事务ACID是一对矛盾体,为了保证事务的ACID特性,必需用一定的手段来控制并发,比如基于锁的并发控制,亦或是基于MVCC的并发控制。基于MVCC的并发控制只是一定程度上解决了读不阻塞的问题,但对于DML或DDL依然通过锁机制来保证事务的隔离性。

所有数据库操作中DDL的锁粒度是最大的,通过包括元数据锁和表对象锁。常见的DDL包括alter,create,drop等,对于create,drop而言,通常执行过程很快,因此影响比较少,而对于alter操作,尤其是对大表的alter,这个过程可能持续时间很长,由于变更过程中,表对象的DML操作会阻塞,因此一个alter操作很有可能导致前台的网站应用出现大量的数据库访问超时情况。那么怎么解呢?第一种是alter操作不上锁,从而不影响写操作,若不行退而求其次,将alter操作的时间想办法缩短,减少不可访问表的时间。

对于mysql数据库而言,解决alter的问题也有一个过程,直到5.6才推出了online ddl功能。5.5版本通过FIC(fast index creation),提高了alter操作中加索引和删索引的速度,5.6的online ddl则优化更多,增加了更多的“在线”操作。在介绍FIC和online ddl的原理之前,我们先来看看有哪些常见的alter操作,参见表1

alter动作

说明

Add index,drop index

增加、删除、修改二级索引

Add column,drop column

增加、删除、修改列

Add primary key,drop primary key

增加、删除、修改主键索引

Set character set utf8/gbk

修改字符集、修改存储引擎

Optimize table

重组表

表1

那么针对以上几种常见的场景,我们看到FIC和online ddl到底做了什么,它们实现的原理是怎样的,下文的分析都是基于innodb表。

对于一般的alter操作而言,它的原理基本是这样的,假设需要对A表做表结构变更,首先创建一个目的表结构的临时表B;其次是锁表,将数据从A表拷贝到B表;最后是将B表rename成A表,释放锁。

FIC针对加索引和删索引做了优化,因为这种情景下,innodb的表存储结构没有变,只是多了或少了索引,因此没有必要进行全表拷贝,直接增加或删除索引即可,这样就减少了拷贝表的时间,同时也减少了锁表时间。对于需要该表存储结构的alter操作,FIC则无能无力。由于mysql迟迟不出现Online ddl的版本,FIC的场景不通用,并且依然会阻塞写,业务不可接受。没有办法,很多时候做表结构变更需要在业务低峰期(凌晨),通过主备库切换的方式去做,真是苦了DBA的童鞋们。

还好,在mysql5.6出现之前,percona公司提供了“在线”表结构变更的工具pt-online-schema-change,这个工具给dba童鞋们带来了福音。工具的核心原理是通过insert… select…语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。假设对A表进行变更,主要步骤如下:

  1. 创建目的表结构的空表,A_gst;
  2. 在A表上创建触发器,包括增、删、改触发器;
  3. 通过insert…select…limit N 语句分片拷贝数据到目的表
  4. Copy完成后,将A_gst表rename到A表

通过这个方式后,执行alter操作时,不再阻塞读和写,而且支持的alter语句也更广泛,比如表1列出来的几种情况都可以支持,除了Optimize table以外。

Mysql online ddl的原理实质与pt-online-schema-change工具原理相同,只不过将这一过程封装在mysql内部了。虽然如此,这种方式也有一定的弊端和限制,比如需要有主键,拷贝表速度不如源生锁表拷贝表快等。

最后,举一个例子说明alter操作在5.5和5.6对于DML的影响。从表2可以看到,5.5和5.6中,查询和更新都会阻塞alter操作;若有alter操作,5.5版本中,alter不会阻塞读,但会阻塞写;5.6版本中,alter不会阻塞读和写。

时间点

会话A(5.6)

会话A(5.5)

会话B

会话C

1

set autocommit=0;

update t set c2='9999' where c1=4;

set autocommit=0;

update t set c2='9999' where c1=4;

2

alter table t drop column c3;

3

Show processlist;

B:Waiting for table metadata lock

4

A:提交事务

commit

5

Show processlist;

B:copy to tmp table

6

B:继续执行

7

Select count(*) from t;

正常执行

Select count(*) from t;

正常执行

8

update t set c2='9999' where c1=4;

正常执行

update t set c2='9999' where c1=4;阻塞

8

Show processlist;

A(5.5): Waiting for table metadata lock

B: copy to tmp table

9

B执行完毕

10

A执行完毕

表2