MySQL：顿悟了，添加索引时竟然不锁表？

一、前言

在 MySQL 数据库中，“锁表” 指的是对数据库表进行锁定，以控制对表中数据的并发访问。锁表是数据库管理系统（DBMS）用来维护数据一致性和完整性的一种机制。

当某个事务（Transaction）或操作需要对表中的数据进行修改（如 INSERT、UPDATE、DELETE）时，它可能会请求锁定该表或表的一部分，以防止其他事务同时访问或修改相同的数据，从而导致数据不一致或冲突。

那么，在创建索引时一定会锁表吗？

二、Online DDL

MySQL 的 Online DDL（Data Definition Language）技术允许在不中断数据库服务的情况下执行表结构的变更操作。这意味着你可以在数据库正在运行并处理查询的同时，添加索引、修改列类型、添加或删除列等操作。
传统的 DDL 操作会锁定表，导致在变更期间无法进行读写操作，而 Online DDL 则大大减少了这种锁定时间，提高了数据库的可用性。

在MySQL 5.6 及更高版本中，InnoDB 存储引擎支持在线添加索引（即使用 Online DDL），这通常意味着在添加索引时不会锁定整个表，从而允许并发读写操作。

Online DDL 的关键参数

ALGORITHM
INPLACE: 尽可能在原地修改表结构，不创建临时表。
COPY: 创建临时表，然后复制数据到新表，最后替换旧表。
DEFAULT: 由 MySQL 自动选择最佳算法。

LOCK
NONE: 尽可能不锁定表，允许读写操作。
SHARED: 锁定表，只允许读操作。
EXCLUSIVE: 锁定表，不允许读写操作。
DEFAULT: 由 MySQL 自动选择最佳锁定级别。

代码示例

ALTER TABLE my_table ADD INDEX idx_my_column (my_column), ALGORITHM=INPLACE, LOCK=NONE;

注意事项：
性能影响：虽然 Online DDL 减少了锁定时间，但在执行期间仍可能对性能产生一定影响，尤其是在大数据量的表上。
事务支持：在 InnoDB 中，Online DDL 操作通常是事务性的，但某些操作可能无法回滚。
限制：某些类型的表结构变更（如改变主键）可能无法使用 Online DDL。

三、总结

在 MySQL 5.6 之前，创建索引时会锁表，但在 MySQL 5.6.7 之后，因为新增了 Online DDL 技术，所以此时在添加索引时，是可以和 DML 数据操作语言 INSERT、UPDATE、DELETE、SELECT 等命令一起执行的。