前言

今天在看面经的时候看到一个很有意思的问题：在MySQL中给大表加索引，结果导致业务崩溃了，这是为什么呢？

给大表加索引、加字段属于DDL（数据定义语言）操作，任何对MySQL大表的DDL操作都值得警惕，因为这样做很可能会引起锁表，报错Waiting for meta data lock，造成业务崩溃。那么该如何对大表进行加索引操作？

我搜寻了一些资料，在此记录一下此类问题发生的原因以及解决方法。

一、事故发生原因

在了解事故发生的原理之前，我们先搞清楚这个几个概念：DDL,DML,元数据锁。

DDL:DDL是数据定义语言的简称，DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。

数据定义语言是由SQL语言集中负责数据结构定义与数据库对象定义的语言，并且由CREATE、ALTER、DROP和TRUNCATE四个语法组成。比如：


create table student(
     id int identity(1,1) not null,
     name varchar(20) null,
     course varchar(20) null,
     grade numeric null
    )

DML: 数据操纵语言全程是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作。主要由insert、update、delete语法组成。

元数据锁： 元数据锁简称MDL。

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

MDL 是为了保证当用户对表执行增删查改操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

MDL锁会在事务提交之后释放，换句话说，在事务的执行期间，该事务会一直占有MDL锁。

那么可以设想一个场景：如果数据库有一个长事务在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

那么在线程 C 阻塞后，后续有对该表的 增删查改语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

之所以MDL读锁会被MDL写锁所阻塞，是因为写锁的优先级高于读锁，优先级低的必须要等待优先级高的执行完释放锁才能得到锁。

那么事故发生的原因就找出来了： 加索引属于ddl操作，ddl操作会对表加mdl写锁，写锁优先级比读锁高，会阻塞后面的所有读写操作，如果在ddl操作之前有长事务，一直不提交，导致该事务无法获取写锁，同时就会阻塞了后面所有的其他获取锁的操作，导致业务崩溃。

既然问题出现的原因已经找到了，那么该怎么解决呢？

主流的解决方法有两种：使用 pt-online-schema-change，使用ONLINE DDL

在此只讨论ONLINE DDL解决方案。

Online DDL是MySQL5.6.7版本中新推出的特性，支持“无锁”DDL。

1、4如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，因此称为online。

【参考】