MySQL加索引时会不会锁表

留言

字节二面：mysql加索引的时候到底会不会锁表 ？被问懵了。。



大家好，我是百里。今天我们聊一聊关于MySQL添加索引是否会锁表的问题。在MySQL 5.5及以前的版本中，添加索引会锁定整个表，但在5.6版本中，引入了Online DDL功能，解决了全程锁表的问题。今天，我们就来验证一下Online DDL是否真的不会锁表。

我这里有一个分数表，包含了600万的测试数据。我们要验证的是，在我们日常使用的InnoDB存储引擎中，添加索引是否会锁定整个表。首先，我们开启一个事务添加索引，然后再开启一个查询事务，结果显示查询可以正常返回。接着，我们再开启一个修改事务，发现也可以正常进行修改。这说明我们的DDL语句并没有锁定整个表。

但是，这真的意味着在执行过程中它不会锁表吗？我们来看另一种情况。首先，我们删除之前添加的索引，然后再次开启一个事务添加索引。如果我们不提交查询事务，然后用show processlist命令查看当前的执行信息，我们会发现DDL语句正在执行中，但如果我们的查询事务持有了元数据锁，那么DDL语句会等待这个锁被释放。

提交查询事务后，索引添加成功，说明即便在Online DDL过程中，还是存在加锁的步骤。如果其他事务占用了元数据锁，DDL语句会被阻塞。

那么，有没有更好的方法来执行DDL语句呢？这时，我们可以了解一下Online DDL的具体作用。简单来说，它允许在执行DDL语句时，不中断现有数据的读写操作。它的执行过程大约分为三个阶段：初始化阶段、执行阶段和提交阶段。在初始化阶段，它会创建一个可升级的元数据读锁；在执行阶段，将读锁升级为写锁，然后降级为读锁；提交时，再次将读锁升级为写锁，最终释放元数据锁。

与我们日常使用的DDL语句不同，Online DDL引入了两个参数：ALGORITHM和LOCK。ALGORITHM有三种模式：默认模式、INPLACE和COPY。INPLACE模式是解决全程锁表问题的关键，它在原表上进行修改，不需要生成临时表或进行数据复制。而COPY模式则是5.6版本之前的处理方式，会锁定整个表。LOCK参数有四种模式，分别是默认模式、无锁、共享锁和排他锁。

实际操作中，我们使用INPLACE方式和LOCK=NONE，可以观察到即使在添加索引的过程中，也能正常进行查询和修改操作，这说明INPLACE方式不会全程锁表。但是，如果有其他事务持有元数据锁，则DDL语句会被暂停，直到锁被释放。

因此，在实际执行Online DDL时，我们需要注意几点：首先，尽量在业务低峰期执行DDL操作，减少阻塞时间；其次，操作前确认表没有正在执行的操作和未提交的事务；最后，设置一个超时时间，以避免长时间阻塞。

今天的分享就到这里，如果你觉得有所收获，不妨点赞支持一下。如果需要配套的笔记，欢迎在评论区留言。我是百里，下期再见。

文章目录