MySQL加索引会导致数据库锁表吗

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. 不同算法的锁影响

三、实际执行建议

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，以下情况仍可能锁表：

五、监控和回滚方案

-- 监控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;

总结建议

最佳实践：对大表（>1GB）的索引操作，建议在维护窗口进行，并使用专业工具监控进度和影响。