添加索引真的不会锁表吗？

1.MySQL DDL执行方式

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

2.Online ddl：

概念：

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

执行过程：

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

用法：

​

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：最原始的方式，通过临时表创建索引，需要多一倍存储，还有更多的IO（类似5.6版本之前的处理过程） 添加索引步骤： 1.新建带索引（主键索引）的临时表 2.锁原表，禁止DML，允许查询 3.将原表数据拷贝到临时表 4.禁止读写,进行rename，升级字典锁 5.完成创建索引操作
LOCK：
LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表 LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操作不支持对表的继续写入，则DDL操作失败，对表修改无效 LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取 LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

注意事项：

不是所有的ddl都支持online ddl；如下官网给出的部分支持场景：

更多Online ddl支持场景，可以通过MySQL官方文档去获取 https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

3.演示：

1.使用ALGORITHM = INPLACE，Lock = NONE;

使用INPLACE，NONE时不阻塞其他事务的DML操作。

2.使用ALGORITHM = COPY，Lock = EXCLUSIVE;

使用COPY，EXCLUSIVE时，会阻塞其他事务的DML操作。当DDL事务提交后，其他事务才能正常DML操作。

3.模拟online ddl执行时，有其他事务持有MDL锁。

Online DDL 过程必须等待已经持有MDL锁的并发事务提交或者回滚才能继续执行。

4.总结：

在online ddl执行过程会两次获取MDL锁，并且需要等待已经持有DML锁的并发事务提交或回滚后才能继续执行，在实际执行时需注意以下几点：
●进行DDL操作时尽量在业务低峰期进行操作。
●在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。
●设置超时时间lock_wait_timeout，避免长时间的metedata锁等待。

​