如何使用SQL语句在不停机的情况下重组频繁修改的数据表？-SQL-PHP中文网

mysql 5.6以前alter table add index会锁表，因其默认采用copy-alter机制：建新表→逐行复制数据→切换表名→删除旧表，全程持有mdl_write锁，阻塞所有dml；5.6+虽支持algorithm=inplace，但仅限部分操作且需显式指定lock=none，否则仍可能退化为锁表。

如何使用sql语句在不停机的情况下重组频繁修改的数据表？

为什么 ALTER TABLE 直接加索引会锁表？

MySQL 5.6 以前的版本，ALTER TABLE ADD INDEX 默认触发表级拷贝，整个过程阻塞写入，业务高峰期直接雪崩。哪怕只是给 user_order 表加个 status_created_at_idx，也可能卡住订单写入几秒到几分钟——这不是慢，是不可接受的停写。

根本原因在于老版本用的是「copy-alter」机制：建新表 → 逐行复制数据 → 切换表名 → 删除旧表。中间任何一步失败，回滚成本高，且全程持有 MDL_WRITE 锁。

MySQL 5.6+ 支持 ALGORITHM=INPLACE 的 DDL（如加索引、改列默认值），但仅限部分操作，且需满足条件（例如不能修改主键）
PostgreSQL 用 CREATE INDEX CONCURRENTLY，不锁写，但建完需手动 VACUUM 清理无效元组
如果用 pt-online-schema-change（Percona Toolkit），它本质是双写+触发器同步，对主从延迟敏感，且要求表有主键或唯一非空索引

MySQL 8.0 怎么安全加索引不锁写？

确认你的 MySQL 版本 ≥ 8.0.12，且存储引擎是 InnoDB —— 这是前提。新版支持原生在线 DDL，但不是所有语句都“真在线”，必须显式指定算法和锁级别：

ALTER TABLE user_order 
  ADD INDEX idx_status_created_at (status, created_at) 
  ALGORITHM=INPLACE, LOCK=NONE;

关键点：

WPS灵犀

WPS灵犀是金山办公推出的原生 Office AI 智能体。

下载

LOCK=NONE 是硬性要求，漏写就可能退化成 LOCK=SHARED（仍允许读，但阻塞写）
加普通二级索引支持 LOCK=NONE；但改列类型、删主键、重命名列等操作不支持
执行前先查 SHOW CREATE TABLE user_order，确保没有 FULLTEXT 或 SPATIAL 索引，它们会强制降级为拷贝模式
观察 INFORMATION_SCHEMA.INNODB_TRX 和 PROCESSLIST，避免在长事务期间执行，否则 DDL 会被阻塞等待

频繁更新字段的索引怎么避免性能反噬？

给高频更新字段（比如 status、updated_at）建索引，写放大问题比锁表更隐蔽。每次 UPDATE 都要维护 B+ 树，尤其当该字段更新占比 > 15%，索引反而拖慢整体吞吐。

实操建议：

用 SELECT COUNT(*) FROM user_order WHERE status = 'paid' + EXPLAIN 查实际选择率，若 > 20%，索引价值低，考虑覆盖查询或冗余字段
把 status 和 created_at 组合成联合索引时，把区分度高的字段放前面（created_at 通常比 status 区分度高）
如果业务允许，用 TINYINT 存状态码代替字符串，减少索引体积和比较开销
定期用 OPTIMIZE TABLE（MySQL 8.0+ 推荐用 ALTER TABLE ... REBUILD）整理页碎片，但注意这会短暂锁表

线上表结构变更失败了怎么办？

DDL 执行中被 kill 或超时，MySQL 一般能自动回滚，但可能残留临时文件（如 #sql-ib-xxx.ibd）或未清理的元数据。别急着重启 mysqld。

先查 SHOW PROCESSLIST，确认没卡在 altering table 状态
检查 datadir 下是否有以 #sql- 开头的临时 ibd 文件，手动删除前务必确认对应表无活跃事务
若出现 ERROR 1050 (42S01): Table 'xxx' already exists，说明 rename 步骤失败，可尝试 DROP TABLE IF EXISTS #mysql50#xxx（注意语法里带井号）
最稳妥的兜底方案：用 mysqldump --no-create-info --skip-triggers 导出数据，重建表后导入，虽然耗时但可控

真正麻烦的不是单次 DDL，而是连续多次变更叠加产生的隐性负担：索引膨胀、统计信息滞后、buffer pool 冲击。每次改表前，先跑 SELECT * FROM information_schema.TABLE_STATISTICS WHERE TABLE_NAME = 'user_order'（需开启 information_schema_stats_expiry=0），看真实读写比例再决定要不要动。