MySQL添加索引时会锁表吗？

简介

在MySQL5.5以及之前的版本，通常更改数据表结构操作（DDL）会阻塞对表数据的增删改操作（DML）。
MySQL5.6提供Online DDL之后可支持DDL与DML操作同时执行，降低了DDL期间对业务延迟带来的影响。

下面进行案例演示。我们准备了一个分数表，有600万的测试数据，接下来我们验证一下我们日常使用的ALTER语句（不提交），看看是否会全程锁表

#事务A 添加索引 不提交
begin;
ALTER TABLE scores ADD index idx_student_id (student_id);
# commit;

#事务B 查询数据与修改数据
begin;
select id from scores where id= 1;
commit;

begin;
update scores set course_name = '张三' where id = 1;
commit;

发现查询事务和修改事务都是可以正常返回的，发现这条DDL语句不会全程锁表，执行过程中真的不会锁表吗？我们再看一种情况，首先删除索引：

ALTER TABLE scores drop index idx_student_id;

然后开启添加索引事务（不提交），然后再开启查询事务，并且也不提交，这个时候通过show processlist命令查看mysql的执行信息，观察加索引时ddl语句的执行情况。

show processlist;

观察到ddl语句正在执行中：

再show一下，发现ddl语句变成了等待我们的元数据锁释放中，即查询语句持有了我们的一个元数据锁。这个时候我们提交查询事务，可以发现索引就添加成功了。说明我们这条sql本质上还是有个加锁的过程。

再看一种情况，也先删除索引，然后开启查询事务不提交，去持有元数据锁，然后再执行添加索引，然后show一下执行信息，发现DDL语句已经在等待元数据锁释放了。然后我们提交一下查询事务，再show一下，发现DDL语句正在往下执行，这个时候我们再开启一个事务进行修改（也不提交），多show几下直到DDL执行完毕，发现我们开启的修改事务阻塞了我们的DDL语句继续执行。提交修改事务，发现索引就添加成功了。那么也就是说我们这个DDL语句实际上有两个加锁的过程，并且在这个加锁的时候如果说我们的元数据锁被其他的事务给占有了，那么我们这个DDL语句就会被阻塞，第二次也是一样的。

MySQL 5.6或更高版本上进行表结构修改操作（如添加索引），MySQL默认情况下会尝试使用Online DDL来最小化操作对读写操作的影响，MySQL会选择默认的最佳方式进行操作。

Online DDL概念

概念：在不中断现有数据读写操作的情况下，自动执行 DDL语句（例如创建、修改、删除表等）的机制。Online DDL 可以在MySQL进行表空间或数据文件的变化时，自动执行 DDL语句，从而避免了传统方式中，执行 DDL 语句时对数据库读写操作的干扰和中断。

执行过程：Online ddl 执行大致可分为三个阶段：初始化阶段、执行阶段和提交表定义阶段：

• 初始化阶段：
• • 评估存储引擎能力与DDL语句
• • 评估ALGORITHM 和 LOCK
• • 创建可升级的MDL读锁（元数据读锁）
• 执行阶段：
• • 此阶段分为两个步骤准备和执行DDL语句
• • 此阶段是否需要MDL写锁取决于初始化阶段评估的因素。如果需要MDL写锁的话，仅在准备过程会短暂的使用MDL写锁，然后降级为MDL读锁
• • DDL执行过程（最耗时）
• 提交表定义阶段：
• • 此阶段会将MDL读锁升级到MDL写锁，此阶段一般较快，因此独占锁的时间也较短
• • 用新的表定义替换旧的表定义，释放MDL锁

Online DDL用法

区别与我们日常使用的DLL语句，多了两个参数

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

ALGORITHM有三个可选项

ALGORITHM=DEFAULT：默认算法，使用最高效的算法


ALGORITHM=INPLACE：解决全程锁表的一个方式，在原表上进行更改，不需要生成临时表，不需要进行数据copy的过程。
添加索引步骤：
1.创建索引（二级索引）数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束


ALGORITHM=COPY：最原始的方式，通过临时表创建索引，需要多一倍存储，还有更多的I0（类似5.6版本之前的处理过程）添加索引步骤：
1.新建带索引（主键索引）的临时表心
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写，进行rename，升级字典锁
5.完成创建索引操作

LOCK有四种

LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。通常与INPLACE搭配使用。如果Online DDL操作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

接下来我们使用这两种执行方式来看一下是不是会全程锁表。先是第一种的INPLACE：

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;

先删除刚刚的索引，然后开启一个事务添加索引（不提交），执行一下查询事务（提交），是可以正常查询的。再开启一个修改事务（提交），是可以正常修改的。即，INPLACE方式是不会全程锁表的。那么它在执行的过程中会加锁吗？

还是删除索引，然后开启一个事务添加索引（不提交），执行一下查询事务（不提交），此时show一下执行信息，发现DDL语句正在执行ALTER语句，再show一下，发现DDL语句已经被阻塞了，因为它在等待元数据锁释放，也就是说，DDL语句实际上还是会有个加锁的过程。提交一下查询事务，发现索引就添加成功了。

然后我们再次验证一种场景，还是删除索引，然后先开启一个查询事务（不提交）先持有元数据锁，然后再开启事务添加索引（不提交），这个时候show一下执行信息，发现DDL语句正在等待元数据锁释放，提交一下查询事务，show一下发现ddl语句正在往下执行，这个时候再开启一个修改事务（不提交），多show几下直到DDL执行完毕，发现我们的DDL语句也被这个修改事务阻塞了，因为它正在等待元数据锁的释放，这个时候我们再开一个修改事务（提交）、查询事务（提交），show一下发现他们都被阻塞住。也就是说online ddl语句被阻塞后它就会进一步的将后续过来的DML事务全部阻塞住，将修改事务提交，索引就添加成功了。

所以日常使用的DDL语句的INPLACE方式执行过程有两次加锁，加锁过程中如果有其他事务持有了元数据锁，DDL语句就会被阻塞，后续来的DML操作都会被阻塞住。

再看一下COPY方式：

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

还是删除索引，然后开启一个事务添加索引（不提交），然后执行查询事务（提交），修改事务（提交），执行信息发现两个DML语句都被阻塞。即COPY方式是全程锁表的，它不允许DDL和DML的并发。

总结

从宏观上看，Online DDL的事务相当于会和其他事务并行执行，只不过Online DDL会在表空闲时进行执行，所以Online DDL不会阻塞其他操作，在Online DDL执行过程会两次获取MDL锁（1.申请MDL写锁 2. 降级成MDL读锁 3.执行DDL --耗时 4. 升级MDL写锁 5. 释放MDL锁），并且需要等待已经持有MDL锁的并发事务提交或回滚后才能继续执行，在实际执行时需注意以下几点：

• 进行DDL操作时尽量在业务低峰期进行操作。尽量的降低我们online ddll的阻塞时间，进而减少整个表的死锁
• 在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。
• 设置超时时间lock_wait_timeout，避免长时间的metedata锁等待。

set global lock_wait_timeout = 60;
# 单位是s 默认好像是一年

show variables like '%timeout%';