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

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

来源：互联网 2026-04-21 20:03:31

是，MySQL 5.6前ADD INDEX会锁表；5.6起仅InnoDB表且无外键、无全文索引时，ADD非主键/非唯一索引才可能LOCK=NONE免锁。 ALTER TABLE ADD INDEX 会锁表吗对于数据库管理员而言，ALTER TABLE ADD INDEX是否会锁表是一个关键问题。在

是，MySQL 5.6前ADD INDEX会锁表；5.6起仅InnoDB表且无外键、无全文索引时，ADD非主键/非唯一索引才可能LOCK=NONE免锁。

ALTER TABLE ADD INDEX 会锁表吗

对于数据库管理员而言，ALTER TABLE ADD INDEX是否会锁表是一个关键问题。在MySQL 5.6版本之前，该操作明确属于“全表拷贝”类型，执行时会为表加上写锁，导致所有数据操作语言（如INSERT、UPDATE、DELETE）被阻塞。若在业务高峰期执行，极易引发服务中断。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

自MySQL 5.6起，情况有所改善。官方引入了ALGORITHM=INPLACE和LOCK=NONE参数，增强了对数据定义语言操作的控制。但并非所有场景都能实现免锁。

在MySQL 5.6及以上版本中，要正确使用在线数据定义语言功能，需显式指定ALGORITHM和LOCK参数。若不指定，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;确认版本，并使用SHOW CREATE TABLE t1;仔细检查表结构，确保无外键、无全文索引，且引擎为InnoDB。

当无法确定是否满足LOCK=NONE条件，或MySQL版本低于5.6时，pt-online-schema-change（来自Percona Toolkit）成为常用的替代方案。其原理是通过“触发器+影子表”模拟在线变更，不依赖MySQL内置机制。

命令示例：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等内存资源。在查询率很高时执行，易引发Waiting for table metadata lock等等待，影响线上业务。