mysql大数据量表如何创建索引_mysql在线创建索引避免锁表

是的，MySQL 5.6版本之前，执行ADD INDEX操作会锁定整个表；从5.6版本开始，仅当表引擎为InnoDB、且表上不存在外键约束和全文索引时，添加非主键或非唯一索引才可能通过指定LOCK=NONE参数实现免锁操作。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

ALTER TABLE ADD INDEX 会锁表吗？深入解析锁表机制

“ALTER TABLE ADD INDEX会锁表吗？”这是许多数据库管理员和开发者频繁遇到的棘手问题。在MySQL 5.6版本之前，答案是肯定的：执行ALTER TABLE ADD INDEX会触发全表拷贝，并对表施加写锁，导致所有数据操作语言（DML），包括INSERT、UPDATE、DELETE，全部被阻塞。在业务高峰期执行此类操作，极易引发服务雪崩，造成严重后果。

幸运的是，自MySQL 5.6起，官方引入了在线DDL（Online DDL）特性，通过ALGORITHM=INPLACE和LOCK=NONE参数，为我们提供了更精细的控制能力，旨在实现免锁创建索引。然而，这并非在所有场景下都适用，存在严格的限制条件。

• 首先，只有为InnoDB引擎的表添加非主键、非唯一的普通索引时，才有可能实现LOCK=NONE免锁操作。
• 其次，如果目标表存在外键约束、已建有全文索引（FULLTEXT），或者MySQL版本过低，则无法享受在线DDL的便利，操作仍会锁表。
• 一个实用的监控方法是：在执行过程中，使用SHOW PROCESSLIST命令观察线程状态。如果长时间显示altering table，则很可能表已被锁定，正在执行拷贝操作。

ONLINE DDL 的正确写法和参数含义详解

既然MySQL 5.6及以上版本支持在线创建索引，那么如何正确使用呢？关键在于必须显式指定ALGORITHM和LOCK参数来控制DDL行为。如果省略参数，MySQL将采用默认策略，这可能并非你期望的免锁模式。

• 安全写法示例：ALTER TABLE t1 ADD INDEX idx_col1 (col1) ALGORITHM=INPLACE, LOCK=NONE;
• 参数深度解读：ALGORITHM=INPLACE意味着操作会尽量在原表的表空间内完成，避免重建整张表；而ALGORITHM=COPY则是传统的、会锁表的全表拷贝模式。
• 重要安全机制：指定LOCK=NONE是我们的目标，但MySQL会自行校验当前条件是否允许。如果条件不满足（例如存在全文索引），它会直接报错（如ALGORITHM=INPLACE not supported），而不会静默地降级为锁表模式，这有效防止了误操作。
• 执行前必备检查清单：首先运行SELECT @@version;确认MySQL版本；其次使用SHOW CREATE TABLE t1;仔细检查表结构，确保引擎为InnoDB，且无外键、无FULLTEXT索引。

pt-online-schema-change 是更稳妥的选择吗？

当不确定是否满足LOCK=NONE的条件，或者使用的MySQL版本低于5.6时，如何安全地为大表添加索引？此时，来自Percona Toolkit的pt-online-schema-change工具成为了业界广泛采用的标准方案。其核心原理是通过“触发器+影子表”的方式模拟在线表结构变更，不依赖MySQL内置的DDL机制。

• 典型命令示例：pt-online-schema-change --alter "ADD INDEX idx_col2 (col2)" D=test,t=t1 --execute
• 优点与代价分析：该工具确实能实现全程不锁原表，但需要付出相应代价：会增加主从复制延迟，占用近乎双倍的磁盘空间，并且通常会对原表的写入吞吐量产生10%到30%的性能影响。
• 严苛的前置条件：使用前必须确保：从库复制无延迟、binlog_format设置为ROW模式、原表必须拥有主键或唯一的非空索引（否则触发器无法准确定位数据行）。
• 血的教训与最佳实践：切勿省略--dry-run（干跑测试）和--print（打印执行计划）步骤。务必先预览工具将执行的操作，确认无误后再正式执行。在线上环境贸然操作，可能导致难以挽回的损失。

创建索引前必须检查的三件事：规避风险指南

为大数据量表创建索引，绝非一个简单的命令执行过程。许多失败的线上变更，其根源往往在于准备阶段的疏忽。以下是三个必须完成的检查项：

• 第一，检查磁盘空间：执行df -h /var/lib/mysql。即使是ALGORITHM=INPLACE操作，索引构建过程中的排序和临时文件也需要充足的磁盘空间。空间不足将直接导致操作失败。
• 第二，确认查询模式与索引有效性：使用EXPLAIN分析你的核心查询语句（例如EXPLAIN SELECT ... WHERE col1 = ?），验证计划中新索引是否会被实际命中。盲目添加无效索引不仅浪费存储和内存资源，还会拖慢数据写入速度。
• 第三，避开业务高峰期并评估资源竞争：即便使用LOCK=NONE，创建索引仍需大量读取数据页，会激烈竞争Buffer Pool等内存资源。在高QPS时段执行，极易引发Waiting for table metadata lock等锁等待，从而影响线上业务的稳定性。

总而言之，真正的挑战往往不在于记住语法命令，而在于综合判断“在当前环境下，这个索引能否安全、有效地创建”。磁盘空间、服务器负载、主从状态、索引设计合理性……这些因素环环相扣。遗漏其中任何一项检查，都可能让一个预计几分钟完成的索引创建任务，演变为持续数小时的线上故障。