当前位置:Gxlcms > mysql > mysqlonlineddl_MySQL

mysqlonlineddl_MySQL

时间:2021-07-01 10:21:17 帮助过:16人阅读

大家知道,互联网业务是典型的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

人气教程排行