InnoDB 加索引，这个时候会锁表吗?

2025年12月31日

面试考察点

面试官提出这个问题，通常旨在考察以下几个层面：

对 MySQL InnoDB 引擎 DDL 操作机制的了解：不仅仅是知道 “会不会锁”，更是想知道你是否清楚存在不同的加索引方式（在线 DDL 和传统拷贝表方式），以及它们之间的核心区别。
对 “锁表” 影响的理解深度：面试官想考察你是否能联系实际场景，理解 “锁表” 对生产环境的并发业务意味着什么（阻塞写操作甚至读操作），以及如何规避。
对 MySQL 版本演进和最佳实践的掌握：这个问题与 MySQL 版本强相关，特别是 5.6 版本引入的 Online DDL 特性。面试官希望你知道如何利用现代 MySQL 的特性安全地进行表结构变更。
实际问题排查与解决能力：通过你的回答，判断你在面对 “需要给大表加索引” 这一经典生产问题时，是否有清晰、稳妥的解决思路和方案。

核心答案

答案：在 MySQL 5.6 及以后的版本中，通常情况下的 ADD INDEX 操作不会锁表（这里指长时间阻塞 DML 操作的锁）。 这得益于 InnoDB 引擎的 Online DDL 特性。

具体来说，当我们使用类似于 ALTER TABLE t_user ADD INDEX idx_name (name) 的语句时，MySQL 默认会尝试使用 INPLACE 算法和 NONE（或 SHARED）锁级别来执行，这期间允许对表的 并发读写（DML） 操作正常进行。

但是，有几种特殊情况会导致锁表或需要短暂的锁：

添加 FULLTEXT（全文）索引 或 SPATIAL（空间）索引。
某些旧版本 MySQL（如 5.5 及以前）默认使用 COPY 算法。
即使在支持 Online DDL 的版本，如果指定了 ALGORITHM=COPY 或 LOCK=EXCLUSIVE，也会导致锁表。

深度解析

原理/机制

Online DDL 的核心原理是在创建索引的过程中，允许并发的 DML 操作。其大致流程如下：

初始化阶段（短暂锁）： MySQL 会获取一个元数据锁（MDL）来确保表结构定义的一致性。这个阶段非常短暂。
执行阶段（无锁）：这是主要阶段。InnoDB 会扫描聚簇索引（主键索引）来构建新的二级索引。在此过程中，所有发生的 INSERT、UPDATE、DELETE 操作，其涉及的索引变更都会被记录到一个专门的 增量日志（Online Log） 中。
应用日志阶段（短暂锁）：当索引构建完成后，MySQL 会再次短暂地获取一个排他锁（MDL），将第二阶段中记录的增量日志应用到新索引上，从而保证数据最终一致性。完成后释放锁。

正因为主要的 “数据扫描与构建” 阶段不阻塞 DML，所以我们说它 “不锁表”。而传统的 COPY 算法需要创建整个表的新副本，期间全程持有排他锁，会完全阻塞写入。

代码示例：

-- 1. 推荐的在线添加索引方式（MySQL 5.6+）
-- 此操作在默认情况下（ALGORITHM=INPLACE, LOCK=NONE）对业务影响极小
ALTER TABLE `order` ADD INDEX idx_created_at (`created_at`);

-- 2. 显式指定算法和锁级别，这是更稳妥的做法，可以避免意外锁表
ALTER TABLE `order` ADD INDEX idx_user_id (`user_id`), ALGORITHM=INPLACE, LOCK=NONE;

-- 3. 可能导致锁表的操作（应避免在业务高峰期使用）
-- 使用 COPY 算法（旧式，会锁表）
ALTER TABLE `order` ADD INDEX idx_amount (`amount`), ALGORITHM=COPY;
-- 或强制要求排他锁
ALTER TABLE `order` ADD INDEX idx_status (`status`), LOCK=EXCLUSIVE;
-- 或添加全文索引（目前仍会锁表）
ALTER TABLE `article` ADD FULLTEXT INDEX ftx_content (`content`);

对比分析与最佳实践

特性	Online DDL (`ALGORITHM=INPLACE`)	传统方式 (`ALGORITHM=COPY`)
锁机制	仅开始和结束时有短暂的元数据锁（MDL），允许并发 DML。	需要长时间的排他锁（Exclusive Lock），阻塞所有 DML。
性能影响	对业务影响小，但本身执行过程可能较慢（因为要处理并发日志）。	对业务影响致命（业务停写），但执行过程相对纯粹。
磁盘空间	需要额外空间存放 Online Log。	需要约等于原表大小的空间存放临时副本。
适用场景	生产环境首选，尤其是大表、高并发表。	仅在小表或业务维护窗口期考虑。

最佳实践建议：

先检查，后操作：在执行 DDL 前，使用 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 先试运行一下，MySQL 会提前告知该操作是否真的支持 Online DDL。
```
-- 执行后会返回该操作是否支持INPLACE算法等信息
ALTER TABLE `your_table` ADD INDEX idx_test (`column`), ALGORITHM=INPLACE, LOCK=NONE;
```
显式指定参数：生产环境执行时，强烈建议显式加上 ALGORITHM=INPLACE, LOCK=NONE，这是一个好习惯，能避免因默认配置不同而导致的意外锁表。
选择业务低峰期：虽然 Online DDL 影响小，但创建索引本身是 CPU 和 I/O 密集型操作，可能影响数据库性能。仍需在低峰期进行。
监控与备份：对大表操作前做好备份，并监控数据库的线程状态、锁等待情况。

常见误区

误区一：Online DDL 等于瞬间完成，对业务无影响。错误。它只是减少了“锁”的影响，但构建索引本身消耗大量资源，可能引起数据库负载升高，进而间接影响业务响应速度。
误区二：所有 ADD INDEX 操作都是 Online 的。错误。如前所述，FULLTEXT 和 SPATIAL 索引目前不支持 Online DDL。修改列的数据类型、删除列等很多其他 DDL 操作也可能需要 COPY 算法。
误区三：主键（Primary Key）的创建或修改也能完全 Online。需要特别注意。在已有数据的表上添加或修改主键是一个极其重量级的操作，通常涉及数据重组，可能使用 COPY 算法或虽为 INPLACE 但影响巨大，必须极度谨慎。

总结

在 MySQL 5.6+ 中，使用 ALGORITHM=INPLACE 方式添加普通二级索引通常不会长时间锁表，但务必注意其资源消耗，并规避那些仍需锁表的特殊索引类型和操作。安全变更表结构的关键在于：理解原理、显式指定参数、并在低峰期操作。

发布于2025年12月31日|10:54:52