MySQL 5.6以前ALTER TABLE ADD INDEX会锁表，因其默认采用copy-alter机制：建新表→逐行复制数据→切换表名→删除旧表，全程持有MDL_WRITE锁，阻塞所有DML；5.6+虽支持ALGORITHM=INPLACE，但仅限部分操作且需显式指定LOCK=NONE，否则仍可能退化为锁表。
为什么 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;
关键点：
-
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），看真实读写比例再决定要不要动。
