MySQL加索引是否锁表取决于索引类型、存储引擎、MySQL版本和操作方式。以下是详细分析：
一、不同情况的锁表分析
1. 默认情况（MySQL 5.6之前）
ALTER TABLE users ADD INDEX idx_email (email);
- MySQL 5.5及之前：大部分情况会锁表（COPY算法）
- 锁表影响：阻塞所有DML操作（INSERT/UPDATE/DELETE）
2. MySQL 5.6+ 在线DDL
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;
- ALGORITHM=INPLACE：原地重建，不复制整表
- LOCK=NONE：允许并发读写
- 实际影响：仅在创建索引的最后阶段有短暂的元数据锁
3. 存储引擎差异
- InnoDB：支持在线DDL（5.6+）
- MyISAM：总是锁表（不支持在线DDL）
二、索引操作的锁机制
1. 在线创建索引（推荐）
-- MySQL 5.6+ 自动使用在线DDL
ALTER TABLE users ADD INDEX idx_name (name);
-- 显式指定
ALTER TABLE users ADD INDEX idx_name (name),
ALGORITHM=INPLACE, -- 原地操作
LOCK=NONE; -- 不锁表
2. 不同算法的锁影响
| 算法 |
锁级别 |
性能影响 |
磁盘使用 |
|---|
| COPY |
表级锁 |
高（阻塞DML） |
高（两倍空间） |
| INPLACE |
元数据锁（短暂） |
中 |
中 |
| INSTANT (8.0) |
元数据锁 |
低 |
低 |
三、实际执行建议
1. 检查支持的DDL类型
-- 查看操作是否支持在线
SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX;
-- 或使用
SHOW ENGINE INNODB STATUS;
2. 生产环境最佳实践
# 1. 使用pt-online-schema-change（第三方工具）
pt-online-schema-change \
--alter "ADD INDEX idx_email (email)" \
D=database,t=users \
--execute
# 2. 分阶段操作（大表）
-- 先创建无数据的索引
CREATE INDEX idx_partial ON users(email) WHERE id > 1000000;
-- 再逐步更新
3. 规避锁表风险
-- 在低峰期执行
SET SESSION lock_wait_timeout = 300;
SET SESSION innodb_lock_wait_timeout = 300;
-- 使用INSTANT算法（MySQL 8.0+）
ALTER TABLE users
ADD INDEX idx_email (email),
ALGORITHM=INSTANT;
四、特殊情况会锁表
即使使用在线DDL，以下情况仍可能锁表：
添加全文索引/FULLTEXT
空间索引/SPATIAL
主键索引变更
表有外键约束且需要检查时
字段类型变更+加索引组合操作
五、监控和回滚方案
-- 监控DDL进度（MySQL 5.7+）
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%stage/innodb/alter%';
-- 设置超时防止长时间锁表
SET SESSION innodb_lock_wait_timeout = 30;
SET SESSION lock_wait_timeout = 30;
总结建议
MySQL 5.6+：默认支持在线加索引，但显式指定
ALGORITHM=INPLACE, LOCK=NONE更安全
大表操作：使用
pt-online-schema-change或gh-ost
MySQL 8.0+：优先使用
ALGORITHM=INSTANT
始终先在测试环境验证：使用相同数据量测试
有备份才操作：避免不可逆问题
最佳实践：对大表（>1GB）的索引操作，建议在维护窗口进行，并使用专业工具监控进度和影响。
