MySQL加索引会导致数据库锁表吗？
在数据库开发和运维中，“加索引是否会导致锁表”是一个非常经典的疑问。很多开发者在生产环境加索引时都遇到过业务阻塞、告警频发的情况，从而对加索引操作心生畏惧。
简单来说：在MySQL 5.6及以上版本中，使用InnoDB引擎给表加普通索引，默认不会锁表（允许并发DML操作）；但在操作的准备和提交阶段会有极短的锁；而在MySQL 5.5及更早版本中，加索引确实会导致锁表。
下面我们来详细剖析这背后的原理以及生产环境中的最佳实践。
一、历史背景：MySQL 5.5及以前的锁表时代
在MySQL 5.5及之前的版本中，DDL（数据定义语言，如ADD INDEX）采用的是COPY算法。其执行过程如下：
对原表加排他锁，此时原表完全不可读写（锁表）。
创建一个与原表结构相同的临时表，并在临时表上添加新索引。
将原表中的数据逐行拷贝到临时表。
删除原表，将临时表重命名为原表名。
释放排他锁。
在这个过程中，由于数据拷贝需要耗费大量时间，特别是对于千万级的大表，拷贝时间可能长达数十分钟甚至数小时，这期间业务的所有增删改查都会被阻塞，对线上业务是毁灭性的打击。
二、现代机制：MySQL 5.6+的Online DDL
为了解决DDL锁表的问题，MySQL 5.6引入了Online DDL（在线数据定义）机制。对于添加普通索引（SECONDARY INDEX），InnoDB默认使用INPLACE算法，并且不排斥DML操作。
其核心流程如下：
准备阶段：短暂地获取MDL（元数据锁）的排他锁，此时新请求会被阻塞，但这个阶段非常快。
执行阶段：释放MDL排他锁，降级为MDL共享锁。此时允许并发的DML（INSERT/UPDATE/DELETE）操作。InnoDB会在线构建索引，同时将执行期间产生的DML变更写入一个Online Log（在线日志）中。
提交阶段：再次短暂获取MDL排他锁，将Online Log中的变更应用到新索引上，更新元数据，然后释放锁。
因此，在绝大多数情况下，我们说MySQL 5.6+加索引是不锁表的。你可以显式地指定算法和锁模式来确保安全：
-- 明确指定使用INPLACE算法，且不阻塞DML操作 ALTER TABLE my_table ADD INDEX idx_name (column_name), ALGORITHM=INPLACE, LOCK=NONE;
三、为什么有时候加了索引依然感觉“锁表”了？
虽然Online DDL在理论上不锁DML，但在实际生产环境中，加索引依然可能引发业务阻塞，通常是由以下几个原因造成的：
1. MDL（元数据锁）等待导致“假锁表”
Online DDL在开始和结束阶段需要获取MDL排他锁。如果此时有一个长事务正在对该表执行查询（持有MDL共享锁），DDL操作会被阻塞，等待该事务释放锁。更严重的是，DDL一旦等待MDL锁，它自身也会持有MDL共享锁，这会导致后续所有试图访问该表的新DML请求全部被阻塞，形成链式等待，表面看起来就像锁表了一样。
-- 查看当前正在执行的MDL锁等待情况（MySQL 5.7+） SELECT * FROM sys.schema_table_lock_waits; -- 查找长事务并Kill掉阻塞源 SHOW PROCESSLIST;
2. 主键/唯一索引的创建依然可能锁表
Online DDL对普通索引支持完美，但如果添加的是主键索引或唯一索引，情况会复杂得多。因为主键即聚簇索引，修改主键意味着要重建整张表。虽然InnoDB也支持INPLACE方式创建主键，但在某些版本和场景下，仍可能退化成COPY算法，或者需要更长时间的MDL排他锁。
3. 硬件资源瓶颈
加索引是一个极其消耗CPU和磁盘I/O的操作。在大表上建索引时，可能会把磁盘I/O打满，导致正常的业务SQL执行变慢，引发数据库连接池耗尽，表现出来的现象和锁表非常相似。
四、生产环境安全加索引的最佳实践
为了避免加索引引发线上事故，建议遵循以下实践：
避开业务高峰期：尽管有Online DDL，依然建议在低峰期操作，避免I/O争抢和潜在的MDL锁冲突。
检查长事务：在执行DDL前，先执行
SHOW PROCESSLIST确保没有长时间运行的查询或事务。设置锁等待超时：如果DDL长时间获取不到MDL锁，不要让它一直阻塞后续请求，可以设置较短的锁等待超时时间，让DDL快速失败。
-- 设置当前会话的锁等待超时时间为5秒（默认通常是50秒） SET SESSION lock_wait_timeout = 5; -- 执行加索引操作，如果5秒内拿不到MDL锁则自动放弃 ALTER TABLE my_table ADD INDEX idx_col (col_name), ALGORITHM=INPLACE, LOCK=NONE;
使用第三方无锁变更工具：对于超大表，建议使用 gh-ost 或 pt-online-schema-change 等工具。它们的核心原理是通过创建影子表、在影子表上执行DDL、然后通过Binlog或触发器同步增量数据，最后通过原子Rename表来切换，对业务几乎零影响。关于这些工具的更多详细信息，你可以参考 www.ipipp.com 上的相关技术文档。
五、总结
MySQL加索引本身在5.6版本以后已经通过Online DDL实现了“在线”添加，不再长时间锁表。但我们必须警惕由于长事务导致的MDL锁互等以及资源消耗过大引发的连锁反应。只要做好操作前的检查，合理控制超时时间，或者在超大表场景下使用专业的变更工具，就能安全、平滑地完成索引添加工作。